Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby
Table of Contents
___________________________________________________________________________________________________
2. Disable DG Broker
3. Install 19c database software on primary
4. Install 19c database software on standby
5. Apply latest Release Update on 19c standby
6. Apply latest Release Update on 19c primary
7. Database Backup
8. Verify INVLAID OBJECTS
9. Verify protection mode
10. Verify fast_recovery_area size
11. Verify archive log dest size
12. Internal Schema Support
13. Find list of objects are not supported
14. Check the reason for unsupported objects
15. Find list of objects are not supported
16. Refresh MVs
17. Gather DICTIONARY STATS
18. Enable Flashback Database
19. Create Flashback Guaranteed Restore Point (On Primary)
20. Verify GAP
CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY
21. Cancel MRP (Standby)
22. Build the logminer dictionary (Primary)
23. Convert Physical to Logical Standby
24. Verify DATABASE_ROLE
25. Open Logical Database
26. Start SQL Apply
27. Verify GAP
28. Run pre-upgrade script
29. View Pre-upgrade log
30. Verify tablespace sizes for upgrade
31. Run preupgrade_fixups.sql
32. Create GUARANTEED Restore Point (Logical STANDBY)
33. Run DBUA from 19c ORACLE HOME
POST-UPGRADE TASKS WHEN DBUA USING
34. Verify /etc/oratab
35. Verify Timezone version
36. Verify INVALID objects
37. Verify DBA_REGISTRY (HKP_DG)
38. Start SQL Apply
39. Verify GAP
SWITCHOVER PRIMARY DATABASE (HKP) TO LOGICAL STANDBY
40. Pre-Switchover tasks
41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY
42. Verify alert_HKP.log
SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY
43. Switchover upgraded logical standby to primary
44. Verify alert_HKP_DG.log
CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
45. Flashback database to restore point
46. Add /etc/oratab entry – 19c
47. Copy password file / initialization file to 19c home/dbs
48. Configure TNS Entries
49. Mount Database using 19c Oracle software
50. Verify TNS Connectivity
51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
52. Enable MRP
53. Verify GAP
54. Disable Flashback Database
55. Drop Restore point
56. Update COMPATIBLE parameter on both primary/standby
57. Verify GAP
58. Revert back parameter values on both primary/standby (In case if you changed)
Below Steps are Optional
59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY
60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY
61. Verify DBA_REGISTRY
62. Configure DG Broker
63. Verify GAP
64. Verify Table
__________________________________________________________________________________________________
++ Perform a rolling database upgrade from 12.2.0.1 to Oracle 19c using a Data Guard physical standby database and transient logical standby database. ++ Rolling upgrade procedure greatly reduces the downtime for an upgrade from hours to a few minutes. ++ Database Downtime only with database switchover duration. ++ Logical standby process uses SQL Apply to take redo generated by a database running a lower Oracle version (12.2.0.1) and apply the redo to a standby database running on a higher Oracle version (19c). Restore point: Restore point is nothing but a name associated with a timestamp or an SCN of the database. Types of restore point: 1. Normal restore point 2. Guaranteed restore point ++ The difference between the two is that guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available (assuming you have enough space in the flash recovery area). To Create Guaranteed Restore point: Prerequisites: ++ The user must have the SYSDBA system privileges ++ Must have created a flash recovery area ++ The database must be in ARCHIVELOG mode ++ To create guaranteed restore point, no need to TRUN ON Flashback Database. I would highly recommend you to enable flashback on both primary and standby database, incase DB upgrade crashed, ++ What will happen to standby database if primary database opened with resetlogs ??? If Flashback database enabled on both primary and standby then no action required from DBA side. If FLASHBACK is NOT enabled on standby database and standby applied redo data past the new resetlogs SCN then recreate the standby database. http://www.br8dba.com/resetlogs-on-primary-where-standby-in-place/ ++ Apply latest RU on 12.2.0.1 on both primary and standby (Recommended) ++ The Data Guard protection mode must be set to either maximum availability or maximum performance. The Data Guard protection mode cannot be set to maximum protection during the rolling upgrade. ++ The databases must not be part of a Data Guard Broker configuration. Data Guard Broker configurations are not supported during a rolling upgrade. If Data Guard Broker is being used, it will need to be disabled on both the primary and standby. Data Guard Broker can be re-enabled after completing the rolling upgrade. ++ To ensure the primary database can proceed while the logical standby database is being upgraded, the LOG_ARCHIVE_DEST_n initialization parameter for the logical standby database destination must be set to OPTIONAL (not MANDATORY). ++ The COMPATIBLE initialization parameter must match the software release prior to the upgrade. That is, a rolling upgrade from database release X to database release Y requires that the COMPATIBLE initialization parameter be set to database release X on both the primary and standby databases throughout the rolling upgrade process. The COMPATIBLE parameter will be set to the new Oracle version after completing the rolling upgrade when both databases have been upgraded and you are satisfied with the new Oracle version. ++ Oracle recommends that you add a primary key or a non-null unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.
On Primary
Hostname : RAC1.RAJASEKHAR.COM Database Name : HKP DB VERSION : 12.2.0.1 DB Home Path : /u01/app/oracle/product/12.2.0/dbhome_1 Datafile Location : /u01/app/oracle/oradata/HKP Target DB VERSION : 19c Target DB Path : /u01/app/oracle/product/19.0.0/dbhome_1
On STANDBY
Hostname : RAC2.RAJASEKHAR.COM STANDBY Database Name : HKP_DG DB VERSION : 12.2.0.1 DB Home Path : /u01/app/oracle/product/12.2.0/dbhome_1 Datafile Location : /u01/app/oracle/oradata/HKP_DG Target DB VERSION : 19c Target DB Path : /u01/app/oracle/product/19.0.0/dbhome_1 Upgrade Method : Rolling Upgrade using Transient Logical Standby Database Upgrade Assistant Database Protection mode: Maximum Performance
On Primary
[oracle@rac1 ~]$ dgmgrl DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sun Feb 9 16:58:00 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@HKP Password: Connected to "HKP" Connected as SYSDBA. DGMGRL> DGMGRL> show configuration; Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database HKP_DG - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 35 seconds ago) DGMGRL> DGMGRL> DISABLE CONFIGURATION; <---- Disabled. DGMGRL> DGMGRL> show configuration; Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database HKP_DG - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED <---- DGMGRL> SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE SQL> SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE; System altered. SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE SQL>
On STANDBY
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP MOUNTED PHYSICAL STANDBY SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE; System altered. SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE <--- SQL>
3. Install 19c database software on primary
Below is sample doc. http://www.br8dba.com/install-19c/
4. Install 19c database software on standby
Below is sample doc. Please modify according to your requirement http://www.br8dba.com/install-19c/
5. Apply latest Release Update on 19c standby
Below is sample doc. Please modify according to your requirement http://www.br8dba.com/apply-ru-on-database-19c/
6. Apply latest Release Update on 19c primary
Below is sample doc. Please modify according to your requirement http://www.br8dba.com/apply-ru-on-database-19c/
RMAN > connect target / run { allocate channel t1 type disk; allocate channel t2 type disk; allocate channel t3 type disk; backup database format '/u02/oracle/backup/database_%d_%u_%s'; release channel t1; release channel t2; release channel t3; } sql 'alter system archive log current'; run { allocate channel a1 type disk; allocate channel a2 type disk; allocate channel a3 type disk; backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s'; release channel a1; release channel a2; release channel a3; } run { allocate channel c1 type disk; backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s'; release channel c1; } exit; sample doc. http://www.br8dba.com/backup-based-rman-duplicate-database/#3
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 0 <----- SQL>
On Primary
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- HKP READ WRITE PRIMARY MAXIMUM PERFORMANCE <--- SQL>
On STANDBY
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- HKP MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL>
10. Verify fast_recovery_area size
On PRIMARY
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 10G <---- SQL> [oracle@rac1 ~]$ df -h /u01/app/oracle/fast_recovery_area Filesystem Size Used Avail Use% Mounted on /dev/sda5 67G 36G 32G 54% /u01 [oracle@rac1 ~]$
On STANDBY
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- HKP MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 10G <---- SQL> [oracle@rac2 ~]$ df -h /u01/app/oracle/fast_recovery_area Filesystem Size Used Avail Use% Mounted on /dev/sda5 72G 59G 14G 82% /u01 [oracle@rac2 ~]$
11. Verify archive log dest size
On PRIMARY
[oracle@rac1 ~]$ df -h /u01/app/archive/HKP Filesystem Size Used Avail Use% Mounted on /dev/sda5 67G 36G 32G 54% /u01 <---- [oracle@rac1 ~]$
On STANDBY
[oracle@rac2 ~]$ df -h /u01/app/archive/HKP_DG Filesystem Size Used Avail Use% Mounted on /dev/sda5 72G 59G 14G 82% /u01 <---- [oracle@rac2 ~]$
++ Below schemas are automatically skipped by SQL Apply. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY <---- SQL> SQL> set pages 999 SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA' order by owner; OWNER -------------------------------------------------------------------------------- ANONYMOUS APPQOSSYS AUDSYS CTXSYS DBSFWUSER DBSNMP DIP DVF DVSYS GGSYS GSMADMIN_INTERNAL GSMCATUSER GSMUSER LBACSYS MDSYS OJVMSYS OLAPSYS ORACLE_OCM ORDDATA ORDPLUGINS ORDSYS OUTLN REMOTE_SCHEDULER_AGENT SI_INFORMTN_SCHEMA SPATIAL_CSW_ADMIN_USR SYS SYS$UMF SYSBACKUP SYSDG SYSKM SYSRAC SYSTEM WMSYS XDB XS$NULL 35 rows selected. SQL>
13. Find list of objects are not supported
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name; no rows selected SQL>
14. Check the reason for unsupported objects
select column_name,data_type from dba_logstdby_unsupported where owner='&OWNER' AND TABLE_NAME='&TABLE_NAME';
15. Find list of objects are not supported
SQL> COL OWNER FOR A10 SQL> COL TABLE_NAME FOR A10 SQL> SET LINES 190 SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE; OWNER TABLE_NAME B ---------- ---------- - SUGI TEJA N SQL> SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name; OWNER TABLE_NAME ---------- ---------- SUGI TEJA SQL> Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key SQL> ALTER TABLE SUGI.TEJA ADD CONSTRAINT PK_NAME PRIMARY KEY (NAME); Table altered. SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name; no rows selected <---- SQL>
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY SQL> SQL> declare list_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL>
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY SQL> SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL>
18. Create Flashback Guaranteed Restore Point (On Primary)
++ The user must have the SYSDBA system privileges ++ Must have created a flash recovery area ++ The database must be in ARCHIVELOG mode ++ Guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available when you have enough space in the flash recovery area. ++ NO need to enable Flashback Database from 11.2.0.1 on wards ++ MUST NOT change the compatible parameter to higher version SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY <--- SQL> select * from V$restore_point; no rows selected <--- SQL> create restore point pre_upgrade guarantee flashback database; Restore point created. SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 09-FEB-20 07.27.58.000000000 PM SQL>
On PRIMARY
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP READ WRITE PRIMARY SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO <----- SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES <---- SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL>
On STANDBY
SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- HKP MOUNTED PHYSICAL STANDBY <----- SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO <----- SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES <---- SQL> show parameter flashback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL>
On PRIMARY
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP PRIMARY SQL> SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 23 23 0 1 23 23 0 SQL>
On STANDBY
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP MOUNTED HKP_DG PHYSICAL STANDBY SQL> SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 23 23 0 <--- SQL>
CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP MOUNTED HKP_DG PHYSICAL STANDBY SQL> SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%'; PROCESS STATUS SEQUENCE# THREAD# --------- ------------ ---------- ---------- MRP0 APPLYING_LOG 27 1 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%'; no rows selected <---- SQL>
22. Build the logminer dictionary (Primary)
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP PRIMARY SQL> set serveroutput on SQL> execute dbms_logstdby.build; PL/SQL procedure successfully completed. <---- SQL> SELECT * FROM V$LOGSTDBY_STATE WHERE STATE='LOADING DICTIONARY'; no rows selected SQL>
23. Convert Physical to Logical Standby
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP MOUNTED HKP_DG PHYSICAL STANDBY SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY; Database altered. SQL> Filename: alert_HKP_DG.log 2020-02-09T19:50:37.383801+08:00 ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY 2020-02-09T19:50:37.384464+08:00 Media Recovery Start: Managed Standby Recovery (HKP_DG) 2020-02-09T19:50:37.386900+08:00 Serial Media Recovery started Managed Standby Recovery not using Real Time Apply 2020-02-09T19:50:37.582360+08:00 Media Recovery Log /u01/app/archive/HKP_DG/1_27_1031841246.dbf 2020-02-09T19:50:37.841765+08:00 Media Recovery Log /u01/app/archive/HKP_DG/1_28_1031841246.dbf Resize operation completed for file# 1, old size 829440K, new size 839680K 2020-02-09T19:50:38.661868+08:00 Resize operation completed for file# 1, old size 839680K, new size 849920K 2020-02-09T19:50:39.187191+08:00 Media Recovery Log /u01/app/archive/HKP_DG/1_29_1031841246.dbf 2020-02-09T19:50:39.298950+08:00 Incomplete Recovery applied until change 1766613 time 02/09/2020 19:47:32 2020-02-09T19:50:39.327325+08:00 Media Recovery Complete (HKP_DG) Killing 3 processes (PIDS:20446,19128,7053) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 18995 2020-02-09T19:50:41.382297+08:00 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 1766613 time 02/09/2020 19:47:32 Resetting resetlogs activation ID 3259734620 (0xc24b9a5c) Online log /u01/app/oracle/oradata/HKP_DG/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/oradata/HKP_DG/redo02.log: Thread 1 Group 2 was previously cleared Online log /u01/app/oracle/oradata/HKP_DG/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1766611 2020-02-09T19:50:41.551507+08:00 Setting recovery target incarnation to 3 <------- 2020-02-09T19:50:41.668431+08:00 Network throttle feature is disabled as mount time RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP MOUNTED HKP_DG LOGICAL STANDBY <----- SQL>
SQL> ALTER DATABASE OPEN; Database altered. SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP_DG LOGICAL STANDBY SQL>
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered. SQL> !ps -ef | grep lsp oracle 28600 1 1 19:55 ? 00:00:00 ora_lsp0_HKP_DG oracle 28710 18994 0 19:56 pts/1 00:00:00 /bin/bash -c ps -ef | grep lsp oracle 28712 28710 0 19:56 pts/1 00:00:00 grep lsp SQL> Filename: alert_HKP_DG.log 2020-02-09T19:55:39.530980+08:00 ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE 2020-02-09T19:55:39.740957+08:00 LOGSTDBY: Creating new session for dbid 3259744860 starting at scn 0x0000000000000000 2020-02-09T19:55:39.750358+08:00 LOGSTDBY: Created session of id 1 2020-02-09T19:55:39.841666+08:00 LOGSTDBY: Attempting to pre-register dictionary build logfiles 2020-02-09T19:55:39.876273+08:00 LOGMINER: session# 1 Error 308 encountered, failed to read logfile 1_28_1031841246.dbf LOGMINER: Encountered error 1291 while adding logfile 1_28_1031841246.dbf to session 1 LOGSTDBY: Unable to register recovery logfiles, will resend 2020-02-09T19:55:39.902719+08:00 ALTER DATABASE START LOGICAL STANDBY APPLY (HKP_DG) with optional part IMMEDIATE Attempt to start background Logical Standby process Starting background process LSP0 2020-02-09T19:55:39.929700+08:00 LSP0 started with pid=77, OS id=28600 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE 2020-02-09T19:55:40.943107+08:00 LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 100M, Checkpoint interval = 500M LOGMINER: SpillScn 0, ResetLogScn 0 2020-02-09T19:56:30.449522+08:00 RFS[10]: Assigned to RFS process (PID:28747) RFS[10]: Opened log for T-1.S-30 dbid 3259744860 branch 1031841246 2020-02-09T19:56:30.457282+08:00 RFS[11]: Assigned to RFS process (PID:28749) RFS[11]: Opened log for T-1.S-29 dbid 3259744860 branch 1031841246 2020-02-09T19:56:30.458727+08:00 RFS[12]: Assigned to RFS process (PID:28751) RFS[12]: Opened log for T-1.S-28 dbid 3259744860 branch 1031841246 2020-02-09T19:56:30.631848+08:00 RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_29_1031841246.dbf] to LogMiner session id [1] 2020-02-09T19:56:30.635771+08:00 RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_30_1031841246.dbf] to LogMiner session id [1] 2020-02-09T19:56:30.757026+08:00 RFS LogMiner: Registered logfile [/u01/app/archive/HKP_DG/1_28_1031841246.dbf] to LogMiner session id [1] 2020-02-09T19:56:30.790912+08:00 RFS LogMiner: RFS id [28178] assigned as thread [1] PING handler 2020-02-09T19:56:31.210747+08:00 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 1766211 (0x00000000001af343) LOGMINER: EndScn: 0 (0x0000000000000000) LOGMINER: HighConsumedScn: 1766612 (0x00000000001af4d4) LOGMINER: PSR flags: 0x1 LOGMINER: Session Flags: 0xba110dc LOGMINER: Session Flags2: 0x4000 LOGMINER: Read buffers: 16 LOGMINER: Memory LWM: limit 10M, LWM 90M, 90% LOGMINER: Memory Release Limit: 1M LOGMINER: Max Decomp Region Memory: 1M 2020-02-09T19:56:31.303271+08:00 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=86 OS id=28754 sid=123 started 2020-02-09T19:56:31.329323+08:00 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=87 OS id=28756 sid=112 started 2020-02-09T19:56:31.352188+08:00 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=88 OS id=28758 sid=127 started 2020-02-09T19:56:32.378089+08:00 LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf 2020-02-09T19:56:34.713730+08:00 LOGMINER: Preparing to load 1 dictionaries for session 1 2020-02-09T19:56:38.021593+08:00 Resize operation completed for file# 3, old size 522240K, new size 532480K 2020-02-09T19:56:38.232296+08:00 Resize operation completed for file# 3, old size 532480K, new size 552960K 2020-02-09T19:56:42.053024+08:00 LOGMINER: Finalizing dictionary load for session 1 2020-02-09T19:56:43.677904+08:00 LOGMINER: Gathering statistics on logminer dictionary. (incremental, nonparallel) 2020-02-09T19:56:55.507688+08:00 LOGMINER: Completed dictionary load for session 1 2020-02-09T19:56:58.556285+08:00 LOGMINER: End mining logfiles during dictionary load for session 1 Starting background process LSP2 2020-02-09T19:56:59.235469+08:00 LSP2 started with pid=90, OS id=28852 2020-02-09T19:57:02.786146+08:00 LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf 2020-02-09T19:57:03.094739+08:00 LOGMINER: End mining logfile during commit scan for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf 2020-02-09T19:57:03.098445+08:00 LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf 2020-02-09T19:57:04.730566+08:00 LOGMINER: End mining logfiles during commit scan for session 1 2020-02-09T19:57:04.746535+08:00 LOGMINER: Begin mining logfile for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf 2020-02-09T19:57:05.348148+08:00 LOGSTDBY Apply process AS04 started with server id=4 pid=93 OS id=28881 2020-02-09T19:57:05.349597+08:00 LOGSTDBY Apply process AS03 started with server id=3 pid=92 OS id=28879 2020-02-09T19:57:05.357542+08:00 LOGSTDBY Apply process AS05 started with server id=5 pid=94 OS id=28883 2020-02-09T19:57:05.369323+08:00 LOGSTDBY Analyzer process AS00 started with server id=0 pid=89 OS id=28873 2020-02-09T19:57:05.381952+08:00 LOGSTDBY Apply process AS02 started with server id=2 pid=91 OS id=28877 2020-02-09T19:57:05.393689+08:00 LOGSTDBY Apply process AS01 started with server id=1 pid=90 OS id=28875 2020-02-09T19:57:05.917293+08:00 LOGMINER: End mining logfile for session 1 thread 1 sequence 28, /u01/app/archive/HKP_DG/1_28_1031841246.dbf 2020-02-09T19:57:05.919218+08:00 LOGMINER: Begin mining logfile for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf 2020-02-09T19:57:05.919362+08:00 LOGMINER: End mining logfile for session 1 thread 1 sequence 29, /u01/app/archive/HKP_DG/1_29_1031841246.dbf 2020-02-09T19:57:05.923045+08:00 LOGMINER: Begin mining logfile for session 1 thread 1 sequence 30, /u01/app/archive/HKP_DG/1_30_1031841246.dbf 2020-02-09T19:57:05.923418+08:00 LOGMINER: End mining logfile for session 1 thread 1 sequence 30, /u01/app/archive/HKP_DG/1_30_1031841246.dbf 2020-02-09T19:57:05.927229+08:00 LOGMINER: Begin mining logfile for session 1 thread 1 sequence 31, /u01/app/oracle/oradata/HKP_DG/redo04.log 2020-02-09T19:57:07.956796+08:00 XDB installed. 2020-02-09T19:57:12.644145+08:00 XDB initialized. 2020-02-09T19:58:32.953910+08:00 Resize operation completed for file# 3, old size 552960K, new size 573440K
On Primary
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE -------------------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP PRIMARY SQL> SQL> CONN SUGI/SUGI; Connected. SQL> INSERT INTO TEJA VALUES ('X','DBA'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TEJA; NAME ROLE -------------------- ---------- RAJ DBA X DBA <----- SQL>
On Logical STANDBY
SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE ---------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP_DG LOGICAL STANDBY SQL> SQL> SELECT * FROM SUGI.TEJA; NAME ROLE -------------------------------------------------- ---------- RAJ DBA X DBA <----- SQL> SQL> conn SUGI/SUGI; Connected. SQL> INSERT INTO TEJA VALUES ('Y','DBA'); INSERT INTO TEJA VALUES ('Y','DBA') * ERROR at line 1: ORA-16224: Database Guard is enabled SQL> CONN / AS SYSDBA Connected. SQL> select guard_status from v$database; GUARD_S ------- ALL <----- SQL> The guard_status column protects the data from being changed. There are three values: ALL - All users other than SYS are prevented from making changes to any data in the database. STANDBY - All users other than SYS are prevented from making changes to any database object being maintained by logical standby. NONE - Indicates normal security for all data in the database.
[oracle@rac2 ~]$ . oraenv ORACLE_SID = [HKP_DG] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac2 ~]$ [oracle@rac2 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade ================== PREUPGRADE SUMMARY ================== /home/oracle/preupgrade/preupgrade.log <---- /home/oracle/preupgrade/preupgrade_fixups.sql /home/oracle/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/home/oracle/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/home/oracle/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2020-02-09T20:20:44 [oracle@rac2 ~]$
[oracle@rac2 ~]$ cat /home/oracle/preupgrade/preupgrade.log Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2020-02-09T20:20:44 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: HKP Container Name: HKP Container ID: 0 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: ARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None INFORMATION ONLY ================ 1. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 560 MB 576 MB SYSTEM 830 MB 943 MB TEMP 20 MB 150 MB UNDOTBS1 70 MB 439 MB Minimum tablespace sizes for upgrade are estimates. 2. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least 4618 MB of archived logs. Check alert log during the upgrade that there is no write error to the destination due to lack of disk space. Archiving cannot proceed if the archive log destination is full during upgrade. Archive Log Destination: Parameter : LOG_ARCHIVE_DEST_1 Destination : /u01/app/archive/HKP_DG The database has archiving enabled. The upgrade process will need free disk space in the archive log destination(s) to generate archived logs to. 3. Check the Oracle Backup and Recovery User's Guide for information on how to manage an RMAN recovery catalog schema. If you are using a version of the recovery catalog schema that is older than that required by the RMAN client version, then you must upgrade the catalog schema. It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using. ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 4. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 19 release ships with time zone file version 32. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 19 Oracle Database Globalization Support Guide. 5. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 6. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database HKP which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following SQL>@/home/oracle/preupgrade/postupgrade_fixups.sql [oracle@rac2 ~]$
30. Verify tablespace sizes for upgrade
** Tablespace Auto extend ON and max size also set, hence no action taken. http://www.br8dba.com/asm-2/#tspace TABLESPACE_NAME AUT FILE_NAME TOTAL_SPACE FREE_SPACE Free% MAX_SPACE ------------------------------ --- ----------------------------------------------- ----------- ---------- ---------- ---------- SYSAUX YES /u01/app/oracle/oradata/HKP_DG/sysaux01.dbf 560 32 5.68 31.9999847 SYSTEM YES /u01/app/oracle/oradata/HKP_DG/system01.dbf 830 3 .38 31.9999847 UNDOTBS1 YES /u01/app/oracle/oradata/HKP_DG/undotbs01.dbf 70 37 53.04 31.9999847 USERS YES /u01/app/oracle/oradata/HKP_DG/users01.dbf 5 4 77.5 31.9999847 ****************************** ----------- ---------- ---------- sum 1465 76 127.999939 SQL>
++ It will run by DBUA, however we are executing manually before start upgrade. SQL> select name,open_mode,db_unique_name,database_role from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE --------- -------------------- ------------------------------ ---------------- HKP READ WRITE HKP_DG LOGICAL STANDBY SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql SQL> REM SQL> REM Oracle PRE-Upgrade Fixup Script SQL> REM SQL> REM Auto-Generated by: Oracle Preupgrade Script SQL> REM Version: 19.0.0.0.0 Build: 1 SQL> REM Generated on: 2020-02-09 20:20:41 SQL> REM SQL> REM Source Database: HKP SQL> REM Source Database Version: 12.2.0.1.0 SQL> REM For Upgrade to Version: 19.0.0.0.0 SQL> REM SQL> SQL> REM SQL> REM Setup Environment SQL> REM SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200; Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2020-02-09 20:20:41 For Source Database: HKP Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 1. tablespaces_info NO Informational only. Further action is optional. 2. min_archive_dest_size NO Informational only. Further action is optional. 3. rman_recovery_version NO Informational only. Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database is not ready for upgrade. To resolve the outstanding issues, start by reviewing the preupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed. SQL>
32. Create GUARANTEED Restore Point (Logical STANDBY)
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected SQL> CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE; Restore point created. <----- SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ BEFORE_UPGRADE YES 09-FEB-20 08.49.22.000000000 PM SQL>
33. Run DBUA from 19c ORACLE HOME
[oracle@rac2 ~]$ export DISPLAY=192.168.2.2:0.0 [oracle@rac2 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbua
Logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-02-09_09-04-14PM Database upgrade has been completed successfully, and the database is ready to use.
Logs directory: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-02-09_09-04-14PM
Database upgrade has been completed successfully, and the database is ready to use.
POST-UPGRADE TASKS WHEN DBUA USING
[oracle@rac2 ~]$ cat /etc/oratab | grep -i "HKP_DG" HKP_DG:/u01/app/oracle/product/19.0.0/dbhome_1:N [oracle@rac2 ~]$
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 <---- SQL> SQL> SELECT version FROM v$timezone_file; VERSION ---------- 32 <---- SQL>
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 0 <----- SQL>
37. Verify DBA_REGISTRY (HKP_DG)
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 SQL> SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- -------------------------------------------- CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID XML Oracle XDK 19.0.0.0.0 VALID CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID APS OLAP Analytic Workspace 19.0.0.0.0 VALID RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF XDB Oracle XML Database 19.0.0.0.0 VALID OWM Oracle Workspace Manager 19.0.0.0.0 VALID CONTEXT Oracle Text 19.0.0.0.0 VALID ORDIM Oracle Multimedia 19.0.0.0.0 VALID SDO Spatial 19.0.0.0.0 VALID XOQ Oracle OLAP API 19.0.0.0.0 VALID OLS Oracle Label Security 19.0.0.0.0 VALID DV Oracle Database Vault 19.0.0.0.0 VALID 15 rows selected. SQL>
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 SQL> [oracle@rac2 ~]$ ps -ef | grep lsp oracle 6953 10539 0 22:59 pts/1 00:00:00 grep --color=auto lsp [oracle@rac2 ~]$ SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered. SQL> SQL> !ps -ef | grep lsp oracle 7246 1 3 23:01 ? 00:00:03 ora_lsp0_HKP_DG oracle 7568 6479 0 23:03 pts/0 00:00:00 /bin/bash -c ps -ef | grep lsp oracle 7570 7568 0 23:03 pts/0 00:00:00 grep lsp SQL> Filename: alert_HKP_DG.log 2020-02-09T23:01:27.014215+08:00 ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE 2020-02-09T23:01:27.196901+08:00 ALTER DATABASE START LOGICAL STANDBY APPLY (HKP_DG) 2020-02-09T23:01:27.196974+08:00 with optional part IMMEDIATE Attempt to start background Logical Standby process Starting background process LSP0 2020-02-09T23:01:27.227080+08:00 LSP0 started with pid=87, OS id=7246 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Test case:
On PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP PRIMARY 12.2.0.1.0 <----- SQL> SQL> CONN SUGI/SUGI; Connected. SQL> SELECT * FROM TEJA; NAME ROLE ---------- ---------- RAJ DBA X DBA SQL> INSERT INTO TEJA VALUES ('SUGI','DBA'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TEJA; NAME ROLE ---------- ---------- RAJ DBA X DBA SUGI DBA <------ SQL> SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 31 <----- SQL>
On STANDBY
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- --------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 SQL> SQL> SELECT * FROM SUGI.TEJA; NAME ROLE ---------- ---------- RAJ DBA X DBA SUGI DBA <------ SQL> SQL> col REALTIME_APPLY for a20 SQL> select * from v$logstdby_state; PRIMARY_DBID PRIMARY_CON_DBID SESSION_ID REALTIME_APPLY STATE CON_ID ------------ ---------------- ---------- -------------------- ---------- ---------- 3259744860 0 1 Y IDLE 0 SQL> SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE ---------- ------------- ------------ --------- -------- ---------- ---------- 28 1766211 1766603 09-FEB-20 YES 35440 512 29 1766603 1766619 09-FEB-20 YES 28 512 30 1766619 1767498 09-FEB-20 YES 483 512 31 1767498 1787013 09-FEB-20 YES 123270 512 SQL>
SWITCHOVER PRIMARY DATABASE (HKP) TO LOGICAL STANDBY
*** Verify that there is network connectivity between the primary and standby locations.
*** Always recommened test the switchover in your testing system befre working on production.
*** Verify primary database READ WRITE and standby instance mounted.
*** Verify there are no active users connected to database.
*** Make sure last redo data transmitted from primary to standby and applied.
*** Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
alter system set job_queue_processes=0 ;
alter system set aq_tm_processes = 0 ;
alter system set dbwr_io_slaves = 0 ;
On Primary
SQL> set lines 190 SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP PRIMARY 12.2.0.1.0 SQL> select name from v$datafile where status='OFFLINE'; no rows selected SQL> select * from dba_jobs_running; no rows selected SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 0 SQL> Note: Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no jobs running during switchover.
On Standby
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- --------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 SQL> SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE ---------- ------------- ------------ --------- -------- ---------- ---------- 28 1766211 1766603 09-FEB-20 YES 35440 512 29 1766603 1766619 09-FEB-20 YES 28 512 30 1766619 1767498 09-FEB-20 YES 483 512 31 1767498 1787013 09-FEB-20 YES 123270 512 SQL> select name from v$datafile where status='OFFLINE'; no rows selected SQL> SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS; SYSDATE APPLIED_TIME ------------------ ------------------ 09-FEB-20 23:25:40 09-FEB-20 23:25:35 SQL>
41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP PRIMARY 12.2.0.1.0 SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY <---- SQL> Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; Database altered. SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP LOGICAL STANDBY 12.2.0.1.0 <---- SQL>
Filename: alert_HKP.log 2020-02-09T23:26:59.463554+08:00 ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY 2020-02-09T23:26:59.463682+08:00 ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (HKP) 2020-02-09T23:26:59.466379+08:00 LOGSTDBY: Switchover initiated via DDL LOGSTDBY: Ensuring no active hot backups. LOGSTDBY: Archiving current online log files. 2020-02-09T23:26:59.588667+08:00 Thread 1 advanced to log sequence 33 (LGWR switch) Current log# 3 seq# 33 mem# 0: /u01/app/oracle/oradata/HKP/redo03.log 2020-02-09T23:27:00.228257+08:00 Archived Log entry 65 added for T-1.S-32 ID 0xc24b9a5c LAD:1 2020-02-09T23:27:00.277230+08:00 LOGSTDBY: Waiting for pending archivals to all destinations. 2020-02-09T23:27:00.320999+08:00 Waiting for all non-current ORLs to be archived 2020-02-09T23:27:00.321160+08:00 All non-current ORLs have been archived 2020-02-09T23:27:00.321286+08:00 Waiting for all FAL entries to be archived 2020-02-09T23:27:00.326657+08:00 All FAL entries have been archived 2020-02-09T23:27:00.326862+08:00 Waiting for potential Logical Standby switchover target to become synchronized 2020-02-09T23:27:01.287138+08:00 TT02: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2 2020-02-09T23:27:02.328956+08:00 Active, synchronized Logical Standby switchover target has been identified 2020-02-09T23:27:02.329025+08:00 LOGSTDBY: Enabling database guard. LOGSTDBY: Waiting for completion of transactions started at or before scn 1816838 (0x00000000001bb906) LOGSTDBY: All transactions started at or before scn 1816838 (0x00000000001bb906) have completed 2020-02-09T23:27:02.462169+08:00 LOGSTDBY: Database guard enabled. User transactions are no longer permitted. 2020-02-09T23:27:02.462242+08:00 LOGSTDBY: Waiting for pending archivals to all destinations. 2020-02-09T23:27:02.498949+08:00 Waiting for all non-current ORLs to be archived 2020-02-09T23:27:02.499038+08:00 All non-current ORLs have been archived 2020-02-09T23:27:02.503040+08:00 Waiting for all FAL entries to be archived 2020-02-09T23:27:02.503187+08:00 All FAL entries have been archived 2020-02-09T23:27:02.503283+08:00 Waiting for potential Logical Standby switchover target to become synchronized 2020-02-09T23:27:03.505105+08:00 Active, synchronized Logical Standby switchover target has been identified LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn 1816841 [0x00000000001bb909]. 2020-02-09T23:27:03.995731+08:00 Thread 1 advanced to log sequence 34 (LGWR switch) Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log 2020-02-09T23:27:04.999145+08:00 ARCH: LGWR is scheduled to archive to LAD:2 after log switch ARCH: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2 2020-02-09T23:27:05.350386+08:00 Archived Log entry 66 added for T-1.S-33 ID 0xc24b9a5c LAD:1 LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover target 2020-02-09T23:27:05.638889+08:00 Thread 1 cannot allocate new log, sequence 35 Checkpoint not complete Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log 2020-02-09T23:27:06.299087+08:00 Thread 1 advanced to log sequence 35 (LGWR switch) Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/HKP/redo02.log 2020-02-09T23:27:06.308965+08:00 Archived Log entry 68 added for T-1.S-34 ID 0xc24b9a5c LAD:1 2020-02-09T23:27:06.329142+08:00 LOGSTDBY: Switchover complete (HKP) LOGSTDBY: enabling scheduler job queue processes. 2020-02-09T23:27:06.329295+08:00 JOBQ: re-enabling CJQ0 Starting background process CJQ0 2020-02-09T23:27:06.346474+08:00 CJQ0 started with pid=70, OS id=1067 2020-02-09T23:27:06.449711+08:00 ARCt: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY
SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY
43. Switchover upgraded logical standby to primary
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- -------------------------------- --------------- HKP READ WRITE HKP_DG LOGICAL STANDBY 19.0.0.0.0 SQL> SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY <----- SQL> SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- ---------------- ---------------- --------------- -------------------- HKP READ WRITE HKP_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com <--- SQL>
Filename: alert_HKP_DG.log ALTER DATABASE SWITCHOVER TO PRIMARY (HKP_DG) 2020-02-09T23:36:45.455212+08:00 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (HKP_DG) 2020-02-09T23:36:45.455516+08:00 LOGSTDBY: Switchover initiated via DDL LOGSTDBY: Preparing to Create Detached Dictionary Build, pre-lockdown scn is [0x00000000002bbf0c] Sun Feb 09 23:36:45 2020 Logminer Bld: Build started 2020-02-09T23:36:45.589273+08:00 ALTER SYSTEM SWITCH ALL LOGFILE start (HKP_DG) 2020-02-09T23:36:45.636455+08:00 Thread 1 advanced to log sequence 36 (LGWR switch) Current log# 3 seq# 36 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo03.log 2020-02-09T23:36:45.641040+08:00 ALTER SYSTEM SWITCH ALL LOGFILE complete (HKP_DG) 2020-02-09T23:36:45.701288+08:00 Sun Feb 09 23:36:45 2020 Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 2866962 LockdownSCN is 2866962 LOGSTDBY: Starting SCN of new stream from recent lockdown [0x00000000002bbf12] 2020-02-09T23:36:45.701730+08:00 LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [2866965]. LOGSTDBY: Successful close of the current log stream: LOGSTDBY: primary: [3259744860] LOGSTDBY: first scn: [0x0000000000000000] LOGSTDBY: end scn: [0x00000000001bb912] LOGSTDBY: processed scn: [0x00000000001bb913] 2020-02-09T23:36:46.270609+08:00 LOGSTDBY: terminating active RFS connections for role change LOGSTDBY: terminated RFS process [12084] 2020-02-09T23:36:46.271142+08:00 Process termination requested for pid 12084 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321] 2020-02-09T23:36:46.271465+08:00 Process termination requested for pid 12100 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321] LOGSTDBY: terminated RFS process [12100] 2020-02-09T23:36:46.271856+08:00 LOGSTDBY: terminated RFS process [12102] 2020-02-09T23:36:46.272637+08:00 Process termination requested for pid 12102 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321] 2020-02-09T23:36:46.273165+08:00 ARC4 (PID:30701): Archived Log entry 62 added for T-1.S-35 ID 0xc24e1bb5 LAD:1 2020-02-09T23:36:46.276222+08:00 LOGSTDBY: (dglcccsp) Archiving standby redo logfiles. LOGSTDBY: (dglcccsp) Not using surrogate archiving mode LOGSTDBY: (dglcccsp) Found [1] standby redo logfiles to archive 2020-02-09T23:37:11.785235+08:00 LOGSTDBY: (dglcccsp) Complete. [1] standby redo logfiles were archived. 2020-02-09T23:37:11.846193+08:00 NET (PID:6480): Database role cleared from LOGICAL STANDBY [dglc.c:1953] Starting background process LSP1 2020-02-09T23:37:11.873051+08:00 LSP1 started with pid=73, OS id=14043 2020-02-09T23:37:11.875570+08:00 LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created 2020-02-09T23:37:11.882847+08:00 LOGSTDBY: Disabling database guard. 2020-02-09T23:37:11.929457+08:00 TT03 (PID:14045): Switchover in progress, stop clearing SRLs 2020-02-09T23:37:11.933903+08:00 LOGSTDBY: (LSP1) Archiving online logs as a primary database 2020-02-09T23:37:12.077504+08:00 Thread 1 advanced to log sequence 37 (LGWR switch) Current log# 1 seq# 37 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo01.log 2020-02-09T23:37:12.095439+08:00 LOGSTDBY: Database guard disabled. User transactions are now permitted. 2020-02-09T23:37:12.108372+08:00 LSP1 (PID:14043): LGWR is scheduled to archive to LAD:2 after log switch 2020-02-09T23:37:12.110050+08:00 LOGSTDBY: enabling scheduler job queue processes. 2020-02-09T23:37:12.110136+08:00 JOBQ: re-enabling CJQ0 2020-02-09T23:37:12.130456+08:00 LSP1 (PID:14043): Error 12154 received logging on to the standby 2020-02-09T23:37:12.130655+08:00 Errors in file /u01/app/oracle/diag/rdbms/hkp_dg/HKP_DG/trace/HKP_DG_lsp1_14043.trc: ORA-12154: TNS:could not resolve the connect identifier specified LSP1 (PID:14043): Error 12154 Creating archive log file to 'HKP' 2020-02-09T23:37:12.181126+08:00 LSP1 (PID:14043): Archived Log entry 63 added for T-1.S-36 ID 0xc24e1bb5 LAD:1 LOGSTDBY: (LSP1) Starting Full LogMiner Dictionary Build Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
45. Flashback database to restore point
++ Before we can convert to physical standby, we need to flashback database to guaranteed restore point SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION --------- -------------------- ------------------------------ ---------------- ----------------- HKP READ WRITE HKP LOGICAL STANDBY 12.2.0.1.0 <---- SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 09-FEB-20 07.27.58.000000000 PM SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE; FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE * ERROR at line 1: ORA-38757: Database must be mounted and not open to FLASHBACK. SQL> SHUT IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 1560281088 bytes Fixed Size 8621088 bytes Variable Size 1325401056 bytes Database Buffers 218103808 bytes Redo Buffers 8155136 bytes Database mounted. SQL> SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION -------------------- -------------------- ------------------------------ ---------------- ----------------- HKP MOUNTED HKP LOGICAL STANDBY 12.2.0.1.0 SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE; Flashback complete. <----- SQL> SHUT IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>
46. Add /etc/oratab entry – 19c
[oracle@rac1 ~]$ cat /etc/oratab | grep -i "HKP" #HKP:/u01/app/oracle/product/12.2.0/dbhome_1:N HKP:/u01/app/oracle/product/19.0.0/dbhome_1:N [oracle@rac1 ~]$
47. Copy password file / initialization file to 19c home/dbs
[oracle@rac1 dbs]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs [oracle@rac1 dbs]$ ls -ltr *HKP* -rw-r-----. 1 oracle oinstall 24 Feb 8 14:32 lkHKP -rw-r-----. 1 oracle oinstall 3584 Feb 8 14:35 orapwHKP -rw-r-----. 1 oracle oinstall 12288 Feb 9 17:17 dr2HKP.dat -rw-r-----. 1 oracle oinstall 12288 Feb 9 17:22 dr1HKP.dat -rw-r-----. 1 oracle oinstall 10829824 Feb 9 19:24 snapcf_HKP.f -rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 spfileHKP.ora -rw-rw----. 1 oracle oinstall 1544 Feb 10 00:08 hc_HKP.dat [oracle@rac1 dbs]$ cp -p spfileHKP.ora orapwHKP /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*HKP* -rw-r-----. 1 oracle oinstall 3584 Feb 8 14:35 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwHKP -rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileHKP.ora [oracle@rac1 dbs]$
OLD PRIMARY (HKP)
[oracle@rac1 ~]$ ps -ef | grep tns root 15 2 0 Feb09 ? 00:00:00 [netns] oracle 6768 1 0 Feb09 ? 00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit oracle 12048 6265 0 00:21 pts/0 00:00:00 grep --color=auto tns [oracle@rac1 ~]$ [oracle@rac1 ~]$ lsnrctl stop LISTENER_HKP LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-FEB-2020 00:21:57 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622))) The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ ps -ef | grep tns root 15 2 0 Feb09 ? 00:00:00 [netns] oracle 12161 6265 0 00:22 pts/0 00:00:00 grep --color=auto tns [oracle@rac1 ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora LISTENER_HKP = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) HKP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP) ) ) HKP_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG) ) ) cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora LISTENER_HKP = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) ) ) SID_LIST_LISTENER_HKP = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = HKP) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = HKP) ) ) [oracle@rac1 ~]$ . oraenv ORACLE_SID = [HKP_DG] ? HKP The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac1 ~]$ lsnrctl start LISTENER_HKP LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 00:28:34 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622))) STATUS of the LISTENER ------------------------ Alias LISTENER_HKP Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 10-FEB-2020 00:28:34 Uptime 0 days 0 hr. 0 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622))) Services Summary... Service "HKP" has 1 instance(s). Instance "HKP", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 ~]$ [oracle@rac1 ~]$ ps -ef | grep tns root 15 2 0 Feb09 ? 00:00:00 [netns] oracle 13439 1 0 00:28 ? 00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit oracle 13498 6265 0 00:28 pts/0 00:00:00 grep --color=auto tns [oracle@rac1 ~]$
NEW PRIMARY (HKP_DG)
++ We have upgraded DB using DBUA, so all TNS entries, password file, spfile will copied automatically to 19c diretories. ++ LISTENER will start by DBUA automatically [oracle@rac2 admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin [oracle@rac2 admin]$ [oracle@rac2 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. HKP_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG) ) ) LISTENER_HKP_DG = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) HKP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP) ) ) [oracle@rac2 admin]$ [oracle@rac2 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER_HKP_DG = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1622)) ) ) [oracle@rac2 admin]$ ps -ef | grep tns root 15 2 0 Feb09 ? 00:00:00 [netns] oracle 24573 10539 0 00:32 pts/1 00:00:00 grep --color=auto tns oracle 27865 1 0 Feb09 ? 00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP_DG -inherit [oracle@rac2 admin]$
49. Mount Database (OLD_PRIMARY) using 19c Oracle software
[oracle@rac1 ~]$ . oraenv ORACLE_SID = [HKP] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac1 ~]$ which sqlplus /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 00:38:28 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1207959552 bytes Database Buffers 335544320 bytes Redo Buffers 7876608 bytes Database mounted. SQL>
ON OLD PRIMARY
[oracle@rac1 ~]$ tnsping HKP TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:16:54 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP))) OK (10 msec) [oracle@rac1 ~]$ [oracle@rac1 ~]$ tnsping HKP_DG TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:17:01 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG))) OK (10 msec) [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus sys@HKP as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:17 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus sys@HKP_DG as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:34 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 [oracle@rac1 ~]$
ON NEW PRIMARY
[oracle@rac2 ~]$ tnsping HKP TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:29 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP))) OK (0 msec) [oracle@rac2 ~]$ [oracle@rac2 ~]$ tnsping HKP_DG TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:34 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG))) OK (10 msec) [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus sys@HKP as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:18:45 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus sys@HKP_DG as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:19:10 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL>
51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- ---------- --------------- ---------------- ----------------- ------------ HKP MOUNTED HKP LOGICAL STANDBY 19.0.0.0.0 rac1 <-- SQL> SQL> alter database convert to physical standby; Database altered. SQL> SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ----------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 SQL> SQL> SHUTDOWN IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> STARTUP NOMOUNT; ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1207959552 bytes Database Buffers 335544320 bytes Redo Buffers 7876608 bytes SQL> SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered. SQL>
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>
ON NEW PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- --------------------- HKP READ WRITE HKP_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com <---- SQL> SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 39 39 0 1 39 39 0 SQL>
ON OLD PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ---------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 <---- SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 39 39 0 <---- SQL>
54. Disable Flashback Database
On OLD PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ---------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 SQL> SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ YES <---- SQL> ALTER DATABASE FLASHBACK OFF; Database altered. SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ RESTORE POINT ONLY SQL>
ON NEW PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- --------------------- HKP READ WRITE HKP_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com SQL> SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ YES <----- SQL> ALTER DATABASE FLASHBACK OFF; Database altered. SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ RESTORE POINT ONLY <----- SQL>
On OLD PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ---------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 SQL> SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ RESTORE POINT ONLY <---- SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 09-FEB-20 07.27.58.000000000 PM SQL> SQL> DROP RESTORE POINT PRE_UPGRADE; Restore point dropped. SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ NO <---- SQL>
On NEW PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- --------------------- HKP READ WRITE HKP_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com SQL> SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ RESTORE POINT ONLY <---- SQL> SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ BEFORE_UPGRADE YES 09-FEB-20 08.49.22.000000000 PM SQL> DROP RESTORE POINT BEFORE_UPGRADE; Restore point dropped. SQL> SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected <--- SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ NO <----- SQL>
56. Update COMPATIBLE parameter on both primary/standby
Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.
If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
On NEW Primary
SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.2.0 <---- noncdb_compatible boolean FALSE SQL> SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE; System altered. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup; ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1325400064 bytes Database Buffers 218103808 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 19.0.0 <---- noncdb_compatible boolean FALSE SQL> SQL>
ON OLD PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ---------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 SQL> SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.2.0 <---- noncdb_compatible boolean FALSE SQL> SQL> SHUT IMMEDIATE; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> STARTUP NOMOUNT; ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1224736768 bytes Database Buffers 318767104 bytes Redo Buffers 7876608 bytes SQL> SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> / ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> SQL> SHOW PARAMETER COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 19.0.0 <---- noncdb_compatible boolean FALSE SQL>
On New Primary
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 44 44 0 1 44 44 0 SQL>
On Standby (Old Primary)
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 44 44 0 <---- SQL>
58. Revert back parameter values on both primary/standby (In case if you changed)
job_queue_processes
aq_tm_processes
dbwr_io_slaves
Congratulations !!! Rolling upgrade completed successfully from 12.2.0.1 to 19c.
Below Steps are Optional. Put the Primary back in place.
59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- --------------------- HKP READ WRITE HKP_DG PRIMARY 19.0.0.0.0 rac2.rajasekhar.com SQL> SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY <---- SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. SQL> shut immediate; ORA-01012: not logged on SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 02:06:57 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1325400064 bytes Database Buffers 218103808 bytes Redo Buffers 7876608 bytes SQL> SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> / alter database recover managed standby database disconnect from session * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- -------------------- HKP MOUNTED HKP_DG PHYSICAL STANDBY 19.0.0.0.0 rac2.rajasekhar.com <---- SQL>
60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- --------------- ---------------- ----------------- ---------- HKP MOUNTED HKP PHYSICAL STANDBY 19.0.0.0.0 rac1 SQL> SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY <---- SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown; Database altered. SQL> alter database open; Database altered. SQL> set lines 190 SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME ------ -------------------- -------------- ---------------- --------------- ---------- HKP READ WRITE HKP PRIMARY 19.0.0.0.0 rac1 <----- SQL>
61. Verify DBA_REGISTRY ON PRIMARY (HKP)
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME ------ -------------------- -------------- ---------------- --------------- ---------- HKP READ WRITE HKP PRIMARY 19.0.0.0.0 rac1 <-- SQL> SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- -------------------------------------------- CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID XML Oracle XDK 19.0.0.0.0 VALID CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID APS OLAP Analytic Workspace 19.0.0.0.0 VALID RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF XDB Oracle XML Database 19.0.0.0.0 VALID OWM Oracle Workspace Manager 19.0.0.0.0 VALID CONTEXT Oracle Text 19.0.0.0.0 VALID ORDIM Oracle Multimedia 19.0.0.0.0 VALID SDO Spatial 19.0.0.0.0 VALID XOQ Oracle OLAP API 19.0.0.0.0 VALID OLS Oracle Label Security 19.0.0.0.0 VALID DV Oracle Database Vault 19.0.0.0.0 VALID 15 rows selected. SQL>
On PRIMARY
SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE <--- SQL> SQL> alter system set dg_broker_start=true; System altered. SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE <---- SQL> SQL> show parameter dg_broker_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr1HKP.dat dg_broker_config_file2 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr2HKP.dat SQL>
On STANDBY
SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE <---- SQL> SQL> alter system set dg_broker_start=true; System altered. SQL> show parameter DG_BROKER_START NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE <---- SQL> SQL> show parameter dg_broker_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr1HKP_DG.dat dg_broker_config_file2 string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/dr2HKP_DG.dat SQL>
On PRIMARY
[oracle@rac1 ~]$ which dgmgrl /u01/app/oracle/product/19.0.0/dbhome_1/bin/dgmgrl [oracle@rac1 ~]$ [oracle@rac1 ~]$ dgmgrl DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Feb 10 02:27:59 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@HKP AS SYSDBA Password: Connected to "HKP" Connected as SYSDBA. DGMGRL> DGMGRL> show configuration; ORA-16532: Oracle Data Guard broker configuration does not exist Configuration details cannot be determined by DGMGRL DGMGRL> DGMGRL> create configuration 'HKP' as primary database is 'HKP' connect identifier is HKP; Configuration "HKP" created with primary database "HKP" DGMGRL> DGMGRL> show configuration; Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database <---- Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical; Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set Failed. <----- DGMGRL>
On Standby
SQL> set lines 190 SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME --------- -------------------- -------------- ---------------- ----------------- --------------------- HKP MOUNTED HKP_DG PHYSICAL STANDBY 19.0.0.0.0 rac2.rajasekhar.com SQL> SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; <---- System altered. SQL>
On Primary
DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical; Database "HKP_DG" added DGMGRL> DGMGRL> show configuration; Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database HKP_DG - Physical standby database <---- Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> DGMGRL> enable configuration; Enabled. DGMGRL> DGMGRL> show configuration; Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database HKP_DG - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 4 seconds ago) <---- DGMGRL>
DGMGRL> show configuration lag Configuration - HKP Protection Mode: MaxPerformance Members: HKP - Primary database HKP_DG - Physical standby database Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) <--- Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 24 seconds ago) DGMGRL> show database HKP_DG; Database - HKP_DG Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) <---- Average Apply Rate: 9.00 KByte/s Real Time Query: OFF Instance(s): HKP_DG Database Status: SUCCESS DGMGRL>
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE VERSION HOST_NAME ------ -------------------- -------------- ---------------- --------------- ---------- HKP READ WRITE HKP PRIMARY 19.0.0.0.0 rac1 SQL> SELECT * FROM SUGI.TEJA; NAME ROLE ---------- ---------- RAJ DBA X DBA SUGI DBA <----- SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Reference:
http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_60.shtml