Duplicating When Datafiles Use
Inconsistent Paths
CONNECT
TARGET /;
CONNECT
CATALOG rman/cat@catdb;
CONNECT
AUXILIARY SYS/oracle@dupdb;
# note
that a RUN command is necessary because you can only execute SET NEWNAME
#
within a RUN command
RUN
{
# The DUPLICATE command uses an automatic sbt
channel.
# Because the target datafiles are spread
across multiple directories,
# run SET NEWNAME rather than
DB_FILE_NAME_CONVERT
SET NEWNAME FOR DATAFILE 1 TO
'/oradata1/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO
'/oradata2/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO
'/oradata3/cwmlite01.dbf';
SET NEWNAME FOR DATAFILE 4 TO
'/oradata4/drsys01';
SET NEWNAME FOR DATAFILE 5 TO
'/oradata5/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO
'/oradata6/indx01.dbf';
# Do not set a newname for datafile 7,
because it is in the tools tablespace,
# and you are excluding tools from the
duplicate database.
SET NEWNAME FOR DATAFILE 8 TO
'/oradata7/users01.dbf';
DUPLICATE TARGET DATABASE TO dupdb
SKIP TABLESPACE tools
LOGFILE
GROUP 1 ('/duplogs/redo01a.log',
'/duplogs/redo01b.log') SIZE
200K REUSE,
GROUP 2 ('/duplogs/redo02a.log',
'/duplogs/redo02b.log') SIZE
200K REUSE;
}
RMAN DUPLICATE DATABASE
From File System to ASM: Example
This duplication example assumes the following:
- You
are using recovery catalog database catdb.
- The
target database trgt is on host1 and the database files are stored in a
non-ASM file system.
- You
want to duplicate the target to database dupdb on remote host host2.
- host2
has diskgroup +DISK1.
- You
want to store the datafiles for dupdb to +DISK1.
- You
want to store two controlfiles in +DISK1.
- The
backups and archivelogs created by host1 are accessible by host2.
Create an initialization parameter for auxiliary instance by copying the
target database initialization parameter file. Change the parameters as
follows:
- Set DB_NAME to the new
database name dupdb
- Set CONTROL_FILES to store two
copies of the control file in +DISK1
- Make
sure DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are not set
- Set
any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK1
For example:
DB_NAME=dupdb
CONTROL_FILES=+DISK1,+DISK1
Create an SPFILE from the parameter file, and start the auxiliary
instance:
SQL>
CONNECT AUXILIARY SYS/oracle@dupdb;
SQL>
CREATE SPFILE FROM PFILE=auxiliary instance pfile;
SQL>
STARTUP NOMOUNT;
Then run the following RMAN commands to duplicate the database:
RMAN>
CONNECT TARGET /;
RMAN>
CONNECT CATALOG rman/cat@catdb;
RMAN>
CONNECT AUXILIARY SYS/oracle@dupdb;
RMAN>
DUPLICATE TARGET DATABASE TO dupdb
LOGFILE GROUP 1 ('+DISK1','+DISK1') SIZE
200K,
GROUP 2 ('+DISK1','+DISK1') SIZE
200K;
RMAN DUPLICATE DATABASE From ASM to
ASM: Example
This duplication example assumes the following:
- You
are using recovery catalog database catdb
- The
target database trgt is on host1 and contains ASM datafiles and online
logs in diskgroup +DISK1
- You
want to duplicate the target to database dupdb on remote host host2.
- host2
has diskgroup +DISK2
- You
want to store the datafiles for dupdb to +DISK2
- You
want to store two controlfiles in +DISK2
- The
backups and archivelogs created by host1 are accessible by host2
Create an initialization parameter for auxiliary instance by copying the
target database initialization parameter file. Change the parameters as
follows:
- Set DB_NAME to the new
database name dupdb
- Set CONTROL_FILES to store two
copies of the control file in +DISK2
- Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the
datafile and online log file names from +DISK1 to +DISK2
- Set
any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK2
For example:
DB_NAME=dupdb
CONTROL_FILES=+DISK2,+DISK2
DB_FILE_NAME_CONVERT=+DISK1,+DISK2
LOG_FILE_NAME_CONVERT=+DISK1,DISK2
Create an SPFILE from the parameter file, and start the auxiliary
instance:
SQL>
CONNECT AUXILIARY SYS/oracle@dupdb;
SQL>
CREATE SPFILE FROM PFILE=auxiliary instance pfile;
SQL>
STARTUP NOMOUNT;
Then run the following RMAN commands to duplicate the database:
RMAN>
CONNECT TARGET /;
RMAN>
CONNECT CATALOG rman/cat@catdb;
RMAN>
CONNECT AUXILIARY SYS/oracle@dupdb;
RMAN>
DUPLICATE TARGET DATABASE TO dupdb;
Steps
To Migrate a Database From Non-ASM to ASM
You can use these steps to migrate a NON-ASM database to ASM database in Oracle 11gR2.
Prerequisite - ASM instance should be created and up and running. Please refer my previous article to create a ASM instance
1) Check the database version and status
SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;
INSTANCE_NAME VERSION DATABASE_STATUS
---------------- ----------------- -----------------
learndb 11.2.0.3.0 ACTIVE
2) We should be able to check the ASM disk available from the instance which we created earlier
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- ----------
DISK1 NORMAL 4777 ORCL:DISK1
Migrating the SPFILE from Non-asm to ASM
1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk
RMAN>BACKUP AS BACKUPSET SPFILE;
2) Now shutdown the database and start it in Mount stage
SQL> Shutdown immediate
SQL> STARTUP MOUNT;
3) Now restore the spfile from the backup to ASM disk as below
RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';
4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/11.2.0/dbh
ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance
SQL> shutdown immediate
6) Delete "spfile
vi initlearndb.ora
SPFILE='+DATA/spfilelearndb.ora'
SQL> Startup ( first it will search for spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilelearndb.ora
Migrating Control files from Non-ASM to ASM
1) These are the current control files in non-asm
SQL>show parameter contol_files
control_files string /u01/data/learndb/control01.ctl,
/u01/data/learndb/control02.ctl
2) Start the instance in nomount state
SQL> startup nomount
3) You can move these control files to ASM using RMAN
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (not mounted)
RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';
4) You can check whether the control file are created
ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'
+DATA/LEARNDB/CONTROLFILE/current.256.787200593
5) Update the spfile with new control file location
SQL> alter system set control_files='+DATA/LEARNDB/CONTROLFILE/current.256.787200593' scope=spfile;
6) Now shutdown the instance and startup in mount stage and check for the control file location
SQL> shutdown immediate
SQL> startup mount
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/learndb/controlfile/curr
ent.256.787200593
Migrating the Datafiles from Non-ASM to ASM
1) Keep the database is in mount stage
2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (DBID=3704858930, not open)
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
3) Switch the database to the copy created using the following RMAN command
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"
4) Migrate the tempfile to ASM using RMAN
RMAN> run
2> {
3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
4> switch tempfile all;
5> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
renamed tempfile 1 to +DATA in control file
5) Now open the Database
RMAN> ALTER DATABASE OPEN;
database opened
6) You can now check the datafiles created in ASM
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/learndb/datafile/users.261.787201725
+DATA/learndb/datafile/undotbs1.259.787201713
+DATA/learndb/datafile/sysaux.257.787201553
+DATA/learndb/datafile/system.258.787201633
Migrating the Redo log files from Non-ASM to ASM
1) Identify the currently available redo log files using the following command
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER STATUS
---------- --------- -----------
3 /u01/data/learndb/redo03.log INACTIVE
2 /u01/data/learndb/redo02.log CURRENT
1 /u01/data/learndb/redo01.log INACTIVE
2) Add the new logfiles to ASM using following command
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;
3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';
4) You can check the logfiles created now in ASM
GROUP# MEMBER STATUS
---------- --------- -----------
1 +DATA/learndb/onlinelog/group_1.264.787205393 CURRENT
2 +DATA/learndb/onlinelog/group_2.265.787205405 INACTIVE
3 +DATA/learndb/onlinelog/group_3.266.787205417 INACTIVE