Configure Data guard physical Standby

Oracle Dataguard physical Standby configuration in 19c:

Overview steps:

Step:1 we have to enable the archive log mode ,flashback  on primary databases.

Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.

Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.

Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.

Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.

Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)

Step:7 create the directory structure in standby similar to the primary side.

Step:8 Start the standby side database in nomount using the pfile.

Step:9 Connect to the RMAN and duplicate the primary database using the command

duplicate target database for standby from active database dorecover nofilenamecheck;

Step:10 Now our standby database is ready for read only purpose.

Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.

Step:12 Verify the current log sequence in both primary and standby sides.

Do the following set of changes in primary side:

Oracle Active Data Guard:

Active Data Guard is a licensed option for Oracle Database Enterprise Edition. … Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo – the information used by every Oracle Database to protect transactions – and applying it to the standby database.

Difference between Oracle Data Guard and Active Data Guard?

Oracle Active Data Guard provides the best data protection and availability for Oracle Database. Active Data Guard enables the offloading of read-only operations, backups, and so on, to an up-to-date physical standby database while also providing disaster protection.

Step:1 Enable the archive log  mode in the primary database.

SQL> alter database archivelog;

Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5

Step:2 Enable the force logging.

SQL>alter database force logging;

Database altered

Step:3 Flashback should be enabled in primary db.

ALTER DATABASE FLASHBACK ON;

Database altered

Check the archive log,force_logging,flashback is enabled.

SQL> select name,force_logging,flashback_on,log_mode from v$database;

   NAME            FORCE_LOGGING       FLASHBACK_ON    LOG_MODE       
--------- ---------------------------  ------------   -------------
CANADA                  YES                  YES       ARCHIVELOG

Step:4 Add the Standby redo log files for standby side.

SQL> alter database add standby logfile thread 1 
group 4('/u01/app/oracle/oradata/redo04.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 5('/u01/app/oracle/oradata/redo05.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 6('/u01/app/oracle/oradata/redo06.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 7('/u01/app/oracle/oradata/redo07.log')size 50m;

Database altered.

Step:5  set the log archive config parameter.

SQL> alter system set log_archive_config='DG_config=(canada,standby)';

System altered.
SQL> show parameter log_archive_config

  NAME                                TYPE                VALUE
--------------------------------- ----------- ------------------------------
log_archive_config                  string      DG_config=(canada,standby)
SQL> show parameter log_archive_dest_2

NAME                                TYPE               VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2                string    SERVICE=standby NOAFFIRM ASYNC
                                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                 DB_UNIQUE_NAME=standby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string

NAME                                TYPE               VALUE
-------------------------------- ----------- -----------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string


Step:6 Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

Step:7 configure the log_archive_process to 30 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES

SQL> alter system set log_archive_max_processes=30;

System altered.

Step:8 register the fal_server and fal_client as standby(standby) and physical(primary) databases respectively.

SQL> alter system set fal_server=standby;

System altered.

SQL> alter system set fal_client=physical;

System altered.

Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.

SQL> alter system set standby_file_management=auto;

System altered.

Step:10 change the remote_login_password file as exclusive.

SQL> show parameter remote_login_passwordfile

    NAME                                TYPE                 VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile              string              EXCLUSIVE

Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a new datafile and logfile respectively on the primary database to a filename on the standby database.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

Step:12 Configure the tns and listener files on both primary and standby databases.

listener entry in the primary database.

tnsnames entry in the primary database.

listener entry in the standby database.

tnsnames entry in the standby database.

Step:13  ping your primary and standby databases from one to another.

Step:14 create the similar directory structures in standby side as of in primary.

/u01/app/oracle/admin/standby/adump

/u01/app/oracle/oradata/standby

/u01/app/oracle/fast_recovery_area/standby

Step:15 Transfer the password files,pfiles  to the standby side through scp.

Step:16 create the initcanada.ora file containing the single parameter db_name=canada in the standby side it is useful to start our database in no mount stage.

Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.

Step:18 Duplicate the target database using the following command,

duplicate target database for standby from active database 
dorecover nofilenamecheck;
[oracle@localhost admin]$ 
rman target sys/Pass#1234@canada auxiliary sys/Pass#1234@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 17 07:28:58 2021
Version 19.3.0.0.0

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

connected to target database: CANADA (DBID=215384084)
connected to auxiliary database: CANADA (not mounted)

RMAN> 
duplicate target database for standby from active database dorecover nofilenamecheck;

Starting Duplicate Db at 17-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwcanada';
}
executing Memory Script

Starting backup at 17-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
Finished backup at 17-FEB-21

contents of Memory Script:
{
restore clone from service 'canada' standby controlfile;
}
executing Memory Script

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/cntrlcanada.dbf
Finished restore at 17-FEB-21

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp 
conflicts with a file used by the target database

contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf";
restore
from nonsparse from service 
'canada' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service 'canada' 
archivelog from scn 2091702;
switch clone datafile all;
}
executing Memory Script

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1064734612 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf

contents of Memory Script:
{
set until scn 2092999;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-FEB-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
archived log for thread 1 with sequence 11 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf 
thread=1 sequence=10
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf 
thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-21

contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script

released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_10_1064697431.dbf RECID=1 STAMP=1064734609
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_11_1064697431.dbf RECID=2 STAMP=1064734611
Deleted 2 objects

Finished duplicate at 17-FEB-21

Yes,we have created the physical standby database successfully.

Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby.

Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.

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

MAX(SEQUENCE#)
--------------
       11

Step:21 Apply the log files using MRP process.

SQL> alter database recover managed standby database using current 
logfile disconnect;


Database altered.

Step:22 check for the sequence no which is applied in standby.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log 
ORDER BY sequence#;

SEQUENCE#    FIRST_TIM  NEXT_TIME APPLIED
----------   --------- --------- ---------
     10      17-FEB-21 17-FEB-21    YES
     11      17-FEB-21 17-FEB-21    YES

Step:23 We can check the difference in sequence using the following query.

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; 
SQL> 

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

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.
begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please  check the link for further.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Create and Configure ACLs in Oracle database

Let us see how to create and configure ACLs in Oracle database.

ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “CINP01314”, line 255
ORA-06512: at line 21

We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Below Steps need to follow configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

— Drop an access control list :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => '/sys/acls/utl_smtp.xml');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

— TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01', -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

— If we want to delete or revoke this permission :-

BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => '/sys/acls/utl_smtp.xml',
principal => 'INDIA',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for INDIA user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;
HOST                   LOWER_PORT      UPPER_PORT       PRIVILEGE       STATUS
------------------- -----------------  ---------------  ----------  ------------
10.0.06.01                25               25          connect           GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

–Status column must have value granted.

SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.

ACL                     PRINCIPAL    PRIVILEGE     IS_GRANT   START_DATE   END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml    INDIA       connect        true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-

ACL                     PRINCIPAL      PRIVILEGE     IS_GRANT    START_DATE    END_DATE
---------------------   -----------   -----------    --------     --------     -------
/sys/acls/utl_mail.xml       INDIA       connect      true
/sys/acls/utl_smtp.xml       INDIA       connect      true
/sys/acls/utl_tcp.xml        INDIA       connect      true

TO enable this ACL permission for a different USER other than above user(INDIA) :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.
begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_smtp.xml',
host => '10.0.06.01',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;

ACL                      PRINCIPAL      PRIVILEGE     IS_GRANT   START_DATE   END_DATE
-----------------       -------------   -----------   ----------  --------   ----------
/sys/acls/utl_smtp.xml     INDIA         connect        true
/sys/acls/utl_smtp.xml     TEST          connect        false

— This query has to be run with TEST user and Status column should have value “GRANTED

SQL> SELECT host, lower_port, upper_port, privilege, status FROM 
user_network_acl_privileges;

HOST                      LOWER_PORT       UPPER_PORT        PRIVILEGE      STATUS
--------------------       ----------     -----------       ---------      -------
10.0.06.01                    25              25              connect       GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED

Now check your procedure to send mail through test user , it will work.

After this configuration is completed. we can send a mail with and without attachment. Please  check the link for further.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SEND MAIL USING UTL_SMTP

UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).

We can send two types of mails using UTL_SMTP:-

  • Mails without attachment.
  • Mails with attachment.

Mails without attachment :-

SQL> DECLARE c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access
denied by access control list (ACL)
ORA-06512: at line 25

If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.

Once we configure ACL then we are able to successfully send mails using above mentioned code.

Please follow below steps :-

SQL> DECLARE
c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

PL/SQL procedure successfully completed.

Mail has been send now.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SEND MAIL USING UTL_SMTP

UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).

We can send two types of mails using UTL_SMTP:-

  • Mails without attachment.
  • Mails with attachment.

Mails without attachment :-

SQL> DECLARE c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

DECLARE
*
ERROR at line 1:
ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access
denied by access control list (ACL)
ORA-06512: at line 25

If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.

Once we configure ACL then we are able to successfully send mails using above mentioned code.

Please follow below steps :-

SQL> DECLARE
c UTL_SMTP.CONNECTION;

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;

BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; 
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

PL/SQL procedure successfully completed.

Mail has been send now.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SEND MAIL USING UTL_SMTP with attachment

 

 

 

UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).

We can send two types of mails using UTL_SMTP:-

  • Mails without attachment.
  • Mails with attachment.

Sending Mails with attachment :-

1. First check UTL_FILE_DIR parameter in Oracle Database :-

SQL> show parameter utl_file

NAME                                        TYPE                               VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir                               string                              /america/report

/america/report has to be physically present in Files and attachment need to be mailed has to be present in this directory.

2. Create a package to send mail with attachment :-

CREATE OR REPLACE PACKAGE AMERICA_ATTACH IS
smtp_port NUMBER := 25;
boundary CONSTANT VARCHAR2(256) := 'CES.Boundary.DACA587499938898';
crlf VARCHAR2(2):= CHR(13) || CHR(10);
my_code NUMBER;
my_errm VARCHAR2(32767);
PROCEDURE AMERICA_ATTACH_EMAIL(smtp VARCHAR2,
from_name VARCHAR2,to_names VARCHAR2,subject VARCHAR2 DEFAULT '',
message VARCHAR2 DEFAULT NULL,cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,filename VARCHAR2 DEFAULT NULL,
filetype VARCHAR2 DEFAULT 'text/plain');
FUNCTION AMERICA_ATTACH_ADDRESS(smtp_host IN VARCHAR2,addr_list IN OUT VARCHAR2) RETURN VARCHAR2 ;
PROCEDURE AMERICA_ATTACH_SPLITPATH(file_path IN VARCHAR2, directory_path OUT VARCHAR2,file_name OUT VARCHAR2);
PROCEDURE AMERICA_ATTACH_APPEND(directory_path IN VARCHAR2, file_name IN VARCHAR2,file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.CONNECTION);
END AMERICA_ATTACH;
/

 

CREATE OR REPLACE PACKAGE BODY AMERICA_ATTACH AS 
FUNCTION AMERICA_ATTACH_ADDRESS (smtp_host IN VARCHAR2, addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;

FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
IS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);

WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);

IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; --
END IF;

GOTO next_char;
END IF;

IF (c = '"')
THEN
inside_quote := TRUE;
GOTO next_char;
END IF;

IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;

<<next_char>>
i := i + 1;
END LOOP;

RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');

IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char (addr, '<');

IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');

IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;

i := lookup_unquoted_char (addr, '@');

IF (i = 0 AND smtp_host != 'localhost')
THEN
i := INSTR (smtp_host, '.', -1, 2);
addr := addr || '@' || SUBSTR (smtp_host, i + 1);
END IF;

addr := '<' || addr || '>';
RETURN addr;
END;
PROCEDURE AMERICA_ATTACH_SPLITPATH (
file_path IN VARCHAR2,
directory_path OUT VARCHAR2,
file_name OUT VARCHAR2
)
IS
pos NUMBER;
BEGIN
pos := INSTR (file_path, '/', -1);

IF pos = 0
THEN
pos := INSTR (file_path, '\', -1);
END IF;

IF pos = 0
THEN
directory_path := NULL;
ELSE
directory_path := SUBSTR (file_path, 1, pos - 1);
END IF;
file_name := SUBSTR (file_path, pos + 1);
END;
PROCEDURE AMERICA_ATTACH_APPEND (
directory_path IN VARCHAR2,
file_name IN VARCHAR2,
file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.connection
)
IS
file_handle UTL_FILE.file_type;
bfile_handle BFILE;
bfile_len NUMBER;
pos NUMBER;
read_bytes NUMBER;
line VARCHAR2 (1000);
DATA RAW (200);
my_code NUMBER;
my_errm VARCHAR2 (32767);
directory_name VARCHAR2 (30);
BEGIN
BEGIN
BEGIN
line := directory_path;
SELECT dd.directory_name
INTO directory_name
FROM SYS.all_directories dd
WHERE dd.directory_path = line AND ROWNUM =1;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (line || 'does not have valid directorty');

IF SUBSTR (file_type, 1, 4) != 'text'
THEN
bfile_handle := BFILENAME (directory_name, file_name);
bfile_len := DBMS_LOB.getlength (bfile_handle);
pos := 1;
DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly);
ELSE
file_handle := UTL_FILE.fopen (directory_name, file_name, 'r');
END IF;

LOOP
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
IF pos + 57 - 1 > bfile_len
THEN
read_bytes := bfile_len - pos + 1;
ELSE
read_bytes := 57;
END IF;

DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA);
UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (DATA));
pos := pos + 57;

IF pos > bfile_len
THEN
EXIT;
END IF;
ELSE
UTL_FILE.get_line (file_handle, line);
UTL_SMTP.write_data (conn, line || crlf);
END IF;
END LOOP;
-- Output any errors, except at end when no more data is found
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm);
END;

-- Close the file (binary or text)
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
DBMS_LOB.CLOSE (bfile_handle);
ELSE
UTL_FILE.fclose (file_handle);
END IF;
END;
PROCEDURE AMERICA_ATTACH_EMAIL (
smtp VARCHAR2,
from_name VARCHAR2,
to_names VARCHAR2,
subject VARCHAR2 DEFAULT '',
MESSAGE VARCHAR2 DEFAULT NULL,
cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,
filename VARCHAR2 DEFAULT NULL,
filetype VARCHAR2 DEFAULT 'text/plain'
)
IS
smtp_host VARCHAR2 (256) := smtp;
recipients VARCHAR2 (32767);
directory_path VARCHAR2 (256);
file_name VARCHAR2 (256);
mesg VARCHAR2 (32767);
conn UTL_SMTP.connection;
i BINARY_INTEGER;
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, smtp_port);
UTL_SMTP.helo (conn, smtp_host);
recipients := from_name;
UTL_SMTP.mail (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
recipients := to_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;

recipients := cc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;

recipients := bcc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;

UTL_SMTP.open_data (conn);
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| from_name
|| crlf
|| 'Subject: '
|| subject
|| crlf
|| 'To: '
|| to_names
|| crlf;

IF cc_names IS NOT NULL
THEN
mesg := mesg || 'Cc: ' || cc_names || crlf;
END IF;

IF bcc_names IS NOT NULL
THEN
mesg := mesg || 'Bcc: ' || bcc_names || crlf;
END IF;

mesg :=
mesg
|| 'Mime-Version: 1.0'
|| crlf
|| 'Content-Type: multipart/mixed; boundary="'
|| boundary
|| '"'
|| crlf
|| crlf
|| 'This is a Mime message.'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

IF MESSAGE IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/plain'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);
UTL_SMTP.write_data (conn, MESSAGE || crlf);
END IF;

IF filename IS NOT NULL
THEN
AMERICA_ATTACH_SPLITPATH (filename, directory_path, file_name);
mesg := crlf || '--' || boundary || crlf;

IF SUBSTR (filetype, 1, 4) != 'text'
THEN
mesg :=
mesg
|| 'Content-Type: '
|| filetype
|| '; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: base64'
|| crlf
|| crlf;
ELSE
mesg :=
mesg
|| 'Content-Type: application/octet-stream; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
END IF;

UTL_SMTP.write_data (conn, mesg);
AMERICA_ATTACH_APPEND (directory_path, file_name, filetype, conn);
UTL_SMTP.write_data (conn, crlf);
END IF;

mesg := crlf || '--' || boundary || '--' || crlf;
UTL_SMTP.write_data (conn, mesg);
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
my_code := SQLCODE;
my_errm := SQLERRM;

BEGIN
UTL_SMTP.quit (conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
NULL;
END;

raise_application_error
(-20000,
'Failed to send mail - SMTP server down or unavailable: Error code '
|| my_code
|| ': '
|| my_errm
);
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
raise_application_error (-20000,
'Failed to send mail: Error code '
|| my_code
|| ': '
|| my_errm
);
END;
END AMERICA_ATTACH;
/

3. Call Email procedure to send america.xls file to desired user :-

DECLARE
SMTP VARCHAR2(200);
FROM_NAME VARCHAR2(200);
TO_NAME VARCHAR2(2000);
SUBJECT VARCHAR2(200);
MESSAGE VARCHAR2(2000);
CC_NAMES VARCHAR2(200);
BCC_NAMES VARCHAR2(200);
FILENAME VARCHAR2(200);
FILETYPE VARCHAR2(200);

BEGIN
SMTP := '10.10.4.10';
FROM_NAME := '[email protected]';
TO_NAME := '[email protected]';
SUBJECT := 'TEST FILE';
MESSAGE := 'Hi this is a test message.'; CC_NAMES := NULL;
BCC_NAMES := NULL;
FILETYPE := 'text';
SELECT ' /america/report/america.xls' INTO FILENAME FROM dual;
AMERICA_ATTACH.AMERICA_ATTACH_EMAIL ( SMTP, FROM_NAME, TO_NAME, SUBJECT, MESSAGE, CC_NAMES, BCC_NAMES, FILENAME, FILETYPE );
COMMIT;
END;
/

PL/SQL procedure successfully completed.

Mail has been sent with attachment america.xls.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Database Upgrade from 12c to 19c using Datapump

Database Upgrade from 12c to 19c using Datapump

Overview steps:

Step:1 Check the database size in source.
Step:2 check which tablespace holds the schema objects.
Step:3 compile invalid objects in source.
step:4 check the  count of invalid dba_objects in the source.
Step:5 create a directory for export purpose both in OS level and database level.
Step:6 Estimate the size of dumpfile, so that we can know when will the export get completed.
Step:7 export the database(TANSTAL).
Step:8 create a fresh database for import.
Step:9 Now create a directory in both OS level and database level for import purpose in the newly created database.
Step:10 import the database.(ZHIGOMA)
Step:11 Post upgrade steps in target database.
               11.1 compile invalid objects in target database.
               11.2 check whether any invalid objects present.
               11.3 Run the query to check for currently installed database components.

Step:12  Verify the timezone of the upgraded database.

Step:13  Check the CONSTRAINTS count in both source & target.it is used to display the constraints that are defined in the database.

Step:1 Check the database size in source .

SQL> col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Database Size             Used space         Free space
-------------------- -------------------- --------------------
    2 GB                    2 GB               0 GB

SQL>

Step:2 execute the following query to check which tablespace holds the schema objects.

set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;

Step:3 compile invalid objects in source to reduce dependencies, so that we can find the best way to recompile it.

SQL> @?/rdbms/admin/utlrp.sql

COMP_TIMESTAMP UTLRP_BGN 2021-01-31 20:19:36

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.

COMP_TIMESTAMP UTLRP_END 2021-01-31 20:19:39

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

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

0

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

step:4 check the  count of invalid dba_objects in the source.

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

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

Step:5 create a directory for export purpose both in OS level and database level.

mkdir /u01/export

create directory export as '/u01/export';

Directory created

[oracle@orcldbs ~]$ mkdir export
[oracle@orcldbs ~]$ ls
12c.env Documents export oraprod.env Templates
19c.env Downloads Music Pictures utlrp.out
Desktop em13400_linux64-3.zip oradiag_oracle Public Videos

Create  a table employee in the user : rahul.

Step:6 Estimate the size of dumpfile by this we are able to know the completion time of export activity.

[oracle@orcldbs export]$ expdp directory=export full=Y nologfile=Y estimate_only=Y

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:09:14 2021

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

Username: system
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=export full=Y nologfile=Y estimate_only=Y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "SYS"."KU$_USER_MAPPING_VIEW" 64 KB
. estimated "ORDDATA"."ORDDCM_DOCS" 1.25 MB
. estimated "WMSYS"."WM$CONSTRAINTS_TABLE$" 320 KB
. estimated "SYS"."AUD$" 256 KB
. estimated "WMSYS"."WM$LOCKROWS_INFO$" 192 KB
. estimated "WMSYS"."WM$UDTRIG_INFO$" 192 KB
. estimated "LBACSYS"."OLS$AUDIT_ACTIONS" 64 KB
. estimated "LBACSYS"."OLS$DIP_EVENTS" 64 KB
. estimated "LBACSYS"."OLS$INSTALLATIONS" 64 KB
. estimated "LBACSYS"."OLS$PROPS" 64 KB
. estimated "SYS"."DAM_CLEANUP_EVENTS$" 64 KB
. estimated "SYS"."DAM_CLEANUP_JOBS$" 64 KB
. estimated "SYS"."DAM_CONFIG_PARAM$" 64 KB
. estimated "SYS"."TSDP_ASSOCIATION$" 64 KB
. estimated "SYS"."TSDP_CONDITION$" 64 KB
. estimated "SYS"."TSDP_FEATURE_POLICY$" 64 KB
. estimated "SYS"."TSDP_PARAMETER$" 64 KB
. estimated "SYS"."TSDP_POLICY$" 64 KB
. estimated "SYS"."TSDP_PROTECTION$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_DATA$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_TYPE$" 64 KB
. estimated "SYS"."TSDP_SOURCE$" 64 KB
. estimated "SYS"."TSDP_SUBPOL$" 64 KB
. estimated "SYSTEM"."REDO_DB" 64 KB
. estimated "SYSTEM"."REDO_LOG" 64 KB
. estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 64 KB
. estimated "WMSYS"."WM$CONS_COLUMNS$" 64 KB
. estimated "WMSYS"."WM$ENV_VARS$" 64 KB
. estimated "WMSYS"."WM$EVENTS_INFO$" 64 KB
. estimated "WMSYS"."WM$HINT_TABLE$" 64 KB
. estimated "WMSYS"."WM$MODIFIED_TABLES$" 64 KB
. estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 64 KB
. estimated "WMSYS"."WM$NEXTVER_TABLE$" 64 KB
. estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_LOCKING_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 64 KB
. estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 64 KB
. estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 64 KB
. estimated "WMSYS"."WM$VERSION_TABLE$" 64 KB
. estimated "WMSYS"."WM$VT_ERRORS_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 64 KB
. estimated "LBACSYS"."OLS$AUDIT" 0 KB
. estimated "LBACSYS"."OLS$COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$DIP_DEBUG" 0 KB
. estimated "LBACSYS"."OLS$GROUPS" 0 KB
. estimated "LBACSYS"."OLS$LAB" 0 KB
. estimated "LBACSYS"."OLS$LEVELS" 0 KB
. estimated "LBACSYS"."OLS$POL" 0 KB
. estimated "LBACSYS"."OLS$POLICY_ADMIN" 0 KB
. estimated "LBACSYS"."OLS$POLS" 0 KB
. estimated "LBACSYS"."OLS$POLT" 0 KB
. estimated "LBACSYS"."OLS$PROFILE" 0 KB
. estimated "LBACSYS"."OLS$PROFILES" 0 KB
. estimated "LBACSYS"."OLS$PROG" 0 KB
. estimated "LBACSYS"."OLS$SESSINFO" 0 KB
. estimated "LBACSYS"."OLS$USER" 0 KB
. estimated "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$USER_GROUPS" 0 KB
. estimated "LBACSYS"."OLS$USER_LEVELS" 0 KB
. estimated "SYS"."FGA_LOG$FOR_EXPORT" 256 KB
. estimated "SYS"."SQL$TEXT_DATAPUMP" 192 KB
. estimated "SYS"."SQLOBJ$DATA_DATAPUMP" 192 KB
. estimated "SYS"."SQL$_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$PLAN_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$_DATAPUMP" 128 KB
. estimated "SYSTEM"."SCHEDULER_JOB_ARGS" 128 KB
. estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 128 KB
. estimated "SYS"."AUDTAB$TBS$FOR_EXPORT" 64 KB
. estimated "SYS"."DBA_SENSITIVE_DATA" 64 KB
. estimated "SYS"."DBA_TSDP_POLICY_PROTECTION" 64 KB
. estimated "SYS"."NACL$_ACE_EXP" 64 KB
. estimated "SYS"."NACL$_HOST_EXP" 64 KB
. estimated "SYS"."NACL$_WALLET_EXP" 64 KB
. estimated "MDSYS"."RDF_PARAM$" 16 KB
. estimated "WMSYS"."WM$EXP_MAP" 16 KB
. estimated "WMSYS"."WM$METADATA_MAP" 16 KB
Total estimation using BLOCKS method: 6.546 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:09:32 2021 elapsed 0 00:00:11

Step:7 export the roles and privileges.

[oracle@orcldbs ~]$ expdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:8 Now it’s time to export our database(TANSTAL) 

[oracle@orcldbs ~]$ expdp system/oracle@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES;

Export: Release 12.2.0.1.0 - Production on Sun Jan 31 22:16:25 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@tanstal DIRECTORY=export DUMPFILE=orclfull.dmp LOGFILE=full_exp.log FULL=YES 
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 37 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.976 KB 2 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/orclfull.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Jan 31 22:19:02 2021 elapsed 0 00:02:03

Step:9 create a fresh database for import.(the dumpfile that is being exported in tanstal db going to be imported in zhigoma) 

Step:10 Now create a directory in both OS level and database level for import purpose in the newly created database.

mkdir /u01/export 

create directory export as '/u01/export';

Step:11 import the roles and privileges to zhigoma db.

[oracle@orcldbs ~]$ impdp directory=export dumpfile=orclfull.dmp logfile=roles.log full=Y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE

Step:12 Now it’s time to import our database.(ZHIGOMA)

[oracle@orcldbs ~]$ impdp system/oracle@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes;

Import: Release 19.0.0.0.0 - Production on Mon Feb 1 01:21:26 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@zhigoma directory=export dumpfile=orclfull.dmp logfile=export.log full=Yes 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER


Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . imported "WMSYS"."E$HINT_TABLE$" 9.984 KB 97 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$" 6.375 KB 1 rows
. . imported "WMSYS"."E$ENV_VARS$" 6.015 KB 3 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . imported "WMSYS"."E$EVENTS_INFO$" 5.812 KB 12 rows
. . imported "LBACSYS"."OLS_DP$OLS$AUDIT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$GROUPS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LAB" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LEVELS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POL" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROFILE" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROG" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$USER" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$" 0 KB 0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$VERSION_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MDSYS"."RDF_PARAM$TBL" 6.515 KB 3 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.976 KB 2 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 9.515 KB 12 rows
. . imported "WMSYS"."E$EXP_MAP" 7.718 KB 3 rows
. . imported "WMSYS"."E$METADATA_MAP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "RAHUL"."EMPLOYEE" 688.6 KB 100000 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed at Mon Feb 1 01:23:21 2021 elapsed 0 00:01:54

Once the import is over, you can also review the log file.

13.1 compile invalid objects in target database.

SQL> @?/rdbms/admin/utlrp.sql

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

COUNT(*)
-------- 0
SQL>  SELECT version FROM v$timezone_file;

  VERSION
-----------------
    32

13.3 Run the query to check for currently installed database components.

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;

13.4 Check the CONSTRAINTS count,it is used to display the constraints that are defined in the database.

SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type; 

C NUM_CONSTRAINTS

C 5784
F 12
O 181
R 327
P 864
V 11
U 250

7 rows selected.
SQL>

yes now we can login to the zhigoma database and we can verify our user(rahul) and the table(employee) in that user is  imported into this database or not.

We have successfully upgraded our database from 12 c to 19c using datapump !!!!

Patch Oracle Home 19c (19.3 to 19.9)

 

Here I am going to upgrade steps of Oracle Database Home 19c from 19.3 to 19.9 within Oracle Linux 8.2 The patch is 31771877 – Database Release Update 19.9.0.0.201020 Prerequisites:You must use the OPatch utility version 12.2.0.1.19 or later to apply this patch.” from READ ME of the Patch 31771877 Checking current ORACLE_HOME – OPATCH Version:

[oracle@db101:USA] ./opatch version
OPatch Version: 12.2.0.1.17 
OPatch succeeded.

Hope everyone is aware of how to upgrade the opatch utility. We need to take a backup of the existing patch and replace our new downloaded opatch in the ORACLE_HOME location.

Step 1: Install OPatch utility “6880880”:

Backup the Old OPatch utility

[oracle@db101:USA] cd [oracle@db101:USA]ORACLE_HOME
[oracle@db101:USA] mv OPatch/ OPatch_backup

Set up the latest Optach Utility 12.2.0.1.23

[oracle@db101:USA] unzip p6880880_190000_Linux-x86-64.zip
[oracle@db101:USA] ./opatch version
OPatch Version: 12.2.0.1.23 
OPatch succeeded

Step 2: Install the patch 31771877

Stop all services using the Oracle HOME: Databases or Listeners, .. Install the Patch:

[oracle@db101:USA] unzip p31771877_190000_Linux-x86-64.zip
[oracle@db101:USA] cd 31771877/
[oracle@db101:USA] $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2020, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : 
/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2021-02-08_10-46-12AM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 31771877 
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/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch '31771877' to OH '/u01/app/oracle/product/19.0.0/db_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , 
[ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 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.xdk.companion, 19.0.0.0.0 ] , 
[ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , 
[ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] ,
 [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.sqlj, 19.0.0.0.0 ] , 
[ oracle.jdk, 1.8.0.191.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.rdbms.util, 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.dbjava.ic, 19.0.0.0.0…
Patching component oracle.dbjava.jdbc, 19.0.0.0.0…
Patching component oracle.dbjava.ucp, 19.0.0.0.0…
Patching component oracle.dbtoolslistener, 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.oracore.rsf, 19.0.0.0.0…
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0…
Patching component oracle.rdbms.deconfig, 19.0.0.0.0…
Patching component oracle.sdo, 19.0.0.0.0…
Patching component oracle.sdo.locator.jrf, 19.0.0.0.0…
Patching component oracle.sqlplus, 19.0.0.0.0…
Patching component oracle.usm.deconfig, 19.0.0.0.0…
Patching component oracle.rdbms.crs, 19.0.0.0.0…
Patching component oracle.oraolap, 19.0.0.0.0…
Patching component oracle.precomp.rsf, 19.0.0.0.0…
Patching component oracle.javavm.client, 19.0.0.0.0…
Patching component oracle.precomp.common.core, 19.0.0.0.0…
Patching component oracle.network.client, 19.0.0.0.0…
Patching component oracle.rdbms.scheduler, 19.0.0.0.0…
Patching component oracle.ctx.atg, 19.0.0.0.0…
Patching component oracle.marvel, 19.0.0.0.0…
Patching component oracle.oraolap.dbscripts, 19.0.0.0.0…
Patching component oracle.ovm, 19.0.0.0.0…
Patching component oracle.bali.ice, 11.1.1.7.0…
Patching component oracle.ctx.rsf, 19.0.0.0.0…
Patching component oracle.rdbms.lbac, 19.0.0.0.0…
Patching component oracle.rdbms.oci, 19.0.0.0.0…
Patching component oracle.odbc, 19.0.0.0.0…
Patching component oracle.rdbms.rman, 19.0.0.0.0…
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0…
Patching component oracle.rdbms.install.plugins, 19.0.0.0.0…
Patching component oracle.rdbms.drdaas, 19.0.0.0.0…
Patching component oracle.ldap.rsf.ic, 19.0.0.0.0…
Patching component oracle.ldap.security.osdt, 19.0.0.0.0…
Patching component oracle.network.listener, 19.0.0.0.0…
Patching component oracle.ons, 19.0.0.0.0…
Patching component oracle.rdbms.dv, 19.0.0.0.0…
Patching component oracle.javavm.server, 19.0.0.0.0…
Patching component oracle.xdk, 19.0.0.0.0…
Patching component oracle.ldap.owm, 19.0.0.0.0…
Patching component oracle.dbdev, 19.0.0.0.0…
Patching component oracle.rdbms.install.common, 19.0.0.0.0…
Patching component oracle.sdo.locator, 19.0.0.0.0…
Patching component oracle.duma, 19.0.0.0.0…
Patching component oracle.sqlplus.ic, 19.0.0.0.0…
Patching component oracle.xdk.rsf, 19.0.0.0.0…
Patching component oracle.xdk.parser.java, 19.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0…
Patching component oracle.precomp.common, 19.0.0.0.0…
Patching component oracle.precomp.lang, 19.0.0.0.0…
Patching component oracle.jdk, 1.8.0.201.0…
Patch 31771877 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set 
patch [31771877].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: 
/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2021-02-08_10-46-12AM_1.log
OPatch succeeded.
[oracle@db101:USA 31771877]

Step 3: Check the upgrade:

[oracle@db101:USA 31771877]$ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2020, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.0.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : 
/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2021-02-08_10-53-14AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/19.0.0/db_1/
cfgtoollogs/opatch/lsinv/lsinventory2021-02-08_10-53-14AM.txt
Local Machine Information::
Hostname: db101.oracle.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 31771877 : applied on Mon Jan 18 10:47:31 CET 2021
Unique Patch ID: 23869227
Patch description: "Database Release Update : 19.9.0.0.201020 (31771877)"
Created on 7 Oct 2020, 21:34:19 hrs PST8PDT
Bugs fixed:
30150710, 31935717, 7391838, 8460502, 8476681, 14735102, 17428816
17468475, 19080742, 19697993, 20313356, 21374587, 21639146, 21888352
21965541, 22580355, 22729345, 22748979, 23294761, 23296836, 23606241
23645975, 23734075, 23763462, 24596874, 24669730, 24687075, 24833686
24971456, 25030027, 25092651, 25093917, 25404117, 25416731, 25560538
25756945, 25804387, 25806201, 25809128, 25883179, 25905368, 25986062
25997810, 26001677, 26284288, 26352615, 26440142, 26476244, 26499997
26611353, 26668264, 26739322, 26777814, 26819036, 26872233, 27036163
27044169, 27101798, 27126122, 27126938, 27166935, 27195935, 27221350
27244999, 27254335, 27260704, 27261477, 27359766, 27369515, 27378053
27392968, 27406105, 27411022, 27423500, 27439716, 27453490, 27458357
27489107, 27582210, 27589260, 27629928, 27666312, 27692173, 27700413
27710072, 27729678, 27745728, 27801144, 27828892, 27846298, 27873364
27880025, 27929509, 27934711, 27935464, 27941110, 27957203, 27967484
28064977, 28072567, 28078186, 28092783, 28104176, 28109326, 28125947
28129791, 28138847, 28144569, 28181021, 28189466, 28204262, 28205555
28209985, 28210681, 28263142, 28271258, 28271693, 28276054, 28279456
28294563, 28313275, 28319114, 28322973, 28326928, 28350595, 28371123
28373960, 28375383, 28379065, 28381939, 28386259, 28390273, 28395302
28397317, 28402823, 28406374, 28410431, 28431445, 28435333, 28436414
28442896, 28454215, 28463226, 28475242, 28482048, 28484299, 28489419
28492006, 28498976, 28502773, 28504631, 28513333, 28521330, 28530171
28534475, 28535127, 28537481, 28538439, 28541606, 28542455, 28546290
28547068, 28547926, 28558645, 28561704, 28564479, 28565296, 28567417
28567819, 28569897, 28572407, 28572533, 28572544, 28572667, 28572834
28578945, 28587723, 28589509, 28593682, 28594086, 28597221, 28601957
28602253, 28605066, 28606598, 28608211, 28612239, 28618343, 28620697
28622202, 28625862, 28627033, 28628592, 28632796, 28636532, 28639299
28642899, 28643583, 28643654, 28643718, 28644549, 28645570, 28646200
28646939, 28649388, 28655209, 28661333, 28663289, 28663782, 28672457
28673945, 28681153, 28689483, 28690694, 28692103, 28692275, 28694639
28694872, 28696373, 28697526, 28703812, 28705231, 28707931, 28708400
28709063, 28710385, 28710469, 28710734, 28714461, 28715655, 28715727
28718469, 28719348, 28720204, 28720418, 28721497, 28722229, 28730079
28734355, 28740708, 28740799, 28742555, 28745367, 28749853, 28752923
28755011, 28758722, 28760206, 28765983, 28767240, 28769456, 28771947
28772390, 28774416, 28776431, 28776811, 28777214, 28778754, 28781599
28781754, 28785531, 28789531, 28791852, 28793062, 28794230, 28795551
28795734, 28802734, 28804517, 28805242, 28808314, 28808656, 28810381
28811560, 28813931, 28815123, 28815355, 28817449, 28819640, 28820669
28821847, 28824482, 28827682, 28831971, 28833912, 28835937, 28836716
28838385, 28844738, 28845346, 28846759, 28849776, 28850084, 28854004
28854733, 28855520, 28855922, 28857552, 28861861, 28862532, 28863263
28863432, 28863487, 28865569, 28867698, 28867992, 28872645, 28873575
28875089, 28876253, 28876639, 28876926, 28878865, 28882784, 28884931
28887305, 28888327, 28889389, 28889730, 28892794, 28897512, 28899663
28900506, 28901126, 28905390, 28905457, 28905615, 28907196, 28910498
28910586, 28912691, 28915561, 28917080, 28918429, 28919145, 28921844
28922227, 28922532, 28922608, 28925634, 28925880, 28927452, 28928462
28932914, 28933158, 28935293, 28936114, 28937717, 28938422, 28938698
28940179, 28940281, 28941901, 28942455, 28945421, 28945994, 28946233
28948554, 28949888, 28950868, 28951533, 28952168, 28954762, 28955606
28955883, 28956908, 28957292, 28957723, 28958088, 28959493, 28960863
28962775, 28965084, 28965095, 28965231, 28965376, 28966444, 28974083
28974999, 28977322, 28981871, 28983095, 28983486, 28984313, 28985478
28986207, 28986231, 28986257, 28986326, 28986481, 28986696, 28988482
28988864, 28989306, 28993295, 28993353, 28994307, 28994542, 28996376
29000000, 29001305, 29001888, 29002488, 29002784, 29002927, 29003407
29003738, 29006318, 29006621, 29007321, 29007353, 29007775, 29008035
29008669, 29009513, 29010126, 29010517, 29011936, 29012609, 29013475
29013832, 29014076, 29015118, 29017265, 29018655, 29019121, 29021063
29021352, 29022986, 29024054, 29024448, 29024552, 29024732, 29026582
29026606, 29027456, 29027694, 29027940, 29031575, 29031600, 29032234
29032276, 29032457, 29032607, 29033052, 29033145, 29033200, 29033280
29034587, 29036278, 29037290, 29038528, 29038728, 29039089, 29039510
29040739, 29041739, 29041775, 29043554, 29043651, 29043725, 29044086
29044763, 29044954, 29046482, 29047850, 29048178, 29048289, 29048498
29048605, 29048728, 29049673, 29050357, 29050560, 29050765, 29050886
29051263, 29051702, 29051953, 29052726, 29053783, 29055644, 29056024
29056270, 29056560, 29056767, 29056894, 29059011, 29060216, 29061016
29061959, 29062692, 29062848, 29062860, 29062868, 29110526, 29110783
29110790, 29110797, 29110802, 29110805, 29111598, 29113282, 29113305
29115857, 29117526, 29117642, 29118543, 29119077, 29120223, 29122224
29122254, 29122367, 29123297, 29123432, 29123482, 29124368, 29125036
29125374, 29125380, 29126345, 29127957, 29128693, 29128935, 29129450
29129497, 29129691, 29129712, 29130219, 29131539, 29132869, 29132938
29133470, 29134447, 29135383, 29135649, 29136111, 29138641, 29139070
29139761, 29139956, 29141316, 29141341, 29141685, 29141886, 29142609
29142667, 29143516, 29144995, 29145214, 29145730, 29146810, 29149829
29150338, 29151520, 29152357, 29154725, 29155099, 29157051, 29157389
29158680, 29158899, 29159909, 29159936, 29160174, 29161597, 29162095
29163073, 29163156, 29163415, 29163437, 29163524, 29163567, 29164376
29165682, 29167111, 29167342, 29167374, 29167940, 29168137, 29168219
29168433, 29169073, 29169215, 29170232, 29171683, 29171942, 29172618
29172826, 29173140, 29173373, 29173817, 29174004, 29174753, 29175638
29176318, 29177466, 29177543, 29177886, 29178385, 29179097, 29180313
29180455, 29180559, 29180721, 29180893, 29181078, 29181153, 29181231
29181620, 29181743, 29181923, 29182019, 29182517, 29182901, 29183912
29184297, 29184666, 29185193, 29186456, 29186605, 29188255, 29189302
29189307, 29189889, 29190235, 29190474, 29190663, 29190740, 29191541
29192419, 29192468, 29192685, 29193207, 29194205, 29194367, 29194493
29194827, 29194981, 29195279, 29195337, 29195758, 29196725, 29198092
29198913, 29199635, 29199733, 29200316, 29200700, 29201494, 29201539
29201787, 29202104, 29202461, 29202850, 29203122, 29203166, 29203227
29203425, 29203443, 29203604, 29205281, 29205323, 29205419, 29205463
29205767, 29205918, 29206109, 29206605, 29207073, 29208260, 29208732
29211457, 29211724, 29212012, 29212433, 29212611, 29213320, 29213351
29213613, 29213775, 29213850, 29213879, 29214561, 29214960, 29216312
29216723, 29216746, 29216984, 29217294, 29217472, 29217828, 29217848
29218570, 29219205, 29219273, 29220079, 29221248, 29221891, 29221942
29222031, 29222784, 29223833, 29223859, 29223967, 29224065, 29224605
29225076, 29225168, 29225758, 29227602, 29228869, 29229164, 29229754
29229844, 29229955, 29230252, 29230565, 29231133, 29232117, 29232154
29232449, 29232653, 29233415, 29233810, 29233953, 29234123, 29236573
29237538, 29237575, 29237744, 29240307, 29240668, 29240759, 29241345
29241651, 29242017, 29242884, 29243958, 29245063, 29245137, 29245160
29246163, 29247415, 29247712, 29247906, 29248495, 29248552, 29248835
29248858, 29249289, 29249412, 29249991, 29250059, 29250317, 29251259
29253184, 29253871, 29254031, 29254930, 29255178, 29255273, 29255431
29255435, 29255718, 29255973, 29256426, 29259119, 29259320, 29260452
29260956, 29261547, 29261548, 29261906, 29262512, 29262887, 29265448
29266248, 29266899, 29267292, 29268412, 29269171, 29269228, 29269825
29270585, 29273539, 29273570, 29273735, 29273812, 29273847, 29274428
29274564, 29274627, 29275461, 29276272, 29277317, 29278218, 29278684
29279658, 29279751, 29279854, 29281527, 29281691, 29281796, 29282233
29282898, 29285197, 29285503, 29285788, 29285956, 29286037, 29286229
29287130, 29287705, 29290110, 29292837, 29293072, 29293574, 29296257
29297863, 29297915, 29298220, 29299049, 29299082, 29299844, 29301463
29301566, 29302963, 29303918, 29304314, 29304692, 29304781, 29304853
29306226, 29306713, 29307638, 29309698, 29311528, 29311588, 29311927
29312310, 29312672, 29312734, 29312753, 29312889, 29313347, 29313417
29313525, 29314539, 29314636, 29317756, 29318410, 29319441, 29320900
29321489, 29323946, 29324568, 29324735, 29325087, 29325105, 29325257
29325765, 29325993, 29327044, 29327892, 29329042, 29329087, 29329807
29330361, 29331066, 29331209, 29331380, 29331493, 29332292, 29332395
29332763, 29332771, 29333500, 29336843, 29336899, 29337294, 29337310
29337742, 29338315, 29338348, 29338453, 29338780, 29338913, 29339101
29339155, 29341209, 29342099, 29343086, 29343156, 29343861, 29344541
29345937, 29346057, 29346211, 29346943, 29347620, 29348176, 29348358
29350052, 29350712, 29350762, 29350868, 29351044, 29351386, 29351662
29351716, 29351735, 29351749, 29351771, 29352298, 29352724, 29352867
29352947, 29353271, 29353432, 29353718, 29353821, 29353960, 29355654
29356547, 29356704, 29356711, 29356752, 29356782, 29358509, 29358828
29360252, 29360285, 29360672, 29360911, 29360950, 29361319, 29361472
29361801, 29362596, 29363151, 29364171, 29364177, 29366406, 29366940
29367019, 29367561, 29368253, 29368310, 29372069, 29372541, 29372562
29373418, 29373588, 29374179, 29375355, 29375941, 29375984, 29376346
29377804, 29377986, 29378029, 29378287, 29378834, 29378913, 29379750
29379978, 29382641, 29382784, 29382815, 29383695, 29384781, 29384854
29384864, 29385339, 29385429, 29385652, 29386502, 29386557, 29386635
29386660, 29386835, 29387073, 29387274, 29387310, 29387337, 29388020
29388072, 29388094, 29388524, 29388830, 29389408, 29389889, 29390011
29390435, 29390785, 29391030, 29391237, 29391438, 29391849, 29391925
29392554, 29392966, 29393291, 29394014, 29394140, 29394749, 29395657
29397954, 29397996, 29398488, 29398863, 29399046, 29399100, 29399121
29399336, 29399938, 29402131, 29404483, 29405012, 29405462, 29405651
29405996, 29407488, 29407804, 29408853, 29409149, 29409455, 29410311
29410834, 29411037, 29411469, 29412066, 29412269, 29413382, 29413517
29413544, 29413634, 29413956, 29416688, 29416700, 29417084, 29417173
29417719, 29417884, 29418165, 29420254, 29420834, 29421059, 29423003
29423016, 29423156, 29423491, 29423826, 29424999, 29426241, 29426320
29428230, 29429017, 29429087, 29429264, 29429466, 29429566, 29429895
29430524, 29430866, 29431192, 29431485, 29432176, 29434301, 29434869
29435474, 29435652, 29436454, 29436514, 29436522, 29436727, 29437379
29437594, 29437712, 29438150, 29438277, 29438736, 29439522, 29440651
29441196, 29442936, 29443187, 29443250, 29443559, 29444072, 29444282
29444602, 29446319, 29446669, 29448498, 29449477, 29449845, 29449852
29450162, 29450193, 29450421, 29450812, 29450936, 29451386, 29452251
29452576, 29452936, 29452953, 29454450, 29454978, 29455424, 29455773
29456538, 29456714, 29457312, 29457370, 29457502, 29457807, 29457978
29460252, 29461420, 29461791, 29461971, 29462594, 29462767, 29462957
29463047, 29463528, 29463798, 29464616, 29464779, 29465177, 29466674
29467622, 29469563, 29469565, 29470059, 29470291, 29471633, 29471832
29471860, 29472618, 29473708, 29476473, 29477015, 29481584, 29482021
29483452, 29483532, 29483626, 29483672, 29483685, 29483712, 29483723
29483771, 29485099, 29485877, 29486181, 29486848, 29487150, 29487189
29488894, 29489436, 29489546, 29490256, 29492127, 29492939, 29493122
29494245, 29495057, 29495684, 29497311, 29497588, 29497696, 29498198
29500257, 29500826, 29500963, 29501218, 29502561, 29503543, 29503631
29503827, 29504103, 29504492, 29504682, 29505668, 29506942, 29507270
29507616, 29508681, 29509777, 29510278, 29511064, 29511611, 29511980
29512890, 29514479, 29515134, 29515240, 29515476, 29515766, 29515834
29516300, 29516727, 29516766, 29517168, 29517883, 29519131, 29521187
29521688, 29521748, 29521862, 29522358, 29522561, 29522662, 29523055
29523216, 29523511, 29524599, 29524985, 29525366, 29525467, 29525886
29526966, 29527595, 29527610, 29528368, 29529147, 29530440, 29530515
29530812, 29530909, 29531654, 29531836, 29532532, 29536342, 29536445
29537829, 29538631, 29541742, 29541769, 29541973, 29542084, 29542449
29542580, 29542643, 29543034, 29543956, 29544552, 29546817, 29547010
29547867, 29548413, 29548427, 29548592, 29548687, 29548722, 29549040
29549071, 29549104, 29549154, 29549730, 29552402, 29552773, 29553141
29554092, 29555105, 29557144, 29557261, 29557336, 29557556, 29558238
29558452, 29558975, 29559187, 29559395, 29559446, 29559908, 29559981
29564592, 29564593, 29565611, 29579919, 29580394, 29580983, 29581771
29584261, 29584693, 29586143, 29587299, 29587765, 29588732, 29589544
29591343, 29592011, 29592215, 29597536, 29597754, 29598039, 29598046
29598226, 29598233, 29599008, 29599300, 29601461, 29602831, 29603460
29603884, 29604002, 29604257, 29606261, 29607136, 29607797, 29608000
29608023, 29610506, 29611020, 29611991, 29614206, 29614987, 29615824
29616244, 29616414, 29618074, 29618190, 29620042, 29622936, 29623323
29623592, 29624124, 29625065, 29625804, 29625876, 29626154, 29626732
29628200, 29629430, 29629650, 29629681, 29629745, 29631749, 29632095
29632265, 29632611, 29633697, 29633753, 29633936, 29634643, 29635427
29635717, 29635990, 29637362, 29637526, 29637560, 29638285, 29641736
29643721, 29644426, 29644464, 29645167, 29645349, 29647176, 29647770
29648928, 29651183, 29651520, 29652809, 29653132, 29653246, 29655164
29655668, 29656400, 29656819, 29656843, 29657399, 29657422, 29657744
29657960, 29658056, 29661028, 29661065, 29661722, 29663191, 29663368
29663494, 29663601, 29664087, 29664161, 29665168, 29665940, 29667527
29667994, 29668005, 29669413, 29670782, 29671363, 29672507, 29675446
29676089, 29677051, 29677173, 29677733, 29677927, 29679856, 29681987
29683039, 29683211, 29684518, 29685137, 29685276, 29687214, 29687220
29687459, 29687718, 29687727, 29687763, 29688867, 29689145, 29689255
29692694, 29694869, 29695425, 29695821, 29695841, 29695964, 29696310
29700125, 29700460, 29700770, 29701720, 29703932, 29705793, 29707099
29707493, 29707896, 29708353, 29708876, 29708915, 29710188, 29710858
29713810, 29715220, 29715703, 29716194, 29716227, 29716491, 29716602
29716871, 29717659, 29717901, 29719146, 29720133, 29721418, 29721576
29722167, 29724658, 29725476, 29725781, 29726695, 29738374, 29738400
29739576, 29741319, 29741976, 29742223, 29744225, 29744400, 29745288
29746962, 29747493, 29747648, 29747653, 29748285, 29748336, 29748513
29749471, 29750673, 29751094, 29753244, 29754196, 29754951, 29755821
29756274, 29756444, 29757099, 29757264, 29757651, 29757687, 29758203
29758217, 29758661, 29761678, 29761837, 29761911, 29763158, 29765035
29765393, 29766207, 29766435, 29766503, 29766679, 29768487, 29768899
29769901, 29770750, 29771032, 29771242, 29773197, 29773205, 29773842
29775393, 29779196, 29780140, 29782211, 29782823, 29782866, 29784106
29785239, 29785311, 29787292, 29787766, 29789911, 29791152, 29791880
29792213, 29792433, 29793318, 29794174, 29794462, 29795712, 29795957
29796335, 29796378, 29797209, 29797726, 29802382, 29802695, 29804875
29805368, 29805772, 29806390, 29807964, 29809792, 29809837, 29812084
29812489, 29813503, 29813650, 29813671, 29815341, 29815713, 29817278
29817547, 29817784, 29821130, 29821582, 29822714, 29825525, 29827647
29827852, 29828644, 29831196, 29833984, 29834506, 29836096, 29838337
29838485, 29838773, 29839715, 29840619, 29841267, 29841687, 29843277
29843692, 29843831, 29844131, 29844226, 29844275, 29845530, 29846126
29846645, 29846688, 29848084, 29848849, 29849100, 29850930, 29851733
29853485, 29856859, 29858121, 29858376, 29859068, 29860994, 29861075
29864203, 29864261, 29865188, 29865590, 29865658, 29869086, 29869149
29869404, 29869887, 29869906, 29870065, 29871098, 29871312, 29871360
29872401, 29872937, 29872983, 29873665, 29874090, 29874761, 29875459
29875565, 29876358, 29876989, 29877608, 29878076, 29881050, 29881478
29881575, 29881643, 29881839, 29882427, 29882454, 29882729, 29884958
29885182, 29885890, 29886809, 29887045, 29887111, 29888621, 29889184
29889358, 29890740, 29891075, 29891853, 29891916, 29892604, 29893132
29896510, 29897418, 29897863, 29900203, 29900824, 29901419, 29902299
29902327, 29902330, 29902659, 29903190, 29903299, 29903357, 29903454
29904002, 29906678, 29907942, 29908389, 29908777, 29909658, 29910402
29912286, 29913805, 29913966, 29914449, 29914544, 29915217, 29915848
29916975, 29919789, 29920025, 29920376, 29920804, 29921318, 29922225
29922461, 29923452, 29924181, 29926466, 29927756, 29928210, 29928340
29928427, 29928564, 29930457, 29932202, 29932430, 29932780, 29934052
29935685, 29937565, 29937655, 29937956, 29938225, 29939400, 29939795
29940373, 29941062, 29942096, 29942554, 29943670, 29943879, 29944035
29944159, 29944660, 29944963, 29945645, 29946388, 29947145, 29948165
29950220, 29951620, 29951759, 29956016, 29956222, 29957412, 29957493
29958925, 29960884, 29961353, 29961609, 29961847, 29962160, 29962248
29962834, 29962927, 29962939, 29965052, 29965603, 29965888, 29966768
29967223, 29968085, 29969557, 29970081, 29970298, 29971027, 29971481
29971888, 29971936, 29971951, 29972176, 29973012, 29989783, 29989845
29991257, 29993717, 29997326, 29997553, 30000664, 30001331, 30003187
30006159, 30006985, 30007450, 30007797, 30008125, 30008198, 30008214
30009710, 30012181, 30015070, 30017836, 30018017, 30018903, 30019864
30024618, 30025814, 30026016, 30027614, 30028599, 30029519, 30029806
30031027, 30032376, 30033040, 30033547, 30034456, 30035598, 30036258
30038392, 30039800, 30039959, 30040157, 30041501, 30041514, 30042490
30043398, 30043610, 30043930, 30044108, 30044507, 30045389, 30045484
30046497, 30047531, 30047702, 30047765, 30047931, 30048688, 30049966
30051176, 30051783, 30051804, 30052928, 30053036, 30053501, 30053748
30054980, 30056058, 30057718, 30057799, 30058149, 30058453, 30059106
30059109, 30060267, 30060330, 30062364, 30062819, 30064268, 30066352
30067565, 30068384, 30068871, 30071446, 30072905, 30073314, 30073744
30074296, 30074349, 30074469, 30074472, 30074820, 30075037, 30076058
30076197, 30076253, 30076604, 30078675, 30078934, 30079949, 30080266
30081546, 30081580, 30082145, 30083100, 30083216, 30083488, 30083807
30084971, 30085897, 30086596, 30086992, 30090568, 30092280, 30092859
30095591, 30095952, 30097092, 30097115, 30098251, 30099302, 30099420
30099454, 30100354, 30101186, 30103551, 30103553, 30104378, 30104555
30106748, 30109365, 30110224, 30110370, 30110518, 30114477, 30114489
30114534, 30116085, 30116203, 30116854, 30117209, 30117335, 30117469
30117593, 30118261, 30118279, 30120608, 30122583, 30125765, 30126145
30127145, 30127522, 30127805, 30127904, 30128047, 30130240, 30131286
30131645, 30132708, 30133841, 30135396, 30135731, 30135942, 30136346
30139392, 30142907, 30143470, 30143593, 30146593, 30146969, 30147473
30147928, 30148999, 30149035, 30149658, 30150606, 30153552, 30153885
30154633, 30155241, 30155814, 30155837, 30159329, 30159511, 30159536
30159752, 30159760, 30161094, 30163243, 30164714, 30165493, 30165503
30165897, 30167787, 30169254, 30170104, 30172925, 30173113, 30173556
30174401, 30175291, 30177597, 30178250, 30178839, 30178990, 30179644
30180208, 30180643, 30181756, 30182498, 30183696, 30183715, 30183920
30184102, 30185852, 30186319, 30186476, 30186706, 30187866, 30189516
30190090, 30191274, 30192691, 30193165, 30193505, 30193736, 30194612
30194710, 30194972, 30195667, 30195668, 30195684, 30196195, 30196358
30196629, 30198861, 30198905, 30199890, 30200034, 30200132, 30200237
30200680, 30200758, 30202349, 30202388, 30203929, 30204042, 30204542
30206493, 30206675, 30207473, 30208327, 30208723, 30209736, 30210884
30213031, 30213540, 30215130, 30215302, 30215351, 30217206, 30217562
30217982, 30218044, 30218317, 30219222, 30221237, 30221298, 30222512
30223712, 30223847, 30224650, 30224868, 30224950, 30225265, 30225439
30225443, 30225718, 30225844, 30226244, 30228567, 30229683, 30232638
30235919, 30235979, 30236554, 30237477, 30238211, 30238715, 30239480
30240010, 30240547, 30240930, 30241567, 30241807, 30241920, 30242120
30242724, 30243216, 30244340, 30246053, 30246179, 30247305, 30249432
30251003, 30252005, 30252098, 30252156, 30252458, 30252977, 30253035
30253090, 30253608, 30253705, 30253835, 30254206, 30254525, 30254576
30254726, 30255143, 30255528, 30256542, 30257412, 30257908, 30260595
30264405, 30265523, 30265608, 30265615, 30265703, 30266791, 30267155
30269428, 30269748, 30270647, 30270744, 30271114, 30272329, 30274090
30274188, 30274324, 30274662, 30275548, 30275569, 30275578, 30276243
30277120, 30277451, 30277589, 30277733, 30281428, 30282501, 30282591
30283296, 30283577, 30283579, 30283581, 30283932, 30284219, 30284369
30285457, 30285540, 30285843, 30288343, 30288491, 30289074, 30289458
30293345, 30294267, 30294671, 30295110, 30295137, 30295549, 30295790
30295808, 30297905, 30299367, 30299817, 30299934, 30300030, 30300342
30300363, 30300538, 30305264, 30305395, 30305568, 30305880, 30307814
30307883, 30308368, 30308624, 30308772, 30308947, 30309098, 30309798
30311826, 30312094, 30312546, 30313848, 30313989, 30314079, 30314198
30314837, 30316667, 30316897, 30317209, 30318638, 30318943, 30319080
30319099, 30320029, 30322980, 30323658, 30323849, 30324180, 30325407
30326882, 30327149, 30328168, 30328690, 30329209, 30330123, 30331356
30331759, 30332505, 30334484, 30334563, 30335127, 30335832, 30335987
30336032, 30336742, 30339103, 30341713, 30342371, 30342878, 30344614
30345201, 30345432, 30345809, 30346330, 30346867, 30347410, 30349714
30350543, 30352581, 30352623, 30352715, 30355490, 30357463, 30357897
30360383, 30362003, 30362850, 30363088, 30363716, 30364329, 30364613
30365745, 30367193, 30368048, 30368482, 30368534, 30368668, 30371264
30371623, 30371909, 30372081, 30373419, 30373550, 30374345, 30374570
30374739, 30375109, 30376986, 30377347, 30381207, 30381525, 30382982
30383286, 30384121, 30384152, 30387666, 30389229, 30389414, 30389507
30391272, 30392011, 30392987, 30393110, 30393653, 30394738, 30394974
30396946, 30397100, 30398257, 30398422, 30399906, 30402386, 30403763
30403881, 30403902, 30403989, 30404117, 30404153, 30406709, 30408515
30408808, 30409207, 30409339, 30409590, 30412188, 30412863, 30412885
30412921, 30413137, 30413294, 30414491, 30414679, 30414714, 30416034
30416603, 30417648, 30417732, 30419024, 30421204, 30421439, 30421476
30421706, 30422487, 30423135, 30423218, 30424347, 30430921, 30431274
30431504, 30431698, 30431703, 30431717, 30431867, 30433177, 30437149
30441687, 30441959, 30442266, 30442749, 30442884, 30443393, 30446583
30447060, 30447589, 30448182, 30448917, 30449194, 30449837, 30450787
30453442, 30454090, 30457633, 30458568, 30458593, 30460922, 30461458
30463938, 30464250, 30464655, 30466081, 30469777, 30472891, 30473634
30474167, 30474774, 30475115, 30476768, 30477588, 30477685, 30477691
30477767, 30479252, 30479715, 30480872, 30483065, 30483140, 30483521
30484042, 30484801, 30485255, 30486436, 30487387, 30490014, 30490578
30493518, 30495035, 30495078, 30495133, 30495483, 30496957, 30497057
30497765, 30498824, 30500224, 30500297, 30500344, 30500582, 30501574
30502415, 30503943, 30505497, 30506794, 30506991, 30507032, 30509277
30510347, 30510527, 30513285, 30513848, 30515886, 30516868, 30517214
30517516, 30519188, 30522285, 30522998, 30523137, 30523538, 30523601
30523750, 30528547, 30528704, 30528935, 30529940, 30532811, 30533132
30534351, 30534549, 30534827, 30537405, 30537533, 30539519, 30540109
30540407, 30544247, 30544595, 30544629, 30545281, 30545556, 30549255
30549637, 30549789, 30549881, 30551000, 30551123, 30554178, 30556581
30556807, 30557386, 30559252, 30560365, 30560513, 30561590, 30561737
30564139, 30564343, 30565805, 30573236, 30573703, 30576112, 30576393
30576853, 30577071, 30577591, 30578221, 30579051, 30580813, 30581448
30582221, 30582500, 30588738, 30591028, 30592859, 30593046, 30595114
30595860, 30596488, 30598682, 30598746, 30598919, 30599405, 30599407
30600173, 30600184, 30602230, 30602828, 30605215, 30606345, 30606451
30609799, 30610667, 30611603, 30612199, 30613937, 30613971, 30614411
30619525, 30619787, 30620805, 30621255, 30622528, 30623138, 30623142
30624792, 30624864, 30625121, 30628899, 30629643, 30629799, 30631393
30631523, 30633259, 30633938, 30635183, 30635302, 30635326, 30637270
30637319, 30641755, 30641900, 30644889, 30647133, 30650404, 30651231
30651621, 30651674, 30652515, 30652853, 30654558, 30655906, 30657365
30657624, 30657706, 30657875, 30658533, 30658555, 30658702, 30660412
30661939, 30662651, 30662736, 30663646, 30668407, 30670584, 30671720
30671813, 30674959, 30676209, 30679595, 30679771, 30681462, 30681516
30686131, 30687047, 30690686, 30691604, 30691731, 30691857, 30692462
30692473, 30694947, 30696566, 30698289, 30703610, 30704826, 30708735
30710807, 30711370, 30714151, 30714715, 30716863, 30718841, 30718862
30719419, 30720736, 30720844, 30722705, 30723671, 30724679, 30724881
30727701, 30729278, 30729604, 30730026, 30732711, 30734707, 30735153
30735736, 30740669, 30740997, 30741263, 30749644, 30749722, 30750991
30751521, 30751968, 30755348, 30758943, 30761878, 30763272, 30763305
30763639, 30763754, 30764663, 30765486, 30769312, 30770717, 30773164
30773797, 30776416, 30776929, 30777759, 30778855, 30779240, 30781032
30781041, 30782414, 30783551, 30785101, 30786655, 30789904, 30790441
30801296, 30801510, 30803210, 30807723, 30808869, 30812574, 30814266
30814285, 30815852, 30816760, 30816938, 30821297, 30823744, 30825391
30825419, 30825656, 30826474, 30828350, 30829779, 30832775, 30833454
30834110, 30835853, 30838605, 30844839, 30847442, 30848097, 30848773
30851951, 30856358, 30857501, 30857721, 30858919, 30861988, 30865805
30866141, 30866988, 30869131, 30870439, 30871716, 30871792, 30873527
30880774, 30880913, 30881407, 30883785, 30883877, 30886188, 30887501
30887777, 30889723, 30890720, 30890971, 30896620, 30904672, 30906274
30906407, 30909918, 30910264, 30913399, 30914272, 30914674, 30919691
30919804, 30922936, 30922996, 30923517, 30923597, 30923940, 30927821
30930339, 30936831, 30937340, 30937410, 30939307, 30939934, 30940259
30940868, 30941056, 30944643, 30945005, 30946072, 30946876, 30952104
30952191, 30953266, 30953836, 30957739, 30964194, 30965649, 30968737
30968781, 30970518, 30972841, 30972887, 30972966, 30973113, 30973137
30973143, 30973698, 30978554, 30980317, 30980733, 30981240, 30985027
30987088, 30990034, 30992597, 30993198, 30994996, 30996991, 30997375
30998759, 30998847, 31001017, 31001455, 31001859, 31003659, 31004077
31004719, 31004844, 31008240, 31008907, 31009680, 31010976, 31013127
31015330, 31016413, 31019249, 31021157, 31021324, 31021542, 31022858
31025859, 31026220, 31028986, 31029936, 31031955, 31032904, 31034794
31035916, 31038220, 31039627, 31039928, 31042208, 31043483, 31051075
31056909, 31061482, 31061504, 31062010, 31066250, 31066265, 31066554
31067892, 31071080, 31077117, 31077365, 31079204, 31080474, 31084921
31086869, 31092129, 31094688, 31097760, 31100172, 31103065, 31104809
31106577, 31109506, 31113089, 31113249, 31115502, 31118809, 31119057
31124914, 31134430, 31141792, 31153120, 31153485, 31155634, 31156383
31163379, 31172207, 31177193, 31178103, 31180519, 31182159, 31182793
31188038, 31192039, 31193936, 31194264, 31200845, 31201001, 31202536
31208287, 31214119, 31215438, 31217946, 31219975, 31220912, 31221454
31222780, 31223382, 31228670, 31234765, 31249406, 31254297, 31254535
31258101, 31265773, 31292298, 31301460, 31305624, 31306248, 31309867
31312976, 31315876, 31321092, 31325584, 31326608, 31331354, 31334606
31338249, 31338673, 31383396, 31386394, 31387443, 31393600, 31394365
31401831, 31414023, 31414524, 31417192, 31429770, 31430722, 31431005
31433092, 31455597, 31475635, 31477424, 31486557, 31500971, 31509279
31513011, 31523548, 31527103, 31536731, 31537521, 31544097, 31570161
31591384, 31591400, 31591409, 31600023, 31609974, 31628753, 31658464
31668061, 31672605, 31683044, 31718134, 31718346, 31747989, 31758846
31781897, 31792615, 31796208, 31796277, 31820859, 31833172, 31867037
31876368, 31886547, 31888148, 31897786, 31905033
Patch 29585399 : applied on Mon Feb 08 08:21:33 CET 2021
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.

Now Its upgraded to Oracle Database Home 19.9 version. Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8  

UPGRADE ORACLE DATABASE FROM ORACLE 12.2.0.1 TO 19C USING DBUA

What is upgradation?

Upgradation is the process of replacing an older version/software with a new version of the product. It transforms the existing Oracle Database Environment into a new Environment. Upgradation can be done within the similar OS or with a different platform.

NOTE: It is important that we should apply the latest PSU or proactive bundle patch, RU, or RUR patch before starting an upgrade.

Why do we need to upgrade?

Upgrading a database generally fixes the bugs, internal errors and other technical issues which can be identified and resolved as soon as possible thereby it reduces the downtime.

You can perform a direct upgrade to the new release from the following releases: 
• 11.2.0.4 
• 12.1.0.1 and 12.1.0.2 
• 12.2.0.1

For example: Here I am converting my db from 12.2.0 (base version) to a 19.0.0 (higher version)  

Types of Upgrade:

We can upgrade our DB  in any one of the following  methods,

1)DBUA (Database Upgrade Assistant)

2)Manual Method

3)Data Pump

4)Transportable Tablespaces

DATABASE UPGRADE ASSISTANT  (DBUA) :

DBUA is a GUI method that checks all pre-requisties themselves.it checks for all the pre-requisties such as configuring the listener, recompiling the invaid objects, upgrading the time zone, running the preupgrade_fixups.sql,postupgrade_fixups.sql, configuring the enterprise manager (EM) database express, creating a Guaranteed Restore Point and all the things by itself.DBUA only performs the upgradation process if it satisfies all the above-mentioned things.

In the manual method, we should perform all the pre-requisties things manually.

Overview of steps to Upgrade a Database:

PRE-UPGRADE STEPS:

  1.  Create a new directory called backup, Take a backup of listener.ora,tnsnames.ora,spfile and password files ,then move all the files to the newly created directory called backup.
  2. Create a new directory called upgrade,Run the preupgrade.jar file which is in  the location
(/u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar),Move the summary of the preupgrade.script to the upgrade directory.

3.Three files named preupgrade.log,preupgrade_fixups.sql,postupgrade_fixups.sql files will be created after running the preupgrade.script.

4.We should execute the below scripts,

Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql

After upgrade    – @/u01/preupgrade/postupgrade_fixups.sql

5.Then check the preupgrade.log whether it is completed without any errors.

6. Verify the tablespace sizes for upgrade. (enable the AUTO EXTENT ON and set the maximum size for tablespaces)

 7. Gather the dictionary statistics query to get the statistical data of fixed objects. we should gather dictionary statistics before and after the upgrade to maintain better DB performance.Because dictionary tables are modified and created during the upgrade.

8.Purge the recyclebin

9.Refresh all the materialised views before upgrade.

10.Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.

11. You should enable the archive log list.

12.Create the flashback restore point before performing upgrade,it is important suppose if dbupgrade fails you can recover the database from this restore point.

13.Set the db_recovery_file_dest_size=10GB

UPGRADE TASK:

After we have completed all our pre-upgrade steps, Now it is time to upgrade our DB using dbua(GUI Method), All the steps will be taken by this.

Once you run the dbua command ,a log will be generated in the location (/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-04-23_10-46-32PM) we can check this log file if any error occurs)

14.We should choose the database which is to be upgraded for 19c.

15. Next choose to Create a Guranteed Restore Point or RMAN backup in case of failure of upgrade. Double-check our restore point

16. Configure a new listener or upgrade the existing listener which is running up from 12.2.20.1 home to target 19c home.

17.Check if all the pre-requisites that we mentioned are correctly mentioned in the GUI.

POST UPGRADE STEPS:

 18.Put an entry of current database in the vi /etc/oratab file.

 19. Verify the timezone version of the upgraded database. (it should be changed to 32)

 20. Then check for any invalid objects in the database after the upgrade.

 21.We have to verify the DBA_REGISTRY view,it displays the information about the components loaded in the database.

 22. Now we can drop the restore point that we have created already before the upgrade.

 23.Update the compatible parameter value, it will be set in the base database version we have to update the upgraded db version in the compatible parameter value.

 24. Finally check the listener.ora and tnsnames.ora files it should be updated to an upgraded db version.

Step:1 create a new directory for backup and for the upgrade.

[oracle@orcldbs u01]$ mkdir backup
[oracle@orcldbs u01]$ ls
app  backup  oradata                             
[oracle@orcldbs u01]$ mkdir preupgrade
[oracle@orcldbs u01]$ ls
app  backup  oradata  preupgrade

Step:2 Take backup of listener.ora,tnsnames.ora.spfile,password file.

Step:3 Run the pre-upgrade jar script,a log(preupgrade.log  will be generated after running the script and send it to the /u01/preupgrade)

[oracle@orcldbs db_1]$ /u02/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade/preupgrade.log

  /u01/preupgrade/preupgrade_fixups.sql

  /u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:
Log into the database and execute the preupgrade fixups

@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:
Log into the database and execute the postupgrade fixups

@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2021-01-30T01:54:48

Step:4 Log in to the database and run the preupgrade_fixups.sql

[oracle@orcldbs db_1]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 30 01:55:09 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by:       Oracle Preupgrade Script
                        Version: 19.0.0.0.0 Build

Generated on:            2021-01-30 01:54:46
For Source Database:        GERMANY
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.  rman_recovery_version     NO          Informational only.
                                            Further action is optional.

PL/SQL procedure successfully completed.

Step:5  Gather the dictionary statistics it should be gathered after performing the upgrade.

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

PL/SQL procedure successfully completed.

Step:6 Purge the recycle bin.it is mainly used to release the storage space used,since we have enabled the GRP we can purge the recycle bin.

PURGE DBA_RECYCLEBIN;

SQL>DBA_RECYCLEBIN purged.

Step:7 Refreshing the Materialised views update all its indexes.

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

PL/SQL procedure successfully completed.

Step:8 Enable the archive log mode it is mandatory to perform an db upgrade.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence     1
Current log sequence           2
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size                  8623592 bytes
Variable Size             654314008 bytes
Database Buffers         1761607680 bytes
Redo Buffers                8151040 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

SQL> show parameter rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string

control_file_record_keep_time        integer     7

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

db_recycle_cache_size                big integer 0

db_unrecoverable_scn_tracking        boolean     TRUE

ldap_directory_access                string      NONE

ldap_directory_sysauth               string      no

recovery_parallelism                 integer     0

recyclebin                           string      on

remote_recovery_file_dest            string

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
--------- -------------------- ------------
GERMANY   MOUNTED              ARCHIVELOG


SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 0

SQL> alter database open;

Database altered.

Step:9 set the db_recovery_fil_dest_size to 10 gb for recovery purpose.

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

Step:10 create the GRP (Guarantee Restore Point) ,suppose if any failure occurs during upgradation we can easily go to the before upgrade of db (ie  looks like as if our db before upgradation)it is used for recovery purpose.

SQL> create restore point pre_upgrade guarantee flashback database;

Restore point created.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

NAME                                                                      TIME                                                        GUA

PRE_UPGRADE                                            30-JAN-21 02.26.08.000000000 AM             YES                                                           

Step:11 We have done all the things to be done before the upgrade. Now it’s time to run the DBUA a GUI Utility to perform the upgrade.

11.1  you have to choose the database which you need to upgrade, here I am choosing Germany db for upgrade to 19.0.0  which is in version 12.2.0 and click next.

 

 

 

11.2 second window shows the Pre Upgrade Recommendations and Post Upgrade Recommendations and then click next.

 

 

 

 

11.3 in this screen click the following checkboxes,

Enable Parallel Upgrade

Recompile Invalid Objects During Post Upgrade

Upgrade Timezone Data

After that you have to browse the locations of preupgrade_fixups.sql,postupgrade_fixups.sql

 

 

 

 

11.4 In this screen you can specify any of the recovery option for the database in case of upgrade failure.

Here I am using Flashback and Guaranteed Restore Point.

There are methods are also available such that RMAN backup , use latest available full RMAN backup. if you have your own backup and restore strategy you can also use  that.

 

 

 

 

11.5 Select the 12c listener, it should be up and running.

 

 

 

 

11.6 select the Configure Enterprise Manager (EM) database express check box and click next.

 

 

 

 

11.7 Finally this window shows the Database Upgrade summary you can verify all your selections that you have choose above are correctly mentioned and proceed further.

 

 

 

 

11.8 After that Oracle upgrade process starts.

 

 

 

 

11.9 when the upgradation process is over it give an upgrade results which db is upgraded from which version to which version. You can check here for information.

 

 

 

 

Step:12 Put an entry of the current database in the vi /etc/oratab file.

[oracle@orcldbs ~]$ cat /etc/oratab | grep -i germany
germany:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@orcldbs ~]$[oracle@orcldbs admin]$ export ORACLE_SID=germany
[oracle@orcldbs admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 04:09:40 2021
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 from v$database;       

NAME
----------------
GERMANY

Step:13 check the timezone version for the upgraded db (GERMANY) it has changed from 26(12c) to 32(19c).

SQL>  SELECT version FROM v$timezone_file;

  VERSION
----------------          
32

Step:14 Check for any invalid objects in upgraded database.

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

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

Step:15 Verify the dba_registry, it should be verified before and after the upgrade because it display information about the components loaded in the database.

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

Step:16  we have upgraded our db to a higher version, hence we can drop the guaranteed restore point.

SQL> drop restore point pre_upgrade; 

Restore point dropped. 

Step:17 check the compatible parameter it will be in 12.2.0 compatible value we have to set the compatible value to 19.0.0

SQL> show parameter COMPATIBLE

NAME                                                        TYPE                VALUE

------------------------------------ ----------- ------------------------------
compatible                                              string                12.2.0

noncdb_compatible                             boolean          FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

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

ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size                                8899432 bytes
Variable Size                        536870912 bytes
Database Buffers             1879048192 bytes
Redo Buffers                         7876608 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

SQL> show parameter COMPATIBLE

NAME                                                        TYPE                VALUE

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

compatible                                              string              19.0.0

noncdb_compatible                            boolean          FALSE

SQL>

Step:18 Verify whether the listener and tns files are changed to the upgraded version.(19.0.0)

[oracle@orcldbs ~]$ ps -ef | grep tns

root        15     2  0 Jan29 ?        00:00:00 [netns]
oracle    2165     1  0 02:35 ?        00:00:00 /u02/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr LISTENER -inherit
oracle    6055     1  0 02:49 ?        00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle   25603 28937  0 04:16 pts/0    00:00:00 grep --color=auto tns


[oracle@orcldbs ~]$
[oracle@orcldbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-JAN-2021 04:17:17
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))

STATUS of the LISTENER
------------------------

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-JAN-2021 02:49:58
Uptime                    0 days 1 hr. 27 min. 18 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/orcldbs/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=orcldbs.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/trichy/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...
Service "oraprod" has 2 instance(s).
Instance "oraprod", status UNKNOWN, has 1 handler(s) for this service...
Instance "oraprod", status READY, has 1 handler(s) for this service...

Service "oraprodXDB" has 1 instance(s).
Instance "oraprod", status READY, has 1 handler(s) for this service...

Service "germany" has 1 instance(s).
Instance "germany", status READY, has 1 handler(s) for this service...

Service "germanyXDB" has 1 instance(s).
Instance "germany", status READY, has 1 handler(s) for this service...

The command completed successfully
[oracle@orcldbs ~]$

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8