This is a sample document showing
how to migrate Non ASM DB to ASM DB
1 create the ASM instance
1 create the ASM instance
2.Create the required DISK
groups according to the needs
DB name DEVEL
SQL> select file_name from
dba_data_files;
FILE_NAME
——————————————————————————–
/oracle/DEVEL/system01.dbf
/oracle/DEVEL/undotbs01.dbf
/oracle/DEVEL/sysaux01.dbf
/oracle/DEVEL/users01.dbf
/oracle/DEVEL/example01.dbf
——————————————————————————–
/oracle/DEVEL/system01.dbf
/oracle/DEVEL/undotbs01.dbf
/oracle/DEVEL/sysaux01.dbf
/oracle/DEVEL/users01.dbf
/oracle/DEVEL/example01.dbf
SQL> select name from
v$controlfile;
NAME
——————————————————————————–
/oracle/DEVEL/control01.ctl
SQL> select MEMBER from v$logfile;
——————————————————————————–
/oracle/DEVEL/control01.ctl
SQL> select MEMBER from v$logfile;
MEMBER
——————————————————————————–
/oracle/DEVEL/redo03.log
/oracle/DEVEL/redo02.log
/oracle/DEVEL/redo01.log
Shutdown the database.
——————————————————————————–
/oracle/DEVEL/redo03.log
/oracle/DEVEL/redo02.log
/oracle/DEVEL/redo01.log
Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
Start the database in nomount mode.
RMAN> STARTUP NOMOUNT (make sure
you start with the spfile )
Restore the controlfile into ASM
RMAN> restore controlfile to ‘+DATA1′ from ‘/oracle/DEVEL/control01.ctl’;
Restore the controlfile into ASM
RMAN> restore controlfile to ‘+DATA1′ from ‘/oracle/DEVEL/control01.ctl’;
Starting restore at 22-FEB-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: copied control
file copy
Finished restore at 22-FEB-06
Finished restore at 22-FEB-06
Find the contlrol file in ASM
ASMCMD> find -t CONTROLFILE
+DATA1 *
+DATA1/DEVEL/CONTROLFILE/backup.264.583101555
+DATA1/DEVEL/CONTROLFILE/backup.265.583101651
Modify the spfile for the controlfile new path
SQL> alter system set control_files=’+DATA1/DEVEL/CONTROLFILE/backup.264.583101555′,’+DATA1/DEVEL/CONTROLFILE/backup.265.583101651′ scope=spfile;
+DATA1/DEVEL/CONTROLFILE/backup.265.583101651
Modify the spfile for the controlfile new path
SQL> alter system set control_files=’+DATA1/DEVEL/CONTROLFILE/backup.264.583101555′,’+DATA1/DEVEL/CONTROLFILE/backup.265.583101651′ scope=spfile;
System altered.
shutdown the database and restart in mount state
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
shutdown the database and restart in mount state
SQL> shutdown
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
473956352 bytes
Fixed Size 1220072 bytes
Variable Size 142606872 bytes
Database Buffers 327155712 bytes
Redo Buffers 2973696 bytes
Database mounted.
Fixed Size 1220072 bytes
Variable Size 142606872 bytes
Database Buffers 327155712 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> select name from
v$controlfile;
NAME
——————————————————————————–
+DATA1/devel/controlfile/backup.264.583101555
+DATA1/devel/controlfile/backup.265.583101651
Now move the datafiles to ASM
——————————————————————————–
+DATA1/devel/controlfile/backup.264.583101555
+DATA1/devel/controlfile/backup.265.583101651
Now move the datafiles to ASM
(here as my DB size was too
small i used this simple method, where as in big sized DB you should
implement some other strategies to do everything in in
parallel as the downtime will be minimal)
RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA1′;
RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA1′;
Starting backup at 22-FEB-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/DEVEL/system01.dbf
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/DEVEL/system01.dbf
output
filename=+DATA1/devel/datafile/system.256.583100553 tag=TAG20060222T202229
recid=5 stamp=583100614
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/DEVEL/sysaux01.dbf
output filename=+DATA1/devel/datafile/sysaux.257.583100615 tag=TAG20060222T202229 recid=6 stamp=583100652
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oracle/DEVEL/example01.dbf
output filename=+DATA1/devel/datafile/example.258.583100661 tag=TAG20060222T202229 recid=7 stamp=583100675
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/DEVEL/undotbs01.dbf
output filename=+DATA1/devel/datafile/undotbs1.259.583100677 tag=TAG20060222T202229 recid=8 stamp=583100680
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/DEVEL/users01.dbf
output filename=+DATA1/devel/datafile/users.260.583100683 tag=TAG20060222T202229 recid=9 stamp=583100684
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA1/devel/controlfile/backup.261.583100685 tag=TAG20060222T202229 recid=10 stamp=583100686
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/devel/backupset/2006_02_22/nnsnf0_tag20060222t202229_0.262.583100689 tag=TAG20060222T202229 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-FEB-06
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/DEVEL/sysaux01.dbf
output filename=+DATA1/devel/datafile/sysaux.257.583100615 tag=TAG20060222T202229 recid=6 stamp=583100652
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oracle/DEVEL/example01.dbf
output filename=+DATA1/devel/datafile/example.258.583100661 tag=TAG20060222T202229 recid=7 stamp=583100675
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/DEVEL/undotbs01.dbf
output filename=+DATA1/devel/datafile/undotbs1.259.583100677 tag=TAG20060222T202229 recid=8 stamp=583100680
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/DEVEL/users01.dbf
output filename=+DATA1/devel/datafile/users.260.583100683 tag=TAG20060222T202229 recid=9 stamp=583100684
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA1/devel/controlfile/backup.261.583100685 tag=TAG20060222T202229 recid=10 stamp=583100686
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/devel/backupset/2006_02_22/nnsnf0_tag20060222t202229_0.262.583100689 tag=TAG20060222T202229 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-FEB-06
RMAN>
RMAN> SWITCH DATABASE TO COPY;
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy
“+DATA1/devel/datafile/system.256.583100553″
datafile 2 switched to datafile copy “+DATA1/devel/datafile/undotbs1.259.583100677″
datafile 3 switched to datafile copy “+DATA1/devel/datafile/sysaux.257.583100615″
datafile 4 switched to datafile copy “+DATA1/devel/datafile/users.260.583100683″
datafile 5 switched to datafile copy “+DATA1/devel/datafile/example.258.583100661″
RMAN> ALTER DATABASE OPEN;
datafile 2 switched to datafile copy “+DATA1/devel/datafile/undotbs1.259.583100677″
datafile 3 switched to datafile copy “+DATA1/devel/datafile/sysaux.257.583100615″
datafile 4 switched to datafile copy “+DATA1/devel/datafile/users.260.583100683″
datafile 5 switched to datafile copy “+DATA1/devel/datafile/example.258.583100661″
RMAN> ALTER DATABASE OPEN;
database opened
SQL> select file_name from
dba_data_files;
FILE_NAME
——————————————————————————–
+DATA1/devel/datafile/system.256.583100553
+DATA1/devel/datafile/undotbs1.259.583100677
+DATA1/devel/datafile/sysaux.257.583100615
+DATA1/devel/datafile/users.260.583100683
+DATA1/devel/datafile/example.258.583100661
Move the redo logs to ASM
——————————————————————————–
+DATA1/devel/datafile/system.256.583100553
+DATA1/devel/datafile/undotbs1.259.583100677
+DATA1/devel/datafile/sysaux.257.583100615
+DATA1/devel/datafile/users.260.583100683
+DATA1/devel/datafile/example.258.583100661
Move the redo logs to ASM
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE#
BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 5 104857600 1 YES INACTIVE
567335 22-FEB-06
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 5 104857600 1 YES INACTIVE
567335 22-FEB-06
2
1 6
104857600 1 YES INACTIVE
567340 22-FEB-06
567340 22-FEB-06
3
1 7
52428800 1 NO CURRENT
567343 22-FEB-06
567343 22-FEB-06
SQL> alter database drop logfile
group 1;
Database altered.
SQL> alter database add logfile
group 1 (‘+DATA1′) size 100M;
Database altered.
SQL> alter database drop logfile
group 2;
Database altered.
SQL> alter database add logfile
group 2 (‘+DATA1′) size 100M;
Database altered.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE#
BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 8 104857600 1 NO CURRENT
567349 22-FEB-06
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 8 104857600 1 NO CURRENT
567349 22-FEB-06
2
1 6
104857600 1 YES INACTIVE
567340 22-FEB-06
567340 22-FEB-06
3
1 7
52428800 1 YES INACTIVE
567343 22-FEB-06
SQL> alter database drop logfile group 3;
567343 22-FEB-06
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile
group 3 (‘+DATA1′) size 100M;
Database altered.
SQL> select member from v$logfile;
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
+DATA1/devel/onlinelog/group_3.268.583102251
+DATA1/devel/onlinelog/group_2.267.583102163
+DATA1/devel/onlinelog/group_1.266.583102121
RMAN> BACKUP AS COPY SPFILE FORMAT ‘+DATA1′;
——————————————————————————–
+DATA1/devel/onlinelog/group_3.268.583102251
+DATA1/devel/onlinelog/group_2.267.583102163
+DATA1/devel/onlinelog/group_1.266.583102121
RMAN> BACKUP AS COPY SPFILE FORMAT ‘+DATA1′;
Starting backup at 22-FEB-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=32 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/devel/backupset/2006_02_22/nnsnf0_tag20060222t211333_0.270.583103615 tag=TAG20060222T211333 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 22-FEB-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=32 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-06
channel ORA_DISK_1: finished piece 1 at 22-FEB-06
piece handle=+DATA1/devel/backupset/2006_02_22/nnsnf0_tag20060222t211333_0.270.583103615 tag=TAG20060222T211333 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 22-FEB-06
Now find the spfile in the ASM
ASMCMD> find -t parameterfile
+DATA1 *
+DATA1/DEVEL/PARAMETERFILE/spfile.273.583106111
+DATA1/DEVEL/PARAMETERFILE/spfile.273.583106111
Now make an entry in the pfile, like
below
[oracle@RAC2 dbs]$ cat initDEVEL.ora
spfile=’+DATA1/DEVEL/PARAMETERFILE/spfile.273.583106111′
[oracle@RAC2 dbs]$
[oracle@RAC2 dbs]$ cat initDEVEL.ora
spfile=’+DATA1/DEVEL/PARAMETERFILE/spfile.273.583106111′
[oracle@RAC2 dbs]$
so now all the DB files are
stored in ASM.
No comments:
Post a Comment