Category Archives: 19C

Rolling Upgrade With an Existing Physical Standby Database

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

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. Environment

PRE-UPGRADE TASKS

2. Disable DG Broker
3. Install 19c database software on primary
4. Install 19c database software on standby
5. Apply latest Release Update on 19c standby
6. Apply latest Release Update on 19c primary
7. Database Backup
8. Verify INVLAID OBJECTS
9. Verify protection mode
10. Verify fast_recovery_area size
11. Verify archive log dest size
12. Internal Schema Support
13. Find list of objects are not supported
14. Check the reason for unsupported objects
15. Find list of objects are not supported
16. Refresh MVs
17. Gather DICTIONARY STATS
18. Enable Flashback Database
19. Create Flashback Guaranteed Restore Point (On Primary)
20. Verify GAP

CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY

21. Cancel MRP (Standby)
22. Build the logminer dictionary (Primary)
23. Convert Physical to Logical Standby
24. Verify DATABASE_ROLE
25. Open Logical Database
26. Start SQL Apply
27. Verify GAP

UPGRADE LOGICAL STANDBY

28. Run pre-upgrade script
29. View Pre-upgrade log
30. Verify tablespace sizes for upgrade
31. Run preupgrade_fixups.sql
32. Create GUARANTEED Restore Point (Logical STANDBY)
33. Run DBUA from 19c ORACLE HOME

POST-UPGRADE TASKS WHEN DBUA USING

34. Verify /etc/oratab
35. Verify Timezone version
36. Verify INVALID objects
37. Verify DBA_REGISTRY (HKP_DG)
38. Start SQL Apply
39. Verify GAP

CONVERT PRIMARY(HKP) TO LOGICAL STANDBY

40. Pre-Switchover tasks
41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY
42. Verify alert_HKP.log

SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY

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

CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY

45. Flashback database to restore point
46. Add /etc/oratab entry – 19c
47. Copy password file / initialization file to 19c home/dbs
48. Configure TNS Entries
49. Mount Database using 19c Oracle software
50. Verify TNS Connectivity
51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY
52. Enable MRP
53. Verify GAP
54. Disable Flashback Database
55. Drop Restore point
56. Update COMPATIBLE parameter on both primary/standby
57. Verify GAP
58. Revert back parameter values on both primary/standby (In case if you changed)

Below Steps are Optional

59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY
60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY
61. Verify DBA_REGISTRY
62. Configure DG Broker
63. Verify GAP
64. Verify Table
__________________________________________________________________________________________________


0. Overview

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


1. Environment

On Primary

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: HKP
DB VERSION		: 12.2.0.1
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/HKP

Target DB VERSION 	: 19c
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

On STANDBY

Hostname       		: RAC2.RAJASEKHAR.COM
STANDBY Database Name  	: HKP_DG
DB VERSION		: 12.2.0.1
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/HKP_DG

Target DB VERSION 	: 19c
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

Upgrade Method		: Rolling Upgrade using Transient Logical Standby
                          Database Upgrade Assistant

Database Protection mode: Maximum Performance


PRE-UPGRADE TASKS


2. Disable DG Broker

On Primary

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sun Feb 9 16:58:00 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@HKP
Password:
Connected to "HKP"
Connected as SYSDBA.
DGMGRL> 
DGMGRL>  show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 35 seconds ago)

DGMGRL>
DGMGRL> DISABLE CONFIGURATION; <----
Disabled.
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED <----

DGMGRL>

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE
SQL>
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       MOUNTED              PHYSICAL STANDBY

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <---
SQL>


3. Install 19c database software on primary

Below is sample doc.
http://www.br8dba.com/install-19c/


4. Install 19c database software on standby

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


5. Apply latest Release Update on 19c standby

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


6. Apply latest Release Update on 19c primary

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


7. Database Backup

RMAN > connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s';
release channel c1;
}
exit;

sample doc.
http://www.br8dba.com/backup-based-rman-duplicate-database/#3


8. Verify INVALID OBJECTS

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

  COUNT(*)
----------
         0 <-----

SQL>


9. Verify Protection mode

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       READ WRITE           PRIMARY          MAXIMUM PERFORMANCE <---

SQL>

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>


10. Verify fast_recovery_area size

On PRIMARY

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G <----
SQL>

[oracle@rac1 ~]$ df -h /u01/app/oracle/fast_recovery_area
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   36G   32G  54% /u01
[oracle@rac1 ~]$

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
HKP       MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G <----
SQL>

[oracle@rac2 ~]$ df -h /u01/app/oracle/fast_recovery_area
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        72G   59G   14G  82% /u01
[oracle@rac2 ~]$


11. Verify archive log dest size

On PRIMARY

[oracle@rac1 ~]$ df -h /u01/app/archive/HKP
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   36G   32G  54% /u01 <----
[oracle@rac1 ~]$

On STANDBY

[oracle@rac2 ~]$ df -h /u01/app/archive/HKP_DG
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        72G   59G   14G  82% /u01 <----
[oracle@rac2 ~]$


12. Internal Schema Support

++ Below schemas are automatically skipped by SQL Apply. 

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY <----

SQL>
SQL> set pages 999
SQL> select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA' order by owner;

OWNER
--------------------------------------------------------------------------------
ANONYMOUS
APPQOSSYS
AUDSYS
CTXSYS
DBSFWUSER
DBSNMP
DIP
DVF
DVSYS
GGSYS
GSMADMIN_INTERNAL
GSMCATUSER
GSMUSER
LBACSYS
MDSYS
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
REMOTE_SCHEDULER_AGENT
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SYS
SYS$UMF
SYSBACKUP
SYSDG
SYSKM
SYSRAC
SYSTEM
WMSYS
XDB
XS$NULL

35 rows selected.

SQL> 


13. Find list of objects are not supported

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

no rows selected

SQL>


14. Check the reason for unsupported objects

select column_name,data_type from dba_logstdby_unsupported where owner='&OWNER' AND TABLE_NAME='&TABLE_NAME';


15. Find list of objects are not supported

SQL> COL OWNER FOR A10
SQL> COL TABLE_NAME FOR A10
SQL> SET LINES 190
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

OWNER      TABLE_NAME B
---------- ---------- -
SUGI       TEJA       N

SQL>

SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name;

OWNER      TABLE_NAME
---------- ----------
SUGI       TEJA

SQL>

Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key

SQL> ALTER TABLE SUGI.TEJA ADD CONSTRAINT PK_NAME PRIMARY KEY (NAME);

Table altered.

SQL> select owner, table_name from dba_logstdby_not_unique where (owner, table_name) not in (select distinct owner, table_name from dba_logstdby_unsupported) order by owner, table_name;

no rows selected  <----

SQL>


16. Refresh MVs

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>


17. Gather DICTIONARY STATS

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL>

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


18. Create Flashback Guaranteed Restore Point (On Primary)

++ The user must have the SYSDBA system privileges
++ Must have created a flash recovery area
++ The database must be in ARCHIVELOG mode
++ Guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter i.e. it is always available when you have enough space in the flash recovery area.
++ NO need to enable Flashback Database from 11.2.0.1 on wards
++ MUST NOT change the compatible parameter to higher version

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY <---

SQL> select * from V$restore_point;

no rows selected <---

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL>


19. Enable Flashback Database

On PRIMARY

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       READ WRITE           PRIMARY

SQL>

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES <----

SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
HKP       MOUNTED              PHYSICAL STANDBY <-----

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES <----

SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>


20. Verify GAP

On PRIMARY

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL>
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     23                    23          0
         1                     23                    23          0

SQL>

On STANDBY

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL>
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     23                    23          0 <---

SQL>


CONVERT PHYSICAL STANDBY TO LOGICAL STANDBY


21. Cancel MRP (Standby)

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL>

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG         27          1

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

no rows selected <----

SQL>


22. Build the logminer dictionary (Primary)

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL> set serveroutput on
SQL> execute dbms_logstdby.build;

PL/SQL procedure successfully completed. <----

SQL> SELECT * FROM V$LOGSTDBY_STATE WHERE STATE='LOADING DICTIONARY';

no rows selected

SQL>


23. Convert Physical to Logical Standby

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         PHYSICAL STANDBY

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.

SQL>

Filename: alert_HKP_DG.log

2020-02-09T19:50:37.383801+08:00
ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
2020-02-09T19:50:37.384464+08:00
Media Recovery Start: Managed Standby Recovery (HKP_DG)
2020-02-09T19:50:37.386900+08:00
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
2020-02-09T19:50:37.582360+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_27_1031841246.dbf
2020-02-09T19:50:37.841765+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_28_1031841246.dbf
Resize operation completed for file# 1, old size 829440K, new size 839680K
2020-02-09T19:50:38.661868+08:00
Resize operation completed for file# 1, old size 839680K, new size 849920K
2020-02-09T19:50:39.187191+08:00
Media Recovery Log /u01/app/archive/HKP_DG/1_29_1031841246.dbf
2020-02-09T19:50:39.298950+08:00
Incomplete Recovery applied until change 1766613 time 02/09/2020 19:47:32
2020-02-09T19:50:39.327325+08:00
Media Recovery Complete (HKP_DG)
Killing 3 processes (PIDS:20446,19128,7053) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 18995
2020-02-09T19:50:41.382297+08:00
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1766613 time 02/09/2020 19:47:32
Resetting resetlogs activation ID 3259734620 (0xc24b9a5c)
Online log /u01/app/oracle/oradata/HKP_DG/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/HKP_DG/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/HKP_DG/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1766611
2020-02-09T19:50:41.551507+08:00
Setting recovery target incarnation to 3
2020-02-09T19:50:41.668431+08:00
Network throttle feature is disabled as mount time

RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY


24. Verify DATABASE_ROLE

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  MOUNTED              HKP_DG                         LOGICAL STANDBY <-----

SQL>


25. Open Logical Database

SQL> ALTER DATABASE OPEN;

Database altered.

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL>


26. Start SQL Apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

SQL> !ps -ef | grep lsp
oracle   28600     1  1 19:55 ?        00:00:00 ora_lsp0_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


27. Verify GAP

On Primary

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

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
-------------------- -------------------- ------------------------------ ----------------
HKP                  READ WRITE           HKP                            PRIMARY

SQL>
SQL> CONN SUGI/SUGI;
Connected.
SQL> INSERT INTO TEJA VALUES ('X','DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEJA;

NAME                 ROLE
-------------------- ----------
RAJ                  DBA
X                    DBA <-----

SQL> 

On Logical STANDBY

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

NAME       OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
---------- -------------------- ------------------------------ ----------------
HKP        READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL>


SQL> SELECT * FROM SUGI.TEJA;

NAME                                               ROLE
-------------------------------------------------- ----------
RAJ                                                DBA
X                                                  DBA <-----

SQL>

SQL> conn SUGI/SUGI;
Connected.
SQL> INSERT INTO TEJA VALUES ('Y','DBA');
INSERT INTO TEJA VALUES ('Y','DBA')
            *
ERROR at line 1:
ORA-16224: Database Guard is enabled

SQL> CONN / AS SYSDBA
Connected.
SQL> select guard_status from v$database;

GUARD_S
-------
ALL <-----

SQL>

The guard_status column protects the data from being changed.  There are three values:

ALL - All users other than SYS are prevented from making changes to any data in the database.

STANDBY - All users other than SYS are prevented from making changes to any database object being maintained by logical standby.

NONE - Indicates normal security for all data in the database.


UPGRADE LOGICAL STANDBY


28. Run pre-upgrade script

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


29. View Pre-upgrade log

[oracle@rac2 ~]$ cat /home/oracle/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-02-09T20:20:44

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  HKP
     Container Name:  HKP
       Container ID:  0
            Version:  12.2.0.1.0
     DB Patch Level:  No Patch Bundle applied
         Compatible:  12.2.0
          Blocksize:  8192
           Platform:  Linux x86 64-bit
      Timezone File:  26
  Database log mode:  ARCHIVELOG
           Readonly:  FALSE
            Edition:  EE

  Oracle Component                       Upgrade Action    Current Status
  ----------------                       --------------    --------------
  Oracle Server                          [to be upgraded]  VALID
  JServer JAVA Virtual Machine           [to be upgraded]  VALID
  Oracle XDK for Java                    [to be upgraded]  VALID
  Real Application Clusters              [to be upgraded]  OPTION OFF
  Oracle Workspace Manager               [to be upgraded]  VALID
  OLAP Analytic Workspace                [to be upgraded]  VALID
  Oracle Label Security                  [to be upgraded]  VALID
  Oracle Database Vault                  [to be upgraded]  VALID
  Oracle Text                            [to be upgraded]  VALID
  Oracle XML Database                    [to be upgraded]  VALID
  Oracle Java Packages                   [to be upgraded]  VALID
  Oracle Multimedia                      [to be upgraded]  VALID
  Oracle Spatial                         [to be upgraded]  VALID
  Oracle OLAP API                        [to be upgraded]  VALID

==============
BEFORE UPGRADE
==============

  REQUIRED ACTIONS
  ================
  None

  INFORMATION ONLY
  ================
  1.  To help you keep track of your tablespace allocations, the following
      AUTOEXTEND tablespaces are expected to successfully EXTEND during the
      upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ----------  -----------
      SYSAUX                             560 MB       576 MB
      SYSTEM                             830 MB       943 MB
      TEMP                                20 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  2.  Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
      4618 MB of archived logs.  Check alert log during the upgrade that there
      is no write error to the destination due to lack of disk space.

      Archiving cannot proceed if the archive log destination is full during
      upgrade.

      Archive Log Destination:
       Parameter    :  LOG_ARCHIVE_DEST_1
       Destination  :  /u01/app/archive/HKP_DG

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

  3.  Check the Oracle Backup and Recovery User's Guide for information on how
      to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

      It is good practice to have the catalog schema the same or higher version
      than the RMAN client version you are using.

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  4.  Upgrade the database time zone file using the DBMS_DST package.

      The database is using time zone file version 26 and the target 19 release
      ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file.  For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

  5.  (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
      command:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

  6.  Gather statistics on fixed objects after the upgrade and when there is a
      representative workload on the system using the command:

        EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

      This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans.  Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database HKP
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

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


[oracle@rac2 ~]$


30. Verify tablespace sizes for upgrade

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

http://www.br8dba.com/asm-2/#tspace

TABLESPACE_NAME                AUT FILE_NAME                                       TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
------------------------------ --- ----------------------------------------------- ----------- ---------- ---------- ----------
SYSAUX                         YES /u01/app/oracle/oradata/HKP_DG/sysaux01.dbf             560         32       5.68 31.9999847
SYSTEM                         YES /u01/app/oracle/oradata/HKP_DG/system01.dbf             830          3        .38 31.9999847
UNDOTBS1                       YES /u01/app/oracle/oradata/HKP_DG/undotbs01.dbf             70         37      53.04 31.9999847
USERS                          YES /u01/app/oracle/oradata/HKP_DG/users01.dbf                5          4       77.5 31.9999847
******************************                                                     ----------- ----------            ----------
sum                                                                                       1465         76            127.999939

SQL>


31. Run preupgrade_fixups.sql

++ It will run by DBUA, however we are executing manually before start upgrade.

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql
SQL> REM
SQL> REM    Oracle PRE-Upgrade Fixup Script
SQL> REM
SQL> REM    Auto-Generated by:       Oracle Preupgrade Script
SQL> REM                             Version: 19.0.0.0.0 Build: 1
SQL> REM    Generated on:            2020-02-09 20:20:41
SQL> REM
SQL> REM    Source Database:         HKP
SQL> REM    Source Database Version: 12.2.0.1.0
SQL> REM    For Upgrade to Version:     19.0.0.0.0
SQL> REM
SQL>
SQL> REM
SQL> REM    Setup Environment
SQL> REM
SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200;
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-02-09 20:20:41

For Source Database:     HKP
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    1.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    2.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
    3.  rman_recovery_version     NO          Informational only.
                                              Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade.  To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

SQL>


32. Create GUARANTEED Restore Point (Logical STANDBY)

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

SQL> CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created. <-----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
BEFORE_UPGRADE       YES        09-FEB-20 08.49.22.000000000 PM

SQL>


33. Run DBUA from 19c ORACLE HOME

[oracle@rac2 ~]$ export DISPLAY=192.168.2.2:0.0
[oracle@rac2 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbua























Logs directory:  /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-02-09_09-04-14PM
Database upgrade has been completed successfully, and the database is ready to use.


POST-UPGRADE TASKS WHEN DBUA USING


34. Verify /etc/oratab

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


35. Verify Timezone version

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0 <----

SQL>

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32 <----

SQL>


36. Verify INVALID objects

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

  COUNT(*)
----------
         0 <-----

SQL>


37. Verify DBA_REGISTRY (HKP_DG)

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0

SQL>
SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL>


38. Start SQL Apply

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
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


39. Verify GAP

Test case:

On PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0 <-----

SQL>

SQL> CONN SUGI/SUGI;
Connected.
SQL> SELECT * FROM TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA

SQL> INSERT INTO TEJA VALUES ('SUGI','DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <------

SQL>

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            31 <-----

SQL>

On STANDBY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- ---------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0 

SQL>

SQL> SELECT * FROM SUGI.TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <------

SQL>

SQL> col REALTIME_APPLY for a20
SQL> select * from v$logstdby_state;

PRIMARY_DBID PRIMARY_CON_DBID SESSION_ID REALTIME_APPLY       STATE          CON_ID
------------ ---------------- ---------- -------------------- ---------- ----------
  3259744860                0          1 Y                    IDLE                0

SQL>

SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED      BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
        28       1766211      1766603 09-FEB-20 YES           35440        512
        29       1766603      1766619 09-FEB-20 YES              28        512
        30       1766619      1767498 09-FEB-20 YES             483        512
        31       1767498      1787013 09-FEB-20 YES          123270        512

SQL>


CONVERT PRIMARY(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

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL> select * from dba_jobs_running;

no rows selected

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0
SQL>

Note: Block further job submission by setting the job_queue_processes parameter to 0 so that 
there would be no jobs running during switchover.

On Standby

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- ---------------
HKP       READ WRITE           HKP_DG                         LOGICAL STANDBY  19.0.0.0.0

SQL> 
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED      BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
        28       1766211      1766603 09-FEB-20 YES           35440        512
        29       1766603      1766619 09-FEB-20 YES              28        512
        30       1766619      1767498 09-FEB-20 YES             483        512
        31       1767498      1787013 09-FEB-20 YES          123270        512


SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL>
SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;

SYSDATE            APPLIED_TIME
------------------ ------------------
09-FEB-20 23:25:40 09-FEB-20 23:25:35

SQL>


41. SWITCHOVER PRIMARY DATABASE TO LOGICAL STANDBY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            PRIMARY          12.2.0.1.0

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY <----

SQL>

Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover using the below query

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            LOGICAL STANDBY  12.2.0.1.0 <----

SQL>


42. Verify alert_HKP.log

Filename: alert_HKP.log

2020-02-09T23:26:59.463554+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY
2020-02-09T23:26:59.463682+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (HKP)
2020-02-09T23:26:59.466379+08:00
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Archiving current online log files.
2020-02-09T23:26:59.588667+08:00
Thread 1 advanced to log sequence 33 (LGWR switch)
  Current log# 3 seq# 33 mem# 0: /u01/app/oracle/oradata/HKP/redo03.log
2020-02-09T23:27:00.228257+08:00
Archived Log entry 65 added for T-1.S-32 ID 0xc24b9a5c LAD:1
2020-02-09T23:27:00.277230+08:00
LOGSTDBY: Waiting for pending archivals to all destinations.
2020-02-09T23:27:00.320999+08:00
Waiting for all non-current ORLs to be archived
2020-02-09T23:27:00.321160+08:00
All non-current ORLs have been archived
2020-02-09T23:27:00.321286+08:00
Waiting for all FAL entries to be archived
2020-02-09T23:27:00.326657+08:00
All FAL entries have been archived
2020-02-09T23:27:00.326862+08:00
Waiting for potential Logical Standby switchover target to become synchronized
2020-02-09T23:27:01.287138+08:00
TT02: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
2020-02-09T23:27:02.328956+08:00
Active, synchronized Logical Standby switchover target has been identified
2020-02-09T23:27:02.329025+08:00
LOGSTDBY: Enabling database guard.
LOGSTDBY: Waiting for completion of transactions started at or before scn 1816838 (0x00000000001bb906)
LOGSTDBY: All transactions started at or before scn 1816838 (0x00000000001bb906) have completed
2020-02-09T23:27:02.462169+08:00
LOGSTDBY: Database guard enabled.  User transactions are no longer permitted.
2020-02-09T23:27:02.462242+08:00
LOGSTDBY: Waiting for pending archivals to all destinations.
2020-02-09T23:27:02.498949+08:00
Waiting for all non-current ORLs to be archived
2020-02-09T23:27:02.499038+08:00
All non-current ORLs have been archived
2020-02-09T23:27:02.503040+08:00
Waiting for all FAL entries to be archived
2020-02-09T23:27:02.503187+08:00
All FAL entries have been archived
2020-02-09T23:27:02.503283+08:00
Waiting for potential Logical Standby switchover target to become synchronized
2020-02-09T23:27:03.505105+08:00
Active, synchronized Logical Standby switchover target has been identified
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn 1816841 [0x00000000001bb909].
2020-02-09T23:27:03.995731+08:00
Thread 1 advanced to log sequence 34 (LGWR switch)
  Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log
2020-02-09T23:27:04.999145+08:00
ARCH: LGWR is scheduled to archive to LAD:2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
2020-02-09T23:27:05.350386+08:00
Archived Log entry 66 added for T-1.S-33 ID 0xc24b9a5c LAD:1
LOG_ARCHIVE_DEST_2 is a potential Logical Standby switchover target
2020-02-09T23:27:05.638889+08:00
Thread 1 cannot allocate new log, sequence 35
Checkpoint not complete
  Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/HKP/redo01.log
2020-02-09T23:27:06.299087+08:00
Thread 1 advanced to log sequence 35 (LGWR switch)
  Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/HKP/redo02.log
2020-02-09T23:27:06.308965+08:00
Archived Log entry 68 added for T-1.S-34 ID 0xc24b9a5c LAD:1
2020-02-09T23:27:06.329142+08:00
LOGSTDBY: Switchover complete (HKP)
LOGSTDBY: enabling scheduler job queue processes.
2020-02-09T23:27:06.329295+08:00
JOBQ: re-enabling CJQ0
Starting background process CJQ0
2020-02-09T23:27:06.346474+08:00
CJQ0 started with pid=70, OS id=1067
2020-02-09T23:27:06.449711+08:00
ARCt: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY


SWITCHOVER UPGRADED LOGICAL STANDBY (HKP_DG) TO PRIMARY


43. Switchover upgraded logical standby to primary

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION
--------- -------------------- -------------------------------- ---------------
HKP       READ WRITE           HKP_DG          LOGICAL STANDBY  19.0.0.0.0

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY <-----

SQL>

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

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

NAME      OPEN_MODE            DB_UNIQUE_NAME   DATABASE_ROLE    VERSION         HOST_NAME
--------- -------------------- ---------------- ---------------- --------------- --------------------
HKP       READ WRITE           HKP_DG           PRIMARY          19.0.0.0.0      rac2.rajasekhar.com <---

SQL>


44. Verify alert_HKP_DG.log

Filename: alert_HKP_DG.log

ALTER DATABASE SWITCHOVER TO PRIMARY (HKP_DG)
2020-02-09T23:36:45.455212+08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (HKP_DG)
2020-02-09T23:36:45.455516+08:00
LOGSTDBY: Switchover initiated via DDL
LOGSTDBY: Preparing to Create Detached Dictionary Build, pre-lockdown scn is [0x00000000002bbf0c]
Sun Feb 09 23:36:45 2020
Logminer Bld: Build started
2020-02-09T23:36:45.589273+08:00
ALTER SYSTEM SWITCH ALL LOGFILE start (HKP_DG)
2020-02-09T23:36:45.636455+08:00
Thread 1 advanced to log sequence 36 (LGWR switch)
  Current log# 3 seq# 36 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo03.log
2020-02-09T23:36:45.641040+08:00
ALTER SYSTEM SWITCH ALL LOGFILE complete (HKP_DG)
2020-02-09T23:36:45.701288+08:00
Sun Feb 09 23:36:45 2020
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 0 2866962 LockdownSCN is 2866962
LOGSTDBY: Starting SCN of new stream from recent lockdown [0x00000000002bbf12]
2020-02-09T23:36:45.701730+08:00
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [2866965].
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY:   primary:       [3259744860]
LOGSTDBY:   first scn:     [0x0000000000000000]
LOGSTDBY:   end scn:       [0x00000000001bb912]
LOGSTDBY:   processed scn: [0x00000000001bb913]
2020-02-09T23:36:46.270609+08:00
LOGSTDBY: terminating active RFS connections for role change
LOGSTDBY: terminated RFS process [12084]
2020-02-09T23:36:46.271142+08:00
Process termination requested for pid 12084 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
2020-02-09T23:36:46.271465+08:00
Process termination requested for pid 12100 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
LOGSTDBY: terminated RFS process [12100]
2020-02-09T23:36:46.271856+08:00
LOGSTDBY: terminated RFS process [12102]
2020-02-09T23:36:46.272637+08:00
Process termination requested for pid 12102 [source = rdbms], [info = 0] [request issued by pid: 6480, uid: 54321]
2020-02-09T23:36:46.273165+08:00
ARC4 (PID:30701): Archived Log entry 62 added for T-1.S-35 ID 0xc24e1bb5 LAD:1
2020-02-09T23:36:46.276222+08:00
LOGSTDBY: (dglcccsp) Archiving standby redo logfiles.
LOGSTDBY: (dglcccsp) Not using surrogate archiving mode
LOGSTDBY: (dglcccsp) Found [1] standby redo logfiles to archive
2020-02-09T23:37:11.785235+08:00
LOGSTDBY: (dglcccsp) Complete. [1] standby redo logfiles were archived.
2020-02-09T23:37:11.846193+08:00
NET  (PID:6480): Database role cleared from LOGICAL STANDBY [dglc.c:1953]
Starting background process LSP1
2020-02-09T23:37:11.873051+08:00
LSP1 started with pid=73, OS id=14043
2020-02-09T23:37:11.875570+08:00
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created
2020-02-09T23:37:11.882847+08:00
LOGSTDBY: Disabling database guard.
2020-02-09T23:37:11.929457+08:00
TT03 (PID:14045): Switchover in progress, stop clearing SRLs
2020-02-09T23:37:11.933903+08:00
LOGSTDBY: (LSP1) Archiving online logs as a primary database
2020-02-09T23:37:12.077504+08:00
Thread 1 advanced to log sequence 37 (LGWR switch)
  Current log# 1 seq# 37 mem# 0: /u01/app/oracle/oradata/HKP_DG/redo01.log
2020-02-09T23:37:12.095439+08:00
LOGSTDBY: Database guard disabled.  User transactions are now permitted.
2020-02-09T23:37:12.108372+08:00
LSP1 (PID:14043): LGWR is scheduled to archive to LAD:2 after log switch
2020-02-09T23:37:12.110050+08:00
LOGSTDBY: enabling scheduler job queue processes.
2020-02-09T23:37:12.110136+08:00
JOBQ: re-enabling CJQ0
2020-02-09T23:37:12.130456+08:00
LSP1 (PID:14043): Error 12154 received logging on to the standby
2020-02-09T23:37:12.130655+08:00
Errors in file /u01/app/oracle/diag/rdbms/hkp_dg/HKP_DG/trace/HKP_DG_lsp1_14043.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
LSP1 (PID:14043): Error 12154 Creating archive log file to 'HKP'
2020-02-09T23:37:12.181126+08:00
LSP1 (PID:14043): Archived Log entry 63 added for T-1.S-36 ID 0xc24e1bb5 LAD:1
LOGSTDBY: (LSP1) Starting Full LogMiner Dictionary Build
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY


CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY


45. Flashback database to restore point

++ Before we can convert to physical standby, we need to flashback database to guaranteed restore point

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

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
--------- -------------------- ------------------------------ ---------------- -----------------
HKP       READ WRITE           HKP                            LOGICAL STANDBY  12.2.0.1.0 <----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;
FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.


SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1560281088 bytes
Fixed Size                  8621088 bytes
Variable Size            1325401056 bytes
Database Buffers          218103808 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL>
SQL>  select name,open_mode,db_unique_name,database_role,version from v$database,v$instance;

NAME                 OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    VERSION
-------------------- -------------------- ------------------------------ ---------------- -----------------
HKP                  MOUNTED              HKP                            LOGICAL STANDBY  12.2.0.1.0

SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE;

Flashback complete. <-----

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


Database dismounted.
ORACLE instance shut down.
SQL>


46. Add /etc/oratab entry – 19c

[oracle@rac1 ~]$ cat /etc/oratab | grep -i "HKP"
#HKP:/u01/app/oracle/product/12.2.0/dbhome_1:N
HKP:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$


47. Copy password file / initialization file to 19c home/dbs

[oracle@rac1 dbs]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *HKP*
-rw-r-----. 1 oracle oinstall       24 Feb  8 14:32 lkHKP
-rw-r-----. 1 oracle oinstall     3584 Feb  8 14:35 orapwHKP
-rw-r-----. 1 oracle oinstall    12288 Feb  9 17:17 dr2HKP.dat
-rw-r-----. 1 oracle oinstall    12288 Feb  9 17:22 dr1HKP.dat
-rw-r-----. 1 oracle oinstall 10829824 Feb  9 19:24 snapcf_HKP.f
-rw-r-----. 1 oracle oinstall     5632 Feb 10 00:04 spfileHKP.ora
-rw-rw----. 1 oracle oinstall     1544 Feb 10 00:08 hc_HKP.dat
[oracle@rac1 dbs]$ cp -p spfileHKP.ora orapwHKP /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr /u01/app/oracle/product/19.0.0/dbhome_1/dbs/*HKP*
-rw-r-----. 1 oracle oinstall 3584 Feb  8 14:35 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwHKP
-rw-r-----. 1 oracle oinstall 5632 Feb 10 00:04 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileHKP.ora
[oracle@rac1 dbs]$


48. Configure TNS Entries

OLD PRIMARY (HKP)

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle    6768     1  0 Feb09 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit
oracle   12048  6265  0 00:21 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl stop LISTENER_HKP

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-FEB-2020 00:21:57

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622)))
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   12161  6265  0 00:22 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

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

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

HKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP)
    )
  )

HKP_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP_DG)
    )
  )


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

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

SID_LIST_LISTENER_HKP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = HKP)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = HKP)
    )
  )
  
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [HKP_DG] ? HKP
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ lsnrctl start LISTENER_HKP

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 00:28:34

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1622)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_HKP
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                10-FEB-2020 00:28:34
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_hkp/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1622)))
Services Summary...
Service "HKP" has 1 instance(s).
  Instance "HKP", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   13439     1  0 00:28 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP -inherit
oracle   13498  6265  0 00:28 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

NEW PRIMARY (HKP_DG)

++ We have upgraded DB using DBUA, so all TNS entries, password file, spfile will copied automatically to 19c diretories.
++ LISTENER will start by DBUA automatically

[oracle@rac2 admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin
[oracle@rac2 admin]$
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HKP_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP_DG)
    )
  )

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

HKP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HKP)
    )
  )

[oracle@rac2 admin]$ 

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_HKP_DG =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1622))
    )
  )

[oracle@rac2 admin]$ ps -ef | grep tns
root        15     2  0 Feb09 ?        00:00:00 [netns]
oracle   24573 10539  0 00:32 pts/1    00:00:00 grep --color=auto tns
oracle   27865     1  0 Feb09 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_HKP_DG -inherit
[oracle@rac2 admin]$


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

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [HKP] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ which sqlplus
/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 00:38:28 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1207959552 bytes
Database Buffers          335544320 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL>


50. Verify TNS Connectivity

ON OLD PRIMARY

[oracle@rac1 ~]$ tnsping HKP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:16:54

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP)))
OK (10 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping HKP_DG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:17:01

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG)))
OK (10 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus sys@HKP as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:17 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus sys@HKP_DG as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:17:34 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac1 ~]$

ON NEW PRIMARY

[oracle@rac2 ~]$ tnsping HKP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:29

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping HKP_DG

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-FEB-2020 01:18:34

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1622)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HKP_DG)))
OK (10 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys@HKP as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:18:45 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus sys@HKP_DG as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 01:19:10 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0

SQL>


51. CONVERT LOGICAL STANDBY(OLD PRIMARY) TO PHYSICAL STANDBY

SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;
                                               
NAME      OPEN_MODE  DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- ---------- --------------- ---------------- ----------------- ------------
HKP       MOUNTED    HKP             LOGICAL STANDBY  19.0.0.0.0        rac1 <--
                                     
SQL> 

SQL> alter database convert to physical standby;

Database altered.

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- -----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

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


Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1207959552 bytes
Database Buffers          335544320 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL>


52. Enable MRP

SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

no rows selected

SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>
SQL> select process,status,sequence#,thread# from v$managed_standby where process like 'MRP%';

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
MRP0      APPLYING_LOG         40          1 <----

SQL>


53. Verify GAP

ON NEW PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com <----

SQL>

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     39                    39          0
         1                     39                    39          0

SQL>

ON OLD PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1 <----

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     39                    39          0 <----

SQL>


54. Disable Flashback Database

On OLD PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES <----

SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>

ON NEW PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES <-----

SQL> ALTER DATABASE FLASHBACK OFF;

Database altered.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY <-----

SQL>


55. Drop Restore point

On OLD PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY <----

SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        09-FEB-20 07.27.58.000000000 PM

SQL>
SQL> DROP RESTORE POINT PRE_UPGRADE;

Restore point dropped.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO <----

SQL>

On NEW PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
RESTORE POINT ONLY <----

SQL>
SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                 GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
BEFORE_UPGRADE       YES        09-FEB-20 08.49.22.000000000 PM

SQL> DROP RESTORE POINT BEFORE_UPGRADE;

Restore point dropped.

SQL> 
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

no rows selected <---

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO <-----

SQL>


56. Update COMPATIBLE parameter on both primary/standby

Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.

If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.

On NEW Primary

SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1325400064 bytes
Database Buffers          218103808 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>
SQL>

ON OLD PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> show parameter COMPATIBLE

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

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


Database dismounted.
ORACLE instance shut down.
SQL>

SQL> STARTUP NOMOUNT;
ORACLE instance started.

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

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL>
SQL> SHOW PARAMETER COMPATIBLE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0 <----
noncdb_compatible                    boolean     FALSE
SQL>


57. Verify GAP

On New Primary

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     44                    44          0
         1                     44                    44          0

SQL>

On Standby (Old Primary)

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     44                    44          0 <----

SQL>


58. Revert back parameter values on both primary/standby (In case if you changed)

job_queue_processes
aq_tm_processes
dbwr_io_slaves

Congratulations !!! Rolling upgrade completed successfully from 12.2.0.1 to 19c.

Below Steps are Optional. Put the Primary back in place.


59. SWITCHOVER NEW PRIMARY(HKP_DG) TO PHYSICAL STANDBY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ---------------------
HKP       READ WRITE           HKP_DG          PRIMARY          19.0.0.0.0        rac2.rajasekhar.com

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY <----

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> shut immediate;
ORA-01012: not logged on
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac2 ~]$
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 10 02:06:57 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1325400064 bytes
Database Buffers          218103808 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> /
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- --------------------
HKP       MOUNTED              HKP_DG          PHYSICAL STANDBY 19.0.0.0.0        rac2.rajasekhar.com <----

SQL>


60. SWITCHOVER PHYSICAL STANDBY (OLD PRIMARY/HKP) TO PRIMARY

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

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------- ---------------- ----------------- ----------
HKP       MOUNTED              HKP             PHYSICAL STANDBY 19.0.0.0.0        rac1

SQL>
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY <----

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1 <-----
                                           
SQL>


61. Verify DBA_REGISTRY ON PRIMARY (HKP)

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

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1 <--
                                           
SQL>
SQL> col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

COMP_ID    COMP_NAME                                VERSION         STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG    Oracle Database Catalog Views            19.0.0.0.0      VALID
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      VALID
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      VALID
XML        Oracle XDK                               19.0.0.0.0      VALID
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      VALID
APS        OLAP Analytic Workspace                  19.0.0.0.0      VALID
RAC        Oracle Real Application Clusters         19.0.0.0.0      OPTION OFF
XDB        Oracle XML Database                      19.0.0.0.0      VALID
OWM        Oracle Workspace Manager                 19.0.0.0.0      VALID
CONTEXT    Oracle Text                              19.0.0.0.0      VALID
ORDIM      Oracle Multimedia                        19.0.0.0.0      VALID
SDO        Spatial                                  19.0.0.0.0      VALID
XOQ        Oracle OLAP API                          19.0.0.0.0      VALID
OLS        Oracle Label Security                    19.0.0.0.0      VALID
DV         Oracle Database Vault                    19.0.0.0.0      VALID

15 rows selected.

SQL>


62. Configure DG Broker

On PRIMARY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <---
SQL>
SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE <----
SQL>

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1HKP.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2HKP.dat
SQL>

On STANDBY

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE <----
SQL>
SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE <----
SQL>

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1HKP_DG.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2HKP_DG.dat
SQL>

On PRIMARY

[oracle@rac1 ~]$ which dgmgrl
/u01/app/oracle/product/19.0.0/dbhome_1/bin/dgmgrl
[oracle@rac1 ~]$
[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Feb 10 02:27:59 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@HKP AS SYSDBA
Password:
Connected to "HKP"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL> create configuration 'HKP' as primary database is 'HKP' connect identifier is HKP;
Configuration "HKP" created with primary database "HKP"
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP - Primary database <----

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed. <-----
DGMGRL> 

On Standby

SQL> set lines 190
SQL> select name,open_mode,db_unique_name,database_role,version,host_name from v$database,v$instance;

NAME      OPEN_MODE            DB_UNIQUE_NAME  DATABASE_ROLE    VERSION           HOST_NAME
--------- -------------------- --------------  ---------------- ----------------- ---------------------
HKP       MOUNTED              HKP_DG          PHYSICAL STANDBY 19.0.0.0.0        rac2.rajasekhar.com
                                              
SQL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both; <----

System altered.

SQL>

On Primary

DGMGRL> add database 'HKP_DG' as connect identifier is HKP_DG maintained as physical;
Database "HKP_DG" added
DGMGRL>

DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database <----

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

DGMGRL>

DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration;

Configuration - HKP

  Protection Mode: MaxPerformance
  Members:
  HKP    - Primary database
    HKP_DG - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago) <----

DGMGRL>


63. Verify GAP

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>


64. Verify Table

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

NAME   OPEN_MODE            DB_UNIQUE_NAME DATABASE_ROLE    VERSION         HOST_NAME
------ -------------------- -------------- ---------------- --------------- ----------
HKP    READ WRITE           HKP            PRIMARY          19.0.0.0.0      rac1

SQL> SELECT * FROM SUGI.TEJA;

NAME       ROLE
---------- ----------
RAJ        DBA
X          DBA
SUGI       DBA <-----

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

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

Upgrade Oracle Database Manually from 12.2.0.1 to 19c

Upgrade Oracle Database Manually from 12.2.0.1 to 19c (NON-CDB)

Table of Contents
___________________________________________________________________________________________________

0. Check Compatibility Before Upgrading Oracle Database
1. Environment

PRE-UPGRADE TASKS

2. Backup
3. Run preupgrade script
4. View Preupgrade log
5. Minimum tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Stop LISTENER
13. Create Flashback Guaranteed Restore Point

UPGRADE TASK

14. Shutdown Database
15. Copy init and password files from 11g to 12c dbs home
16. Startup DB in Upgrade mode
17. Run dbupgrade
18. Starup DB from 19c home

POST-UPGRADE TASKS WHEN DBUA USING

19. Run catcon.pl to start utlrp.sql
20. Run postupgrade_fixups.sql
21. Upgrade Timezone
22. Run utlusts.sql
23. Run catuppst.sql
24. Re-Run postupgrade_fixups.sql
25. Reverify INVALID OBJECTS
26. Drop Restore point
27. Set COMPATIBALE parameter value to 19.0.0
28. Verify DBA_REGISTRY
29. Add TNS Entries in 19c TNS home
30. Password File – orapwCID
31. Edit oratab
32. Back Up the Database
___________________________________________________________________________________________________


0. Check Compatibility Before Upgrading Oracle Database






1. Environment

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: CID
DB VERSION		: 12.2.0.1
CDB			: NON-CDB, Single Instance
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/CID

Target DB VERSION 	: 19c (19.4.0.0.0)
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

Upgrade Method	       : Manual


PRE-UPGRADE TASKS


2. Backup

Database Backup scripts - I have taken already

Sample Backup Database Script

rmanbackup.sh

[oracle@rac1 CID]$ 
[oracle@rac1 CID]$ chmod 775 rmanbackup.sh
[oracle@rac1 CID]$ 
[oracle@rac1 CID]$ nohup ./rmanbackup.sh & <--- hit ENTER twice.

TNS Files 

[oracle@rac1 ~]$ cd u01/app/oracle/product/12.2.0/dbhome_1/network/admin/ 
[oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/ 

PFILE/SPFILE/PASSWORD (orapwSID) FILES 

[oracle@rac1 dbs]$ pwd 
/u01/app/oracle/product/12.2.0/dbhome_1/dbs 
[oracle@rac1 dbs]$ cp -p spfileCID.ora orapwCID /u01/app/backup/ 

INVALID OBJECTS 

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

  COUNT(*)
----------
         0 <----

SQL>


3. Run preupgrade script

. oraenv  (CID)

/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 TERMINAL TEXT

--- OR ---

/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/CID/preupgrade

[oracle@rac1 ~]$ mkdir -p /home/oracle/CID/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? CID
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ /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/CID/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /home/oracle/CID/preupgrade/preupgrade.log
  /home/oracle/CID/preupgrade/preupgrade_fixups.sql
  /home/oracle/CID/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/home/oracle/CID/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-01-28T20:45:05
[oracle@rac1 ~]$


4. View Preupgrade log

[oracle@rac1 ~]$ cat /home/oracle/CID/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-01-28T20:45:05

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  CID
     Container Name:  CID
       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

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  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                             460 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  4.  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/CID

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

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

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CID
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

    SQL>@/home/oracle/CID/preupgrade/preupgrade_fixups.sql

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  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.

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

  8.  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 CID
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

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


[oracle@rac1 ~]$


5. Verify tablespace sizes for upgrade

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

http://www.br8dba.com/asm-2/#tspace


TABLESPACE_NAME   AUT FILE_NAME                                  TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
----------------- --- ------------------------------------------ ----------- ---------- ---------- ----------
SYSAUX            YES /u01/app/oracle/oradata/CID/sysaux01.dbf           460         23       5.08 31.9999847
SYSTEM            YES /u01/app/oracle/oradata/CID/system01.dbf           800          4        .48 31.9999847
UNDOTBS1          YES /u01/app/oracle/oradata/CID/undotbs01.dbf           70          3        4.2 31.9999847
USERS             YES /u01/app/oracle/oradata/CID/users01.dbf              5          4         80 31.9999847
*****************                                                ----------- ----------            ----------
sum                                                                     1335         34            127.999939

SQL>


6. Update INITIALIZATION PARAMETERS

In this test scenario, noting to update as per preupgrade.log. Hence no action taken.


7. Gather DICTIONARY STATS

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


8. Purge Recyclebin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


9. Refresh MVs

*** Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Run the following SQL query:

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

no rows selected

SQL>

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/

PL/SQL procedure successfully completed.

SQL>


10. Run preupgrade_fixups.sql

SQL> @/home/oracle/CID/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-01-28 20:45:02
SQL> REM
SQL> REM    Source Database:         CID
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-01-28 20:45:02

For Source Database:     CID
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.  dictionary_stats          YES         None.
    2.  pre_fixed_objects         YES         None.
    3.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    4.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
    5.  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>


11. Verify archive log dest size

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/CID
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>
SQL> !df -h /u01/app/archive/CID
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   35G   33G  52% /u01 <----

SQL>


12. Stop LISTENER

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 20:01 ?        00:00:00 [netns]
oracle    3943     1  0 20:08 ?        00:00:00 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr LISTENER_CID -inherit
oracle   16771  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$ lsnrctl stop LISTENER_CID

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 21:03:14

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1524)))
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 20:01 ?        00:00:00 [netns]
oracle   16812  3093  0 21:03 pts/1    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$


13. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onwards
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

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

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CID       READ WRITE           ARCHIVELOG

SQL> show parameter compatible

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL>!mkdir -p /u01/app/oracle/fast_recovery_area

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

SQL> select * from V$restore_point;

no rows selected

SQL>

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.

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
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        28-JAN-20 09.05.50.000000000 PM

SQL>


UPGRADE TASK


14. Shutdown Database

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME                 OPEN_MODE
-------------------- --------------------
CID                  READ WRITE

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>


15. Copy init and password files from 11g to 12c dbs home

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr *CID*
-rw-r-----. 1 oracle oinstall   24 Jan 28 20:08 lkCID
-rw-r-----. 1 oracle oinstall 3584 Jan 28 20:11 orapwCID <---
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:05 spfileCID.ora <---
-rw-rw----. 1 oracle oinstall 1544 Jan 28 21:07 hc_CID.dat
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwCID spfileCID.ora /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/*CID*
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileCID.ora
-rw-r-----. 1 oracle oinstall 3584 Jan 28 21:10 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCID
[oracle@rac1 dbs]$


16. Startup DB in Upgrade mode from 19c home

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 ~]$ export ORACLE_SID=CID
[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH
[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 Tue Jan 28 21:13:24 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size             939524096 bytes
Database Buffers          603979776 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
CID       READ WRITE           NO  19.0.0.0.0        OPEN MIGRATE <---

SQL>


17. Run dbupgrade

You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.

# Regular upgrade command
[oracle@rac1 ~]$ mkdir -p /home/oracle/whileupgrade
[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
[oracle@rac1 ~]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql &

--- OR ---

# Shorthand command
[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@rac1 bin]$ ls -ltr dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17  2019 dbupgrade
[oracle@rac1 bin]$
[oracle@rac1 bin]$ nohup ./dbupgrade & <--- Hit ENTER twice
[1] 22584
[oracle@rac1 bin]$ nohup: ignoring input and appending output to ânohup.outâ

[oracle@rac1 bin]$ jobs -l
[1]+ 22584 Running                 nohup ./dbupgrade & <----
[oracle@rac1 bin]$
[oracle@rac1 bin]$ disown
[oracle@rac1 bin]$
[oracle@rac1 bin]$ ps -ef | grep -i catctl.pl
oracle   22589 22584  0 21:33 pts/1    00:00:03 /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl -I/u01/app/oracle/product/19.0.0/dbhome_1/perl/lib /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
oracle   24991  3093  0 21:40 pts/1    00:00:00 grep --color=auto -i catctl.pl
[oracle@rac1 bin]$

Monitor upgrade log under below location

[oracle@rac1 upgrade20200128213345]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345
[oracle@rac1 upgrade20200128213345]$ ls -ltr *.log
-rw-------. 1 oracle oinstall 1688451 Jan 28 21:37 catupgrd1.log
-rw-------. 1 oracle oinstall 1278535 Jan 28 21:37 catupgrd2.log
-rw-------. 1 oracle oinstall 1461585 Jan 28 21:37 catupgrd3.log
-rw-------. 1 oracle oinstall 7944713 Jan 28 21:38 catupgrd0.log
[oracle@rac1 upgrade20200128213345]$

tail -f catupgrd0.log
tail -f catupgrd1.log
tail -f catupgrd2.log
tail -f catupgrd3.log

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@rac1 bin]$ more nohup.out

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = 0
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.4.0.0.0DBRU_LINUX.X64_190626]


/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20200128213334]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_catcon_22589.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 1
Database Name         = CID
DataBase Version      = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/catupgrd_cat
con_22589.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/catupgrd*.log] files for output generated by
scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345]

Parallel SQL Process Count            = 4
Components in [CID]
    Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-107]         Start Time:[2020_01_28 21:33:56]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [CID] Files:1    Time: 32s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [CID] Files:5    Time: 56s
Restart  Phase #:2    [CID] Files:1    Time: 1s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [CID] Files:19   Time: 32s
Restart  Phase #:4    [CID] Files:1    Time: 1s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [CID] Files:7    Time: 24s
*****************   Catproc Start   ****************
Serial   Phase #:6    [CID] Files:1    Time: 21s
*****************   Catproc Types   ****************
Serial   Phase #:7    [CID] Files:2    Time: 14s
Restart  Phase #:8    [CID] Files:1    Time: 1s
****************   Catproc Tables   ****************
Parallel Phase #:9    [CID] Files:67   Time: 47s
Restart  Phase #:10   [CID] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [CID] Files:1    Time: 94s
Restart  Phase #:12   [CID] Files:1    Time: 1s
**************   Catproc Procedures   **************
Parallel Phase #:13   [CID] Files:94   Time: 19s
Restart  Phase #:14   [CID] Files:1    Time: 1s
Parallel Phase #:15   [CID] Files:121  Time: 29s
Restart  Phase #:16   [CID] Files:1    Time: 1s
Serial   Phase #:17   [CID] Files:22   Time: 7s
Restart  Phase #:18   [CID] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [CID] Files:32   Time: 37s
Restart  Phase #:20   [CID] Files:1    Time: 1s
Serial   Phase #:21   [CID] Files:3    Time: 19s
Restart  Phase #:22   [CID] Files:1    Time: 1s
Parallel Phase #:23   [CID] Files:25   Time: 217s
Restart  Phase #:24   [CID] Files:1    Time: 2s
Parallel Phase #:25   [CID] Files:12   Time: 120s
Restart  Phase #:26   [CID] Files:1    Time: 1s
Serial   Phase #:27   [CID] Files:1    Time: 0s
Serial   Phase #:28   [CID] Files:3    Time: 6s
Serial   Phase #:29   [CID] Files:1    Time: 0s
Restart  Phase #:30   [CID] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [CID] Files:1    Time: 3s
Restart  Phase #:32   [CID] Files:1    Time: 1s
Serial   Phase #:34   [CID] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [CID] Files:294  Time: 48s
Serial   Phase #:36   [CID] Files:1    Time: 0s
Restart  Phase #:37   [CID] Files:1    Time: 1s
Serial   Phase #:38   [CID] Files:6    Time: 8s
Restart  Phase #:39   [CID] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [CID] Files:3    Time: 59s
Restart  Phase #:41   [CID] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [CID] Files:13   Time: 131s
Restart  Phase #:43   [CID] Files:1    Time: 1s
Parallel Phase #:44   [CID] Files:11   Time: 20s
Restart  Phase #:45   [CID] Files:1    Time: 1s
Parallel Phase #:46   [CID] Files:3    Time: 4s
Restart  Phase #:47   [CID] Files:1    Time: 2s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [CID] Files:1    Time: 10s
Restart  Phase #:49   [CID] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [CID] Files:1    Time: 5s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [CID] Files:1    Time: 3s
Restart  Phase #:52   [CID] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [CID] Files:2    Time: 382s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [CID] Files:1    Time: 2s
Serial   Phase #:56   [CID] Files:3    Time: 10s
Serial   Phase #:57   [CID] Files:3    Time: 7s
Parallel Phase #:58   [CID] Files:10   Time: 6s
Parallel Phase #:59   [CID] Files:25   Time: 10s
Serial   Phase #:60   [CID] Files:4    Time: 12s
Serial   Phase #:61   [CID] Files:1    Time: 0s
Serial   Phase #:62   [CID] Files:32   Time: 7s
Serial   Phase #:63   [CID] Files:1    Time: 0s
Parallel Phase #:64   [CID] Files:6    Time: 9s
Serial   Phase #:65   [CID] Files:2    Time: 22s
Serial   Phase #:66   [CID] Files:3    Time: 32s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [CID] Files:1    Time: 0s
Serial   Phase #:69   [CID] Files:1    Time: 5s
Parallel Phase #:70   [CID] Files:2    Time: 45s
Restart  Phase #:71   [CID] Files:1    Time: 1s
Parallel Phase #:72   [CID] Files:2    Time: 4s
Serial   Phase #:73   [CID] Files:2    Time: 5s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [CID] Files:1    Time: 1s
Serial   Phase #:76   [CID] Files:1    Time: 63s
Serial   Phase #:77   [CID] Files:2    Time: 6s
Restart  Phase #:78   [CID] Files:1    Time: 1s
Serial   Phase #:79   [CID] Files:1    Time: 55s
Restart  Phase #:80   [CID] Files:1    Time: 0s
Parallel Phase #:81   [CID] Files:3    Time: 115s
Restart  Phase #:82   [CID] Files:1    Time: 3s
Serial   Phase #:83   [CID] Files:1    Time: 13s
Restart  Phase #:84   [CID] Files:1    Time: 1s
Serial   Phase #:85   [CID] Files:1    Time: 13s
Restart  Phase #:86   [CID] Files:1    Time: 1s
Parallel Phase #:87   [CID] Files:4    Time: 155s
Restart  Phase #:88   [CID] Files:1    Time: 1s
Serial   Phase #:89   [CID] Files:1    Time: 4s
Restart  Phase #:90   [CID] Files:1    Time: 1s
Serial   Phase #:91   [CID] Files:2    Time: 12s
Restart  Phase #:92   [CID] Files:1    Time: 1s
Serial   Phase #:93   [CID] Files:1    Time: 2s
Restart  Phase #:94   [CID] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [CID] Files:1    Time: 17s
Restart  Phase #:96   [CID] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:97   [CID] Files:1    Time: 5s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [CID] Files:1    Time: 520s
*******************   Migration   ******************
Serial   Phase #:99   [CID] Files:1    Time: 3s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [CID] Files:1    Time: 2s
Serial   Phase #:101  [CID] Files:1    Time: 0s
Serial   Phase #:102  [CID] Files:1    Time: 108s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [CID] Files:1    Time: 15s
****************   Summary report   ****************
Serial   Phase #:104  [CID] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [CID] Files:1    Time: 2s
Serial   Phase #:106  [CID] Files:1    Time: 0s
Serial   Phase #:107  [CID] Files:1     Time: 30s

------------------------------------------------------
Phases [0-107]         End Time:[2020_01_28 22:20:35]
------------------------------------------------------

Grand Total Time: 2800s

 LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345/upg_summary.log

Grand Total Upgrade Time:    [0d:0h:46m:40s]
[oracle@rac1 bin]$

[oracle@rac1 upgrade20200128213345]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/CID/upgrade20200128213345
[oracle@rac1 upgrade20200128213345]$ cat upg_summary.log

Oracle Database Release 19 Post-Upgrade Status Tool    01-28-2020 22:20:0
Database Name: CID

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                          UPGRADED      19.4.0.0.0  00:18:01
JServer JAVA Virtual Machine           UPGRADED      19.4.0.0.0  00:02:29
Oracle XDK                             UPGRADED      19.4.0.0.0  00:01:12
Oracle Database Java Packages          UPGRADED      19.4.0.0.0  00:00:13
OLAP Analytic Workspace                UPGRADED      19.4.0.0.0  00:00:19
Oracle Label Security                  UPGRADED      19.4.0.0.0  00:00:11
Oracle Database Vault                  UPGRADED      19.4.0.0.0  00:00:32
Oracle Text                            UPGRADED      19.4.0.0.0  00:00:43
Oracle Workspace Manager               UPGRADED      19.4.0.0.0  00:00:37
Oracle Real Application Clusters       UPGRADED      19.4.0.0.0  00:00:01
Oracle XML Database                    UPGRADED      19.4.0.0.0  00:01:49
Oracle Multimedia                      UPGRADED      19.4.0.0.0  00:00:55
Spatial                                 LOADING      19.4.0.0.0  00:07:19
Oracle OLAP API                        UPGRADED      19.4.0.0.0  00:00:14
Datapatch                                                        00:08:34
Final Actions                                                    00:08:43
Post Upgrade                                                     00:00:12

Total Upgrade Time: 00:44:08

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:0h:46m:40s]
[oracle@rac1 upgrade20200128213345]$


18. Starup DB from 19c home

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 ~]$ export ORACLE_SID=CID
[oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH
[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 Tue Jan 28 22:25:18 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1174405120 bytes
Database Buffers          369098752 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS
--------- -------------------- --- ----------------- ------------
CID       READ WRITE           NO  19.0.0.0.0        OPEN <-----

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      UPGRADED
CATPROC    Oracle Database Packages and Types       19.0.0.0.0      UPGRADED
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0      UPGRADED
XML        Oracle XDK                               19.0.0.0.0      UPGRADED
CATJAVA    Oracle Database Java Packages            19.0.0.0.0      UPGRADED
APS        OLAP Analytic Workspace                  19.0.0.0.0      UPGRADED
RAC        Oracle Real Application Clusters         19.0.0.0.0      UPGRADED
XDB        Oracle XML Database                      19.0.0.0.0      UPGRADED
OWM        Oracle Workspace Manager                 19.0.0.0.0      UPGRADED
CONTEXT    Oracle Text                              19.0.0.0.0      UPGRADED
ORDIM      Oracle Multimedia                        19.0.0.0.0      UPGRADED
SDO        Spatial                                  19.0.0.0.0      LOADING
XOQ        Oracle OLAP API                          19.0.0.0.0      UPGRADED
OLS        Oracle Label Security                    19.0.0.0.0      UPGRADED
DV         Oracle Database Vault                    19.0.0.0.0      UPGRADED

15 rows selected.

SQL>


POST-UPGRADE TASKS WHEN DBUA USING


19. Run utlrp.sql

Run catcon.pl to start utlrp.sql, and to recompile any remaining invalid objects.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

-- OR ---

cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 &


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

  COUNT(*)
----------
      1413  <----

SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

  COUNT(*)
----------
       655 <----

SQL>

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2020-01-29 00:31:04

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2020-01-29 00:36:03

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>

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

  COUNT(*)
----------
         0

SQL>


20. Run postupgrade_fixups.sql

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

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.





Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-28 20:45:05

For Source Database:     CID
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
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      NO          Manual fixup recommended.
    7.  post_dictionary           YES         None.
    8.  post_fixed_objects        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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.


Session altered.

SQL>


21. Upgrade Timezone

For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory

The following scripts get delivered with Oracle Database 18c onward

    $ORACLE_HOME/rdbms/admin/utltz_countstats.sql
    Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
    
    $ORACLE_HOME/rdbms/admin/utltz_countstar.sql
    Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.
    
    $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
    Time zone upgrade check script
    
    $ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
    Time zone apply script. Warning: This script will restart the database and adjust time zone data.

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/
[oracle@rac1 admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql
-rw-r--r--. 1 oracle oinstall  8317 Feb 25  2017 utltz_countstats.sql
-rw-r--r--. 1 oracle oinstall  7423 Feb 25  2017 utltz_countstar.sql
-rw-r--r--. 1 oracle oinstall 33684 Sep  9  2017 utltz_upg_check.sql
-rw-r--r--. 1 oracle oinstall 21526 Sep  9  2017 utltz_upg_apply.sql
[oracle@rac1 admin]$

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        26 <-----

SQL>

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Session altered.

SQL>

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1191182336 bytes
Database Buffers          352321536 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size            1191182336 bytes
Database Buffers          352321536 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.

Session altered.

SQL>
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32 <----

1 row selected.

SQL>


22. Run utlusts.sql

*** Note: utluNNNs.sql is replaced by utlusts.sql in 19c version 
*** Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql
*** Run utlusts.sql as many times as you want, at any time after the upgrade is completed. 
*** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT

Oracle Database Release 19 Post-Upgrade Status Tool    01-29-2020 01:41:3
Database Name: CID

Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS

Oracle Server                             VALID      19.4.0.0.0  00:18:01
JServer JAVA Virtual Machine              VALID      19.4.0.0.0  00:02:29
Oracle XDK                                VALID      19.4.0.0.0  00:01:12
Oracle Database Java Packages             VALID      19.4.0.0.0  00:00:13
OLAP Analytic Workspace                   VALID      19.4.0.0.0  00:00:19
Oracle Label Security                     VALID      19.4.0.0.0  00:00:11
Oracle Database Vault                     VALID      19.4.0.0.0  00:00:32
Oracle Text                               VALID      19.4.0.0.0  00:00:43
Oracle Workspace Manager                  VALID      19.4.0.0.0  00:00:37
Oracle Real Application Clusters     OPTION OFF      19.4.0.0.0  00:00:01
Oracle XML Database                       VALID      19.4.0.0.0  00:01:49
Oracle Multimedia                         VALID      19.4.0.0.0  00:00:55
Spatial                                   VALID      19.4.0.0.0  00:07:19
Oracle OLAP API                           VALID      19.4.0.0.0  00:00:14
Datapatch                                                        00:08:34
Final Actions                                                    00:08:43
Post Upgrade                                                     00:00:12
Post Compile                                                     00:04:58

Total Upgrade Time: 00:49:07

Database time zone version is 32. It meets current release needs.

SQL>


23. Run catuppst.sql

/*
In 12c:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/oracle-database-upgrade-utilities.html#GUID-408F22C3-2AD6-4DA4-8015-F5C6149508F0
You must run this script, either through DBUA or manually, if you perform a manual upgrade.

DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.

Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.

Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time.
*/

*** Actually it will run as part of upgrade. We have reviewed catupgrd0.log and below is the output... found catuppst.sql ran and don't see errors.

/*
Rem catuppst.sql
Rem
Rem Copyright (c) 2006, 2018, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      catuppst.sql - CATalog UPgrade PoST-upgrade actions
Rem
Rem    DESCRIPTION
Rem      This post-upgrade script performs remaining upgrade actions that
Rem      do not require that the database be open in UPGRADE mode.
Rem      Automatically apply the latest PSU.
Rem
Rem    NOTES
Rem      You must be connected AS SYSDBA to run this script.
..
..
22:19:59 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
22:19:59 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 	      2020-01-28 22:19:59
DBUA_TIMESTAMP CATUPPST      FINISHED 2020-01-28 22:19:59
DBUA_TIMESTAMP CATUPPST 	 NONE 2020-01-28 22:19:59
*/

*** If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. we need to run it manually if did have errors.
*** However can run one more time make sure no errors during execution.

SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART              2020-01-29 02:24:23
DBUA_TIMESTAMP DBRESTART     FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP DBRESTART         NONE 2020-01-29 02:24:23


TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST       STARTED 2020-01-29 02:24:23

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN             2020-01-29 02:24:23
DBUA_TIMESTAMP POSTUP_BGN    FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP POSTUP_BGN        NONE 2020-01-29 02:24:23


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN             2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_BGN    FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_BGN        NONE 2020-01-29 02:24:23

catrequtlmg: b_StatEvt     = TRUE
catrequtlmg: b_SelProps    = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig    = FALSE

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END             2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_END    FINISHED 2020-01-29 02:24:23
DBUA_TIMESTAMP CATREQ_END        NONE 2020-01-29 02:24:23

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END             2020-01-29 02:24:24
DBUA_TIMESTAMP POSTUP_END    FINISHED 2020-01-29 02:24:24
DBUA_TIMESTAMP POSTUP_END        NONE 2020-01-29 02:24:24


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST               2020-01-29 02:24:24
DBUA_TIMESTAMP CATUPPST      FINISHED 2020-01-29 02:24:24
DBUA_TIMESTAMP CATUPPST          NONE 2020-01-29 02:24:24

SQL>


24. Re-Run postupgrade_fixups.sql

SQL> @/home/oracle/CID/preupgrade/postupgrade_fixups.sql
No errors.
No errors.



No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-28 20:45:05

For Source Database:     CID
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
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      YES         None.
    7.  post_dictionary           YES         None.
    8.  post_fixed_objects        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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.
SQL>


25. Reverify INVALID OBJECTS

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

  COUNT(*)
----------
         0 <-----
SQL>


26. Drop Restore point

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        28-JAN-20 09.05.50.000000000 PM
SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/CID/flashback
total 1433680
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:39 o1_mf_h30dfg5q_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:52 o1_mf_h30dfkos_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:56 o1_mf_h30gdpcm_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:12 o1_mf_h30h4zjr_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30hdtcw_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30jhoxw_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 29 02:35 o1_mf_h30jc0ct_.flb

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>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/CID/flashback
total 0  <---

SQL>


27. Set COMPATIBALE parameter value to 19.0.0

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.

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            1191182336 bytes
Database Buffers          352321536 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>


28. Verify DBA_REGISTRY

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>


29. Add TNS Entries in 19c TNS home

[oracle@rac1 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_CID =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524))
    )
  )

SID_LIST_LISTENER_CID =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CID.rajasekhar.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = CID)
    )
  )
[oracle@rac1 admin]$

[oracle@rac1 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.

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

CID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CID.rajasekhar.com)
    )
  )

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[oracle@rac1 admin]$

[oracle@rac1 admin]$ lsnrctl start LISTENER_CID

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:28

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_cid/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1524)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_CID
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                29-JAN-2020 03:16:28
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_cid/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1524)))
Services Summary...
Service "CID.rajasekhar.com" has 1 instance(s).
  Instance "CID", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping cid

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:50

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 = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CID.rajasekhar.com)))
OK (0 msec)
[oracle@rac1 admin]$


30. Password File – orapwCID

*** REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*** Password file orapwCID copied automatically during upgrade process. 
No action taken.

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwCID
-rw-r-----. 1 oracle oinstall 3584 Jan 28 22:26 orapwCID <----
[oracle@rac1 dbs]$
SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>
SQL>


31. Edit oratab

[oracle@rac1 ~]$ cat /etc/oratab | grep -i CID
#CID:/u01/app/oracle/product/12.2.0/dbhome_1:N
CID:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$


32. Back Up the Database

cat rmanbackup_19c.sh

Sample Database Backup Script:

rmanbackup_19c.sh

[oracle@rac1 AFTER_UPGRADE_19C]$ cat backup_db.log 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 29 03:41:08 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. RMAN>
connected to target database: CID (DBID=3899729854)

RMAN> 2> 3> 4> 5> 6> 7> 8>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=79 device type=DISK

allocated channel: t2
channel t2: SID=82 device type=DISK

allocated channel: t3
channel t3: SID=80 device type=DISK

Starting backup at 29-JAN-20
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CID/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/CID/sysaux01.dbf
channel t1: starting piece 1 at 29-JAN-20
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/CID/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/CID/users01.dbf
channel t2: starting piece 1 at 29-JAN-20
channel t2: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/database_CID_02un5nen_2 tag=TAG20200129T034111 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:15
channel t1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/database_CID_01un5nen_1 tag=TAG20200129T034111 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
Finished backup at 29-JAN-20

Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938097_h313ml67_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8>
allocated channel: a1
channel a1: SID=79 device type=DISK

allocated channel: a2
channel a2: SID=82 device type=DISK

allocated channel: a3
channel a3: SID=80 device type=DISK

Starting backup at 29-JAN-20
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1030914018
input archived log thread=1 sequence=2 RECID=2 STAMP=1030916097
input archived log thread=1 sequence=3 RECID=3 STAMP=1030916252
input archived log thread=1 sequence=4 RECID=4 STAMP=1030916367
input archived log thread=1 sequence=5 RECID=5 STAMP=1030916430
input archived log thread=1 sequence=6 RECID=6 STAMP=1030916704
input archived log thread=1 sequence=7 RECID=7 STAMP=1030916941
input archived log thread=1 sequence=8 RECID=8 STAMP=1030917153
input archived log thread=1 sequence=9 RECID=9 STAMP=1030917188
channel a1: starting piece 1 at 29-JAN-20
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=10 STAMP=1030917335
input archived log thread=1 sequence=11 RECID=11 STAMP=1030917430
input archived log thread=1 sequence=12 RECID=12 STAMP=1030917598
input archived log thread=1 sequence=13 RECID=13 STAMP=1030917716
input archived log thread=1 sequence=14 RECID=14 STAMP=1030917786
input archived log thread=1 sequence=15 RECID=15 STAMP=1030917852
input archived log thread=1 sequence=16 RECID=16 STAMP=1030917938
input archived log thread=1 sequence=17 RECID=17 STAMP=1030918034
input archived log thread=1 sequence=18 RECID=18 STAMP=1030918175
channel a2: starting piece 1 at 29-JAN-20
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=19 STAMP=1030918283
input archived log thread=1 sequence=20 RECID=20 STAMP=1030918326
input archived log thread=1 sequence=21 RECID=21 STAMP=1030918365
input archived log thread=1 sequence=22 RECID=22 STAMP=1030918454
input archived log thread=1 sequence=23 RECID=23 STAMP=1030918512
input archived log thread=1 sequence=24 RECID=24 STAMP=1030918594
input archived log thread=1 sequence=25 RECID=25 STAMP=1030919293
input archived log thread=1 sequence=26 RECID=26 STAMP=1030927180
input archived log thread=1 sequence=27 RECID=27 STAMP=1030935285
input archived log thread=1 sequence=28 RECID=28 STAMP=1030938102
channel a3: starting piece 1 at 29-JAN-20
channel a1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_04un5nfn_4 tag=TAG20200129T034143 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:16
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 RECID=29 STAMP=1030938103
channel a1: starting piece 1 at 29-JAN-20
channel a2: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_05un5nfn_5 tag=TAG20200129T034143 comment=NONE
channel a2: backup set complete, elapsed time: 00:02:18
channel a3: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_06un5nfo_6 tag=TAG20200129T034143 comment=NONE
channel a3: backup set complete, elapsed time: 00:02:17
channel a1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/arch_CID_07un5nk1_7 tag=TAG20200129T034143 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JAN-20

Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938246_h313rjrj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=79 device type=DISK

Starting backup at 29-JAN-20
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 29-JAN-20
channel c1: finished piece 1 at 29-JAN-20
piece handle=/u01/app/backup/CID/AFTER_UPGRADE_19C/Control_CID_09un5nkm_9 tag=TAG20200129T034422 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-20

Starting Control File and SPFILE Autobackup at 29-JAN-20
piece handle=/u01/app/oracle/fast_recovery_area/CID/autobackup/2020_01_29/o1_mf_s_1030938266_h313rv5m_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-JAN-20

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 AFTER_UPGRADE_19C]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:

Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html

Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA

Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA

Table of Contents
___________________________________________________________________________________________________

1. Environment

PRE-UPGRADE TASKS

2. Backup
3. Run pre-upgrade script
4. View Pre-upgrade log
5. Verify tablespace sizes for upgrade
6. Update INITIALIZATION PARAMETERS
7. Gather DICTIONARY STATS
8. Purge Recyclebin
9. Refresh MVs
10. Run preupgrade_fixups.sql
11. Verify archive log dest size
12. Create Flashback Guaranteed Restore Point

UPGRADE TASK

13. Run DBUA

POST-UPGRADE TASKS WHEN DBUA USING

14. Verify /etc/oratab
15. Verify Timezone version
16. Verify INVALID objects
17. Verify DBA_REGISTRY
18. Run postupgrade_fixups.sql
19. Drop Restore point
20. Update COMPATIBLE parameter

FOR YOUR INFORMATION ONLY

___________________________________________________________________________________________________


1. Environment

Hostname       		: RAC1.RAJASEKHAR.COM
Database Name  		: VFX
DB VERSION		: 12.2.0.1
CDB			: NON-CDB, Single Instance
DB Home Path 		: /u01/app/oracle/product/12.2.0/dbhome_1
Datafile Location 	: /u01/app/oracle/oradata/VFX

Target DB VERSION 	: 19c (19.4.0.0.0)
Target DB Path		: /u01/app/oracle/product/19.0.0/dbhome_1

Upgrade Method		: Database Upgrade Assistant


PRE-UPGRADE TASKS


2. Backup

** Database Backup  -- I have it.
** tnsnames.ora
** listener.ora
** sqlnet.ora
** pfile and spfile
** orapw file

DBUA adds the selected listener to the listener.ora file of the target Oracle home, and starts it.

DBUA removes the entry of the upgraded database from the old (source) listener.ora file.

DBUA reloads the listener.ora file in both the source and target Oracle Database environments.

[oracle@rac1 ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/
[oracle@rac1 admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/app/backup/
[oracle@rac1 admin]$ ls -ltr /u01/app/backup/*.ora
-rwxrwxr-x. 1 oracle oinstall 743 Jan 27 12:58 /u01/app/backup/listener.ora
-rwxrwxr-x. 1 oracle oinstall 202 Jan 27 12:59 /u01/app/backup/sqlnet.ora
-rwxrwxr-x. 1 oracle oinstall 816 Jan 27 13:02 /u01/app/backup/tnsnames.ora
[oracle@rac1 admin]$

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ cp -p  spfileVFX.ora orapwVFX /u01/app/backup/


3. Run pre-upgrade script

. oraenv  (VFX)

/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 TERMINAL TEXT

--- OR ---

/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

[oracle@rac1 ~]$ mkdir -p /home/oracle/preupgrade
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [dell] ? VFX
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ /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-01-27T16:04:10
[oracle@rac1 ~]$


4. View Preupgrade log

[oracle@rac1 ~]$ 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-01-27T16:04:09

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name:  VFX
     Container Name:  VFX
       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

  RECOMMENDED ACTIONS
  ===================
  1.  (AUTOFIXUP) Gather stale data dictionary statistics prior to database
      upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

      Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  2.  (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.

      None of the fixed object tables have had stats collected.

      Gathering statistics on fixed objects, if none have been gathered yet, is
      recommended prior to upgrading.

      For information on managing optimizer statistics, refer to the 12.2.0.1
      Oracle Database SQL Tuning Guide.

  INFORMATION ONLY
  ================
  3.  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                             470 MB       500 MB
      SYSTEM                             800 MB       912 MB
      TEMP                                32 MB       150 MB
      UNDOTBS1                            70 MB       439 MB

      Minimum tablespace sizes for upgrade are estimates.

  4.  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/VFX

      The database has archiving enabled.  The upgrade process will need free
      disk space in the archive log destination(s) to generate archived logs to.

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

  ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database VFX
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

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

=============
AFTER UPGRADE
=============

  REQUIRED ACTIONS
  ================
  None

  RECOMMENDED ACTIONS
  ===================
  6.  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.

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

  8.  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 VFX
  which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following

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


[oracle@rac1 ~]$


5. Verify tablespace sizes for upgrade

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

http://www.br8dba.com/asm-2/#tspace


TABLESPACE_NAME   AUT FILE_NAME                                 TOTAL_SPACE FREE_SPACE      Free%  MAX_SPACE
----------------- --- ----------------------------------------- ----------- ---------- ---------- ----------
SYSAUX            YES /u01/app/oracle/oradata/VFX/sysaux01.dbf          470         27       5.78 31.9999847
SYSTEM            YES /u01/app/oracle/oradata/VFX/system01.dbf          800          4        .49 31.9999847
UNDOTBS1          YES /u01/app/oracle/oradata/VFX/undotbs01.dbf          70         49      69.64 31.9999847
USERS             YES /u01/app/oracle/oradata/VFX/users01.dbf             5          4         80 31.9999847
*****************                                               ----------- ----------            ----------
sum                                                                    1345         84            127.999939

SQL>


6. Update INITIALIZATION PARAMETERS

In this test scenario, noting to update as per preupgrade.log. Hence no action taken.


7. Gather DICTIONARY STATS

SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>


8. Purge Recyclebin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

SQL>


9. Refresh MVs

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/

PL/SQL procedure successfully completed.

SQL>


10. Run preupgrade_fixups.sql

It will run by DBUA, however we are executing manually before start upgrade.

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-01-27 16:04:07
SQL> REM
SQL> REM    Source Database:         VFX
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-01-27 16:04:07

For Source Database:     VFX
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.  dictionary_stats          YES         None.
    2.  pre_fixed_objects         YES         None.
    3.  tablespaces_info          NO          Informational only.
                                              Further action is optional.
    4.  min_archive_dest_size     NO          Informational only.
                                              Further action is optional.
    5.  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>


11. Verify archive log dest size

*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/VFX <-----
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>
SQL> !df -h /u01/app/archive/VFX
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda5        67G   27G   41G  41% /u01

SQL>


12. Create Flashback Guaranteed Restore Point

*** NO need to enable Flashback Database from 11.2.0.1 onwards
*** Database MUST be in Archive Log mode
*** MUST NOT change the compatible parameter to higher version

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO <-----

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

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
VFX       READ WRITE           ARCHIVELOG <----

SQL> show parameter compatible

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL>!mkdir -p /u01/app/oracle/fast_recovery_area/VFX

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 10G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>

SQL> select * from V$restore_point;

no rows selected <------

SQL>

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created. <-----

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
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE          YES        27-JAN-20 05.26.20.000000000 PM

SQL>


UPGRADE TASK


13. Run DBUA

export DISPLAY=CLIENT_HOST_IP:0.0
example as below:
export DISPLAY=192.168.2.2:0.0

/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbua

























Database upgrade has been completed successfully, and the database is ready to use.


POST-UPGRADE TASKS WHEN DBUA USING


14. Verify /etc/oratab

After upgrade, ORACLE_HOME location changed to new 19c ORACLE_HOME by DBUA

[oracle@rac1 ~]$ cat /etc/oratab | grep -i VFX
VFX:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$


15. Verify Timezone version

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        32 <-----

SQL>


16. Verify INVALID objects

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

  COUNT(1)
----------
         0 <----

SQL>


17. Verify DBA_REGISTRY

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>


18. Run postupgrade_fixups.sql

*** This script alreday ran by DBUA under post-upgrade section. However i have executed again.

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

Session altered.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.

No errors.

Package body created.


PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 1
Generated on:            2020-01-27 16:04:09

For Source Database:     VFX
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
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      YES         None.
    7.  post_dictionary           YES         None.
    8.  post_fixed_objects        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 upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_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.


Session altered.

SQL>


19. Drop Restore point

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        27-JAN-20 05.26.20.000000000 PM

SQL>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/VFX/flashback
total 1433668
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 17:55 o1_mf_h2xc5wy5_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:09 o1_mf_h2xc5zbo_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:12 o1_mf_h2xdwp08_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:30 o1_mf_h2xfq0wh_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:32 o1_mf_h2xfx790_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 18:32 o1_mf_h2xh1orn_.flb
-rw-r-----. 1 oracle oinstall 209723392 Jan 27 19:06 o1_mf_h2xgxgwj_.flb

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>
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/VFX/flashback
total 0 <-----

SQL>


20. Update COMPATIBLE parameter

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.

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             922746880 bytes
Database Buffers          620756992 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> select name,open_mode,version from v$database,v$instance;

NAME                 OPEN_MODE            VERSION
-------------------- -------------------- ---------------
VFX                  READ WRITE           19.0.0.0.0 <-----

SQL>

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:32 ?        00:00:00 [netns]
oracle   18145     1  0 18:38 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_VFX -inherit
oracle   32707  4398  0 19:51 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$ lsnrctl status LISTENER_VFX

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-JAN-2020 19:51:50

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_VFX
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                27-JAN-2020 18:38:51
Uptime                    0 days 1 hr. 12 min. 58 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_vfx/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621)))
Services Summary...
Service "VFX.rajasekhar.com" has 1 instance(s).
  Instance "VFX", status READY, has 1 handler(s) for this service...
Service "VFXXDB.rajasekhar.com" has 1 instance(s).
  Instance "VFX", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$


FOR YOUR INFORMATION ONLY

If local_listener set in INITIALIZATION parameter file, then please remove it before upgrade, after upgrade you can add it back manually. If you don’t remove, may get error while running Post upgrade steps DBUA. I have faced this issue while upgrade from 11g to 12c not in this test case. FYI ONLY


  
Found below error in upgrade log file...

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Apply RU on Database 19c

Applying RU (Release Update) on Oracle Database 19c

Goal: We are going to apply Patch 29834717 – Database Release Update 19.4.0.0.190716

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. Introduction
3. Download the patch from MOS (My Oracle Support)
4. Unzip patch
5. List patches which were applied
6. Check Conflict
7. Shutdown Database
8. Stop LISTENER
9. Backup Oracle Home / Inventory
10. Apply Patch
11. List Patches
12. Startup Database
13. Apply SQL changes (datapatch)
14. Verify from dba_registry_sqlpatch
15. Start LISTENER
16. Verify INVALID objects
_________________________________________________________________________________________________


1. Environment

Platform   : Linuxx86_64
Server IP  : 192.168.2.101
DB Version : Oracle 19c, File system: Normal, RAC: Non-RAC
DB Name    : dell
Oracle Home: /u01/app/oracle/product/19.0.0/dbhome_1


2. Introduction

What is an RU? Release Update (formerly known as Proactive Bundle Patches). 

RU are actually the second number, eg. 19.4.0.0.0

Each release update can also be updated with PSUs, still the last number, for example 19.4.0.0.190716

You must use the OPatch utility version 12.2.0.1.17 or later


3. Download the patch from MOS (My Oracle Support)


4. Unzip patch

-- Copy the patch to server from local machine using WinSCP or FTP

[oracle@rac1 ~]$ cd /u01/app/backup/
[oracle@rac1 backup]$ ls -ltr
total 738064
-rw-r--r--. 1 oracle oinstall 755773734 Jan 26 18:25 p29834717_190000_Linux-x86-64.zip
[oracle@rac1 backup]$ unzip p29834717_190000_Linux-x86-64.zip
[oracle@rac1 backup]$ ls -ltr
total 738204
drwxr-xr-x. 4 oracle oinstall        67 Jul 10  2019 29834717
-rw-rw-r--. 1 oracle oinstall    142160 Jul 16  2019 PatchSearch.xml
-rw-r--r--. 1 oracle oinstall 755773734 Jan 26 18:25 p29834717_190000_Linux-x86-64.zip
[oracle@rac1 backup]$


5. List patches which were applied

[oracle@rac1 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lsinv -oh /u01/app/oracle/product/19.0.0/dbhome_1
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-26_21-32-18PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2020-01-26_21-32-18PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1.rajasekhar.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  29585399     : applied on Thu Apr 18 15:21:33 SGT 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
     27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
     28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
     28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
     28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
     28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
     28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
     29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
     29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
     29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
     29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
     29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
     29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
     29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
     29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
     29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
     29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
     29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
     29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
     29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
     29413360, 29457319, 29465047

Patch  29517242     : applied on Thu Apr 18 15:21:17 SGT 2019
Unique Patch ID:  22862832
Patch description:  "Database Release Update : 19.3.0.0.190416 (29517242)"
   Created on 17 Apr 2019, 23:27:10 hrs PST8PDT
   Bugs fixed:
     29515240, 14735102, 19697993, 20313356, 21965541, 25806201, 25883179
     25986062, 26476244, 26611353, 26872233, 27369515, 27423500, 27666312
     27710072, 27846298, 27957203, 28064977, 28072567, 28129791, 28181021
     28210681, 28279456, 28313275, 28350595, 28371123, 28379065, 28431445
     28463226, 28489419, 28502773, 28513333, 28534475, 28561704, 28569897
     28572533, 28572544, 28587723, 28593682, 28594086, 28597221, 28601957
     28605066, 28606598, 28625862, 28627033, 28636532, 28643718, 28644549
     28645570, 28646200, 28646939, 28649388, 28655209, 28663782, 28673945
     28692275, 28694872, 28696373, 28705231, 28710385, 28710734, 28714461
     28718469, 28730079, 28740708, 28760206, 28772390, 28774416, 28777214
     28789531, 28791852, 28795551, 28802734, 28804517, 28810381, 28811560
     28815123, 28815355, 28819640, 28824482, 28833912, 28835937, 28862532
     28863432, 28873575, 28876253, 28876639, 28884931, 28888327, 28892794
     28897512, 28899663, 28901126, 28905457, 28907196, 28912691, 28915561
     28917080, 28918429, 28919145, 28922227, 28922532, 28922608, 28925634
     28925880, 28933158, 28936114, 28937717, 28938698, 28940179, 28940281
     28941901, 28942455, 28945421, 28945994, 28951533, 28952168, 28954762
     28955606, 28957292, 28957723, 28962775, 28965231, 28966444, 28974083
     28977322, 28983095, 28983486, 28986326, 28986481, 28988482, 28989306
     28993295, 28994307, 28996376, 29000000, 29001888, 29002927, 29003738
     29006318, 29006621, 29007321, 29007775, 29008035, 29011936, 29013475
     29014076, 29015118, 29017265, 29018655, 29019121, 29021063, 29021352
     29024552, 29026582, 29026606, 29027456, 29027694, 29027940, 29031575
     29031600, 29032234, 29032457, 29032607, 29033052, 29033145, 29033200
     29033280, 29034587, 29037290, 29038528, 29039089, 29039510, 29043554
     29043651, 29043725, 29044763, 29044954, 29047850, 29048289, 29048498
     29048605, 29050560, 29050765, 29051702, 29052726, 29053783, 29056024
     29056270, 29056560, 29059011, 29061959, 29062692, 29062848, 29062860
     29062868, 29110526, 29110783, 29110790, 29110797, 29110802, 29110805
     29111598, 29113282, 29113305, 29117526, 29117642, 29119077, 29120223
     29122224, 29122254, 29123297, 29123432, 29123482, 29124368, 29125380
     29126345, 29127957, 29128935, 29129450, 29129497, 29129712, 29130219
     29131539, 29132938, 29134447, 29136111, 29138641, 29141316, 29141341
     29141685, 29142667, 29144995, 29145214, 29145730, 29149829, 29150338
     29151520, 29152357, 29155099, 29157389, 29158680, 29158899, 29159909
     29159936, 29160174, 29162095, 29163415, 29163437, 29163524, 29163567
     29167111, 29167342, 29167374, 29167940, 29168219, 29168433, 29169073
     29169215, 29171942, 29172618, 29172826, 29173140, 29173373, 29173817
     29174004, 29176318, 29177466, 29177543, 29177886, 29178385, 29180313
     29180455, 29180559, 29180893, 29181153, 29181231, 29181620, 29181743
     29181923, 29182019, 29183912, 29184297, 29184666, 29185193, 29186456
     29189302, 29189307, 29189889, 29190235, 29190474, 29190740, 29191541
     29192419, 29192468, 29192685, 29193207, 29194205, 29194367, 29194493
     29194827, 29195279, 29195337, 29195758, 29196725, 29199635, 29199733
     29200316, 29200700, 29201494, 29201539, 29202104, 29202850, 29203122
     29203166, 29203425, 29203443, 29203604, 29205281, 29205323, 29205419
     29205463, 29205767, 29205918, 29206109, 29206605, 29207073, 29208260
     29208732, 29211457, 29211724, 29212012, 29212433, 29212611, 29213351
     29213775, 29213850, 29213879, 29214561, 29214960, 29216746, 29216984
     29217294, 29217472, 29217828, 29217848, 29218570, 29219205, 29219273
     29220079, 29221248, 29221891, 29222031, 29222784, 29223833, 29223859
     29223967, 29224065, 29224605, 29225076, 29227602, 29228869, 29229164
     29229754, 29229844, 29229955, 29230252, 29230565, 29231133, 29232117
     29232154, 29234123, 29237538, 29240307, 29241345, 29242017, 29242884
     29243958, 29245137, 29245160, 29246163, 29247415, 29247712, 29247906
     29248552, 29248835, 29248858, 29249991, 29250059, 29251259, 29253184
     29253871, 29254031, 29254930, 29255178, 29255273, 29255431, 29255435
     29256426, 29259119, 29259320, 29261906, 29262512, 29262887, 29265448
     29266248, 29266899, 29267292, 29268412, 29269171, 29270585, 29273539
     29273735, 29273847, 29274428, 29274564, 29274627, 29275461, 29278218
     29279658, 29279751, 29279854, 29282898, 29285503, 29285788, 29285956
     29286037, 29287130, 29287705, 29292837, 29293072, 29297863, 29297915
     29298220, 29299049, 29299082, 29299844, 29301566, 29302963, 29303918
     29304781, 29311588, 29312310, 29312734, 29312753, 29313347, 29313417
     29313525, 29314539, 29318410, 29319441, 29321489, 29323946, 29324568
     29325087, 29325105, 29325257, 29325993, 29327044, 29329087, 29331209
     29331380, 29331493, 29332771, 29333500, 29337310, 29338315, 29338453
     29338780, 29338913, 29339155, 29341209, 29343086, 29346943, 29347620
     29348176, 29350052, 29351386, 29351716, 29351749, 29352867, 29353271
     29355654, 29356547, 29356752, 29358828, 29360672, 29361801, 29363151
     29364171, 29364177, 29367019, 29367561, 29368253, 29373418, 29373588
     29374179, 29377986, 29378029, 29379978, 29382784, 29385652, 29386502
     29386635, 29386660, 29387274, 29388094, 29388524, 29388830, 29389889
     29390435, 29394140, 29394749, 29397954, 29398863, 29399100, 29399121
     29405012, 29405651, 29412066, 29412269, 29417884, 29423826, 29429264
     29429566, 29431485, 29435652, 29438736, 29439522, 29450421, 29451386
     29452576, 29452953, 29457807, 29460252, 29462957, 29486181, 29507616
     29521748, 29530812, 29531654, 29557336, 29558975, 29601461



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

OPatch succeeded.
[oracle@rac1 ~]$


6. Check Conflict

[oracle@rac1 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph /u01/app/backup/29834717
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-26_21-37-49PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed. <----

OPatch succeeded.
[oracle@rac1 ~]$


7. Shutdown Database

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>


8. Stop LISTENER

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:28 ?        00:00:00 [netns]
oracle   25615     1  0 21:41 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_DELL -inherit
oracle   26108 16793  0 21:43 pts/1    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl stop LISTENER_DELL

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JAN-2020 21:43:08

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1522)))
The command completed successfully
[oracle@rac1 ~]$
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:28 ?        00:00:00 [netns]
oracle   26141 16793  0 21:43 pts/1    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$


9. Backup Oracle Home / Inventory


10. Apply Patch

[oracle@rac1 backup]$ cd 29834717/
[oracle@rac1 29834717]$ pwd
/u01/app/backup/29834717
[oracle@rac1 29834717]$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/19.0.0/dbhome_1 -local
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-26_21-45-10PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   29834717

Do you want to proceed? [y|n]
y
User Responded with: Y <-----
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y <-----
User Responded with: Y
Backing up files...
Applying interim patch '29834717' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.nlsrtl.companion, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...
Patch 29834717 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [29834717].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-26_21-45-10PM_1.log

OPatch succeeded.
[oracle@rac1 29834717]$


11. List Patches

[oracle@rac1 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lsinv -oh /u01/app/oracle/product/19.0.0/dbhome_1
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-26_21-52-57PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2020-01-26_21-52-57PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: rac1.rajasekhar.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  29834717     : applied on Sun Jan 26 21:46:03 SGT 2020
Unique Patch ID:  23016168
Patch description:  "Database Release Update : 19.4.0.0.190716 (29834717)"
   Created on 10 Jul 2019, 02:09:26 hrs PST8PDT
   Bugs fixed:
     29850930, 29225758, 29276272, 14735102, 17428816, 19080742, 19697993
     20313356, 21374587, 21965541, 23296836, 23606241, 25756945, 25806201
     25883179, 25986062, 25997810, 26476244, 26611353, 26739322, 26872233
     27126938, 27244999, 27359766, 27369515, 27423500, 27453490, 27666312
     27710072, 27846298, 27957203, 28064977, 28072567, 28125947, 28129791
     28181021, 28210681, 28271693, 28279456, 28313275, 28326928, 28350595
     28371123, 28379065, 28381939, 28390273, 28431445, 28463226, 28475242
     28489419, 28502773, 28513333, 28534475, 28547068, 28561704, 28569897
     28572533, 28572544, 28572667, 28572834, 28587723, 28593682, 28594086
     28597221, 28601957, 28605066, 28606598, 28612239, 28625862, 28627033
     28636532, 28643718, 28644549, 28645570, 28646200, 28646939, 28649388
     28655209, 28663782, 28673945, 28692275, 28694872, 28696373, 28705231
     28710385, 28710734, 28714461, 28715727, 28718469, 28719348, 28720418
     28722229, 28730079, 28740708, 28742555, 28749853, 28755011, 28758722
     28760206, 28767240, 28772390, 28774416, 28777214, 28781754, 28785531
     28789531, 28791852, 28795551, 28802734, 28804517, 28810381, 28811560
     28815123, 28815355, 28819640, 28824482, 28827682, 28833912, 28835937
     28849776, 28854004, 28862532, 28863432, 28867992, 28873575, 28876253
     28876639, 28884931, 28888327, 28889730, 28892794, 28897512, 28899663
     28901126, 28905457, 28907196, 28912691, 28915561, 28917080, 28918429
     28919145, 28922227, 28922532, 28922608, 28925634, 28925880, 28933158
     28936114, 28937717, 28938698, 28940179, 28940281, 28941901, 28942455
     28945421, 28945994, 28950868, 28951533, 28952168, 28954762, 28955606
     28956908, 28957292, 28957723, 28962775, 28965231, 28966444, 28974083
     28977322, 28983095, 28983486, 28986207, 28986231, 28986326, 28986481
     28988482, 28989306, 28993295, 28993353, 28994307, 28996376, 29000000
     29001305, 29001888, 29002784, 29002927, 29003738, 29006318, 29006621
     29007321, 29007775, 29008035, 29008669, 29009513, 29011936, 29012609
     29013475, 29014076, 29015118, 29017265, 29018655, 29019121, 29021063
     29021352, 29024054, 29024552, 29024732, 29026582, 29026606, 29027456
     29027694, 29027940, 29031575, 29031600, 29032234, 29032276, 29032457
     29032607, 29033052, 29033145, 29033200, 29033280, 29034587, 29037290
     29038528, 29039089, 29039510, 29040739, 29043554, 29043651, 29043725
     29044763, 29044954, 29047850, 29048178, 29048289, 29048498, 29048605
     29050357, 29050560, 29050765, 29051702, 29051953, 29052726, 29053783
     29056024, 29056270, 29056560, 29056767, 29059011, 29061959, 29062692
     29062848, 29062860, 29062868, 29110526, 29110783, 29110790, 29110797
     29110802, 29110805, 29111598, 29113282, 29113305, 29117526, 29117642
     29119077, 29120223, 29122224, 29122254, 29123297, 29123432, 29123482
     29124368, 29125036, 29125374, 29125380, 29126345, 29127957, 29128693
     29128935, 29129450, 29129497, 29129712, 29130219, 29131539, 29132938
     29134447, 29135383, 29135649, 29136111, 29138641, 29141316, 29141341
     29141685, 29142609, 29142667, 29144995, 29145214, 29145730, 29149829
     29150338, 29151520, 29152357, 29155099, 29157389, 29158680, 29158899
     29159909, 29159936, 29160174, 29162095, 29163156, 29163415, 29163437
     29163524, 29163567, 29167111, 29167342, 29167374, 29167940, 29168219
     29168433, 29169073, 29169215, 29171683, 29171942, 29172618, 29172826
     29173140, 29173373, 29173817, 29174004, 29176318, 29177466, 29177543
     29177886, 29178385, 29180313, 29180455, 29180559, 29180893, 29181153
     29181231, 29181620, 29181743, 29181923, 29182019, 29183912, 29184297
     29184666, 29185193, 29186456, 29189302, 29189307, 29189889, 29190235
     29190474, 29190740, 29191541, 29192419, 29192468, 29192685, 29193207
     29194205, 29194367, 29194493, 29194827, 29194981, 29195279, 29195337
     29195758, 29196725, 29199635, 29199733, 29200316, 29200700, 29201494
     29201539, 29202104, 29202850, 29203122, 29203166, 29203425, 29203443
     29203604, 29205281, 29205323, 29205419, 29205463, 29205767, 29205918
     29206109, 29206605, 29207073, 29208260, 29208732, 29211457, 29211724
     29212012, 29212433, 29212611, 29213351, 29213775, 29213850, 29213879
     29214561, 29214960, 29216746, 29216984, 29217294, 29217472, 29217828
     29217848, 29218570, 29219205, 29219273, 29220079, 29221248, 29221891
     29222031, 29222784, 29223833, 29223859, 29223967, 29224065, 29224605
     29225076, 29225168, 29227602, 29228869, 29229164, 29229754, 29229844
     29229955, 29230252, 29230565, 29231133, 29232117, 29232154, 29233415
     29234123, 29237538, 29240307, 29241345, 29241651, 29242017, 29242884
     29243958, 29245137, 29245160, 29246163, 29247415, 29247712, 29247906
     29248495, 29248552, 29248835, 29248858, 29249991, 29250059, 29250317
     29251259, 29253184, 29253871, 29254031, 29254930, 29255178, 29255273
     29255431, 29255435, 29256426, 29259119, 29259320, 29260452, 29261547
     29261906, 29262512, 29262887, 29265448, 29266248, 29266899, 29267292
     29268412, 29269171, 29269228, 29270585, 29273539, 29273570, 29273735
     29273812, 29273847, 29274428, 29274564, 29274627, 29275461, 29277317
     29278218, 29279658, 29279751, 29279854, 29281527, 29281691, 29281796
     29282233, 29282898, 29285503, 29285788, 29285956, 29286037, 29287130
     29287705, 29292837, 29293072, 29293574, 29297863, 29297915, 29298220
     29299049, 29299082, 29299844, 29301463, 29301566, 29302963, 29303918
     29304781, 29306226, 29306713, 29311588, 29312310, 29312672, 29312734
     29312753, 29313347, 29313417, 29313525, 29314539, 29317756, 29318410
     29319441, 29321489, 29323946, 29324568, 29324735, 29325087, 29325105
     29325257, 29325765, 29325993, 29327044, 29329042, 29329087, 29329807
     29330361, 29331066, 29331209, 29331380, 29331493, 29332292, 29332395
     29332771, 29333500, 29336843, 29337310, 29338315, 29338453, 29338780
     29338913, 29339101, 29339155, 29341209, 29343086, 29345937, 29346057
     29346211, 29346943, 29347620, 29348176, 29350052, 29351386, 29351716
     29351735, 29351749, 29352298, 29352724, 29352867, 29352947, 29353271
     29353432, 29353960, 29355654, 29356547, 29356704, 29356711, 29356752
     29358509, 29358828, 29360285, 29360672, 29360911, 29360950, 29361472
     29361801, 29363151, 29364171, 29364177, 29366940, 29367019, 29367561
     29368253, 29372541, 29373418, 29373588, 29374179, 29375941, 29376346
     29377986, 29378029, 29378834, 29378913, 29379978, 29382784, 29382815
     29384781, 29384854, 29384864, 29385429, 29385652, 29386502, 29386635
     29386660, 29387073, 29387274, 29388020, 29388072, 29388094, 29388524
     29388830, 29389889, 29390011, 29390435, 29390785, 29391030, 29394014
     29394140, 29394749, 29395657, 29397954, 29397996, 29398488, 29398863
     29399100, 29399121, 29399938, 29402131, 29404483, 29405012, 29405462
     29405651, 29405996, 29407804, 29409149, 29410311, 29410834, 29411037
     29412066, 29412269, 29417719, 29417884, 29420834, 29421059, 29423826
     29424999, 29426241, 29429017, 29429264, 29429566, 29430524, 29431192
     29431485, 29434301, 29435474, 29435652, 29436454, 29436514, 29437594
     29437712, 29438277, 29438736, 29439522, 29441196, 29443187, 29443250
     29444072, 29444282, 29444602, 29446669, 29449477, 29450421, 29451386
     29452576, 29452936, 29452953, 29455424, 29457312, 29457370, 29457807
     29460252, 29461791, 29462594, 29462767, 29462957, 29464779, 29465177
     29467622, 29476473, 29483532, 29483672, 29483685, 29483712, 29486181
     29488894, 29489546, 29490256, 29492127, 29492939, 29494245, 29495057
     29495684, 29497311, 29500826, 29502561, 29503543, 29503631, 29503827
     29504492, 29504682, 29505668, 29507616, 29510278, 29511611, 29514479
     29515134, 29515240, 29515766, 29515834, 29516300, 29517168, 29521187
     29521748, 29522358, 29522561, 29522662, 29523511, 29525467, 29525886
     29527595, 29529147, 29530440, 29530515, 29530812, 29531654, 29541769
     29542449, 29543034, 29546817, 29547867, 29548687, 29548722, 29549154
     29549730, 29557336, 29558975, 29559187, 29559446, 29559908, 29559981
     29565611, 29580983, 29581771, 29584261, 29586143, 29597536, 29597754
     29598039, 29598233, 29599300, 29601461, 29604002, 29608000, 29611020
     29611991, 29616244, 29616414, 29618074, 29618190, 29622936, 29626732
     29628200, 29629650, 29629681, 29631749, 29632095, 29633697, 29635427
     29635717, 29637362, 29638285, 29641736, 29653246, 29656819, 29657422
     29664087, 29664161, 29670782, 29676089, 29677051, 29677733, 29679856
     29687459, 29687763, 29692694, 29695841, 29703932, 29707099, 29742223
     29747648, 29751094, 29753244, 29754951, 29756274, 29757651, 29766207
     29766503, 29766679, 29775393, 29779196, 29795957, 29838773

Patch  29585399     : applied on Thu Apr 18 15:21:33 SGT 2019
Unique Patch ID:  22840393
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
   Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
   Bugs fixed:
     27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
     28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
     28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
     28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
     28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
     28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
     29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
     29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
     29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
     29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
     29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
     29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
     29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
     29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
     29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
     29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
     29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
     29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
     29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
     29413360, 29457319, 29465047



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

OPatch succeeded.
[oracle@rac1 ~]$


12. Startup Database

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [dell] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 21:54:39 2020
Version 19.4.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1560277408 bytes
Fixed Size                  8896928 bytes
Variable Size             905969664 bytes
Database Buffers          637534208 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE
--------- --------------------
DELL      READ WRITE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
[oracle@rac1 ~]$


13. Apply SQL changes (datapatch)

[oracle@rac1 ~]$ /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/datapatch -verbose
SQL Patching tool version 19.4.0.0.0 Production on Sun Jan 26 21:58:27 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_31031_2020_01_26_21_58_27/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.4.0.0.0 Release_Update 190626171128: Installed
  SQL registry:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 26-JAN-20 06.51.23.778177 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 29834717 (Database Release Update : 19.4.0.0.190716 (29834717)):
    Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.4.0.0.0 Release_Update 190626171128
  No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 29834717 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29834717/23016168/29834717_apply_DELL_2020Jan26_21_58_59.log (no errors)
SQL Patching tool complete on Sun Jan 26 22:03:33 2020
[oracle@rac1 ~]$


14. Verify from dba_registry_sqlpatch

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000

COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
 action,
 status,
 description,
 patch_id
 FROM   sys.dba_registry_sqlpatch
 ORDER by action_time;

ACTION_TIME  ACTION     STATUS     DESCRIPTION                                                    PATCH_ID
------------ ---------- ---------- ------------------------------------------------------------ ----------
2020-01-26   APPLY      SUCCESS    Database Release Update : 19.4.0.0.190716 (29834717)           29834717
2020-01-26   APPLY      SUCCESS    Database Release Update : 19.3.0.0.190416 (29517242)           29517242


15. Start LISTENER

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:28 ?        00:00:00 [netns]
oracle    1800  2552  0 22:12 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl start LISTENER_DELL

[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:28 ?        00:00:00 [netns]
oracle    1816     1  0 22:12 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_DELL -inherit
oracle    6865  2552  0 22:38 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$


16. Verify INVALID objects

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

  COUNT(*)
----------
         0  <------

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create DB 19C – DBCA-SILENT

CREATE NON-CONTAINER DATABASE ON ORACLE 19C IN SILENT MODE USING DBCA

Contents
___________________________________________________________________________________________________

1. Overview
2. Set the Environment
3. Create Database using DBCA silent mode
4. Verify
___________________________________________________________________________________________________


1. Overview

Usually to create database we will use DBCA graphical user interface.

Some times may not have access to a graphical user interface then we can use the DBCA silent mode to create a database.

We are going to create NON-CONTAINER database as COKE 

Database Binary Version: 19.3.0.0.0
OS                     : Linux x86_64


2. Set the Environment

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@rac1 ~]$ which dbca
/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbca
[oracle@rac1 ~]$ dbca 

-- No need to create directory COKE, it will create automatically by dbca


3. Create Database using DBCA silent mode

[oracle@rac1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName COKE.rajasekhar.com -sid COKE -sysPassword Coke123$ -systemPassword Coke@123 -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS -characterSet AL32UTF8
Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
50% complete
54% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/COKE.
Database Information:
Global Database Name:COKE.rajasekhar.com
System Identifier(SID):COKE
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/COKE/COKE.log" for further details.
[oracle@rac1 ~]$


4. Verify

[oracle@rac1 ~]$ cat /etc/oratab | grep -i COKE
COKE:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@rac1 ~]$

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle    4273     1  0 19:53 ?        00:00:00 ora_pmon_COKE
oracle    5240  2552  0 19:56 pts/0    00:00:00 grep --color=auto pmon
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [dell] ? COKE
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 19:57:31 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
COKE      READ WRITE           NO <----

SQL>

SQL> show con_name

CON_NAME
------------------------------
COKE
SQL>
SQL> archive log list
Database log mode              No Archive Mode <----
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence     3
Current log sequence           5
SQL>
SQL> select name from v$datafile
UNION
select name from v$tempfile; 

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/COKE/sysaux01.dbf
/u01/app/oracle/oradata/COKE/system01.dbf
/u01/app/oracle/oradata/COKE/temp01.dbf
/u01/app/oracle/oradata/COKE/undotbs01.dbf
/u01/app/oracle/oradata/COKE/users01.dbf

SQL> 

listener.ora

LISTENER_COKE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )

SID_LIST_LISTENER_COKE =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = COKE.rajasekhar.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = COKE)
    )
  )

  
tnsnames.ora
 
COKE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = COKE.rajasekhar.com)
    )
  )


[oracle@rac1 admin]$ tnsping COKE

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JAN-2020 20:07:59

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 = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = COKE.rajasekhar.com)))
OK (10 msec)
[oracle@rac1 admin]$

[oracle@rac1 admin]$ lsnrctl status LISTENER_COKE

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JAN-2020 20:09:54

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_COKE
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-JAN-2020 20:03:42
Uptime                    0 days 0 hr. 6 min. 12 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_coke/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "COKE.rajasekhar.com" has 1 instance(s).
  Instance "COKE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 admin]$

[oracle@rac1 ~]$ sqlplus sys@coke as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 20:15:24 2020
Version 19.3.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.3.0.0.0

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" FROM DUAL;

SYSDATE
-----------------------------
26-JAN-2020 20:15:45

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create DB 19C – DBCA-GUI

Create Non-CDB Oracle Database 19c on Linux using DBCA (GUI)

Table of Contents
___________________________________________________________________________________________________

1. Environment
2. Invoke dbca
3. Verify
___________________________________________________________________________________________________


1. Environment

Platform		: Linuxx86_64 (OEL 7)
Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version		: Oracle 19.3.0.0.0, File system: Normal
NON-CDB Name	        : DELL
Oracle Home Path        : /u01/app/oracle/product/19.0.0/dbhome_1


2. Invoke dbca

[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@rac1 ~]$ which dbca
/u01/app/oracle/product/19.0.0/dbhome_1/bin/dbca  <---
[oracle@rac1 ~]$ dbca 


































3. Verify

[oracle@rac1 ~]$ cat /etc/oratab | grep -i dell
dell:/u01/app/oracle/product/19.0.0/dbhome_1:N  <-----
[oracle@rac1 ~]$

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle   24068     1  0 18:57 ?        00:00:00 ora_pmon_dell
oracle   25204  2552  0 19:01 pts/0    00:00:00 grep --color=auto pmon
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DELL] ? dell
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 19:02:20 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
DELL      READ WRITE           NO  <-----

SQL> show con_name

CON_NAME
------------------------------
dell
SQL> select name from v$datafile
UNION
select name from v$tempfile; 

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DELL/sysaux01.dbf
/u01/app/oracle/oradata/DELL/system01.dbf
/u01/app/oracle/oradata/DELL/temp01.dbf
/u01/app/oracle/oradata/DELL/undotbs01.dbf
/u01/app/oracle/oradata/DELL/users01.dbf

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@rac1 ~]$ ps -ef | grep tns
root        15     2  0 12:28 ?        00:00:00 [netns]
oracle   19876     1  0 18:41 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER_DELL -inherit
oracle   25594  2552  0 19:03 pts/0    00:00:00 grep --color=auto tns
[oracle@rac1 ~]$

[oracle@rac1 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_DELL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

[oracle@rac1 admin]$

[oracle@rac1 ~]$ lsnrctl status LISTENER_DELL | grep -i "dell"
Alias                     LISTENER_DELL
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener_dell/alert/log.xml
Service "dell.rajasekhar.com" has 1 instance(s).
  Instance "dell", status READY, has 1 handler(s) for this service...
Service "dellXDB.rajasekhar.com" has 1 instance(s).
  Instance "dell", status READY, has 1 handler(s) for this service...
[oracle@rac1 ~]$



[oracle@rac1 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.

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


DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dell.rajasekhar.com)
    )
  )

[oracle@rac1 admin]$


[oracle@rac1 ~]$ tnsping dell

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JAN-2020 19:04:49

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 = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dell.rajasekhar.com)))
OK (10 msec)
[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus sys@dell as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 19:06:48 2020
Version 19.3.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.3.0.0.0

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" FROM DUAL;

SYSDATE
-----------------------------
26-JAN-2020 19:07:06

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Oracle 19c Deinstall

Deinstall Oracle 19c Database Binaries

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. Deinstall
3. Verification
___________________________________________________________________________________________________


1. Overview

You can run the deinstall command from an Oracle home directory after installation.

deinstall stops Oracle software, and removes Oracle software and configuration files on the operating system for a specific Oracle home.

Please note deinstall command also delete/remove your databases associated with respective oracle home from where invoke deinstall command. 

*** Please DO NOT try until comfortable.


2. Deinstall

[oracle@rac1 ~]$  /u01/app/oracle/product/19.0.0/db_1/deinstall/deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DECONFIG TOOL START ############


######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/19.0.0/db_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2020-01-26_02-51-37PM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2020-01-26_02-51-37PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END

######################### DECONFIG CHECK OPERATION END #########################


####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/19.0.0/db_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-01-26_02-51-34-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-01-26_02-51-34-PM.err'

######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2020-01-26_02-51-37PM.log

Network Configuration clean config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2020-01-26_02-51-37PM.log

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END


######################### DECONFIG CLEAN OPERATION END #########################


####################### DECONFIG CLEAN OPERATION SUMMARY #######################
#######################################################################


############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2020-01-26_02-51-17PM/response/deinstall_2020-01-26_02-51-34-PM.rsp
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DEINSTALL TOOL START ############





####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-01-26_02-51-34-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2020-01-26_02-51-34-PM.err'

######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to rac1
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2020-01-26_02-51-17PM/oraInst.loc
Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/19.0.0/db_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/19.0.0/db_1' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/middleware'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##


## [END] Oracle install clean ##


######################### DEINSTALL CLEAN OPERATION END #########################


####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/19.0.0/db_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/19.0.0/db_1' on the local node.
Oracle Universal Installer cleanup was successful.

Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'rac1'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL TOOL END #############

[oracle@rac1 ~]$


3. Verification

[oracle@rac1 ~]$ ls -ld /u01/app/oracle/product/19.0.0/db_1
ls: cannot access /u01/app/oracle/product/19.0.0/db_1: No such file or directory
[oracle@rac1 ~]$
[oracle@rac1 ~]$ cd /u01/app/oraInventory/ContentsXML
[oracle@rac1 ContentsXML]$ cat inventory.xml | grep -i "/u01/app/oracle/product/19.0.0/db_1"
[oracle@rac1 ContentsXML]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Install Oracle 19c database software in silent mode

Install Oracle 19c database software in silent mode on Linux x86_64

Pre-requisites already been taken care like memory, swap, kernel parameters, add groups, profile and users…etc

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. Hardware Requirements
3. Verify OS version
4. Oracle Installation Prerequisites
5. Download 19c db software
6. Unzip software
7. Backup response file
8. Modify the response file
9. Execute Pre-requisites
10. Install oracle software in Silent mode
11. Execute root.sh
12. Verify
___________________________________________________________________________________________________


1. Overview

Usually to install database software we will use ./runInstaller graphical user interface.

Some times we may not have access to a graphical user interface. 

Silent mode installation allows to configure necessary Oracle components without using graphical interface

In this response file can be used to provide all the required information for the installation, so no additional user input is required.


2. Hardware Requirements

The first thing we need to verify the hardware requirements for an Oracle 19c Release 3

— Check Physical RAM.
# grep MemTotal /proc/meminfo
We need at least 8192 MB of physical RAM. <----

— Check Swap Space.
# grep SwapTotal /proc/meminfo/*
RAM up to 1024MB then swap = 2 times the size of RAM
RAM between 2049MB and 8192MB then swap = equal to the size of RAM
RAM more than 8192MB then swap size = 0.75 times the size of RAM

We need at least 8192 MB of swap <----


-– Check space available in /tmp
# df -h /tmp/*
You need to have at least 2048 MB of space in the /tmp directory. <---

-– Check space for Oracle Software and pre-configured database.
# df -h

Space requirement for Oracle 19c Software:
Enterprise Edition 10G  <---- Minimum

[oracle@rac1 19.0.0]$ du -sh db_1/
9.9G    db_1/
[oracle@rac1 19.0.0]$

—- To determine whether the system architecture can run the software, enter the following command:

# grep "model name" /proc/cpuinfo

This command displays the processor type. Verify that the processor architecture matches the Oracle software release that you want to install. If you do not see the expected output, then you cannot install the software on this system.


3. Verify OS version

[oracle@rac1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.5
[oracle@rac1 ~]$


4. Oracle Installation Prerequisites

[root@rac1 ~]# yum install -y oracle-database-preinstall-19c
Loaded plugins: langpacks, ulninfo
ol7_UEKR4                                                       | 2.5 kB  00:00:00
ol7_latest                                                      | 2.7 kB  00:00:00
(1/4): ol7_UEKR4/x86_64/updateinfo                              |  87 kB  00:00:00
(2/4): ol7_UEKR4/x86_64/primary_db                              | 5.6 MB  00:00:02
(3/4): ol7_latest/x86_64/primary_db                             |  26 MB  00:00:04
(4/4): ol7_latest/x86_64/updateinfo                             | 2.6 MB  00:00:04
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                                 Arch                            Version                              Repository                           Size
========================================================================================================================================================================
Installing:
 oracle-database-preinstall-19c                          x86_64                          1.0-1.el7                            ol7_latest                           18 k

Transaction Summary
========================================================================================================================================================================
Install  1 Package

Total download size: 18 k
Installed size: 55 k
Downloading packages:
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm                                                                                              |  18 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-preinstall-19c-1.0-1.el7.x86_64                                                                                                      1/1
  Verifying  : oracle-database-preinstall-19c-1.0-1.el7.x86_64                                                                                                      1/1

Installed:
  oracle-database-preinstall-19c.x86_64 0:1.0-1.el7

Complete!
[root@rac1 ~]#


5. Download 19c db software

Download the Oracle software from OTN or MY ORACLE SUPPORT (MOS).

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html


6. Unzip software

NOTE: You can't edit oracle home location while installation using OUI. It will pickup automatically ORACLE HOME location, where you have unzipped database binaries. Hence directly unzip in ORACLE HOME location and then start ./runInstaller

After unzip, it will NOT keep all files in single directory like 10g,11g and 12c.

[oracle@rac1 dbhome_1]$ pwd
/u01/app/oracle/product/19.0.0/db_1
[oracle@rac1 db_1]$ ls -ltr
total 2987996
-rwxrwxr-x. 1 oracle oinstall 3059705302 Jan 24 20:25 LINUX.X64_193000_db_home.zip
[oracle@rac1 db_1]$
[oracle@rac1 db_1]$ unzip LINUX.X64_193000_db_home.zip
[oracle@rac1 db_1]$ 
[oracle@rac1 db_1]$ ls -ltr
total 2988120
-rw-r--r--.  1 oracle oinstall        852 Aug 18  2015 env.ora
-rw-r--r--.  1 oracle oinstall       2927 Oct 15  2016 schagent.conf
-rwxr-x---.  1 oracle oinstall       1783 Mar  9  2017 runInstaller
drwxr-x---. 14 oracle oinstall       4096 Apr 12  2019 OPatch
drwxr-x---.  7 oracle oinstall         69 Apr 17  2019 xdk
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 wwg
drwxr-xr-x.  4 oracle oinstall         31 Apr 17  2019 usm
drwxr-xr-x.  5 oracle oinstall         45 Apr 17  2019 suptools
drwxr-xr-x.  6 oracle oinstall         54 Apr 17  2019 srvm
drwxr-xr-x.  3 oracle oinstall         17 Apr 17  2019 sqlj
drwxr-xr-x.  4 oracle oinstall         41 Apr 17  2019 sqldeveloper
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 slax
-rw-r-----.  1 oracle oinstall         10 Apr 17  2019 root.sh.old.1
drwxr-xr-x.  3 oracle oinstall         21 Apr 17  2019 relnotes
drwxr-xr-x.  4 oracle oinstall         29 Apr 17  2019 racg
drwxr-xr-x.  5 oracle oinstall         52 Apr 17  2019 R
drwxr-xr-x.  5 oracle oinstall         39 Apr 17  2019 perl
drwxr-xr-x.  4 oracle oinstall         33 Apr 17  2019 owm
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 oss
drwxr-xr-x.  6 oracle oinstall         52 Apr 17  2019 ord
drwxr-xr-x.  4 oracle oinstall         34 Apr 17  2019 oracore
drwxr-xr-x.  7 oracle oinstall         65 Apr 17  2019 opmn
drwxr-xr-x.  5 oracle oinstall         42 Apr 17  2019 olap
drwxr-xr-x.  5 oracle oinstall         46 Apr 17  2019 nls
drwxr-xr-x.  4 oracle oinstall         31 Apr 17  2019 mgw
drwxr-xr-x.  9 oracle oinstall       4096 Apr 17  2019 md
drwxr-xr-x. 10 oracle oinstall       4096 Apr 17  2019 ldap
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 has
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 dv
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 diagnostics
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 demo
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 dbjava
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 data
drwxr-xr-x.  7 oracle oinstall         71 Apr 17  2019 cv
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 css
drwxr-xr-x.  6 oracle oinstall         55 Apr 17  2019 crs
drwxr-xr-x.  6 oracle oinstall         78 Apr 17  2019 plsql
drwxr-xr-x.  2 oracle oinstall         22 Apr 17  2019 dbs
drwxr-xr-x.  2 oracle oinstall         33 Apr 17  2019 utl
drwxr-xr-x.  2 oracle oinstall         29 Apr 17  2019 instantclient
drwxr-xr-x. 13 oracle oinstall       4096 Apr 17  2019 dmu
drwxr-xr-x.  3 oracle oinstall         35 Apr 17  2019 ucp
drwxr-xr-x.  3 oracle oinstall         35 Apr 17  2019 jdbc
drwxr-xr-x.  2 oracle oinstall         26 Apr 17  2019 QOpatch
drwxr-xr-x.  4 oracle oinstall         66 Apr 17  2019 ords
drwxr-xr-x.  5 oracle oinstall       4096 Apr 17  2019 sdk
drwxr-xr-x.  6 oracle oinstall       4096 Apr 17  2019 apex
drwxr-xr-x.  8 oracle oinstall       4096 Apr 17  2019 odbc
drwxr-xr-x.  2 oracle oinstall       4096 Apr 17  2019 jlib
drwxr-xr-x.  4 oracle oinstall         30 Apr 17  2019 drdaas
drwxr-xr-x. 11 oracle oinstall       4096 Apr 17  2019 ctx
-rwx------.  1 oracle oinstall        786 Apr 17  2019 root.sh.old
drwxr-xr-x. 10 oracle oinstall       4096 Apr 17  2019 network
drwxr-xr-x.  5 oracle oinstall         41 Apr 17  2019 hs
drwxr-xr-x.  9 oracle oinstall         93 Apr 17  2019 assistants
drwxr-xr-x.  6 oracle oinstall         53 Apr 18  2019 sqlplus
-rwx------.  1 oracle oinstall        638 Apr 18  2019 root.sh
drwxr-xr-x.  8 oracle oinstall       4096 Apr 18  2019 oui
drwxr-xr-x.  5 oracle oinstall       4096 Apr 18  2019 deinstall
drwxr-xr-x.  4 oracle oinstall         87 Apr 18  2019 clone
drwxr-xr-x.  2 oracle oinstall       4096 Apr 18  2019 addnode
drwxr-xr-x.  4 oracle oinstall       4096 Apr 18  2019 sqlpatch
drwxr-xr-x. 13 oracle oinstall       4096 Apr 18  2019 rdbms
drwxr-xr-x.  6 oracle oinstall         56 Apr 18  2019 precomp
drwxr-xr-x.  4 oracle oinstall      12288 Apr 18  2019 lib
drwxr-xr-x.  6 oracle oinstall       4096 Apr 18  2019 jdk
drwxr-xr-x.  8 oracle oinstall         82 Apr 18  2019 javavm
drwxr-x---. 13 oracle oinstall       4096 Apr 18  2019 inventory
drwxr-xr-x.  2 oracle oinstall       8192 Apr 18  2019 bin
drwxr-xr-x. 10 oracle oinstall       4096 Apr 18  2019 install
-rwxrwxr-x.  1 oracle oinstall 3059705302 Jan 24 20:25 LINUX.X64_193000_db_home.zip
[oracle@rac1 db_1]$


7. Backup response file (db_install.rsp)

[oracle@rac1 response]$ ls -ltr /u01/app/oracle/product/19.0.0/db_1/install/response/db_install.rsp
-rw-r--r--. 1 oracle oinstall 19932 Feb  6  2019 /u01/app/oracle/product/19.0.0/db_1/install/response/db_install.rsp
[oracle@rac1 response]$
[oracle@rac1 response]$ ls -ltr /u01/app/oracle/product/19.0.0/db_1/assistants/dbca/*.rsp
-rw-r-----. 1 oracle oinstall 25502 Apr  6  2019 /u01/app/oracle/product/19.0.0/db_1/assistants/dbca/dbca.rsp
[oracle@rac1 response]$
[oracle@rac1 response]$ ls -ltr /u01/app/oracle/product/19.0.0/db_1/assistants/netca/*.rsp
-rw-r-----. 1 oracle oinstall 6207 Apr  6  2019 /u01/app/oracle/product/19.0.0/db_1/assistants/netca/netca.rsp
[oracle@rac1 response]$


[oracle@rac1 response]$ cd /u01/app/oracle/product/19.0.0/db_1/install/response
[oracle@rac1 response]$ ls -ltr
total 20
-rw-r--r--. 1 oracle oinstall 19932 Feb  6  2019 db_install.rsp
[oracle@rac1 response]$
[oracle@rac1 response]$ cp db_install.rsp db_install.rsp.bkp
[oracle@rac1 response]$ ls -ltr
total 40
-rw-r--r--. 1 oracle oinstall 19932 Feb  6  2019 db_install.rsp
-rw-r--r--. 1 oracle oinstall 19932 Jan 26 12:40 db_install.rsp.bkp
[oracle@rac1 response]$


8. Modify the response file

[oracle@rac1 response]$ cat db_install.rsp
####################################################################
## Copyright(c) Oracle Corporation 1998,2019. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################


#------------------------------------------------------------------------------
# Do not change the following system generated value.
#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0

#-------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
#   - INSTALL_DB_SWONLY
#   - INSTALL_DB_AND_CONFIG
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY

#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory
#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#-------------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1

#-------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle

#-------------------------------------------------------------------------------
# Specify the installation edition of the component.
#
# The value should contain only one of these choices.
#   - EE     : Enterprise Edition
#   - SE2     : Standard Edition 2


#-------------------------------------------------------------------------------

oracle.install.db.InstallEdition=EE
###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
# Provide values for the OS groups to which SYSDBA and SYSOPER privileges     #
# needs to be granted. If the install is being performed as a member of the   #
# group "dba", then that will be used unless specified otherwise below.       #
#                                                                             #
# The value to be specified for OSDBA and OSOPER group is only for UNIX based #
# Operating System.                                                           #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# The OSDBA_GROUP is the OS group which is to be granted SYSDBA privileges.
#-------------------------------------------------------------------------------
oracle.install.db.OSDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSOPER_GROUP is the OS group which is to be granted SYSOPER privileges.
# The value to be specified for OSOPER group is optional.
#------------------------------------------------------------------------------
oracle.install.db.OSOPER_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSBACKUPDBA_GROUP is the OS group which is to be granted SYSBACKUP privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSBACKUPDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSDGDBA_GROUP is the OS group which is to be granted SYSDG privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSDGDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSKMDBA_GROUP is the OS group which is to be granted SYSKM privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSKMDBA_GROUP=oinstall

#------------------------------------------------------------------------------
# The OSRACDBA_GROUP is the OS group which is to be granted SYSRAC privileges.
#------------------------------------------------------------------------------
oracle.install.db.OSRACDBA_GROUP=oinstall
################################################################################
#                                                                              #
#                      Root script execution configuration                     #
#                                                                              #
################################################################################

#-------------------------------------------------------------------------------------------------------
# Specify the root script execution mode.
#
#   - true  : To execute the root script automatically by using the appropriate configuration methods.
#   - false : To execute the root script manually.
#
# If this option is selected, password should be specified on the console.
#-------------------------------------------------------------------------------------------------------
oracle.install.db.rootconfig.executeRootScript=false

#--------------------------------------------------------------------------------------
# Specify the configuration method to be used for automatic root script execution.
#
# Following are the possible choices:
#   - ROOT
#   - SUDO
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.configMethod=
#--------------------------------------------------------------------------------------
# Specify the absolute path of the sudo program.
#
# Applicable only when SUDO configuration method was chosen.
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.sudoPath=

#--------------------------------------------------------------------------------------
# Specify the name of the user who is in the sudoers list.
# Applicable only when SUDO configuration method was chosen.
# Note:For Single Instance database installations,the sudo user name must be the username of the user installing the database.
#--------------------------------------------------------------------------------------
oracle.install.db.rootconfig.sudoUserName=

###############################################################################
#                                                                             #
#                               Grid Options                                  #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# Value is required only if the specified install option is INSTALL_DB_SWONLY
#
# Specify the cluster node names selected during the installation.
#
# Example : oracle.install.db.CLUSTER_NODES=node1,node2
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=

###############################################################################
#                                                                             #
#                        Database Configuration Options                       #
#                                                                             #
###############################################################################

#-------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
#   - GENERAL_PURPOSE
#   - DATA_WAREHOUSE
# GENERAL_PURPOSE: A starter database designed for general purpose use or transaction-heavy applications.
# DATA_WAREHOUSE : A starter database optimized for data warehousing applications.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=

#-------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database SID.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=

#-------------------------------------------------------------------------------
# Specify whether the database should be configured as a Container database.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.ConfigureAsContainerDB=

#-------------------------------------------------------------------------------
# Specify the  Pluggable Database name for the pluggable database in Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.PDBName=

#-------------------------------------------------------------------------------
# Specify the Starter Database character set.
#
#  One of the following
#  AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
#  EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
#  BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
#  AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
#  IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
#  KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
#  ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=

#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=

#-------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=

#-------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto
# the starter database or not.
# The value can be either "true" or "false". If left blank it will be assumed
# to be "false".
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=

###############################################################################
#                                                                             #
# Passwords can be supplied for the following four schemas in the             #
# starter database:                                                           #
#   SYS                                                                       #
#   SYSTEM                                                                    #
#   DBSNMP (used by Enterprise Manager)                                       #
#                                                                             #
# Same password can be used for all accounts (not recommended)                #
# or different passwords for each account can be provided (recommended)       #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=

#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=

#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=

#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=

#-------------------------------------------------------------------------------
# Specify the PDBADMIN password required for creation of Pluggable Database in the Container Database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.PDBADMIN=

#-------------------------------------------------------------------------------
# Specify the management option to use for managing the database.
# Options are:
# 1. CLOUD_CONTROL - If you want to manage your database with Enterprise Manager Cloud Control along with Database Express.
# 2. DEFAULT   -If you want to manage your database using the default Database Express option.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.managementOption=

#-------------------------------------------------------------------------------
# Specify the OMS host to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsHost=

#-------------------------------------------------------------------------------
# Specify the OMS port to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.omsPort=

#-------------------------------------------------------------------------------
# Specify the EM Admin user name to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminUser=

#-------------------------------------------------------------------------------
# Specify the EM Admin password to use to connect to Cloud Control.
# Applicable only when oracle.install.db.config.starterdb.managementOption=CLOUD_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.emAdminPassword=

###############################################################################
#                                                                             #
# SPECIFY RECOVERY OPTIONS                                                    #
# ------------------------------------                                        #
# Recovery options for the database can be mentioned using the entries below  #
#                                                                             #
###############################################################################

#------------------------------------------------------------------------------
# This variable is to be set to false if database recovery is not required. Else
# this can be set to true.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableRecovery=

#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
#   - FILE_SYSTEM_STORAGE
#   - ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=

#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

#-------------------------------------------------------------------------------
# Specify the recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
# Applicable only when oracle.install.db.config.starterdb.storageType=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=

#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_STORAGE
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=
[oracle@rac1 response]$


9. Execute Pre-requisites

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/db_1/
[oracle@rac1 db_1]$ ls -ltr runInstaller
-rwxr-x---. 1 oracle oinstall 1783 Mar  9  2017 runInstaller
[oracle@rac1 db_1]$

[oracle@rac1 db_1]$ ./runInstaller -executePrereqs -silent -responseFile /u01/app/oracle/product/19.0.0/db_1/install/response/db_install.rsp
Launching Oracle Database Setup Wizard...

Prerequisite checks executed successfully.
[oracle@rac1 db_1]$


10. Install oracle software in Silent mode

[oracle@rac1 db_1]$ ./runInstaller -silent -responseFile /u01/app/oracle/product/19.0.0/db_1/install/response/db_install.rsp
Launching Oracle Database Setup Wizard...

The response file for this session can be found at:
 /u01/app/oracle/product/19.0.0/db_1/install/response/db_2020-01-26_12-57-15PM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/InstallActions2020-01-26_12-57-15PM/installActions2020-01-26_12-57-15PM.log

As a root user, execute the following script(s):
        1. /u01/app/oracle/product/19.0.0/db_1/root.sh

Execute /u01/app/oracle/product/19.0.0/db_1/root.sh on the following nodes:
[rac1]

Successfully Setup Software. <-----
[oracle@rac1 db_1]$


11. Execute root.sh

[root@rac1 ~]# /u01/app/oracle/product/19.0.0/db_1/root.sh
Check /u01/app/oracle/product/19.0.0/db_1/install/root_rac1_2020-01-26_13-00-18-602822551.log for the output of root script
[root@rac1 ~]#
[root@rac1 ~]# cat /u01/app/oracle/product/19.0.0/db_1/install/root_rac1_2020-01-26_13-00-18-602822551.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/19.0.0/db_1
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script. <----
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA) is available at : /u01/app/oracle/product/19.0.0/db_1/bin/tfactl
[root@rac1 ~]#


12. Verify

[oracle@rac1 db_1]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1
[oracle@rac1 db_1]$ export PATH=/u01/app/oracle/product/19.0.0/db_1/bin:$PATH;
[oracle@rac1 db_1]$ which sqlplus
/u01/app/oracle/product/19.0.0/db_1/bin/sqlplus
[oracle@rac1 db_1]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 13:06:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name:

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Install 19c

Step by Step Install Oracle 19c Release 3 on Linux 7.5

Pre-requisites already been taken care like memory, swap, kernel parameters, add groups, profile and users…etc

Table of Contents
___________________________________________________________________________________________________

1. Hardware Requirements
2. Verify OS version
3. Download 19c db software
4. Unzip
5. Oracle Installation Prerequisites
6. Set the DISPLAY Environment
7. Invoke ./runInstaller
8. Verify
___________________________________________________________________________________________________


1. Hardware Requirements

The first thing we need to verify the hardware requirements for an Oracle 19c Release 3

— Check Physical RAM.
# grep MemTotal /proc/meminfo
We need at least 8192 MB of physical RAM. <----

— Check Swap Space.
# grep SwapTotal /proc/meminfo/*
RAM up to 1024MB then swap = 2 times the size of RAM
RAM between 2049MB and 8192MB then swap = equal to the size of RAM
RAM more than 8192MB then swap size = 0.75 times the size of RAM

We need at least 8192 MB of swap <----


-– Check space available in /tmp
# df -h /tmp/*
You need to have at least 2048 MB of space in the /tmp directory. <---

-– Check space for Oracle Software and pre-configured database.
# df -h

Space requirement for Oracle 19c Software:
Enterprise Edition 10G  <---- Minimum

[oracle@rac1 19.0.0]$ du -sh dbhome_1
9.9G    dbhome_1
[oracle@rac1 19.0.0]$

—- To determine whether the system architecture can run the software, enter the following command:

# grep "model name" /proc/cpuinfo

This command displays the processor type. Verify that the processor architecture matches the Oracle software release that you want to install. If you do not see the expected output, then you cannot install the software on this system.


2. Verify OS version

[oracle@rac1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.5
[oracle@rac1 ~]$


3. Download Software

Download the Oracle software from OTN or MY ORACLE SUPPORT (MOS).

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html


4. Unzip Software

NOTE: You can't edit oracle home location while installation using OUI. It will pickup automatically ORACLE HOME location, where you have unzipped database binaries. Hence directly unzip in ORACLE HOME location and then start ./runInstaller

After unzip, it will NOT keep all files in single directory like 10g,11g and 12c.

[oracle@rac1 dbhome_1]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 dbhome_1]$ ls -ltr
-rwxrwxr-x. 1 oracle oinstall 3059705302 Jan 24 20:25 LINUX.X64_193000_db_home.zip
[oracle@rac1 dbhome_1]$

[oracle@rac1 dbhome_1]$ unzip LINUX.X64_193000_db_home.zip

[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/
[oracle@rac1 dbhome_1]$ ls -ltr
total 2988120
-rw-r--r--.  1 oracle oinstall        852 Aug 18  2015 env.ora
-rw-r--r--.  1 oracle oinstall       2927 Oct 15  2016 schagent.conf
-rwxr-x---.  1 oracle oinstall       1783 Mar  9  2017 runInstaller
drwxr-x---. 14 oracle oinstall       4096 Apr 12  2019 OPatch
drwxr-x---.  7 oracle oinstall         69 Apr 17  2019 xdk
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 wwg
drwxr-xr-x.  4 oracle oinstall         31 Apr 17  2019 usm
drwxr-xr-x.  5 oracle oinstall         45 Apr 17  2019 suptools
drwxr-xr-x.  6 oracle oinstall         54 Apr 17  2019 srvm
drwxr-xr-x.  3 oracle oinstall         17 Apr 17  2019 sqlj
drwxr-xr-x.  4 oracle oinstall         41 Apr 17  2019 sqldeveloper
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 slax
-rw-r-----.  1 oracle oinstall         10 Apr 17  2019 root.sh.old.1
drwxr-xr-x.  3 oracle oinstall         21 Apr 17  2019 relnotes
drwxr-xr-x.  4 oracle oinstall         29 Apr 17  2019 racg
drwxr-xr-x.  5 oracle oinstall         52 Apr 17  2019 R
drwxr-xr-x.  5 oracle oinstall         39 Apr 17  2019 perl
drwxr-xr-x.  4 oracle oinstall         33 Apr 17  2019 owm
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 oss
drwxr-xr-x.  6 oracle oinstall         52 Apr 17  2019 ord
drwxr-xr-x.  4 oracle oinstall         34 Apr 17  2019 oracore
drwxr-xr-x.  7 oracle oinstall         65 Apr 17  2019 opmn
drwxr-xr-x.  5 oracle oinstall         42 Apr 17  2019 olap
drwxr-xr-x.  5 oracle oinstall         46 Apr 17  2019 nls
drwxr-xr-x.  4 oracle oinstall         31 Apr 17  2019 mgw
drwxr-xr-x.  9 oracle oinstall       4096 Apr 17  2019 md
drwxr-xr-x. 10 oracle oinstall       4096 Apr 17  2019 ldap
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 has
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 dv
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 diagnostics
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 demo
drwxr-xr-x.  3 oracle oinstall         19 Apr 17  2019 dbjava
drwxr-xr-x.  3 oracle oinstall         20 Apr 17  2019 data
drwxr-xr-x.  7 oracle oinstall         71 Apr 17  2019 cv
drwxr-xr-x.  3 oracle oinstall         18 Apr 17  2019 css
drwxr-xr-x.  6 oracle oinstall         55 Apr 17  2019 crs
drwxr-xr-x.  6 oracle oinstall         78 Apr 17  2019 plsql
drwxr-xr-x.  2 oracle oinstall         22 Apr 17  2019 dbs
drwxr-xr-x.  2 oracle oinstall         33 Apr 17  2019 utl
drwxr-xr-x.  2 oracle oinstall         29 Apr 17  2019 instantclient
drwxr-xr-x. 13 oracle oinstall       4096 Apr 17  2019 dmu
drwxr-xr-x.  3 oracle oinstall         35 Apr 17  2019 ucp
drwxr-xr-x.  3 oracle oinstall         35 Apr 17  2019 jdbc
drwxr-xr-x.  2 oracle oinstall         26 Apr 17  2019 QOpatch
drwxr-xr-x.  4 oracle oinstall         66 Apr 17  2019 ords
drwxr-xr-x.  5 oracle oinstall       4096 Apr 17  2019 sdk
drwxr-xr-x.  6 oracle oinstall       4096 Apr 17  2019 apex
drwxr-xr-x.  8 oracle oinstall       4096 Apr 17  2019 odbc
drwxr-xr-x.  2 oracle oinstall       4096 Apr 17  2019 jlib
drwxr-xr-x.  4 oracle oinstall         30 Apr 17  2019 drdaas
drwxr-xr-x. 11 oracle oinstall       4096 Apr 17  2019 ctx
-rwx------.  1 oracle oinstall        786 Apr 17  2019 root.sh.old
drwxr-xr-x. 10 oracle oinstall       4096 Apr 17  2019 network
drwxr-xr-x.  5 oracle oinstall         41 Apr 17  2019 hs
drwxr-xr-x.  9 oracle oinstall         93 Apr 17  2019 assistants
drwxr-xr-x.  6 oracle oinstall         53 Apr 18  2019 sqlplus
-rwx------.  1 oracle oinstall        638 Apr 18  2019 root.sh
drwxr-xr-x.  8 oracle oinstall       4096 Apr 18  2019 oui
drwxr-xr-x.  5 oracle oinstall       4096 Apr 18  2019 deinstall
drwxr-xr-x.  4 oracle oinstall         87 Apr 18  2019 clone
drwxr-xr-x.  2 oracle oinstall       4096 Apr 18  2019 addnode
drwxr-xr-x.  4 oracle oinstall       4096 Apr 18  2019 sqlpatch
drwxr-xr-x. 13 oracle oinstall       4096 Apr 18  2019 rdbms
drwxr-xr-x.  6 oracle oinstall         56 Apr 18  2019 precomp
drwxr-xr-x.  4 oracle oinstall      12288 Apr 18  2019 lib
drwxr-xr-x.  6 oracle oinstall       4096 Apr 18  2019 jdk
drwxr-xr-x.  8 oracle oinstall         82 Apr 18  2019 javavm
drwxr-x---. 13 oracle oinstall       4096 Apr 18  2019 inventory
drwxr-xr-x.  2 oracle oinstall       8192 Apr 18  2019 bin
drwxr-xr-x. 10 oracle oinstall       4096 Apr 18  2019 install
-rwxrwxr-x.  1 oracle oinstall 3059705302 Jan 24 20:25 LINUX.X64_193000_db_home.zip
drwxr-xr-x.  3 oracle oinstall         17 Jan 26 02:03 cfgtoollogs
[oracle@rac1 dbhome_1]$


5. Oracle Installation Prerequisites

[root@rac1 ~]# yum install -y oracle-database-preinstall-19c
Loaded plugins: langpacks, ulninfo
ol7_UEKR4                                                       | 2.5 kB  00:00:00
ol7_latest                                                      | 2.7 kB  00:00:00
(1/4): ol7_UEKR4/x86_64/updateinfo                              |  87 kB  00:00:00
(2/4): ol7_UEKR4/x86_64/primary_db                              | 5.6 MB  00:00:02
(3/4): ol7_latest/x86_64/primary_db                             |  26 MB  00:00:04
(4/4): ol7_latest/x86_64/updateinfo                             | 2.6 MB  00:00:04
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                                                 Arch                            Version                              Repository                           Size
========================================================================================================================================================================
Installing:
 oracle-database-preinstall-19c                          x86_64                          1.0-1.el7                            ol7_latest                           18 k

Transaction Summary
========================================================================================================================================================================
Install  1 Package

Total download size: 18 k
Installed size: 55 k
Downloading packages:
oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm                                                                                              |  18 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-preinstall-19c-1.0-1.el7.x86_64                                                                                                      1/1
  Verifying  : oracle-database-preinstall-19c-1.0-1.el7.x86_64                                                                                                      1/1

Installed:
  oracle-database-preinstall-19c.x86_64 0:1.0-1.el7

Complete!
[root@rac1 ~]#


6. Set the DISPLAY Environment

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

[oracle@rac1 dbhome_1]$ export DISPLAY=192.168.2.1:0.0;


7. Invoke ./runInstaller

Start the Oracle Universal Installer (OUI) by issuing the following command.



























8. Verify

[oracle@rac1 bin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/bin
[oracle@rac1 bin]$
[oracle@rac1 bin]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@rac1 bin]$
[oracle@rac1 bin]$ ./sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 26 02:26:02 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name:

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/