How to Recover Standby from Primary Database

— Server A – Is a Live Server
— Server B – Is a Physical Standby Server
Both were configured for a data guard

One fine day a standby blows away and we need to recreate it from Live

At Live Server A

1. There is no need but if you want please disable Dataguard Broker

SQLPLUS> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQLPLUS> alter system set dg_broker_start=FALSE

DGMGRL> show configuration verbose;

Configuration
Name: odba
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
catp_hongkong – Primary database
catp_morocco – Physical standby database (disabled)

2. Put current database into a HOT backup mode.
Backup Datafiles, Controlfiles and Redo logs

select ‘alter tablespace ‘|| tablespace_name ||’ begin backup;’ from dba_tablespaces; select ‘cp ‘|| name || ‘ /oracle5/orabkup/ ‘ from v$datafile/v$controlfile/v$logfile alter database create standby controlfile as ‘/oracle5/orabkup/control_sndby.ctl’ select ‘alter tablespace ‘|| tablespace_name ||’ end backup;’ from dba_tablespaces;

Copy files from backup set on a standby database.

At Standby Server B

3. Please make sure following parameters are set on a Physical Standby database init.ora file

Add 2 parameters at init.ora on standby only standby_file_management=auto standby_archive_dest=’f:\oracle\oradata\standby’

4. Recover Standby Database Now

SQL> connect / as sysdba
Connected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle1/oradata/catp
Oldest online log sequence 199166
Next log sequence to archive 199169
Current log sequence 199169
SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2030296 bytes
Variable Size 503317800 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14745600 bytes

SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQL> recover managed standby database disconnect from session;
ORA-01507: database not mounted

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Monitor Alrt log for messages for ARCHIVE Success

Media Recovery Waiting for thread 1 sequence 199169
Fetching gap sequence in thread 1, gap sequence 199169-199177
Tue Jun 5 15:54:45 2007
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS[2]: Assigned to RFS process 3397
RFS[2]: Identified database type as ‘physical standby’
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199179_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199169_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199170_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199171_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199172_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199173_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199174_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199175_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199176_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199177_1_528884944.arc’
Tue Jun 5 15:55:15 2007
Media Recovery Log /oracle1/oradata/catp/catp_0000199169_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199170_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199171_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199172_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199173_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199174_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199175_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199176_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199177_1_528884944.arc
Media Recovery Waiting for thread 1 sequence 199178
Fetching gap sequence in thread 1, gap sequence 199178-199178
Tue Jun 5 15:55:18 2007
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199178_1_528884944.arc’

Note : You don’t need to use “recover managed standby database disconnect from session;” if you have used ” alter system set dg_broker_start=TRUE scope=both;”

You can check v$archived_log and v$dataguard_status views to find out logs applied and errors if any respectively.

1. Logs Applied on Standby
Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;
OR
select sequence#, applied ,completion_time from v$archived_log where STANDBY_DEST=’YES’ order by 3 desc

2. Error message at Dataguard
Select to_char(TIMESTAMP, ‘dd-mon-yyyy hh24:mm:ss’), MESSAGE from v$dataguard_status;

Sometimes Oracle throws error messages at ALRTLOG – Failed to request gap sequence

Reason : DG FAL failed to copy archive files from PRIMARY to STANDBY

Media Recovery Waiting for thread 1 sequence 84730
Fetching gap sequence in thread 1, gap sequence 84730-85042
FAL[client]: Trying FAL server: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zagreb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=bfp_zagreb_XPT.yell)(SERVER=dedicated)))
Mon Nov 12 15:06:45 2007
Completed: ALTER DATABASE RECOVER managed standby database d
Mon Nov 12 15:11:45 2007
FAL[client]: Failed to request gap sequence for thread 1 gap sequence 84730-85042
FAL[client]: All defined FAL servers have been attempted.

Fix:

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;

no rows selected

SQL> recover standby database;
ORA-01153: an incompatible media recovery is active

STOP EARLIER recovery session

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover standby database;
ORA-00279: change 1967405177515 generated at 11/08/2007 10:06:55 needed for
thread 1
ORA-00289: suggestion : /oracle1/oradata/bfp/bfp_1_569156389_0000084730.arc
ORA-00280: change 1967405177515 for thread 1 is in sequence #84730

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

This will end up as

ORA-00308: cannot open archived log
‘/oracle1/oradata/bfp/bfp_1_569156389_0000084800.arc’
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Rman Script to Create Standby From Live Server
connect target rman/obst@live
connect auxiliary sys/ams@standby
connect rcvcat rmanc/obss@rcvcat
Run {
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;
send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default,NSR_CLIENT=miles)’;
duplicate target database for standby dorecover nofilenamecheck;
release channel tp1;
release channel tp2;
}

Above script will return an error
ORA-01830: date format picture ends before converting entire input string
Add a line
Run {
set until time “to_date(‘Dec 11 2007 06:30:09′,’Mon DD YYYY HH24:MI:SS’)”;

Restore Archivelogs which were backed up thru rman

connect rcvcat rmanc/obseq@rcvcat
connect target rmant/obus@live
run {
allocate channel tp1 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
allocate channel tp2 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
set archivelog destination to ‘f:\oracle\oradata\live’;
restore archivelog from logseq 111094 until logseq 111111;
release channel tp1;
release channel tp2;

Run RMAN script to create log as
rman cmdfile=create_standby.rman msglog=create_standby.log