My Oracle: December 2015

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}

Step 3 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

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

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

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!

My Oracle

Thursday, December 31, 2015

ORA-01578: ORACLE data block corrupted in Active Standby Database

Tuesday, December 29, 2015

Duplicate the Standby Database from Standby database backup

Oracle10g RAC Administrator

Oracle12c Certified DBA

Welcome to my Blog

Blog Archive

FEEDJIT Live Traffic Feed

Online Users