This article explains how to upgrade Oracle database from the version 12cR1 (12.1.0.2) to the version 12cR2 (12.2.0.1) using the RMAN Full backup.
Table of Contents:
– Introduction
– Backup of the Oracle Database 12cR1 12.1.0.2:
– Restore the Oracle Database 12cR2 Restore
– Upgrade of Oracle Database components
1. Introduction:
Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files. For more information.
2. Backup of Oracle Database 12cR1 12.1.0.2:
Step 1: Create a pfile from the Oracle database 12cR1:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 5 10:34:02 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create pfile from spfile;
Step 2: Move and edit pfile on the server oracle database 12cR2:
scp /u01/app/oracle/product/12.1.0.2/dbhome/dbs/initwadhah.ora oracle@db12cr2:/u01/app/oracle/product/12.2.0.1/dbhome/dbs
Note: Edit pfile and update the compatible initialisation parameter:
*.compatible='12.1.0.2.0'
to
*.compatible='12.2.0.1.0'
Step 3: This backup done on test server in the mount state wih no archivelog.
$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jan 5 10:30:36 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: WADHAH (DBID=171501468, not open)
RMAN> backup database;
Starting backup at 05-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/wadhah/example01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/wadhah/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/wadhah/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/wadhah/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/wadhah/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JAN-19
channel ORA_DISK_1: finished piece 1 at 05-JAN-19
piece handle=/u01/app/oracle/fast_recovery_area/WADHAH/backupset/2019_01_05/o1_mf_nnndf_TAG20190105T103044_g30yb4y4_.bkp tag=TAG20190105T103044 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-JAN-19
channel ORA_DISK_1: finished piece 1 at 05-JAN-19
piece handle=/u01/app/oracle/fast_recovery_area/WADHAH/backupset/2019_01_05/o1_mf_ncsnf_TAG20190105T103044_g30ycxdt_.bkp tag=TAG20190105T103044 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JAN-19
RMAN>
Step 4: Verify the backup on the 12cR1 Server:
RMAN> list backup summary;
List of Backups
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
1 B F A DISK 05-JAN-19 1 1 NO TAG20190105T103044
2 B F A DISK 05-JAN-19 1 1 NO TAG20190105T103044
RMAN>
Step 5: Copy the backup on the 12cR1 Server to the server 12cR2:
$ scp /u01/app/oracle/fast_recovery_area/WADHAH/backupset/2019_01_05/*TAG20190105T103044* oracle@db12cr2:/u01/app/oracle/fast_recovery_area/WADHAH/
3. Restore Oracle Database 12cR1 to 12cR2:
Note: After the installation of the Oracle 12cR2 Binary, and the creation of the Listener using the netca tool, and the update of the pfile and the creation of the password file:
Step 1: Startup the orcl 12cR2 instance on nomount.
$ /u01/app/oracle/product/12.2.0.1/dbhome/bin/rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jan 5 10:38:01 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
RMAN>
Step 2: Restore the Control file on the 12cR2 Server:
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/o1_mf_ncsnf_TAG20190105T103044_g30ycxdt_.bkp';
Starting restore at 05-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/wadhah/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/wadhah/control02.ctl
Finished restore at 05-JAN-19
Step 3: In the MOUNT state catalog the full backup:
RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/';
Starting implicit crosscheck backup at 05-JAN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 05-JAN-19
Starting implicit crosscheck copy at 05-JAN-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-JAN-19
searching for all files in the recovery area
cataloging files…
no files cataloged
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/
List of Files Unknown to the Database
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncsnf_TAG20190105T103044_g30ycxdt_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20190105T103044_g30yb4y4_.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done
List of Cataloged Files
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_ncsnf_TAG20190105T103044_g30ycxdt_.bkp
File Name: /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20190105T103044_g30yb4y4_.bkp
Step 4: In the MOUNT restore the full backup:
RMAN> run {
2> restore database;
3> recover database;
4> }
Starting restore at 05-JAN-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/wadhah/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/wadhah/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/wadhah/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/wadhah/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/wadhah/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20190105T103044_g30yb4y4_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/o1_mf_nnndf_TAG20190105T103044_g30yb4y4_.bkp tag=TAG20190105T103044
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 05-JAN-19
Starting recover at 05-JAN-19
using channel ORA_DISK_1
starting media recovery
RMAN-08187: warning: media recovery until SCN 1626677 complete
Finished recover at 05-JAN-19
Step 5: Open Database with Error of the no upgrade option:
RMAN> alter database open RESETLOGS;
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
ORA-01092: ORACLE instance terminated. Disconnection forced
RMAN-03002: failure of sql statement command at 01/05/2019 10:45:14
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "ACDRROWTSINTCOL#": invalid identifier
Process ID: 4929
Session ID: 33 Serial number: 14955
Step 6: Startup Database with upgrade option:
$ /u01/app/oracle/product/12.2.0.1/dbhome/bin/sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 10:45:58 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL>
Step 7: Upgrade the Database catalog :
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Argument list for [catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
/u01/app/oracle/product/12.2.0.1/dbhome/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1/dbhome]
/u01/app/oracle/product/12.2.0.1/dbhome/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1/dbhome]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0.1/dbhome]
Analyzing file /u01/app/oracle/product/12.2.0.1/dbhome/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20190105105148]
catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190105105148/catupgrd_catcon_7098.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20190105105148/catupgrd.log] files for output generated by scripts catcon: See [/tmp/cfgtoollogs/upgrade20190105105148/catupgrd_.lst] files for spool files, if any
Number of Cpus = 1
Database Name = wadhah
DataBase Version = 12.1.0.2.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150/catupgrd_catcon_7098.lst]
catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150/catupgrd.log] files for output generated by scripts catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150/catupgrd_.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150]
Parallel SQL Process Count = 4
Components in [wadhah]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]
Phases [0-115] Start Time:[2019_01_05 10:51:52]
*** Executing Change Scripts ***
Serial Phase #:0 [wadhah] Files:1 Time: 53s
* Catalog Core SQL *
Serial Phase #:1 [wadhah] Files:5 Time: 99s
Restart Phase #:2 [wadhah] Files:1 Time: 1s
*** Catalog Tables and Views ***
Parallel Phase #:3 [wadhah] Files:19 Time: 33s
Restart Phase #:4 [wadhah] Files:1 Time: 1s
* Catalog Final Scripts
Serial Phase #:5 [wadhah] Files:6 Time: 19s
* Catproc Start
Serial Phase #:6 [wadhah] Files:1 Time: 17s
* Catproc Types
Serial Phase #:7 [wadhah] Files:2 Time: 15s
Restart Phase #:8 [wadhah] Files:1 Time: 2s
** Catproc Tables **
Parallel Phase #:9 [wadhah] Files:69 Time: 72s
Restart Phase #:10 [wadhah] Files:1 Time: 2s
* Catproc Package Specs
Serial Phase #:11 [wadhah] Files:1 Time: 51s
Restart Phase #:12 [wadhah] Files:1 Time: 1s
Catproc Procedures
Parallel Phase #:13 [wadhah] Files:97 Time: 40s
Restart Phase #:14 [wadhah] Files:1 Time: 2s
Parallel Phase #:15 [wadhah] Files:118 Time: 51s
Restart Phase #:16 [wadhah] Files:1 Time: 2s
Serial Phase #:17 [wadhah] Files:13 Time: 5s
Restart Phase #:18 [wadhah] Files:1 Time: 1s
* Catproc Views
Parallel Phase #:19 [wadhah] Files:33 Time: 59s
Restart Phase #:20 [wadhah] Files:1 Time: 2s
Serial Phase #:21 [wadhah] Files:3 Time: 10s
Restart Phase #:22 [wadhah] Files:1 Time: 1s
Parallel Phase #:23 [wadhah] Files:24 Time: 166s
Restart Phase #:24 [wadhah] Files:1 Time: 1s
Parallel Phase #:25 [wadhah] Files:11 Time: 70s
Restart Phase #:26 [wadhah] Files:1 Time: 1s
Serial Phase #:27 [wadhah] Files:1 Time: 0s
Serial Phase #:28 [wadhah] Files:3 Time: 5s
Serial Phase #:29 [wadhah] Files:1 Time: 0s
Restart Phase #:30 [wadhah] Files:1 Time: 2s
* Catproc CDB Views
Serial Phase #:31 [wadhah] Files:1 Time: 1s
Restart Phase #:32 [wadhah] Files:1 Time: 1s
Serial Phase #:34 [wadhah] Files:1 Time: 0s
* Catproc PLBs *
Serial Phase #:35 [wadhah] Files:283 Time: 42s
Serial Phase #:36 [wadhah] Files:1 Time: 0s
Restart Phase #:37 [wadhah] Files:1 Time: 1s
Serial Phase #:38 [wadhah] Files:1 Time: 7s
Restart Phase #:39 [wadhah] Files:1 Time: 1s
* Catproc DataPump *
Serial Phase #:40 [wadhah] Files:3 Time: 73s
Restart Phase #:41 [wadhah] Files:1 Time: 1s
**** Catproc SQL **
Parallel Phase #:42 [wadhah] Files:13 Time: 79s
Restart Phase #:43 [wadhah] Files:1 Time: 1s
Parallel Phase #:44 [wadhah] Files:12 Time: 30s
Restart Phase #:45 [wadhah] Files:1 Time: 1s
Parallel Phase #:46 [wadhah] Files:2 Time: 3s
Restart Phase #:47 [wadhah] Files:1 Time: 1s
Final Catproc scripts
Serial Phase #:48 [wadhah] Files:1 Time: 5s
Restart Phase #:49 [wadhah] Files:1 Time: 1s
Final RDBMS scripts *
Serial Phase #:50 [wadhah] Files:1 Time: 25s
Upgrade Component Start ***
Serial Phase #:51 [wadhah] Files:1 Time: 1s
Restart Phase #:52 [wadhah] Files:1 Time: 1s
** Upgrading Java ****
Serial Phase #:53 [wadhah] Files:1 Time: 339s
Restart Phase #:54 [wadhah] Files:1 Time: 1s
** Upgrading XDK *
Serial Phase #:55 [wadhah] Files:1 Time: 86s
Restart Phase #:56 [wadhah] Files:1 Time: 2s
* Upgrading APS,OLS,DV,CONTEXT *
Serial Phase #:57 [wadhah] Files:1 Time: 99s
* Upgrading XDB
Restart Phase #:58 [wadhah] Files:1 Time: 1s
Serial Phase #:60 [wadhah] Files:3 Time: 14s
Serial Phase #:61 [wadhah] Files:3 Time: 9s
Parallel Phase #:62 [wadhah] Files:9 Time: 8s
Parallel Phase #:63 [wadhah] Files:24 Time: 8s
Serial Phase #:64 [wadhah] Files:4 Time: 8s
Serial Phase #:65 [wadhah] Files:1 Time: 0s
Serial Phase #:66 [wadhah] Files:30 Time: 4s
Serial Phase #:67 [wadhah] Files:1 Time: 0s
Parallel Phase #:68 [wadhah] Files:6 Time: 7s
Serial Phase #:69 [wadhah] Files:2 Time: 26s
Serial Phase #:70 [wadhah] Files:3 Time: 47s
Restart Phase #:71 [wadhah] Files:1 Time: 1s
* Upgrading CATJAVA,OWM,MGW,RAC
Serial Phase #:72 [wadhah] Files:1 Time: 83s
** Upgrading ORDIM *
Restart Phase #:73 [wadhah] Files:1 Time: 2s
Serial Phase #:75 [wadhah] Files:1 Time: 2s
Parallel Phase #:76 [wadhah] Files:2 Time: 87s
Serial Phase #:77 [wadhah] Files:1 Time: 79s
Restart Phase #:78 [wadhah] Files:1 Time: 1s
Parallel Phase #:79 [wadhah] Files:2 Time: 17s
Serial Phase #:80 [wadhah] Files:2 Time: 2s
* Upgrading SDO
Restart Phase #:81 [wadhah] Files:1 Time: 1s
Serial Phase #:83 [wadhah] Files:1 Time: 35s
Serial Phase #:84 [wadhah] Files:1 Time: 2s
Restart Phase #:85 [wadhah] Files:1 Time: 2s
Serial Phase #:86 [wadhah] Files:1 Time: 33s
Restart Phase #:87 [wadhah] Files:1 Time: 1s
Parallel Phase #:88 [wadhah] Files:3 Time: 179s
Restart Phase #:89 [wadhah] Files:1 Time: 1s
Serial Phase #:90 [wadhah] Files:1 Time: 8s
Restart Phase #:91 [wadhah] Files:1 Time: 1s
Serial Phase #:92 [wadhah] Files:1 Time: 5s
Restart Phase #:93 [wadhah] Files:1 Time: 1s
Parallel Phase #:94 [wadhah] Files:4 Time: 176s
Restart Phase #:95 [wadhah] Files:1 Time: 2s
Serial Phase #:96 [wadhah] Files:1 Time: 3s
Restart Phase #:97 [wadhah] Files:1 Time: 1s
Serial Phase #:98 [wadhah] Files:2 Time: 72s
Restart Phase #:99 [wadhah] Files:1 Time: 2s
Serial Phase #:100 [wadhah] Files:1 Time: 1s
Restart Phase #:101 [wadhah] Files:1 Time: 2s
*** Upgrading Misc. ODM, OLAP **
Serial Phase #:102 [wadhah] Files:1 Time: 21s
** Upgrading APEX **
Restart Phase #:103 [wadhah] Files:1 Time: 1s
Serial Phase #:104 [wadhah] Files:1 Time: 961s
Restart Phase #:105 [wadhah] Files:1 Time: 1s
*** Final Component scripts ***
Serial Phase #:106 [wadhah] Files:1 Time: 2s
* Final Upgrade scripts
Serial Phase #:107 [wadhah] Files:1 Time: 171s
End PDB Application Upgrade
Serial Phase #:108 [wadhah] Files:1 Time: 2s
Migration
Serial Phase #:109 [wadhah] Files:1 Time: 69s
Serial Phase #:110 [wadhah] Files:1 Time: 0s
Serial Phase #:111 [wadhah] Files:1 Time: 113s
* Post Upgrade *
Serial Phase #:112 [wadhah] Files:1 Time: 136s
Summary report **
Serial Phase #:113 [wadhah] Files:1 Time: 3s
Serial Phase #:114 [wadhah] Files:1 Time: 0s
Serial Phase #:115 [wadhah] Files:1 Time: 35s
Phases [0-115] End Time:[2019_01_05 11:59:07]
Grand Total Time: 4052s
LOG FILES: (/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0.1/dbhome/cfgtoollogs/wadhah/upgrade20190105105150/upg_summary.log
Grand Total Upgrade Time: [0d:1h:7m:32s]
Step 8: Open database.
$ /u01/app/oracle/product/12.2.0.1/dbhome/bin/sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 14:25:04 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL>
Step 9: Verify Upgrade:
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
Oracle Database Packages and Types 12.2.0.1.0 UPGRADED
JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
Oracle XDK 12.2.0.1.0 UPGRADED
Oracle Database Java Packages 12.2.0.1.0 UPGRADED
OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
Oracle Real Application Clusters 12.2.0.1.0 UPGRADED
Oracle XML Database 12.2.0.1.0 UPGRADED
Oracle Workspace Manager 12.2.0.1.0 UPGRADED
Oracle Text 12.2.0.1.0 UPGRADED
Oracle Multimedia 12.2.0.1.0 UPGRADED
Spatial 12.2.0.1.0 UPGRADED
Oracle OLAP API 12.2.0.1.0 UPGRADED
Oracle Label Security 12.2.0.1.0 UPGRADED
Oracle Application Express 5.0.4.00.12 UPGRADED
Oracle Database Vault 12.2.0.1.0 UPGRADED
16 rows selected.
4. Conclusion:
This article explains how to upgrade Oracle database 12cR1 (12.1.0.2) to Oracle Database 12cR2 (12.2.0.1).
Google+
Facebook
