Rolling Upgrade Oracle 11.2.0.2 database to Oracle 12.1.0.2 using Standby with No Downtime

It is possible to use Standby database to upgrade a database with near no downtime. Application may experience a failover/switchover but no real downtime window that really interrupt the continued availability of application. This is using Oracle Logical standby feature as that allows application to connect to the database with read/write capability.

This is no-RAC environment currently primary as MAIN and standby as MAINSB.

Primary: MAIN
Standby: MAINSB

1. Check the standby log files exist. Standby Log files are used when Primary LGWR try to archive redo changes to Standby environment. If LGWR cannot archive to standby logs for some reason, then it goes for archive file shipping and is done by the ARC process.

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
/u04/oradata/MAIN/MAIN/redo01.log
/u04/oradata/MAIN/MAIN/redo02.log
/u04/oradata/MAIN/MAIN/redo03.log
/u04/oradata/MAIN/standby_redo01.log
/u04/oradata/MAIN/standby_redo02.log
/u04/oradata/MAIN/standby_redo03.log
/u04/oradata/MAIN/standby_redo04.log

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
/u04/oradata/MAINSB/MAIN/redo01.log
/u04/oradata/MAINSB/MAIN/redo02.log
/u04/oradata/MAINSB/MAIN/redo03.log
/u04/oradata/MAINSB/standby_redo01.log
/u04/oradata/MAINSB/standby_redo02.log
/u04/oradata/MAINSB/standby_redo03.log
/u04/oradata/MAINSB/standby_redo04.log

2. Check the protection mode. It should be “maximum availability” or “Maximum performance”.

3. Check if flashback on or not. If not turn on flashback. Flashback should be on for this upgrade process to proceed.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————————————————
YES

4. We need a guaranteed restore point on Primary and Standby side. This is the restore point that we are going to go back on primary after standby is upgraded.

Create restore point BEFORE_0001 guarantee flashback database

On Primary

SQL> Create restore point BEFORE_0001 guarantee flashback database;

Restore point created.

On Standby:

SQL> Create restore point BEFORE_0001 guarantee flashback database;
Create restore point BEFORE_0001 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point ‘BEFORE_0001’.
ORA-01153: an incompatible media recovery is active

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> Create restore point BEFORE_0001 guarantee flashback database;

Restore point created.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

5. We are going to convert Physical STANDBY to logical standby. In this process, we will have to cancel managed recovery (MRP) from standby and build data dictionary from Primary. This is basically the general process to establish a logical standby database.

Standby:

SQL> alter database recover managed standby database cancel;

Database altered.

Then on Primary:

SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed.

SQL>

From Alert Log:

Mon Apr 10 14:06:58 2017
alter database add supplemental log data (primary key, unique index) columns
SUPLOG: Previous supplemental logging attributes at scn = 3223727
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 3223727
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
Completed: alter database add supplemental log data (primary key, unique index) columns
alter database add supplemental log data for procedural replication
SUPLOG: Previous supplemental logging attributes at scn = 3223735
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = OFF
SUPLOG: New supplemental logging attributes at scn = 3223735
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
SUPLOG: procedural replication = ON
Completed: alter database add supplemental log data for procedural replication
Mon Apr 10 14:06:58 2017
Logminer Bld: Build started
ALTER SYSTEM SWITCH ALL LOGFILE start (MAIN)
ALTER SYSTEM SWITCH ALL LOGFILE complete (MAIN)
Mon Apr 10 14:06:58 2017
Thread 1 advanced to log sequence 99 (LGWR switch)
Current log# 3 seq# 99 mem# 0: /u04/oradata/MAIN/MAIN/redo03.log
Mon Apr 10 14:06:58 2017
Archived Log entry 227 added for thread 1 sequence 98 ID 0x25ea1fd4 dest 1:
Mon Apr 10 14:06:58 2017
LNS: Standby redo logfile selected for thread 1 sequence 99 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 14:06:58 2017
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 3223727 LockdownSCN is 3223744
ALTER SYSTEM ARCHIVE LOG
Thread 1 advanced to log sequence 100 (LGWR switch)
Current log# 1 seq# 100 mem# 0: /u04/oradata/MAIN/MAIN/redo01.log
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 229 added for thread 1 sequence 99 ID 0x25ea1fd4 dest 1:
Mon Apr 10 14:07:03 2017
Logminer Bld: Done
Thread 1 cannot allocate new log, sequence 101
Checkpoint not complete
Current log# 1 seq# 100 mem# 0: /u04/oradata/MAIN/MAIN/redo01.log
Thread 1 advanced to log sequence 101 (LGWR switch)
Current log# 2 seq# 101 mem# 0: /u04/oradata/MAIN/MAIN/redo02.log
Archived Log entry 231 added for thread 1 sequence 100 ID 0x25ea1fd4 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination LOG_ARCHIVE_DEST_2

From Standby Log (MAINSB):

Mon Apr 10 14:06:44 2017
Archived Log entry 212 added for thread 1 sequence 98 ID 0x25ea1fd4 dest 1:
Mon Apr 10 14:06:44 2017
RFS[3]: Selected log 4 for thread 1 sequence 99 dbid 538973938 branch 848052247
RFS[3]: Selected log 5 for thread 1 sequence 100 dbid 538973938 branch 848052247
Mon Apr 10 14:06:48 2017
Archived Log entry 213 added for thread 1 sequence 99 ID 0x25ea1fd4 dest 1:
RFS[3]: Selected log 4 for thread 1 sequence 101 dbid 538973938 branch 848052247
Mon Apr 10 14:06:50 2017
Archived Log entry 214 added for thread 1 sequence 100 ID 0x25ea1fd4 dest 1:

6. Convert Physical standby database to Logical Standby.

Physical Standby:

SQL> alter database recover to logical standby keep identity;
alter database recover to logical standby keep identity
*
ERROR at line 1:
ORA-19953: database should not be open

SQL> alter database close;

Database altered.

SQL> alter database recover to logical standby keep identity;

Database altered.
SQL> select database_role from v$database;

DATABASE_ROLE
————————————————
LOGICAL STANDBY

From Standby Alert Log(MAINSB):

alter database recover to logical standby keep identity
Media Recovery Start: Managed Standby Recovery (MAINSB)
started logmerger process
Mon Apr 10 14:11:18 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 3 slaves
Media Recovery Log /u01/oradata/MAINSB/1_98_848052247.dbf
Media Recovery Log /u01/oradata/MAINSB/1_99_848052247.dbf
Media Recovery Log /u01/oradata/MAINSB/1_100_848052247.dbf
Incomplete Recovery applied until change 3224762 time 04/10/2017 14:07:03
Media Recovery Complete (MAINSB)
krsv_proc_kill: Killing 4 processes (all RFS)
Mon Apr 10 14:11:22 2017
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 3224762
Resetting resetlogs activation ID 636100564 (0x25ea1fd4)
Online log /u04/oradata/MAINSB/MAIN/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u04/oradata/MAINSB/MAIN/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u04/oradata/MAINSB/MAIN/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3224760
Mon Apr 10 14:11:22 2017
Setting recovery target incarnation to 4
RECOVER TO LOGICAL STANDBY: Complete – Database mounted as logical standby
Completed: alter database recover to logical standby keep identity

7. Restart the logical standby (MAINSB) database

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 513585152 bytes
Fixed Size 2227992 bytes
Variable Size 171966696 bytes
Database Buffers 331350016 bytes
Redo Buffers 8040448 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;

DATABASE_ROLE
————————————————
LOGICAL STANDBY

SQL>

8. Start the logical standby apply process

SQL> alter database start logical standby apply immediate;

Database altered.

SQL>

From Standby (MAINSB):

Mon Apr 10 14:15:59 2017
alter database start logical standby apply immediate
LOGSTDBY: Creating new session for dbid 538973938 starting at scn 0x0000.00000000
LOGSTDBY: Created session of id 1
LOGSTDBY: Attempting to pre-register dictionary build logfiles
LOGMINER: Error 308 encountered, failed to read logfile 1_99_848052247.dbf
LOGMINER: Encountered error 1291 while adding logfile 1_99_848052247.dbf to session 1
LOGSTDBY: Unable to register recovery logfiles, will resend
ALTER DATABASE START LOGICAL STANDBY APPLY (MAINSB)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Mon Apr 10 14:16:00 2017
LSP0 started with pid=31, OS id=24475
Completed: alter database start logical standby apply immediate
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: SpillScn 0, ResetLogScn 0
Mon Apr 10 14:16:25 2017
RFS[2]: Assigned to RFS process 24471
RFS[2]: Opened log for thread 1 sequence 100 dbid 538973938 branch 848052247
Mon Apr 10 14:16:25 2017
RFS[3]: Assigned to RFS process 24427
RFS[3]: Opened log for thread 1 sequence 99 dbid 538973938 branch 848052247
Mon Apr 10 14:16:25 2017
RFS[4]: Assigned to RFS process 24480
RFS[4]: Selected log 4 for thread 1 sequence 101 dbid 538973938 branch 848052247
Mon Apr 10 14:16:25 2017
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 3223744 (0x0000.003130c0)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 3224761 (0x0000.003134b9)
LOGMINER: session_flag 0x1
RFS LogMiner: Registered logfile [/u01/oradata/MAINSB/1_100_848052247.dbf] to LogMiner session id [1]
RFS LogMiner: Registered logfile [/u01/oradata/MAINSB/1_99_848052247.dbf] to LogMiner session id [1]
Mon Apr 10 14:16:26 2017
RFS LogMiner: Registered logfile [/u01/oradata/MAINSB/1_101_848052247.dbf] to LogMiner session id [1]
RFS LogMiner: RFS id [24471] assigned as thread [1] PING handler
Mon Apr 10 14:16:26 2017
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=33 OS id=24484 sid=13 started
Mon Apr 10 14:16:26 2017
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=34 OS id=24488 sid=99 started
Mon Apr 10 14:16:26 2017
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=35 OS id=24492 sid=180 started
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 99, /u01/oradata/MAINSB/1_99_848052247.dbf
Mon Apr 10 14:16:31 2017
Starting background process SMCO
Mon Apr 10 14:16:31 2017
SMCO started with pid=36, OS id=24496
Mon Apr 10 14:16:42 2017
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PROPS$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ENC$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRIBUTE$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_NTAB$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_REFCON$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OPQTYPE$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_KOPM$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PARTOBJ$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SEED$ have been marked unusable
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable
Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
Mon Apr 10 14:16:44 2017
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u04/oradata/MAINSB/MAIN/redo03.log
Mon Apr 10 14:16:46 2017
Archived Log entry 216 added for thread 1 sequence 2 ID 0x25e95b45 dest 1:
Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ENC$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_KOPM$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_NTAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OPQTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_PARTOBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_PROPS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_REFCON$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_SEED$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
Mon Apr 10 14:17:08 2017
LOGMINER: End mining logfiles during dictionary load for session 1
Mon Apr 10 14:17:08 2017
LSP2 started with pid=38, OS id=24505
Mon Apr 10 14:17:10 2017
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 99, /u01/oradata/MAINSB/1_99_848052247.dbf
LOGMINER: End mining logfile during commit scan for session 1 thread 1 sequence 99, /u01/oradata/MAINSB/1_99_848052247.dbf
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 100, /u01/oradata/MAINSB/1_100_848052247.dbf
LOGMINER: End mining logfiles during commit scan for session 1
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 99, /u01/oradata/MAINSB/1_99_848052247.dbf
Mon Apr 10 14:17:14 2017
LOGSTDBY Analyzer process AS00 started with server id=0 pid=38 OS id=24509
Mon Apr 10 14:17:14 2017
LOGSTDBY Apply process AS01 started with server id=1 pid=39 OS id=24513
Mon Apr 10 14:17:14 2017
LOGSTDBY Apply process AS02 started with server id=2 pid=40 OS id=24517
Mon Apr 10 14:17:14 2017
LOGSTDBY Apply process AS05 started with server id=5 pid=43 OS id=24529
Mon Apr 10 14:17:14 2017
LOGSTDBY Apply process AS04 started with server id=4 pid=42 OS id=24525
Mon Apr 10 14:17:14 2017
LOGSTDBY Apply process AS03 started with server id=3 pid=41 OS id=24521
LOGMINER: End mining logfile for session 1 thread 1 sequence 99, /u01/oradata/MAINSB/1_99_848052247.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 100, /u01/oradata/MAINSB/1_100_848052247.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 100, /u01/oradata/MAINSB/1_100_848052247.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 101, /u01/oradata/MAINSB/1_101_848052247.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 101, /u01/oradata/MAINSB/1_101_848052247.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 102, /u04/oradata/MAINSB/standby_redo02.log

SQL> select * from v$logstdby_state;

PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
———— ———- ——————– ——————–
538973938 1 Y IDLE

IDLE indicate that data dictionary build is complete.

9. Next step is the upgrade this logical standby database to 12c. If there is no 12c home already installed, it can be done now. Or if there is 12c Home, You can use that for upgrading this database. I am upgrading to 12.1.0.2 version of 12c. Once upgrade is completed Successfully restart the DB and start SQL apply process. Remember this is done in the Standby (MAINSB) database.

Make sure password file is copied from 11.2 home to new 12c Home. Also make appropriate changes in the listener, if required. If listener is changed then the TNS entries has to be updated for standby to work properly.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL>

ALTER DATABASE START LOGICAL STANDBY APPLY (MAINSB)
Mon Apr 10 15:43:02 2017
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Starting background process LSP0
Mon Apr 10 15:43:02 2017
LSP0 started with pid=34, OS id=811
Completed: alter database start logical standby apply immediate
Mon Apr 10 15:43:04 2017
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 100M, Checkpoint interval = 500M
LOGMINER: SpillScn 3227226, ResetLogScn 1283109
Mon Apr 10 15:43:04 2017
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 0 (0x0000.00000000)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 3225379 (0x0000.00313723)
LOGMINER: session_flag: 0x1
LOGMINER: SessionFlags_krvxsctx: 0xba110dc
LOGMINER: SessionFlags2_krvxsctx: 0x0
LOGMINER: Read buffers: 16
LOGMINER: Memory LWM: limit 10M, LWM 90M, 90%
LOGMINER: Memory Release Limit: 1M
Mon Apr 10 15:43:04 2017
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=53 OS id=815 sid=337 started
Mon Apr 10 15:43:04 2017
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=57 OS id=819 sid=19 started
Mon Apr 10 15:43:04 2017
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=58 OS id=823 sid=170 started
Starting background process LSP2
Mon Apr 10 15:43:05 2017
LSP2 started with pid=59, OS id=827
Mon Apr 10 15:43:08 2017
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 102, /u01/oradata/MAINSB/1_102_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: End mining logfile for session 1 thread 1 sequence 102, /u01/oradata/MAINSB/1_102_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 103, /u01/oradata/MAINSB/1_103_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: End mining logfile for session 1 thread 1 sequence 103, /u01/oradata/MAINSB/1_103_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 104, /u01/oradata/MAINSB/1_104_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGSTDBY Analyzer process AS00 started with server id=0 pid=59 OS id=831
Mon Apr 10 15:43:08 2017
LOGMINER: End mining logfile for session 1 thread 1 sequence 104, /u01/oradata/MAINSB/1_104_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 105, /u01/oradata/MAINSB/1_105_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: End mining logfile for session 1 thread 1 sequence 105, /u01/oradata/MAINSB/1_105_848052247.dbf
Mon Apr 10 15:43:08 2017
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 106, /u04/oradata/MAINSB/standby_redo01.log
Mon Apr 10 15:43:08 2017
LOGSTDBY Apply process AS04 started with server id=4 pid=63 OS id=847
Mon Apr 10 15:43:08 2017
LOGSTDBY Apply process AS05 started with server id=5 pid=64 OS id=851
Mon Apr 10 15:43:08 2017
LOGSTDBY Apply process AS03 started with server id=3 pid=62 OS id=843
Mon Apr 10 15:43:08 2017
LOGSTDBY Apply process AS01 started with server id=1 pid=60 OS id=835
Mon Apr 10 15:43:08 2017
LOGSTDBY Apply process AS02 started with server id=2 pid=61 OS id=83

10. It time for switch over. All applications will be switched over to logical standby database. This is the time we may see some disruption in the application. You may also make sure DATA GUARD status of the standby environment. It should be NONE for applications to update records.

Shutdown all applications connecting to Primary (MAIN)

On Primary(MAIN):

SQL> alter database commit to switchover to logical standby;

Database altered.

SQL>
SQL> select database_role from v$database;

DATABASE_ROLE
————————————————
LOGICAL STANDBY

alter database commit to switchover to logical standby
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (MAIN)
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Enabling database guard to prevent new transactions.
LOGSTDBY: Waiting for transactions in flight at scn 0x0000.0031515c to complete
Waiting for transactions in flight at scn 0x0000.0031515c to complete
Mon Apr 10 15:46:01 2017
Thread 1 advanced to log sequence 107 (LGWR switch)
Current log# 2 seq# 107 mem# 0: /u04/oradata/MAIN/MAIN/redo02.log
Archived Log entry 245 added for thread 1 sequence 106 ID 0x25ea1fd4 dest 1:
LOGSTDBY: Waiting for pending archivals to dest [all].
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived…
All FAL entries have been archived.
Waiting for potential Logical Standby switchover target to become synchronized…
Active, synchronized Logical Standby switchover target has been identified
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn [3232101].
Thread 1 advanced to log sequence 108 (LGWR switch)
Current log# 3 seq# 108 mem# 0: /u04/oradata/MAIN/MAIN/redo03.log
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 107 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 246 added for thread 1 sequence 107 ID 0x25ea1fd4 dest 1:
LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover target
Mon Apr 10 15:46:06 2017
NSA2 started with pid=23, OS id=13660
Thread 1 advanced to log sequence 109 (LGWR switch)
Current log# 1 seq# 109 mem# 0: /u04/oradata/MAIN/MAIN/redo01.log
Archived Log entry 248 added for thread 1 sequence 108 ID 0x25ea1fd4 dest 1:
LOGSTDBY: Switchover complete (MAIN)
LOGSTDBY: enabling scheduler job queue processes.
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to logical standby

11. Make standby database as primary. This is the logical standby database that we just upgrade. Once it has been made primary, application can be restarted after making the TNS change. TNS change is to point the application to new standby (logical) environment temporarily.

On Primary:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oradata/MAIN
Oldest online log sequence 107
Next log sequence to archive 109
Current log sequence 109

On standby (MAINSB):

SQL> select file_name,thread# trd, sequence#,
2 to_char(timestamp, ‘hh:mi:ss’) timestamp, timestamp,
3 (case when l.next_change# < p.read_scn then 'YES' 4 when l.first_change# < p.applied_scn then 'CURRENT' 5 else 'NO' end) applied 6 from dba_logstdby_log l, dba_logstdby_progress p 7 order by thread#, first_change#; FILE_NAME TRD SEQUENCE# TIMESTAMP TIMESTAMP APPLIED ------------------------------------------- --- ---------- ------------------------ --------------- ---------------------/u01/oradata/MAINSB/1_99_848052247.dbf 1 99 02:16:25 10-APR-17 YES /u01/oradata/MAINSB/1_100_848052247.dbf 1 100 02:16:25 10-APR-17 YES /u01/oradata/MAINSB/1_101_848052247.dbf 1 101 02:16:26 10-APR-17 YES /u01/oradata/MAINSB/1_102_848052247.dbf 1 102 02:39:26 10-APR-17 YES /u01/oradata/MAINSB/1_103_848052247.dbf 1 103 03:23:42 10-APR-17 YES /u01/oradata/MAINSB/1_104_848052247.dbf 1 104 03:23:42 10-APR-17 YES /u01/oradata/MAINSB/1_105_848052247.dbf 1 105 03:27:31 10-APR-17 YES /u01/oradata/MAINSB/1_106_848052247.dbf 1 106 03:45:47 10-APR-17 YES /u01/oradata/MAINSB/1_107_848052247.dbf 1 107 03:45:51 10-APR-17 CURRENT Upto sequence 106 is applied, and 107 is the current. SQL> alter database commit to switchover to primary;

Database altered.

SQL>

From alert Log (MAINSB):

alter database commit to switchover to primary
Mon Apr 10 15:54:01 2017
ALTER DATABASE SWITCHOVER TO PRIMARY (MAINSB)
Mon Apr 10 15:54:01 2017
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (MAINSB)
Mon Apr 10 15:54:01 2017
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY: primary: [538973938]
LOGSTDBY: first scn: [0x0000.00000000]
LOGSTDBY: end scn: [0x0000.0031516b]
LOGSTDBY: processed scn: [0x0000.0031516c]
LOGSTDBY: Preparing to Create Detached Dictionary Build, pre-lockdown scn is [0x0000.0037fe7e]
Mon Apr 10 15:54:01 2017
Logminer Bld: Build started
Mon Apr 10 15:54:01 2017
ALTER SYSTEM SWITCH ALL LOGFILE start (MAINSB)
Mon Apr 10 15:54:01 2017
ALTER SYSTEM SWITCH ALL LOGFILE complete (MAINSB)
Mon Apr 10 15:54:01 2017
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: /u04/oradata/MAINSB/MAIN/redo01.log
Mon Apr 10 15:54:02 2017
Mon Apr 10 15:54:02 2017
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 3669636 LockdownSCN is 3669636
LOGSTDBY: Starting SCN of new stream from recent lockdown [0x0000.0037fe84]
Starting background process LSP1
Mon Apr 10 15:54:02 2017
LSP1 started with pid=32, OS id=991
Mon Apr 10 15:54:02 2017
LOGSTDBY: LogMiner Dictionary Build Process Created
LOGSTDBY Event: Starting Full LogMiner Dictionary Build
Mon Apr 10 15:54:02 2017
Logminer Bld: Build started
Mon Apr 10 15:54:02 2017
ALTER SYSTEM SWITCH ALL LOGFILE start (MAINSB)
Mon Apr 10 15:54:02 2017
ALTER SYSTEM SWITCH ALL LOGFILE complete (MAINSB)
Mon Apr 10 15:54:02 2017
Thread 1 advanced to log sequence 53 (LGWR switch)
Current log# 2 seq# 53 mem# 0: /u04/oradata/MAINSB/MAIN/redo02.log
Mon Apr 10 15:54:02 2017
Archived Log entry 265 added for thread 1 sequence 51 ID 0x25e95b45 dest 1:
Mon Apr 10 15:54:02 2017
Archived Log entry 266 added for thread 1 sequence 52 ID 0x25e95b45 dest 1:
Mon Apr 10 15:54:02 2017
Mon Apr 10 15:54:02 2017
Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 3669645 LockdownSCN is 3669645
Mon Apr 10 15:54:04 2017
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [3669706].
Mon Apr 10 15:54:04 2017
Thread 1 cannot allocate new log, sequence 54
Checkpoint not complete
Current log# 2 seq# 53 mem# 0: /u04/oradata/MAINSB/MAIN/redo02.log
Mon Apr 10 15:54:05 2017
ARC0: STARTING ARCH PROCESSES
Mon Apr 10 15:54:05 2017
Thread 1 advanced to log sequence 54 (LGWR switch)
Current log# 3 seq# 54 mem# 0: /u04/oradata/MAINSB/MAIN/redo03.log
Starting background process ARC5
Mon Apr 10 15:54:05 2017
ARC5 started with pid=34, OS id=999
Mon Apr 10 15:54:05 2017
ARC5: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 15:54:05 2017
ALTER SYSTEM ARCHIVE LOG
Mon Apr 10 15:54:06 2017
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 53 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 15:54:07 2017
Thread 1 cannot allocate new log, sequence 55
Checkpoint not complete
Current log# 3 seq# 54 mem# 0: /u04/oradata/MAINSB/MAIN/redo03.log
Shutting down archive processes
Mon Apr 10 15:54:08 2017
ARCH shutting down
ARC5: Archival stopped
Mon Apr 10 15:54:09 2017
Archived Log entry 267 added for thread 1 sequence 53 ID 0x25e95b45 dest 1:
LOGSTDBY: enabling scheduler job queue processes.
Mon Apr 10 15:54:09 2017
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to primary
Mon Apr 10 15:54:09 2017
Thread 1 advanced to log sequence 55 (LGWR switch)
Current log# 1 seq# 55 mem# 0: /u04/oradata/MAINSB/MAIN/redo01.log
Mon Apr 10 15:54:09 2017
Archived Log entry 269 added for thread 1 sequence 54 ID 0x25e95b45 dest 1:
Mon Apr 10 15:54:09 2017
Logminer Bld: Done
Mon Apr 10 15:54:09 2017
******************************************************************
Mon Apr 10 15:54:09 2017
LOGMINER: Dictionary Build: Waiting for txns in-flight at scn 0x0000.0037fe8d [3669645] to complete
LOGMINER: Dictionary Build: All in-flight txns at scn 0x0000.0037fe8d [3669645] completed
Mon Apr 10 15:54:09 2017
TT01: Setting ‘active’ archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Mon Apr 10 15:54:10 2017
Thread 1 cannot allocate new log, sequence 56
Checkpoint not complete
Current log# 1 seq# 55 mem# 0: /u04/oradata/MAINSB/MAIN/redo01.log
Mon Apr 10 15:54:10 2017
TT01: Standby redo logfile selected for thread 1 sequence 55 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 15:54:10 2017
ARC4: Standby redo logfile selected for thread 1 sequence 54 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 15:54:12 2017
Thread 1 advanced to log sequence 56 (LGWR switch)
Current log# 2 seq# 56 mem# 0: /u04/oradata/MAINSB/MAIN/redo02.log
Mon Apr 10 15:54:12 2017
Archived Log entry 271 added for thread 1 sequence 55 ID 0x25e95b45 dest 1:
LOGSTDBY Event: LogMiner Dictionary Build Process Completed
Mon Apr 10 15:54:12 2017
TT01: Standby redo logfile selected for thread 1 sequence 56 for destination LOG_ARCHIVE_DEST_2

12. Flashback the database to the restore Point created before upgrade. This is done in the (MAIN) logical standby database. That was the primary database before the upgrade process started.

SQL> SELECT NAME, SCN,GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

NAME SCN GUARANTEE_FLASHBACK_DATABASE
———- ———- ———
BEFORE_0001 3223034 YES

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 513585152 bytes
Fixed Size 2227992 bytes
Variable Size 163578088 bytes
Database Buffers 339738624 bytes
Redo Buffers 8040448 bytes
Database mounted.
SQL> flashback database to restore point BEFORE_0001;

Flashback complete.

SQL>

From alert log (MAIN):

ALTER DATABASE MOUNT
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/oradata/MAIN
Successful mount of redo thread 1, with mount id 636078102
Allocated 3981120 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Apr 10 16:04:42 2017
RVWR started with pid=18, OS id=13830
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Apr 10 16:04:45 2017
flashback database to restore point BEFORE_0001
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 3 slaves
Flashback Media Recovery Log /u01/oradata/MAIN/1_95_848052247.dbf
Mon Apr 10 16:04:48 2017
Incomplete Recovery applied until change 3223035 time 04/10/2017 13:47:27
Flashback Media Recovery Complete
Completed: flashback database to restore point BEFORE_0001

13. Convert current logical standby (MAIN) to physical standby database. Now that we have gone back to our restore point. Restore point BEFORE_0001 was created before we started the upgrade process. Another important point to be noted is that, when we recover this database we need archived log generated in the standby (MAINSB) to recover this database up to date.

SQL> alter database convert to physical standby;

Database altered.

SQL>

alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (MAIN)
LOGSTDBY: Cleared controlfile of logical standby state.
Clearing standby activation ID 636100564 (0x25ea1fd4)
The primary database controlfile was created using the
‘MAXLOGFILES 16’ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 52428800;
Completed: alter database convert to physical standby

14. Shut down the database. This were the fun part start. Now we are going to start the database with 12c Home and let the MRP recovery process the play the upgrade process that is completed in the original standby. So at this point you should have the 12c Home ready. In order to save time, this can be installed way ahead or even days ahead.

Make sure you copy following files from 11.2 home to new 12c Home

1. Copy spfile
2. Copy password file
3. Set new ORACLE_HOME
4. Also make appropriate changes in the listener, if required. If listener is changed then the TNS entries has to be updated for standby to work properly.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 515899392 bytes
Fixed Size 2926224 bytes
Variable Size 167774576 bytes
Database Buffers 339738624 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL>

SQL> alter database recover managed standby database;

Database altered.

SQL>

alter database recover managed standby database
Mon Apr 10 16:19:56 2017
Attempt to start background Managed Standby Recovery process (MAIN)
Starting background process MRP0
Mon Apr 10 16:19:56 2017
MRP0 started with pid=34, OS id=14218
Mon Apr 10 16:19:56 2017
MRP0: Background Managed Standby Recovery process started (MAIN)
RFS[2]: Opened log for thread 1 sequence 30 dbid 538973938 branch 940947082
Mon Apr 10 16:19:58 2017
Archived Log entry 276 added for thread 1 sequence 28 rlc 940947082 ID 0x25e95b45 dest 2:
RFS[5]: Opened log for thread 1 sequence 31 dbid 538973938 branch 940947082
Mon Apr 10 16:19:59 2017
Archived Log entry 277 added for thread 1 sequence 29 rlc 940947082 ID 0x25e95b45 dest 2:
RFS[3]: Opened log for thread 1 sequence 32 dbid 538973938 branch 940947082
Mon Apr 10 16:20:01 2017
Started logmerger process
Mon Apr 10 16:20:01 2017
Managed Standby Recovery starting Real Time Apply
Mon Apr 10 16:20:02 2017
Archived Log entry 278 added for thread 1 sequence 26 rlc 940947082 ID 0x25e95b45 dest 2:
Mon Apr 10 16:20:02 2017
Archived Log entry 279 added for thread 1 sequence 30 rlc 940947082 ID 0x25e95b45 dest 2:
RFS[2]: Opened log for thread 1 sequence 100 dbid 538973938 branch 848052247
Mon Apr 10 16:20:02 2017
Archived Log entry 280 added for thread 1 sequence 100 rlc 848052247 ID 0x25ea1fd4 dest 2:
Mon Apr 10 16:20:02 2017
Parallel Media Recovery started with 3 slaves
Mon Apr 10 16:20:03 2017
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 3224762
Mon Apr 10 16:20:03 2017
Waiting for all non-current ORLs to be archived…
Mon Apr 10 16:20:03 2017
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u04/oradata/MAIN/MAIN/redo01.log

Clearing online log 1 of thread 1 sequence number 109
Completed: alter database recover managed standby database

Basically, it applied the redo that was generated in the MAINSB that include the upgrade redo as well.
At this time MAIN is upgraded to 12c version of Oracle.

15. Now we can verify the status of the database roles

Current Primary (MAINSB):

SQL> select open_mode,protection_mode from v$database;

OPEN_MODE PROTECTION_MODE

———————— ————————————
READ WRITE MAXIMUM PERFORMANCE

Current Standby (MAIN):

SQL> select open_mode,protection_mode from v$database;

OPEN_MODE PROTECTION_MODE
————————- ————————-
READ ONLY WITH APPLY MAXIMUM PERFORMANCE

Now we can switch over to the old primary to complete the process. This switch over also needs an application down time as we have to make new TNS changes