Rolling Upgrade With an Existing Physical Standby Database

Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment

PRE-UPGRADE TASKS

2. Disable DG Broker
3. Install 19c database software on primary
4. Install 19c database software on standby
5. Apply latest Release Update on 19c standby
6. Apply latest Release Update on 19c primary
7. Database Backup
8. Verify INVLAID OBJECTS
9. Verify protection mode
10. Verify fast_recovery_area size
11. Verify archive log dest size
12. Internal Schema Support
13. Find list of objects are not supported
14. Check the reason for unsupported objects
15. Find list of objects are not supported
16. Refresh MVs
17. Gather DICTIONARY STATS
18. Enable Flashback Database
19. Create Flashback Guaranteed Restore Point (On Primary)
20. Verify GAP

CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY

21. Cancel MRP (Standby)
22. Build the logminer dictionary (Primary)
23. Convert Physical to Logical Standby
24. Verify DATABASE_ROLE
25. Open Logical Database
26. Start SQL Apply
27. Verify GAP

UPGRADE LOGICAL STANDBY

28. Run pre-upgrade script
29. View Pre-upgrade log
30. Verify tablespace sizes for upgrade
31. Run preupgrade_fixups.sql
32. Create GUARANTEED Restore Point (Logical STANDBY)
33. Run DBUA from 19c ORACLE HOME

POST-UPGRADE TASKS WHEN DBUA USING

34. Verify /etc/oratab
35. Verify Timezone version
36. Verify INVALID objects
37. Verify DBA_REGISTRY (HKP_DG)
38. Start SQL Apply
39. Verify GAP

CONVERT PRIMARY(HKP) TO LOGICAL STANDBY

40. Pre-Switchover tasks
41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY
42. Verify alert_HKP.log

SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY

43. Switchover upgraded logical standby to primary
44. Verify alert_HKP_DG.log

CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY

45. Flashback database to restore point
46. Add /etc/oratab entry – 19c
47. Copy password file / initialization file to 19c home/dbs
48. Configure TNS Entries
49. Mount Database using 19c Oracle software
50. Verify TNS Connectivity
51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
52. Enable MRP
53. Verify GAP
54. Disable Flashback Database
55. Drop Restore point
56. Update COMPATIBLE parameter on both primary/standby
57. Verify GAP
58. Revert back parameter values on both primary/standby (In case if you changed)

Below Steps are Optional

59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY
60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY
61. Verify DBA_REGISTRY
62. Configure DG Broker
63. Verify GAP
64. Verify Table
__________________________________________________________________________________________________


0. Overview

++ Perform a rolling database upgrade from 12.2.0.1 to Oracle 19c using a Data Guard physical standby database and transient logical standby database.

++ Rolling upgrade procedure greatly reduces the downtime for an upgrade from hours to a few minutes.

++ Database Downtime only with database switchover duration.

++ Logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle version (12.2.0.1) and apply the redo to a standby database running on a higher Oracle version (19c).

Restore point:

Restore point is nothing but a name associated with a timestamp or an SCN of the database. 

Types of restore point: 
1. Normal restore point
2. Guaranteed restore point

++  The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area).

To Create Guaranteed Restore point:

Prerequisites: 
++ The user must have the SYSDBA system privileges
++ Must have created a flash recovery area
++ The database must be in ARCHIVELOG mode
++ To create guaranteed restore point, no need to TRUN ON Flashback Database. I would highly recommend you to enable flashback on both primary and standby database, incase DB upgrade crashed,

++  What will happen to standby database if primary database opened with resetlogs ???

If Flashback database enabled on both primary and standby then no action required from DBA side.

If FLASHBACK is NOT enabled on standby database and standby applied redo data past the new resetlogs SCN then recreate the standby database.

http://www.br8dba.com/resetlogs-on-primary-where-standby-in-place/

++  Apply latest RU on 12.2.0.1 on both primary and standby (Recommended)

++ The Data Guard protection mode must be set to either maximum availability or maximum performance. The Data Guard protection mode cannot be set to maximum protection during the rolling upgrade.

++ The databases must not be part of a Data Guard Broker configuration. Data Guard Broker configurations are not supported during a rolling upgrade. If Data Guard Broker is being used, it will need to be disabled on both the primary and standby. Data Guard Broker can be re-enabled after completing the rolling upgrade.

++ To ensure the primary database can proceed while the logical standby database is being upgraded, the LOG_ARCHIVE_DEST_n initialization parameter for the logical standby database destination must be set to OPTIONAL (not MANDATORY).

++ The COMPATIBLE initialization parameter must match the software release prior to the upgrade. That is, a rolling upgrade from database release X to database release Y requires that the COMPATIBLE initialization parameter be set to database release X on both the primary and standby databases throughout the rolling upgrade process. The COMPATIBLE parameter will be set to the new Oracle version after completing the rolling upgrade when both databases have been upgraded and you are satisfied with the new Oracle version.

++ Oracle recommends that you add a primary key or a non-null unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.


1. Environment

On Primary

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: HKP
DB VERSION		: 12.2.0.1
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/HKP

Target DB VERSION 	: 19c
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

On STANDBY

Hostname       		: RAC2.RAJASEKHAR.COM
STANDBY Database Name  	: HKP_DG
DB VERSION		: 12.2.0.1
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/HKP_DG

Target DB VERSION 	: 19c
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

Upgrade Method		: Rolling Upgrade using Transient Logical Standby
                          Database Upgrade Assistant

Database Protection mode: Maximum Performance


PRE-UPGRADE TASKS


2. Disable DG Broker

On Primary

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sun Feb 9 16:58:00 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@HKP
Password:
Connected to "HKP"
Connected as SYSDBA.
DGMGRL> 
DGMGRL>  show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 35 seconds ago)

DGMGRL>
DGMGRL> DISABLE CONFIGURATION; <----
Disabled.
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED <----

DGMGRL>

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
SQL>
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>

On STANDBY

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       MOUNTED              PHYSICAL STANDBY

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <---
SQL>


3. Install 19c database software on primary

Below is sample doc.
http://www.br8dba.com/install-19c/


4. Install 19c database software on standby

Below is sample doc. Please modify according to your requirement
http://www.br8dba.com/install-19c/


5. Apply latest Release Update on 19c standby

Below is sample doc. Please modify according to your requirement
http://www.br8dba.com/apply-ru-on-database-19c/


6. Apply latest Release Update on 19c primary

Below is sample doc. Please modify according to your requirement
http://www.br8dba.com/apply-ru-on-database-19c/


7. Database Backup

RMAN > connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s';
release channel c1;
}
exit;

sample doc.
http://www.br8dba.com/backup-based-rman-duplicate-database/#3


8. Verify INVALID OBJECTS

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0 <-----

SQL>


9. Verify Protection mode

On Primary

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       READ WRITE           PRIMARY          MAXIMUM PERFORMANCE <---

SQL>

On STANDBY

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>


10. Verify fast_recovery_area size

On PRIMARY

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G <----
SQL>

[oracle@rac1 ~]$ df -h /u01/app/oracle/fast_recovery_area
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   36G   32G  54% /u01
[oracle@rac1 ~]$

On STANDBY

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G <----
SQL>

[oracle@rac2 ~]$ df -h /u01/app/oracle/fast_recovery_area
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        72G   59G   14G  82% /u01
[oracle@rac2 ~]$


11. Verify archive log dest size

On PRIMARY

[oracle@rac1 ~]$ df -h /u01/app/archive/HKP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   36G   32G  54% /u01 <----
[oracle@rac1 ~]$

On STANDBY

[oracle@rac2 ~]$ df -h /u01/app/archive/HKP_DG
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        72G   59G   14G  82% /u01 <----
[oracle@rac2 ~]$


12. Internal Schema Support

++ Below schemas are automatically skipped by SQL Apply. 

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY <----

SQL>
SQL> set pages 999
SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA' order by owner;

OWNER
--------------------------------------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
DVF
DVSYS
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
LBACSYS
MDSYS
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
WMSYS
XDB
XS$NULL

35 rows selected.

SQL> 


13. Find list of objects are not supported

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

no rows selected

SQL>


14. Check the reason for unsupported objects

select column_name,data_type from dba_logstdby_unsupported where owner='&OWNER' AND TABLE_NAME='&TABLE_NAME';


15. Find list of objects are not supported

SQL> COL OWNER FOR A10
SQL> COL TABLE_NAME FOR A10
SQL> SET LINES 190
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

OWNER      TABLE_NAME B
---------- ---------- -
SUGI       TEJA       N

SQL>

SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name;

OWNER      TABLE_NAME
---------- ----------
SUGI       TEJA

SQL>

Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key

SQL> ALTER TABLE SUGI.TEJA ADD CONSTRAINT PK_NAME PRIMARY KEY (NAME);

Table altered.

SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name;

no rows selected  <----

SQL>


16. Refresh MVs

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL>

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL>


17. Gather DICTIONARY STATS

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL>

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


18. Create Flashback Guaranteed Restore Point (On Primary)

++ The user must have the SYSDBA system privileges
++ Must have created a flash recovery area
++ The database must be in ARCHIVELOG mode
++ Guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available when you have enough space in the flash recovery area.
++ NO need to enable Flashback Database from 11.2.0.1 on wards
++ MUST NOT change the compatible parameter to higher version

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY <---

SQL> select * from V$restore_point;

no rows selected <---

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL>


19. Enable Flashback Database

On PRIMARY

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL>

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES <----

SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>

On STANDBY

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       MOUNTED              PHYSICAL STANDBY <-----

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES <----

SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>


20. Verify GAP

On PRIMARY

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL>
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                     23                    23          0
         1                     23                    23          0

SQL>

On STANDBY

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL>
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                     23                    23          0 <---

SQL>


CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY


21. Cancel MRP (Standby)

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL>

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG         27          1

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

no rows selected <----

SQL>


22. Build the logminer dictionary (Primary)

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL> set serveroutput on
SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed. <----

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

no rows selected

SQL>


23. Convert Physical to Logical Standby

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.

SQL>

Filename: alert_HKP_DG.log

2020-02-09T19:50:37.383801+08:00
ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
2020-02-09T19:50:37.384464+08:00
Media Recovery Start: Managed Standby Recovery (HKP_DG)
2020-02-09T19:50:37.386900+08:00
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
2020-02-09T19:50:37.582360+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_27_1031841246.dbf
2020-02-09T19:50:37.841765+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_28_1031841246.dbf
Resize operation completed for file# 1, old size 829440K, new size 839680K
2020-02-09T19:50:38.661868+08:00
Resize operation completed for file# 1, old size 839680K, new size 849920K
2020-02-09T19:50:39.187191+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_29_1031841246.dbf
2020-02-09T19:50:39.298950+08:00
Incomplete Recovery applied until change 1766613 time 02/09/2020 19:47:32
2020-02-09T19:50:39.327325+08:00
Media Recovery Complete (HKP_DG)
Killing 3 processes (PIDS:20446,19128,7053) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 18995
2020-02-09T19:50:41.382297+08:00
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1766613 time 02/09/2020 19:47:32
Resetting resetlogs activation ID 3259734620 (0xc24b9a5c)
Online log /u01/app/oracle/oradata/HKP_DG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/HKP_DG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/HKP_DG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1766611
2020-02-09T19:50:41.551507+08:00
Setting recovery target incarnation to 3
2020-02-09T19:50:41.668431+08:00
Network throttle feature is disabled as mount time

RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY


24. Verify DATABASE_ROLE

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         LOGICAL STANDBY <-----

SQL>


25. Open Logical Database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL>


26. Start SQL Apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

SQL> !ps -ef | grep lsp
oracle   28600     1  1 19:55 ?        00:00:00 ora_lsp0_HKP_DG
oracle   28710 18994  0 19:56 pts/1    00:00:00 /bin/bash -c ps -ef | grep lsp
oracle   28712 28710  0 19:56 pts/1    00:00:00 grep lsp

SQL> 

Filename: alert_HKP_DG.log

2020-02-09T19:55:39.530980+08:00
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2020-02-09T19:55:39.740957+08:00
LOGSTDBY: Creating new session for dbid 3259744860 starting at scn 0x0000000000000000
2020-02-09T19:55:39.750358+08:00
LOGSTDBY: Created session of id 1
2020-02-09T19:55:39.841666+08:00
LOGSTDBY: Attempting to pre-register dictionary build logfiles
2020-02-09T19:55:39.876273+08:00
LOGMINER: session# 1 Error 308 encountered, failed to read logfile 1_28_1031841246.dbf
LOGMINER: Encountered error 1291 while adding logfile 1_28_1031841246.dbf to session 1
LOGSTDBY: Unable to register recovery logfiles, will resend
2020-02-09T19:55:39.902719+08:00
ALTER DATABASE START LOGICAL STANDBY APPLY (HKP_DG)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Starting background process LSP0
2020-02-09T19:55:39.929700+08:00
LSP0 started with pid=77, OS id=28600
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2020-02-09T19:55:40.943107+08:00
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 100M, Checkpoint interval = 500M
LOGMINER: SpillScn 0, ResetLogScn 0
2020-02-09T19:56:30.449522+08:00
RFS[10]: Assigned to RFS process (PID:28747)
RFS[10]: Opened log for T-1.S-30 dbid 3259744860 branch 1031841246
2020-02-09T19:56:30.457282+08:00
RFS[11]: Assigned to RFS process (PID:28749)
RFS[11]: Opened log for T-1.S-29 dbid 3259744860 branch 1031841246
2020-02-09T19:56:30.458727+08:00
RFS[12]: Assigned to RFS process (PID:28751)
RFS[12]: Opened log for T-1.S-28 dbid 3259744860 branch 1031841246
2020-02-09T19:56:30.631848+08:00
RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_29_1031841246.dbf] to LogMiner session id [1]
2020-02-09T19:56:30.635771+08:00
RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_30_1031841246.dbf] to LogMiner session id [1]
2020-02-09T19:56:30.757026+08:00
RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_28_1031841246.dbf] to LogMiner session id [1]
2020-02-09T19:56:30.790912+08:00
RFS LogMiner: RFS id [28178] assigned as thread [1] PING handler
2020-02-09T19:56:31.210747+08:00
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 1766211 (0x00000000001af343)
LOGMINER: EndScn: 0 (0x0000000000000000)
LOGMINER: HighConsumedScn: 1766612 (0x00000000001af4d4)
LOGMINER: PSR flags: 0x1
LOGMINER: Session Flags: 0xba110dc
LOGMINER: Session Flags2: 0x4000
LOGMINER: Read buffers: 16
LOGMINER: Memory LWM: limit 10M, LWM 90M, 90%
LOGMINER: Memory Release Limit: 1M
LOGMINER: Max Decomp Region Memory: 1M
2020-02-09T19:56:31.303271+08:00
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=86 OS id=28754 sid=123 started
2020-02-09T19:56:31.329323+08:00
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=87 OS id=28756 sid=112 started
2020-02-09T19:56:31.352188+08:00
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=88 OS id=28758 sid=127 started
2020-02-09T19:56:32.378089+08:00
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf
2020-02-09T19:56:34.713730+08:00
LOGMINER: Preparing to load 1 dictionaries for session 1
2020-02-09T19:56:38.021593+08:00
Resize operation completed for file# 3, old size 522240K, new size 532480K
2020-02-09T19:56:38.232296+08:00
Resize operation completed for file# 3, old size 532480K, new size 552960K
2020-02-09T19:56:42.053024+08:00
LOGMINER: Finalizing dictionary load for session 1
2020-02-09T19:56:43.677904+08:00
LOGMINER: Gathering statistics on logminer dictionary. (incremental, nonparallel)
2020-02-09T19:56:55.507688+08:00
LOGMINER: Completed dictionary load for session 1
2020-02-09T19:56:58.556285+08:00
LOGMINER: End mining logfiles during dictionary load for session 1
Starting background process LSP2
2020-02-09T19:56:59.235469+08:00
LSP2 started with pid=90, OS id=28852
2020-02-09T19:57:02.786146+08:00
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf
2020-02-09T19:57:03.094739+08:00
LOGMINER: End   mining logfile during commit scan for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf
2020-02-09T19:57:03.098445+08:00
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf
2020-02-09T19:57:04.730566+08:00
LOGMINER: End mining logfiles during commit scan for session 1
2020-02-09T19:57:04.746535+08:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf
2020-02-09T19:57:05.348148+08:00
LOGSTDBY Apply process AS04 started with server id=4 pid=93 OS id=28881
2020-02-09T19:57:05.349597+08:00
LOGSTDBY Apply process AS03 started with server id=3 pid=92 OS id=28879
2020-02-09T19:57:05.357542+08:00
LOGSTDBY Apply process AS05 started with server id=5 pid=94 OS id=28883
2020-02-09T19:57:05.369323+08:00
LOGSTDBY Analyzer process AS00 started with server id=0 pid=89 OS id=28873
2020-02-09T19:57:05.381952+08:00
LOGSTDBY Apply process AS02 started with server id=2 pid=91 OS id=28877
2020-02-09T19:57:05.393689+08:00
LOGSTDBY Apply process AS01 started with server id=1 pid=90 OS id=28875
2020-02-09T19:57:05.917293+08:00
LOGMINER: End   mining logfile for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf
2020-02-09T19:57:05.919218+08:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf
2020-02-09T19:57:05.919362+08:00
LOGMINER: End   mining logfile for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf
2020-02-09T19:57:05.923045+08:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 30, /u01/app/archive/HKP_DG/1_30_1031841246.dbf
2020-02-09T19:57:05.923418+08:00
LOGMINER: End   mining logfile for session 1 thread 1 sequence 30, /u01/app/archive/HKP_DG/1_30_1031841246.dbf
2020-02-09T19:57:05.927229+08:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 31, /u01/app/oracle/oradata/HKP_DG/redo04.log
2020-02-09T19:57:07.956796+08:00

XDB installed.
2020-02-09T19:57:12.644145+08:00

XDB initialized.
2020-02-09T19:58:32.953910+08:00
Resize operation completed for file# 3, old size 552960K, new size 573440K


27. Verify GAP

On Primary

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL>
SQL> CONN SUGI/SUGI;
Connected.
SQL> INSERT INTO TEJA VALUES ('X','DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEJA;

NAME                 ROLE
-------------------- ----------
RAJ                  DBA
X                    DBA <-----

SQL> 

On Logical STANDBY

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME       OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
---------- -------------------- ------------------------------ ----------------
HKP        READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL>


SQL> SELECT * FROM SUGI.TEJA;

NAME                                               ROLE
-------------------------------------------------- ----------
RAJ                                                DBA
X                                                  DBA <-----

SQL>

SQL> conn SUGI/SUGI;
Connected.
SQL> INSERT INTO TEJA VALUES ('Y','DBA');
INSERT INTO TEJA VALUES ('Y','DBA')
            *
ERROR at line 1:
ORA-16224: Database Guard is enabled

SQL> CONN / AS SYSDBA
Connected.
SQL> select guard_status from v$database;

GUARD_S
-------
ALL <-----

SQL>

The guard_status column protects the data from being changed.  There are three values:

ALL - All users other than SYS are prevented from making changes to any data in the database.

STANDBY - All users other than SYS are prevented from making changes to any database object being maintained by logical standby.

NONE - Indicates normal security for all data in the database.


UPGRADE LOGICAL STANDBY


28. Run pre-upgrade script

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [HKP_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$
[oracle@rac2 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /home/oracle/preupgrade/preupgrade.log <----
  /home/oracle/preupgrade/preupgrade_fixups.sql
  /home/oracle/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/home/oracle/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/home/oracle/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-02-09T20:20:44
[oracle@rac2 ~]$


29. View Pre-upgrade log

[oracle@rac2 ~]$ cat /home/oracle/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-02-09T20:20:44

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  HKP
     Container Name:  HKP
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  INFORMATION ONLY
  ================
  1.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             560 MB       576 MB
      SYSTEM                             830 MB       943 MB
      TEMP                                20 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  2.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
      4618 MB of archived logs.  Check alert log during the upgrade that there
      is no write error to the destination due to lack of disk space.

      Archiving cannot proceed if the archive log destination is full during
      upgrade.

      Archive Log Destination:
       Parameter    :  LOG_ARCHIVE_DEST_1
       Destination  :  /u01/app/archive/HKP_DG

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

  3.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  6.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database HKP
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/home/oracle/preupgrade/postupgrade_fixups.sql


[oracle@rac2 ~]$


30. Verify tablespace sizes for upgrade

** Tablespace Auto extend ON and max size also set, hence no action taken.

http://www.br8dba.com/asm-2/#tspace

TABLESPACE_NAME                AUT FILE_NAME                                       TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
------------------------------ --- ----------------------------------------------- ----------- ---------- ---------- ----------
SYSAUX                         YES /u01/app/oracle/oradata/HKP_DG/sysaux01.dbf             560         32       5.68 31.9999847
SYSTEM                         YES /u01/app/oracle/oradata/HKP_DG/system01.dbf             830          3        .38 31.9999847
UNDOTBS1                       YES /u01/app/oracle/oradata/HKP_DG/undotbs01.dbf             70         37      53.04 31.9999847
USERS                          YES /u01/app/oracle/oradata/HKP_DG/users01.dbf                5          4       77.5 31.9999847
******************************                                                     ----------- ----------            ----------
sum                                                                                       1465         76            127.999939

SQL>


31. Run preupgrade_fixups.sql

++ It will run by DBUA, however we are executing manually before start upgrade.

SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle PRE-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 19.0.0.0.0 Build: 1
SQL> REM    Generated on:            2020-02-09 20:20:41
SQL> REM
SQL> REM    Source Database:         HKP
SQL> REM    Source Database Version: 12.2.0.1.0
SQL> REM    For Upgrade to Version:     19.0.0.0.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-02-09 20:20:41

For Source Database:     HKP
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    2.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
    3.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

SQL>


32. Create GUARANTEED Restore Point (Logical STANDBY)

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

SQL> CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created. <-----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
BEFORE_UPGRADE       YES        09-FEB-20 08.49.22.000000000 PM

SQL>


33. Run DBUA from 19c ORACLE HOME

[oracle@rac2 ~]$ export DISPLAY=192.168.2.2:0.0
[oracle@rac2 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbua























Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-02-09_09-04-14PM
Database upgrade has been completed successfully, and the database is ready to use.


POST-UPGRADE TASKS WHEN DBUA USING


34. Verify /etc/oratab

[oracle@rac2 ~]$ cat /etc/oratab | grep -i "HKP_DG"
HKP_DG:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac2 ~]$


35. Verify Timezone version

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0 <----

SQL>

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32 <----

SQL>


36. Verify INVALID objects

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
         0 <-----

SQL>


37. Verify DBA_REGISTRY (HKP_DG)

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0

SQL>
SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL>


38. Start SQL Apply

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0

SQL>
[oracle@rac2 ~]$ ps -ef | grep lsp
oracle    6953 10539  0 22:59 pts/1    00:00:00 grep --color=auto lsp
[oracle@rac2 ~]$

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

SQL>
SQL> !ps -ef | grep lsp
oracle    7246     1  3 23:01 ?        00:00:03 ora_lsp0_HKP_DG
oracle    7568  6479  0 23:03 pts/0    00:00:00 /bin/bash -c ps -ef | grep lsp
oracle    7570  7568  0 23:03 pts/0    00:00:00 grep lsp

SQL>

Filename: alert_HKP_DG.log

2020-02-09T23:01:27.014215+08:00
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
2020-02-09T23:01:27.196901+08:00
ALTER DATABASE START LOGICAL STANDBY APPLY (HKP_DG)
2020-02-09T23:01:27.196974+08:00
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Starting background process LSP0
2020-02-09T23:01:27.227080+08:00
LSP0 started with pid=87, OS id=7246
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE


39. Verify GAP

Test case:

On PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0 <-----

SQL>

SQL> CONN SUGI/SUGI;
Connected.
SQL> SELECT * FROM TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA

SQL> INSERT INTO TEJA VALUES ('SUGI','DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <------

SQL>

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            31 <-----

SQL>

On STANDBY

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- ---------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0 

SQL>

SQL> SELECT * FROM SUGI.TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <------

SQL>

SQL> col REALTIME_APPLY for a20
SQL> select * from v$logstdby_state;

PRIMARY_DBID PRIMARY_CON_DBID SESSION_ID REALTIME_APPLY       STATE          CON_ID
------------ ---------------- ---------- -------------------- ---------- ----------
  3259744860                0          1 Y                    IDLE                0

SQL>

SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED      BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
        28       1766211      1766603 09-FEB-20 YES           35440        512
        29       1766603      1766619 09-FEB-20 YES              28        512
        30       1766619      1767498 09-FEB-20 YES             483        512
        31       1767498      1787013 09-FEB-20 YES          123270        512

SQL>


CONVERT PRIMARY(HKP) TO LOGICAL STANDBY


40. Pre-Switchover tasks

*** Verify that there is network connectivity between the primary and standby locations.
*** Always recommened test the switchover in your testing system befre working on production.
*** Verify primary database READ WRITE and standby instance mounted.
***  Verify there are no active users connected to database.
*** Make sure last redo data transmitted from primary to standby and applied.
*** Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
alter system set job_queue_processes=0 ;
alter system set aq_tm_processes = 0 ;
alter system set dbwr_io_slaves = 0 ;

On Primary

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL> select * from dba_jobs_running;

no rows selected

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL>

Note: Block further job submission by setting the job_queue_processes parameter to 0 so that 
there would be no jobs running during switchover.

On Standby

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- ---------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0

SQL> 
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED      BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
        28       1766211      1766603 09-FEB-20 YES           35440        512
        29       1766603      1766619 09-FEB-20 YES              28        512
        30       1766619      1767498 09-FEB-20 YES             483        512
        31       1767498      1787013 09-FEB-20 YES          123270        512


SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL>
SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;

SYSDATE            APPLIED_TIME
------------------ ------------------
09-FEB-20 23:25:40 09-FEB-20 23:25:35

SQL>


41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY <----

SQL>

Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            LOGICAL STANDBY  12.2.0.1.0 <----

SQL>


42. Verify alert_HKP.log

Filename: alert_HKP.log

2020-02-09T23:26:59.463554+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY
2020-02-09T23:26:59.463682+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (HKP)
2020-02-09T23:26:59.466379+08:00
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Archiving current online log files.
2020-02-09T23:26:59.588667+08:00
Thread 1 advanced to log sequence 33 (LGWR switch)
  Current log# 3 seq# 33 mem# 0: /u01/app/oracle/oradata/HKP/redo03.log
2020-02-09T23:27:00.228257+08:00
Archived Log entry 65 added for T-1.S-32 ID 0xc24b9a5c LAD:1
2020-02-09T23:27:00.277230+08:00
LOGSTDBY: Waiting for pending archivals to all destinations.
2020-02-09T23:27:00.320999+08:00
Waiting for all non-current ORLs to be archived
2020-02-09T23:27:00.321160+08:00
All non-current ORLs have been archived
2020-02-09T23:27:00.321286+08:00
Waiting for all FAL entries to be archived
2020-02-09T23:27:00.326657+08:00
All FAL entries have been archived
2020-02-09T23:27:00.326862+08:00
Waiting for potential Logical Standby switchover target to become synchronized
2020-02-09T23:27:01.287138+08:00
TT02: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
2020-02-09T23:27:02.328956+08:00
Active, synchronized Logical Standby switchover target has been identified
2020-02-09T23:27:02.329025+08:00
LOGSTDBY: Enabling database guard.
LOGSTDBY: Waiting for completion of transactions started at or before scn 1816838 (0x00000000001bb906)
LOGSTDBY: All transactions started at or before scn 1816838 (0x00000000001bb906) have completed
2020-02-09T23:27:02.462169+08:00
LOGSTDBY: Database guard enabled.  User transactions are no longer permitted.
2020-02-09T23:27:02.462242+08:00
LOGSTDBY: Waiting for pending archivals to all destinations.
2020-02-09T23:27:02.498949+08:00
Waiting for all non-current ORLs to be archived
2020-02-09T23:27:02.499038+08:00
All non-current ORLs have been archived
2020-02-09T23:27:02.503040+08:00
Waiting for all FAL entries to be archived
2020-02-09T23:27:02.503187+08:00
All FAL entries have been archived
2020-02-09T23:27:02.503283+08:00
Waiting for potential Logical Standby switchover target to become synchronized
2020-02-09T23:27:03.505105+08:00
Active, synchronized Logical Standby switchover target has been identified
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn 1816841 [0x00000000001bb909].
2020-02-09T23:27:03.995731+08:00
Thread 1 advanced to log sequence 34 (LGWR switch)
  Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log
2020-02-09T23:27:04.999145+08:00
ARCH: LGWR is scheduled to archive to LAD:2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
2020-02-09T23:27:05.350386+08:00
Archived Log entry 66 added for T-1.S-33 ID 0xc24b9a5c LAD:1
LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover target
2020-02-09T23:27:05.638889+08:00
Thread 1 cannot allocate new log, sequence 35
Checkpoint not complete
  Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log
2020-02-09T23:27:06.299087+08:00
Thread 1 advanced to log sequence 35 (LGWR switch)
  Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/HKP/redo02.log
2020-02-09T23:27:06.308965+08:00
Archived Log entry 68 added for T-1.S-34 ID 0xc24b9a5c LAD:1
2020-02-09T23:27:06.329142+08:00
LOGSTDBY: Switchover complete (HKP)
LOGSTDBY: enabling scheduler job queue processes.
2020-02-09T23:27:06.329295+08:00
JOBQ: re-enabling CJQ0
Starting background process CJQ0
2020-02-09T23:27:06.346474+08:00
CJQ0 started with pid=70, OS id=1067
2020-02-09T23:27:06.449711+08:00
ARCt: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY


SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY


43. Switchover upgraded logical standby to primary

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION
--------- -------------------- -------------------------------- ---------------
HKP       READ WRITE           HKP_DG          LOGICAL STANDBY  19.0.0.0.0

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY <-----

SQL>

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME   DATABASE_ROLE    VERSION         HOST_NAME
--------- -------------------- ---------------- ---------------- --------------- --------------------
HKP       READ WRITE           HKP_DG           PRIMARY          19.0.0.0.0      rac2.rajasekhar.com <---

SQL>


44. Verify alert_HKP_DG.log

Filename: alert_HKP_DG.log

ALTER DATABASE SWITCHOVER TO PRIMARY (HKP_DG)
2020-02-09T23:36:45.455212+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (HKP_DG)
2020-02-09T23:36:45.455516+08:00
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Preparing to Create Detached Dictionary Build, pre-lockdown scn is [0x00000000002bbf0c]
Sun Feb 09 23:36:45 2020
Logminer Bld: Build started
2020-02-09T23:36:45.589273+08:00
ALTER SYSTEM SWITCH ALL LOGFILE start (HKP_DG)
2020-02-09T23:36:45.636455+08:00
Thread 1 advanced to log sequence 36 (LGWR switch)
  Current log# 3 seq# 36 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo03.log
2020-02-09T23:36:45.641040+08:00
ALTER SYSTEM SWITCH ALL LOGFILE complete (HKP_DG)
2020-02-09T23:36:45.701288+08:00
Sun Feb 09 23:36:45 2020
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 2866962 LockdownSCN is 2866962
LOGSTDBY: Starting SCN of new stream from recent lockdown [0x00000000002bbf12]
2020-02-09T23:36:45.701730+08:00
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [2866965].
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY:   primary:       [3259744860]
LOGSTDBY:   first scn:     [0x0000000000000000]
LOGSTDBY:   end scn:       [0x00000000001bb912]
LOGSTDBY:   processed scn: [0x00000000001bb913]
2020-02-09T23:36:46.270609+08:00
LOGSTDBY: terminating active RFS connections for role change
LOGSTDBY: terminated RFS process [12084]
2020-02-09T23:36:46.271142+08:00
Process termination requested for pid 12084 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
2020-02-09T23:36:46.271465+08:00
Process termination requested for pid 12100 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
LOGSTDBY: terminated RFS process [12100]
2020-02-09T23:36:46.271856+08:00
LOGSTDBY: terminated RFS process [12102]
2020-02-09T23:36:46.272637+08:00
Process termination requested for pid 12102 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
2020-02-09T23:36:46.273165+08:00
ARC4 (PID:30701): Archived Log entry 62 added for T-1.S-35 ID 0xc24e1bb5 LAD:1
2020-02-09T23:36:46.276222+08:00
LOGSTDBY: (dglcccsp) Archiving standby redo logfiles.
LOGSTDBY: (dglcccsp) Not using surrogate archiving mode
LOGSTDBY: (dglcccsp) Found [1] standby redo logfiles to archive
2020-02-09T23:37:11.785235+08:00
LOGSTDBY: (dglcccsp) Complete. [1] standby redo logfiles were archived.
2020-02-09T23:37:11.846193+08:00
NET  (PID:6480): Database role cleared from LOGICAL STANDBY [dglc.c:1953]
Starting background process LSP1
2020-02-09T23:37:11.873051+08:00
LSP1 started with pid=73, OS id=14043
2020-02-09T23:37:11.875570+08:00
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created
2020-02-09T23:37:11.882847+08:00
LOGSTDBY: Disabling database guard.
2020-02-09T23:37:11.929457+08:00
TT03 (PID:14045): Switchover in progress, stop clearing SRLs
2020-02-09T23:37:11.933903+08:00
LOGSTDBY: (LSP1) Archiving online logs as a primary database
2020-02-09T23:37:12.077504+08:00
Thread 1 advanced to log sequence 37 (LGWR switch)
  Current log# 1 seq# 37 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo01.log
2020-02-09T23:37:12.095439+08:00
LOGSTDBY: Database guard disabled.  User transactions are now permitted.
2020-02-09T23:37:12.108372+08:00
LSP1 (PID:14043): LGWR is scheduled to archive to LAD:2 after log switch
2020-02-09T23:37:12.110050+08:00
LOGSTDBY: enabling scheduler job queue processes.
2020-02-09T23:37:12.110136+08:00
JOBQ: re-enabling CJQ0
2020-02-09T23:37:12.130456+08:00
LSP1 (PID:14043): Error 12154 received logging on to the standby
2020-02-09T23:37:12.130655+08:00
Errors in file /u01/app/oracle/diag/rdbms/hkp_dg/HKP_DG/trace/HKP_DG_lsp1_14043.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
LSP1 (PID:14043): Error 12154 Creating archive log file to 'HKP'
2020-02-09T23:37:12.181126+08:00
LSP1 (PID:14043): Archived Log entry 63 added for T-1.S-36 ID 0xc24e1bb5 LAD:1
LOGSTDBY: (LSP1) Starting Full LogMiner Dictionary Build
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY


CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY


45. Flashback database to restore point

++ Before we can convert to physical standby, we need to flashback database to guaranteed restore point

SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            LOGICAL STANDBY  12.2.0.1.0 <----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;
FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1560281088 bytes
Fixed Size                  8621088 bytes
Variable Size            1325401056 bytes
Database Buffers          218103808 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL>
SQL>  select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
-------------------- -------------------- ------------------------------ ---------------- -----------------
HKP                  MOUNTED              HKP                            LOGICAL STANDBY  12.2.0.1.0

SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;

Flashback complete. <-----

SQL> SHUT IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>


46. Add /etc/oratab entry – 19c

[oracle@rac1 ~]$ cat /etc/oratab | grep -i "HKP"
#HKP:/u01/app/oracle/product/12.2.0/dbhome_1:N
HKP:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$


47. Copy password file / initialization file to 19c home/dbs

[oracle@rac1 dbs]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *HKP*
-rw-r-----. 1 oracle oinstall       24 Feb  8 14:32 lkHKP
-rw-r-----. 1 oracle oinstall     3584 Feb  8 14:35 orapwHKP
-rw-r-----. 1 oracle oinstall    12288 Feb  9 17:17 dr2HKP.dat
-rw-r-----. 1 oracle oinstall    12288 Feb  9 17:22 dr1HKP.dat
-rw-r-----. 1 oracle oinstall 10829824 Feb  9 19:24 snapcf_HKP.f
-rw-r-----. 1 oracle oinstall     5632 Feb 10 00:04 spfileHKP.ora
-rw-rw----. 1 oracle oinstall     1544 Feb 10 00:08 hc_HKP.dat
[oracle@rac1 dbs]$ cp -p spfileHKP.ora orapwHKP /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*HKP*
-rw-r-----. 1 oracle oinstall 3584 Feb  8 14:35 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwHKP
-rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileHKP.ora
[oracle@rac1 dbs]$


48. Configure TNS Entries

OLD PRIMARY (HKP)

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle    6768     1  0 Feb09 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit
oracle   12048  6265  0 00:21 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl stop LISTENER_HKP

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-FEB-2020 00:21:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622)))
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   12161  6265  0 00:22 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora

LISTENER_HKP =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))

HKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP)
    )
  )

HKP_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP_DG)
    )
  )


cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

LISTENER_HKP =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))
    )
  )

SID_LIST_LISTENER_HKP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = HKP)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = HKP)
    )
  )
  
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [HKP_DG] ? HKP
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ lsnrctl start LISTENER_HKP

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 00:28:34

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_HKP
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                10-FEB-2020 00:28:34
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622)))
Services Summary...
Service "HKP" has 1 instance(s).
  Instance "HKP", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   13439     1  0 00:28 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit
oracle   13498  6265  0 00:28 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

NEW PRIMARY (HKP_DG)

++ We have upgraded DB using DBUA, so all TNS entries, password file, spfile will copied automatically to 19c diretories.
++ LISTENER will start by DBUA automatically

[oracle@rac2 admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@rac2 admin]$
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HKP_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP_DG)
    )
  )

LISTENER_HKP_DG =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))

HKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP)
    )
  )

[oracle@rac2 admin]$ 

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_HKP_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1622))
    )
  )

[oracle@rac2 admin]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   24573 10539  0 00:32 pts/1    00:00:00 grep --color=auto tns
oracle   27865     1  0 Feb09 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP_DG -inherit
[oracle@rac2 admin]$


49. Mount Database (OLD_PRIMARY) using 19c Oracle software

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [HKP] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ which sqlplus
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 00:38:28 2020
Version 19.4.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1207959552 bytes
Database Buffers          335544320 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL>


50. Verify TNS Connectivity

ON OLD PRIMARY

[oracle@rac1 ~]$ tnsping HKP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:16:54

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP)))
OK (10 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping HKP_DG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:17:01

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG)))
OK (10 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus sys@HKP as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:17 2020
Version 19.4.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus sys@HKP_DG as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:34 2020
Version 19.4.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac1 ~]$

ON NEW PRIMARY

[oracle@rac2 ~]$ tnsping HKP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:29

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping HKP_DG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:34

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG)))
OK (10 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys@HKP as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:18:45 2020
Version 19.4.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys@HKP_DG as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:19:10 2020
Version 19.4.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL>


51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;
                                               
NAME      OPEN_MODE  DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- ---------- --------------- ---------------- ----------------- ------------
HKP       MOUNTED    HKP             LOGICAL STANDBY  19.0.0.0.0        rac1 <--
                                     
SQL> 

SQL> alter database convert to physical standby;

Database altered.

SQL>
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- -----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1207959552 bytes
Database Buffers          335544320 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL>


52. Enable MRP

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

no rows selected

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

Database altered.

SQL>
SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG         40          1 <----

SQL>


53. Verify GAP

ON NEW PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com <----

SQL>

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                     39                    39          0
         1                     39                    39          0

SQL>

ON OLD PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1 <----

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                     39                    39          0 <----

SQL>


54. Disable Flashback Database

On OLD PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

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>

ON NEW PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>
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>


55. Drop Restore point

On OLD PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY <----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL>
SQL> DROP RESTORE POINT PRE_UPGRADE;

Restore point dropped.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO <----

SQL>

On NEW PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY <----

SQL>
SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
BEFORE_UPGRADE       YES        09-FEB-20 08.49.22.000000000 PM

SQL> DROP RESTORE POINT BEFORE_UPGRADE;

Restore point dropped.

SQL> 
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected <---

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO <-----

SQL>


56. Update COMPATIBLE parameter on both primary/standby

Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.

On NEW Primary

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

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

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1325400064 bytes
Database Buffers          218103808 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL>

ON OLD PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <----
noncdb_compatible                    boolean     FALSE
SQL>

SQL> SHUT IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1224736768 bytes
Database Buffers          318767104 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

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

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL>
SQL> SHOW PARAMETER COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>


57. Verify GAP

On New Primary

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                     44                    44          0
         1                     44                    44          0

SQL>

On Standby (Old Primary)

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                     44                    44          0 <----

SQL>


58. Revert back parameter values on both primary/standby (In case if you changed)

job_queue_processes
aq_tm_processes
dbwr_io_slaves

Congratulations !!! Rolling upgrade completed successfully from 12.2.0.1 to 19c.

Below Steps are Optional. Put the Primary back in place.


59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY <----

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shut immediate;
ORA-01012: not logged on
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 02:06:57 2020
Version 19.4.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1325400064 bytes
Database Buffers          218103808 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> /
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL>
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- --------------------
HKP       MOUNTED              HKP_DG          PHYSICAL STANDBY 19.0.0.0.0        rac2.rajasekhar.com <----

SQL>


60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY <----

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1 <-----
                                           
SQL>


61. Verify DBA_REGISTRY ON PRIMARY (HKP)

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1 <--
                                           
SQL>
SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL>


62. Configure DG Broker

On PRIMARY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <---
SQL>
SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE <----
SQL>

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1HKP.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2HKP.dat
SQL>

On STANDBY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <----
SQL>
SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE <----
SQL>

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1HKP_DG.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2HKP_DG.dat
SQL>

On PRIMARY

[oracle@rac1 ~]$ which dgmgrl
/u01/app/oracle/product/19.0.0/dbhome_1/bin/dgmgrl
[oracle@rac1 ~]$
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Feb 10 02:27:59 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@HKP AS SYSDBA
Password:
Connected to "HKP"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL> create configuration 'HKP' as primary database is 'HKP' connect identifier is HKP;
Configuration "HKP" created with primary database "HKP"
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP - Primary database <----

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed. <-----
DGMGRL> 

On Standby

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------  ---------------- ----------------- ---------------------
HKP       MOUNTED              HKP_DG          PHYSICAL STANDBY 19.0.0.0.0        rac2.rajasekhar.com
                                              
SQL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; <----

System altered.

SQL>

On Primary

DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical;
Database "HKP_DG" added
DGMGRL>

DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database <----

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL>

DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago) <----

DGMGRL>


63. Verify GAP

DGMGRL> show configuration lag

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database
             Transport Lag:      0 seconds (computed 1 second ago)
             Apply Lag:          0 seconds (computed 1 second ago) <---

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 24 seconds ago)

DGMGRL> show database HKP_DG;

Database - HKP_DG

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago) <----
  Average Apply Rate: 9.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    HKP_DG

Database Status:
SUCCESS

DGMGRL>


64. Verify Table

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1

SQL> SELECT * FROM SUGI.TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <-----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/using-sql-apply-to-perform-rolling-upgrade.html#GUID-C5DF6148-C1E9-4ADF-A975-AC95FC64E0C4

http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_60.shtml

Leave a Reply

Your email address will not be published. Required fields are marked *