Network based recovery (12c new feature)

Author
Recent Posts

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Renaming a RAC cluster - 27/09/2018
Stop/Start all RAC databases at once - 26/09/2018
RMAN Backup script - 08/11/2017

In this article, I’ll explain how to manualy recover a standby database using the oracle RMAN new feature “RECOVER FROM SERVICE”.

Configuration for the test

HOSTS : 2 Virtual Machines with 2 GB of Memory
OS : Oracle Linux 6.4
ORACLE VERSION : Oracle 12.1.0.1 Standard Edition

A database named DB12C in archivelog mode has been created on the first host using DBCA, a listener is running on each server on the default port 1521 and the tnsnames.ora file has been defined as follow :

DB12C_PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr-ora-03)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB12C)
    )
  )

DB12C_STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = svr-ora-04)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = DB12C)
    )
  )

Standby database creation

We are going to create the standby database by duplicating the primary database for standby using RMAN. Since 11g no backup is needed, you can duplicate directly from an active database.
Before running the duplicate, we need to copy the spfile and the password file of the first database to the second server:

[oracle@svr-ora-03 dbs]$ scp spfileDB12C.ora orapwDB12C svr-ora-04:$ORACLE_HOME/dbs
oracle@svr-ora-04's password:
spfileDB12C.ora                                                    100% 3584     3.5KB/s   00:00
orapwDB12C                                                         100% 5120     5.0KB/s   00:00
[oracle@svr-ora-03 dbs]$

Second step is to create the directory structure which will contain the datafiles,archivelogs and audit files on the second host:

[oracle@svr-ora-04 oracle]$ mkdir -p /u01/app/oracle/oradata
[oracle@svr-ora-04 oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@svr-ora-04 oracle]$ mkdir -p /u01/app/oracle/admin/DB12C/adump

Before we can launch the duplication we have to create a pfile from the spfile. Once your pfile is created, drop your spfile and put your standby database in nomount mode :

[oracle@svr-ora-04 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Feb 18 13:36:23 2014

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2293928 bytes
Variable Size             599789400 bytes
Database Buffers          234881024 bytes
Redo Buffers                2318336 bytes

We are now ready to launch the duplication. Here is the RMAN script that I used for my duplication, this script has to be run from the standby host:

rman target sys/*******@DB12C_PRIMARY auxiliary sys/*******@DB12C_STANDBY
run 
{
	allocate channel c1 type disk;
	allocate auxiliary channel aux1 type disk;
	DUPLICATE TARGET DATABASE
	FOR STANDBY
	FROM ACTIVE DATABASE
	DORECOVER
	NOFILENAMECHECK;
}

here is the output of the script :

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Feb 18 14:01:52 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB12C (DBID=1293874550)
connected to auxiliary database: DB12C (not mounted)

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=37 device type=DISK

allocated channel: aux1
channel aux1: SID=1 device type=DISK

Starting Duplicate Db at 18-FEB-14
current log archived

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0.1/db_home1/dbs/orapwDB12C' auxiliary format
 '/u01/app/oracle/product/12.1.0.1/db_home1/dbs/orapwDB12C'   ;
   restore clone from service  'DB12C_PRIMARY' spfile to
 '/u01/app/oracle/product/12.1.0.1/db_home1/dbs/spfileDB12C.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0.1/db_home1/dbs/spfileDB12C.ora''";
}
executing Memory Script

Starting backup at 18-FEB-14
Finished backup at 18-FEB-14

Starting restore at 18-FEB-14

channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0.1/db_home1/dbs/spfileDB12C.ora
channel aux1: restore complete, elapsed time: 00:00:01
Finished restore at 18-FEB-14

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0.1/db_home1/dbs/spfileDB12C.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''DB12C_STBY'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''DB12C_STBY'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2293928 bytes
Variable Size                599789400 bytes
Database Buffers             234881024 bytes
Redo Buffers                   2318336 bytes
allocated channel: aux1
channel aux1: SID=7 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9j7301j4_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9j7301sz_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone from service  'DB12C_PRIMARY' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9j7301j4_.ctl'', ''/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9j7301sz_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 18-FEB-14

channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9j7301j4_.ctl
output file name=/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9j7301sz_.ctl
Finished restore at 18-FEB-14

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  6 to new;
   restore
   from service  'DB12C_PRIMARY'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_temp_%u_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 18-FEB-14

channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to /u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_system_%u_.dbf
channel aux1: restore complete, elapsed time: 00:00:55
channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00003 to /u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_sysaux_%u_.dbf
channel aux1: restore complete, elapsed time: 00:00:45
channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00004 to /u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_undotbs1_%u_.dbf
channel aux1: restore complete, elapsed time: 00:00:07
channel aux1: starting datafile backup set restore
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00006 to /u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_users_%u_.dbf
channel aux1: restore complete, elapsed time: 00:00:01
Finished restore at 18-FEB-14

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'DB12C_PRIMARY'
           archivelog from scn  1777293;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 18-FEB-14

channel aux1: starting archived log restore to default destination
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: restoring archived log
archived log thread=1 sequence=7
channel aux1: restore complete, elapsed time: 00:00:01
channel aux1: starting archived log restore to default destination
channel aux1: using network backup set from service DB12C_PRIMARY
channel aux1: restoring archived log
archived log thread=1 sequence=8
channel aux1: restore complete, elapsed time: 00:00:01
Finished restore at 18-FEB-14

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=839858660 file name=/u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_system_9j7cl5nd_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=839858660 file name=/u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_sysaux_9j7cmwtl_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=839858660 file name=/u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_undotbs1_9j7cob73_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=839858660 file name=/u01/app/oracle/oradata/DB12C_STBY/datafile/o1_mf_users_9j7cojq4_.dbf

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

executing command: SET until clause

Starting recover at 18-FEB-14

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/DB12C_STBY/archivelog/2014_02_18/o1_mf_1_7_9j7col90_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/DB12C_STBY/archivelog/2014_02_18/o1_mf_1_8_9j7com8o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C_STBY/archivelog/2014_02_18/o1_mf_1_7_9j7col90_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C_STBY/archivelog/2014_02_18/o1_mf_1_8_9j7com8o_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-FEB-14
Finished Duplicate Db at 18-FEB-14
released channel: c1
released channel: aux1

RMAN>

An important note is that you must connect to your auxiliary database using a service name and not using os authentication because during a duplication from active database, the primary database communicate with the standby using the service.

add the database to your oratab file:

[oracle@svr-ora-04 dbs]$ echo "DB12C:$ORACLE_HOME:Y" >> /etc/oratab

Recover the standby database

To maintain ou standby database up to date we will use a new Oracle 12c RMAN functionality which is : RECOVER DATABASE FROM SERVICE

First we should verify than our standby is ready for recover :

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

We are now ready for recovery, we will now run the following RMAN script on standby server in order to recover our standby database.

rman target sys/*******@DB12C_STANDBY
run
{
	RECOVER DATABASE FROM SERVICE DB12C_PRIMARY USING COMPRESSED BACKUPSET;
}

Here is the output from the script:

run {
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
RESTORE STANDBY CONTROLFILE FROM SERVICE DB12C_PRIMARY;
}

RMAN>
database dismounted
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2293928 bytes
Variable Size                603983704 bytes
Database Buffers             230686720 bytes
Redo Buffers                   2318336 bytes

RMAN>

Starting restore at 17-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DB12C_PRIMARY
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/DB12C/controlfile/o1_mf_9j7301j4_.ctl
output file name=/u01/app/oracle/fast_recovery_area/DB12C/controlfile/o1_mf_9j7301sz_.ctl
Finished restore at 17-MAR-14

What happens now if I try to open my standby database?

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN>  alter database open read only;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/17/2014 11:32:55
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jpp643g_.dbf'

By looking carefully, the datafile 1 that oracle is searching has the name of the primary database instead of the datafile restored, that makes sense because the controlfile comes from the primary database, we need to register the restored files to the restored controlfile:

RMAN> CATALOG START WITH '/u01/app/oracle/oradata/DB12C/datafile' NOPROMPT;

Starting implicit crosscheck backup at 17-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK
Finished implicit crosscheck backup at 17-MAR-14

Starting implicit crosscheck copy at 17-MAR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-MAR-14

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_16/o1_mf_1_91_9ld87sbs_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_92_9lfoh799_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_93_9lfxh3fz_.arc
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_94_9lg4lh8j_.arc

searching for all files that match the pattern /u01/app/oracle/oradata/DB12C/datafile

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_temp_9kvh938z_.tmp
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9jw1o6w6_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9jw1nfs3_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jw1mnqh_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9jw1o9b5_.dbf
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_temp_9kvh938z_.tmp
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9jw1o6w6_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9jw1nfs3_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jw1mnqh_.dbf
File Name: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9jw1o9b5_.dbf

My restored files are now known from my controlfile as datafiles copies, what I need to do now, is to switch all my datafiles to these copies :

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jw1mnqh_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9jw1nfs3_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9jw1o6w6_.dbf"
datafile 6 switched to datafile copy "/u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9jw1o9b5_.dbf"

Ok that looks good for the datafiles but what about the onlinelog ?

RMAN> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9jppbtd0_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9jppbtho_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9jppboh7_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9jppbom2_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9jppbkjz_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9jppbko7_.log

[oracle@svr-ora-04 archivelog]$ ls -l /u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9jppbtd0_.log ls: cannot access /u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9jppbtd0_.log: No such file or directory [oracle@svr-ora-04 archivelog]$ ls -l /u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9jppbtho_.log ls: cannot access /u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9jppbtho_.log: No such file or directory [oracle@svr-ora-04 archivelog]$

Same problem, the filenames are from the primary database, I need to clear the redologs register them with the controlfile :

RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 1;

using target database control file instead of recovery catalog
Statement processed

RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Statement processed

RMAN> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Statement processed

RMAN> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_3_9lg68jjz_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_3_9lg68jp0_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_2_9lg68b8w_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_2_9lg68bf1_.log
/u01/app/oracle/oradata/DB12C/onlinelog/o1_mf_1_9lg685bd_.log
/u01/app/oracle/fast_recovery_area/DB12C/onlinelog/o1_mf_1_9lg685nw_.log

RMAN>

Now, all my files are known of my controlfile, lets try to open the standby database again:

RMAN> alter database open read only;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/17/2014 11:41:31
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jw1mnqh_.dbf'

No more luck, but if we think about it for a minute, no archivelogs have been applied since the datafiles restoration, so the database is not intended to be consitent, I’m going to switch current log from the primary and copy the archivelogs to the standby host.

SQL*Plus: Release 12.1.0.1.0 Production on Mon Mar 17 11:52:17 2014

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

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SQL> alter system switch logfile;

System altered.

SQL> exit

[oracle@svr-ora-03 archivelog]$ scp -r 2014_03_17 svr-ora-04:/u01/app/oracle/fast_recovery_area/DB12C/archivelog
oracle@svr-ora-04's password:
o1_mf_1_92_9lfoh799_.arc                                                  100%   41MB  41.3MB/s   00:01
o1_mf_1_93_9lfxh3fz_.arc                                                  100% 2139KB   2.1MB/s   00:00
o1_mf_1_94_9lg4lh8j_.arc                                                  100% 1822KB   1.8MB/s   00:00
o1_mf_1_95_9lg6kwft_.arc                                                  100%  252KB 252.0KB/s   00:00

I have now to register these files to the controlfile to be able to use them :

RMAN> CATALOG START WITH '/u01/app/oracle/fast_recovery_area/DB12C/archivelog' NOPROMPT;

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/DB12C/archivelog

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc

RMAN>

Only the last one has been registered, the others are already registered. The question now is, until which scn will I have to restore the standby database to be consistent, we can use the following RMAN command to find out:

RMAN> recover database preview;

Starting recover at 17-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name DB12C
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
5       1    95      A 17-MAR-14
        Name: /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc

Media recovery start SCN is 2638258
Recovery must be done beyond SCN 2638258 to clear datafile fuzziness
Finished recover at 17-MAR-14

Ok, so lets try to recover our database beyond SCN 2638258, I’ll choose SCN+1 :

RMAN> recover database until scn 2638259;

Starting recover at 17-MAR-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 95 is already on disk as file /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc thread=1 sequence=95
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jw1mnqh_.dbf'

media recovery complete, elapsed time: 00:00:01
Finished recover at 17-MAR-14

It doesn’t looks good, why it is not working ? This is because our controlfile has been restored after our datafiles, so it has a newer SCN than our datafiles, let’s check the current SCN of the controlfile :

RMAN> select controlfile_change# from v$database;

CONTROLFILE_CHANGE#
-------------------
            2638588

We will have to recover the database beyond this SCN to be able to open our standby database :

RMAN>  recover database until scn 2638589;

Starting recover at 17-MAR-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 95 is already on disk as file /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_03_17/o1_mf_1_95_9lg6kwft_.arc thread=1 sequence=95
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-MAR-14

Here we go, our database is finaly consistent, we can open it :

RMAN> alter database open read only;

Statement processed

Conclusion :

This is not a good approach to recover a stanby regularly, first, this method uses incremental backups and as I use the standard edition, block change tracking is not enabled which implies that RMAN have to scan all my datafiles to see what changes have been made every time I recover my standby, this could be problematic for large databases. Secondly it requires many steps to open the standby database, it’s complicated.
The only interesting usage I can see is if you want to recover the standby database from a large gap sequence, in that case, this method should be more efficient than applying all missing archivelogs sequences.

5 thoughts on “Network based recovery (12c new feature)”

fouedgray says:

04/03/2015 at 15:31

Nice article and demonstration. I read another article about it here http://allthingsoracle.com/rolling-forward-a-physical-standby-database-using-the-recover-command/ , and since you are more experienced on this subject than me, can you please tell if their procedure leads also to failure if they try to open read-only ? the author stopped at ” RESTORE STANDBY CONTROLFILE FROM SERVICE boston; ” then mount.
I’m wondering if it is the broker that gives another way to it automatically .
Thanks,
Foued

Cyrille Modiano says:

04/03/2015 at 16:54

Hi Foued,

you’ve got it, when you use an automatic recovery, DG broker takes care of the consistency of your standby.for you.
In my case this was a test to recover manually a standby database. Each time I recover it I need to be sure I can open it.
My expectation was the “RECOVER FROM SERVICE” will recover the standby to be consistent with the primary at the time recovery was started but this is proof it’s not the case 🙂

Cyrille

1. fouedgray says:
  
  04/03/2015 at 19:20
  
  Thanks Cyrille for your quick investigation, this clarifies the process, and put more value on your procedure since broker won’t always be there seeing that a separate server (in a production setup) will be required.
  Merci encore 🙂
  Foued
  
  1. Cyrille MODIANO says:
    
    04/03/2015 at 20:22
    
    Thanks Foued,
    
    You’re right, In my situation I was interested to do this because I have to maintain standby in standard edition without the help of dataguard.
    The interesting thing about network recovery which is not covered by this article is you can do the recover in both way, meaning you can recover a lost datafile in you’re primary using the standby datafile.
    
    see you.
    
    Cyrille
    
mdinh says:

02/07/2016 at 08:00

Excellent article and thanks for sharing.

Oracle DBA Scripts and Articles (Montreal)

This blog is intended to share my knowledge on Oracle and various scripts that I use as an oracle DBA.

Network based recovery (12c new feature)

Configuration for the test

Standby database creation

Recover the standby database

Conclusion :

5 thoughts on “Network based recovery (12c new feature)”

Leave a Reply Cancel reply

Configuration for the test

Standby database creation

Recover the standby database

Conclusion :

You may also like:

5 thoughts on “Network based recovery (12c new feature)”

Leave a Reply Cancel reply