Apache Cassandra Tombstones

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

5 min read

Jul 20, 2015

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: [code] 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 [/code] 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: [code] SQL> alter database open resetlogs upgrade ; Database altered. SQL> [/code] Now the database is opened in upgrade mode, we can now apply the 11.2.0.4 patchset and open it. [code] 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> [/code] This one is in fact quick and easy.

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 : [code] 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 [/code] 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. [code] 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> [/code] 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). [code] 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#; [/code] And then recreate the controlfile: [code] 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> [/code] 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). [code] 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> [/code] Now we can apply the 11.2.0.4 patchset: [code] SQL> @?/rdbms/admin/catupgrd ... SQL> [/code] And check that everything is good: [code] 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>   [/code] This saved me a lot of time, have a good day :) Discover more about our expertise in Oracle.