One of the ways to migrate a database to ASM storage is to use Rman to make a “Backup as Copy” into ASM and then switching the database to the copy.
This technique can be used, combined with incremental backups, to move even very large databases into ASM. The first backup may take a long time, one or more incremental backups can be used to
update the first backup, until a downtime window is obtained to switch the database on filesystem to the backup on ASM.
Migrating a Database Into ASM
Backup Database Into ASM
[oracle@vasm ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 21 11:49:12 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ZHONGWC (DBID=182841977)
RMAN> run{
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5> allocate channel c4 device type disk;
6> backup as copy incremental level 0 database format '+DATADGNR' tag 'ORA_ASM_MIGRATION';
7> release channel c1;
8> release channel c2;
9> release channel c3;
10> release channel c4;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=857 devtype=DISK
allocated channel: c2
channel c2: sid=856 devtype=DISK
allocated channel: c3
channel c3: sid=859 devtype=DISK
allocated channel: c4
channel c4: sid=874 devtype=DISK
Starting backup at 21-FEB-2013 11:49:57
channel c1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/zhongwc/system01.dbf
channel c2: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/zhongwc/undotbs01.dbf
channel c3: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/zhongwc/sysaux01.dbf
channel c4: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/zhongwc/users01.dbf
output filename=+DATADGNR/zhongwc/datafile/users.259.807968999 tag=ORA_ASM_MIGRATION recid=6 stamp=807968998
channel c4: datafile copy complete, elapsed time: 00:00:03
channel c4: starting datafile copy
copying current control file
output filename=+DATADGNR/zhongwc/controlfile/backup.257.807969001 tag=ORA_ASM_MIGRATION recid=7 stamp=807969008
channel c4: datafile copy complete, elapsed time: 00:00:15
channel c4: starting incremental level 0 datafile backupset
channel c4: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c4: starting piece 1 at 21-FEB-2013 11:50:16
output filename=+DATADGNR/zhongwc/datafile/sysaux.256.807968999 tag=ORA_ASM_MIGRATION recid=8 stamp=807969020
channel c3: datafile copy complete, elapsed time: 00:00:26
channel c4: finished piece 1 at 21-FEB-2013 11:50:23
piece handle=+DATADGNR/zhongwc/backupset/2013_02_21/nnsnn0_ora_asm_migration_0.261.807969017 tag=ORA_ASM_MIGRATION comment=NONE
channel c4: backup set complete, elapsed time: 00:00:08
output filename=+DATADGNR/zhongwc/datafile/undotbs1.258.807968999 tag=ORA_ASM_MIGRATION recid=9 stamp=807969038
channel c2: datafile copy complete, elapsed time: 00:00:41
output filename=+DATADGNR/zhongwc/datafile/system.260.807968997 tag=ORA_ASM_MIGRATION recid=10 stamp=807969041
channel c1: datafile copy complete, elapsed time: 00:00:44
Finished backup at 21-FEB-2013 11:50:41
released channel: c1
released channel: c2
released channel: c3
released channel: c4
Once the backup finished we can check that the datafiles were copied to the datadgnr ASM diskgroup
[oracle@vasm ~]$ asmcmd ls -l DATADGNR/zhongwc/datafile Type Redund Striped Time Sys Name DATAFILE MIRROR COARSE FEB 21 11:00:00 Y SYSAUX.256.807968999 DATAFILE MIRROR COARSE FEB 21 11:00:00 Y SYSTEM.260.807968997 DATAFILE MIRROR COARSE FEB 21 11:00:00 Y UNDOTBS1.258.807968999 DATAFILE MIRROR COARSE FEB 21 11:00:00 Y USERS.259.807968999
Spfile Backup into ASM
The next step is to make a backup of the spfile and to restore it into ASM
[oracle@vasm ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 21 11:53:04 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ZHONGWC (DBID=182841977)
RMAN> run{
2> backup as backupset spfile;
3> restore spfile to '+DATADGNR/ZHONGWC/spfilezhongwc.ora';
4> }
Starting backup at 21-FEB-2013 11:54:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=874 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 21-FEB-2013 11:54:24
channel ORA_DISK_1: finished piece 1 at 21-FEB-2013 11:54:25
piece handle=/u01/app/oracle/flash_recovery_area/ZHONGWC/backupset/2013_02_21/o1_mf_nnsnf_TAG20130221T115424_8lc6mjwd_.bkp tag=TAG20130221T115424 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-FEB-2013 11:54:25
Starting restore at 21-FEB-2013 11:54:25
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+DATADGNR/ZHONGWC/spfilezhongwc.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ZHONGWC/backupset/2013_02_21/o1_mf_nnsnf_TAG20130221T115424_8lc6mjwd_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ZHONGWC/backupset/2013_02_21/o1_mf_nnsnf_TAG20130221T115424_8lc6mjwd_.bkp tag=TAG20130221T115424
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 21-FEB-2013 11:54:32
Once the spfile restore into ASM is ready can check again the datadgnr ASM diskgroup. Note that the spfilesati.ora is a
link to +DATADGNR/DB_UNKNOWN/PARAMETERFILE/SPFILE.262.807969267
[oracle@vasm ~]$ asmcmd ls -l DATADGNR/zhongwc/spfilezhongwc.ora
Type Redund Striped Time Sys Name
N spfilezhongwc.ora => +DATADGNR/DB_UNKNOWN/PARAMETERFILE/SPFILE.262.807969267
Consistent database shutdown
Next step is to shutdown the database based on Filesystem
[oracle@vasm ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 21 13:18:23 2013 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Prepare Pfile for the ASM Database
Next step is to prepare a parameter file “initsati.ora” that will point to the spfile inside ASM
[oracle@vasm ~]$ cd $ORACLE_HOME [oracle@vasm db_1]$ echo "spfile=+DATADGNR/zhongwc/spfilezhongwc.ora" > dbs/initzhongwc.ora [oracle@vasm db_1]$ cat dbs/initzhongwc.ora spfile=+DATADGNR/zhongwc/spfilezhongwc.ora
Start the database in NOMOUNT mode
On the next step we start the database in nomount mode using the pfile that points into the ASM spfile
[oracle@vasm ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 21 13:24:35 2013 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initzhongwc.ora' ORACLE instance started. Total System Global Area 1224736768 bytes Fixed Size 2095896 bytes Variable Size 318768360 bytes Database Buffers 889192448 bytes Redo Buffers 14680064 bytes SQL> show parameter spfile NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string +DATADGNR/zhongwc/spfilezhongw c.ora
Change Parameters on Spfile to point to ASM
On this step we will prepare the spfile to migrate the controlfile into ASM, an we will set recovery area size and
destination, then we will shutdown the database
SQL> alter system set control_files='+DATADGNR','+DATADGNR' scope=spfile; System altered. SQL> alter system set db_recovery_file_dest_size=2g scope=spfile; System altered. SQL> alter system set db_recovery_file_dest='+DATADGNR'; System altered. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down.
Move the controlfiles into ASM
The controlfiles will be restored to the location we specified on the previous step using the parameter “control_files”.
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initzhongwc.ora' ORACLE instance started. Total System Global Area 1224736768 bytes Fixed Size 2095896 bytes Variable Size 318768360 bytes Database Buffers 889192448 bytes Redo Buffers 14680064 bytes SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@vasm ~]$ rman target / Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 21 13:32:17 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: zhongwc (not mounted) RMAN> restore controlfile from '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_zhongwc.f'; Starting restore at 21-FEB-2013 13:35:17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=871 devtype=DISK channel ORA_DISK_1: copied control file copy output filename=+DATADGNR/zhongwc/controlfile/current.263.807975321 output filename=+DATADGNR/zhongwc/controlfile/current.264.807975323 Finished restore at 21-FEB-2013 13:35:33 RMAN>
Switch the Database from File System to ASM
On this step we will actually point the database to switch from the datafiles located on file system to the datafiles located
inside ASM.
From within the same Rman session we were working on the previous step we mount the database and we switch to the
ASM datafiles.
RMAN> mount database; database mounted released channel: ORA_DISK_1 RMAN> switch database to copy; Starting implicit crosscheck backup at 21-FEB-2013 13:38:23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=871 devtype=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 21-FEB-2013 13:38:23 Starting implicit crosscheck copy at 21-FEB-2013 13:38:24 using channel ORA_DISK_1 Crosschecked 5 objects Finished implicit crosscheck copy at 21-FEB-2013 13:38:24 searching for all files in the recovery area cataloging files... no files cataloged datafile 1 switched to datafile copy "+DATADGNR/zhongwc/datafile/system.260.807968997" datafile 2 switched to datafile copy "+DATADGNR/zhongwc/datafile/undotbs1.258.807968999" datafile 3 switched to datafile copy "+DATADGNR/zhongwc/datafile/sysaux.256.807968999" datafile 4 switched to datafile copy "+DATADGNR/zhongwc/datafile/users.259.807968999"
Recover The Database
Once the switch is completed we apply any changes that were writen to disk during the process
RMAN> recover database; Starting recover at 21-FEB-2013 13:39:16 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/oradata/zhongwc/redo01.log archive log filename=/u01/app/oracle/oradata/zhongwc/redo01.log thread=1 sequence=25 media recovery complete, elapsed time: 00:00:06 Finished recover at 21-FEB-2013 13:39:22
Migrate the Temporary Datafiles to ASM
RMAN> run{
2> set newname for tempfile 1 to '+DATADGNR';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed temporary file 1 to +DATADGNR in control file
Move Flashback logs into flash recovery Area
SQL> alter database flashback off; Database altered.
Move RMAN Change Tracking File Into ASM
SQL> alter database disable block change tracking; alter database disable block change tracking * ERROR at line 1: ORA-19759: block change tracking is not enabled SQL> alter database enable block change tracking using file '+DATADGNR'; Database altered.
Open the Database and Move Online Logs Into ASM
SQL> alter database open resetlogs; Database altered. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/zhongwc/redo01.log /u01/app/oracle/oradata/zhongwc/redo02.log /u01/app/oracle/oradata/zhongwc/redo03.log SQL> declare 2 cursor rlc is 3 select group# grp,thread# thr,bytes/1024 bytes_k, 'NO' srl 4 from v$log 5 union 6 select group# grp,thread# thr,bytes/1024 bytes_k, 'YES' srl 7 from v$standby_log 8 order by 1; 9 stmt varchar2(2048); 10 swtstmt varchar2(1024) := 'alter system switch logfile'; 11 ckpstmt varchar2(1024) := 'alter system checkpoint global'; 12 begin 13 for rlcRec in rlc loop 14 if (rlcRec.srl = 'YES') then 15 stmt := 'alter database add standby logfile thread ' || 16 rlcRec.thr || ' ''+DATADGNR'' size ' || 17 rlcRec.bytes_k || 'K'; 18 execute immediate stmt; 19 stmt := 'alter database drop standby logfile group ' || rlcRec.grp; 20 execute immediate stmt; 21 else 22 stmt := 'alter database add logfile thread ' || 23 rlcRec.thr || ' ''+DATADGNR'' size ' || 24 rlcRec.bytes_k || 'K'; 25 execute immediate stmt; 26 begin 27 stmt := 'alter database drop logfile group ' || rlcRec.grp; 28 dbms_output.put_line(stmt); 29 execute immediate stmt; 30 exception 31 when others then 32 execute immediate swtstmt; 33 execute immediate ckpstmt; 34 execute immediate stmt; 35 end; 36 end if; 37 end loop; 38 end; 39 / PL/SQL procedure successfully completed. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- +DATADGNR/zhongwc/onlinelog/group_1.268.807976647 +DATADGNR/zhongwc/onlinelog/group_2.269.807976653 +DATADGNR/zhongwc/onlinelog/group_4.267.807976637 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATADGNR/zhongwc/datafile/system.260.807968997 +DATADGNR/zhongwc/datafile/undotbs1.258.807968999 +DATADGNR/zhongwc/datafile/sysaux.256.807968999 +DATADGNR/zhongwc/datafile/users.259.807968999 SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- +DATADGNR/zhongwc/controlfile/current.263.807975321 +DATADGNR/zhongwc/controlfile/current.264.807975323 SQL> select name from v$archived_log; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/zhongwc/redo01.log /u01/app/oracle/oradata/zhongwc/redo02.log /u01/app/oracle/oradata/zhongwc/redo03.log /u01/app/oracle/arch/1_25_807957945.dbf /u01/app/oracle/arch/1_23_807957945.dbf /u01/app/oracle/arch/1_24_807957945.dbf /u01/app/oracle/arch/1_1_807975928.dbf NAME -------------------------------------------------------------------------------- /u01/app/oracle/arch/1_2_807975928.dbf 12 rows selected. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATADGNR/zhongwc/tempfile/temp.266.807975941
Remove the File System Old Files
[oracle@vasm ~]$ cd /u01/app/oracle/oradata [oracle@vasm oradata]$ ls zhongwc/ [oracle@vasm oradata]$ rm -rf zhongwc
Remove the Old Spfile from Filesystem
[oracle@vasm oradata]$ cd $ORACLE_HOME/dbs [oracle@vasm dbs]$ ls spfile* spfile+ASM.ora spfilezhongwc.ora [oracle@vasm dbs]$ rm -rf spfilezhongwc.ora