Database Rolling Upgrades Made Easy by Using a Physical Standby Database
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
Source: https://oracledbwr.com/oracle-database-rolling-upgrade-from-11g-to-12c-using-a-data-guard/
0 Response to "Database Rolling Upgrades Made Easy by Using a Physical Standby Database"
Post a Comment