RMAN 11g : How to restore / duplicate to a more recent patchset

In an Oracle DBA's life, you'll be regularly asked to work on applying a new patchset on a database and then you will apply it starting from the development database to the production database and this process can be quite long depending on the organization you are working for.

In an Oracle DBA's life, you'll be regularly asked to refresh a development database (or any environment before the production) with the production data for development, test or whatever needs. For years now, RMAN has helped us a lot to perform this kind of task easily.

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—and specially if you discover that the versions are different once the RESTORE / DUPLICATE is terminated because you have launched the usual refresh scripts forgetting this little detail...

A solution could be to ask some GB to the sys admin team, copy an 11.2.0.3 ORACLE_HOME from another server, quickly 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 and then upgrade it to 11.2.0.4. But this will probably be quite long and in the case that adding some GB to a server requires some procedures, validations, etc... it could take many days to refresh the DEV database which is obviously not what everybody wants. And this possibility does not exists if you face the issue 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 is also working for 10g databases). Let's explore the two cases you can face doing a direct RESTORE / DUPLICATE to a more recent patchset database.

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  

Opening the Database for Upgrade

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 is in fact quick and easy.

DUPLICATE FROM ACTIVE DATABASE case

Starting from 11g, we have the cool DUPLICATE FROM 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>  

Recreating the Controlfile

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>  

Final Recovery and Upgrade Application

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: {<ret>=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  ... 18 rows selected.  

This saved me a lot of time, have a good day :)

Cassandra Database Consulting

Ready to handle massive data volumes with zero downtime?