Oracle Dynamic query creation

 

How to create oracle SQL statement from a select query

In this post, a simple yet very powerful query is written. If anytime we need to create large number of insert/update/alter statements from existing SQL statement, we can utilize the below operator to create it successfully.

Dynamic Create kill session example:

select 'alter system kill session ('||sid||','||serial#||');' from v$session where sid in (

select lo.SESSION_ID

from gv$locked_object lo, dba_objects do

where lo.object_id = do.object_id);

Best Wishes!! 

Oracle Blocking and Long running sessions

 Oracle Blocking and Long running sessions troubleshooting

As an Oracle DBA this is one of the biggest challenge to identify the root cause of any performance issue. This article will help to kickstart the journey to begin the troubleshooting. I have seen hundreds of issues and thousands of questions being asked by the application and business team. I believe these set of queries will help you identify the root cause of the issue and fix it efficiently. 

Note: This queries are drafted sequentially for my demo instance, before running in your environment, please check all the prerequisites. For major issues, please talk to Oracle Support for more customized issue finding. 

Check for long running sessions:

select SID,SERIAL#,sql_id,username,

TARGET,TOTALWORK,SOFAR,TIME_REMAINING/60 Mins_Remaining,

ELAPSED_SECONDS,

Elapsed_seconds/60 Elapsed_in_mins

from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;

Identify the sql ID

Run this to check the query:

select sql_text from v$sql where sql_id='&sqlid';

Query to check if anything is running for more than 5 mins(300secs):

--SQL running for more than 5 mins

set lines 300

set pages 400

col username format a20

col sql_id format a12

col sql_text format a40

col mins_running format 9999.99

col sql_text format a80

col sql_text heading 'SQL Text'

col sql_text format a80

select

s.username,

s.sid,

s.serial#,

s.sql_id,

s.last_call_et/60 mins_running,

t.sql_text

from v$session s

join v$sqltext_with_newlines t

on s.sql_address = t.address

where status='ACTIVE'

and type <>'BACKGROUND'

and last_call_et> 3600

order by sid,serial#,t.piece;

check for blocking sessions:

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine

|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2;

Find lock wait time:

SELECT

blocking_session "BLOCKING_SESSION",

sid "BLOCKED_SESSION", serial# "BLOCKED_SERIAL#",

seconds_in_wait/60 "WAIT_TIME(MINUTES)"

FROM v$session

WHERE blocking_session is not NULL

ORDER BY blocking_session;

 

 Check when the gather stat last executed:

SELECT * FROM

(SELECT log_date,job_name,status,actual_start_date,run_duration

FROM DBA_SCHEDULER_JOB_RUN_DETAILS

WHERE job_name='GATHER_STATS_JOB'

ORDER BY log_id DESC)

WHERE rownum<=2;

Find blocked sql from sid:

SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT

FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC

WHERE

SES.SQL_ID=SQL.SQL_ID AND

SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND

SES.PADDR=PRC.ADDR AND

SES.SID=&Enter_blocked_session_SID;

Note: Input is the blocked sid

Check sessions that are blocking objects:

select a.sid,a.serial#,a.username,a.status,a.schemaname,a.osuser,a.machine,a.terminal,

c.owner,c.object_name,c.status

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

 Hope this helps in troubleshooting the issues.

Best Wishes!!

Oracle 19c Client Installation error ' The java.library.path system variable is missing or invalid'

The java.library.path system variable is missing or invalid

During the Oracle 19c Client installation on RHEL8, the below error is very common.

Error:

The java.library.path system variable is missing or invalid. Please set java.library.path with a correct value and retry the operation.

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader

Cause:

This is because of the missing libraries in package libnsl.x86_64

Solution:

Login as root user, and install the binary.

yum install libnsl.x86_64

Hope this helps, Best wishes!!

Install Oracle 19c Client on Linux

Install Oracle 19c Client on Linux (RHEL8)

Title:

This article will cover the oracle client installation only. 

Action Plan:

Login to Oracle downloads, and download the specific Version and OS related Client. In this case I am installing a 19.3 client on Linux 64bit.

Once downloaded, SCP/FTP the zip file to the server. keep it on a temp location like /tmp

Now unzip the zip file.

unzip LINUX.X64_193000_client.zip

Initiate Xming or any X terminal session in your system.

export DISPLAY=<Local machine IP >:0.0

export CV_ASSUME_DISTID=OL7

(This is required as we are using 19.3 client binary and it was released for RHEL7, and we are installing the client on RHEL8)

Now go the the /tmp location where the patch was unzipped and execute runInstaller.

cd client

./runInstaller

This will open up an XWindow and on the GUI, select as per your requirement. I have chosen Administration part, as I would like to configure my listeners from the client binary. Then clieck next.

Below parameter will be asked

ORACLE_BASE=/opt/app/oracle

ORACLE_HOME=<This actually takes automatically when you enter the Oracle Base path.>

After this the Installer will check for the prerequisites and it will prompt for any missing library.

For my case, it was complaining about the above libraries, and ask the root owner to install those binaries. Or, if you are the server owner or having root access, you can install those binaries as well. 

eg: yum install <package_name>

One of the Library is obsolete in RHEL 8, so it can be safely ignored.

compat-libcap1

Once rest of the packages are installed, click on check again, and it will complain for only one package, which can be ignore and select ignore all checkbox on the top right hand corner and proceed to next step. In this step review the oracle home and base and click on install. 

Once the installation is done, it will ask for running the root.sh with root user. 

After running the root.sh/ work with Unix admin to complete executing the root.sh. click on OK. 

That completes the Client Installation. the below will be displayed.

Optional Steps:

After the successful installation the environment variables can be added to the bash profile for oracle user, so that it automatically assigned after each fresh login to the server. The path can be changed according to the environment and business standard that is followed by the enterprise. 

Example:

echo "ORACLE_HOME=/opt/app/oracle/product/19.0.0/client_1; export ORACLE_HOME" >> ~/.bash_profile

echo "LD_LIBRARY_PATH=\$ORACLE_HOME/lib; export LD_LIBRARY_PATH" >> ~/.bash_profile

echo "TNS_ADMIN=\$ORACLE_HOME/network/admin; export TNS_ADMIN" >> ~/.bash_profile

echo "PATH=\$PATH:\$ORACLE_HOME/bin; export PATH" >> ~/.bash_profile

cat ~/.bash_profile

Thanks for reading. Hope this helps you. Best Wishes!!

OEM 13.5 Error | Refresh from My Oracle Support Job is failing

 OEM Offline Patching - Refresh From Oracle Support Error

Title:

Refresh from My Oracle Support Job is failing

Description:

For security recommendations, the oracle em catalog can be uploaded to OEM in offline mode. And while doing so, the Refresh from my oracle support executes to validate the current and required patch sets for all the monitoring agents and systems. The below error occurs due to a Bug which is fixed in OEM 13.5 RU 110 version.

OEM Offline Patching Navigation

Log file will show error as below:

The OMS is in offline mode. Using the catalog file saved in the Software Library.

Ensure that the catalog file saved in the Software Library is up to date to avoid stale updates.

Catalog Location is :<Software Library location>

[Free Memory: 274] [Total Memory: 1,706] [Max Memory: 1,706]

Catalog Data Details

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

aru_products.xml : Parsing...Done : Loading...Done

 - Error 065c9318-ad29-41c3-8602-0abdcc12baf4: An integrity constraint, that is, a foreign key, a primary key, or a unique key has been violated.

Cause: the release id '600000000226924' is not found in the parent release table

 - An error has occurred. The session is being rolled back.

Total Time Taken: 1 minute(s) and 35 seconds

This job failed. For more information, check the logs.

Solution:

Please do not directly perform this is Production environment. If the issue is only happening in Prod system, it is recommended to consult Oracle Support for more environment specific solution. This was part of a bug and I got this solution from oracle Support. 

Refresh from MOS and retry

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

Login as SYSMAN user in the repository database and run:

SQL> DELETE FROM mgmt_aru_patch_recom_md;

SQL> begin

mos_pa_patch_recom.delete_checksums;

commit;

end;

/

Rerun the "Refresh from My Oracle Support" job.

Reference: 

Below Bugs were fixed in 13.5 RU 110 version & higher.

BUG 34221296 : REFRESH_FROM_MY_ORACLE_SUPPORT_JOB > 'Download Patch Recommendations Metadata' s

BUG 34370999 : EM 13c: Refresh from MOS Job Fails with ORA-02291: integrity constraint Error

Hope this helps. Best Wishes!!

How to check the size of an Oracle Table

 Check the size of a table in Oracle

set pages 300

set line 200

col "SEGMENT_NAME" format a 30

col "Size in GB" format a20

 

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024/1024 as "Size in GB" FROM dba_segments WHERE SEGMENT_TYPE='TABLE' and owner='&Owner' and segment_name in ('&Table_Name') group by SEGMENT_NAME order by SEGMENT_NAME;

Sample Output:

SEGMENT_NAME         Size in GB

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

UNDO$                0.265

How to check Oracle Database Size

Check the size of the Database:

Execute the below query as sys/ system or user with DBA privilege. 

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
/

Sample Output:

Database Size        Used space           Free space

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

500 GB               462 GB               38 GB

If you find this useful, please leave a comment. If you need the query to check for the size specific to any schema / Tables/ Please keep following the next posts.

Check Size of a specific Table

Best Wishes!!

Patching: Apply Oracle Database Patch on Linux/Unix host

 How to apply Oracle Database Patches in Linux?

This guide explains how to apply database patches into oracle database and oracle home running on Unix/Linux/ Aix etc.
First, we need to download the required patch from oracle support or metalink. 
Then ftp/scp the patch in a shared path on the server. 

Login to the server:

Setup environment variables.

ORACLE_SID=dev

ORACLE_HOME=/u01/app/oracle

ORACLE_BASE=/u01/app

PATCH_TOP=<Temporary location where the patch is staged>

PATH=$ORACLE_HOME/OPatch:$PATCH_TOP:$PATH:.

Note: Setup the PATH Variable to point to OPatch directory and the Patch directory where the patch is staged.