Oracle11gR2 use RMANDuplicate to copy the database

Oracle11gR2 use RMANDuplicate to copy the database
Oracle 11gR2 uses RMAN Duplicate to copy a database

 

Preface:

After a project was just completed last week, the user requested that the RAC database be automatically backed up to another single node, and that a single node can be properly picked up. I was talking to them about using ADG, but after the description, the user thought it would be too troublesome to perform manual intervention, the data is automatically sent to this single machine. It is only a backup database. you can log on to the database to check the historical data when it is okay.

At this time, I proposed the 11g RMAN Duplicate solution, which can implement online backup of different machines online without manual intervention. After the data is transmitted over the network, the backup machine can be automatically opened.

 

RMAN Duplicate is a feature that has been available since Oracle 10 Gb. However, it has made many enhancements during the 11g period, eliminating the embarrassment of manually copying data to the slave machine for recovery in the 10G version. With the 11g Duplicate, you can create a backup database with the same but different dbids. In the 11g RMAN Duplicate, you can use the Active database duplicate and Backup-based duplicate methods. Active Database Duplicate is used at the user site, because it does not need to back up the target Database for RMAN first, and the Database can be restored through the network only when the target Database is in archive mode, after the restoration is complete, the Duplicate Database can be automatically opened. This satisfies the user's needs without human intervention. Especially for large databases, especially TB-level databases, their advantages are obvious. Separate backup is not required before recovery, which reduces the backup and copy time, it also saves backup space. Next we will perform the complete RAC to Single Instance Active Database Duplicate operation.

I. Environment Introduction

 

	HostName	DBName	Public ip
11gR2 RAC	Db01	Woo1	192.168.7.51
	Db02	Woo2	192.168.7.52
Duplicate	Standby	Woo	192.168.7.55

 

 

2. Configure Duplicate Database as follows:
2.1 construct the directory structure of the secondary database and configure system parameters of the secondary database.

2.2 install the software and create a database.

2.3 enable Archiving
2.3 create and modify pfile
2.4 create a password file for the secondary database instance
2.5 configure static listeners
2.6 Use the pfile file to start the auxiliary database to the nomount state
2.7 run the RMAN duplicate command to copy the database
2.8 create a spfile File

3. Create a directory structure for the secondary database and configure relevant parameters for the secondary database. (Simplified)

 

/Usr/sbin/groupadd-g 502 dba/usr/sbin/groupadd-g 501 oinstall/usr/sbin/useradd-u 502-g oinstall-G dba oraclemkdir-p/DBSoft /oraInventorymkdir-p/DBSoft/oracle/product/11.2.4/dbhome_1chown-R oracle: oinstall/DBSoftchmod-R 775/DBSoftecho "oracle" | passwd -- stdin oracleyum install-y setarch binutils compat-libstdc ++-33 compat-libcap1 compat-db compat-libstdc ++ compat-gcc elfutils-libelf kernel-libelf-devel gcc-c ++ glibc-common glibc-devel libaio-devel libgcc libstdc ++-devel make ksh numactl sysstat libXp unixODBC unixODBC-devel udevcat>/etc/sysctl. conf <
 
  
>/Etc/security/limits. conf <
  
   
>/Etc/profile <
   
    
>/Home/oracle/. bash_profile <
    
     
 
     
 
4. Install software and create a database
     
 
     
./runInstaller -silent -debug -force -responseFile /home/oracle/database/response/db_install.rspdbca -silent -responsefile /home/oracle/database/response/dbca.rsp
     
5. Enable Archiving
     
 
     
 
     
SQL> alter system set log_archive_dest_1=\'location=+FRA001/archive\' scope=spfile sid=\'woo1\';System altered.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination +FRA001/archiveOldest online log sequence 978Next log sequence to archive 979Current log sequence 979SQL> alter system switch logfile;alter system switch logfile;System altered.SQL> System altered.SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination +FRA001/archiveOldest online log sequence 980Next log sequence to archive 981Current log sequence 981
     

     
 
     
6. Configure static listeners
     
The RAC node listening information is as follows:
     
$ cd $ORACLE_HOME/network/adminLISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )  ) SID_LIST_LISTENER =  (SID_LIST =     (SID_DESC =        (GLOBAL_DBNAME = woo.com)        (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)        (SID_NAME = woo)      )  )ADR_BASE_LISTENER = /DBSoft/oracle
     

     
The monitoring information for a single node is as follows:
     
LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )  ) SID_LIST_LISTENER =  (SID_LIST =     (SID_DESC =        (GLOBAL_DBNAME = woo.com)        (ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)        (SID_NAME = woo)      )  )ADR_BASE_LISTENER = /DBSoft/oracle
     

     
Configure tnsname information for all nodes as follows:
     
WOO =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = woo)    )  ) DUPLICATE =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = woo)    )  )
     
6. Start the database to the nomount status
     

     
 
     
SQL> startup nomountORA-32004: obsolete or deprecated parameter (s) specified for RDBMS instanceORACLE instance started. total System Global Area 954155008 bytesFixed Size 1368632 bytesVariable Size 306187720 bytesDatabase Buffers 641728512 bytesRedo Buffers 4870144 bytesSQL> exit --------- the Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-ProductionWith the Partitioning, oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
     

     
 
     
7. connect to the target instance and secondary instance through RMAN on any node on the RAC end, and execute the duplicate command to copy the database:
     
[oracle@db01 ~]$ rman target sys/oracle@woo auxiliary sys/oracle@standbyRecovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 3 19:39:00 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: WOO (DBID=4218366793)connected to auxiliary database: WOO (not mounted)RMAN>RMAN> duplicate target database to woo from active database nofilenamecheck;Starting Duplicate Db at 03-JUN-15using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=19 device type=DISKcontents of Memory Script:{   sql clone "alter system set  db_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile";   sql clone "alter system set  db_unique_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile";   shutdown clone immediate;   startup clone force nomount   backup as copy current controlfile auxiliary format  '/DBSoft/oracle/oradata/woo/control01.ctl';   restore clone controlfile to  '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from  '/DBSoft/oracle/oradata/woo/control01.ctl';   alter clone database mount;}executing Memory Scriptsql statement: alter system set  db_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfilesql statement: alter system set  db_unique_name =  ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area     954155008 bytesFixed Size                     1368632 bytesVariable Size                306187720 bytesDatabase Buffers             641728512 bytesRedo Buffers                   4870144 bytesStarting backup at 03-JUN-15allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=43 device type=DISKchannel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150603T194000 RECID=3 STAMP=881437202channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08Finished backup at 03-JUN-15Starting restore at 03-JUN-15allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=19 device type=DISKchannel ORA_AUX_DISK_1: copied control file copyFinished restore at 03-JUN-15database mountedcontents of Memory Script:{   sql clone 'alter database flashback off';   set newname for datafile  1 to  "/DBSoft/oracle/oradata/woo/system01.dbf";   set newname for datafile  2 to  "/DBSoft/oracle/oradata/woo/sysaux01.dbf";   set newname for datafile  3 to  "/DBSoft/oracle/oradata/woo/undotbs01.dbf";   set newname for datafile  4 to  "/DBSoft/oracle/oradata/woo/users01.dbf";   backup as copy reuse   datafile  1 auxiliary format  "/DBSoft/oracle/oradata/woo/system01.dbf"   datafile  2 auxiliary format  "/DBSoft/oracle/oradata/woo/sysaux01.dbf"   datafile  3 auxiliary format  "/DBSoft/oracle/oradata/woo/undotbs01.dbf"   datafile  4 auxiliary format  "/DBSoft/oracle/oradata/woo/users01.dbf"   ;   sql 'alter system archive log current';}executing Memory Scriptsql statement: alter database flashback offexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 03-JUN-15using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbfoutput file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150603T194019channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:47channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbfoutput file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150603T194019channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbfoutput file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150603T194019channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbfoutput file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150603T194019channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 03-JUN-15sql statement: alter system archive log currentcontents of Memory Script:{   backup as copy reuse   archivelog like  "/DBArchive/archive/1_63_878860684.dbf" auxiliary format  "/DBArchive2/archive/1_63_878860684.dbf"   ;   catalog clone archivelog  "/DBArchive2/archive/1_63_878860684.dbf";   switch clone datafile all;}executing Memory ScriptStarting backup at 03-JUN-15using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=63 RECID=38 STAMP=881437711output file name=/DBArchive2/archive/1_63_878860684.dbf RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 03-JUN-15cataloged archived logarchived log file name=/DBArchive2/archive/1_63_878860684.dbf RECID=38 STAMP=881437696datafile 1 switched to datafile copyinput datafile copy RECID=3 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=4 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=5 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=6 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/users01.dbfcontents of Memory Script:{   set until scn  1338740;   recover   clone database    delete archivelog   ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 03-JUN-15using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 63 is already on disk as file /DBArchive2/archive/1_63_878860684.dbfarchived log file name=/DBArchive2/archive/1_63_878860684.dbf thread=1 sequence=63media recovery complete, elapsed time: 00:00:00Finished recover at 03-JUN-15Oracle instance startedTotal System Global Area     954155008 bytesFixed Size                     1368632 bytesVariable Size                306187720 bytesDatabase Buffers             641728512 bytesRedo Buffers                   4870144 bytescontents of Memory Script:{   sql clone "alter system set  db_name =  ''WOO'' comment= ''Reset to original value by RMAN'' scope=spfile";   sql clone "alter system reset  db_unique_name scope=spfile";   shutdown clone immediate;   startup clone nomount;}executing Memory Scriptsql statement: alter system set  db_name =  ''WOO'' comment= ''Reset to original value by RMAN'' scope=spfilesql statement: alter system reset  db_unique_name scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area     954155008 bytesFixed Size                     1368632 bytesVariable Size                306187720 bytesDatabase Buffers             641728512 bytesRedo Buffers                   4870144 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "WOO" RESETLOGS ARCHIVELOG   MAXLOGFILES     16  MAXLOGMEMBERS      3  MAXDATAFILES      100  MAXINSTANCES     8  MAXLOGHISTORY      292 LOGFILE  GROUP   1 ( '/DBSoft/oracle/oradata/woo/redo01.log' ) SIZE 50 M  REUSE,  GROUP   2 ( '/DBSoft/oracle/oradata/woo/redo02.log' ) SIZE 50 M  REUSE,  GROUP   3 ( '/DBSoft/oracle/oradata/woo/redo03.log' ) SIZE 50 M  REUSE DATAFILE  '/DBSoft/oracle/oradata/woo/system01.dbf' CHARACTER SET WE8MSWIN1252contents of Memory Script:{   set newname for tempfile  1 to  "/DBSoft/oracle/oradata/woo/temp01.dbf";   switch clone tempfile all;   catalog clone datafilecopy  "/DBSoft/oracle/oradata/woo/sysaux01.dbf",  "/DBSoft/oracle/oradata/woo/undotbs01.dbf",  "/DBSoft/oracle/oradata/woo/users01.dbf";   switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control filecataloged datafile copydatafile copy file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf RECID=1 STAMP=881437718cataloged datafile copydatafile copy file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf RECID=2 STAMP=881437718cataloged datafile copydatafile copy file name=/DBSoft/oracle/oradata/woo/users01.dbf RECID=3 STAMP=881437718datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/users01.dbfcontents of Memory Script:{   Alter clone database open resetlogs;}executing Memory Scriptdatabase openedReenabling controlfile options for auxiliary databaseExecuting: alter database flashback onFinished Duplicate Db at 03-JUN-15RMAN>
     
Now duplicate has been completed and the standby database has been up.
     

     
 
     
[oracle@db02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 3 19:53:02 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> col host_name format a15SQL> col file_name format a40SQL> set line 150SQL> select host_name,instance_name,status from gv$instance;HOST_NAME INSTANCE_NAME STATUS--------------- ---------------- ------------db02 woo OPENSQL> select tablespace_name,file_name from dba_data_files;TABLESPACE_NAME FILE_NAME------------------------------ ----------------------------------------SYSTEM /DBSoft/oracle/oradata/woo/system01.dbfSYSAUX /DBSoft/oracle/oradata/woo/sysaux01.dbfUNDOTBS1 /DBSoft/oracle/oradata/woo/undotbs01.dbfUSERS /DBSoft/oracle/oradata/woo/users01.dbf
     

     
 
     
Note: The nofilenamecheck parameter must be explained below:
     
If the file location is not the same during replication, we use the db_file_name_convert parameter to convert the file location. However, in this copy example, we use the same location. Therefore, the nofilenamecheck parameter must be added here. This parameter indicates that the copy operation does not have to confirm that the file name is different before performing the restore operation. If the nofilenamecheck parameter is not specified, rman will give the following error:
     
RMAN-05001: auxiliary filename/DBSoft/oracle/oradata/woo/users01.dbf conflicts with a file used by the target database