Page Comparison

Data instantiation can be done by creating a target database using the RMAN restore and recover command.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 database databases are both Oracle. Once the target instance is ready, select a particular SCN to start replication from and restore and recover the target database up to that SCN. You can then run the setup wizard of Dbvisit Replicate to configure replication.

...

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

...

Note
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.

panel

Section

Column

width	95

No Format

bgColor	CCC

SQL> select current_scn from v$database;


CURRENT_SCN


-----------


SQL> alter system switch logfile;


System altered.

2. Take the backup of the source database using RMAN.

Section

Column

width	95

Panel

No Format

bgColor	CCC

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 storedRMAN> 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';


RMAN> backup database;


Starting backup at 01/07/2013:12:47:19


allocated channel: ORA_DISK_1


channel ORA_DISK_1: SID=31 device type=DISK


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 01/07/2013:12:47:19


channel ORA_DISK_1: finished piece 1 at 01/07/2013:12:48:04


piece handle=/backups/rman/REPTEST1_02odla6n_1_1 tag=TAG20130701T124719 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 01/07/2013:12:48:06


channel ORA_DISK_1: finished piece 1 at 01/07/2013:12:48:07


piece handle=/backups/rman/REPTEST1_03odla85_1_1 tag=TAG20130701T124719 comment=NONE


channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01


Finished backup at 01/07/2013:12:48:07

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

Section

Column

width	95

Panel

No Format

bgColor	CCC

RMAN> backup archivelog all;


Starting backup at 01/07/2013:14:02:41


current log archived


using target database control file instead of recovery catalog


allocated channel: ORA_DISK_1


channel ORA_DISK_1: SID=41 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=819637105


input archived log thread=1 sequence=3 RECID=2 STAMP=819637137


input archived log thread=1 sequence=4 RECID=3 STAMP=819640946


input archived log thread=1 sequence=5 RECID=4 STAMP=819640961


channel ORA_DISK_1: starting piece 1 at 01/07/2013:14:02:42


channel ORA_DISK_1: finished piece 1 at 01/07/2013:14:02:45


piece handle=/backups/rman/REPTEST1_05odlek2_1_1 tag=TAG20130701T140242 comment=NONE


channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03


Finished backup at 01/07/2013:14:02:45

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

Section

Column

width	95

Panel

No Format

bgColor	CCC

oracle@dbvldemo101[/backups/rman]: scp -P 10022 * oracle@dbvldemo102:/backups/rman/


REPTEST1_07od2qo5_1_1 100% 258MB 36.8MB/s 00:

07

07 
REPTEST1_09od2qr4_1_1 100% 258MB 36.8MB/s 00:

07

07 
REPTEST1_0aod2qsi_1_1 100% 1072KB 1.1MB/s 00:

00

00 
REPTEST1_0bod2quj_1_1 100% 8782KB 8.6MB/s 00:

00

00 
REPTEST1_0cod2r54_1_1 100% 8841KB 8.6MB/s 00:00

On the Target Server

5. Create admin directory reptest1 in $ORACLE_HOME/admin and audit directory adump under reptest1 on target server.

Section

Column

width	95

Panel

No Format

bgColor	CCC

oracle@dbvldemo102[/u01/app/oracle/admin]: mkdir reptest1


oracle@dbvldemo102[/u01/app/oracle/admin]: cd reptest1


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

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

...

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

8. 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.

Sectionnoformat

Column

width	95

Panel

bgColor

bgColor	CCC

reptest1.__db_cache_size=432013312


reptest1.__java_pool_size=4194304


reptest1.__large_pool_size=4194304


reptest1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment


reptest1.__pga_aggregate_target=209715200


reptest1.__sga_target=620756992


reptest1.__shared_io_pool_size=0


reptest1.__shared_pool_size=171966464


reptest1.__streams_pool_size=0


*.audit_file_dest='/u01/app/oracle/admin/reptest1/adump'


*.audit_trail='db'


*.compatible='11.2.0.0.0'


*.control_files='/u01/app/oracle/oradata/reptest1/control01.ctl','/u01/app/oracle/fast_recovery_area/reptest1/control02.ctl'


*.db_block_size=8192


*.db_domain=''


*.db_name='reptest1'


*.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=reptest1XDB)'


*.local_listener='REPTEST1_STD'


*.log_archive_format='%t_%s_%r.dbf'


*.open_cursors=300


*.pga_aggregate_target=206569472


*.processes=150


*.remote_login_passwordfile='EXCLUSIVE'


*.sga_target=619708416


*.undo_tablespace='UNDOTBS1'

9. Create a password file orapwdreptest1 in $ORACLE_HOME/dbs.

Sectionnoformat

Column

width	95

Panel

bgColor

bgColor	CCC

$cd $ORACLE_HOME/dbs


$orapwd password=***** file=orapwreptest1

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

Section

Column

width	95

Panel

No Format

bgColor	CCC

oracle@dbvldemo102[/u01/app/oracle/admin/reptest1]: 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>

SQL>

11. Restore the control file and mount the database.

Section

Column

width	95

Panel

No Format

bgColor	CCC

RMAN> restore controlfile from '/backups/rman/REPTEST1_03odla85_1_1';


Starting restore at 01/07/2013:14:26:18


using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file


channel ORA_DISK_1: restore complete, elapsed time: 00:00:01


output file name=/u01/app/oracle/oradata/reptest1/control01.ctl


output file name=/u01/app/oracle/fast_recovery_area/reptest1/control02.ctl


Finished restore at 01/07/2013:14:26:19


RMAN> alter database mount;


database mounted


released channel: ORA_DISK_1

12. Register all the copied backup piecies in the controlfile.

Section

Column

width	95

Panel

No Format

bgColor	CCC

RMAN> catalog backuppiece '/backups/rman/REPTEST1_02odla6n_1_1';


using target database control file instead of recovery catalog


cataloged backup piece


backup piece handle=/backups/rman/REPTEST1_02odla6n_1_1 RECID=2 STAMP=819642773


RMAN> catalog backuppiece '/backups/rman/REPTEST1_03odla85_1_1';


cataloged backup piece


backup piece handle=/backups/rman/REPTEST1_03odla85_1_1 RECID=3 STAMP=819642787


RMAN> catalog backuppiece '/backups/rman/REPTEST1_04odlasi_1_1';


cataloged backup piece


backup piece handle=/backups/rman/REPTEST1_04odlasi_1_1 RECID=4 STAMP=819642797


RMAN> catalog backuppiece '/backups/rman/REPTEST1_05odlek2_1_1';


cataloged backup piece


backup piece handle=/backups/rman/REPTEST1_05odlek2_1_1 RECID=5 STAMP=819642823

13. Restore and recover the target database up to SCN 1009189.

Section

Column

width	95

Panel

No Format

bgColor	CCC

RMAN> RUN

 SET UNTIL SCN 1009189;

 RESTORE DATABASE;


 RECOVER DATABASE;


executing command: SET until clause


Starting restore at 01/07/2013:14:33:54


allocated channel: ORA_DISK_1


channel ORA_DISK_1: SID=18 device type=DISK


skipping datafile 1; already restored to file /u01/app/oracle/oradata/reptest1/system01.dbf


skipping datafile 2; already restored to file /u01/app/oracle/oradata/reptest1/sysaux01.dbf


skipping datafile 3; already restored to file /u01/app/oracle/oradata/reptest1/undotbs01.dbf


skipping datafile 4; already restored to file /u01/app/oracle/oradata/reptest1/users01.dbf


restore not done; all files read only, offline, or already restored


Finished restore at 01/07/2013:14:33:55


Starting recover at 01/07/2013:14:33:55


using channel ORA_DISK_1


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination


channel ORA_DISK_1: restoring archived log


archived log thread=1 sequence=2


channel ORA_DISK_1: restoring archived log


archived log thread=1 sequence=3


channel ORA_DISK_1: restoring archived log


archived log thread=1 sequence=4


channel ORA_DISK_1: reading from backup piece /backups/rman/REPTEST1_05odlek2_1_1


channel ORA_DISK_1: piece handle=/backups/rman/REPTEST1_05odlek2_1_1 tag=TAG20130701T140242


channel ORA_DISK_1: restored backup piece 1


channel ORA_DISK_1: restore complete, elapsed time: 00:00:03


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_2_8x1tnmlw_.arc thread=1 sequence=2


channel default: deleting archived log(s)


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_2_8x1tnmlw_.arc RECID=3 STAMP=819642836


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_3_8x1tnmph_.arc thread=1 sequence=3


channel default: deleting archived log(s)


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_3_8x1tnmph_.arc RECID=1 STAMP=819642835


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_4_8x1tnmns_.arc thread=1 sequence=4


channel default: deleting archived log(s)


archived log file name=/u01/app/oracle/fast_recovery_area/REPTEST1/archivelog/2013_07_01/o1_mf_1_4_8x1tnmns_.arc RECID=2 STAMP=819642835


media recovery complete, elapsed time: 00:00:01


Finished recover at 01/07/2013:14:33:59

14. Open the database and reset the online logs.

Section

Column

width	95

Panel

No Format

bgColor	CCC

SQL> ALTER DATABASE OPEN RESETLOGS;


Database altered.

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.

Section

Column

width	95

Panel

No Format

bgColor	CCC

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]

(NETWORK_QUALITY) - Please specify your network type (LAN or WAN). Autoconfigures timeouts, use of compression etc. [LAN]


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:

LAN

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.

Note

This will require the Mine 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 MINE to process all the changes.

When target database creation takes a long time the Mine MINE process has to do a lot of catchup work before it can actually start to replicate the current changes.

...

Versions Compared

Old Version 5

New Version Current

Key