ASM Migration

Migration:

STEP:1 Backup the controlfile

RMAN> backup current controlfile format '/oracle/aa.ctl';

Starting backup at 27-JAN-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at 27-JAN-12

channel ORA_DISK_1: finished piece 1 at 27-JAN-12

piece handle=/oracle/aa.ctl tag=TAG20120127T161724 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 27-JAN-12

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

[oracle@dev140 dbs]$

STEP2: Add the pfile parameter

[oracle@dev140 dbs]# vi initPROD.ora

DB_CREATE_FILE_DEST = '+PRODDG'

DB_CREATE_ONLINE_LOG_DEST_1='+PRODDG'

control_files='+PRODDG'

step2:

SQL> startup nomount pfile='/oracle/PROD/db/tech_st/10.2.0/dbs/initPROD.ora';

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 1271540 bytes

Variable Size 427821324 bytes

Database Buffers 633339904 bytes

Redo Buffers 11309056 bytes

SQL> create spfile from pfile;

File created.

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

STEP3: Restore Controfile

[oracle@dev140 dbs]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jan 27 16:21:34 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 1271540 bytes

Variable Size 427821324 bytes

Database Buffers 633339904 bytes

Redo Buffers 11309056 bytes

RMAN>

RMAN> restore controlfile from '/oracle/aa.ctl';

RMAN> backup as copy database format ='+PRODDG';

RMAN> switch database to copy;

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/27/2012 17:24:57

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> backup archivelog all;

RMAN> recover database;

Exit

SQL> alter database open resetlogs;

Database altered.

SQL> alter database add logfile group 4;

Database altered.

SQL> alter database add logfile group 5;

Database altered.

SQL> alter database add logfile group 6;

Database altered.

SQL>

STEP4: Create New Redologs

SQL> alter database add logfile group 4;

Database altered.

SQL> alter database add logfile group 5;

Database altered.

SQL> alter database add logfile group 6;

Database altered.

STEP5: Removing Old redologs

We can drop only inactive status loggroups

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

1 1 6 1048576000 2 YES ACTIVE

5.9651E+12 27-JAN-12

2 1 7 1048576000 2 YES ACTIVE

5.9651E+12 27-JAN-12

4 1 8 104857600 1 YES ACTIVE

5.9651E+12 27-JAN-12

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

5 1 9 104857600 1 YES ACTIVE

5.9651E+12 27-JAN-12

6 1 10 104857600 1 NO CURRENT

5.9651E+12 27-JAN-12

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL>

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

4 1 12 104857600 1 YES INACTIVE

5.9651E+12 27-JAN-12

5 1 13 104857600 1 YES INACTIVE

5.9651E+12 27-JAN-12

6 1 14 104857600 1 NO CURRENT

5.9651E+12 27-JAN-12

SQL>

STEP 6: Create new TEMP tablespace & drop old TEMP tablespace.

SQL> create temporary tablespace TEMP_ASM tempfile size 2048m;

Tablespace created.

SQL> alter database default temporary tablespace TEMP_ASM;

Database altered.

SQL> select name from v$tablespace;

SQL> drop tablespace TEMP1 including contents;

Tablespace dropped.

SQL> drop tablespace TEMP2 including contents;

Tablespace dropped.

SQL>