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
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 :-
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
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.
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 !!!!
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.201020Prerequisites: “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
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
Upgradation is the process of replacing an older version/software with a new version of the product. It transforms the existing Oracle Database Environment into a new Environment. Upgradation can be done within the similar OS or with a different platform.
NOTE: It is important that we should apply the latest PSU or proactive bundle patch, RU, or RUR patch before starting an upgrade.
Why do we need to upgrade?
Upgrading a database generally fixes the bugs, internal errors and other technical issues which can be identified and resolved as soon as possible thereby it reduces the downtime.
You can perform a direct upgrade to the new release from the following releases: • 11.2.0.4 • 12.1.0.1 and 12.1.0.2 • 12.2.0.1
For example: Here I am converting my db from 12.2.0 (base version) to a 19.0.0 (higher version)
Types of Upgrade:
We can upgrade our DB in any one of the following methods,
1)DBUA (Database Upgrade Assistant)
2)Manual Method
3)Data Pump
4)Transportable Tablespaces
DATABASE UPGRADE ASSISTANT (DBUA) :
DBUA is a GUI method that checks all pre-requisties themselves.it checks for all the pre-requisties such as configuring the listener, recompiling the invaid objects, upgrading the time zone, running the preupgrade_fixups.sql,postupgrade_fixups.sql, configuring the enterprise manager (EM) database express, creating a Guaranteed Restore Point and all the things by itself.DBUA only performs the upgradation process if it satisfies all the above-mentioned things.
In the manual method, we should perform all the pre-requisties things manually.
Overview of steps to Upgrade a Database:
PRE-UPGRADE STEPS:
Create a new directory called backup, Take a backup of listener.ora,tnsnames.ora,spfile and password files ,then move all the files to the newly created directory called backup.
Create a new directory called upgrade,Run the preupgrade.jar file which is in the location
(/u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar),Move the summary of the preupgrade.script to the upgrade directory.
3.Three files named preupgrade.log,preupgrade_fixups.sql,postupgrade_fixups.sql files will be created after running the preupgrade.script.
4.We should execute the below scripts,
Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql
After upgrade – @/u01/preupgrade/postupgrade_fixups.sql
5.Then check the preupgrade.log whether it is completed without any errors.
6. Verify the tablespace sizes for upgrade. (enable the AUTO EXTENT ON and set the maximum size for tablespaces)
7. Gather the dictionary statistics query to get the statistical data of fixed objects. we should gather dictionary statistics before and after the upgrade to maintain better DB performance.Because dictionary tables are modified and created during the upgrade.
8.Purge the recyclebin
9.Refresh all the materialised views before upgrade.
10.Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.
11. You should enable the archive log list.
12.Create the flashback restore point before performing upgrade,it is important suppose if dbupgrade fails you can recover the database from this restore point.
13.Set the db_recovery_file_dest_size=10GB
UPGRADE TASK:
After we have completed all our pre-upgrade steps, Now it is time to upgrade our DB using dbua(GUI Method), All the steps will be taken by this.
Once you run the dbua command ,a log will be generated in the location (/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-04-23_10-46-32PM) we can check this log file if any error occurs)
14.We should choose the database which is to be upgraded for 19c.
15. Next choose to Create a Guranteed Restore Point or RMAN backup in case of failure of upgrade. Double-check our restore point
16. Configure a new listener or upgrade the existing listener which is running up from 12.2.20.1 home to target 19c home.
17.Check if all the pre-requisites that we mentioned are correctly mentioned in the GUI.
POST UPGRADE STEPS:
18.Put an entry of current database in the vi /etc/oratab file.
19. Verify the timezone version of the upgraded database. (it should be changed to 32)
20. Then check for any invalid objects in the database after the upgrade.
21.We have to verify the DBA_REGISTRY view,it displays the information about the components loaded in the database.
22. Now we can drop the restore point that we have created already before the upgrade.
23.Update the compatible parameter value, it will be set in the base database version we have to update the upgraded db version in the compatible parameter value.
24. Finally check the listener.ora and tnsnames.ora files it should be updated to an upgraded db version.
Step:1 create a new directory for backup and for the upgrade.
Before upgrade: Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql
After the upgrade: Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2021-01-30T01:54:48
Step:4 Log in to the database and run the preupgrade_fixups.sql
[oracle@orcldbs db_1]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 30 01:55:09 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @/u01/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build
Generated on: 2021-01-30 01:54:46 For Source Database: GERMANY Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 1. dictionary_stats YES None.
2. pre_fixed_objects YES None.
3. tablespaces_info NO Informational only. Further action is optional. 4. rman_recovery_version NO Informational only. Further action is optional.
PL/SQL procedure successfully completed.
Step:5 Gather the dictionary statistics it should be gathered after performing the upgrade.
SQL> SET ECHO ON; SET SERVEROUTPUT ON; EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Step:6 Purge the recycle bin.it is mainly used to release the storage space used,since we have enabled the GRP we can purge the recycle bin.
PURGE DBA_RECYCLEBIN;
SQL>DBA_RECYCLEBIN purged.
Step:7 Refreshing the Materialised views update all its indexes.
SQL> declare list_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); end;
PL/SQL procedure successfully completed.
Step:8 Enable the archive log mode it is mandatory to perform an db upgrade.
SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch Oldest online log sequence 1 Current log sequence 2 SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size 8623592 bytes Variable Size 654314008 bytes Database Buffers 1761607680 bytes Redo Buffers 8151040 bytes Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2
SQL> show parameter rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_recycle_cache_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
ldap_directory_access string NONE
ldap_directory_sysauth string no
recovery_parallelism integer 0
recyclebin string on
remote_recovery_file_dest string
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE --------- -------------------- ------------ GERMANY MOUNTED ARCHIVELOG
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 0
SQL> alter database open;
Database altered.
Step:9 set the db_recovery_fil_dest_size to 10 gb for recovery purpose.
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
Step:10 create the GRP (Guarantee Restore Point) ,suppose if any failure occurs during upgradation we can easily go to the before upgrade of db (ie looks like as if our db before upgradation)it is used for recovery purpose.
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME TIME GUA
PRE_UPGRADE 30-JAN-21 02.26.08.000000000 AM YES
Step:11 We have done all the things to be done before the upgrade. Now it’s time to run the DBUA a GUI Utility to perform the upgrade.
11.1 you have to choose the database which you need to upgrade, here I am choosing Germany db for upgrade to 19.0.0 which is in version 12.2.0 and click next.
11.2 second window shows the Pre Upgrade Recommendations and Post Upgrade Recommendations and then click next.
11.3 in this screen click the following checkboxes,
Enable Parallel Upgrade
Recompile Invalid Objects During Post Upgrade
Upgrade Timezone Data
After that you have to browse the locations of preupgrade_fixups.sql,postupgrade_fixups.sql
11.4 In this screen you can specify any of the recovery option for the database in case of upgrade failure.
Here I am using Flashback and Guaranteed Restore Point.
There are methods are also available such that RMAN backup , use latest available full RMAN backup. if you have your own backup and restore strategy you can also use that.
11.5 Select the 12c listener, it should be up and running.
11.6 select the Configure Enterprise Manager (EM) database express check box and click next.
11.7 Finally this window shows the Database Upgrade summary you can verify all your selections that you have choose above are correctly mentioned and proceed further.
11.8 After that Oracle upgrade process starts.
11.9 when the upgradation process is over it give an upgrade results which db is upgraded from which version to which version. You can check here for information.
Step:12 Put an entry of the current database in the vi /etc/oratab file.
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 04:09:40 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> SELECT name from v$database;
NAME ---------------- GERMANY
Step:13 check the timezone version for the upgraded db (GERMANY) it has changed from 26(12c) to 32(19c).
SQL> SELECT version FROM v$timezone_file;
VERSION ---------------- 32
Step:14 Check for any invalid objects in upgraded database.
select count(1) from dba_objects where status='INVALID';
COUNT(1) ------------------ 0
Step:15 Verify the dba_registry, it should be verified before and after the upgrade because it display information about the components loaded in the database.
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- --------------------------------------------
[oracle@orcldbs ~]$ [oracle@orcldbs ~]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-JAN-2021 04:17:17 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER ------------------------
Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 30-JAN-2021 02:49:58 Uptime 0 days 1 hr. 27 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF
Services Summary... Service "oraprod" has 2 instance(s). Instance "oraprod", status UNKNOWN, has 1 handler(s) for this service... Instance "oraprod", status READY, has 1 handler(s) for this service...
Service "oraprodXDB" has 1 instance(s). Instance "oraprod", status READY, has 1 handler(s) for this service...
Service "germany" has 1 instance(s). Instance "germany", status READY, has 1 handler(s) for this service...
Service "germanyXDB" has 1 instance(s). Instance "germany", status READY, has 1 handler(s) for this service...
The command completed successfully [oracle@orcldbs ~]$
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
This article guides you to perform the installation of Oracle Linux 7 (OEL7).
Choose “Install Oracle Linux 7.3” and press enter.
Step:1 Select the type of configuration do you want, typical or custom. Mostly in this below options typical is recommended if you need to set some advanced configuration you can choose custom option.
Step:2 Browse the iso file location and select I will install the operating system later option.
Step:3 Select a guest operating system, I have chosen the Linux operating system.
Step:4 Specify the virtual machine name and location of the virtual machine.
Step:5 Configure the maximum disk size I have configured the 200GB disk size and select the option store virtual disk as a single file.
Step:6 Specify the memory size of the virtual machine.
Step:7 Use ISO image file, specify the OS location OEL 7(Linux 7) software.
Step:8 Set the Date and time, choose the timezone, region and city as Asia and Kolkata respectively.
Step:9Select the base environment and adds on for selected environment, select all the check boxes for all the base environments.
Step:10 Specify the installation destination, here we can partition our storage either by automatic partitioning or customized partitioning.
Step:11 Do the partition manually. Such as create the following mount points /boot , / , swap.
Step:12 This page asks for the confirmation, whether we can go with the configured partitions.
Step:13 Setup the password for the root user.
Step:14 We can create the user, I have created the user oracle and set up the password for the oracle user. Now we all set for the installation we can proceed further.
Step:15 Now we can login to the oracle user and get in to the VM.
Step:16 Choose the language as English(United states).
Step:17 Now we can use the Virtual Machine installed with Linux 7 OS and I checked my internet is working within the VM by pinging google.com.
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
set lines 750 pages 9999
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;
Users Details Session:
For Cluster:
set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr
and a.inst_id = b.inst_id and type='USER' order by logon_time;
For Standalone:
set pages 500
set linesize 750
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr
and type='USER' order by b.sid;
(or)
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To Find Session Information Details based on SID or SPID or CLIENTPID :
col program for a15
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
Timing details, Client PID of associated oracle SID:
undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);
Checking Timing Details of SID and waiting event:
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;
Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);
Checking what is the Last SQL
undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);
All Active and Inactive connections
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action,
s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';
Active sessions
select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);
Session details from Session long ops
select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;
To list the nodes
set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /+ CHOOSE/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB ..............: '||SUPPORT_DB
from apps.fnd_nodes;
Session details through SPID:
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '18924');
To list count of connections from other machines:
select count(1),machine from gv$session where inst_id=3 group by machine;
To get total count of sessions and processes:
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext through sqladdress:
select sql_address from v$session where sid=8794;
select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;
To find sqltext for different sql hashvalue:
select hash_value,sql_text from v$sql where hash_value in (193756789,156412346,
456812142,8971248686,2358702945);
To list long running forms user sessions:
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username:
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE'; SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs:
select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');
To find Sql Text given SQLHASH & SQLADDR:
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece; select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
To find Undo Generated For a given session:
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sessionid';
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
col sid_serial for a20
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50SELECT a.inst_id,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
,(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
-- AND c.hash_value = a.sql_hash_value
AND a.inst_id=b.inst_id
ORDER BY 6 desc; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50
break on report
compute SUM of MB_USED on report
SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, 5 desc; (or) select ROUND(SUM(tempseg_size)/1048576) temp_mb from gv$sql_workarea_active WHERE sid=&sid;
History of Temp tablespace usage:
select sql_id,SQL_PLAN_HASH_VALUE,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-10 and TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) group by sql_id,SQL_PLAN_HASH_VALUE order by sql_id;
Shrink Temp file:
ALTER TABLESPACE temp SHRINK TEMPFILE '+ORA_DATA/JAPAN/tempfile/temp.313.868020865' KEEP 500M; 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
select inst_id,name,value from gv$parameter where name like '%undo%';
UNDO tablespaces Free As per expired segments:
SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');
does undo have expired segments?
select status, count(1) from dba_undo_extents group by status;
User Generated UNDO
col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id
and s.username = '<user>'
/
UNDO tablespace usage by USER
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s
where t.addr=s.taddr and s.sid='&sid';
Recreating the undo tablespace or resolving to undo issue
select a.name,b.status , d.username , d.sid , d.serial#
from v$rollname a,v$rollstat b, v$transaction c , v$session d
where a.name IN ( select segment_name
from dba_segments where tablespace_name = 'UNDOTBS1')
and a.usn = b.usn
and a.usn = c.xidusn
and c.ses_addr = d.saddr;
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;
If TRUE means it will not show any undo errors, So some times job may complete successfully.
Find active, expired, unexpired undo
select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' );select count(status) from dba_undo_extents where status = 'EXPIRED';
select count(status) from dba_undo_extents where status = 'UNEXPIRED';
select count(status) from dba_undo_extents where status = 'ACTIVE';
Find active, expired, and unexpired undo by Size in MB
select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME;select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;
Undo advisor:
SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a,
v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec
FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;
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
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:
