What is ASM:

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed.

Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

In summary ASM provides the following functionality:

1. Manages groups of disks, called disk groups.
2. Manages disk redundancy within a disk group.
3. Provides near-optimal I/O balancing without any manual tuning.
4. Enables management of database objects without specifying mount points and filenames.
5. Supports large files.

Configuration of ASM:

Use the following command to identify the kernel version

[oracle@node1 u01]$ uname -r

2.6.18-53.el5

Download Oracle ASMLib from following site ==>

http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

[oracle@node1 u01]$ rpm -ivh --force --nodeps *

oracleasm-support-2.1.7-1.el5.x86_64.rpm

oracleasm-2.6.18-274.el5-2.0.5-1.el5.x86_64.rpm

oracleasmlib-2.0.4-1.el5.x86_64.rpm

oracleasm-2.6.18-53.1.19.el5xen-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5debug-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5PAE-2.0.4-1.el5.i686.rpm

oracleasm-2.6.18-53.1.19.el5-2.0.4-1.el5.i686.rpm

Configure Oracle ASM Library driver

[oracle@node1 init.d]# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting <ENTER> without typing an

answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Initializing the Oracle ASMLib driver: [OK ]

Scanning the system for Oracle ASMLib disks: [OK]

You can check the Oracle ASM status

[oracle@node1 init.d]# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

Creating Raw disks in VM:

You can check the Oracle ASM status

[oracle@node1 init.d]# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

Creating a new Partition for ASM disk

[oracle@node1 ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): p

Disk /dev/sdb: 5368 MB, 5368709120 bytes

255 heads, 63 sectors/track, 652 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n

Command action

e extended

p primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-652, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-652, default 652): +5000M

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[oracle@node1 ~]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 38 305203+ 83 Linux

/dev/sda2 39 3405 27045427+ 83 Linux

/dev/sda3 3406 3916 4104607+ 82 Linux swap / Solaris

Disk /dev/sdb: 5368 MB, 5368709120 bytes

255 heads, 63 sectors/track, 652 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sdb1 1 609 4891761 83 Linux

To create a ASM disk using Oracle ASMLib

[oracle@node1 ~]# oracleasm createdisk DISK1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[oracle@node1 ~]# oracleasm createdisk DISK2 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[oracle@node1 ~]# oracleasm createdisk DISK3 /dev/sdd1

Writing disk header: done

Instantiating disk: done

To List and check the Oracle ASM disks

[oracle@node1 ~]# oracleasm listdisks

[oracle@node1 ~]# oracleasm querydisk DISK1

Disk "DISK1" is a valid ASM disk

Creating password file for ASM instance:

orapwd file=$ORACLE_HOME/dbs/orapwasm password=oracle

Creating directories:

+ASM,bdump,cdump,udump

Creating pfile for ASM instance:

db_unique_name = 'ASM'

instance_type = 'asm'

background_dump_dest = '/u01/app/oracle/admin/+ASM/bdump'

core_dump_dest = '/u01/app/oracle/admin/+ASM/cdump'

user_dump_dest = '/u01/app/oracle/admin/+ASM/udump'

export ORACLE_SID=ASM

sqlplus / as sysdba

SQL>startup nomount pfile='/u01/initasm.ora';

ASM instance started

Total System Global Area 79691776 bytes

Fixed Size 1217812 bytes

Variable Size 53308140 bytes

ASM Cache 25165824 bytes

Now creating database through DBCA and use the ASM disks:

Why should we perform consistent export?

As per TomKyte....

it makes all of the queries run by exp be "as of the same point in time-- consistent with
regards to eachother"

imagine if you started an export at 9am of the EMP and DEPT tables.

EMP started exporting at 9am and DEPT at 9:15am.

Now, the EMP data would be "as of 9am", but the DEPT data would be as of 9:15am.  What if
you import that data now -- will it work?  maybe, maybe not -- perhaps at 9:10am, someone
fired the last employee in department 50 and deleted department 50.  Your EMP export
would have them in this deptno, your DEPT export would not have this deptno.  The import
would fail.

If you use consistent=y, Oracle will export all of the data "as of 9am", so deptno=50
will be included in all tables and the import will succeed.

RMAN BACKUP VALIDATION AND RECOVERY FROM BLOCK CORRUPTION:

The main purpose of RMAN validation is to check for corrupt blocks and missing files

Corruption in block:

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

Physical(media corrupt) and Logical(software corrupt) Block Corruption:

In a physical corruption, which is also called a media corruption, the database does not recognize the block at all: the checksum is invalid.

Checksum:

A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every datafile or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency.

In a logical corruption, the contents of the block are logically inconsistent.

The logical corruption happens within the blocks , for eg. some index entry pointing towards a null rowid.

Validating Database Files with BACKUP VALIDATE:

You can use the BACKUP VALIDATE command to do the following:

-Check datafiles for physical and logical block corruption.

-Confirm that all database files exist and are in the correct locations.

Validating only physical corruption:

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Validating both physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Validating Backups Before Restoring Them:

You can run RESTORE ... VALIDATE to test whether RMAN can restore a specific file or set of files from a backup.

RESTORE DATABASE VALIDATE;

RESTORE ARCHIVELOG ALL VALIDATE;

RECOVRING A BLOCK CORRUPTION:

If it finds corrupted blocks(after checking with BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;) it will place the information about the corruption into a view:

v$database_block_corruption

SQL>select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

———- ———- ———- —————— ———

5 81 4 0 CORRUPT

this is what we find in the alert_.log:

Corrupt block relative dba: 0x014000b1 (file 5, block 177)

Bad header found during backing up datafile

Data in bad block:

type: 67 format: 7 rdba: 0x0a545055

last change scn: 0×0000.0007bc77 seq: 0×3 flg: 0×04

spare1: 0×52 spare2: 0×52 spare3: 0×0

consistency value in tail: 0xbc772003

check value in block header: 0xb32

computed block checksum: 0xe4c1

Reread of blocknum=177, file=/u01/app/oracle/oradata/orcl/

example01.dbf.

found same corrupt data 

Now we can tell RMAN to recover all the blocks which it has found as being corrupt:

RMAN> blockrecover corruption list;

# (all blocks from v$database_block_corruption)

Starting blockrecover at 05-04-2006:10:09:15

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/

oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_236tmb1c_.dbf

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/

flash_recovery_area/ORCL/archivelog/2006_04_05/o1_mf_1_2_236wxbsp_.arc

archive log thread 1 sequence 1 is already on disk as file

/u01/app/oracle/oradata/

orcl/redo01.log

media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 05-04-2006:10:09:24

Also read:


Segment checking using DBV


DBMS_REPAIR




Enjoy:-)

Difference between WITH GRANT and WITH ADMIN

Sometimes people are puzzled to find out the difference between WITH GRANT and WITH ADMIN.

Here is the simple explanation:

WITH GRANT OPTION:

We can grant object privileges only with grant option like (select,update,insert)

 A--------->B--------->C

If A want to revoke privileges from C, it is not possible. The user who granted the privilege can only revoke. we have to revoke the privilege from B ,that automatically revoke the privileges from C.

WITH ADMIN OPTION:

We can give the system privileges onlywith admin option(CREATE TABLE,CREATE INDEX,CREATE SESSION)

 A------>B-------->C

In admin option,it is possible to revoke the privileges from both B and C by A.Here suppose if we revoke the privileges from B means it didn’t revoke the privileges of C.


Examples:

WITH ADMIN OPTION  When a user is granted a system privilege, the grantor (i.e., the person granting the privilege, typically the DBA) also has the option to allow the grantee (the person receiving the privilege, typically the user) to grant the same privilege to other users. If this is the result desired, the grantor can grant the privilege using the WITH ADMIN OPTION. When privileges are granted WITH ADMIN OPTION,this means that the grantor has decided that the grantee can be fully trusted by him as well as by the user that granted him the system privilege in the first place. In essence 
all users holding a system privilege


Security Hole:  WITH ADMIN OPTION are considered equal and can grant and revoke that privilege from anyone, including the person who granted it to them in the first place.



For example, if you give the key to your car to a friend and tell him that it is alright to make copies of the key, when you ask for the key back from your friend, you cannot, at the same time, get back all copies that were made by him and given to others. In order to get the other copies of the key back, you need to find out who has them. Similarly, in Oracle you need to query the data dictionary to determine which other users were granted the permission being revoked by the user from which it is being revoked.



WITH GRANT OPTION on object privileges allows a user granted 
the privilege to grant it to someone else. The reason for doing this is to minimize the administrative burden of granting object privileges.if an object privilege isrevoked from a user to whom it was granted WITH GRANT OPTION, that privilege would also be removed from anyone that user granted the privilege to. For example,if Damir granted John the SELECT privilege on the DAMIR.JOHN1 table WITH GRANT OPTION, and John then granted the SELECT privilege to Tim, then if Damir issued the command REVOKE SELECT ON DAMIR.JOHN1 FROM JOHN, Tim would also no longer have the privilege. This is because when object privileges are revoked,the revoke also cascades to anyone that the privilege was granted to by the user from whom it is being revoked.



Enjoy:-)

Manually create database in Oracle 10g Linux

Hello,

It is very exciting to create database manually on Linux platform.

Play this link

Enjoy:-) 

Who are you? Development DBA or Production DBA.

Development Oracle DBAs:

Generally speaking, Oracle Development DBAs are concerned with developing applications from a database perspective. Here are some of their tasks:

• ·         Know the source code very well, and the database structure
• ·         Look mainly after one or a set of applications
• ·         Involved in SQL tuning, and re-writing of code
• ·         Development of new functionality
• ·         Liaise with developers regularly in development issues

Production Oracle DBAs

On the whole, the Oracle Production DBA is all about maintaining a reliable, secure and performant database. Here are some common tasks:

• ·         Patching
• ·         Storage and capacity planning
• ·         Backup and recovery
• ·         Rollout of new releases
• ·         Performance tuning of instance as a whole
• ·         Troubleshooting expert

It’s also essential to bear in mind that there will certainly be overlap in any job you go to; just because you don’t work on production doesn’t mean you shouldn’t know how to patch or backup and recover a database. These are just an idea of the main functions of the different types of roles and what you can expect to spend most of your time doing.

It is very commong for you to be both the Development and Production DBA. I would say that at much larger organisations where there are many Oracle DBAs you are more likely to get the distinction between Development and Production DBAs. In smaller companies, you are more likely to be working both as the Development and Production DBA.

Enjoy:-)

Wishing you all a very Happy New year 2012