Oracle Database Scripts

Sunday, May 7, 2023

USER MANAGEMENT IN ORACLE DATABASE

Check Current User Permission
------------------------------------------------------
select * from USER_ROLE_PRIVS where USERNAME=USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;
----------------------------------------------------------------
To Check the roles granted to a user:
--------------------------------------
select * from dba_role_privs where grantee = 'username';
select * from dba_tab_privs where grantee = 'username';
select * from dba_sys_privs where grantee = 'username';
------------------------------------------------------------------

To check status of user on database:--

select

(select host_name from v$instance) "Host_Name",

(select to_char(sysdate,'DD-MM-YY HH:MM:SS') from dual) "Date",

username,

account_status

from dba_users

where

username='&username';

output:

Host_Name Date username account_status

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

example 08-May-23 scott open

To Check Profile for a user :-

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

select username,profile from dba_users where username='&username';

select profile,resource_name,limit from dba_profiles where profile='&profile';

To Change User/schema password :-

alter user username account unlocked;

alter user username identified by "password";

To Change Profile Resource limit:-

ALTER PROFILE sample_profile LIMIT <resource_name> <resource_limit>;

To Create Profile for User:-

This one is ideal but not preferable to use in production environment for security reason just we can use this for testing purpose on lab environment.

SQL>

create profile <profile_name> limit

composite_limit unlimited

sessions_per_user unlimited

cpu_per_session unlimited

cpu_per_call unlimited

logical_reads_per_session unlimited

logical_reads_per_call unlimited

idle_time unlimited

connect_time unlimited

private_sga unlimited

failed_login_attempts unlimited

password_life_time unlimited

password_reuse_time unlimited

password_reuse_max unlimited

password_verify_function null

password_lock_time unlimited

password_grace_time unlimited;

If We Want To Change Profile For User:-

alter user <username> profile <profile_name>;

GET DIRECTORY LOCATION ON ORACLE DATABASE

echo "
col name_col_plus_show_param for a20;
col value_col_plus_show_param for a80;
show parameter db_recovery_file_dest;
" | sqlplus \/ as sysdba | grep -o "\w*/[[:graph:]]*" > bkploc.txt

--------------------- To find the RMAN backup locations --------------------------------------------

echo "

echo "select fname from v\$backup_files where file_type='PIECE' order by completion_time desc fetch first 1 rows only;" | sqlplus \/ as sysdba | dirname $(grep -e "^/[[:graph:]]*")

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

TABLESPACE ORACLE DATABASE GUIDE

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,]
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
for getting usage for only given tablespace
---------------------------------------------------------------
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and fs.tablespace_name='&tablespace_name'
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#TEMP TABLESPACE ######################
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_GB;
=========================================================================
select file_name,autoextensible,sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='&tablespace_name' group by file_name,autoextensible order by file_name desc;
=========================================================================
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
=========================================================================
select tablespace_name, bytes / (1024*1024*1024) "Size (GB) from dba_data_files;
for resize datafile :
alter database datafile 'path' resize 4G ;
for adding datafile :
alter tablespace tablespace_name add datafile 'path' size 3G autoextend off ;

################################ FOR RAC DB ################
Add or Resize Datafile in RAC database

alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend on ;
alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend off;

alter database datafile '+/path/to/datafile' resize nG;

FRA STATUS ON ORACLE DATABASE

set linesize 500
col NAME for a20
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select ((round(SPACE_USED/1024/1024/1024,2)/ROUND(SPACE_LIMIT/1024/1024/1024,2))*100) from v$recovery_file_dest) "Percentage Used %",
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

Oracle Patching Guide

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, source_version,patch_type,target_version, status,Action_time from dba_registry_sqlpatch order by action_time ;
=========================================================================
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
***************************************************************************************************************
To check GRID PATCH VERSION:
$ps -ef | grep -i d.bin
$ cd bin
/bin:$
./crsctl query crs releasepatch
./crsctl query crs activeversion
./crsctl query crs releaseversion
./crsctl query crs softwarepatch [node_name]
eg. ./crsctl query crs softwarepatch flpd670
./crsctl query crs softwareversion [node_name]
eg. ./crsctl query crs softwareversion flpd670
or login as oragrid ---> sqlplus / as sysadmin ---> run SQL Query

Archivelog Complete Guide

************Daily Archive Log Generation****************
SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from gv$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
DAY THREAD# GB ARCHIVES_GENERATED
-------------------- ---------- ---------- ------------------
10-OCT-2016 00:00:00 1 19 233
11-OCT-2016 00:00:00 1 34 417
12-OCT-2016 00:00:00 1 42 522
*************Hourly Archive Log Generation****************
set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from gv$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;

HOUR THREAD# GB ARCHIVES
--------- ---------- ---------- ----------
18-MAR-23 1 0 1
18-MAR-23 1 0 1
18-MAR-23 1 2 5
18-MAR-23 1 0 2
*****************************************************

TABLE CONSTRAINTS STATUS

set lines 400
col constraint_name for a20;
col constraint_type for a20;
col search_condition for a20;
select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('table_name');

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where owner='schema';

col owner for a20;
col constraint_name for a20;
col constraint_type for a5;
col table_name for a20;
col status for a20;
col validated for a5;
col invalid for a5;
col index_owner for a20;
col index_name for a20;
select owner,constraint_name,constraint_type,table_name,status,validated,invalid,index_owner,index_name from all_constraints where owner='&username' and table_name='&table';

spool 1_truncateActiveUserTables_02262023.out

set echo on

show user

select * from global_name;

-- disable the constraints so the table can be truncated

ALTER TABLE SCHEMA.TABLE

DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

ALTER TABLE SCHEMA.TABLE

ENABLE CONSTRAINT CONSTRAINT_NAME;

-- disable the constraints

ALTER TABLE SCHEMA.TABLE

DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

ALTER TABLE SCHEMA.TABLE

ENABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- confirm counts are all 0

select count(*) from SCHEMA.TABLE;

spool off

********************* To check status of constraints on tables **********************

set lines 400

col constraint_name for a20;

col constraint_type for a20;

col search_condition for a20;

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('TABLE','TABLE');

DATA PUMP IMPDP EXPDP

set lines 400;
col owner_name for a10;
col job_name for a20;
col operation for a40;
col job_mode for a20;
col state for a20 ;
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs
where state='EXECUTING';
==================================================
select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
======================================================
+++++++++++++++ Shows waiting status of data pump ++++++++++++++
set lines 400;
col module for a20;
col state for a10;
col event for a40;
col sql_text for a80;
select s.sid, s.module, s.state,
substr(s.event, 1, 21) as event,
s.seconds_in_wait as secs,
substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;
****************** showing percentage completed ocptechnology *************************
set lines 400;
col opname for a20;
col username for a20;
SELECT OPNAME, SID, SERIAL#, username,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

***************** DBAGENISIs****************************
SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
==========================================================
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
---------------------------------------------------------------------------------------------
alter tablespace JBBOS_DAT add datafile '+DATA' size 30g;

--------------------------------------------------------------------------------------
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from gv$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from gv$tablespace b, gv$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_GB;

--------------------------------------------------------------------------------
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
===================================================================

select tablespace_name,file_name,bytes/(1024*1024*1024) "size_GB" from dba_temp_files;
alter tablespace TEMP add tempfile '+DATA' size 30G;
=========================================================================

Notes: expdp and impdp
If the Target database’s release is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release.
The VERSION parameter should be used when the Source database’s release is higher than the Target database.

expdp can be done --> from lower version to higher version
expdp can be need (version=oracle version) ---> from higher version to lower version
=================================================================================================
[user@hostname]$ cat schema_export_prod_<date-time>.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_01132023_%U.dmp
LOGFILE=exp_prod_schema_01132023.log
PARALLEL=6
FILESIZE=10G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
JOB_NAME=exp_prod_schema_01132023

[user@hostname]$ cat schema_prod_expdp_table_tablename.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_<table_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>_<table_name>.log
PARALLEL=12
FILESIZE=15G
COMPRESSION=ALL
CLUSTER=N
STATUS=30
#JOB_NAME=exp_prod_<schema_name>_<table_name>_01132023
INCLUDE=TABLE:"IN('<table_name>')"

[user@hostname]$ cat imp_<schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_prod_<schema_name>_01282023_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_02022023.log
TABLE_EXISTS_ACTION=REPLACE
#o#JOB_NAME=<schema_name>_<table_name>
EXCLUDE=VIEW,PROCEDURE,PACKAGE,FUNCTION,TRIGGER
#schemas=<schema_name>
EXCLUDE=SCHEMA:"IN('<schema1_name>','<schema2_name>')"
PARALLEL=32
CLUSTER=N
FULL=Y

[user@hostname]$ cat <schema_name>_<host_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_10022018.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N

[user@hostname]$ cat <schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N

[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_database_%U.dmp
LOGFILE=Full_<schema_name>_<table_name>.log
##JOB_NAME=03282016_IMP_EXPORT_ST1_NEW
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>)"

[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_DATABASE_exp_%U.dmp
LOGFILE=Full_database_exp.log
##JOB_NAME=<full_database_exp>
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>')"

[user@hostname]$ cat DATABASE_TABLE_INDEX_CREATE_SCRIPT.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=NAME_%U.dmp
LOGFILE=database_INDEX.log
JOB_NAME=database_INDEX
CLUSTER=N
PARALLEL=24
INCLUDE=INDEX
sqlfile=DATABASE_INDEXES_CREATE.sql

[user@hostname]$ cat <schema_name>_export_prod.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>.log
PARALLEL=12
FILESIZE=15G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
EXCLUDE=TABLE:"IN ('<table_name>',...,'<table_name>')"
JOB_NAME=exp_prod_<schema_name>_<table_name>

########### Most Important Note ######################
Always take expdp backup for schemas before dropping its objects
------------>> Take expdp schema backup before dropping schema objects <<---------------
or take restore point or rman backup before doing same if asked.

ADBA VALIDATION STEPS FOR CR ( CHANGE REQUEST)

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

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

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

******************************************************************************

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 ;

***************************************************************************

RMAN BACKUP STATUS DETAILS SCRIPTS

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

---------------------######## RMAN Backup logs details will show which are files completed ##############------------------------

cd logs/voyager/backups

voyager:/home/oracle/ $ cd logs/voyager/backups

voyager:logs/voyager/backups/ $ ls -lrth

total 3218

-rw-r----- 1 oracle dba 54K Feb 21 08:17 DB_Full_Backup_202202210158.log

-rw-r----- 1 oracle dba 54K Feb 21 08:18 DB_Full_Backup_202202210155.log

-rw-r----- 1 oracle dba 103K Feb 21 23:27 DB_Full_Backup_202202212300.log

-rw-r----- 1 oracle dba 155K Feb 23 01:09 DB_Full_Backup_202202222300.log

-rw-r----- 1 oracle dba 132K Feb 24 01:13 DB_Full_Backup_202202232300.log

-rw-r----- 1 oracle dba 29K Feb 25 00:25 DB_Full_Backup_202202242300.log

voyager:logs/voyager/backups/ $ more DB_Full_Backup_202202242300.log

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

SQL> col STATUS format a9

SQL> col hrs format 999.99

SQL> 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;

SQL> /

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .00

SQL> /

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .01

SQL> /

SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS

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

29 DB FULL COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

======================================================================================

next script

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

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

SID SERIAL# CONTEXT SOFAR TOTALWORK %COMPLETE

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

18 29 1 9115569 19258880 47.33

===========================================================================

next script

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

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000

col Hours format 9999.99

col STATUS format a10

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SESSION_KEY INPUT_TYPE STATUS RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME HOURS

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

137764 DB FULL COMPLETED 06-06-2017 02:00:32 06-06-2017 04:12:13 2.19

137770 ARCHIVELOG COMPLETED 06-06-2017 04:00:29 06-06-2017 04:01:05 .01

137778 ARCHIVELOG COMPLETED 06-06-2017 06:00:27 06-06-2017 06:00:35 .00

137782 ARCHIVELOG COMPLETED 06-06-2017 08:00:32 06-06-2017 08:03:36 .05

137786 ARCHIVELOG COMPLETED 06-06-2017 10:00:30 06-06-2017 10:02:03 .03

137790 ARCHIVELOG COMPLETED 06-06-2017 12:00:30 06-06-2017 12:02:34 .03

137794 ARCHIVELOG COMPLETED 06-06-2017 14:00:30 06-06-2017 14:02:58 .04

=========================================================================================

next script

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

select OUTPUT from V$RMAN_OUTPUT;

============================================================================

To check backup status is running and how much minutes remaining...
SQL> select opname,round(time_remaining/60) time_remaining_mins from 
gv$session_longops where opname like '%RMAN%' and time_remaining<>0;

opname                                      time_remaining_mins
------------------------------------------------------------------
RMAN: aggregate input                       4.4 
RMAN: incremental datafile backup           9.05 

SQL> select fname, round(bytes/power(1023,3)) SIZE_GB from v$backup_files 
where file_type='PIECE';

FNAME                                                       SIZE_GB
------------------------------------------------------------------------
/path/to/backup_file.dbf                                    10.14 

---------------
v$backup_files 
---------------
BACKUP_TYPE='BACKUP SET/COPY/PROXY COPY'
FILE_TYPE = 'DATAFILE/CONTROLFILE/SPFILE/REDO LOG/ARCHIVED LOG/
COPY(for an image copy backup)/PIECE(for a backup piece)'

How To Monitor Gather Schema stats status and progress and also Tablespace Used by Users

to check schema gather stats :-

SELECT inst_id,sid,serial#,opname,target_desc,round(sofar/totalwork*100,2) "%_completed",units,start_time,last_update_time,time_remaing "time_remaining(seconds)",elapsed_seconds,context,message,username,sql_address,sql_hash_value,
sql_id,sql_exec_start,sql_plan_operation,sql_plan_options FROM gv$session_longopsWHERE opname LIKE '%Gather%' AND time_remaing "time_remaining(seconds)" != 0
ORDER BY SID;

SELECT inst_id,sid,serial#,opname,target_desc,round(sofar/totalwork*100,2) "%_completed",username,units,
to_char(start_time,'DD-MM-YY HH:MM:SS') start_time,last_update_time,time_remaining time_remainig_seconds,elapsed_seconds,
message,sql_address,sql_hash_value,sql_id,sql_exec_start,sql_plan_operation,sql_plan_options
FROM gv$session_longops
WHERE opname LIKE '%Gather%' AND time_remaining != 0 and username!='SYS'
ORDER BY SID;

Best query to monitor whose using most of the tablespace in Oracle:

SELECT b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE )) / power(1024,3 ), 2 ) size_GB
, a.inst_ID
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
-- AND b.TABLESPACE='TEMP2'
order by size_GB desc;

and to kill sessions :

SELECT b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
-- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;

Oracle Database Scripts

Sunday, May 7, 2023

USER MANAGEMENT IN ORACLE DATABASE

GET DIRECTORY LOCATION ON ORACLE DATABASE

TABLESPACE ORACLE DATABASE GUIDE

FRA STATUS ON ORACLE DATABASE

Oracle Patching Guide

Archivelog Complete Guide

TABLE CONSTRAINTS STATUS

DATA PUMP IMPDP EXPDP

ADBA VALIDATION STEPS FOR CR ( CHANGE REQUEST)

RMAN BACKUP STATUS DETAILS SCRIPTS

How To Monitor Gather Schema stats status and progress and also Tablespace Used by Users

Enable OpenSSH on Windows 11

Translate