We will see how to create duplicate database from available rman backup.o
a) Active Database Duplication method - from Primary
b) Duplicate database from RMAN Backup
a) Following Active Database Duplication method - from Primary
Introduction:
Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions. First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.
Purpose of Database Duplication:
A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:
RMAN supports two basic types of duplication: active database duplication and backup-based duplication.
A connection to both is required for active database duplication.
Figure-1 shows the decision tree for the two duplication techniques.
Active Database Duplication:
In active database duplication, RMAN connects as TARGET to the source database instance and as AUXILIARY to the auxiliary instance. RMAN copies the live source database over the network to the auxiliary instance, thereby creating the duplicate database. No backups of the source database are required. Figure -2 illustrates active database duplication.
Figure-2 Active Database Duplication
Steps of Database Duplication (Active method)
A) Pre-Configuration Steps:
1) Find Source and Target database
Assume host name of Source database is "DB1" and target database is "DB2". Both Severs are stand-alone type.
Assume source database name is PRODDB and target database name is REPDB.
2) Find connectivity between both hosts
use ping with IP from both server. Ping status should OK.
3) Verify OS and database versions
Assume OS version RHEL 6.3 and Oracle Software version 11.2.0.3
4) Verify Source database database size and more than space should available in target database.
5) Check Archive log is enabled in source database.
B) Configuration steps:
-- in proposed duplicate database side
1) configure listener.
Check listener should up and running in target side.
2) configure tnsname.ora for itself and source also
Check tnsping from both databses. Both side status should OK.
3) configure initREPDB.ora ( Duplicate Specific parameters)
db_name='REPDB'
DB_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'
LOG_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'
See sample pfile:
Note: Remove db_unique_name option, if your target database listener is blocking.
4) create a password file in source database and move it to target database side and rename it database SID name.
e.g.,
-- Source side
DB1] $ cd $ORACLE_HOME/dbs
DB1] $ orapwd force=y file=orapwPRODDB password=system
$ scp orapwPRODDB oracle@DB2:/$ORACLE_HOME/dbs
-- Traget side
DB2] $ cd $ORACLE_HOME/dbs
$ mv orapwPRODDB orapwREPDB
Now check connectivity using sqlplus in source database using password. It should connect
DB1] $ sqlplus /nolog
sql> connect sys/system@REPDB as sysdba
connected.
C) Create Duplicate database:
1) When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.
$ export ORACLE_SID=$PROD
$ rman target=sys/system auxiliary=sys/system@UAT
OR
$ rman target /
rman> connect auxiliary sys/system@REPDB;
2) Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.
RMAN>
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database to REPDB from active database;
}
2) Duplicate database from RMAN Backup
Note: Automatically database will be created and open it r/w mode also.
====================================================================
b) Duplicate database from RMAN Backup
You can create duplicate database from backup. If source and target directory structure for C|R|D files are same then most probably you may not face any issues. But if directory structure are different, then you need to follow "SET NEWNAME FOR DATAFILE xx TO" method.
a). add following in pfile ( only required if you are not using OMF and source & target file structure different)
*.db_file_name_convert='/u02/oracle/oradata','/u03/oracle/oradata'
*.log_file_name_convert ='/u02/oracle/oradata','/u03/oracle/oradata'
b). create directory structures for C|R|D files and other audit location
Note: Run the backup in source like similar as shown in below
connect target `/'
run
{
allocate channel chd1 device type disk;
allocate channel chd2 device type disk;
backup as compressed backupset incremental level 0 database plus archivelog;
release channel chd1;
release channel chd2;
}
Note: Auto backup on must be configured in the above case and you need to copy those backup files to target. Here all backups will be available in db_recovery_dest location.
OR
You can use below script.
e.g.,
run
{
sql 'alter system archive log current';
backup device type disk as backupset current controlfile format '/u03/backup/proddb_full_cf.bkp' tag 'proddb_full_cf';
backup device type disk incremental level 0 database format '/u03/backup/proddb_full_db_%U.bkp' tag 'proddb_full_db';
sql 'alter system archive log current';
backup device type disk archivelog all format '/u03/backup/proddb_full_al_%U.bkp' not backed up 2 times tag 'proddb_full_arc';
}
c) Start the database in nomount stage.
$ sqlplus / as sysdba
SQL> startup nomount;
ORACLE instance started.
d) Backup files with auto Bkp
$ ls -ltr
-rw-r-----. 1 oracle oinstall 220672 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrwt_.bkp
-rw-r-----. 1 oracle oinstall 4608 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrvy_.bkp
-rw-r-----. 1 oracle oinstall 12708352 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrsp_.bkp
-rw-r-----. 1 oracle oinstall 2946310144 Mar 18 22:47 o1_mf_nnndf_TAG20170318T222452_ddtslxoq_.bkp
-rw-r-----. 1 oracle oinstall 2602360832 Mar 18 22:48 o1_mf_nnndf_TAG20170318T222452_ddtsly31_.bkp
-rw-r-----. 1 oracle oinstall 4789755904 Mar 18 22:55 o1_mf_nnndf_TAG20170318T222452_ddtslyjw_.bkp
-rw-r-----. 1 oracle oinstall 4372389888 Mar 18 22:56 o1_mf_nnndf_TAG20170318T222452_ddtslz1g_.bkp
-rw-r-----. 1 oracle oinstall 1840640 Mar 18 22:56 o1_mf_annnn_TAG20170318T225630_ddtvg74l_.bkp
-rw-r-----. 1 oracle oinstall 8192 Mar 18 23:06 o1_mf_1_4700_ddtvzpol_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4699_ddtvzkw1_.arc
-rw-r-----. 1 oracle oinstall 627712 Mar 18 23:06 o1_mf_1_4698_ddtvzhld_.arc
-rw-r-----. 1 oracle oinstall 3643904 Mar 18 23:06 o1_mf_1_4697_ddtvg6j4_.arc
-rw-r-----. 1 oracle oinstall 218624 Mar 18 23:06 o1_mf_1_4696_ddtslr78_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4695_ddts5s8n_.arc
-rw-r-----. 1 oracle oinstall 2560 Mar 18 23:06 o1_mf_1_4694_ddts5pqj_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4693_ddts5md7_.arc
-rw-r-----. 1 oracle oinstall 39057408 Mar 18 23:06 o1_mf_1_4692_ddts5kt4_.arc
-rw-r-----. 1 oracle oinstall 13631488 Mar 18 23:07 o1_mf_s_938991392_ddtvg8yn_.bkp
e) Change ownership and permissions of Backup pieces.
$ id
uid=502(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),506(asmdba)
$
#chown -R oracle:oinstall 2017_03_18
#chmod -R 775 2017_03_18
f) Create Duplicate database.
$rman auxiliary / log = rmandup_2017_03_18.log
run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
DUPLICATE DATABASE "PROD" DBID 3590353289 TO "UAT" BACKUP LOCATION '/u04/2017_03_18' NOFILENAMECHECK;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
OR
you can script like below to call with nohup option:
vi uat_rman_duplicate_script.rman
run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
DUPLICATE DATABASE "prod" DBID 3590353289 TO "uat" BACKUP LOCATION '/u04/2017_03_18' NOFILENAMECHECK;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
$ env | grep ORA
ORACLE_UNQNAME=uat
ORACLE_SID=uat
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
$ nohup rman auxiliary / @uat_rman_duplicate_script.rman log=uat_rman_duplicate_2017_03_18.log &
g) Check the database status.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
UAT READ WRITE PRIMARY
SQL>
When directory structure NOT same for C/R/D files:
You can follow the same steps as given above, but step-5 to be modified and you can use below method. Omit db_file_name_convert and log_file_name_convert from parameter file.
rman> RUN
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO '/u02/oracle/uatdata/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u02/oracle/uatdata/sysaux01.dbf';
...
SET NEWNAME FOR DATAFILE 61 TO '/u02/oracle/uatdata/txn_data01.dbf';
SET NEWNAME FOR DATAFILE 62 TO '/u02/oracle/uatdata/undo_01.dbf';
DUPLICATE DATABASE "PROD" DBID 3655036336 TO "UAT" BACKUP LOCATION '/prod/FRA/PROD/backupset/2017_04_13' NOFILENAMECHECK;
LOGFILE
GROUP 1 ('/u02/oracle/uatdata/redo01.log') SIZE 500M REUSE,
GROUP 2 ('/u02/oracle/uatdata/redo02.log') SIZE 500M REUSE,
GROUP 3 ('/u02/oracle/uatdata/redo03.log') SIZE 500M REUSE,
GROUP 4 ('/u02/oracle/uatdata/redo04.log') SIZE 500M REUSE,
release channel aux1;
release channel aux2;
}
Note: If you are Creating Duplicate database from TAPE backups, then you can follow below method in rman script. But you should ensure where your TAPE agent is runnig, give the same path with required inforrmation:
e.g.,
run {
set dbid xxxxxxx;
allocate auxiliary channel ch1 device type sbt parms="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
duplicate database 'EXMPRD' to 'EXMUAT' NOFILENAMECHECK;
}
OR
## To retsore up to specific period.
run {
set dbid xxxxxxx;
set until time "TO_DATE('2017-03-18 07:00:00', 'YYYY-MM-DD HH24:MI:SS')";
allocate auxiliary channel ch1 device type sbt parms="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
duplicate database 'EXMPRD' to 'EXMUAT' NOFILENAMECHECK;
}
Issues and Troubleshooting:
-- Issue-1:
DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :fhdbi = 32767
released channel: stby1
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/21/2014 19:52:49
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution:1: like this
pfile :
*.db_name=prod1
*.db_unique_name=prod2
-- Issue-2:
RMAN-04006: ORA-28547:
RMAN-04006: error from auxiliary database: ORA-28547: connection to server failed, probable Oracle Net admin error
Solution:2:
1) re-create password file for target / duplicate database properly
2) recheck tnsnames.ora entry
3) Check the target database/ duplcate database listener status
4) While connecting auxiliary database, check auxiliary is connecting or not.
-- Issue-3:
Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2015 17:37:34
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01102: cannot mount database in EXCLUSIVE mode
OR
Solution-1:
If any oracle process is running with your duplicate database name, then kill them.
e.g.
$ ps -ef|grep $ORACLE_SID
$ kill -9 <pid>
Solution-2:
Add below parameter in standby parameter.
*.remote_login_passwordfile='EXCLUSIVE'
Solution-3:
Comment below entry in standby parameter file if you have kept/ add.
.db_unique_name='primary_dbname'
-- Issue - 4:
Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 16:54:33
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ch1 channel at 04/29/2015 16:54:24
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
I found below message like this and terminated the session:
.....
.....
.....
datafile 71 switched to datafile copy
input datafile copy RECID=70 STAMP=878329989 file name=/u01/ORADATA/UAT/HR02.dbf
datafile 72 switched to datafile copy
input datafile copy RECID=71 STAMP=878329989 file name=/u01/ORADATA/UAT/SUPPORT_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 04/29/2015 20:33:48
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86663)
ORA-01110: data file 1: '/u01/ORADATA/UAT/system01.dbf'
Process ID: 25656
Session ID: 805 Serial number: 5
$
The above message is clearly telling there is issue with disk and corrupted block in source database.
Action to be taken:
1) Verify source database block corruption issues.
2) Validate some backups for corrupted blocks in source and then transfer to target database and again validate in target database side and restore.
3) The last option is to take a full export dump and restore it.
Note: Try to replace the exiting storage.
Thanks .. This document will help you. Cheers!!!
a) Active Database Duplication method - from Primary
b) Duplicate database from RMAN Backup
a) Following Active Database Duplication method - from Primary
Introduction:
Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions. First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.
Purpose of Database Duplication:
A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:
- Test backup and recovery procedures
- Test an upgrade to a new release of Oracle Database
- Test the effect of applications on database performance
- Create a standby database
- Generate reports
RMAN supports two basic types of duplication: active database duplication and backup-based duplication.
A connection to both is required for active database duplication.
Figure-1 shows the decision tree for the two duplication techniques.
Active Database Duplication:
In active database duplication, RMAN connects as TARGET to the source database instance and as AUXILIARY to the auxiliary instance. RMAN copies the live source database over the network to the auxiliary instance, thereby creating the duplicate database. No backups of the source database are required. Figure -2 illustrates active database duplication.
Figure-2 Active Database Duplication
Steps of Database Duplication (Active method)
A) Pre-Configuration Steps:
1) Find Source and Target database
Assume host name of Source database is "DB1" and target database is "DB2". Both Severs are stand-alone type.
Assume source database name is PRODDB and target database name is REPDB.
2) Find connectivity between both hosts
use ping with IP from both server. Ping status should OK.
3) Verify OS and database versions
Assume OS version RHEL 6.3 and Oracle Software version 11.2.0.3
4) Verify Source database database size and more than space should available in target database.
5) Check Archive log is enabled in source database.
B) Configuration steps:
-- in proposed duplicate database side
1) configure listener.
Check listener should up and running in target side.
2) configure tnsname.ora for itself and source also
Check tnsping from both databses. Both side status should OK.
3) configure initREPDB.ora ( Duplicate Specific parameters)
db_name='REPDB'
DB_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'
LOG_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'
See sample pfile:
Note: Remove db_unique_name option, if your target database listener is blocking.
4) create a password file in source database and move it to target database side and rename it database SID name.
e.g.,
-- Source side
DB1] $ cd $ORACLE_HOME/dbs
DB1] $ orapwd force=y file=orapwPRODDB password=system
$ scp orapwPRODDB oracle@DB2:/$ORACLE_HOME/dbs
-- Traget side
DB2] $ cd $ORACLE_HOME/dbs
$ mv orapwPRODDB orapwREPDB
Now check connectivity using sqlplus in source database using password. It should connect
DB1] $ sqlplus /nolog
sql> connect sys/system@REPDB as sysdba
connected.
C) Create Duplicate database:
1) When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.
$ export ORACLE_SID=$PROD
$ rman target=sys/system auxiliary=sys/system@UAT
OR
$ rman target /
rman> connect auxiliary sys/system@REPDB;
2) Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.
RMAN>
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database to REPDB from active database;
}
2) Duplicate database from RMAN Backup
Note: Automatically database will be created and open it r/w mode also.
====================================================================
b) Duplicate database from RMAN Backup
You can create duplicate database from backup. If source and target directory structure for C|R|D files are same then most probably you may not face any issues. But if directory structure are different, then you need to follow "SET NEWNAME FOR DATAFILE xx TO" method.
a). add following in pfile ( only required if you are not using OMF and source & target file structure different)
*.db_file_name_convert='/u02/oracle/oradata','/u03/oracle/oradata'
*.log_file_name_convert ='/u02/oracle/oradata','/u03/oracle/oradata'
b). create directory structures for C|R|D files and other audit location
Note: Run the backup in source like similar as shown in below
connect target `/'
run
{
allocate channel chd1 device type disk;
allocate channel chd2 device type disk;
backup as compressed backupset incremental level 0 database plus archivelog;
release channel chd1;
release channel chd2;
}
Note: Auto backup on must be configured in the above case and you need to copy those backup files to target. Here all backups will be available in db_recovery_dest location.
OR
You can use below script.
e.g.,
run
{
sql 'alter system archive log current';
backup device type disk as backupset current controlfile format '/u03/backup/proddb_full_cf.bkp' tag 'proddb_full_cf';
backup device type disk incremental level 0 database format '/u03/backup/proddb_full_db_%U.bkp' tag 'proddb_full_db';
sql 'alter system archive log current';
backup device type disk archivelog all format '/u03/backup/proddb_full_al_%U.bkp' not backed up 2 times tag 'proddb_full_arc';
}
c) Start the database in nomount stage.
$ sqlplus / as sysdba
SQL> startup nomount;
ORACLE instance started.
d) Backup files with auto Bkp
$ ls -ltr
-rw-r-----. 1 oracle oinstall 220672 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrwt_.bkp
-rw-r-----. 1 oracle oinstall 4608 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrvy_.bkp
-rw-r-----. 1 oracle oinstall 12708352 Mar 18 22:24 o1_mf_annnn_TAG20170318T222448_ddtslrsp_.bkp
-rw-r-----. 1 oracle oinstall 2946310144 Mar 18 22:47 o1_mf_nnndf_TAG20170318T222452_ddtslxoq_.bkp
-rw-r-----. 1 oracle oinstall 2602360832 Mar 18 22:48 o1_mf_nnndf_TAG20170318T222452_ddtsly31_.bkp
-rw-r-----. 1 oracle oinstall 4789755904 Mar 18 22:55 o1_mf_nnndf_TAG20170318T222452_ddtslyjw_.bkp
-rw-r-----. 1 oracle oinstall 4372389888 Mar 18 22:56 o1_mf_nnndf_TAG20170318T222452_ddtslz1g_.bkp
-rw-r-----. 1 oracle oinstall 1840640 Mar 18 22:56 o1_mf_annnn_TAG20170318T225630_ddtvg74l_.bkp
-rw-r-----. 1 oracle oinstall 8192 Mar 18 23:06 o1_mf_1_4700_ddtvzpol_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4699_ddtvzkw1_.arc
-rw-r-----. 1 oracle oinstall 627712 Mar 18 23:06 o1_mf_1_4698_ddtvzhld_.arc
-rw-r-----. 1 oracle oinstall 3643904 Mar 18 23:06 o1_mf_1_4697_ddtvg6j4_.arc
-rw-r-----. 1 oracle oinstall 218624 Mar 18 23:06 o1_mf_1_4696_ddtslr78_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4695_ddts5s8n_.arc
-rw-r-----. 1 oracle oinstall 2560 Mar 18 23:06 o1_mf_1_4694_ddts5pqj_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 18 23:06 o1_mf_1_4693_ddts5md7_.arc
-rw-r-----. 1 oracle oinstall 39057408 Mar 18 23:06 o1_mf_1_4692_ddts5kt4_.arc
-rw-r-----. 1 oracle oinstall 13631488 Mar 18 23:07 o1_mf_s_938991392_ddtvg8yn_.bkp
e) Change ownership and permissions of Backup pieces.
$ id
uid=502(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),506(asmdba)
$
#chown -R oracle:oinstall 2017_03_18
#chmod -R 775 2017_03_18
f) Create Duplicate database.
$rman auxiliary / log = rmandup_2017_03_18.log
run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
DUPLICATE DATABASE "PROD" DBID 3590353289 TO "UAT" BACKUP LOCATION '/u04/2017_03_18' NOFILENAMECHECK;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
OR
you can script like below to call with nohup option:
vi uat_rman_duplicate_script.rman
run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
DUPLICATE DATABASE "prod" DBID 3590353289 TO "uat" BACKUP LOCATION '/u04/2017_03_18' NOFILENAMECHECK;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
}
$ env | grep ORA
ORACLE_UNQNAME=uat
ORACLE_SID=uat
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
$ nohup rman auxiliary / @uat_rman_duplicate_script.rman log=uat_rman_duplicate_2017_03_18.log &
g) Check the database status.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
UAT READ WRITE PRIMARY
SQL>
When directory structure NOT same for C/R/D files:
You can follow the same steps as given above, but step-5 to be modified and you can use below method. Omit db_file_name_convert and log_file_name_convert from parameter file.
rman> RUN
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO '/u02/oracle/uatdata/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u02/oracle/uatdata/sysaux01.dbf';
...
SET NEWNAME FOR DATAFILE 61 TO '/u02/oracle/uatdata/txn_data01.dbf';
SET NEWNAME FOR DATAFILE 62 TO '/u02/oracle/uatdata/undo_01.dbf';
DUPLICATE DATABASE "PROD" DBID 3655036336 TO "UAT" BACKUP LOCATION '/prod/FRA/PROD/backupset/2017_04_13' NOFILENAMECHECK;
LOGFILE
GROUP 1 ('/u02/oracle/uatdata/redo01.log') SIZE 500M REUSE,
GROUP 2 ('/u02/oracle/uatdata/redo02.log') SIZE 500M REUSE,
GROUP 3 ('/u02/oracle/uatdata/redo03.log') SIZE 500M REUSE,
GROUP 4 ('/u02/oracle/uatdata/redo04.log') SIZE 500M REUSE,
release channel aux1;
release channel aux2;
}
Note: If you are Creating Duplicate database from TAPE backups, then you can follow below method in rman script. But you should ensure where your TAPE agent is runnig, give the same path with required inforrmation:
e.g.,
run {
set dbid xxxxxxx;
allocate auxiliary channel ch1 device type sbt parms="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
duplicate database 'EXMPRD' to 'EXMUAT' NOFILENAMECHECK;
}
OR
## To retsore up to specific period.
run {
set dbid xxxxxxx;
set until time "TO_DATE('2017-03-18 07:00:00', 'YYYY-MM-DD HH24:MI:SS')";
allocate auxiliary channel ch1 device type sbt parms="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
duplicate database 'EXMPRD' to 'EXMUAT' NOFILENAMECHECK;
}
Issues and Troubleshooting:
-- Issue-1:
DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :fhdbi = 32767
released channel: stby1
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/21/2014 19:52:49
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution:1: like this
pfile :
*.db_name=prod1
*.db_unique_name=prod2
-- Issue-2:
RMAN-04006: ORA-28547:
RMAN-04006: error from auxiliary database: ORA-28547: connection to server failed, probable Oracle Net admin error
Solution:2:
1) re-create password file for target / duplicate database properly
2) recheck tnsnames.ora entry
3) Check the target database/ duplcate database listener status
4) While connecting auxiliary database, check auxiliary is connecting or not.
-- Issue-3:
Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2015 17:37:34
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01102: cannot mount database in EXCLUSIVE mode
OR
Solution-1:
If any oracle process is running with your duplicate database name, then kill them.
e.g.
$ ps -ef|grep $ORACLE_SID
$ kill -9 <pid>
Solution-2:
Add below parameter in standby parameter.
*.remote_login_passwordfile='EXCLUSIVE'
Solution-3:
Comment below entry in standby parameter file if you have kept/ add.
.db_unique_name='primary_dbname'
-- Issue - 4:
Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 16:54:33
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ch1 channel at 04/29/2015 16:54:24
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
Solution:
1) re-create password file in target db and test connection from source db.
2) Check the listener status. No blocked service should be there:
e.g.
Services Summary...
Service "PROD1" has 2 instance(s).
Instance "PROD1", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD2" has 2 instance(s).
Instance "PROD2", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD2", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
Verify your listener:
sample listener.ora
LISTENER_UAT =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = uat-bkp)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = UAT)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER_UAT = /u01/app/oracle
Note: Most of cases issue will come when listener.ora is not configured properly.
3) while you are connecting from source database via rman to target database, check which target db is connecting:
sample:
$ rman target=sys/system auxiliary=sys/system@UAT;
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 29 18:45:29 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=123456789)
connected to auxiliary database: UAT (not mounted)
4) Check source db is archivelog mode is on or not.
The the rman log / message while creating duplicate database:
.....
.....
duplicate target database to UAT from active database;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=964 device type=DISK
allocated channel: stby1
channel stby1: SID=541 device type=DISK
Starting Duplicate Db at 29-APR-15
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Fixed Size 2237008 bytes
Variable Size 1577061808 bytes
Database Buffers 8455716864 bytes
Redo Buffers 19767296 bytes
allocated channel: stby1
channel stby1: SID=272 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''UAT'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/ORADATA/UAT/control01.ctl';
restore clone controlfile to '/u01/ORADATA/UAT/control02.ctl' from
'/u01/ORADATA/UAT/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''UAT'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
...
...
72 auxiliary format
"/u01/ORADATA/UAT/SUPPORT_01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
...
...
Starting backup at 29-APR-15
channel ch1: starting datafile copy
input datafile file number=00033 name=/u01/ORADATA/PROD/HR01.dbf
...
...
input datafile copy RECID=71 STAMP=878325378 file name=/u01/ORADATA/UAT/SUPPORT_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 29-APR-15
released channel: ch1
released channel: stby1
RMAN>
Issue -5 : When data block corruption will come during duplicate database creation:
The the rman log / message while creating duplicate database:
.....
.....
duplicate target database to UAT from active database;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=964 device type=DISK
allocated channel: stby1
channel stby1: SID=541 device type=DISK
Starting Duplicate Db at 29-APR-15
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Fixed Size 2237008 bytes
Variable Size 1577061808 bytes
Database Buffers 8455716864 bytes
Redo Buffers 19767296 bytes
allocated channel: stby1
channel stby1: SID=272 device type=DISK
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''UAT'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/ORADATA/UAT/control01.ctl';
restore clone controlfile to '/u01/ORADATA/UAT/control02.ctl' from
'/u01/ORADATA/UAT/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''UAT'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
...
...
72 auxiliary format
"/u01/ORADATA/UAT/SUPPORT_01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
...
...
Starting backup at 29-APR-15
channel ch1: starting datafile copy
input datafile file number=00033 name=/u01/ORADATA/PROD/HR01.dbf
...
...
input datafile copy RECID=71 STAMP=878325378 file name=/u01/ORADATA/UAT/SUPPORT_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 29-APR-15
released channel: ch1
released channel: stby1
RMAN>
Issue -5 : When data block corruption will come during duplicate database creation:
I found below message like this and terminated the session:
.....
.....
.....
datafile 71 switched to datafile copy
input datafile copy RECID=70 STAMP=878329989 file name=/u01/ORADATA/UAT/HR02.dbf
datafile 72 switched to datafile copy
input datafile copy RECID=71 STAMP=878329989 file name=/u01/ORADATA/UAT/SUPPORT_01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 04/29/2015 20:33:48
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86663)
ORA-01110: data file 1: '/u01/ORADATA/UAT/system01.dbf'
Process ID: 25656
Session ID: 805 Serial number: 5
$
Action to be taken:
1) Verify source database block corruption issues.
2) Validate some backups for corrupted blocks in source and then transfer to target database and again validate in target database side and restore.
3) The last option is to take a full export dump and restore it.
Note: Try to replace the exiting storage.
Thanks .. This document will help you. Cheers!!!
brilliant details!!
ReplyDeletethanks
This comment has been removed by a blog administrator.
ReplyDeleteHey, Check This content for more information about klean uv pro Photostick Omni vs Photo Stick Mobile
ReplyDelete