oracle_12c_upgrade_11g_noopen

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

  1. archive log mode enabled on 11.2.0.4 database
  2. executed preupgrd.sql script on 11.2.0.4 database following all steps needed as per upgrade guide - mandatory
  3. (otherwise upgrade fail)
  4. full online disk backup done including archive backups
  5. disk backup available on server B

Steps on server A and server B:

  1. prepare tnsnames.ora, listener.ora and password files so database clone can be done (more information in official Oracle documentation)

Steps on server A:

  1. enable archive log mode if not done yet
  2. execute preupgrd.sql - there is need to copy preupgrd.sql and utluppkg.sql from 12c $OH/rdmbs/admin to server A
  3. solve ERROR messages and run fixup scripts
  4. run online backup to disk
  5. copy or make available disk backup on server B
  6. copy post upgrade scripts generated in step 2 to server B

Steps on server B:

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

  1. 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/';
}

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

  • oracle_12c_upgrade_11g_noopen.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1