Tuesday, December 29, 2015

Duplicate the Standby Database from Standby database backup

Scenario :  The standby site is crashed or unavailable and need to restore from another standby without touching the primary site. This post also will be useful who wants to duplicate the  standby site  from primary database backup on data guard environment. 

The below steps are worked well on my production environment. But i do not recommend to use the below script in your environment unless you change the scripts(instance name, DB name, host name, directory name etc) and test in test environment. 

For security reason, i used XXXXX for hiding my host name, instance name, directory names.  Also i am using DEVDB for hiding the instance name.

My instance names are devdb12(primary), devdb12_dg(first standby), devdb12_dr(second standby).  Now devdb12_dg is crashed or unavailable. The goal is to restore devdb12_dg from other standby site(devdb12_dr). My environment has no ASM disks. 

Prerequisite : 

Oracle software should be installed and version should be same as other site.
The directory structure  and  file system size  should be same as other site.


Step 1

Create pfile from spfile on working standby site and copy to new standby site. Modify the pfile parameter(instance name, DG parameter etc) according to new standby location. Copy the password file, wallet file from standby location to new standby location.

Step 2

Take L0 backup on working standby site and copy the RMAN backup files to new standby site.  I am backing up the RMAN backup files under /backup file system. We will have to copy the backup files to same file system(/backup) on the new standby site.  If you do not have same file system in new standby site, then you have to use the BACKUP LOCATION option on the restore command. Please review this link to find details for different options.

Sample RMAN backup script is as below.  DO NOT USE this in your environment without making the changes(directory location, instance name etc).  I am using 8 channel for RMAN backup. However, i will let you choose the number of channel according to your server CPU power.  We can also restore the database from tape.. please refer this link just in case if you don't prefer to backup the database in working standby site. Restore from tape.    

ORACLE_BASE=/ora/app/oracle
ORACLE_HOME=/ora/app/oracle/product/11.1.0/db_1
ORACLE_SID=XXXXXXX
RMAN_SCRIPT=/ora/app/oracle/admin/XXXXXXX/rman
BACKUP_LOG_PATH=$RMAN_SCRIPT/logs
MAIL_TO_1=XXXXX@XXXX.com
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export RMAN_SCRIPT
export BACKUP_LOG_PATH
export MAIL_TO_1
DT=`date "+%m%d%Y_%H%M"`
LOG_FILE=${BACKUP_LOG_PATH}/bckp_level_0_${ORACLE_SID}_${DT}.log
export TODAY=`date +%Y%m%d'_'%H%M`
export TAG=`echo ${ORACLE_SID}_L0_${TODAY}`
export HOSTNAME=`hostname`
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} << EOF
connect target;
run
{
    ALLOCATE CHANNEL T01 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T02 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T03 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T04 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T05 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T06 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T07 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    ALLOCATE CHANNEL T08 TYPE DISK  FORMAT '/dbbackup/XXXXXXX/%d_L0_${TODAY}_%s_%U_%t';
    SET COMMAND ID TO 'LEVEL0 BACKUP';
    CROSSCHECK BACKUP;
    DELETE NOPROMPT OBSOLETE;
    BACKUP INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET DATABASE TAG=${TAG};
    BACKUP FORMAT  '/dbbackup/XXXXXXX/%d_SPFILE_${TODAY}_%s_%U_%t' SPFILE TAG=${TAG};
    BACKUP FORMAT  '/dbbackup/XXXXXXX/%d_CTRL_${TODAY}_%s_%U_%t' CURRENT CONTROLFILE TAG=${TAG};
    RELEASE CHANNEL T01;
    RELEASE CHANNEL T02;
    RELEASE CHANNEL T03;
    RELEASE CHANNEL T04;
    RELEASE CHANNEL T05;
    RELEASE CHANNEL T06;
    RELEASE CHANNEL T07;
    RELEASE CHANNEL T08;
    host "cp /ora/app/oracle/admin/XXXXXXX/wallet/ewallet.p12 /dbbackup/XXXXXXX/ewallet.p12_`date +%m%d%y%H%M%S`";
    host "cp /ora/app/oracle/admin/XXXXXXX/wallet/cwallet.sso /dbbackup/XXXXXXX/cwallet.sso_`date +%m%d%y%H%M%S`";
}
exit;
EOF
ERRM="RMAN-00569"

cat ${LOG_FILE} | grep "${ERRM}" > /dev/null 2>&1
if [ "$?" -eq 0 ]; then
  SUBJ="RMAN Level 0 Backup Failed ${ORACLE_SID} at ${HOSTNAME}"
  mailx -s "${SUBJ}" ${MAIL_TO_1} < ${LOG_FILE}
else
  SUBJ="RMAN Level 0 Backup Successful ${ORACLE_SID} at ${HOSTNAME}"
  mailx -s "${SUBJ}" ${MAIL_TO_1} < ${LOG_FILE}
fi


Step   Make sure, all the directories in new standby site is same as other node.

Step 4   Start the instance without mounting.

startup nomount pfile= initdevdb12_dg.ora

Step 5   Create spfile and start the instance again with spfile

create spfile from pfile= spfiledevdb12_dg.ora

shutdown immediate;
startup nomount;

Step 6 Check the RMAN connection between new standby to  working standby site. This script will make sure, no connection issues between two sites. Make sure, you setup tnsnames.ora, listener.ora file on new standby site.  Auxiliary site(XXXXXXX_dg) is new standby site. Target site(XXXXXXX_dr) is existing standby site.

DT=`date +%Y%m%d_%H%M%S`
export DT
$ORACLE_HOME/bin/rman msglog=rman_create_standby_XXXXXXX_dg1_${DT}.log <

connect target       sys/password@XXXXXXX_dr
connect auxiliary    sys/password@XXXXXXX_dg
 run
  {
  allocate channel prmy1 type disk;
  allocate channel prmy2 type disk;
  allocate channel prmy3 type disk;
  allocate channel prmy4 type disk;
  allocate channel prmy5 type disk;
  allocate channel prmy6 type disk;
  allocate channel prmy7 type disk;
  allocate channel prmy8 type disk;
  allocate auxiliary channel stby1 type disk;
  allocate auxiliary channel stby2 type disk;
  allocate auxiliary channel stby3 type disk;
  allocate auxiliary channel stby4 type disk;
  allocate auxiliary channel stby5 type disk;
  allocate auxiliary channel stby6 type disk;
  allocate auxiliary channel stby7 type disk;
  allocate auxiliary channel stby8 type disk;
    }
 exit
EOF

Step 7 Restore the new standby.  Auxiliary site is new standby site. Target site is existing standby site.

file name :  rman_standby_to_standby.sh

You need to run the script in background mode.. In my production environment, it took 10 hours to complete with 8 Channel.  My database size was 10TB.

DT=`date +%Y%m%d_%H%M%S`
export DT
$ORACLE_HOME/bin/rman msglog=rman_create_standby_XXXXXXX_dg_${DT}.log <

connect target       sys/password@XXXXXXX_dr
connect auxiliary    sys/password@XXXXXXX_dg
 run
  {
  allocate channel prmy1 type disk;
  allocate channel prmy2 type disk;
  allocate channel prmy3 type disk;
  allocate channel prmy4 type disk;
  allocate channel prmy5 type disk;
  allocate channel prmy6 type disk;
  allocate channel prmy7 type disk;
  allocate channel prmy8 type disk;
  allocate auxiliary channel stby1 type disk;
  allocate auxiliary channel stby2 type disk;
  allocate auxiliary channel stby3 type disk;
  allocate auxiliary channel stby4 type disk;
  allocate auxiliary channel stby5 type disk;
  allocate auxiliary channel stby6 type disk;
  allocate auxiliary channel stby7 type disk;
  allocate auxiliary channel stby8 type disk;
  DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK;
  }
 exit
EOF

The restore log file should be as below.  

Note :  The log file size is huge and several pages. Hence i trimmed the log file size to smaller. I removed many lines in the middle of log file.

RMAN – log of rman_standby_to_standby.sh

Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 11 16:54:37 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN>
RMAN>
connected to target database: XXXXXXX (DBID=1081194209)

RMAN>
connected to auxiliary database: XXXXXXX (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=995 device type=DISK

allocated channel: prmy2
channel prmy2: SID=916 device type=DISK

allocated channel: prmy3
channel prmy3: SID=920 device type=DISK

allocated channel: prmy4
channel prmy4: SID=908 device type=DISK

allocated channel: prmy5
channel prmy5: SID=909 device type=DISK

allocated channel: prmy6
channel prmy6: SID=914 device type=DISK

allocated channel: prmy7
channel prmy7: SID=917 device type=DISK

allocated channel: prmy8
channel prmy8: SID=919 device type=DISK

allocated channel: stby1
channel stby1: SID=1085 device type=DISK

allocated channel: stby2
channel stby2: SID=1105 device type=DISK

allocated channel: stby3
channel stby3: SID=1080 device type=DISK

allocated channel: stby4
channel stby4: SID=1079 device type=DISK

allocated channel: stby5
channel stby5: SID=1078 device type=DISK

allocated channel: stby6
channel stby6: SID=1084 device type=DISK

allocated channel: stby7
channel stby7: SID=1083 device type=DISK

allocated channel: stby8
channel stby8: SID=1082 device type=DISK

Starting Duplicate Db at 01/11/2013 16:54:50

contents of Memory Script:
{
   set until scn  51178358684;
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 01/11/2013 16:55:15

channel stby1: starting datafile backup set restore
channel stby1: restoring control file
channel stby1: reading from backup piece /dbbackup/XXXXXXX/XXXXXXX_CTRL_20130110_2315_3526_e6nv4cje_1_1_804401774
channel stby1: piece handle=/dbbackup/XXXXXXX/XXXXXXX_CTRL_20130110_2315_3526_e6nv4cje_1_1_804401774 tag=XXXXXXX_L0_20130110_2315
channel stby1: restored backup piece 1
channel stby1: restore complete, elapsed time: 00:00:03
output file name=/data01/oradata/XXXXXXX/control01.ctl
output file name=/data02/oradata/XXXXXXX/control02.ctl
output file name=/data03/oradata/XXXXXXX/control03.ctl
Finished restore at 01/11/2013 16:55:19

sql statement: alter database mount standby database

contents of Memory Script:
{
   set until scn  51178358684;
   set newname for tempfile  1 to
 "/orasys/oradata/XXXXXXX/temp01.dbf";
   set newname for tempfile  2 to
 "/orasys/oradata/XXXXXXX/temp02.dbf";
   set newname for tempfile  3 to
   switch clone tempfile all;
   set newname for datafile  1 to
 "/orasys/oradata/XXXXXXX/system01.dbf";
   set newname for datafile  2 to
 "/orasys/oradata/XXXXXXX/sysaux01.dbf";
   set newname for datafile  3 to
 "/undo/oradata/XXXXXXX/undotbs01.dbf";
   set newname for datafile  4 to
 "/undo/oradata/XXXXXXX/undotbs02.dbf";
   set newname for datafile  5 to
 "/orasys/oradata/XXXXXXX/users01.dbf";
   set newname for datafile  6 to
 "/orasys/oradata/XXXXXXX/db_audit_f01.dbf";
   set newname for datafile  7 to
 "/data01/oradata/XXXXXXX/XXXXXX01_SMALL_F01.dbf";
   set newname for datafile  8 to
 "/data02/oradata/XXXXXXX/XXXXXX01_SMALL_F02.dbf";
   set newname for datafile  225 to
 "/data08/oradata/XXXXXXX/XXXXXX11_TRANSHISTORY_INDX_F39.dbf";
   set newname for datafile  226 to
 "/data11/oradata/XXXXXXX/XXXXXX08_TRANSPROC_INDX_F18.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /orasys/oradata/XXXXXXX/temp01.dbf in control file
renamed tempfile 2 to /orasys/oradata/XXXXXXX/temp02.dbf in control file
renamed tempfile 3 to /data14/oradata/XXXXXXX/temp03.dbf in control file
renamed tempfile 4 to /data15/oradata/XXXXXXX/temp04.dbf in control file
renamed tempfile 5 to /data16/oradata/XXXXXXX/temp05.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME


Starting restore at 01/11/2013 16:55:39

channel stby1: starting datafile backup set restore
channel stby1: specifying datafile(s) to restore from backup set
channel stby1: restoring datafile 00006 to /orasys/oradata/XXXXXXX/db_audit_f01.dbf
channel stby1: restoring datafile 00028 to /data07/oradata/XXXXXXX/XXXXXX03_LARGE_F09.dbf
channel stby1: restoring datafile 00128 to
handle=/dbbackup/XXXXXXX/XXXXXXX_L0_20130110_2315_3515_drnv48r5_1_1_804397925 tag=XXXXXXX_L0_20130110_2315
channel stby3: restored backup piece 1
channel stby3: restore complete, elapsed time: 00:57:32
channel stby4: piece handle=/dbbackup/XXXXXXX/XXXXXXX_L0_20130110_2315_3518_dunv49es_1_1_804398556 tag=XXXXXXX_L0_20130110_2315
channel stby4: restored backup piece 1
channel stby4: restore complete, elapsed time: 00:56:46
Finished restore at 01/11/2013 22:29:55

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=21 STAMP=804465000 file name=/orasys/oradata/XXXXXXX/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=22 STAMP=804465000 file name=/orasys/oradata/XXXXXXX/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=23 STAMP=804465000 file name=/undo/oradata/XXXXXXX/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=24 STAMP=804465000 file name=/undo/oradata/XXXXXXX/undotbs02.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=25 STAMP=804465000 file name=/orasys/oradata/XXXXXXX/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=26 STAMP=804465000 file name=/orasys/oradata/XXXXXXX/db_audit_f01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=27 STAMP=804465001 file name=/data01/oradata/XXXXXXX/XXXXXX01_SMALL_F01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=28 STAMP=804465001 file name=/data02/oradata/XXXXXXX/XXXXXX01_SMALL_F02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=29 STAMP=804465001 file name=/data03/oradata/XXXXXXX/XXXXXX02_MEDIUM_F01.dbf
datafile 10 switched to datafile copy
name=/data11/oradata/XXXXXXX/XXXXXX08_TRANSPROC_INDX_F18.dbf

contents of Memory Script:
{
   set until scn  51178358684;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01/11/2013 22:30:27

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/orasys/oradata/XXXXXXX/system01.dbf'

released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: prmy6
released channel: prmy7
released channel: prmy8
released channel: stby1
released channel: stby2
released channel: stby3
released channel: stby4
released channel: stby5
released channel: stby6
released channel: stby7
released channel: stby8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/11/2013 22:31:08
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 90545 and starting SCN of 51178331763 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90544 and starting SCN of 51178280286 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90543 and starting SCN of 51178228751 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90542 and starting SCN of 51178172350 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90541 and starting SCN of 51178116145 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90430 and starting SCN of 51172378542 found to restore

RMAN>

Recovery Manager complete.

Step 8 Copy the archive logs from sequence# 90430 to sequence# 90550 (actually only up to 90545). Use SCP to Copy the archive log from working standby(devdb12_dr) to devdb12_dg site.

Step 9 Recover the database.

Standby database is in mount state

alter database recover standby database until cancel

You may want to give few archive log files manually and then put AUTO for recovery Watch alert log in another window

It will recover standby database until last archive log file which is in as defined by parameter log_archive_dest_1  - in our case it was /dbArch01/XXXXXXX and media recovery will fail – this is expected

Step 10  Make the changes on Data Guard Parameter on primary as well as other standby.
You need to change the parameter according to your environment. 

On new standby(devdb12_dg) : 

alter system set  db_unique_name='devdb12_dg' scope=spfile;
alter system set  instance_name='devdb12_dg' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=devdb12 LGWR ASYNC DB_UNIQUE_NAME=devdb12 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_dest_3 = 'SERVICE=devdb12_dr  LGWR ASYNC DB_UNIQUE_NAME=devdb12_dr VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_config = 'dg_config=(devdb12,devdb12_dg,devdb12_dr)';
alter system set log_archive_dest_state_4 = 'ENABLE';
alter system set standby_file_management = 'AUTO';
alter system set log_archive_max_processes=5;
alter system set fal_client=devdb12_dg;
alter system set fal_server=devdb12,devdb12_dr ;

On working standby(devdb12_dr) :

alter system set log_archive_config = 'DG_CONFIG=(devdb12,devdb12_dg,devdb12_dr)' scope=both;
alter system set log_archive_dest_2 = 'SERVICE=devdb12_dg LGWR ASYNC DB_UNIQUE_NAME=devdb12_dg VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_dest_3 = 'SERVICE=devdb12 LGWR ASYNC DB_UNIQUE_NAME=devdb12 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_max_processes=5;
alter system set fal_client=devdb12_dr;
alter system set fal_server=devdb12_dg,devdb12 scope=both;
alter system switch logfile;

On Primary(devdb12) :

alter system set log_archive_config = 'DG_CONFIG=(devdb12,devdb12_dg,devdb12_dr)' scope=both;
alter system set log_archive_dest_2 = 'SERVICE=devdb12_dg LGWR ASYNC DB_UNIQUE_NAME=devdb12_dg VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_dest_3 = 'SERVICE=devdb12_dr LGWR ASYNC DB_UNIQUE_NAME=devdb12_dr VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set log_archive_max_processes=5;
alter system set fal_client=devdb12;
alter system set fal_server=devdb12_dg,devdb12_dr scope=both;
alter system switch logfile;

Step 11  Create the temp file same as other standby node.

alter tablespace temp add tempfile '/oratemp01/oradata/XXXXXXX/temp01.dbf' size 16G;

Step 12  Create the standby redo log file same as other standby node.

Create standby logs on new standby database – by default oracle will create one standby redo log – which you can drop

alter database drop STANDBY LOGFILE GROUP 6;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6  ('/redo01/oradata/XXXXXXX/std_redo01.log') SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7  ('/redo02/oradata/XXXXXXX/std_redo02.log') SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8  ('/redo03/oradata/XXXXXXX/std_redo03.log') SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9  ('/redo01/oradata/XXXXXXX/std_redo04.log') SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/redo02/oradata/XXXXXXX/std_redo05.log') SIZE 300M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/redo03/oradata/XXXXXXX/std_redo06.log') SIZE 300M;

Step 13  Shutdown the database and open in read mode. Watch the alert log on separate window.

shutdown immediate;
startup nomount;
alter database mount standby database;
-- The below three lines are related to wallet. The below three lines can be ignored if you are not using the wallet.
select * from v$encryption_wallet;
alter system set wallet open iddentified by "XXXX";
select * from v$encryption_wallet;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Hope this post helps!  Please provide your comments and feedback!

No comments: