Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 13 August 2012

Convert Non-ASM Database to ASM Database Using RMAN

OS: Windows 2003
DB: Oracle 10.2.0.1.0
Assume that you have already created an ASM instance in a disk group. For more information about ASM configuration please check my earlier post:
Here in this example we are going to convert an already existing Non ASM database SHAAN to an ASM instance with the RMAN
C:\>set oracle_sid=shaan
C:\>SQLPLUS /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 21 10:50:22 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL> SHOW parameter inst
NAME TYPE VALUE
-------------- ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string shaan
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL> alter system set control_files='+disk_group_1' scope=spfile;
SQL> alter system set db_create_file_dest='+disk_group_1' scope=spfile;
SQL> alter system set db_recovery_file_dest='+disk_group_1' scope=spfile;
SQL> shutdown immediate;
Now connect the RMAN to the target database in nomount phase and restore the controlfile into the new location from the original location then after mount the database and copy the database into ASM disk group.
C:\> RMAN target sys/oracle@SHAAN
Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jul 21 10:58:22 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHAAN (not started)
RMAN>Restore controlfile from 'E:\oracle1\product\10.2.0\oradata\shaan\control01.ctl' ;
RMAN> ALTER DATABASE MOUNT ;
RMAN> BACKUP AS COPY DATABASE FORMAT '+ disk_group_1' ;
Switch all datafile to the new ASM location and recover the database
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;
Now exit from Rman prompt and Using SQL*Plus to migrate flashback logs, change tracking file and temp files
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
Then Add New tempfile in ASM disk group and Drop the old one or simply create a new temporary tablespace using CREATE TEMPORARY TABLESPACE command and drop the old tablespace including contents and datafiles
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
SQL> create temporary tablespace temp1 tempfile '+ disk_group_1';
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
Database altered.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Create new Redo logs in ASM Diskgroup and delete the old ones.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter system switch logfile;
SQL> select group#, status from v$log;
GROUP# STATUS
------- -----------
1 ACTIVE
2 ACTIVE
3 CURRENT
4 UNUSED
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance shaan (thread 1)
ORA-00312: online log 1 thread 1:
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAAN\REDO01.LOG'
Note: Need to make sure that the redo logs groups that are dropped are not CURRENT or ACTIVE
SQL> alter system checkpoint global;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 50m ;
Database altered.
SQL> alter system checkpoint global;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
-------- -----------
1 UNUSED
2 INACTIVE
3 CURRENT
4 UNUSED
SQL>alter database drop logfile group 2;
SQL>alter database add logfile group 2 size 50m ;
SQL>alter system checkpoint global;
SQL>select group#,status from v$log;
GROUP# STATUS
-------- -----------
1 UNUSED
2 UNUSED
3 CURRENT
4 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 50m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED

SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 size 50m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ----------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group#
= b.group#;
Finally Create spfile for ASMSHAAN
SQL> create pfile ='E:\oracle\asmshaan\initshaan1.ora' from spfile ;
SQL> create spfile='+ disk_group_1/SPFILEASM.ORA' FROM pfile='E:\oracle\asmshaan\init
shaan1.ora' ;

1 comments:

  1. BlueHost is definitely the best website hosting company for any hosting plans you require.

    ReplyDelete