ADBA VALIDATION STEPS
-----------------------------------------------------------------------------------------------------------------------------
*****************************To Find Database Status ********************************
col start_time for a20;
SELECT inst_id,instance_name,host_name,status,logins,INSTANCE_ROLE,to_char(startup_time,'mm/dd/yy hh24:mi:ss') start_time, version from gv$instance;
select to_char(sysdate,'mm/dd/yy hh24:mi:ss') from dual;
select distinct host_name,name, open_mode, database_role, INSTANCE_NAME,to_char(startup_time,'mm/dd/yy hh24:mi:ss') start_time from v$database,v$instance;
select distinct host_name,name, open_mode, database_role, INSTANCE_NAME,to_char(startup_time,'mm/dd/yy hh24:mi:ss') start_time from gv$database,gv$instance;
-- best command for db status :
-- for 19c use below
set lines 400;
set pages 400;
col version_full for a15;
col host_name for a10;
col open_mode for a10;
select distinct
i.host_name,
d.name,
d.open_mode,
d.database_role,
i.instance_name,
to_char(i.startup_time,'mm/dd/yy hh24:mi:ss AM') start_time,
floor(sysdate-i.startup_time) days_since_up,
i.version,
p.version_full
from
gv$database d,
gv$instance i,
product_component_version p;
---for 11g and before use below product_component_version
select distinct
i.host_name,
d.name,
d.open_mode,
d.database_role,
i.instance_name,
to_char(i.startup_time,'mm/dd/yy hh24:mi:ss AM') start_time,
floor(sysdate-i.startup_time) days_since_up,
p.version
from
gv$database d,
gv$instance i,
product_component_version p;
prompt ***********DB REGISTRY COMPONENTS STATUS ******************
set lines 400;
col object_name for a50;
col comp_name for a50;
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;
prompt ******** INVALID OBJECT STATUS *********************
SELECT object_name, object_type FROM all_objects WHERE status = 'INVALID';
SELECT count(object_name) invalid_object_count FROM all_objects WHERE status = 'INVALID';
prompt ********* DB STATUS *********************
col host_name for a20;
col start_time for a20;
select distinct host_name,name, open_mode, database_role, INSTANCE_NAME,to_char(startup_time,'mm/dd/yy hh24:mi:ss') start_time,version database_version from gv$database,gv$instance;
********************* FOR PATCH VALIDATION ***********************************
select * from ( Select patch_id , patch_uid , version , status , action_time , flags from dba_registry_sqlpatch order by action_time desc ) Where rownum <= 2 ; ---------------best compact query
select patch_id, version, status, bundle_id, bundle_series , description,logfile from dba_registry_sqlpatch;
select * from sys.registry$history;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select host_name,instance_name, (select to_char(sysdate,'DD/MM/YYYY HH:MM:SS') from dual) "sysdate" from v$instance;
HOST_NAME INSTANCE_NAME sysdate
---------------------------------------------------------------- ---------------- -------------------
hostname instance_name 01/02/2023 05:02:58
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
********To check on OS LEVEL "oracle && listener && golden gate&& CRS :**********
------------------------------------------------------------------------------------
ps -ef | grep pmon && ps -ef | grep tnslsnr && ps -ef | grep crs && ps -ef | grep PARAM
ps -ef | grep pmon ; ps -ef | grep tnslsnr ; ps -ef | grep crs ; ps -ef | grep PARAM
ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep -----------------------> ALL IN ONE COMMAND TO GET STATUS FOR ALL STUFFS ON RAC OR STANDBY
------------------------------------------------------------------------------------------------
****************** TO CHECK RP OR GRP ****************************************
SELECT distinct NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE, host_name,instance_name
FROM GV$RESTORE_POINT,gv$instance
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
---------------------------------------------------------------------------------------------------------------
***************** RMAN BACKUPS **********************************
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key desc;
for specific status of rman :--
select
(select host_name from v$instance) "host_name",
(select instance_name from v$instance) "host_name",
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display ,
(elapsed_seconds/3600) hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key desc;
for specific time frame:
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
where to_char(end_time,'DD-MON-YYYY')= to_char(sysdate,'DD-MON-YYYY') or
to_char(end_time,'DD-MON-YYYY')= to_char(sysdate-1,'DD-MON-YYYY')
order by session_key desc;
----------------------------------------------------------------------------------------
##########To Find the log_archive_dest_state_n location on PROD For DR ##############
*********************Make it defer*************
col dest_name for a30
col status for a10
col destination for a50
select dest_name,status,destination from gv$archive_dest_status where destination is not null;
select dest_name,status,destination from gv$archive_dest where distination is not null;
select dest_name,status,destination from gv$archive_dest_status;
#############################################################
************************* FOR MRP VALIDATION STANDBY ********************
select host_name as "host_name and process" ,instance_name as "instance_name and status" from gv$instance
union select process,status from gv$managed_standby where process='MRP0';
host_name and process instance_name and status
---------------------------------------------------------------- ----------------
MRP0 APPLYING_LOG
host993 instance11
host994 instance12
host995 instance13
host996 instance14
select distinct inst_id,host_name,instance_name,(select process from gv$managed_standby where process like 'MRP%') "process",
(select status from gv$managed_standby where process like 'MRP%') "status" from gv$instance;
INST_ID HOST_NAME INSTANCE_NAME process status
---------- ---------------------------------------------------------------- ---------------- --------- ------------
1 host1 instance1 MRP0 APPLYING_LOG
3 host2 instance2 MRP0 APPLYING_LOG
4 host3 instance3 MRP0 APPLYING_LOG
2 host4 instance4 MRP0 APPLYING_LOG
select a.thread# thread,
(select process from gv$managed_standby where process like '%MRP%') "process",
(select status from gv$managed_standby where process like '%MRP%') "status",
a.arch_seq arch,
b.appl_seq appl,
(a.arch_seq-b.appl_seq)
difference from (select thread#, max(sequence#)
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
(select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b
where a.thread# = b.thread# (+) order by a.thread#, a.arch_seq, b.appl_seq;
THREAD process status ARCH APPL DIFFERENCE
---------- --------- ------------ ---------- ---------- ----------------------------
1 MRP0 APPLYING_LOG 202532 202532 0
2 MRP0 APPLYING_LOG 212923 212922 1
3 MRP0 APPLYING_LOG 212875 212875 0
4 MRP0 APPLYING_LOG 210705 210705 0
------------------------Final all in one query for MRP DR ARCHIVE LOG GAP --------------
select a.thread# thread,
k.host_name,
k.instance_name,
m.process,
m.status,
a.arch_seq arch,
b.appl_seq appl,
(a.arch_seq-b.appl_seq)
difference from (select thread#, max(sequence#)
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
(select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b,gv$instance k,gv$managed_standby m
where a.thread# = b.thread# (+) and b.thread# = k.thread# (+) and m.thread#<>0 and m.process like '%MRP%' order by a.thread#, a.arch_seq, b.appl_seq;
--------------------------------------------------------------------------------------------------------
THREAD HOST_NAME INSTANCE_NAME PROCESS STATUS ARCH APPL DIFFERENCE
---------- ---------------------------------------------------------------- ---------------- --------- ------------ ---------- ---------- ---------- -------------------
1 host1 instance1 MRP0 APPLYING_LOG 418336 418336 0
2 host2 instance2 MRP0 APPLYING_LOG 512681 512681 0
3 host3 instance3 MRP0 APPLYING_LOG 564134 564134 0
4 host4 instance4 MRP0 APPLYING_LOG 549583 549583 0
------------------------------------------------------------------------------------------------
select distinct a.thread# thread,
k.host_name,
k.instance_name,
l.name,
l.open_mode,
to_char(startup_time,'mm/dd/yy hh24:mi:ss') "startup_time",
m.process,
m.status,
a.arch_seq arch,
b.appl_seq appl,
(a.arch_seq-b.appl_seq)
difference from (select thread#, max(sequence#)
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
(select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b,gv$instance k,gv$managed_standby m,
gv$database l
where a.thread# = b.thread# (+) and b.thread# = k.thread# (+) and m.thread#<>0 and m.process like '%MRP%' and l.name is not null order by a.thread#, a.arch_seq, b.appl_seq;
THREAD HOST_NAME INSTANCE_NAME NAME OPEN_MODE startup_time PROCESS STATUS ARCH APPL DIFFERENCE
---------- ---------- ---------------- --------- -------------------- -------------------- --------- ------------ ---------- ---------- ----------
1 host1 instance1 dbname READ ONLY WITH APPLY 08/05/23 15:42:12 MRP0 APPLYING_LOG 1823613 1823613 0
2 host2 instance2 dbname READ ONLY WITH APPLY 08/05/23 15:42:12 MRP0 APPLYING_LOG 1461971 1461971 0
3 host3 instance3 dbname READ ONLY WITH APPLY 08/05/23 15:42:12 MRP0 APPLYING_LOG 1343884 1343884 0
4 host4 instance4 dbname READ ONLY WITH APPLY 08/05/23 15:42:12 MRP0 APPLYING_LOG 1493486 1493486 0
*********************check mrp is shutdown on dr:***********************************
----------------------------------------------------------------------
select process,status from gv$managed_standby;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
--------------------------------------------------------------------
*******************LAG Differencce PR AND DR***********************
---------------------------------------------------------
select a.thread# thred,
a.arch_seq arch,
b.appl_seq appl,
(a.arch_seq-b.appl_seq)
difference from (select thread#, max(sequence#)
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
(select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b
where a.thread# = b.thread# (+) order by a.thread#, a.arch_seq, b.appl_seq;
============================================================
**********************DR LAG TRANSPORT ***************************************************************
=================================================
select name,value from v$dataguard_stats;
NAME VALUE
-------------------------------- ----------------------------------------------------------------
transport lag +00 00:00:00
apply lag +00 00:00:36
apply finish time +00 00:00:02.200
estimated startup time 21
========================================================
startup time and shutdown time history & days:
--------------------------------------------------------------------------------------------------------------------
******************will only show startup time 10 rows :******************
select * from
( select STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC)
WHERE rownum < 10;
-----------------------------------------------------------------------------------------------
******************will show details and startup time in days:**************
SELECT host_name, instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,
FLOOR(sysdate-startup_time) days
FROM sys.v_$instance;
-----------------------------------------------------------------------------------------------------------------------
*****************will show startup and shutdown time:******************
SELECT SCN_TO_TIMESTAMP(TIMESTAMP_TO_SCN(startup_time)) "DB Shutdown Time" ,
startup_time "DB Startup Time" FROM v$instance;
========================================================
*****************patch details(12c onwards):*********************
-------------------------
col action_time for a28
col action for a8
col version for a8
col comments for a30
col status for a10
set line 999 pages 999
select patch_id, version, status, Action,Action_time from dba_registry_sqlpatch order by action_time;
*************Best Working command for Patch Validation:
-------------------------------------------------------------
col action_time for a28;
col description for a80;
set line 999 pages 999;
select patch_id, action, description, action_time from dba_registry_sqlpatch order by action_time;
Output:
----------------
PATCH_ID ACTION DESCRIPTION ACTION_TIME
---------- -------- ------------------------------------------------------------ ----------------------------
30869156 APPLY Database Release Update : 19.7.0.0.200414 (30869156) 09-JUL-20 10.25.59.281935 AM
30805684 APPLY OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 09-JUL-20 10.25.59.286150 AM
31281355 APPLY Database Release Update : 19.8.0.0.200714 (31281355) 22-JUL-20 01.30.47.736379 PM
30805684 ROLLBACK OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 22-JUL-20 01.36.22.283160 PM
31219897 APPLY OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897) 22-JUL-20 01.36.22.287535 PM
===========================================================
###################### Locks on tables or objects and how to handle them ###################
Locks on tables by sessions :
solution:
1) commit;
2) rollback;
3) kill the session;
4) wait to complete;
===================================================================
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
===================================================================
*************************************BLOCKING SESSIONS *******************
SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;
********* TO KILL SESSIONS ON PRODUCTION DEPLOYMENT***********************
col sid_serial format a40
col machine format a16
col username format a20
select a.username, a.sid,a.serial#, a.inst_id, a.program, a.machine,A.STATUS,a.sql_id,a.LAST_CALL_ET
from gv$session a,
gv$process b
where a.USERNAME not in('KM907T','SYS') and a.STATUS='ACTIVE'
and a.inst_id = b.inst_id and a.paddr = b.addr order by a.LAST_CALL_ET, a.sql_id;
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
substr(program||module,1,15) program,substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,
last_call_et "last call",status
from gv$session where status='ACTIVE'
order by 1;
SELECT
s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.username not in ('YOU_USERNAME','SYS') and s.status!='ACTIVE';
TOP CPU CONSUMING SESSIONS:
-----------------------------------------------
select rownum as rank, a.*
from (
SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMins
FROM gv$statname s , gv$sesstat v, gv$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;
-------------------------------------------------------
TOP CPU CONSUMING SESSIONS LAST 10 MIN:-
-------------------------------------------------------------------
select * from
(
select session_id, session_serial#, count(*)
from gv$active_session_history
where session_state= 'ON CPU' and
sample_time >= sysdate - interval '10' minute
group by session_id, session_serial#
order by count(*) desc
);
--------------------------------------------------------------------
TOP SQL ID CPU :-
-------------------------------------------------------------------
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program",
ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_sec
from gv$session ss,gv$sesstat se,
gv$statname sn,gv$process p
where
se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);
------------------------------------------------------------------------------------
TOP SQL TEXT :-
-------------------------------------------------------------
select * from (
select
(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from gv$session ss,gv$sesstat se,
gv$statname sn, gv$process p, gv$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);
-----------------------------------------------------------------------------------
TOP USERNAME CPU USAGE
---------------------------------------------------------------
select * from (
SELECT se.username, ROUND (value/100) "CPU Usage"
FROM gv$session se, gv$sesstat ss, gv$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID and ROUND (value/100) <> 0
AND se.username IS NOT NULL
ORDER BY value DESC) where rownum <= 10;
------------------------------------------------------------------------------
alter system kill session 'sid, serial#'; -----> kill sessions instance wise
commit;
***********************************************************
########## Below are the queries to find LOCKED OBJECTS STATUS #####################
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
==========================================================
column oracle_username format a15;
column os_user_name format a15;
column object_name format a37;
column object_type format a37;
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
==========================================================
Want to check PARAMETERS on PROD AND DR
select
inst_id,
name,
value
from gv$parameter
where
name in ('parameter_name_lowercase','parameter_name_lowercase');
**********************************************************************
Last Password change:-
----------------------------------
set lines 400 set pages 60;
col name form a20;
col Last_Changed for a30;
col User_Created_Date for a30;
col account_status for a20;
SELECT name, b.account_status "account_status", to_char(ctime,'DD/MM/YYYY HH:MI:SS AM') "User_Created_Date" ,to_char(ptime,'DD/MM/YYYY HH:MI:SS AM') "Last_Changed"
FROM sys.user$ a, dba_users b
where a.name=b.username and b.username=upper('&username')
order by 1;
DESC sys.user$
Name Null Type
------------ -------- --------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
DATATS# NOT NULL NUMBER
TEMPTS# NOT NULL NUMBER
CTIME NOT NULL DATE
PTIME DATE
EXPTIME DATE
LTIME DATE
RESOURCE$ NOT NULL NUMBER
AUDIT$ VARCHAR2(38)
DEFROLE NOT NULL NUMBER
DEFGRP# NUMBER
DEFGRP_SEQ# NUMBER
ASTATUS NOT NULL NUMBER
LCOUNT NOT NULL NUMBER
DEFSCHCLASS VARCHAR2(30)
EXT_USERNAME VARCHAR2(4000)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon ***
SQL> select systimestamp AT TIME ZONE 'Asia/Kolkata' from dual;
SYSTIMESTAMPATTIMEZONE'ASIA/KOLKATA'
---------------------------------------------------------------------------
09-APR-24 06.16.09.711511 AM ASIA/KOLKATA
***********************************************************************************
Shell script to import profiles :-
----------------------------------------------
#!/bin/bash
if test $(whoami) == "ggsuser"
then
source ~/.profile # for importing all environment variables . ~/.profile
source ~/.bash_profile # for importing all environment variables . ~/.bash_profile
source ~/.bashrc # for importing all aliases . ~/.bashrc
fi
******************************************************************************
SESSION INFO :-
--------------------------
More detail query :-
select a.username, a.osuser,a.sid,a.serial#,a.status,a.schemaname,to_char(a.sql_exec_start,'DD-MON-YY HH:MI:SS AM') "sql_exec_start",
to_char(sysdate,'DD-MON-YY HH:MI:SS AM') "current_date_time",a.event,c.sql_text,
a.inst_id, a.program, a.machine,A.STATUS,a.sql_id,a.LAST_CALL_ET
from gv$session a, gv$process b, gv$sqlstats c where a.USERNAME not in('username','SYS') AND A.STATUS='ACTIVE'
and a.inst_id = b.inst_id and a.paddr = b.addr and c.sql_id=a.sql_id order by a.LAST_CALL_ET,a.sql_id;
******************************************************************
KILL SESSION COMMAND:-
-------------------------------------
SET SERVEROUT ON;
begin
for x in (
select a.username, a.sid,a.serial#, a.inst_id, a.program, a.machine,A.STATUS,a.sql_id,a.LAST_CALL_ET
from gv$session a, gv$process b where a.USERNAME not in('USERNAME','SYS') AND A.STATUS='ACTIVE'
and a.inst_id = b.inst_id and a.paddr = b.addr order by a.LAST_CALL_ET,a.sql_id
)
loop
dbms_output.put_line('alter system kill session '||''''||x.sid||', '||x.serial#||''''||';');
end loop;
end ;
/
***************************************************************************
