DBA Hub

📋Steps in this guide1/69

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

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

oracle Oracle 19cupgradeintermediate
by OracleDba
20 views
1

Overview

Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby Below Steps are Optional 0. Overview

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
++ 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.
2

Section 2

++ 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). Prerequisites: ++ What will happen to standby database if primary database opened with resetlogs ??? 1. Environment On Primary
3

Section 3

On Primary RAC1.RAJASEKHAR.COM On STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

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
4

Section 4

Hostname : RAC2.RAJASEKHAR.COM PRE-UPGRADE TASKS 2. Disable DG Broker On Primary
5

Section 5

On Primary ALTER SYSTEM SET DG_BROKER_START=FALSE; On STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
[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>

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

Section 6

3. Install 19c database software on primary 4. Install 19c database software on standby

Code/Command (click line numbers to comment):

1
2
3
4
5
Below is sample doc.
http://www.br8dba.com/install-19c/

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

Section 7

5. Apply latest Release Update on 19c standby 6. Apply latest Release Update on 19c primary 7. Database Backup

Code/Command (click line numbers to comment):

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

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

Section 8

7. Database Backup 8. Verify INVALID OBJECTS 9. Verify Protection mode

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>
9

Section 9

9. Verify Protection mode On Primary On STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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>
10

Section 10

10. Verify fast_recovery_area size On PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>

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 ~]$
11

Section 11

On STANDBY 11. Verify archive log dest size On PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 ~]$
12

Section 12

On PRIMARY On STANDBY 12. Internal Schema Support

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[oracle@rac1 ~]$
df -h /u01/app/archive/HKP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   36G
32G
54% /u01
<----
[oracle@rac1 ~]$

[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 ~]$
13

Section 13

12. Internal Schema Support ++ Below schemas are automatically skipped by SQL Apply. 13. Find list of objects are not supported

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
++ 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>

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

Section 14

14. Check the reason for unsupported objects 15. Find list of objects are not supported

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select column_name,data_type from dba_logstdby_unsupported where owner='&OWNER' AND TABLE_NAME='&TABLE_NAME';

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

Section 15

Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key 16. Refresh MVs 17. Gather DICTIONARY STATS

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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>
16

Section 16

18. Create Flashback Guaranteed Restore Point (On Primary) PRE_UPGRADE YES 09-FEB-20 07.27.58.000000000 PM 19. Enable Flashback Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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>

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

Section 17

19. Enable Flashback Database On PRIMARY On STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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>
18

Section 18

20. Verify GAP On PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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>

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

Section 19

On STANDBY CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY 21. Cancel MRP (Standby)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>
20

Section 20

21. Cancel MRP (Standby) 22. Build the logminer dictionary (Primary) 23. Convert Physical to Logical Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>

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

Section 21

23. Convert Physical to Logical Standby ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY; Filename: alert_HKP_DG.log RESETLOGS after incomplete recovery UNTIL CHANGE 1766613 time 02/09/2020 19:47:32 Resetting resetlogs activation ID 3259734620 (0xc24b9a5c) Standby became primary SCN: 1766611

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
22

Section 22

Setting recovery target incarnation to 3 <------- 24. Verify DATABASE_ROLE LOGICAL STANDBY 25. Open Logical Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
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>
23

Section 23

25. Open Logical Database READ WRITE 26. Start SQL Apply

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
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
24

Section 24

lsp0 Filename: alert_HKP_DG.log 27. Verify GAP On Primary
25

Section 25

CONN SUGI/SUGI; X DBA <----- On Logical STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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>

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

Section 26

X DBA <----- ALL - All users other than SYS are prevented from making changes to any data in the database. UPGRADE LOGICAL STANDBY 28. Run pre-upgrade script
27

Section 27

rac2 29. View Pre-upgrade log 30. Verify tablespace sizes for upgrade

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
[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 ~]$
28

Section 28

30. Verify tablespace sizes for upgrade 31. Run preupgrade_fixups.sql 32. Create GUARANTEED Restore Point (Logical STANDBY)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
** 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>
29

Section 29

32. Create GUARANTEED Restore Point (Logical STANDBY) 33. Run DBUA from 19c ORACLE HOME /u01/app/oracle/product /19.0.0/ dbhome_1/bin/dbua
Step 29

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>

[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.
30

Section 30

POST-UPGRADE TASKS WHEN DBUA USING 34. Verify /etc/oratab 35. Verify Timezone version

Code/Command (click line numbers to comment):

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

Section 31

35. Verify Timezone version 36. Verify INVALID objects 37. Verify DBA_REGISTRY (HKP_DG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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>
32

Section 32

37. Verify DBA_REGISTRY (HKP_DG) 38. Start SQL Apply ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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
33

Section 33

Filename: alert_HKP_DG.log 39. Verify GAP Test case: On PRIMARY
34

Section 34

On PRIMARY On STANDBY SWITCHOVER PRIMARY DATABASE (HKP) TO LOGICAL STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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>

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

Section 35

SWITCHOVER PRIMARY DATABASE (HKP) TO LOGICAL STANDBY 40. Pre-Switchover tasks *** Verify that there is network connectivity between the primary and standby locations. *** Always recommened test the switchover in your testing system befre working on production. *** Verify primary database READ WRITE and standby instance mounted. ***  Verify there are no active users connected to database. *** Make sure last redo data transmitted from primary to standby and applied. *** Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR alter system set job_queue_processes=0 ; alter system set aq_tm_processes = 0 ; alter system set dbwr_io_slaves = 0 ; On Primary
36

Section 36

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 41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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.

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

Section 37

41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; 42. Verify alert_HKP.log

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>
38

Section 38

Filename: alert_HKP.log ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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
39

Section 39

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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>
40

Section 40

Filename: alert_HKP_DG.log ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (HKP_DG) LOGSTDBY: (LSP1) Starting Full LogMiner Dictionary Build Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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
41

Section 41

45. Flashback database to restore point ++ Before we can convert to physical standby, we need to flashback database to guaranteed restore point FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE * ERROR at line 1: ORA-38757: Database must be mounted and not open to FLASHBACK. FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
++ 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>
42

Section 42

46. Add /etc/oratab entry – 19c HKP:/u01/app/oracle/product/19.0.0/dbhome_1:N 47. Copy password file / initialization file to 19c home/dbs

Code/Command (click line numbers to comment):

1
2
3
4
5
6
[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 ~]$
43

Section 43

cp -p spfileHKP.ora orapwHKP /u01/app/oracle/product/19.0.0/dbhome_1/dbs 48. Configure TNS Entries OLD PRIMARY (HKP)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[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]$
44

Section 44

cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora lsnrctl start LISTENER_HKP NEW PRIMARY (HKP_DG)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
[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 ~]$
45

Section 45

/u01/app/oracle/product/ 19.0.0 /dbhome_1/bin/tnslsnr LISTENER_HKP_DG 49. Mount Database (OLD_PRIMARY) using 19c Oracle software SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 00:38:28 2020 Version 19.4.0.0.0

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
++ 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]$

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

Section 46

50. Verify TNS Connectivity ON OLD PRIMARY ON NEW PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[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 ~]$
47

Section 47

ON NEW PRIMARY 51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY LOGICAL STANDBY 19.0.0.0.0 rac1 <--

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
[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>

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

Section 48

alter database convert to physical standby; PHYSICAL STANDBY ALTER DATABASE MOUNT STANDBY DATABASE; 52. Enable MRP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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> s
elect process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';
PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG         40          1 <----
SQL>
49

Section 49

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 53. Verify GAP ON NEW PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>
50

Section 50

ON OLD PRIMARY 54. Disable Flashback Database On OLD PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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>
51

Section 51

On OLD PRIMARY ALTER DATABASE FLASHBACK OFF; ON NEW PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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>

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

Section 52

ALTER DATABASE FLASHBACK OFF; 55. Drop Restore point On OLD PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>
53

Section 53

DROP RESTORE POINT PRE_UPGRADE; On NEW PRIMARY DROP RESTORE POINT BEFORE_UPGRADE; 56. Update COMPATIBLE parameter on both primary/standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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>
54

Section 54

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
55

Section 55

On NEW Primary ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE; 19.0.0 <---- ON OLD PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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>
56

Section 56

SHUT IMMEDIATE; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; / ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01153: an incompatible media recovery is active 57. Verify GAP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
SQL>
select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;
NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP
PHYSICAL STANDBY
19.0.0.0.0        rac1

SQL>

SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

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

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


Database dismounted.
ORACLE instance shut down.
SQL>

SQL>
STARTUP NOMOUNT;
ORACLE instance started.

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

SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL>
/
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL>
SQL>
SHOW PARAMETER COMPATIBLE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
57

Section 57

57. Verify GAP On New Primary On Standby (Old Primary)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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>
58

Section 58

58. Revert back parameter values on both primary/standby (In case if you changed) Congratulations !!! Rolling upgrade completed successfully from 12.2.0.1 to 19c.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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>

job_queue_processes
aq_tm_processes
dbwr_io_slaves
59

Section 59

Below Steps are Optional. Put the Primary back in place. 59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
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

Section 60

alter database commit to switchover to physical standby with session shutdown; shut immediate; alter database mount standby database; alter database recover managed standby database disconnect from session; / alter database recover managed standby database disconnect from session * ERROR at line 1: ORA-01153: an incompatible media recovery is active 60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY
61

Section 61

60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY HKP alter database recover managed standby database cancel; alter database commit to switchover to primary with session shutdown; PRIMARY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>
62

Section 62

61. Verify DBA_REGISTRY ON PRIMARY (HKP) select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; 62. Configure DG Broker

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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>
63

Section 63

On PRIMARY On STANDBY

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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>

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

Section 64

On PRIMARY create configuration 'HKP' as primary database is 'HKP' connect identifier is HKP; Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set On Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[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>
65

Section 65

On Standby ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; <---- On Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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>

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

Section 66

add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical; HKP_DG - Physical standby database <---- enable configuration; 63. Verify GAP

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>
67

Section 67

show configuration lag Apply Lag: 0 seconds (computed 1 second ago) <--- show database HKP_DG; Apply Lag: 0 seconds (computed 1 second ago) <---- 64. Verify Table
68

Section 68

SELECT * FROM SUGI.TEJA; SUGI DBA <----- 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
69

Section 69

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/using-sql-apply-to-perform-rolling-upgrade.html#GUID-C5DF6148-C1E9-4ADF-A975-AC95FC64E0C4 http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_60.shtml

Comments (0)

Please to add comments

No comments yet. Be the first to comment!