General Queries

                                                                      



     

Tablespace growth Size

We can use view to check tablespace size greater than 80%

RAC / NON-RAC / Cloud RDS:

SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME where round(tbm.USED_PERCENT) > 80;";

SQL monitoring

We can use to check current executing SQL's.

RAC / Cloud RDS:

select inst_id,status,round(elapsed_time/1000000,2) as duration_secs,sql_id,sql_plan_hash_value,username,

px_maxdop as degree_of_parallelism, px_maxdop_instances as instances_used,

px_is_cross_instance as cross_instance,user_io_wait_time,cluster_wait_time,

concurrency_wait_time,round(cpu_time/1000000,2),queuing_time,PHYSICAL_READ_REQUESTS,

PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,

to_char(SQL_EXEC_START,'HH24:MI:SS'),to_char(LAST_REFRESH_TIME,'HH24:MI:SS'),sql_text from gv$sql_monitor where status='EXECUTING';


NON-RAC / Cloud RDS:

select inst_id,status,round(elapsed_time/1000000,2) as duration_secs,sql_id,sql_plan_hash_value,username,

px_maxdop as degree_of_parallelism, px_maxdop_instances as instances_used,

px_is_cross_instance as cross_instance,user_io_wait_time,cluster_wait_time,

concurrency_wait_time,round(cpu_time/1000000,2),queuing_time,PHYSICAL_READ_REQUESTS,

PHYSICAL_READ_BYTES,PHYSICAL_WRITE_REQUESTS,PHYSICAL_WRITE_BYTES,

to_char(SQL_EXEC_START,'HH24:MI:SS'),to_char(LAST_REFRESH_TIME,'HH24:MI:SS'),sql_text from gv$sql_monitor where status='EXECUTING';


 SQL_ID from sqltext


select sql_id,sql_text from  DBA_HIST_SQLTEXT where sql_text like '<query partial string>';


Datapump Jobs

We can use view to check running Datapump jobs.

RAC / NON-RAC / Cloud RDS:

select owner_name,job_name,operation,job_mode,state,degree,attached_sessions, datapump_sessions from dba_datapump_jobs where state like '%RUNNING%';


DB Scheduler Jobs Status

We can user view to check DB Scheduler Failed Jobs details.

RAC / NON-RAC / Cloud RDS:

select log_id, owner,job_name, status,run_duration, additional_info,log_date         from  dba_scheduler_job_run_details where status != 'SUCCEEDED' and log_date >= sysdate-1;


Standby Lag Status

We can use view to check Standby Lag delay.

RAC / NON-RAC / Cloud RDS:

SELECT SUM(DECODE(name, 'apply finish time', value, 0)) as Apply_Time, SUM(DECODE(name, 'apply lag', value, 0)) as Apply_LAG, SUM(DECODE(name, 'transport lag', value, 0)) as Transport_LAG from (SELECT name, extract(day from p.val) *86400 + extract(hour from p.val) *3600 + extract(minute from p.val) *60 + extract(second from p.val) value from (SELECT name,to_dsinterval(value) val from v$dataguard_stats) p );


RMAN Backup Status

We can use view to check rman backup status.

RAC / NON-RAC / Cloud RDS:

select to_char(start_time,'dd-mm-yyyy hh24:mi:ss') as start_time, to_char(end_time,'dd-mm-yyyy_hh24:mi:ss') as end_time, status, round(mbytes_processed/1024, 2) as GB_processed,object_type,operation,row_type,sid from v$rman_status where start_time >= sysdate-1  order by start_time desc;


Blocking Sessions

We can use view to identify blocking sessions. Below query checking blocking sessions > 60 seconds.

RAC / Cloud RDS:

SELECT   inst_id, blocking_session,    sid,   serial#,    seconds_in_wait FROM   gv$session WHERE    blocking_session IS NOT NULL and seconds_in_wait > 60 ;

NON-RAC / Cloud RDS:

SELECT   blocking_session,    sid,   serial#,    seconds_in_wait FROM   v$session WHERE    blocking_session IS NOT NULL and seconds_in_wait > 60 ;


Long Running Queries

We can use view to check long running queries.

RAC / Cloud RDS:

SELECT distinct sl.SID, sl.SERIAL#,s.sql_id, sl.opname, sl.SOFAR, sl.TOTALWORK,sl.target,ROUND(sl.SOFAR/sl.TOTALWORK*100,2) COMPLETE,sq.sql_text
FROM   gV$SESSION_LONGOPS sl, gv$session s, gv$sql sq
WHERE sl.sid=s.sid and s.sql_id=sq.sql_id and
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

NON-RAC / Cloud RDS:

SELECT distinct sl.SID, sl.SERIAL#,s.sql_id, sl.opname, sl.SOFAR, sl.TOTALWORK,sl.target,ROUND(sl.SOFAR/sl.TOTALWORK*100,2) COMPLETE,sq.sql_text
FROM   V$SESSION_LONGOPS sl, v$session s, v$sql sq
WHERE sl.sid=s.sid and s.sql_id=sq.sql_id and
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

Long Running Sessions

We can use view to check long running sessions.

RAC / Cloud RDS: sessions running more than 15 minutes.

select * from (select inst_id,sid,serial#,username,trunc(last_call_et/3600,2)||' hr' as last_call_et,sql_id,status,machine,osuser,logon_time from gv$session where last_call_et > 900 and username not like '%SYS%' and sql_id is not null) ;


NON-RAC / Cloud RDS: sessions running more than 15 minutes.


select * from (select sid,serial#,username,trunc(last_call_et/3600,2)||' hr' as last_call_et,sql_id,status,machine,osuser,logon_time from v$session where last_call_et > 900 and username not like '%SYS%' and sql_id is not null) ;


Locked Tables

We can use view to check locks on Tables.

RAC / Cloud RDS: Tables locked more than 5 minutes

SELECT s.inst_id,d.OBJECT_ID, object_name, l.SESSION_ID,s.last_call_et, l.ORACLE_USERNAME, l.LOCKED_MODE from   gv$locked_object l, dba_objects d, gv$session s where  d.OBJECT_ID=l.OBJECT_ID and s.sid=l.SESSION_ID and s.last_call_et > 300 ;


NON-RAC / Cloud RDS: Tables locked more than 5 minutes

SELECT d.OBJECT_ID, object_name, l.SESSION_ID,s.last_call_et, l.ORACLE_USERNAME, l.LOCKED_MODE from   v$locked_object l, dba_objects d, v$session s where  d.OBJECT_ID=l.OBJECT_ID and s.sid=l.SESSION_ID and s.last_call_et > 300 ;


Live Transactions

We can use view to check current running transactions.

RAC / Cloud RDS: transactions running more than 5 minutes.

SELECT s.inst_id,l.ORACLE_USERNAME,object_name, l.SESSION_ID,s.last_call_et,  l.LOCKED_MODE,t.xidusn, t.used_urec, t.used_ublk,s.saddr, t.ses_addr from   gv$locked_object l, dba_objects d, gv$session s, gv$transaction t where  d.OBJECT_ID=l.OBJECT_ID and s.sid=l.SESSION_ID and s.last_call_et > 300 and s.saddr = t.ses_addr;


NON-RAC / Cloud RDS: transactions running more than 5 minutes.

SELECT l.ORACLE_USERNAME,object_name, l.SESSION_ID,s.last_call_et,  l.LOCKED_MODE,t.xidusn, t.used_urec, t.used_ublk,s.saddr, t.ses_addr from   v$locked_object l, dba_objects d, v$session s, v$transaction t where  d.OBJECT_ID=l.OBJECT_ID and s.sid=l.SESSION_ID and s.last_call_et > 300 and s.saddr = t.ses_addr;


Temp Usage

We can use view to check temp usage percentage.

RAC / Cloud RDS:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment where free_blocks <=0;


NON-RAC / Cloud RDS:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from v$sort_segment where free_blocks <=0;


CPU Sessions usage

we can use view to get each session cpu usage.

RAC / Cloud RDS: Top 10 sessions from last 1 hour cpu usage

select * from (select * from

(

select username,sid,

       round((cpu_usage/(

                        select sum(value) total_cpu_usage

                          from gv$sesstat t

                         inner join gv$session  s on ( t.sid = s.sid )

                         inner join gv$statname n on ( t.statistic# = n.statistic# )

                         where n.name like '%CPU used by this session%'

                           and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24

                        ))*100/16,2) cpu_usage_per_cent,

       module_info,client_info,sql_id

  from

(

select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.sql_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info

  from gv$sesstat t

       inner join gv$session  s on ( t.sid = s.sid )

       inner join gv$statname n on ( t.statistic# = n.statistic# )

where n.name like '%CPU used by this session%'

   and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24

) s1

)

order by cpu_usage_per_cent desc) where rownum < 10;

NON-RAC / Cloud RDS: Top 10 sessions from last 1 hour cpu usage

select * from (select * from

(

select username,sid,

       round((cpu_usage/(

                        select sum(value) total_cpu_usage

                          from v$sesstat t

                         inner join v$session  s on ( t.sid = s.sid )

                         inner join v$statname n on ( t.statistic# = n.statistic# )

                         where n.name like '%CPU used by this session%'

                           and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24

                        ))*100/16,2) cpu_usage_per_cent,

       module_info,client_info,sql_id

  from

(

select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.sql_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info

  from v$sesstat t

       inner join v$session  s on ( t.sid = s.sid )

       inner join v$statname n on ( t.statistic# = n.statistic# )

where n.name like '%CPU used by this session%'

   and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24

) s1

)

order by cpu_usage_per_cent desc) where rownum < 10;


Alert Log view

We can use view to read errors/warnings in alert log file.

RAC / Cloud RDS:

SELECT inst_id, originating_timestamp, message_text
FROM TABLE (
gv$ (
CURSOR (
SELECT inst_id, originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE     originating_timestamp > (sysdate- 1)
AND message_text like '%ORA-%')))
ORDER BY inst_id, originating_timestamp;

NON-RAC / Cloud RDS:

select inst_id,to_char(originating_timestamp) as originating_timestamp,message_text,host_address,to_char(normalized_timestamp) as normalized_timestamp from  v$diag_alert_ext where message_text like '%ORA-%' and originating_timestamp > sysdate-1/24 order by originating_timestamp desc;


LOB segment size

We can use below steps to claim space from LOB segments.

Get list of LOBs:

select owner,segment_name,segment_type,bytes/1024/1024/1024 as "Size_GB" from dba_segments where segment_name='<table_name>' and owner='<table_owner>';

Get List LOB Columns:

select owner,table_name,column_name from dba_lobs where owner='<owner_name>' and segment_name='<lob_segment_name>';

LOBs Size:

select sum(dbms_lob.getlength(<lob_column_name>)) from <table_owner>.<table_name>;


Invalid Objects

We can use view to get invalid objects list;

RAC / NON-RAC / Cloud RDS:

select owner,object_name,object_type,status from dba_objects where status='INVALID';

Index Invalid Status

We can use view to check Index Invalid list.

RAC / NON-RAC / Cloud RDS:

select OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,status,ini_trans from dba_indexes where status='INVALID' ;


Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install   ...
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10   Select langu...
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Oracle to Postgres

Image
  Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$  -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$  ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Read more

Active Session History Analysis

Image
ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Read more

Fragmentation & Re-organisation

Total  Savings: set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) smallest, ceil( blocks*&amp;&amp;blksize/1024/1024) currsize, ceil( blocks*&amp;&amp;blksize/1024/1024) - ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc / HWM query for all datafiles: col data_file_name for a50 col tablespace_name for a20 set lines 300 select tablespace_name, file_id, file_name DATA_FILE_NAME, Al...
Read more

Oracle 11g Physical Standy Setup

  Step1: take Binaries tar backup from Primary DB server tar -cvf /path/11.2.0.4.tar 11.2.0.4 Step2: untar Binaries tar back on Standby DB server tar -xvf /path/11.2.0.4.tar -C /path ###################### Binaries Cloning ################### Step3: run clone command on Standby DB server export ORACLE_HOME=<PATH> cd $ORACLE_HOME/clone/bin perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>' ###################### POST Binaries Cloning ################### Step4: run root scripts $ORACLE_BASE/oraInventory/orainstRoot.sh $ORACLE_HOME/root.sh ###################### Pre-requisites ################### Standby: Step5: update hostnames in listener and tnsnames files Step6: start the listener in standby Server Primary: Step7: check force logging enabled -- to make sys activities will logged in redo logs SQL> select force_logging from v$database; - NO ( default ) to enable force logging - S...
Read more

Postgresql installation offline using NGINX

  [root@localhost yumdownloaddir]# sudo  yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm Loaded plugins: langpacks, ulninfo epel-release-latest-7.noarch.rpm                                                                                                                                                            |  15 kB  00:00:00      Examining /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm: epel-release-7-14.noarch Marking /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm to be installed Resolving Dependencies --...
Read more