Oracle Database 12c: Network Recovery in RMAN

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:

[email protected] --> (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:

[email protected] --> (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
[email protected]> (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:

[email protected]> (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

[email protected]> (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

[email protected]> (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

[email protected]> (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/[email protected] 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.