Wednesday, 18 December 2013

Convert Non-ASM Database to ASM Database in oracle Using RMAN

Assume that you have already created an ASM instance in a disk group.
1. Install ASM binaries.
2. Configure ASM & create 2 Disk groups DATA & FRA & assign disks for the respective disk groups.
3. Keep the ASM Instance UP.



Step by Step Configuring ASM in Oracle 10g.
$ export ORACLE_SID=PROD

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 15 11:30:10 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>

Make the necessary changes:--
SQL> alter system set control_files='+DATA' scope=spfile;
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;
SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;

SQL> shutdown immediate;

Connect the RMAN & restore the controlfile into the new location from the original location

$ export ORACLE_SID=Prod
$ rman target
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (not started)


RMAN> startup nomount
Oracle instance started


RMAN>Restore controlfile from '\u01\product\10.2.0\oradata\prd\ctl_prd01.ctl' ;
RMAN> ALTER DATABASE MOUNT ;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA' ;

Switch the database to the copy created using the following RMAN command

RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;

Using SQL*Plus to migrate flashback logs.

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> select flashback_on from v$database;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS


Convert Redo log files from Non-ASM to ASM
SQL> create temporary tablespace temp1 tempfile '+DATA';
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 prod (thread 1)
ORA-00312: online log 1 thread 1:
'\U01\PRODUCT\10.2.0\ORADATA\PROD\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 10m ;
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 10m ;
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 10m ;
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 10m ;
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#;

Create spfile for ASM

SQL> create pfile from spfile ;
SQL> create spfile='+DATA/SPFILEASM.ORA' FROM pfile;

No comments:

Post a comment