RMAN Duplicate

Data instantiation can be done by creating a target database through the duplicate command of RMAN.This method is more suitable for data instantiation when almost every schema of the source database is to be replicated, and the source and target databases are both Oracle. Once the target instance is ready, select a particular SCN to start replication from and duplicate the source database up to that SCN. You can then run the setup wizard of Dbvisit Replicate to configure replication. 

Example

Following is a step-by-step illustrative example of using RMAN duplicate command for data instantiation before configuring Dbvisit Replicate. The following environment is used in this example:

Server/DatabaseName
Source databasereptest1
Source serverdbvldemo101 (Linux)
Standby/Target databasereptest2
Target serverdbvldemo102(Linux)
TNS alias for source database   reptest1
TNS alias for target databasereptest2
Oracle11.2.0.3.0
ORACLE_BASE/u01/app/oracle
ORACLE_HOME$ORACLE_BASE/product/11.2.0/dbhome_1
TNS_ADMIN$ORACLE_HOME/network/admin

 

1. Take the backup the source database using RMAN. 

RMAN> connect target /
connected to target database: REPTEST1 (DBID=290605816)
RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/rman/REPTEST1_%U';
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/rman/reptest1_%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/rman/REPTEST1_%U';
new RMAN configuration parameters are successfully stored
RMAN> backup database;
Starting backup at 25/06/2013:12:34:44
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/reptest1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/reptest1/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/reptest1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/reptest1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25/06/2013:12:34:44
channel ORA_DISK_1: finished piece 1 at 25/06/2013:12:35:29
piece handle=/backups/rman/REPTEST1_09od2qr4_1_1 tag=TAG20130625T123444 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 25/06/2013:12:35:31
channel ORA_DISK_1: finished piece 1 at 25/06/2013:12:35:32
piece handle=/backups/rman/REPTEST1_0aod2qsi_1_1 tag=TAG20130625T123444 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25/06/2013:12:35:32
RMAN> exit

 

2. Create admin directory reptest2 in $ORACLE_HOME/admin and audit directory adump under reptest2 on target server

oracle@dbvldemo102[/u01/app/oracle/admin]: mkdir reptest2
oracle@dbvldemo102[/u01/app/oracle/admin]: cd reptest2
oracle@dbvldemo102[/u01/app/oracle/admin/reptest2]: mkdir adump

 

3. Edit $TNS_ADMIN/tnsnames.ora and add source and target database on target server.

 

4. Register target database in $TNS_ADMIN/listener.ora and restart the listener on target server.

 

5. Create pfile from spfile and edit it according to the target database environment. Make sure all the paths specified in spfile exists on target server.

reptest2.__db_cache_size=381681664
reptest2.__java_pool_size=4194304
reptest2.__large_pool_size=4194304
reptest2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
reptest2.__pga_aggregate_target=209715200
reptest2.__sga_target=629145600
reptest2.__shared_io_pool_size=0
reptest2.__shared_pool_size=230686720
reptest2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/reptest2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/reptest2/control01.ctl','/u01/app/oracle/fast_recovery_area/reptest2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='reptest2'
*.db_file_name_convert=('/u01/app/oracle/oradata/reptest1', '/u01/app/oracle/oradata/reptest2','/u01/app/oracle/fast_recovery_area/reptest1','/u01/app/oracle/fast_recovery_area/reptest2')
*.log_file_name_convert=('/u01/app/oracle/oradata/reptest1', '/u01/app/oracle/oradata/reptest2','/u01/app/oracle/fast_recovery_area/REPTEST1','/u01/app/oracle/fast_recovery_area/REPTEST2')
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=reptest2XDB)'
*.local_listener='REPTEST2'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1

 

6. Create a password file orapwdreptest2 in $ORACLE_HOME/dbs.

$cd $ORACLE_HOME/dbs
$orapwd password=***** file=orapwreptest2

 

7. Add the target database in /etc/oratab and start the target database in nomount mode. 

oracle@dbvldemo102[/u01/app/oracle/admin/reptest2]: sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 24 23:29:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 239076696 bytes
Database Buffers 381681664 bytes
Redo Buffers 3338240 bytes
SQL>

 

8. Once confirm that no transactions are pending on the source database and all the tables to be replicated are idle, get the current SCN and generate a log switch.

Locking the tables is not necessary if you ensure in other ways (like application downtime) that no transactions are pending on the tables as of the SCN because pending and non-committed transactions would be lost.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1007088
SQL> alter system switch logfile;
System altered.

 

 

9. Backup archivelogs, these backups are used to restore target database up to the SCN 1007088.

RMAN> backup archivelog all;
Starting backup at 25/06/2013:12:40:03
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=819030972
input archived log thread=1 sequence=3 RECID=2 STAMP=819030994
input archived log thread=1 sequence=4 RECID=3 STAMP=819031186
input archived log thread=1 sequence=5 RECID=4 STAMP=819031187
input archived log thread=1 sequence=6 RECID=5 STAMP=819031204
channel ORA_DISK_1: starting piece 1 at 25/06/2013:12:40:04
channel ORA_DISK_1: finished piece 1 at 25/06/2013:12:40:07
piece handle=/backups/rman/REPTEST1_0cod2r54_1_1 tag=TAG20130625T124004 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25/06/2013:12:40:07
RMAN> exit
Recovery Manager complete.

 

10. Copy these backupsets to the same location on target server. 

oracle@dbvldemo101[/backups/rman]: scp -P 10022 * oracle@dbvldemo102:/backups/rman/
REPTEST1_07od2qo5_1_1 100% 258MB 36.8MB/s 00:07 
REPTEST1_09od2qr4_1_1 100% 258MB 36.8MB/s 00:07 
REPTEST1_0aod2qsi_1_1 100% 1072KB 1.1MB/s 00:00 
REPTEST1_0bod2quj_1_1 100% 8782KB 8.6MB/s 00:00 
REPTEST1_0cod2r54_1_1 100% 8841KB 8.6MB/s 00:00

 

11.  Start RMAN on target server and execute duplicate target database command.

oracle@dbvldemo102[/home/oracle/]: rman target sys/password@reptest1 auxiliary sys/password@reptest2
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jun 25 12:52:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: REPTEST1 (DBID=290605816)
connected to auxiliary database: REPTEST2 (not mounted)
RMAN> duplicate target database to reptest2 until scn 1007088;
duplicate target database to reptest2 until scn 1007088 command will restore,open the database and does resetlogs too.
The database is ready to be used after successful execution of this command.


12. Now, you can configure Dbvisit Replicate by running setup wizard normally on the source server. Select 'resetlogs' as data instantiation method in step 2 of setup wizard.

Step 2 - Replication pairs
========================================
The second step is to set source and targets for each replication pair. This is usually just choosing the first database as
source and the second one as target, but many more configurations are possible.
Let's configure the replication pair, selecting source and target.
Following databases are described:
1: reptest1 (Oracle) 
2: reptest2 (Oracle) 
Select source database: [1] 
Select target database: [2] 
Will be DDL replication enabled? (If YES, the script will grant more privileges to the Dbvisit Replicate users and enable database-wide supplemental logging): [yes] 
Use fetcher to offload the mining to a different server? (yes/no) [no] 
Would you like to encrypt the data across the network (yes or no) [no]
Would you like to compress the data across the network (yes or no) [no]
How long do you want to set the network timeouts. Recommended range between 60-300 seconds [60]
Lock and copy the data initially one-by-one or at a single SCN? (one-by-one/single-scn/ddl-only/resetlogs) [single-scn] resetlogs 
What data copy script to create? (dp_networklink/dp_exp/exp/ddl_file/ddl_run/none) [none]
Following replication pairs are now configured:
1: reptest1 (Oracle) ==> reptest2 (Oracle), DDL: yes, fetcher: no, process suffix: (no suffix),
compression: no, encryption: no, network timeout: 60, prepare type: resetlogs, data load: none
Enter number of replication pair to modify it, or "add", or "done": [done]

 

13. Run the reptest1-all.sh script  followed by starting MINE and APPLY processes.

  • This will require the MINE process to go back in time to start mining from the SCN starting point. You will need archivelogs available from the SCN starting point for MINE to process all the changes.
  • When target database creation takes a long time the MINE process has to do a lot of catchup work before it can actually start to replicate the current changes.