Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Description:-

In this article we are going to see Zero downtime database upgrade from 11g to 12c using Oracle Goldengate
1)Using Goldengate we are going to upgrade from 11g to 12c database without any downtime.
2)Already Data is available source11g scheme Hari.

Environment Detail’s:-

High Level Steps  upgrade from 11g to 12c database

1)check network between source and target.
2)install goldengate Software both side
3)setup extract and datapump on source site
4)setup replict on target side
5)export and import initial load using SCN
6)start the replicat using on scn

Source 11g database side GG Configuration

Step1:-Login in to 11g server and connect to Goldengate 

[oracle@gg-11 gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (gg-11.2.com) dblogin userid gguser,password gguser
Successfully logged into database.

Step2:-Configure manager 

GGSCI (gg-11.2.com)view param mgr
PORT 7809

Step3:-Add schematrandata

GGSCI (gg-11.2.com )  add schematrandata hari

2018-07-31 19:56:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema hari.
2018-07-31 19:56:12 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hari.

GGSCI (gg-11.2.com ) info schematrandata hari

2018-07-31 19:56:34 INFO OGG-01785 Schema level supplemental logging is enabled on schema HARI.
2018-07-31 19:56:34 INFO OGG-01980 Schema level supplemental logging is enabled on schema HARI for all scheduling columns.

Step4:-Configure EXTRACT Process 

GGSCI (gg-11.2.com) view param ext1

EXTRACT ext1
SETENV (ORACLE_SID=”SOURCE”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/gghome/dirdat/ac
TABLE hari.*;

GGSCI (gg-11.2.com ) add extract ext1 tranlog begin now
EXTRACT added.

GGSCI (gg-11.2.com )  add exttrail /u01/gghome/dirdat/ac extract ext1
EXTTRAIL added.

GGSCI (gg-11.2.com as gguser@source)  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:00:40

Step4:-Configure Pump Process  

GGSCI (gg-11.2.com )  view param dpump1

EXTRACT dpump1
USERID gguser@source, PASSWORD gguser
RMTHOST gg-12.2, MGRPORT 7810
RMTTRAIL /u01/gghome/dirdat/ad
DDL INCLUDE ALL
TABLE hari.*;

GGSCI (gg-11.2.com) add extract dpump1 exttrailsource /u01/gghome/dirdat/ac
EXTRACT added.

GGSCI (gg-11.2.com ) add rmttrail /u01/gghome/dirdat/ad extract dpump1
RMTTRAIL added.

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED DPUMP1 00:00:00 00:01:00
EXTRACT STOPPED EXT1 00:00:00 00:04:40

Step5:-Start all the process and Check

GGSCI (gg-11.2.com )  start ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (gg-11.2.com )  start dpump1

Sending START request to MANAGER …
EXTRACT DPUMP1 starting

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:04:33
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Target side 12c database GG Configuration

Step1:-Configure manager 

GGSCI (gg-12.2.com) 9> dblogin userid gguser,password gguser
Successfully logged into database.

GGSCI (gg-12.2.com ) info mgr

Manager is running (IP port gg-12.2.com.7810, Process ID 3999).

GGSCI (gg-12.2.com ) view param mgr

PORT 7810

Step2:-Checkpoint table creation

GGSCI (gg-12.2.com)add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.

Step3:-Configure Replicat Process 

GGSCI (gg-12.2.com) view param rep1

REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;

GGSCI (gg-12.2.com )  add replicat rep1 exttrail /u01/gghome/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.

GGSCI (gg-12.2.com ) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:03

Source side check before datapump starts

GGSCI (gg-11.2.com ) info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:00
EXTRACT RUNNING EXT1 00:00:00 00:00:10

Start the initial dataload using Datapump on source side 11g database

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1584462

SQL> select count(*) from hari.chennai;

COUNT(*)
———-
1835008

Step1:-Export the table “chennai” using flashback_scn

[oracle@gg-11 gghome]$ expdp system/oracle dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462

Export: Release 11.2.0.3.0 - Production on Tue Jul 31 20:02:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/source/dpdump/hari1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:02:45

Step2:-copy the datapump files to 11g server to 12c server

[oracle@gg-11 gghome]$ cd /u01/app/oracle/admin/source/dpdump/
[oracle@gg-11 dpdump]$ scp hari1.dmp oracle@192.168.2.157:/u01/app/oracle/admin/source/dpdump/
The authenticity of host ‘192.168.2.157 (192.168.2.157)’ can’t be established.
RSA key fingerprint is c2:3d:72:16:52:01:ae:5c:41:6b:34:f5:c7:a1:df:74.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.2.157’ (RSA) to the list of known hosts.
oracle@192.168.2.157’s password:
hari1.dmp 100% 73MB 72.8MB/s 00:01

Step3:-After export i am inserting more records in Chennai table

[oracle@gg-11 ~]$ sqlplus hari/hari
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 31 20:06:47 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> insert into chennai select * from chennai;
1835008 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from chennai;
COUNT(*)
———-
3670016     

Step4:-Now we are going to Import 12c database (1835008 Rows)

[oracle@gg-12 dpdump]$ impdp system/oracle dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR

Import: Release 12.2.0.1.0 - Production on Tue Jul 31 20:09:23 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 20:09:57 2018 elapsed 0 00:00:24

Step5:-Target (12c) side going to start replicat process Using CSN 

GGSCI (gg-12.2.com ) start replicat rep1 aftercsn 1584462    ---(this scn number we get from 11g database)

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg-12.2.com )info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01

Note :-Already initial load was completed using datapump,
Now extract was capturing all the changes happening on the source database 11g. These changes will now get applied to the target database 12c by the replicat process

[oracle@gg-12 dpdump]$ sqlplus hari/hari

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 20:19:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Tue Jul 31 2018 20:14:07 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from chennai;

COUNT(*)
----------
3670016 

Repoint the users from 11g to 12c server.

Successfully completed without downtime upgrade

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

 

 

Oracle Database Rolling Upgrade From 11g to 12c Using a Data Guard

Description:- 

  • Database Rolling Upgrade using Transient Logical Standby (SQL Apply)
  • Allows for running different database versions during rolling upgrade window.
  • Gives you more time to test the new version, before activating it.
  • In case you already use Data Guard – no additional hardware/storage(*) footprint,license fees and knowledge(*) necessary.
  • Near zero downtime – reduced to one switchover (two, if you go back).
  • Fairly simple method, compared to some other replication technologies.But with some restrictions.

High Level Steps:-

Environment Details:- Lets Start the Demo:-

  • We have primary & standby database running in 11.2.0.4 Here
  • Already installed oracle 12.2.0.1 binaries on both nodes

Step1:-Check the Standby database status

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                      14              14             0

Step2:-Stop the mrp process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:-Open the database in read only mode

SQL>ALTER DATABASE OPEN READ ONLY;

Database altered.

Step 4:-Ensure Flashback is ON in both primary and standby databases

SQL>SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
————–
YES

Step 5:-Create restore point at Primary database

SQL>CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

GUARANTEE_FLASHBACK_DATABASE              Name
———————————–    ——————–
YES                                                                                          BEFORE_UPGRADE

START APPLY PROCESS:

Step 6:- Start the log apply process by executing the below query in standby and make sure the standby is in sync with primary

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

LOGICAL STANDBY DATABASE CONVERSION:

Step 1:-Stop the media recovery at standby database

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 2:-Build data dictionary at primary database for logical standby database

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:-Convert physical standby database to logical standby database (ensure database is not in open state)

SQL>ALTER DATABASE CLOSE;

Database altered.

SQL>ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.

Step 4:-Restart the database, once it gets started logical standby role will be enabled

SQL> SHUT IMMEDIATE
SQL> STARTUP
SQL>SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB Unique Name Database Role
———————— —————-
ORCL_PROD102 LOGICAL STANDBY

Step 5:-Start the log shipping process at logical standby database

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Check the alert log for the Logminer process would have been started in the background:-

$tail -100f alert_ORCL.log

LOGSTDBY Apply process AS02 started with server id=2 pid=46 OS id=38615
LOGMINER: End mining logfile for session 1 thread 1 sequence 16, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_16_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 17, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_17_981941460.arc
LOGMINER: End mining logfile for session 1 thread 1 sequence 17, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_17_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 18, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_18_981941460.arc
LOGMINER: End mining logfile for session 1 thread 1 sequence 18, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_18_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 19, /oradb/app/oracle/oradata/ORCL_PROD102/redo05.log

Also you can notice archive log sequence got reset in Standby:-

SQL>ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradb/app/oracle/archive/orcl_prod102
Oldest online log sequence         1
Next log sequence to archive    2
Current log sequence                  2

Step 6:-Check if the data dictionary build is completed by executing below query in primary

SQL>SELECT * FROM V$LOGSTDBY_STATE WHERE STATE = ‘LOADING DICTIONARY’;

no rows selected

START THE UPGRADE PROCESS:

Step 1:-Upgrade Logical standby database to Oracle 12c (12.2.0.1.0) database version

Create the below directory before starting the upgrade and run the preupgrade.jar from Oracle 12.2.0.1 home as below and check the logs in the below created log directory if any prerequisite as to be done before the upgrade:-

$ mkdir -p /oradb/app/oracle/scripts/logs/12cR2
$ cd /oradb/app/oracle/scripts/logs/12cR2
$ sqlplus ‘/ as sysdba’
$ /oradb/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /oradb/app/oracle/scripts/logs/12cR2

Upgrade can be done either using DBUA or Manual upgrade. Here we followed manual upgrade:
Shutdown logical standby database and it’s listener at Oracle 11g (11.2.0.4)

SQL>SHUTDOWN IMMEDIATE

$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 21-JUL-2018 03:54:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod102.oracledbwr.com)(PORT=1624)))
The command completed successfully

Step 2:-Copy listener.ora and tnsnames.ora to 12c ORACLE_HOME and edit listener.ora to 12c ORACLE_HOME. Copy pfile and password file to Oracle 12c (12.2.0.1.0) home

$ export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=ORCL
$ sqlplus ‘/ as sysdba’

SQL> STARTUP UPGRADE

Step 3:-Start the manual upgrade process using catctl.pl as below

$ cd $ORACLE_HOME/rdbms/admin
$ /oradb/app/oracle/product/12.2.0.1/db_1/perl/bin/perl catctl.pl -n 4 -l /oradb/app/oracle/scripts/logs/12cR2 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 = /oradb/app/oracle/scripts/logs/12cR2
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]
/oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/orahome = [/oradb/app/oracle/product/12.2.0.1/db_1]
/oradb/app/oracle/product/12.2.0.1/db_1/bin/orabasehome = [/oradb/app/oracle/pro
catctlGetOrabase = [/oradb/app/oracle/product/12.2.0.1/db_1]
Analyzing file /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/oradb/app/oracle/scripts/logs/12cR2]
catcon: ALL catcon-related output will be written to [/oradb/app/oracle/scripts/
catcon: See [/oradb/app/oracle/scripts/logs/12cR2/catupgrd*.log] files for outpu
catcon: See [/oradb/app/oracle/scripts/logs/12cR2/catupgrd_*.lst] files for spoo
Number of Cpus = 1
Database Name = ORCL_PROD102
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [ORCL_PROD102]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO
Not Installed [DV MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-115] Start Time:[2018_07_21 04:07:28]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORCL_PROD102] Files:1 Time: 142s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORCL_PROD102] Files:5 Time: 44s
Restart Phase #:2 [ORCL_PROD102] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ORCL_PROD102] Files:19 Time: 22s
Restart Phase #:4 [ORCL_PROD102] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [ORCL_PROD102] Files:6 Time: 16s
***************** Catproc Start ****************
Serial Phase #:6 [ORCL_PROD102] Files:1 Time: 13s
***************** Catproc Types ****************
Serial Phase #:7 [ORCL_PROD102] Files:2 Time: 12s
Restart Phase #:8 [ORCL_PROD102] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [ORCL_PROD102] Files:69 Time: 48s
Restart Phase #:10 [ORCL_PROD102] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [ORCL_PROD102] Files:1 Time: 42s
Restart Phase #:12 [ORCL_PROD102] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [ORCL_PROD102] Files:97 Time: 21s
Restart Phase #:14 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:15 [ORCL_PROD102] Files:118 Time: 24s
Restart Phase #:16 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:17 [ORCL_PROD102] Files:13 Time: 4s
Restart Phase #:18 [ORCL_PROD102] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [ORCL_PROD102] Files:33 Time: 34s
Restart Phase #:20 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:21 [ORCL_PROD102] Files:3 Time: 9s
Restart Phase #:22 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:23 [ORCL_PROD102] Files:24 Time: 154s
Restart Phase #:24 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:25 [ORCL_PROD102] Files:11 Time: 50s
Restart Phase #:26 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:27 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:28 [ORCL_PROD102] Files:3 Time: 3s
Serial Phase #:29 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:30 [ORCL_PROD102] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:32 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:34 [ORCL_PROD102] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORCL_PROD102] Files:283 Time: 30s
Serial Phase #:36 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:37 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:38 [ORCL_PROD102] Files:1 Time: 4s
Restart Phase #:39 [ORCL_PROD102] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [ORCL_PROD102] Files:3 Time: 56s
Restart Phase #:41 [ORCL_PROD102] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORCL_PROD102] Files:13 Time: 79s
Restart Phase #:43 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:44 [ORCL_PROD102] Files:12 Time: 31s
Restart Phase #:45 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:46 [ORCL_PROD102] Files:2 Time: 1s
Restart Phase #:47 [ORCL_PROD102] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [ORCL_PROD102] Files:1 Time: 7s
Restart Phase #:49 [ORCL_PROD102] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORCL_PROD102] Files:1 Time: 25s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:52 [ORCL_PROD102] Files:1 Time: 0s
**************** Upgrading Java ****************
Serial Phase #:53 [ORCL_PROD102] Files:1 Time: 342s
Restart Phase #:54 [ORCL_PROD102] Files:1 Time: 0s
***************** Upgrading XDK ****************
Serial Phase #:55 [ORCL_PROD102] Files:1 Time: 38s
Restart Phase #:56 [ORCL_PROD102] Files:1 Time: 1s
********* Upgrading APS,OLS,DV,CONTEXT *********
Serial Phase #:57 [ORCL_PROD102] Files:1 Time: 76s
***************** Upgrading XDB ****************
Restart Phase #:58 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:60 [ORCL_PROD102] Files:3 Time: 35s
Serial Phase #:61 [ORCL_PROD102] Files:3 Time: 6s
Parallel Phase #:62 [ORCL_PROD102] Files:9 Time: 3s
Parallel Phase #:63 [ORCL_PROD102] Files:24 Time: 4s
Serial Phase #:64 [ORCL_PROD102] Files:4 Time: 6s
Serial Phase #:65 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:66 [ORCL_PROD102] Files:30 Time: 4s
Serial Phase #:67 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:68 [ORCL_PROD102] Files:6 Time: 3s
Serial Phase #:69 [ORCL_PROD102] Files:2 Time: 20s
Serial Phase #:70 [ORCL_PROD102] Files:3 Time: 76s
Restart Phase #:71 [ORCL_PROD102] Files:1 Time: 1s
********* Upgrading CATJAVA,OWM,MGW,RAC ********
Serial Phase #:72 [ORCL_PROD102] Files:1 Time: 86s
**************** Upgrading ORDIM ***************
Restart Phase #:73 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:75 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:76 [ORCL_PROD102] Files:2 Time: 75s
Serial Phase #:77 [ORCL_PROD102] Files:1 Time: 59s
Restart Phase #:78 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:79 [ORCL_PROD102] Files:2 Time: 17s
Serial Phase #:80 [ORCL_PROD102] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:81 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:83 [ORCL_PROD102] Files:1 Time: 81s
Serial Phase #:84 [ORCL_PROD102] Files:1 Time: 2s
Restart Phase #:85 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:86 [ORCL_PROD102] Files:1 Time: 28s
Restart Phase #:87 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:88 [ORCL_PROD102] Files:3 Time: 135s
Restart Phase #:89 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:90 [ORCL_PROD102] Files:1 Time: 4s
Restart Phase #:91 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:92 [ORCL_PROD102] Files:1 Time: 2s
Restart Phase #:93 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:94 [ORCL_PROD102] Files:4 Time: 89s
Restart Phase #:95 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:96 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:97 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:98 [ORCL_PROD102] Files:2 Time: 40s
Restart Phase #:99 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:100 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:101 [ORCL_PROD102] Files:1 Time: 0s
*********** Upgrading Misc. ODM, OLAP **********
Serial Phase #:102 [ORCL_PROD102] Files:1 Time: 36s
**************** Upgrading APEX ****************
Restart Phase #:103 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:104 [ORCL_PROD102] Files:1 Time: 581s
Restart Phase #:105 [ORCL_PROD102] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:106 [ORCL_PROD102] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:107 [ORCL_PROD102] Files:1 Time: 166s
********** End PDB Application Upgrade *********
Serial Phase #:108 [ORCL_PROD102] Files:1 Time: 0s
******************* Migration ******************
Serial Phase #:109 [ORCL_PROD102] Files:1 Time: 66s
Serial Phase #:110 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:111 [ORCL_PROD102] Files:1 Time: 62s
***************** Post Upgrade *****************
Serial Phase #:112 [ORCL_PROD102] Files:1 Time: 334s
**************** Summary report ****************
Serial Phase #:113 [ORCL_PROD102] Files:1 Time: 2s
Serial Phase #:114 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:115 [ORCL_PROD102] Files:1 Time: 34s
------------------------------------------------------
Phases [0-115] End Time:[2018_07_21 05:04:04]
------------------------------------------------------
Grand Total Time: 3402s
LOG FILES: (/oradb/app/oracle/scripts/logs/12cR2/catupgrd*.log)
Upgrade Summary Report Located in:
/oradb/app/oracle/scripts/logs/12cR2/upg_summary.log
Grand Total Upgrade Time: [0d:0h:56m:42s]
Once the upgrade is completed, check the log files from the log directory. Then update the timezone by Doc ID 1585343.1 and run the postupgrade_fixups.sql

Step 4:-Once the Upgrade is completed start the database and listener and check the upgraded database

$ lsnrctl start
$ sqlplus ‘/ as sysdba’
SQL> STARTUP
SQL> col COMP_ID format A10
SQL> col COMP_NAME format A30
SQL> col VERSION format A10
SQL> col STATUS format A15
SQL> SELECT SUBSTR(COMP_ID,1,15) COMP_ID,
SUBSTR(COMP_NAME,1,30) COMP_NAME,
SUBSTR(VERSION,1,10) VERSION,STATUS
FROM DBA_REGISTRY
/

COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- ---------------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
CATPROC Oracle Database Packages and T 12.2.0.1.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
XML Oracle XDK 12.2.0.1.0 UPGRADED
CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED
APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED
CONTEXT Oracle Text 12.2.0.1.0 UPGRADED
XDB Oracle XML Database 12.2.0.1.0 UPGRADED
ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED
SDO Spatial 12.2.0.1.0 UPGRADED
XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED
APEX Oracle Application Express 5.0.4.00.1 UPGRADED

Step 5:-Start the log apply process at Logical standby which is now Oracle 12c (12.2.0.1.0) version. Primary still in Oracle 11g (11.2.0.4.0) version

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SWITCHOVER FROM LOGICAL STANDBY TO PRIMARY:-

Step 1:-Switchover the roles. Now primary database will become Oracle 11g (11.2.0.4.0) Logical standby and other side Oracle 12c (12.2.0.1.0) will be as primary database

1) Execute the below query at primary(Oracle 11.2.0.4):

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

2) Execute the below query at standby(Oracle 12.2.0.1):

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

3) Now the Primary database become as Standby database:

SQL> SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB Unique Name Database Role
———————— —————-
ORCL_PROD101 LOGICAL STANDBY

4) Standby database become Primary database:

SQL> SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB_UNIQUE_NAME DATABASE_ROLE
—————————— —————-
ORCL_PROD102 PRIMARY

Step 2:-We cannot directly convert Logical standby database to physical standby database, hence flashback to guarantee restore point which was created prior to upgrade

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE;

Flashback complete.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

Now the current logical standby database Oracle 11g (11.2.0.4.0) becomes physical standby database. i.e Logical to physical conversion:-

Step 1:-Shutdown database and start Physical standby database with Oracle 12c (12.2.0.1.0) binary

SQL> SHUTDOWN IMMEDIATE

$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 21-JUL-2018 05:34:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod101.oracledbwr.com)(PORT=1624)))
The command completed successfully

Step 2:-Copy listener.ora and tnsnames.ora to 12c ORACLE_HOME and edit listener.ora to 12c ORACLE_HOME. Copy pfile and password file to Oracle 12c (12.2.0.1.0) home

$ export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=ORCL
$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-JUL-2018 05:39:46
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /oradb/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /oradb/app/oracle/diag/tnslsnr/prod101/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod101.oracledbwr.com)(PORT=1624)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod101.oracledbwr.com)(PORT=1624)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 21-JUL-2018 05:39:46
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prod101/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod101.oracledbwr.com)(PORT=1624)))
Services Summary...
Service "ORCL_PROD101" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ sqlplus ‘/ as sysdba’
SQL> STARTUP MOUNT

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

ENABLE LOG SHIPPING:

Step 3:-Execute the below statement to apply all the archived log in current standby which are generated in current primary during the upgrade

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE THROUGH NEXT SWITCHOVER DISCONNECT;

Database altered.

$ tail -f alert_ORCL.log

Attempt to start background Managed Standby Recovery process (ORCL)
Starting background process MRP0
2018-07-21T05:46:17.785256+05:30
MRP0 started with pid=46, OS id=41380
2018-07-21T05:46:17.788222+05:30
MRP0: Background Managed Standby Recovery process started (ORCL)
2018-07-21T05:46:22.822202+05:30
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 1079326
2018-07-21T05:46:23.303437+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_15_981941460.arc
2018-07-21T05:46:23.667888+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_16_981941460.arc
2018-07-21T05:46:23.903688+05:30
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE THROUGH NEXT SWITCHOVER DISCONNECT
2018-07-21T05:46:24.209195+05:30
Resize operation completed for file# 1, old size 757760K, new size 768000K
2018-07-21T05:46:24.667692+05:30
Resize operation completed for file# 1, old size 768000K, new size 778240K
2018-07-21T05:46:24.947548+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_17_981941460.arc
2018-07-21T05:46:25.354518+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_1_982035897.arc
2018-07-21T05:46:26.299705+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_2_982035897.arc
2018-07-21T05:46:29.570245+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_3_982035897.arc
2018-07-21T05:46:31.737226+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_4_982035897.arc
2018-07-21T05:46:36.087151+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_5_982035897.arc
2018-07-21T05:46:37.658848+05:30

In Current Primary:-

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradb/app/oracle/archive/orcl_prod102
Oldest online log sequence 133
Next log sequence to archive 135
Current log sequence 135

In Current Standby:-

$ tail -20f alert_ORCL.log

Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_130_982035897.arc
2018-07-21T06:02:35.974947+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_131_982035897.arc
2018-07-21T06:02:37.840241+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_132_982035897.arc
2018-07-21T06:02:38.248442+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_133_982035897.arc
2018-07-21T06:02:43.617234+05:30
Resize operation completed for file# 2, old size 962560K, new size 972800K
2018-07-21T06:02:43.880337+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_134_982035897.arc
2018-07-21T06:02:53.665249+05:30
Media Recovery Waiting for thread 1 sequence 135 (in transit)
2018-07-21T06:02:53.677109+05:30
Recovery of Online Redo Log: Thread 1 Group 4 Seq 135 Reading mem 0
Mem# 0: /oradb/app/oracle/oradata/ORCL_PROD101/redo04.log

Step 4:-Check the flashback restore point and drop it

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;
GUARANTEE_FLASHBACK_DATABASE Name
—————————— ————————
YES BEFORE_UPGRADE

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
YES

SQL> alter database flashback off;
Database altered.

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY

SQL> drop restore point BEFORE_UPGRADE;
Restore point dropped.

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
NO

SQL> ALTER SYSTEM SET COMPATIBLE = ‘12.2.0.1.0’ SCOPE=SPFILE;
System altered.

SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
———- ——————– ——————– ———
132 21-JUL-2018 05:25:25 21-JUL-2018 05:25:28 YES
133 21-JUL-2018 05:25:28 21-JUL-2018 05:40:51 YES
134 21-JUL-2018 05:40:51 21-JUL-2018 06:01:46 YES
135 21-JUL-2018 06:01:46 21-JUL-2018 06:11:50 NO

CURRENT PRIMARY DATABASE:-

Step 1:-Convert primary database to physical standby database and shutdown the database. It can be used in future

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.

CURRENT STANDBY DATABASE:-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

Step 2:-Now open the database

SQL> ALTER DATABASE OPEN;
Database altered.

Step 3:-Check the components have been upgraded to 12c

SQL> col COMP_ID format A10
SQL> col COMP_NAME format A30
SQL> col VERSION format A10
SQL> col STATUS format A15
SQL> SELECT SUBSTR(COMP_ID,1,15) COMP_ID,
SUBSTR(COMP_NAME,1,30) COMP_NAME,
SUBSTR(VERSION,1,10) VERSION,STATUS
FROM DBA_REGISTRY
/

COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- ---------------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
CATPROC Oracle Database Packages and T 12.2.0.1.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
XML Oracle XDK 12.2.0.1.0 UPGRADED
CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED
APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED
CONTEXT Oracle Text 12.2.0.1.0 UPGRADED
XDB Oracle XML Database 12.2.0.1.0 UPGRADED
ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED
SDO Spatial 12.2.0.1.0 UPGRADED
XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED
APEX Oracle Application Express 5.0.4.00.1 UPGRADED

Done…!!! We have successfully upgrade the database from 11g to 12c.

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