Tag: Osama blog

Templates:

Templates are named groups of attributes that can be applied to the files within a disk group

Examples :

-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

Available attributes include:

• UNPROTECTED – No mirroring or striping regardless of the redundancy setting.
• MIRROR – Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
• COARSE – Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
• FINE – Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

Directories

A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing

Examples :

-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

Aliases

Aliases allow you to reference ASM files using user-friendly names

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
  TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS

Files

Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

ASM Views:

Migrating to ASM Using RMAN:

• Disable change tracking (only available in Enterprise Edition) if it is currently being used.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

• Shutdown the database.
  

  SQL> SHUTDOWN IMMEDIATE 

  • Modify the parameter file of the target database as follows:
    Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
    Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
• Start the database in nomount mode.

                            RMAN> STARTUP NOMOUNT

• Restore the controlfile into the new location from the old location.

 RMAN> RESTORE CONTROLFILE FROM ‘old_control_file_name’;

• Mount the database.
• Copy the database into the ASM disk group.
  

  RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';

• Switch all datafile to the new ASM location.
  
  

  RMAN> SWITCH DATABASE TO COPY;

• Open the database.
  
  

  RMAN> ALTER DATABASE OPEN;

• Create new redo logs in ASM and delete the old ones.
• Enable change tracking if it was being used.
  
  

  SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;