Recover standby using incremental from scn

If the standby database gets out of sync and the archive logs are no longer available on the primary then follow these steps:

$ . oraenv
ORACLE_SID = [empdb] ?
The Oracle base for ORACLE_HOME=/app/oracle/product/10.2.0.5/db_1 is /app/oracle/product
$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 20 08:46:09 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col current_scn format 99999999999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
---------------------
11997965926691

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


host1-oracle> df -h .
Filesystem size used avail capacity Mounted on
host2-mount:/backups
275G 68G 204G 26% /rman


host1-oracle> rman

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 08:55:28 2014

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

RMAN> connect target /

connected to target database: empdb (DBID=3441594330)

RMAN> connect catalog rcat_user/******@rmandb;

connected to recovery catalog database

RMAN> BACKUP INCREMENTAL FROM SCN 11997965926690
2> DATABASE FORMAT '/rman/empdb/ForStandby_%U' tag 'FORSTANDBY';

Starting backup at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1006 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=906 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1047 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/u27_db/oradata/empdb/dld_data.302.700417423
input datafile fno=00007 name=/u27_db/oradata/empdb/dct_data.304.700417369
input datafile fno=00006 name=/u27_db/oradata/empdb/undotbs3.278.700408761
input datafile fno=00001 name=/u27_db/oradata/empdb/system.276.700408493
input datafile fno=00018 name=/u27_db/oradata/empdb/index.293.700417487
input datafile fno=00004 name=/u27_db/oradata/empdb/users.280.700408495
input datafile fno=00011 name=/u27_db/oradata/empdb/ins_data.300.700417475
channel ORA_DISK_1: starting piece 1 at 20-JAN-14
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00017 name=/u27_db/oradata/empdb/tpz_data.294.700417483
input datafile fno=00005 name=/u27_db/oradata/empdb/undotbs2.279.700408761
input datafile fno=00020 name=/u27_db/oradata/empdb/ukp_index.291.700417495
input datafile fno=00003 name=/u27_db/oradata/empdb/sysaux.287.700408493
input datafile fno=00010 name=/u27_db/oradata/empdb/indx.301.700417475
input datafile fno=00014 name=/u27_db/oradata/empdb/nup_index.297.700417481
input datafile fno=00016 name=/u27_db/oradata/empdb/sag_index.295.700417483
channel ORA_DISK_2: starting piece 1 at 20-JAN-14
channel ORA_DISK_3: starting full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00002 name=/u27_db/oradata/empdb/undotbs1.312.700408495
input datafile fno=00019 name=/u27_db/oradata/empdb/ukp_data.292.700417487
input datafile fno=00021 name=/u27_db/oradata/empdb/ins_temp.336.709281269
input datafile fno=00008 name=/u27_db/oradata/empdb/dct_index.303.700417409
input datafile fno=00013 name=/u27_db/oradata/empdb/nup_data.298.700417479
input datafile fno=00015 name=/u27_db/oradata/empdb/sag_data.296.700417481
input datafile fno=00012 name=/u27_db/oradata/empdb/ins_index.299.700417477
channel ORA_DISK_3: starting piece 1 at 20-JAN-14
channel ORA_DISK_2: finished piece 1 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0souhdif_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:02:55
channel ORA_DISK_3: finished piece 1 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0touhdif_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_3: starting piece 2 at 20-JAN-14
channel ORA_DISK_1: finished piece 1 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0rouhdif_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:06:35
channel ORA_DISK_3: finished piece 2 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0touhdif_2_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_3: starting piece 3 at 20-JAN-14
channel ORA_DISK_3: finished piece 3 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0touhdif_3_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_3: starting piece 4 at 20-JAN-14
channel ORA_DISK_3: finished piece 4 at 20-JAN-14
piece handle=/rman/empdb/ForStandby_0touhdif_4_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:19:20
Finished backup at 20-JAN-14

RMAN> exit

-- HOST2 --
# ls -lrt ForStandby_*
-rw-rw---- 1 oracle oinstall 139739136 Jan 20 08:59 ForStandby_0souhdif_1_1
-rw-rw---- 1 oracle oinstall 1610612736 Jan 20 09:02 ForStandby_0touhdif_1_1
-rw-rw---- 1 oracle oinstall 1522728960 Jan 20 09:03 ForStandby_0rouhdif_1_1
-rw-rw---- 1 oracle oinstall 1610612736 Jan 20 09:08 ForStandby_0touhdif_2_1
-rw-rw---- 1 oracle oinstall 1610612736 Jan 20 09:13 ForStandby_0touhdif_3_1
-rw-rw---- 1 oracle oinstall 765911040 Jan 20 09:16 ForStandby_0touhdif_4_1

$ rman target / nocatalog

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 10:04:59 2014

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

connected to target database: empdb (DBID=3441594330, not open)
using target database control file instead of recovery catalog

RMAN> catalog start with '/rman/empdb/ForStandby';

searching for all files that match the pattern /rman/empdb/ForStandby

List of Files Unknown to the Database
=====================================
File Name: /rman/empdb/ForStandby_0rouhdif_1_1
File Name: /rman/empdb/ForStandby_0souhdif_1_1
File Name: /rman/empdb/ForStandby_0touhdif_1_1
File Name: /rman/empdb/ForStandby_0touhdif_2_1
File Name: /rman/empdb/ForStandby_0touhdif_3_1
File Name: /rman/empdb/ForStandby_0touhdif_4_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /rman/empdb/ForStandby_0rouhdif_1_1
File Name: /rman/empdb/ForStandby_0souhdif_1_1
File Name: /rman/empdb/ForStandby_0touhdif_1_1
File Name: /rman/empdb/ForStandby_0touhdif_2_1
File Name: /rman/empdb/ForStandby_0touhdif_3_1
File Name: /rman/empdb/ForStandby_0touhdif_4_1

RMAN> shutdown immediate;

database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 3305111552 bytes

Fixed Size 2055696 bytes
Variable Size 1140851184 bytes
Database Buffers 2147483648 bytes
Redo Buffers 14721024 bytes

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1083 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1082 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /data02/oradata/empdb/system.276.700408493
destination for restore of datafile 00004: /data02/oradata/empdb/users.280.700408495
destination for restore of datafile 00006: /data02/oradata/empdb/undotbs3.278.700408761
destination for restore of datafile 00007: /data02/oradata/empdb/dct_data.304.700417369
destination for restore of datafile 00009: /data02/oradata/empdb/dld_data.302.700417423
destination for restore of datafile 00011: /data02/oradata/empdb/ins_data.300.700417475
destination for restore of datafile 00018: /data02/oradata/empdb/index.293.700417487
channel ORA_DISK_1: reading from backup piece /rman/empdb/ForStandby_0rouhdif_1_1
channel ORA_DISK_2: starting incremental datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /data02/oradata/empdb/sysaux.287.700408493
destination for restore of datafile 00005: /data02/oradata/empdb/undotbs2.279.700408761
destination for restore of datafile 00010: /data02/oradata/empdb/indx.301.700417475
destination for restore of datafile 00014: /data02/oradata/empdb/nup_index.297.700417481
destination for restore of datafile 00016: /data02/oradata/empdb/sag_index.295.700417483
destination for restore of datafile 00017: /data02/oradata/empdb/tpz_data.294.700417483
destination for restore of datafile 00020: /data02/oradata/empdb/ukp_index.291.700417495
channel ORA_DISK_2: reading from backup piece /rman/empdb/ForStandby_0souhdif_1_1
channel ORA_DISK_3: starting incremental datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /data02/oradata/empdb/undotbs1.312.700408495
destination for restore of datafile 00008: /data02/oradata/empdb/dct_index.303.700417409
destination for restore of datafile 00012: /data02/oradata/empdb/ins_index.299.700417477
destination for restore of datafile 00013: /data02/oradata/empdb/nup_data.298.700417479
destination for restore of datafile 00015: /data02/oradata/empdb/sag_data.296.700417481
destination for restore of datafile 00019: /data02/oradata/empdb/ukp_data.292.700417487
destination for restore of datafile 00021: /data02/oradata/empdb/ins_temp.336.709281269
channel ORA_DISK_3: reading from backup piece /rman/empdb/ForStandby_0touhdif_1_1
channel ORA_DISK_2: restored backup piece 1
piece handle=/rman/empdb/ForStandby_0souhdif_1_1 tag=FORSTANDBY
channel ORA_DISK_2: restore complete, elapsed time: 00:00:46
channel ORA_DISK_3: restored backup piece 1
piece handle=/rman/empdb/ForStandby_0touhdif_1_1 tag=FORSTANDBY
channel ORA_DISK_3: reading from backup piece /rman/empdb/ForStandby_0touhdif_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rman/empdb/ForStandby_0rouhdif_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:08:06
channel ORA_DISK_3: restored backup piece 2
piece handle=/rman/empdb/ForStandby_0touhdif_2_1 tag=FORSTANDBY
channel ORA_DISK_3: reading from backup piece /rman/empdb/ForStandby_0touhdif_3_1
channel ORA_DISK_3: restored backup piece 3
piece handle=/rman/empdb/ForStandby_0touhdif_3_1 tag=FORSTANDBY
channel ORA_DISK_3: reading from backup piece /rman/empdb/ForStandby_0touhdif_4_1
channel ORA_DISK_3: restored backup piece 4
piece handle=/rman/empdb/ForStandby_0touhdif_4_1 tag=FORSTANDBY
channel ORA_DISK_3: restore complete, elapsed time: 00:27:51

Finished recover at 20-JAN-14

RMAN>
RMAN>
RMAN> exit

SQL> shutdown imediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

Completed: alter database recover managed standby database disconnect from session
Mon Jan 20 10:42:47 GMT 2014
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 252974-253047
DBID 3441594330 branch 718225268
FAL[client]: All defined FAL servers have been attempted.


column Sequence# format 9999999
column First_Time format a20
column Next_Time format a20
column Applied format a3
Select Sequence#, First_Time, Next_Time, Applied
From V$ARCHIVED_LOG
Order by Sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APP
--------- -------------------- -------------------- ---
252966 17-JAN-14 17-JAN-14 YES
252967 17-JAN-14 17-JAN-14 YES
252968 17-JAN-14 17-JAN-14 YES
252969 17-JAN-14 17-JAN-14 YES
252970 17-JAN-14 17-JAN-14 YES
252971 17-JAN-14 17-JAN-14 YES
252972 17-JAN-14 17-JAN-14 YES
252973 17-JAN-14 17-JAN-14 YES
253048 18-JAN-14 18-JAN-14 NO
253050 18-JAN-14 18-JAN-14 NO
253051 18-JAN-14 18-JAN-14 NO

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

The controlfile still thinks it's missing logs - so resend the primary controlfile


rac01-oracle> rman

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 10:48:18 2014

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

RMAN> connect target /

connected to target database: empdb (DBID=3441594330)

RMAN> connect catalog rcat_user/******@rmandb;

connected to recovery catalog database

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Starting backup at 20-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=966 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=960 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1054 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-14
channel ORA_DISK_1: finished piece 1 at 20-JAN-14
piece handle=/tmp/ForStandbyCTRL.bck tag=TAG20140120T105016 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 20-JAN-14

Starting Control File and SPFILE Autobackup at 20-JAN-14
piece handle=/app/oracle/product/10.2.0/db/dbs/c-3441594330-20140120-02 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-14

RMAN> exit


Recovery Manager complete.


host1-oracle> scp /tmp/ForStandbyCTRL.bck stndby_host:/tmp
Password:
ForStandbyCTRL.bck 100% |**********************************************************************************************| 23008 KB 00:03

$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 10:51:03 2014

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 3305111552 bytes

Fixed Size 2055696 bytes
Variable Size 1140851184 bytes
Database Buffers 2147483648 bytes
Redo Buffers 14721024 bytes

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

Starting restore at 20-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1090 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/db_redo/oradata/empdb/control01.ctl
output filename=/db_redo01/oradata/empdb/control02.ctl
output filename=/db_system/oradata/empdb/control03.ctl
Finished restore at 20-JAN-14

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 3305111552 bytes

Fixed Size 2055696 bytes
Variable Size 1140851184 bytes
Database Buffers 2147483648 bytes
Redo Buffers 14721024 bytes

RMAN>exit

$ sqlplus / as sysdba

SQL> SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;

GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 ONLINE

SQL> SELECT GROUP#, STATUS FROM V$LOG;

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 INACTIVE
3 CURRENT
2 INACTIVE

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3305111552 bytes
Fixed Size 2055696 bytes
Variable Size 1140851184 bytes
Database Buffers 2147483648 bytes
Redo Buffers 14721024 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


host1-oracle> tail -f alert_empdb.log
Mon Jan 20 10:52:12 GMT 2014
Errors in file /app/oracle/product/admin/empdb/bdump/empdb_arc0_14728.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
PING[ARC0]: Heartbeat failed to connect to standby '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=stndby_host)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=empdbSB_XPT.)(INSTANCE_NAME=empdb)(SERVER=dedicated)))'. Error is 12528.
Mon Jan 20 10:57:13 GMT 2014
Error 12537 received logging on to the standby
Mon Jan 20 10:57:13 GMT 2014
Errors in file /app/oracle/product/admin/empdb/bdump/empdb_arc0_14728.trc:
ORA-12537: TNS:connection closed
PING[ARC0]: Heartbeat failed to connect to standby '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=stndby_host)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=empdbSB_XPT.)(INSTANCE_NAME=empdb)(SERVER=dedicated)))'. Error is 12537.
Mon Jan 20 11:11:50 GMT 2014
ALTER SYSTEM ARCHIVE LOG
Mon Jan 20 11:11:50 GMT 2014
Thread 1 advanced to log sequence 253625 (LGWR switch)
Current log# 1 seq# 253625 mem# 0: /u27_db/oradata/empdb/group_1_1a.redo
Current log# 1 seq# 253625 mem# 1: /u27_db/oradata/empdb/group_1_1b.redo
Mon Jan 20 11:11:52 GMT 2014
ALTER SYSTEM ARCHIVE LOG
Mon Jan 20 11:11:52 GMT 2014
Thread 1 advanced to log sequence 253626 (LGWR switch)
Current log# 2 seq# 253626 mem# 0: /u27_db/oradata/empdb/group_2_2a.redo
Current log# 2 seq# 253626 mem# 1: /u27_db/oradata/empdb/group_2_2b.redo


$ tail -f alert_empdb.log
Mon Jan 20 10:58:37 GMT 2014
Completed: alter database recover managed standby database disconnect from session
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 7035
RFS[1]: Identified database type as 'physical standby'
Mon Jan 20 11:02:13 GMT 2014
RFS LogMiner: Client disabled from further notification
Mon Jan 20 11:02:14 GMT 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 7037
RFS[2]: Identified database type as 'physical standby'
RFS[2]: Archived Log: '/archive01/empdb/standby/empdb_1_253623718225268.arc'
Mon Jan 20 11:11:53 GMT 2014
RFS[2]: Archived Log: '/archive01/empdb/standby/empdb_1_253624718225268.arc'
Mon Jan 20 11:11:53 GMT 2014
RFS[1]: Archived Log: '/archive01/empdb/standby/empdb_1_253625718225268.arc'