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

DBVERIFY

DBVERIFY

It is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files.
You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

Overview:

  • Verifying the table and tablespaces
  • Checking the header block for the table
  • Stimulating a corruption
  • Recovering the block using RMAN
  • Verifying using DATABASE BLOCK CORRUPTION

Step 1:

Verifying the TABLESPACE_NAME and its associated SEGMENT_NAME.

SQL> select SEGMENT_NAME,TABLESPACE_NAME from dba_segments where OWNER=’SCOTT’;

SEGMENT_NAME TABLESPACE_NAME
————— ——————————
SALGRADE USERS
DEPT USERS
EMP USERS
PK_DEPT USERS
PK_EMP USERS

Step 2:

By issuing the below command we can check the header block for the table ‘DEPT’

SELECT header_block FROM dba_segments WHERE segment_name=’DEPT’;

SQL> select file_name from dba_data_files;

FILE_NAME

/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_undotbs1_j21snxbn_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_system_j21skfkg_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_sysaux_j21sn40n_.dbf

Step 3:

Command to corrupt the block ,

[oracle@oracle19c ~]$ dd of=/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf bs=8192 conv=notrunc seek=347 << EOF

corruption test
EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 4.9494e-05 s, 323 kB/s

Step 4:

To flush the buffer cache we need to provide the below command

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Step 5:

Selecting the corrupted table , but it shows error

Step 6:

Viewing the corrected block in the database

select * from V$DATABASE_BLOCK_CORRUPTION;

Step 7:

Command to verify the corrupted block across the datafile using the below command

dbv file=/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf blocksize=8192

Step 8:

Command to recover the block and datafile using RMAN,
The datafile and corrupted block we can check it in the previous step ‘DBVERIFY-DBV’ command

recover datafile 7 block 347;

Verification’s:

After recovering there are no rows found in database block corruption ,
And after issuing dbverify command ,there we can see the marked corruption is ‘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

Get DDL ‘s

DDL ‘s of Objects in a Schema :- select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;

Last DDL Date:-

select to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') from dba_objects where owner='&user' and object_name='&table_name';

Generating DDL of table:-

set long 1000
select dbms_metadata.get_ddl('TABLE','EMP','SCHEMA')||'/' from dual;
set lines 1000 long 2000 pages 9999
select dbms_metadata.get_ddl('TABLE', table_name) from user_tables;

Generate DDL of DB_LINK:-

SET LONG 1000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;

Generate Sequence DDL:-

select 'CREATE SEQUENCE '||SEQUENCE_NAME||chr(10)||
' INCREMENT BY '||INCREMENT_BY||chr(10)||
' START WITH '||LAST_NUMBER||chr(10)||
' MINVALUE '||MIN_VALUE||chr(10)||
' MAXVALUE '||MAX_VALUE||chr(10)||
decode(CYCLE_FLAG,'N',' NOCYCLE','CICLE')||chr(10)||
decode(ORDER_FLAG,'N',' NOORDER','ORDER')||chr(10)||
' CACHE '||CACHE_SIZE|| ';'
from DBA_SEQUENCES where SEQUENCE_OWNER='&OWNER_NAME';

Tablespace DDL:-

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''|| tablespace_name || ''') from dual;' from dba_tablespaces;
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') from dual;
select 'create tablespace ' || df.tablespace_name || chr(10)
|| ' datafile ''' || df.file_name || ''' size ' || df.bytes
|| decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
|| maxbytes)
|| chr(10)
|| 'default storage ( initial ' || initial_extent
|| decode (next_extent, null, null, ' next ' || next_extent )
|| ' minextents ' || min_extents
|| ' maxextents ' || decode(max_extents,'2147483645','unlimited',max_extents)
|| ') ;'
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

Users DDL:-

set long 2000
select (case
when ((select count(*)
from dba_users
where username = 'ODB') > 0)
then dbms_metadata.get_ddl ('USER', 'ODB')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = 'ODB') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'ODB')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'ODB')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'ODB')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'ODB')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/
spool Riskusercreate.sql
set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED and LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system and default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
SELECT 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
ORDER BY USERNAME;
select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');
set pagesize 100
set escape off
spool off
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
variable v_username VARCHAR2(30);
exec:v_username := upper('&1');
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
/
set linesize 80 pagesize 14 feedback on trimspool on verify on
select dbms_metadata.get_ddl('USER', 'KPHU000') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KPHU000') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'KPHU000') '/' from dual;

Roles,system privs,other privs granted to user:-

select dbms_metadata.get_ddl('USER', '&USER') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&USER') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&USER') '/' from dual;

Single Objects Grants any type of Object :-

select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where table_name='&Any_type_of_object_name' ;

Password DDL:-

select 'alter user ' || NAME ||' identified by values ''' || password || ''';' from user$;

Cannot reuse the password:-

declare
userNm varchar2(100);
userpswd varchar2(100);
begin
userNm := upper('&TypeUserNameHere');
select password into userpswd from sys.user$ where name = userNm;
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION null
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED');
execute immediate ('alter user '||userNm||' identified by oct152014oct');
execute immediate ('alter user '||userNm||' identified by values '''||userpswd||'''');
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION');
end;
/
ORA-28003: password verification for the specified password failed ORA-20009: Error: You cannot change password SQL> ALTER PROFILE FUNCTIONAL_USER LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.
SQL> alter user trial identified by test;
User altered.
SQL> conn trial/test;
ALTER PROFILE "FUNCTIONAL_USER" LIMIT PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION;

Profile DDL:-

select ' alter user '||username||' profile '||PROFILE||';' from dba_users;
select 'grant ' || GRANTED_ROLE || ' to ' || ROLE || ';' from role_role_privs where role='&ROLE'
union
select 'grant ' || PRIVILEGE || ' to ' || ROLE || ';' from role_sys_privs where role='&&ROLE'
union
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where GRANTEE='&&ROLE';

Roles DDL:-

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
variable v_role VARCHAR2(30);
exec :v_role := upper('&1');
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from dba_roles r
where r.role = :v_role
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_role
and rownum = 1
/
set long 100000
set longchunksize 200
set heading off
set feedback off
set echo off
set verify off
undefine role
select (case when ((select count(*) from dba_roles
where role = '&&role') > 0)
then dbms_metadata.get_ddl ('ROLE', '&&role')
else to_clob ('Role does not exist')
end ) Extracted_DDL from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_sys_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_tab_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&role')
end ) from dual;

create a role and assign all privileges to the role:-

create role L1_SUPPORT;
create role L2_SUPPORT;
create role L3_SUPPORT;
set pagesize 0
set echo off
set trimspool on
set linesize 120
set feedback off
spool grant.sql
select 'grant select,insert,update,delete on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L2_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L1_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L3_SUPPORT;'
from dba_tables a
where owner in ('SCHEMA_NAME')
order by owner, table_name;
spool off
set feedback on

Roles comparison between databases:-

DB link : dev to prod CREATE DATABASE LINK "COMPARE" CONNECT TO DBSNMP IDENTIFIED BY mypwd USING 'destonation-db-name';
select * from DBA_TAB_PRIVS@compare T1 where not exists (select 1 from dba_tab_privs T2 where t1.TABLE_NAME=t2.TABLE_NAME and t1.PRIVILEGE=t2.PRIVILEGE and t1.GRANTEE=t2.GRANTEE ) and t1.grantee='&ROLE_NAME';

No of users have a particular role:-

select GRANTEE as users from dba_role_privs where GRANTED_ROLE=UPPER('&GRANTED_ROLE');
select * from DBA_TAB_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_ROLE_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_SYS_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_COL_PRIVS where grantee like upper('%&enter_username%');

ALL Privileges for a SINGLE user:-

select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');

List of Users/Roles having privilege on TABLE:-

SELECT grantee || ' Through role ' || granted_role ge, 'SELECT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'SELECT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'UPDATE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'UPDATE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INSERT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INSERT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'DELETE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'DELETE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INDEX' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INDEX')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'ALTER' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'ALTER')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'REFERENCES' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'REFERENCES')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'EXECUTE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'EXECUTE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Direct' ge, PRIVILEGE priv
FROM SYS.dba_tab_privs
WHERE table_name = UPPER ('&TABLE_NAME')
ORDER BY 1, 2;
select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end from (
/* THE OBJECTS */
select null p1,null p2,object_name obj,owner own,object_type typ from
dba_objects where owner like upper('%&enter_username%')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union select table_name p1, owner p2, grantee, grantee, privilege
from dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union select granted_role p1, granted_role p2, grantee,grantee,null
from dba_role_privs ) start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

Privileges granted by you to others:-

LISTING INFORMATION ABOUT PRIVILEGES —————————————————————————— To see which table privileges are granted by you to other users. SELECT * FROM USER_TAB_PRIVS_MADE To see which table privileges are granted to you by other users SELECT * FROM USER_TAB_PRIVS_RECD; To see which column level privileges are granted by you to other users. SELECT * FROM USER_COL_PRIVS_MADE To see which column level privileges are granted to you by other users SELECT * FROM USER_COL_PRIVS_RECD; To see which privileges are granted to roles SELECT * FROM USER_ROLE_PRIVS;

System privileges to roles and users:-

select lpad(' ', 2*level) || c "Privilege, Roles and Users" from (
/* THE PRIVILEGES */
select null p,name c from system_privilege_map
where name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union select granted_role p,grantee c from dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union select privilege p,grantee c from dba_sys_privs) start with p is null
connect by p = prior c;

Granted role permissions:-

select * from ROLE_ROLE_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME USER);
select * from ROLE_TAB_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);
select * from ROLE_SYS_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);

Roles & privs for a user:-

Granted Roles: SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER';
Privileges Granted Directly To User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER';
Privileges Granted to Role Granted to User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT granted_role
FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER');
Granted System Privileges:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER';

when was the password was changed for a user?

SELECT NAME, TO_CHAR (ptime, ‘DD-MON-YYYY HH24:MI:SS’) AS “LAST TIME CHANGED”, ctime “CREATION TIME”, ltime “LOCKED” FROM USER$ WHERE ptime IS NOT NULL ORDER BY ptime DESC;

NON Default database users:-

select username from dba_users where username not in ( 'ANONYMOUS','AURORA','$JIS','$UTILITY','$AURORA','$ORB','$UNAUTHENTICATED','CTXSYS',
'DBSNMP','DMSYS','DSSYS','EXFSYS','MDSYS','ODM','ODM_MTR','OLAPSYS','ORDPLUGINS',
'ORDSYS','OSE$HTTP$ADMIN','OUTLN','PERFSTAT','PUBLIC','REPADMIN','SYS','SYSMAN',
'SYSTEM','TRACESVR','WKPROXY','WKSYS','WMSYS','XDB','APEX_PUBLIC_USER' ,'APEX_030200',
'APPQOSSYS','BI' ,'DIP' ,'FLOWS_XXXXXX' ,'HR','IX' ,'LBACSYS' ,'MDDATA' ,'MGMT_VIEW' ,
'OE' ,'ORACLE_OCM' ,'ORDDATA' ,'PM' ,'SCOTT' ,'SH' ,'SI_INFORMTN_SCHEMA' ,
'SPATIAL_CSW_ADMIN_USR' ,'SPATIAL_WFS_ADMIN_USR','MTSSYS','OASPUBLIC','OLAPSYS',
'OWBSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','WK_TEST','AURORA$JIS$UTILITY$',
'SECURE','AURORA$ORB$UNAUTHENTICATED','XS$NULL','FLOWS_FILES');

DDL of V$ Tables:-

set long 10000 select  VIEW_DEFINITION  from V$FIXED_VIEW_DEFINITION where view_name=’GV$SQL_MONITOR’;

Script to Generate DDL ‘s of Various Objects of database :-
Script for DDL ‘s of All Indexes of database:-
 

SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || index_name|| ”’ ) from dual;’  from user_indexes; SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’INDEX’; SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’INDEX’ and owner not in (‘SYS’,’SYSTEM’); Script for DDL’s of all Tables of database:- SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || table_name|| ”’ ) from dual;’  from user_tables; SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TABLE’; SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TABLE’ and owner not in (‘SYS’,’SYSTEM’); Script for DDL’s of All Procedures of database:- SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’; SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’PROCEDURE’ and owner=’OWNER_NAME’; Script for DDL’s of All Functions of database :- SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’; SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’FUNCTION’ and owner=’OWNER_NAME’; Script for DDL’s of All Triggers of database:- SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’; SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’TRIGGER’ and owner=’OWNER_NAME’; Script for DDL’s of All Views of database:- SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’; SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’ and owner not in (‘SYS’,’SYSTEM’); SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’  from dba_OBJECTS where object_type=’VIEW’ and owner=’OWNER_NAME’; 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

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