Limitations with automatic RMAN conversion from standby controlfile to primary controlfile in 11.2.0.4

by | 1 Oct 2017 | Oracle, portada, slider-post | 0 comments

11g brought the hability of moving all backup activity to a physical standby database without restrictions, including controlfile copies that are interchangeable between primary and standby databases. This let us backup a standby database covering any primary failure wituout the need of rebuild our controlfile as Oracle will be able to transform it and maintain the original or specified roel we are interested on. As a requisite, we need to configure a RMAN catalog and correctly register our Data Guard configuration on it. This is very important for succesfull backups and specially sucesfull restores. We can find information regarding this role independency for controlfile copies since version 11.1 documentation. Here we can find that metadata information stored inside the catalog is what enables the database to be able to be conscious of datafiles names and configuration all along the Data Guard, being able to cross restore or backup datafiles and controlfiles.

Days ago, performing in a test environment restoration from a standby database, I found this was not the real behaviour, because the database I restored still was seing its controlfile as standby. After additional tests and search for mor information in My Oracle Support, we found we could be hitting bug 18455956, that arises in a restore environment when no RMAN catalog is used. We were trying to perform our test restoration explicitly without using catalog just to avoid aplying changes in database incarnation configuration when opening the restored database with resetlogs option. These tests where performed to validate a backup strategy based completely in backups being taken from the standby database.

So finally, I performed additional tests in a new single instance test environment in Data Guard configuration, using two servers in cluster configuration with Grid Infrastructure, using shared ACFS storage for both database to be able to access the databases taken at the standby location. Primary database was named alberto and standby database was named bertodg (names given by Sergio, to whom I want to thank for providing me in a very fast way this environment using his magic with gdbClone tool!). First step was the execution of a backup database RMAN command connected to bertosg (standby) generating a backup inside the FRA, created in the same filesystem. By doing so, we primary and standby database share the access to backups and there’s no need to perform additional copy or recatalog of backup pieces.

Primary restore from standby copy with RMAN catalog connection

We connect to the alberto database and we also use a catalog connection. In this test our RMAN script is explicitly restoring the controlfile from the backuppiece in standby’s FRA. We took a backup of a standby controlfile, but it is restored at the primary location as a current controlfile, fine for a primary database.There’s no need to tell RMAN anything about this, all is performed automatically.

[oracle@nodo1 ~]$ rman target / catalog=usrcat/xxxxxx@rman
RMAN> run
{
  shutdown immediate;
  startup nomount;
  restore controlfile from '/acfs/desdata/bertodg/BERTODG/backupset/2017_09_29/o1_mf_ncsnf_TAG20170929T120630_dww6sbyk_.bkp';
  alter database mount;
  restore database;
  recover database noredo;
  alter database open resetlogs;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2260088 bytes
Variable Size               2449474440 bytes
Database Buffers            1811939328 bytes
Redo Buffers                  12107776 bytes

Starting restore at 29/09/2017 12:54:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/acfs/desdata/alberto/control01.ctl
Finished restore at 29/09/2017 12:54:13

database mounted
released channel: ORA_DISK_1

Starting restore at 29/09/2017 12:54:17
Starting implicit crosscheck backup at 29/09/2017 12:54:17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 29/09/2017 12:54:18

Starting implicit crosscheck copy at 29/09/2017 12:54:18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 29/09/2017 12:54:18

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

List of Cataloged Files
=======================
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_34_06sfjc3k_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_1_dwsnj7rf_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_2_dwsnjx2d_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_3_dwsnlrhm_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_4_dwsnlvhy_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_5_dwt92r23_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_6_dwt92wjf_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_28/o1_mf_1_7_dwtrr0tq_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_8_dwvggf46_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_1_dww55lnk_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_2_dww55q9x_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_3_dww5rdbj_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_4_dww7z6wb_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_1_dww7z75j_.arc
File Name: /acfs/desdata/alberto/ALBERTO/archivelog/2017_09_29/o1_mf_1_2_dww7zbnc_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=11 STAMP=955976061 file name=/acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_system_03sfjd9o_.dbf
destination for restore of datafile 00001: /acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_system_02sfjc33_.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_system_02sfjc33_.dbf RECID=15 STAMP=955976062
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=12 STAMP=955976061 file name=/acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_sysaux_04sfjd9o_.dbf
destination for restore of datafile 00002: /acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_sysaux_03sfjc33_.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_sysaux_03sfjc33_.dbf RECID=16 STAMP=955976065
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=13 STAMP=955976061 file name=/acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_undotbs1_05sfjd9o_.dbf
destination for restore of datafile 00003: /acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_undotbs1_04sfjc33_.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_undotbs1_04sfjc33_.dbf RECID=17 STAMP=955976068
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=14 STAMP=955976061 file name=/acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_users_06sfjd9r_.dbf
destination for restore of datafile 00004: /acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_users_05sfjc3i_.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_users_05sfjc3i_.dbf RECID=18 STAMP=955976068

datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=955976069 file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_system_02sfjc33_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=955976069 file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_sysaux_03sfjc33_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=955976069 file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_undotbs1_04sfjc33_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=955976069 file name=/acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_users_05sfjc3i_.dbf
Finished restore at 29/09/2017 12:54:29

Starting recover at 29/09/2017 12:54:30
using channel ORA_DISK_1
renamed tempfile 1 to /acfs/desdata/.ACFS/snaps/alberto/ALBERTO/datafile/o1_mf_temp_dwsmc1kp_.tmp in control file

Finished recover at 29/09/2017 12:54:31

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
starting full resync of recovery catalog
full resync complete

Primary restore from standby copy without RMAN catalog connection

Let’s repeat same test, but this time without connecting to the RMAN catalog. Restore and recover database works fine, but it’s not possible to open the database because controlfile was not converted during the process and is still a standby controlfie.

[oracle@nodo1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 1 15:57:04 2017

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

connected to target database: ALBERTO (DBID=2724215542)

RMAN> run
{
  shutdown immediate;
  startup nomount;
  restore controlfile from '/acfs/desdata/bertodg/BERTODG/backupset/2017_09_29/o1_mf_ncsnf_TAG20170929T120630_dww6sbyk_.bkp';
  alter database mount;
  restore database;
  recover database noredo;
  alter database open resetlogs;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2260088 bytes
Variable Size               2449474440 bytes
Database Buffers            1811939328 bytes
Redo Buffers                  12107776 bytes

Starting restore at 01/10/2017 15:57:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/acfs/desdata/alberto/control01.ctl
Finished restore at 01/10/2017 15:57:25

database mounted
released channel: ORA_DISK_1

Starting restore at 01/10/2017 15:57:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK

skipping datafile 1; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_system_03sfjd9o_.dbf
skipping datafile 2; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_sysaux_04sfjd9o_.dbf
skipping datafile 3; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_undotbs1_05sfjd9o_.dbf
skipping datafile 4; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_users_06sfjd9r_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 01/10/2017 15:57:31

Starting recover at 01/10/2017 15:57:31
using channel ORA_DISK_1

Finished recover at 01/10/2017 15:57:32

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/01/2017 15:57:33
ORA-01666: control file is for a standby database

We can check that we have restores a STANDBY CONTROLFILE, this is not what we are looking for as we are not trying to change the role of our primary database.

SQL> select controlfile_type from v$database;

CONTROL
-------
STANDBY

Bug 18455956

It could be OK that Oracle do not convert the standby controlfile when we are not connected to the catalog, just because it has no way to tell the role of the database as this information is inside the catalog. In fact, RESTORE CONTROLFILE command can be manually performed, since 11gR1, in 3 different ways:

  • RESTORE CONTROLFILE. Controlfile is restored into the database, and the role is determined by means of gathering this information form the recovery catalog. The role will remain the same the database had before starting restoration.
  • RESTORE PRIMARY CONTROLFILE. We force the controlfile to be transformed into a primary controlfile without caring if it was originally a primary or standby one.
  • RESTORE STANDBY CONTROLFILE. Controlfile will be converted into a standby controlfile.

So we have the choice of automatically restore a controlfile depending on the role of the database we are connected to if we use a recovery catalog, and alsa can specify the role we want this restored controlfile to be converted to if we just want to use a backup to create a new primary or standby database independently of what kind of controlfile we have. Taking a look at MOS note Step by Step method to create Primary/Standby Database from Standby Backup (Doc ID 1604251.1), we see that as consequence of bug 7553431, a new bug arises after applying the patch, 18455956. Without path 18455956 applied, RESTORE PRIMARY CONTROLFILE command won’t convert a standby controlfile if we are not using recovery catalog when restoring from a standby controlfile. Using a recovery catalog is just the workaround for avoinding this issue.

In our test case, we verify patch 18455956 is not applied in our 11.2.0.4 April’s 2017 PSU level.

[oracle@nodo1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | grep "Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Sub-patch  24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"
Sub-patch  23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"
Sub-patch  22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Sub-patch  21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"

[oracle@nodo1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | grep 18455956
[oracle@nodo1 ~]$

We check we are hitting this bug by performing another test using RESTORE PRIMARY CONTROLFILE:

[oracle@nodo1 ~]$ rman target /
 
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 1 15:58:16 2017
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ALBERTO (DBID=2724215542)

RMAN> run
{
  shutdown immediate;
  startup nomount;
  restore primary controlfile from '/acfs/desdata/bertodg/BERTODG/backupset/2017_09_29/o1_mf_ncsnf_TAG20170929T120630_dww6sbyk_.bkp';
  alter database mount;
  restore database;
  recover database noredo;
  alter database open resetlogs;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 

database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2260088 bytes
Variable Size               2449474440 bytes
Database Buffers            1811939328 bytes
Redo Buffers                  12107776 bytes

Starting restore at 01/10/2017 15:58:17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/acfs/desdata/alberto/control01.ctl
Finished restore at 01/10/2017 15:58:19

database mounted
released channel: ORA_DISK_1

Starting restore at 01/10/2017 15:58:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 device type=DISK

skipping datafile 1; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_system_03sfjd9o_.dbf
skipping datafile 2; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_sysaux_04sfjd9o_.dbf
skipping datafile 3; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_undotbs1_05sfjd9o_.dbf
skipping datafile 4; already restored to file /acfs/desdata/.ACFS/snaps/bertodg/BERTODG/datafile/o1_mf_users_06sfjd9r_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 01/10/2017 15:58:24

Starting recover at 01/10/2017 15:58:24
using channel ORA_DISK_1

Finished recover at 01/10/2017 15:58:25

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/01/2017 15:58:25
ORA-01666: control file is for a standby database

Alternatives when we are not using recovery catalog

Now we know the problem, we can use different aproaches as a workaround if we want to perfom a recovery without connecting to RMAN catalog.

It can be interesting taking in care we can just open the database in this state in read only mode. This enables checking the data if that is just what we need.

SQL> alter database open read only;

Database altered.

We can recreate the controlfile from the satdnby (backup controlfile to trace) and adapt it to recreate the primary version. Another solution is to open the restored standby database as a primary database, by forcing a failover. This is what we test in the next test. Before that, we clear the standby logs and disable flashback database to avoid errors when trying to open the database:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER									    IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
	 3	   ONLINE  /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_3_dwsnjzp0_.log		    NO
	 2	   ONLINE  /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_2_dwsnjzkj_.log		    NO
	 1	   ONLINE  /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_1_dwsnjzdr_.log		    NO
	 4	   STANDBY /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_4_dww5rdg9_.log		    NO
	 5	   STANDBY /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_5_dww5rdgy_.log		    NO
	 6	   STANDBY /acfs/desdata/bertodg/BERTODG/onlinelog/o1_mf_6_dww5rdg2_.log		    NO


SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database clear logfile group 6;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select controlfile_type from v$database;

CONTROL
-------
CURRENT

Summary

We must use a recovery catlaog when performing backups in a Data Guard configuration. Anyway, there are specific scenarios where a connection to the catalog is not desirable, just to avoid any disturb in the real state of production backups. If this is our case, we must be conscius, if we are using 11.2.0.4 (this issue does not affect 11.2.0.3 or earlier versions) of the existence of bug 18455856 that will make us choose between applying the software patch, or planning the restore operations with this handicup in mind.

Credits

Ilustración: Alicia Constela

Submit a Comment

Your email address will not be published. Required fields are marked *

14 − nine =