Step by Step Upgrade Oracle RAC Grid Infrastructure and Database from 11g to 12c

 

Upgrade RAC Grid and Database from 11.2.0.4 to 12.1.0.2 :-

Main steps :

Grid :-

  1.  Check all services are up and running from 11gR2 GRID_HOME
  2.  Perform backup of OCR, voting disk and Database.
  3.  Create new directory for installing 12C software on both RAC nodes.
  4.  Run “runcluvfy.sh” to verify errors .
  5.  Install and upgrade GRID from 11gR2 to 12cR1
  6. Verify upgrade version

Database  :-

  1. Backup the database before the upgrade
  2. Database upgrade Pre-check
    • Creating Stage for 12c database software
    • Creating directory for 12c oracle home
    • Check the pre upgrade status.
  3. Unzip 12c database software in stage
  4. Install the 12.1.0.2 using the software only installation
  5. Run Preupgrade.sql script in 11.2.0.4 existing database from newly installed 12c home.
  6. Run the DBUA to start the database upgrade.
  7. Database post upgrade check.
  8. Check Database version.

Environment variables for 11g database :-

GRID :

grid()
{
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/11.2.0/grid; export ORACLE_HOME
export ORACLE_SID=+ASM1
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
SQLPATH=/u01/app/oracle/scripts/sql:/u01/app/11.2.0/grid/rdbms/admin:/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin; export SQLPATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
}

DATABASE :

11g()
{
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_SID=orcl11g1
export ORACLE_SID
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:.
export LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/lib:/lib
export LIBPATH
TNS_ADMIN=${ORACLE_HOME}/network/admin
export TNS_ADMIN
PATH=$ORACLE_HOME/bin:$PATH:.
export PATH
}

Upgrade GRID Infrastructure Software 12c :-

Check GRID Infrastructure software version and Clusterware status:

[oracle@racpb1 ~]$ grid
[oracle@racpb1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]

[oracle@racpb1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Verify all services are up and running from 11gR2 GRID Home :

[oracle@racpb1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE racpb1
ONLINE ONLINE racpb2
ora.LISTENER.lsnr
ONLINE ONLINE racpb1
ONLINE ONLINE racpb2
ora.asm
ONLINE ONLINE racpb1 Started
ONLINE ONLINE racpb2 Started
ora.gsd
OFFLINE OFFLINE racpb1
OFFLINE OFFLINE racpb2
ora.net1.network
ONLINE ONLINE racpb1
ONLINE ONLINE racpb2
ora.ons
ONLINE ONLINE racpb1
ONLINE ONLINE racpb2
ora.registry.acfs
ONLINE ONLINE racpb1
ONLINE ONLINE racpb2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racpb2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE racpb1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE racpb1
ora.cvu
1 ONLINE ONLINE racpb1
ora.oc4j
1 ONLINE ONLINE racpb1
ora.orcl11g.db
1 ONLINE ONLINE racpb1 Open
2 ONLINE ONLINE racpb2 Open
ora.racpb1.vip
1 ONLINE ONLINE racpb1
ora.racpb2.vip
1 ONLINE ONLINE racpb2
ora.scan1.vip
1 ONLINE ONLINE racpb2
ora.scan2.vip
1 ONLINE ONLINE racpb1
ora.scan3.vip
1 ONLINE ONLINE racpb1

Check Database status and configuration :

oracle@racpb1 ~]$ srvctl status database -d orcl11g
Instance orcl11g1 is running on node racpb1
Instance orcl11g2 is running on node racpb2

[oracle@racpb1 ~]$ srvctl config database -d orcl11g
Database unique name: orcl11g
Database name: orcl11g
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl11g/spfileorcl11g.ora
Domain: localdomain.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl11g
Database instances: orcl11g1,orcl11g2
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Perform local backup of OCR :

[root@racpb1 ~]$ mkdir -p /u01/ocrbkp
[root@racpb1 ~]# cd /u01/app/11.2.0/grid/bin/
[root@racpb1 bin]# ./ocrconfig -export /u01/ocrbkp/ocrfile

Move the 12c GRID Software to the server and unzip the software :

[oracle@racpb1 12102_64bit]$ unzip -d /u01/ linuxamd64_12102_grid_1of2.zip
Archive:  linuxamd64_12102_grid_1of2.zip
   creating: /u01/grid/
.
.

[oracle@racpb1 12102_64bit]$ unzip -d /u01/ linuxamd64_12102_grid_2of2.zip
Archive:  linuxamd64_12102_grid_2of2.zip
   creating: /u01/grid/stage/Components/oracle.has.crs/
.
.

Run cluvfy utility to pre-check  any errors :

Execute runcluvfy.sh from 12cR1 software location,

[oracle@racpb1 grid]$ ./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/11.2.0/grid -dest_crshome /u01/zpp/12.1.0/grid -dest_version 12.1.0.2.0 -verbose

Make sure the cluvfy executed successfully. If any error, please take action before going to GRID 12cR1 upgrade.The cluvfy log is attached here.

Stop the running 11g database :

[oracle@racpb1 ~]$ ps -ef|grep pmon
oracle 3953 1 0 Dec22 ? 00:00:00 asm_pmon_+ASM1
oracle 4976 1 0 Dec22 ? 00:00:00 ora_pmon_orcl11g1
oracle 23634 4901 0 00:55 pts/0 00:00:00 grep pmon

[oracle@racpb1 ~]$ srvctl stop database -d orcl11g

[oracle@racpb1 ~]$ srvctl status database -d orcl11g
Instance orcl11g1 is not running on node racpb1
Instance orcl11g2 is not running on node racpb2

Take GRID_HOME backup on both nodes :

[oracle@racpb1 ~]$ grid
[oracle@racpb1 ~]$  tar -cvf grid_home_11g.tar $GRID_HOME

Check Clusterware services status before upgrade :

[oracle@racpb1 ~]$ crsctl check cluster -all
**************************************************************
racpb1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
racpb2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Start the 12cR1 upgrade by executing runInstaller :

[oracle@racpb1 ~]$ cd /u01/
[oracle@racpb1 u01]$ cd grid/

[oracle@racpb1 grid]$ ./runInstaller 
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 415 MB. Actual 8565 MB Passed
Checking swap space: must be greater than 150 MB. Actual 5996 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-12-23_01

Select Upgrade option to upgrade GRID 12c infrastructure and ASM.

Check the public host names and existing GRID_HOME

Uncheck the EM cloud control option to disable EM.

Specify location for ORACLE_BASE and ORACLE_HOME for 12c. 

Ignore the SWAP SIZE it has to be twice the size of memory in server.

 

Execute rootupgrade.sh script in both nodes :

 First node (racpb1)  :-

[root@racpb1 bin]# sh /u01/app/12.1.0/grid/rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2018/12/23 12:18:59 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:18:59 CLSRSC-4012: Shutting down Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:19:08 CLSRSC-4013: Successfully shut down Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:19:19 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:19:22 CLSRSC-464: Starting retrieval of the cluster configuration data
2018/12/23 12:19:30 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.
2018/12/23 12:19:30 CLSRSC-363: User ignored prerequisites during installation
2018/12/23 12:19:38 CLSRSC-468: Setting Oracle Clusterware and ASM to rolling migration mode
2018/12/23 12:19:38 CLSRSC-482: Running command: '/u01/app/12.1.0/grid/bin/asmca -silent -upgradeNodeASM -nonRolling false -oldCRSHome /u01/app/11.2.0/grid -oldCRSVersion 11.2.0.4.0 -nodeNumber 1 -firstNode true -startRolling true'

ASM configuration upgraded in local node successfully.

2018/12/23 12:19:45 CLSRSC-469: Successfully set Oracle Clusterware and ASM to rolling migration mode
2018/12/23 12:19:45 CLSRSC-466: Starting shutdown of the current Oracle Grid Infrastructure stack
2018/12/23 12:20:36 CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully completed.
OLR initialization - successful
2018/12/23 12:24:43 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.conf'
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2018/12/23 12:29:05 CLSRSC-472: Attempting to export the OCR
2018/12/23 12:29:06 CLSRSC-482: Running command: 'ocrconfig -upgrade oracle oinstall'
2018/12/23 12:29:23 CLSRSC-473: Successfully exported the OCR
2018/12/23 12:29:29 CLSRSC-486:
At this stage of upgrade, the OCR has changed.
Any attempt to downgrade the cluster after this point will require a complete cluster outage to restore the OCR.
2018/12/23 12:29:29 CLSRSC-541:
To downgrade the cluster:
1. All nodes that have been upgraded must be downgraded.

2018/12/23 12:29:30 CLSRSC-542:
2. Before downgrading the last node, the Grid Infrastructure stack on all other cluster nodes must be down.

2018/12/23 12:29:30 CLSRSC-543:
3. The downgrade command must be run on the node racpb1 with the '-lastnode' option to restore global configuration data.
2018/12/23 12:29:55 CLSRSC-343: Successfully started Oracle Clusterware stack
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully taken the backup of node specific configuration in OCR.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
2018/12/23 12:30:19 CLSRSC-474: Initiating upgrade of resource types
2018/12/23 12:31:12 CLSRSC-482: Running command: 'upgrade model -s 11.2.0.4.0 -d 12.1.0.2.0 -p first'
2018/12/23 12:31:12 CLSRSC-475: Upgrade of resource types successfully initiated.
2018/12/23 12:31:21 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Second node (racpb2)  :-

[root@racpb2 ~]# sh /u01/app/12.1.0/grid/rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/12.1.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2018/12/23 12:34:35 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:35:15 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.
2018/12/23 12:35:17 CLSRSC-464: Starting retrieval of the cluster configuration data
2018/12/23 12:35:24 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.
2018/12/23 12:35:24 CLSRSC-363: User ignored prerequisites during installation
ASM configuration upgraded in local node successfully.
2018/12/23 12:35:41 CLSRSC-466: Starting shutdown of the current Oracle Grid Infrastructure stack
2018/12/23 12:36:10 CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully completed.
OLR initialization - successful
2018/12/23 12:36:37 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2018/12/23 12:39:54 CLSRSC-343: Successfully started Oracle Clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully taken the backup of node specific configuration in OCR.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Start upgrade invoked..
2018/12/23 12:40:21 CLSRSC-478: Setting Oracle Clusterware active version on the last node to be upgraded

2018/12/23 12:40:21 CLSRSC-482: Running command: '/u01/app/12.1.0/grid/bin/crsctl set crs activeversion'

Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the OCR.
Started to upgrade the CSS.
The CSS was successfully upgraded.
Started to upgrade Oracle ASM.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Successfully upgraded the Oracle Clusterware.
Oracle Clusterware operating version was successfully set to 12.1.0.2.0
2018/12/23 12:42:33 CLSRSC-479: Successfully set Oracle Clusterware active version

2018/12/23 12:42:39 CLSRSC-476: Finishing upgrade of resource types

2018/12/23 12:43:00 CLSRSC-482: Running command: 'upgrade model -s 11.2.0.4.0 -d 12.1.0.2.0 -p last'

2018/12/23 12:43:00 CLSRSC-477: Successfully completed upgrade of resource types

2018/12/23 12:43:34 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

After running rootupgrade.sh script,Click OK button.

Check the Clusterware upgrade version:

[root@racpb1 ~]# cd /u01/app/12.1.0/grid/bin/
[root@racpb1 bin]# ./crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.2.0]

Note: If you are upgrading from 11.2.0.1/11.2.0.2/11.2.0.3 version to 12cR1 then you may need to apply additional patches before you proceed with upgrade.

Start the 11g database :

[oracle@racpb1 ~]$ srvctl start database -d orcl11g
[oracle@racpb1 ~]$ srvctl status database -d orcl11g
Instance orcl11g1 is running on node racpb1
Instance orcl11g2 is running on node racpb2

Upgrade RAC database from 11gR2 to 12cR1 :-

Backup the database before the upgrade :

Take level zero backup or cold backup of database.

Database upgrade Pre-check :

  • Creating Stage for 12c database software.
[oracle@racpb1 ~]$ mkdir -p /u01/stage
[oracle@racpb1 ~]$ chmod -R 755 /u01/stage/
  • Creating directory for 12c ORACLE_HOME.
[oracle@racpb1 ~]$ mkdir -p /u01/app/oracle/product/12.1.0/db_1
[oracle@racpb1 ~]$ chown -R oracle:oinstall /u01/app/oracle/product/12.1.0/db_1
[oracle@racpb1 ~]$ chmod -R 775 /u01/app/oracle/product/12.1.0/db_1
  • Check the preupgrade status :

Run runcluvfy.sh from grid stage location :

[oracle@racpb1 grid]$ ./runcluvfy.sh stage -pre dbinst -upgrade -src_dbhome /u01/app/oracle/product/11.2.0/dbhome_1 -dest_dbhome /u01/app/oracle/product/12.1.0/db_1 -dest_version 12.1.0.2.0

Above command output has to be completed successfully to upgrade database from 11gR1 to 12cR1.

Unzip 12c database software in stage :

[oracle@racpb1 12102_64bit]$ unzip -d /u01/stage/ linuxamd64_12102_database_1of2.zip

[oracle@racpb1 12102_64bit]$ unzip -d /u01/stage/ linuxamd64_12102_database_2of2.zip

Unset the 11g env. :

unset ORACLE_HOME
unset ORACLE_BASE
unset ORACLE_SID

Install the 12.1.0.2 using the software only installation :

Set new 12c env. and Execute runInstaller.

[oracle@racpb1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@racpb1 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@racpb1 ~]$ export ORACLE_SID=orcl12c
[oracle@racpb1 ~]$ 
[oracle@racpb1 ~]$ cd /u01/stage/database/
[oracle@racpb1 database]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 8533 MB Passed
Checking swap space: must be greater than 150 MB. Actual 5999 MB Passed
Checking monitor: must be configured to display at least 256 colors. 
Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-12-23_02-05-54PM. Please wait ...

Skip the security updates from Oracle Support.

Select RAC database installation.

After database 12c software installation done run the below script from both nodes :

 

Run Preupgrade.sql script :

  • Preupgrade script to identify any pre-reqs tasks that must be done on the database  before the upgrade.
  • Execute Preupgrade.sql script in 11.2.0.4 existing database from newly installed 12c ORACLE_HOME.
[oracle@racpb1 ~]$ . .bash_profile
[oracle@racpb1 ~]$ 11g
[oracle@racpb1 ~]$ cd /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/
[oracle@racpb1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 03:35:26 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @preupgrd.sql

Loading Pre-Upgrade Package...

***************************************************************************
Executing Pre-Upgrade Checks in ORCL11G...
***************************************************************************************************************************************

====>> ERRORS FOUND for ORCL11G <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.

You MUST resolve the above errors prior to upgrade

************************************************************************************************************************

====>> PRE-UPGRADE RESULTS for ORCL11G <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/orcl11g/preupgrade/postupgrade_fixups.sql

***************************************************************************************************************************************
Pre-Upgrade Checks in ORCL11G Completed.
******************************************************************************************************************************************************
***********************************************************************

Run the DBUA to start the database upgrade :

Check Database version and configuration :-

[oracle@racpb1 ~]$ srvctl config database -d orcl11g
Database unique name: orcl11g
Database name: orcl11g
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/orcl11g/spfileorcl11g.ora
Password file: 
Domain: localdomain.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oinstall
Database instances: orcl11g1,orcl11g2
Configured nodes: racpb1,racpb2
Database is administrator managed

[oracle@racpb1 ~]$ srvctl status database -d orcl11g
Instance orcl11g1 is running on node racpb1
Instance orcl11g2 is running on node racpb2

Successfully upgrade the Rac database From 11g to 12c (Grid & DB).

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Upgrade Oracle database from 11g to 12c using the RMAN Backup

Description:-

In this article we are going to see upgrade Oracle database from the version 11g to the version 12c using the RMAN

Environment Detail’s

High Level Steps:-

  1. Oracle Database 11.2.0.3 Rman Full Backup
  2. Oracle Database 12c Restore
  3. Alter database open
  4. Upgrade Database

Let’s start the Demo:-

  1. Oracle Database 11.2.0.3 Rman Full Backup

Step1:- Verify the Database name and archive log is enabled on database 11g.

[oracle@gg-11 ~]$ export ORACLE_SID=oradbwr
[oracle@gg-11 ~]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 3 01:20:57 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,log_mode from v$database;
NAME LOG_MODE
——— ————
ORADBWR ARCHIVELOG

Step2:-Testing After Upgrade to 12c

SQL> create table chennai (name varchar(10));
Table created.

SQL> insert into chennai values(‘HARI’);
1 row created.

SQL> commit;
Commit complete.

Step3:-Going to take 11g database Full backup using rman

[oracle@gg-11 ~]$ export ORACLE_SID=oradbwr
[oracle@gg-11 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Aug 3 01:26:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1410039869)

RMAN> backup database plus archivelog;

Starting backup at 03-AUG-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=983150751
input archived log thread=1 sequence=3 RECID=2 STAMP=983150751
input archived log thread=1 sequence=4 RECID=3 STAMP=983150755
input archived log thread=1 sequence=5 RECID=4 STAMP=983150755
input archived log thread=1 sequence=6 RECID=5 STAMP=983150756
input archived log thread=1 sequence=7 RECID=6 STAMP=983150757
input archived log thread=1 sequence=8 RECID=7 STAMP=983150758
input archived log thread=1 sequence=9 RECID=8 STAMP=983150824
channel ORA_DISK_1: starting piece 1 at 03-AUG-18
channel ORA_DISK_1: finished piece 1 at 03-AUG-18
piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_annnn_TAG20180803T012704_fp6rjjmz_.bkp tag=TAG20180803T012704 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-AUG-18
Starting backup at 03-AUG-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oradbwr/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oradbwr/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oradbwr/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oradbwr/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-AUG-18
channel ORA_DISK_1: finished piece 1 at 03-AUG-18
piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_nnndf_TAG20180803T012705_fp6rjkt8_.bkp tag=TAG20180803T012705 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 03-AUG-18
Starting backup at 03-AUG-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=9 STAMP=983150870
channel ORA_DISK_1: starting piece 1 at 03-AUG-18
channel ORA_DISK_1: finished piece 1 at 03-AUG-18
piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_annnn_TAG20180803T012751_fp6rkz5m_.bkp tag=TAG20180803T012751 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-AUG-18
Starting Control File and SPFILE Autobackup at 03-AUG-18
piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/2018_08_03/o1_mf_s_983150872_fp6rl0lw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-AUG-18

Step4:- Copy the rman backup pieces from Server 11g to Server 12c

Datafile and archivelog

[oracle@gg-11 ~]$ cd /u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/
[oracle@gg-11 2018_08_03]$ scp o1_mf_nnndf_TAG20180803T012705_fp6rjkt8_.bkp o1_mf_annnn_TAG20180803T012751_fp6rkz5m_.bkp oracle@192.168.2.157:/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/
oracle@192.168.2.157's password: 
o1_mf_nnndf_TAG20180803T012705_fp6rjkt8_.bkp 100% 993MB 26.8MB/s 00:37 
o1_mf_annnn_TAG20180803T012751_fp6rkz5m_.bkp 100% 3072 3.0KB/s 00:00 

Controlfile autobackup location

[oracle@gg-11 2018_08_03]$ cd /u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/
[oracle@gg-11 autobackup]$ scp 2018_08_03/o1_mf_s_983150872_fp6rl0lw_.bkp oracle@192.168.2.157:/u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/
oracle@192.168.2.157's password: 
o1_mf_s_983150872_fp6rl0lw_.bkp

Step5:- Create and copy the pfile $ passwordfile from source 11g to destination 12c

[oracle@gg-11 dbs]$ scp orapworadbwr initoradbwr.ora oracle@192.168.2.157:/u01/app/oracle/product/12.2.0/dbhome_2/dbs
oracle@192.168.2.157’s password:
orapworadbwr 100% 1536 1.5KB/s 00:00
initoradbwr.ora 100% 1046 1.0KB/s 00:00

2.Oracle Database 12c Restore

Step6:-Change the pfile parameters for 12c database

compatible=’12.2.0.1.0′

Step7:- Create directory for 12c database

[oracle@gg-12 dbs]$ mkdir -p /u01/app/oracle/oradata/oradbwr/
[oracle@gg-12 ]$ mkdir -p /u01/app/oracle/admin/oradbwr/adump

Step8:- Startup the 12c database on the 12c Server

[oracle@gg-12 dbs]$ export ORACLE_SID=oradbwr
[oracle@gg-12 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 02:21:04 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 931135488 bytes
Fixed Size 8627296 bytes
Variable Size 301992864 bytes
Database Buffers 616562688 bytes
Redo Buffers 3952640 bytes

SQL> create spfile from pfile;
File created.

Step9:- Restore the Control file on the 12c Server.

[oracle@gg-12 ~]$ export ORACLE_SID=oradbwr
[oracle@gg-12 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Aug 3 02:26:50 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 03-AUG-18
using channel ORA_DISK_1
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORADBWR
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/o1_mf_s_983150872_fp6rl0lw_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/o1_mf_s_983150872_fp6rl0lw_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/oradbwr/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/oradbwr/control02.ctl
Finished restore at 03-AUG-18

Step10:-Mount the Database

RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1

Step11:-Catalog the 11g backup within the controlfile

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/';

Starting implicit crosscheck backup at 03-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 03-AUG-18
Starting implicit crosscheck copy at 03-AUG-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-AUG-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORADBWR/autobackup/o1_mf_s_983150872_fp6rl0lw_.bkp
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/
no files found to be unknown to the database

Step12:-Restore Database

RMAN> restore database;

Starting restore at 03-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK

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/oradbwr/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oradbwr/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oradbwr/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oradbwr/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_nnndf_TAG20180803T012705_fp6rjkt8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_nnndf_TAG20180803T012705_fp6rjkt8_.bkp tag=TAG20180803T012705
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 03-AUG-18

Step13:-Recover database upto last sequence

RMAN> run {
set until sequence = 11;  (this sequence number i get from 11g DB)
recover database;
}

executing command: SET until clause
Starting recover at 03-AUG-18
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_annnn_TAG20180803T012751_fp6rkz5m_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORADBWR/backupset/2018_08_03/o1_mf_annnn_TAG20180803T012751_fp6rkz5m_.bkp tag=TAG20180803T012751
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORADBWR/archivelog/2018_08_03/o1_mf_1_10_fp6wvskv_.arc thread=1 sequence=10
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORADBWR/archivelog/2018_08_03/o1_mf_1_10_fp6wvskv_.arc RECID=10 STAMP=983155281
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-AUG-18

3.Alter database open

Step14:-Connect sqlplus and open the database

[oracle@gg-12 dbs]$ export ORACLE_SID=oradbwr
[oracle@gg-12 dbs]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 02:44:51 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure   (we can’t open in normal mode)
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier
Process ID: 4004
Session ID: 1 Serial number: 22560

4.Upgrade Database

Step15:- Startup upgrade and run the catctl.pl  script .

[oracle@gg-12 dbs]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 03:11:46 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.
Total System Global Area 931135488 bytes
Fixed Size 8627296 bytes
Variable Size 301992864 bytes
Database Buffers 616562688 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@gg-12 dbs]$ cd $ORACLE_HOME/rdbms/admin
[oracle@gg-12 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 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 = 4
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/dbhome_2/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/dbhome_2]
/u01/app/oracle/product/12.2.0/dbhome_2/bin/orabasehome = [/u01/app/oracle/product/12.2.0/dbhome_2]
catctlGetOrabase = [/u01/app/oracle/product/12.2.0/dbhome_2]
Analyzing file /u01/app/oracle/product/12.2.0/dbhome_2/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20180803031607]
catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20180803031607/catupgrd_catcon_4242.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20180803031607/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20180803031607/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = oradbwr
DataBase Version = 11.2.0.3.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609/catupgrd_catcon_4242.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609]
Parallel SQL Process Count = 4
Components in [oradbwr]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-115] Start Time:[2018_08_03 03:16:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [oradbwr] Files:1 Time: 128s
*************** Catalog Core SQL ***************
Serial Phase #:1 [oradbwr] Files:5 Time: 67s
Restart Phase #:2 [oradbwr] Files:1 Time: 0s
Serial Phase #:109 [oradbwr] Files:1 Time: 60s
Serial Phase #:110 [oradbwr] Files:1 Time: 0s
Serial Phase #:111 [oradbwr] Files:1 Time: 107s
***************** Post Upgrade *****************
Serial Phase #:112 [oradbwr] Files:1 Time: 425s
**************** Summary report ****************
Serial Phase #:113 [oradbwr] Files:1 Time: 2s
Serial Phase #:114 [oradbwr] Files:1 Time: 0s
Serial Phase #:115 [oradbwr] Files:1 Time: 40s
------------------------------------------------------
Phases [0-115] End Time:[2018_08_03 05:15:24]
------------------------------------------------------
Grand Total Time: 7161s 
LOG FILES: (/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/oradbwr/upgrade20180803031609/upg_summary.log
Grand Total Upgrade Time: [0d:1h:30m:21s]

Step16:- Recompile the Invalid objects using utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-08-03 05:24:35
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-08-03 05:33:52
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0

Function created.
PL/SQL procedure successfully completed.

Function dropped.
...Starting validation 05:34:07
...Database user "SYS", database schema "APEX_050000", user# "121" 05:34:07
...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 05:34:10
...272 packages
...265 package bodies
...465 tables
...8 functions
...16 procedures
...4 sequences
...497 triggers
...1582 indexes
...255 views
...0 libraries
...14 types
...5 type bodies
...0 operators
...0 index types
...Begin key object existence check 05:34:17
...Completed key object existence check 05:34:17
...Setting DBMS Registry 05:34:17
...Setting DBMS Registry Complete 05:34:17
...Exiting validate 05:34:17
PL/SQL procedure successfully completed


Step17:-check the database status 

SQL> select comp_name,version from dba_registry where status = 'VALID';

COMP_NAME                                  VERSION
----------------                ------------------------------
Oracle Database Catalog Views            12.2.0.1.0
Oracle Database Packages and Types       12.2.0.1.0
JServer JAVA Virtual Machine             12.2.0.1.0
Oracle XDK                               12.2.0.1.0
Oracle Database Java Packages            12.2.0.1.0
OLAP Analytic Workspace                  12.2.0.1.0
Oracle Workspace Manager                 12.2.0.1.0
Oracle Text                              12.2.0.1.0
Oracle XML Database                      12.2.0.1.0
Oracle Multimedia                        12.2.0.1.0
Spatial                                  12.2.0.1.0
Oracle OLAP API                          12.2.0.1.0
Oracle Application Express               5.0.4.00.12

13 rows selected.

Successfully upgrade to 12c database

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba