Manual Upgrade from 11g to 12c
There is nice new feature for duplicate option presented in Oracle 12c release. The option is NOOPEN which leaves database closed after duplicate operation is done.
https://docs.oracle.com/database/121/BRADV/rcmdupdb.htm#BRADV010
This is useful in cases when you need to do upgrade to Oracle12c on different server where former Oracle RDBMS release is not installed.
Test case:
- server A - oracle database 11.2.0.4, disk backup (direct upgrade to 12c can be done from 10.2.0.5 as well)
- server B - oracle 12c software installed, backup from server A available (it could be done also using tape backup, but in our case I used disk backup)
Prerequisites
- archive log mode enabled on 11.2.0.4 database
- executed preupgrd.sql script on 11.2.0.4 database following all steps needed as per upgrade guide - mandatory
- (otherwise upgrade fail)
- full online disk backup done including archive backups
- disk backup available on server B
Steps on server A and server B:
- prepare tnsnames.ora, listener.ora and password files so database clone can be done (more information in official Oracle documentation)
Steps on server A:
- enable archive log mode if not done yet
- execute preupgrd.sql - there is need to copy preupgrd.sql and utluppkg.sql from 12c $OH/rdmbs/admin to server A
- solve ERROR messages and run fixup scripts
- run online backup to disk
- copy or make available disk backup on server B
- copy post upgrade scripts generated in step 2 to server B
Steps on server B:
- Prepare init ora file for new database, in our case I used following init ora parameters to rename datafiles and redo log files during clone:
alter system set db_file_name_convert='/opt/oracle/oradata/orcl11du/','/opt/oracle/oradata/orcl12du/' scope=spfile; alter system set log_file_name_convert='/opt/oracle/oradata/orcl11du/','/opt/oracle/oradata/orcl12du/' scope=spfile; startup NOMOUNT auxiliary database
- connect to rman and make auxiliary connection to new 12c database (in our case named ORCL12DU)
RMAN> connect auxiliary sys/sys@orcl12du connected to auxiliary database: ORCL12DU (not mounted)- execute following command (backup location is location where is stored online backup done on 11.2 database
run { duplicate database to orcl12du noopen backup location '/backup/orcl11du/'; }
- from sqlplus open database with resetlogs upgrade command:
alter database open resetlogs upgrade;That's all. You did it! Now you can continue with manual upgrade steps as described in upgrade guide: https://docs.oracle.com/database/121/UPGRD/toc.htm
Outlook
[oracle@localhost admin]$ rman Recovery Manager: Release 12.1.0.1.0 - Production on Thu Mar 9 09:13:28 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. RMAN> connect auxiliary sys/sys@orcl12du connected to auxiliary database: ORCL12DU (not mounted) RMAN> run { duplicate database to orcl12du noopen backup location '/backup/orcl11du/'; }2> 3> Starting Duplicate Db at 09-MAR-17 contents of Memory Script: { sql clone "alter system set db_name = ''ORCL11'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''ORCL12DU'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile from '/backup/orcl11du/orcl11du_ctl_01.bkp'; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''ORCL11'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''ORCL12DU'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 801701888 bytes Fixed Size 2293496 bytes Variable Size 591397128 bytes Database Buffers 201326592 bytes Redo Buffers 6684672 bytes Starting restore at 09-MAR-17 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 device type=DISK channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/opt/oracle/oradata/orcl12du/control01.ctl output file name=/opt/oracle/oradata/orcl12du/control02.ctl Finished restore at 09-MAR-17 database mounted released channel: ORA_AUX_DISK_1 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=12 device type=DISK contents of Memory Script: { set until scn 1756064; set newname for datafile 1 to "/opt/oracle/oradata/orcl12du/system01du.dbf"; set newname for datafile 2 to "/opt/oracle/oradata/orcl12du/sysaux01du.dbf"; set newname for datafile 3 to "/opt/oracle/oradata/orcl12du/undotbs01du.dbf"; set newname for datafile 4 to "/opt/oracle/oradata/orcl12du/users01du.dbf"; set newname for datafile 5 to "/opt/oracle/oradata/orcl12du/standby.dbf"; restore clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 09-MAR-17 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/orcl12du/system01du.dbf channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/orcl12du/sysaux01du.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/orcl12du/undotbs01du.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/orcl12du/users01du.dbf channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/orcl12du/standby.dbf channel ORA_AUX_DISK_1: reading from backup piece /backup/orcl11du/orcl11du_3eruhhfm_1_1 channel ORA_AUX_DISK_1: piece handle=/backup/orcl11du/orcl11du_3eruhhfm_1_1 tag=FOR_UPGRADE channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25 Finished restore at 09-MAR-17 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=938164572 file name=/opt/oracle/oradata/orcl12du/system01du.dbf datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=938164573 file name=/opt/oracle/oradata/orcl12du/sysaux01du.dbf datafile 3 switched to datafile copy input datafile copy RECID=8 STAMP=938164573 file name=/opt/oracle/oradata/orcl12du/undotbs01du.dbf datafile 4 switched to datafile copy input datafile copy RECID=9 STAMP=938164574 file name=/opt/oracle/oradata/orcl12du/users01du.dbf datafile 5 switched to datafile copy input datafile copy RECID=10 STAMP=938164574 file name=/opt/oracle/oradata/orcl12du/standby.dbf contents of Memory Script: { set until scn 1756064; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 09-MAR-17 using channel ORA_AUX_DISK_1 starting media recovery channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=4 channel ORA_AUX_DISK_1: reading from backup piece /backup/orcl11du/orcl11du_3fruhhh5_1_1 channel ORA_AUX_DISK_1: piece handle=/backup/orcl11du/orcl11du_3fruhhh5_1_1 tag=TAG20170307T120557 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/backup/archives/orcl12du_1_4_880226544.dbf thread=1 sequence=4 channel clone_default: deleting archived log(s) archived log file name=/backup/archives/orcl12du_1_4_880226544.dbf RECID=1 STAMP=938164579 media recovery complete, elapsed time: 00:00:02 Finished recover at 09-MAR-17 Oracle instance started Total System Global Area 801701888 bytes Fixed Size 2293496 bytes Variable Size 591397128 bytes Database Buffers 201326592 bytes Redo Buffers 6684672 bytes contents of Memory Script: { sql clone "alter system set db_name = ''ORCL12DU'' comment= ''Reset to original value by RMAN'' scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; } executing Memory Script sql statement: alter system set db_name = ''ORCL12DU'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset db_unique_name scope=spfile Oracle instance started Total System Global Area 801701888 bytes Fixed Size 2293496 bytes Variable Size 591397128 bytes Database Buffers 201326592 bytes Redo Buffers 6684672 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL12DU" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/opt/oracle/oradata/orcl12du/redo01du.log' ) SIZE 50 M REUSE, GROUP 2 ( '/opt/oracle/oradata/orcl12du/redo02du.log' ) SIZE 50 M REUSE, GROUP 3 ( '/opt/oracle/oradata/orcl12du/redo03du.log' ) SIZE 50 M REUSE DATAFILE '/opt/oracle/oradata/orcl12du/system01du.dbf' CHARACTER SET AL32UTF8 contents of Memory Script: { set newname for tempfile 2 to "/opt/oracle/oradata/orcl12du/temp01du.dbf"; switch clone tempfile all; catalog clone datafilecopy "/opt/oracle/oradata/orcl12du/sysaux01du.dbf", "/opt/oracle/oradata/orcl12du/undotbs01du.dbf", "/opt/oracle/oradata/orcl12du/users01du.dbf", "/opt/oracle/oradata/orcl12du/standby.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 2 to /opt/oracle/oradata/orcl12du/temp01du.dbf in control file cataloged datafile copy datafile copy file name=/opt/oracle/oradata/orcl12du/sysaux01du.dbf RECID=1 STAMP=938164597 cataloged datafile copy datafile copy file name=/opt/oracle/oradata/orcl12du/undotbs01du.dbf RECID=2 STAMP=938164597 cataloged datafile copy datafile copy file name=/opt/oracle/oradata/orcl12du/users01du.dbf RECID=3 STAMP=938164597 cataloged datafile copy datafile copy file name=/opt/oracle/oradata/orcl12du/standby.dbf RECID=4 STAMP=938164597 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=938164597 file name=/opt/oracle/oradata/orcl12du/sysaux01du.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=938164597 file name=/opt/oracle/oradata/orcl12du/undotbs01du.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=938164597 file name=/opt/oracle/oradata/orcl12du/users01du.dbf datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=938164597 file name=/opt/oracle/oradata/orcl12du/standby.dbf Reenabling controlfile options for auxiliary database Executing: alter database force logging Leaving database unopened, as requested Finished Duplicate Db at 09-MAR-17 RMAN>Opening database in upgrade mode:
[oracle@localhost admin]$ sqlplus '/ as sysdba' SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 9 09:20:51 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@orcl12du> alter database open resetlogs upgrade; Database altered. SYS@orcl12du> select instance_name, host_name, TO_CHAR(startup_time, 'DD-MON-YY HH:MI:SS AM') as startup_time, status, archiver from v$instance; INSTANCE_N HOST_NAME STARTUP_TIME STATUS ARCHIVE ---------- ---------------------------------------- ------------------------------ -------------------- ------- orcl12du localhost.localdomain 09-MAR-17 09:16:29 AM OPEN MIGRATE STARTED
Possible Errors
Please note, that you cannot use this feature for ACTIVE duplicate command, if you try so, you get following error from RMAN when trying connect to (11.2) target database:
[oracle@localhost admin]$ rman Recovery Manager: Release 12.1.0.1.0 - Production on Thu Mar 9 09:45:54 2017 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. RMAN> connect auxiliary sys/sys@orcl12du connected to auxiliary database: ORCL12DU (not mounted) RMAN> connect target sys/sys@orcl11du RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-06429: TARGET database is not compatible with this version of RMAN RMAN-06618: RMAN client and database release mismatch; indicated database release is 11.2.0.4