Skip to content

Insight and analysis of technology and business strategy

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 with your PROD data (let's say that it's running against an 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 ORACLE_HOME from another server, quickly clone it on the DEV server, start a RMAN DUPLICATE / RESTORE DATABASE from the PROD to the DEV and then upgrade it to 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.  


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 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.  


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 to a 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 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 - 64bit Production PL/SQL Release - Production CORE Production TNS for Linux: Version - Production NLSRTL Version - Production SQL> select comp_name, version, status from dba_registry ; COMP_NAME VERSION STATUS --------------------------------------------- ------------------------------ ----------- OWB VALID Oracle Application Express VALID Oracle Enterprise Manager VALID OLAP Catalog INVALID Spatial VALID Oracle Multimedia VALID Oracle XML Database VALID Oracle Text VALID Oracle Expression Filter VALID Oracle Rules Manager VALID Oracle Workspace Manager VALID Oracle Database Catalog Views VALID Oracle Database Packages and Types INVALID JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID OLAP Analytic Workspace INVALID Oracle OLAP API VALID 18 rows selected. SQL> &nbsp; [/code]   This saved me a lot of time, have a good day :)   Discover more about our expertise in Oracle.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!