Oracle Database 12c: Network Recovery in RMAN

Aug 14, 2013 / By Michael Abbey

Tags: , , , , , , , ,

Over the past 8 months, I have had the pleasure of working on a soon-to-be-released update for our popular Beginner’s Guide for Oracle Database 12c. The publisher was looking for a “new brand” for this book, which has been published for Oracle 7, Oracle 8, Oracle 8i, Oracle 9, Database 10g, and Database 11g. The works have been translated into 15 languages; I have always wanted to get a translation done back into English and see what it says :).

The new work is entitled Oracle Database 12c: Install, Configure, and Maintain Like a Professional. I have had the pleasure of sharing the author responsibilities with colleagues in and outside of Pythian. Ian Abramson, Michelle Malcher, and Mike Corey are the primary authors alongside yours truly. Michael McKee, Fahd Mirza, and Marc Fielding of Pythian are contributing authors to this latest work.


Many of the new features have fascinated me and my peers; rman has been a friend and good companion since a business case in 2000 provided the opportunity to get “dirty” with this product. (Thanks Steve Jacobs, wherever you are.) I have thirsted in particular for the following handful of rman enhancements that are bundled with Database 12c:

  • Multisection incremental backups where the data files can be broken up into smaller chunks that can be backed up in parallel across multiple channels.
  • Network-enabled restore – copying of one or more database files from a primary to a physical standby or vice-versa over the network. The work can be done using compression and the new multi-section feature.

Not long before intensive work began on the new book, Pythian invested a huge amount of money and time in setting up a Private Cloud environment called Delphic Lab. With its emergence under the auspices of the Office of the CTO and dedicated volunteers, it was bye-bye to local Linux VMs on my two MacBooks for me. I do not miss them in the least.

Prep of VM

The setup to house the databases used to thrash the new features covered in this post is as follows:

O/S

Oracle Linux Server release 6.4 Linux dlabvm46.dlab.pythian.com

Disk space 

Filesystem   1K-blocks    Used Available Use% Mounted on 
/dev/xvda2     9823392 1206832   8118056  13% / 
tmpfs          1005964       0   1005964   0% /dev/shm 
/dev/xvda1      497829   63408    408719  14% /boot 
/dev/xvdb      1113160 7553396  40963364  `6% /u01 
Database creation (dbca.cmd)
/u01/app/oracle/product/12.1.0/db_1/bin/dbca -silent 
-createDatabase 
-templateName General_Purpose.dbc 
-gdbName pythian 
-sid pythian 
-createAsContainerDatabase false 
-SysPassword manager 
-SystemPassword manager 
-emConfiguration NONE 
-datafileDestination /u01/oradata 
-storageType FS 
-characterSet AL32UTF8 
-memoryPercentage 40 

When dbca.cmd was run, the output displayed was:

oracle@dlabvm46.dlab.pythian.com --> (pythian)
/home/oracle> ./pythian.cmd
Copying database files
1% complete
2% complete
8% complete
13% complete
19% complete
24% complete
27% complete
Creating and starting Oracle instance
29% complete
32% complete
33% complete
34% complete
38% complete
42% complete
43% complete
45% complete
Completing Database Creation
48% complete
51% complete
53% complete
62% complete
70% complete
72% complete
78% complete
83% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/pythian/pythian.log" for further details.

To perform the testing, I put the database in archivelog mode as follows:

oracle@dlabvm46.dlab.pythian.com --> (pythian)
/home/oracle> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 3 11:44:08 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Current log sequence           15
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  822579200 bytes
Fixed Size                  2293736 bytes
Variable Size             595591192 bytes
Database Buffers          218103808 bytes
Redo Buffers                6590464 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15

SQL>

The following Oracle*Net files were created:

********************************
* Oracle*Net configuration files
********************************

** on the primary

# listener.ora

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=dlabvm46)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=pythian)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME=pythian)
    )
  )

# tnsnames.ora

PYTHIAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =  
        (PROTOCOL = TCP)
        (HOST = dlabvm46.dlab.pythian.com)    
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pythian) 
    )
  )

PYTHIANSB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =  
        (PROTOCOL = TCP)
        (HOST = dlabvm48.dlab.pythian.com)    
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pythian) 
    )
  )

** on the standby

# listener.ora

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=dlabvm48)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=pythian)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
      (SID_NAME=pythian)
    )
  )

# tnsnames.ora

PYTHIAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =  
        (PROTOCOL = TCP)
        (HOST = dlabvm48.dlab.pythian.com)    
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pythian) 
      (UR = A)
    )
  )

PYTHIANPR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =  
        (PROTOCOL = TCP)
        (HOST = dlabvm46.dlab.pythian.com)    
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pythian) 
    )
  )

Build the physical standby

Next, the standby was prepared to house the duplicated primary.

** Prepare standby for duplication
oracle@dlabvm48.dlab.pythian.com--> (pythian) ** Standby **
/u01/app/oracle/product/12.1.0/db_1/network/admin> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 18:42:31 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> startup nomount
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2288968 bytes
Variable Size             331350712 bytes
Database Buffers           71303168 bytes
Redo Buffers                4251648 bytes
SQL>

Time to create the standby: first, a status reality check.

Status of primary database: OPEN
Status of standby database: NOMOUNT

The duplicate performed from the primary site:

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/home/oracle> rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Aug 7 18:47:38 2013

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

RMAN> connect target sys/******

connected to target database: PYTHIAN (DBID=2086712234)

RMAN> connect auxiliary sys/******@pythiansb

connected to auxiliary database: PYTHIAN (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 07-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwpythian' auxiliary format 
 '/u01/app/oracle/product/12.1.0/db_1/dbs/orapwpythian'   ;
}
executing Memory Script

Starting backup at 07-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 07-AUG-13

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/oradata/pythian/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/pythian/control02.ctl' from 
 '/u01/oradata/pythian/control01.ctl';
}
executing Memory Script

Starting backup at 07-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_pythian.f tag=TAG20130807T184803
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-AUG-13

Starting restore at 07-AUG-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-AUG-13

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 tempfile  1 to 
 "/u01/oradata/pythian/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/oradata/pythian/system01.dbf";
   set newname for datafile  3 to 
 "/u01/oradata/pythian/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/oradata/pythian/undotbs01.dbf";
   set newname for datafile  6 to 
 "/u01/oradata/pythian/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/oradata/pythian/system01.dbf"   datafile
 3 auxiliary format 
 "/u01/oradata/pythian/sysaux01.dbf"   datafile 
 4 auxiliary format 
 "/u01/oradata/pythian/undotbs01.dbf"   datafile 
 6 auxiliary format 
 "/u01/oradata/pythian/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oradata/pythian/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-AUG-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/pythian/system01.dbf
output file name=/u01/oradata/pythian/system01.dbf tag=TAG20130807T184815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/oradata/pythian/sysaux01.dbf
output file name=/u01/oradata/pythian/sysaux01.dbf tag=TAG20130807T184815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/pythian/undotbs01.dbf
output file name=/u01/oradata/pythian/undotbs01.dbf tag=TAG20130807T184815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/oradata/pythian/users01.dbf
output file name=/u01/oradata/pythian/users01.dbf tag=TAG20130807T184815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-AUG-13

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=822855043 file name=/u01/oradata/pythian/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=822855043 file name=/u01/oradata/pythian/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=822855043 file name=/u01/oradata/pythian/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=822855043 file name=/u01/oradata/pythian/users01.dbf
Finished Duplicate Db at 07-AUG-13

RMAN>

In most cases, the next step would be to start managed recovery on the standby site. However, we will do some manual recovery to ensure log transport services are working as expected. We will test the arrival and manual application of archived redo on the standby site as follows:

  1. Switch logfile a few times on the primary.
  2. Run command to discover the status of redo to ascertain:
    • oldest online log sequence
    • next log sequence to archive
    • current log sequence
  3. Recover the standby database to confirm arrival of archived redo.
  4. Allow recovery to abend when it runs out of archived redo.

Manual recovery test for proof-of-concept:

** Switch logfiles on primary

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07> !sqlp
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 18:58:16 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> archive log list    
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Next log sequence to archive   21
Current log sequence           22

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22

SQL>

If all goes well, we should be able to toddle off to the standby and perform manual recovery expecting successful application of archived log sequences 19 and 20. The envelope, please…

Recovery test on the standby:

** Recover standby manually

oracle@dlabvm48.dlab.pythian.com--> (pythian) ** Standby **
/u01/app/oracle> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 7 18:58:03 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> recover standby database
ORA-00279: change 1882090 generated at 08/07/2013 18:57:22 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_20_%u_.
arc
ORA-00280: change 1882090 for thread 1 is in sequence #20

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1882266 generated at 08/07/2013 18:58:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_21_%u_.
arc
ORA-00280: change 1882266 for thread 1 is in sequence #21
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_20_905
n6lw5_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1884132 generated at 08/07/2013 19:09:24 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_22_%u_.
arc
ORA-00280: change 1884132 for thread 1 is in sequence #22
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_21_905
n9gfc_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1884132 generated at 08/07/2013 19:09:24 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_22_%u_.
arc
ORA-00280: change 1884132 for thread 1 is in sequence #22
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_07/o1_mf_1_21_905
n9gfc_.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-16145: archival for thread# 1 sequence# 22 in progress

Network-based recovery

Looks like the stage is set to try recovering the standby from the primary. A few tasks are to be performed beforehand, then away we go. Each task is outlined in the following code:

Task #1: get the service name for the standby database
From standby site

sqlplus / as sysdba
show parameters service

Task #2: verify the open mode and that this is a physical standby
From standby site

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

Task #3: verify managed recovery is not running
From standby site

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
SQL>

Task #4: create a new table on the primary site
From primary site

SQL> create table tester1 as select * from obj_1

Table created.

Task #5: switch a few logfiles
From primary site

SQL> alter system switch logfile;

System altered.
SQL> alter system switch logfile;

System altered.
SQL>

Task #6: verify TESTER1 table not there
From standby site

SQL> alter database open read only;

Database altered.

SQL> desc pythian.tester1
ERROR:
ORA-04043: object pythian.tester1 does not exist

Task #7: put standby back in MOUNT mode
From standby site

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

Total System Global Area  409194496 bytes
Fixed Size                  2288968 bytes
Variable Size             331350712 bytes
Database Buffers           71303168 bytes
Redo Buffers                4251648 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNT                PHYSICAL STANDBY

Task #8: perform network-based recovery
From primary site

oracle@dlabvm46.dlab.pythian.com--> (pythian) ** Master **
/home/oracle> rman

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Aug 13 16:22:51 2013

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

RMAN> connect target "sys/m3n3g3r@pythiansb as sysdba"

connected to target database: PYTHIAN (DBID=2086712234, not open)

RMAN> recover database
2> from service pythian
3> section size 120m
4> using compressed backupset;

Starting recover at 13-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
skipping datafile 1; already restored to SCN 1997353
skipping datafile 3; already restored to SCN 1997353
skipping datafile 4; already restored to SCN 1997353
skipping datafile 6; already restored to SCN 1997353

starting media recovery

archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_30_90o56dp6_.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_31_90o56dyj_.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_32_90o56k6p_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_30_90o56dp6_.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_31_90o56dyj_.arc thread=1 sequence=31
archived log file name=/u01/app/oracle/fast_recovery_area/PYTHIAN/archivelog/2013_08_13/o1_mf_1_32_90o56k6p_.arc thread=1 sequence=32
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-AUG-13

Task #9: open standby read only and verify TESTER1 is there
From standby site

SQL> alter database open read only;

Database altered.

SQL> desc pythian.tester1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJ#                                      NOT NULL NUMBER
DATAOBJ#                                           NUMBER
OWNER#                                    NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(128)
NAMESPACE                                 NOT NULL NUMBER
SUBNAME                                            VARCHAR2(128)
TYPE#                                     NOT NULL NUMBER
CTIME                                     NOT NULL DATE
MTIME                                     NOT NULL DATE
STIME                                     NOT NULL DATE
STATUS                                    NOT NULL NUMBER
REMOTEOWNER                                        VARCHAR2(128)
LINKNAME                                           VARCHAR2(128)
FLAGS                                              NUMBER
OID$                                               RAW(16)
SPARE1                                             NUMBER
SPARE2                                             NUMBER
SPARE3                                             NUMBER
SPARE4                                             VARCHAR2(1000)
SPARE5                                             VARCHAR2(1000)
SPARE6                                             DATE
SIGNATURE                                          RAW(16)
SPARE7                                             NUMBER
SPARE8                                             NUMBER
SPARE9                                             NUMBER

SQL> select count(*) from pythian.tester1;

COUNT(*)
----------
90775

SQL>

Closing remarks

Only testing and time will confirm the usability and time saving of this new-fangled feature. This blog post has shown an example of an alternative way to recover a physical standby database. Up until 12c, archived redo was the only way to do it other than shutting down the standby and refreshing its database files. Gone are the days of the forever-flashing cursor as a recovery exercise plows through days of archived redo. This example showed how a standby is caught up with its primary. Network-based recovery can be used as well to replace missing datafiles, control files, or tablespaces on the primary using the corresponding entity from the physical standby.

7 Responses to “Oracle Database 12c: Network Recovery in RMAN”

  • Thanks Michael for posting the example!
    As I see from the example the recovery did not go with incremental backups but rather copied and applied redo logs.
    Do you know what is going to be if some of redo logs are not accessible neither on primary nor on standby? Will it go with incremental backup from SCN?
    Cheers,
    Andrey

  • dinh says:

    Love it.
    dbca.cmd never used silent of this version before, but looks nice and easy.
    stealing this blog.

  • Monowar Mukul says:

    Hi Andrey,
    I think rman incremental ( backup from scn and controlfile backup from primary and then restore controlfile on standby and recover after that) is the good option for that.
    If you use ‘far sync’ then you can check from that instance.

    Thanks

  • Cyrille says:

    Hi,

    I try to apply this technique with a manual standby, when I try to recover fro the service it work great :

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

    Recovery Manager: Release 12.1.0.1.0 – Production on Tue Feb 25 10:11:52 2014

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

    connected to target database: DB12C (DBID=1294385856, not open)

    RMAN>

    Starting recover at 25-FEB-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1 device type=DISK
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service DB12C_PRIMARY
    destination for restore of datafile 00001: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_9jpp643g_.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service DB12C_PRIMARY
    destination for restore of datafile 00003: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_9jpp34ky_.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service DB12C_PRIMARY
    destination for restore of datafile 00004: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_9jpp9rxv_.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service DB12C_PRIMARY
    destination for restore of datafile 00006: /u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_9jpp9qo7_.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

    starting media recovery

    media recovery complete, elapsed time: 00:00:00
    Finished recover at 25-FEB-14

    but i’m not able to open my standby database in read only mode, it is still not consistent :

    SQL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    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_9jpp643g_.dbf’

    SQL> recover standby database;
    ORA-00279: change 1797454 generated at 02/25/2014 10:01:47 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_02_25/o1_mf_1_33_%u_.ar
    c
    ORA-00280: change 1797454 for thread 1 is in sequence #33

    Specify log: {=suggested | filename | AUTO | CANCEL}

    sequence 33 is not the right one, he is the current one from the primary :

    SQL> select SEQUENCE# from v$log where status=’CURRENT’;

    SEQUENCE#
    ———-
    36

    I saw in oracle documentation that I should restore the controlfile as well, so I tried :

    ecovery Manager: Release 12.1.0.1.0 – Production on Tue Feb 25 10:20:04 2014

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

    connected to target database: DB12C (DBID=1294385856, not open)

    RMAN> shutdown immediate

    using target database control file instead of recovery catalog
    startup nomodatabase dismounted
    unt;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> RESTORE STANDBY CONTROLFILE FROM SERVICE DB12C_PRIMARY;

    Starting restore at 25-FEB-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:01
    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 25-FEB-14

    Now I try again :

    QL> alter database open read only;
    alter database open read only
    *
    ERROR at line 1:
    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_9jpp643g_.dbf’

    QL> recover standby database ;
    ORA-00279: change 1797858 generated at 02/25/2014 10:11:55 needed for thread 1
    ORA-00289: suggestion :
    /u01/app/oracle/fast_recovery_area/DB12C/archivelog/2014_02_25/o1_mf_1_36_%u_.ar
    c
    ORA-00280: change 1797858 for thread 1 is in sequence #36

    now it asks for the good sequence but I’m still not able to open my standby, do you have a clue ?

    Thanks

    Cyrille

  • oracleman consulting says:

    Task #1: get the service name for the standby database
    From standby site

    sqlplus / as sysdba
    show parameters service

    (do not see the output from this)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>