Friday, October 19, 2012

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

Download Oracle ASMLib from following site ==>

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


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)
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:


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'
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:

Friday, April 27, 2012

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.

Friday, February 3, 2012


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.


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:


Validating both physical and logical corruption:


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.




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:


SQL>select * from v$database_block_corruption;

———- ———- ———- —————— ———
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/
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/
starting media recovery
archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/
archive log thread 1 sequence 1 is already on disk as file
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 05-04-2006:10:09:24

Wednesday, January 25, 2012

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:


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


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.


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


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.


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.


Monday, January 16, 2012

Manually create database in Oracle 10g Linux


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