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


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.

No comments:

Post a Comment