RMAN 11g : How To Restore / Duplicate To A More Recent Patchset

In an Oracle DBA's life, you'll be asked to work on applying new patchsets on databases. The natural way to achieve this goal is to start patching the DEVelopment database then the QA database and eventually the Production database. And this process can be quite long depending on the organization you are working for; you will then live a certain amount of time with a more recent patchset on your DEV database than on your Prod database:

You may also be asked to refresh a development database (or any environment before the production) with the production data for development, test or whatever needs:

And what should happen always happens and one day you will be asked to refresh your more recent patchset DEV database (let's say 11.2.0.4) with your PROD data (let's say that it's running against an 11.2.0.3 version). And let's call a spade a spade, that could be a bit tricky -- especially if you discover that the versions are different once the RESTORE / DUPLICATE is terminated because you have started the usual refresh scripts forgetting this little detail.

A solution could be to :

• Ask some GB to the sys admin team
• Copy the Prod 11.2.0.3 ORACLE_HOME to the DEV server and clone it on the DEV server
• Start a RMAN DUPLICATE / RESTORE DATABASE from the 11.2.0.3 PROD to the 11.2.0.3 DEV
• Upgrade the prod copy to 11.2.0.4

I won't go in too many details here but this is not a situation we want; indeed, this would probably be quite long, adding some GB to a server may require some procedures, validations, etc... And this possibility does not exist if you find the version difference after the RESTORE / DUPLICATE is finished.

Hopefully, there's a way to achieve this goal by directly RESTORE / DUPLICATE a database to a more recent patchset (note that this method also works for 10g databases). Let's explore the two cases you can face doing a direct RESTORE / DUPLICATE to a more recent patchset database.

1/ RESTORE / DUPLICATE DATABASE case

Whether we are restoring or duplicating the production database from a backup, here is what will happen on the DEV database:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2015 22:38:59
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 2232530
RMAN

Here, we can’t open the database with the RESETLOGS option due to the patchset version difference. We have to use a slightly different command:

SQL> alter database open resetlogs upgrade ;
Database altered.
SQL>

Now the database is opened in upgrade mode, we can now apply the 11.2.0.4 patchset and open it.

SQL> @?/rdbms/admin/catupgrd
...
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 243270696 bytes
Database Buffers 377487360 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
SQL>

This one was actually quick and easy.

2/ DUPLICATE FROM ACTIVE DATABASE case

Starting from 11g, we have the cool DUPLICATE FORM ACTIVE DATABASE feature that we can also use to perform this kind of refresh. When you perform a DUPLICATE FROM ACTIVE DATABASE operation from a 11.2.0.3 to a 11.2.0.4 version, the procedure is different from the previous one as the RESETLOGS will begin but will not be able to finish properly and you will face this error :

RMAN-08161: contents of Memory Script:
{
 Alter clone database open resetlogs;
}
RMAN-08162: executing Memory Script
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 03/25/2015 20:22:56
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 24341
Session ID: 1 Serial number: 9

At this stage, it’s not possible to open the database in UPGRADE mode nor RECOVER the database and not even generate a BACKUP CONTROLFILE TO TRACE.

SQL> recover database using backup controlfile until cancel ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL>

So we have to recreate the controlfile. By using these queries, we can easily create a new CREATE CONTROLFILE statement (or we could generate a BACKUP CONTROLFILE TO TRACE from the source database and then adapt it for the destination database).

SQL> select name from v$datafile order by file#;
SQL> select group#, member from v$logfile;
SQL> select name, bytes from v$tempfile order by file#;

And then recreate the controlfile:

SQL> CREATE CONTROLFILE REUSE DATABASE "TST11204" RESETLOGS ARCHIVELOG
 2 MAXLOGFILES 16
 3 MAXLOGMEMBERS 3
 4 MAXDATAFILES 100
 5 MAXINSTANCES 8
 6 MAXLOGHISTORY 292
 7 LOGFILE
 8 GROUP 1 '/u01/app/oracle/data/orcl11204/redo01.log' SIZE 50M BLOCKSIZE 512,
 9 GROUP 2 '/u01/app/oracle/data/orcl11204/redo02.log' SIZE 50M BLOCKSIZE 512,
 10 GROUP 3 '/u01/app/oracle/data/orcl11204/redo03.log' SIZE 50M BLOCKSIZE 512
 11 DATAFILE
 12 '/u01/app/oracle/data/orcl11204/system01.dbf',
 13 '/u01/app/oracle/data/orcl11204/sysaux01.dbf',
 14 '/u01/app/oracle/data/orcl11204/undotbs01.dbf',
 15 '/u01/app/oracle/data/orcl11204/users01.dbf'
CHARACTER SET AL32UTF8
 16 ;
Control file created.
SQL>

To finish the recover and open the database in UPGRADE mode, we would need to apply the current redolog (and not any archivelog -- we don’t have any archivelog as the RESETLOGS didn’t happen yet).

SQL> select * from v$logfile ;
 GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
 3 STALE ONLINE /u01/app/oracle/data/orcl11204/redo03.log NO
 2 STALE ONLINE /u01/app/oracle/data/orcl11204/redo02.log NO
 1 STALE ONLINE /u01/app/oracle/data/orcl11204/redo01.log NO
 
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel ;
ORA-00279: change 2059652 generated at 03/25/2015 20:22:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/data/TST11204/archivelog/2015_03_25/o1_mf_1_1_%u_.arc
ORA-00280: change 2059652 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/data/orcl11204/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs upgrade ;
Database altered.
SQL>

Now we can apply the 11.2.0.4 patchset:

SQL> @?/rdbms/admin/catupgrd
...
SQL>

And check that everything is good:

SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select comp_name, version, status from dba_registry ;
COMP_NAME VERSION STATUS
--------------------------------------------- ------------------------------ -----------
OWB 11.2.0.3.0 VALID
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 INVALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 INVALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 INVALID
Oracle OLAP API 11.2.0.4.0 VALID

18 rows selected.

SQL>

This saved me a lot of time !