30 December 2012

Step by Step Migratation from NonASM to ASM database using RMAN

Step by Step Migratation from NonASM to ASM database using RMAN

Before stoping the database, we must know datafile,tempfile,logfile location .

SQL> select name from v$datafile;

NAME

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

/u02/PROD/data/system04.dbf

/u02/PROD/data/system05.dbf

/u02/PROD/data/ctxd01.dbf

/u02/PROD/data/owad01.dbf

SQL> select name from v$controlfile;

NAME

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

/u02/PROD/data/cntrl01.dbf

/u02/PROD/data/cntrl02.dbf

/u02/PROD/data/cntrl03.dbf

SQL>select GROUP#,MEMBER from v$logfile;

    GROUP#

----------

MEMBER

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

         3

/u02/PROD/data/redo03a.dbf

         3

/u02/PROD/data/redo03b.dbf

         2

/u02/PROD/data/redo02a.dbf

SQL> select FILE#,name from v$tempfile;

     FILE#

----------

NAME

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

         1

/u02/PROD/data/temp04.dbf

         2

/u02/PROD/data/temp03.dbf

         3

/u02/PROD/data/temp02.dbf

SQL> select name from v$asm_diskgroup where name='ASMPROD';

NAME

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

ASMPROD

1 rows selected.

SQL> exit

then Shutdown your database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

Database mounted.

SQL>exit

Take Full backup of your Database using RMAN:-

[oraprod@prod02 ~]$ rman target / catalog rman/muthu@RMAN

Recovery Manager: Release 11.1.0.7.0 - Production on Sat Dec 29 23:22:21 2012

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

connected to target database: PROD (DBID=3877050111, not open)

connected to recovery catalog database

RMAN> backup as compressed backupset incremental level 0 cumulative tag LEVEL0_BACKUP filesperset 4 format '/l01/backup/PROD/level0/%d_LVL0_%T_%s' database;

Starting backup at 29-DEC-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1983 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1981 device type=DISK

input datafile file number=00396 name=/u02/PROD/data/a_summ01.dbf

channel ORA_DISK_4: starting piece 1 at 29-DEC-12

channel ORA_DISK_5: starting compressed incremental level 0 datafile backup set

channel ORA_DISK_5: specifying datafile(s) in backup set

input datafile file number=00038 name=/u02/PROD/data/a_media04.dbf

input datafile file number=00085 name=/u02/PROD/data/sysaux28.dbf

channel ORA_DISK_5: backup set complete, elapsed time: 00:04:43

channel ORA_DISK_6: finished piece 1 at 30-DEC-12

piece handle=/l01/backup/PROD/level0/PROD_LVL0_20121230_19000 tag=LEVEL0_BACKUP comment=NONE

channel ORA_DISK_6: backup set complete, elapsed time: 00:04:42

Finished backup at 30-DEC-12

RMAN>exit

[oraprod@prod02 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

SQL> exit

[oraprod@prod02 ~]$

Chnage below parameter on initPROD.ora file

Old value :   *.control_files='/u02/PROD/data/cntrl01.dbf','/u02/PROD/data/cntrl02.dbf','/u02/PROD/data/cntrl03.dbf'

New Value :   *.control_files='+DBIMP02','+DBIMP02'

[oraprod@prod02 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012

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

connected to target database: PROD (not mounted)

RMAN> restore controlfile from '/u02/PROD/data/cntrl01.dbf';

Starting restore at 30-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1977 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+ASMPROD/prod/controlfile/current.256.803349501

output file name=+ASMPROD/prod/controlfile/current.257.803349503

Finished restore at 30-DEC-12

RMAN>exit

[oraprod@prod02 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012

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

Connected to an idle instance

SQL> alter database mount;

Database altered.

SQL> exit

[oraprod@prod02 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012

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

connected to target database: PROD (not mounted)

RMAN>backup as copy database format '+ASMPROD';

Starting backup at 30-DEC-12

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1977 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1975 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00030 name=/u02/PROD/data/sysaux03.dbf

channel ORA_DISK_2: starting datafile copy

input datafile file number=00034 name=/u02/PROD/data/undotbs5.dbf

output file name=+ASMPROD/prod/datafile/apps_ts_queues.364.803353979 tag=TAG20121230T004026 RECID=133 STAMP=803354005

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

output file name=+ASMPROD/prod/datafile/system.361.803353939 tag=TAG20121230T004026 RECID=132 STAMP=803354002

channel ORA_DISK_6: datafile copy complete, elapsed time: 00:01:07

Finished backup at 30-DEC-12

RMAN>switch database to copy;

datafile 1 switched to datafile copy "+ASMPROD/prod/datafile/system.351.803353795"

datafile 2 switched to datafile copy "+ASMPROD/prod/datafile/system.352.803353795"

datafile 3 switched to datafile copy "+ASMPROD/prod/datafile/system.353.803353795"

datafile 4 switched to datafile copy "+ASMPROD/prod/datafile/system.354.803353795"

datafile 405 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.287.803351559"

datafile 406 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.288.803351655"

datafile 407 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_seed.333.803353361"

RMAN> run {

set newname for tempfile 1 to '+ASMPROD';

set newname for tempfile 2 to '+ASMPROD';

set newname for tempfile 3 to '+ASMPROD';

set newname for tempfile 4 to '+ASMPROD';

set newname for tempfile 5 to '+ASMPROD';

set newname for tempfile 6 to '+ASMPROD';

set newname for tempfile 7 to '+ASMPROD';

set newname for tempfile 8 to '+ASMPROD';

set newname for tempfile 9 to '+ASMPROD';

switch tempfile all;

}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +ASMPROD in control file

renamed tempfile 8 to +ASMPROD in control file

renamed tempfile 9 to +ASMPROD in control file

RMAN>exit

[oraprod@prod02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:05:02 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> select name from v$datafile;

NAME

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

+ASMPROD/prod/datafile/system.351.803353795

+ASMPROD/prod/datafile/system.352.803353795

+ASMPROD/prod/datafile/system.353.803353795

SQL> select name from v$tempfile;

NAME

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

+ASMPROD

+ASMPROD

+ASMPROD

SQL> alter database open;

Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER                                   STATUS

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

         3 /u02/PROD/data/redo03a.dbf            INACTIVE

         3 /u02/PROD/data/redo03b.dbf            INACTIVE

         2 /u02/PROD/data/redo02a.dbf            INACTIVE

         2 /u02/PROD/data/redo02b.dbf            INACTIVE

         1 /u02/PROD/data/redo01a.dbf            CURRENT

         1 /u02/PROD/data/redo01b.dbf            CURRENT

         4 /u02/PROD/data/redo04a.dbf            UNUSED

         4 /u02/PROD/data/redo04b.dbf            UNUSED

         5 /u02/PROD/data/redo05a.dbf            UNUSED

         5 /u02/PROD/data/redo05b.dbf            UNUSED

         6 /u02/PROD/data/redo06a.dbf            UNUSED

    GROUP# MEMBER                                   STATUS

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

         6 /u02/PROD/data/redo06b.dbf            UNUSED

12 rows selected.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASMPROD' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/PROD/data/redo01a.dbf';

Database altered.

SQL> set line 200

SQL> col member for a40

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                   STATUS

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

         2 +ASMPROD/prod/onlinelog/group_2.387.8 CURRENT

           03355621

         1 +ASMPROD/prod/onlinelog/group_1.383.8 INACTIVE

           03355199

         1 +ASMPROD/prod/onlinelog/group_1.384.8 INACTIVE

           03355211

         2 +ASMPROD/prod/onlinelog/group_2.388.8 CURRENT

           03355625

    GROUP# MEMBER                                   STATUS

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

         3 +ASMPROD/prod/onlinelog/group_3.389.8 INACTIVE

           03355633

         3 +ASMPROD/prod/onlinelog/group_3.390.8 INACTIVE

           03355635

6 rows selected.

SQL>shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oraprod@prod02 ~]$ vi /u01/PROD/oracle/db/tech_st/11.1.0/db_1/dbs/initPROD.ora

Change below parameter on initPROD.ora file

Old value :   *.control_files='+DBIMP02','+DBIMP02'

New Value :   *.control_files='+ASMPROD/prod/controlfile/current.256.803349501','+ASMPROD/prod/controlfile/current.257.803349503'

[oraprod@prod02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:35:28 2012

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

Database mounted.

Database opened.

SQL>

No comments: