Learning Oracle: Rolling Database Upgrade from 12.2.0.1 to 19c Using Transient Logical Standby

Rolling Database Upgrade from 12.2.0.1 to 19c Using Transient Logical Standby

1. Environment

On Primary

Hostname : RAC1.ORACLE.COM
Database Name : QPROD
DB VERSION : 12.2.0.1
Target DB VERSION : 19c
Target DB Path : /u01/app/oracle/product/19.0.0/dbhome_1

On STANDBY

Hostname : RAC2.ORACLE.COM
STANDBY Database Name : QPROD_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/QPROD_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 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@QPROD

Password:

Connected to "QPROD"

Connected as SYSDBA.

DGMGRL> show configuration;

Configuration - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database

QPROD_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 - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database

QPROD_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

--------- -------------------- ----------------

QPROD 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

--------- -------------------- ----------------

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

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

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;

8. Verify INVALID OBJECTS

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

COUNT(*)

----------

0 <-----

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

--------- -------------------- ---------------- --------------------

QPROD READ WRITE PRIMARY MAXIMUM PERFORMANCE <----

On STANDBY

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

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE

--------- -------------------- ---------------- --------------------

QPROD MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE

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

--------- -------------------- ---------------- --------------------

QPROD 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/QPROD

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/QPROD_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

--------- -------------------- ----------------

QPROD 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

--------- -------------------- ----------------

QPROD 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

---------- ---------- -

RUMA SHOB N

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

---------- ----------

RUMA SHOB

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 RUMA.SHOB 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

--------- -------------------- ----------------

QPROD READ WRITE PRIMARY

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.

17. Gather DICTIONARY STATS

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

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

QPROD READ WRITE PRIMARY

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.

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

--------- -------------------- ----------------

QPROD 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

19. Enable Flashback Database

On PRIMARY

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

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

QPROD READ WRITE PRIMARY

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

--------- -------------------- ----------------

QPROD 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.

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

-------------------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD 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 23 23 0

1 23 23 0

On STANDBY

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

NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE

-------------------- -------------------- ------------------------------ ----------------

QPROD MOUNTED QPROD_DG PHYSICAL STANDBY

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

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

-------------------- -------------------- ------------------------------ ----------------

QPROD MOUNTED QPROD_DG PHYSICAL STANDBY

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

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

-------------------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD 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

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

-------------------- -------------------- ------------------------------ ----------------

QPROD MOUNTED QPROD_DG PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
Database altered.

Alertlog:

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 (QPROD_DG)

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

-------------------- -------------------- ------------------------------ ----------------

QPROD MOUNTED QPROD_DG LOGICAL STANDBY <-----

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

-------------------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD_DG LOGICAL STANDBY

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

Alertlog:

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

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

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

-------------------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD PRIMARY

SQL> CONN RUMA/RUMA;

Connected.

SQL> INSERT INTO SHOB VALUES ('X','DBA');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM SHOB;

NAME ROLE

-------------------- ----------

MQM DBA

X DBA <-----

On Logical STANDBY

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

NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE

---------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD_DG LOGICAL STANDBY

SQL> SELECT * FROM RUMA.SHOB;

NAME ROLE

-------------------------------------------------- ----------

MQM DBA

X DBA <-----

SQL> conn RUMA/RUMA;

Connected.

SQL> INSERT INTO SHOB VALUES ('Y','DBA');

INSERT INTO SHOB 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 <-----

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 = [QPROD_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

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: QPROD

Container Name: QPROD

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.

-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/QPROD_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.

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

which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by

executing the following

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

30. Verify tablespace sizes for upgrade

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

TABLESPACE_NAME AUT FILE_NAME TOTAL_SPACE FREE_SPACE Free% MAX_SPACE

------------------------------ --- ----------------------------------------------- ----------- ---------- ---------- ----------

SYSAUX YES /u01/app/oracle/oradata/QPROD_DG/sysaux01.dbf 560 32 5.68 31.9999847

SYSTEM YES /u01/app/oracle/oradata/QPROD_DG/system01.dbf 830 3 .38 31.9999847

UNDOTBS1 YES /u01/app/oracle/oradata/QPROD_DG/undotbs01.dbf 70 37 53.04 31.9999847

USERS YES /u01/app/oracle/oradata/QPROD_DG/users01.dbf 5 4 77.5 31.9999847

****************************** ----------- ---------- ----------

sum 1465 76 127.999939

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

--------- -------------------- ------------------------------ ----------------

QPROD READ WRITE QPROD_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: QPROD

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: QPROD

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.

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

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

34. Verify /etc/oratab

[oracle@rac2 ~]$ cat /etc/oratab | grep -i "QPROD_DG"

QPROD_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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD_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 (QPROD_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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD_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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD_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_QPROD_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>

Alertlog:

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 (QPROD_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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD PRIMARY 12.2.0.1.0 <-----

SQL>

SQL> CONN RUMA/RUMA;

Connected.

SQL> SELECT * FROM SHOB;

NAME ROLE

---------- ----------

MQM DBA

X DBA

SQL> INSERT INTO SHOB VALUES ('RUMA','DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM SHOB;

NAME ROLE

---------- ----------

MQM DBA

X DBA

RUMA 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

--------- -------------------- ------------------------------ ---------------- ---------------

QPROD READ WRITE QPROD_DG LOGICAL STANDBY 19.0.0.0.0

SQL>

SQL> SELECT * FROM RUMA.SHOB;

NAME ROLE

---------- ----------

MQM DBA

X DBA

RUMA 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(QPROD) 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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD 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

--------- -------------------- ------------------------------ ---------------- ---------------

QPROD READ WRITE QPROD_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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD 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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD LOGICAL STANDBY 12.2.0.1.0 <----

SQL>

Alertlog:

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 (QPROD)

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.

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY

SWITCHOVER UPGRADED LOGICAL STANDBY (QPROD_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

--------- -------------------- -------------------------------- ---------------

QPROD READ WRITE QPROD_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

--------- -------------------- ---------------- ---------------- --------------- --------------------

QPROD READ WRITE QPROD_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com <---

SQL>

Alertlog:

ALTER DATABASE SWITCHOVER TO PRIMARY (QPROD_DG)

2020-02-09T23:36:45.455212+08:00

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (QPROD_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

LSP1 (PID:14043): Error 12154 Creating archive log file to 'QPROD'

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

--------- -------------------- ------------------------------ ---------------- -----------------

QPROD READ WRITE QPROD 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

-------------------- -------------------- ------------------------------ ---------------- -----------------

QPROD MOUNTED QPROD 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 "QPROD"

#QPROD:/u01/app/oracle/product/12.2.0/dbhome_1:N

QPROD:/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 *QPROD*

-rw-r-----. 1 oracle oinstall 24 Feb 8 14:32 lkQPROD

-rw-r-----. 1 oracle oinstall 3584 Feb 8 14:35 orapwQPROD

-rw-r-----. 1 oracle oinstall 12288 Feb 9 17:17 dr2QPROD.dat

-rw-r-----. 1 oracle oinstall 12288 Feb 9 17:22 dr1QPROD.dat

-rw-r-----. 1 oracle oinstall 10829824 Feb 9 19:24 snapcf_QPROD.f

-rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 spfileQPROD.ora

-rw-rw----. 1 oracle oinstall 1544 Feb 10 00:08 hc_QPROD.dat

[oracle@rac1 dbs]$ cp -p spfileQPROD.ora orapwQPROD /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/*QPROD*

-rw-r-----. 1 oracle oinstall 3584 Feb 8 14:35 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwQPROD

-rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileQPROD.ora

[oracle@rac1 dbs]$

48. Configure TNS Entries

OLD PRIMARY (QPROD)

[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_QPROD -inherit

oracle 12048 6265 0 00:21 pts/0 00:00:00 grep --color=auto tns

[oracle@rac1 ~]$

[oracle@rac1 ~]$ lsnrctl stop LISTENER_QPROD

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_QPROD =

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

QPROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = QPROD)

)

)

QPROD_DG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = QPROD_DG)

)

)

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

LISTENER_QPROD =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

SID_LIST_LISTENER_QPROD =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = QPROD)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = QPROD)

)

)

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [QPROD_DG] ? QPROD

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1 ~]$ lsnrctl start LISTENER_QPROD

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_QPROD

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 "QPROD" has 1 instance(s).

Instance "QPROD", 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_QPROD -inherit

oracle 13498 6265 0 00:28 pts/0 00:00:00 grep --color=auto tns

[oracle@rac1 ~]$

NEW PRIMARY (QPROD_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.

QPROD_DG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = QPROD_DG)

)

)

LISTENER_QPROD_DG =

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

QPROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = QPROD)

)

)

[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_QPROD_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_QPROD_DG -inherit

[oracle@rac2 admin]$

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

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [QPROD] ?

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 QPROD

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 = QPROD)))

OK (10 msec)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ tnsping QPROD_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 = QPROD_DG)))

OK (10 msec)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus sys@QPROD 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@QPROD_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 QPROD

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 = QPROD)))

OK (0 msec)

[oracle@rac2 ~]$

[oracle@rac2 ~]$ tnsping QPROD_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 = QPROD_DG)))

OK (10 msec)

[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus sys@QPROD 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@QPROD_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

--------- ---------- --------------- ---------------- ----------------- ------------

QPROD MOUNTED QPROD 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

--------- -------------------- --------------- ---------------- ----------------- -----------

QPROD MOUNTED QPROD 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

--------- -------------------- --------------- ---------------- ----------------- ---------------------

QPROD READ WRITE QPROD_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

--------- -------------------- --------------- ---------------- ----------------- ----------

QPROD MOUNTED QPROD 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

--------- -------------------- --------------- ---------------- ----------------- ----------

QPROD MOUNTED QPROD 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

--------- -------------------- --------------- ---------------- ----------------- ---------------------

QPROD READ WRITE QPROD_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

--------- -------------------- --------------- ---------------- ----------------- ----------

QPROD MOUNTED QPROD 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

--------- -------------------- --------------- ---------------- ----------------- ---------------------

QPROD READ WRITE QPROD_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

--------- -------------------- --------------- ---------------- ----------------- ----------

QPROD MOUNTED QPROD 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

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(QPROD_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

--------- -------------------- --------------- ---------------- ----------------- ---------------------

QPROD READ WRITE QPROD_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

--------- -------------------- --------------- ---------------- ----------------- --------------------

QPROD MOUNTED QPROD_DG PHYSICAL STANDBY 19.0.0.0.0 rac2.rajasekhar.com <----

SQL>

60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/QPROD) 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

--------- -------------------- --------------- ---------------- ----------------- ----------

QPROD MOUNTED QPROD 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

------ -------------------- -------------- ---------------- --------------- ----------

QPROD READ WRITE QPROD PRIMARY 19.0.0.0.0 rac1 <-----

SQL>

61. Verify DBA_REGISTRY ON PRIMARY (QPROD)

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

------ -------------------- -------------- ---------------- --------------- ----------

QPROD READ WRITE QPROD 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/dr1QPROD.dat

dg_broker_config_file2 string /u01/app/oracle/product/19.0.0

/dbhome_1/dbs/dr2QPROD.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/dr1QPROD_DG.dat

dg_broker_config_file2 string /u01/app/oracle/product/19.0.0

/dbhome_1/dbs/dr2QPROD_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@QPROD AS SYSDBA

Password:

Connected to "QPROD"

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 'QPROD' as primary database is 'QPROD' connect identifier is QPROD;

Configuration "QPROD" created with primary database "QPROD"

DGMGRL>

DGMGRL> show configuration;

Configuration - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database <----

Fast-Start Failover: Disabled

Configuration Status:

DISABLED

DGMGRL> add database 'QPROD_DG' as connect identifier is QPROD_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

--------- -------------------- -------------- ---------------- ----------------- ---------------------

QPROD MOUNTED QPROD_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 'QPROD_DG' as connect identifier is QPROD_DG maintained as physical;

Database "QPROD_DG" added

DGMGRL>

DGMGRL> show configuration;

Configuration - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database

QPROD_DG - Physical standby database <----

Fast-Start Failover: Disabled

Configuration Status:

DISABLED

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL> show configuration;

Configuration - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database

QPROD_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 - QPROD

Protection Mode: MaxPerformance

Members:

QPROD - Primary database

QPROD_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 QPROD_DG;

Database - QPROD_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):

QPROD_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

------ -------------------- -------------- ---------------- --------------- ----------

QPROD READ WRITE QPROD PRIMARY 19.0.0.0.0 rac1

SQL> SELECT * FROM RUMA.SHOB;

NAME ROLE

---------- ----------

MQM DBA

X DBA

RUMA DBA <-----

SQL>

Points:

++ 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.

++ 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.

Learning Oracle

Pages

Sunday, July 19, 2020

Rolling Database Upgrade from 12.2.0.1 to 19c Using Transient Logical Standby

Rolling Database Upgrade from 12.2.0.1 to 19c Using Transient Logical Standby

No comments:

Post a Comment