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