Database_STATUS set line 190 Select db_unique_name,open_mode,database_role,(select host_name from v$instance) HOST_NAME from v$database; Check the Oracle Instance Startup Time SET LINES 200 SET PAGES 999 COLUMN INSTANCE_NAME FOR A20 SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME" FROM V$INSTANCE; To check FRA free space availability ------------------------------------------------- SELECT ROUND((A.SPACE_LIMIT /1024/1024/1024), 2) AS FLASH_IN_GB, ROUND((A.SPACE_USED /1024/1024/1024), 2) AS FLASH_USED_IN_GB, ROUND((A.SPACE_RECLAIMABLE /1024/1024/1024), 2) AS FLASH_RECLAIMABLE_GB, SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED FROM V$RECOVERY_FILE_DEST A, V$FLASH_RECOVERY_AREA_USAGE B GROUP BY SPACE_LIMIT,SPACE_USED , SPACE_RECLAIMABLE; ************** Disk GROUP ************** col name for a20 SELECT name, total_mb/1024 "TOTAL_GB",free_mb/1024 "FREE_GB",USABLE_FILE_MB/1024 usable_gb,ROUND((1- (free_mb / total_mb))*100, 2) as pct_used FROM v$asm_diskgroup; select name, state, total_mb/1024 total_gb, free_mb/1024 free_gb, USABLE_FILE_MB/1024 usable_gb from v$asm_diskgroup; select name, state, total_mb/1024/1024 total_tb, free_mb/1024/1024 free_tb, USABLE_FILE_MB/1024/1024 usable_tb from v$asm_diskgroup; ********** RMAN BACKUP Status set lines 200 pagesize 200 col INPUT_TYPE for a20 col status for a30 col OUTPUT_BYTES_DISPLAY for a20 col START_TIME for a20 col end_time for a20 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, round(elapsed_seconds/3600,2) hrs, OUTPUT_BYTES_DISPLAY, OUTPUT_DEVICE_TYPE from V$RMAN_BACKUP_JOB_DETAILS order by session_key; col NAME format a30 trunc col SCN format 999999999999999999999 col TIME format a30 trunc set linesize 300 pagesize 9999 SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES'; select name, db_unique_name, open_mode, database_role, flashback_on from v$database; RMAN> crosscheck archivelog all; RMAN> delete noprompt archivelog all; #### Check +RECO and Resize FRA if not able to decrease the FRA ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3500G SCOPE=BOTH SID='*'; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 32G SCOPE=BOTH SID='*'; delete force archivelog until time 'SYSDATE-2'; delete noprompt backup completed before 'SYSDATE-1';
Check the PDB/CDB Size of the databases
select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;
Check size of database-none-pdb
select sum(bytes/1024/1024/1024/1024) "Database Size(TB)" from dba_data_files;
select sum(bytes/1024/1024/1024) "Database Size(GB)" from dba_data_files;
select sum(bytes/1024/1024/1024/1024) "Database Size(TB)" from dba_segments;
select sum(bytes/1024/1024/1024/1024) "Database Size(TB)" from dba_segments;
___________________________
DATABASE SIZE.
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
/
_____________________________
To Check FRA freespace Availability
_____________________________
SELECT ROUND((A.SPACE_LIMIT /1024/1024/1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED /1024/1024/1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE /1024/1024/1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED FROM V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B GROUP BY SPACE_LIMIT,SPACE_USED , SPACE_RECLAIMABLE;
____________________________________________________________________________________
col NAME format a30 trunc
col SCN format 999999999999999999999
col TIME format a30 trunc
set linesize 300 pagesize 9999
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
select name, db_unique_name, open_mode, database_role, flashback_on from v$database;
___________________
Check for RECO
___________________
select name,total_mb/1024,usable_file_mb/1024 from v$asm_diskgroup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt archivelog all;
_____________________
For Checking the backups
_________________________
set lines 200 pagesize 200
col INPUT_TYPE for a20
col status for a30
col OUTPUT_BYTES_DISPLAY for a20
col START_TIME for a20
col end_time for a20
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,
round(elapsed_seconds/3600,2) hrs, OUTPUT_BYTES_DISPLAY, OUTPUT_DEVICE_TYPE from V$RMAN_BACKUP_JOB_DETAILS order by session_key;
_______________
DISK Group
_______________
select GROUP_NUMBER,NAME,TOTAL_MB/1024/1024 as "Total Space in TB" ,FREE_MB/1024/1024 as "Free Space in TB" ,DATABASE_COMPATIBILITY from v$asm_diskgroup;
set pagesize 200
set linesize 200
col name format a20
col "%_Free" format a10
col "%_Used" format a10
col Total_GB format 99999999.99
select
name,
round((round(total_mb,2)-round(free_mb,2))/1024,2) as USED_GB,
round(free_mb/1024,2) as FREE_GB,
round(total_mb/1024,2) as TOTAL_GB,
round((free_mb/total_mb)*100,2) || '%' as "%_Free",
100-(round((free_mb/total_mb)*100,2)) ||'%' as "%_Used"
From v$asm_diskgroup order by 4;
+++++++++++++++++++++++++++
TABLESPACE DATAFILE
___________________________
SELECT tablespace_name, file_name
FROM dba_temp_files
ORDER BY tablespace_name;
SELECT tablespace_name,
(bytes_free + bytes_used) / 1024 / 1024 / 1024 "TOTAL SIZE(MB)",
BYTES_FREE / 1024 / 1024 / 1024 "FREE SPACE (MB)",
BYTES_USED / 1024 / 1024 / 1024 "USED SPACE (MB)"
FROM v$temp_space_header
ORDER BY BYTES_FREE
/
Dataguard Scripts
Non-RAC: select process, status, thread#, sequence#, block#, blocks from v$managed_standby where process like 'MRP%';
RAC: select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process like 'MRP%';
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ORDER BY PROCESS;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
#############
Lag
###########
set lines 190
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
SELECT ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference",
arch.last_app_timestamp Last_app_date
FROM (SELECT THREAD# ,MAX(SEQUENCE#) SEQUENCE#,MAX(next_time) last_app_timestamp FROM V$ARCHIVED_LOG GROUP BY THREAD#) ARCH,
(SELECT THREAD# ,MAX(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG WHERE APPLIED='YES' GROUP BY THREAD#) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');
col MEMBER format a66
set linesize 300 pagesize 9999
col bytes format 99999999999999
col DBID format a10
select a.group#, a.thread#, status from v$log a order by 1,2;
select a.group#, a.thread#, a.bytes, a.blocksize, b.member, a.status from v$log a, v$logfile b where a.group#=b.group# order by a.group#,a.thread#;
select * from v$standby_log;
TABLESPACE–
Check Tablespace
col owner for a15
select owner,sum(bytes/1024/1024/1024) size_gb from dba_segments group by owner;
*******Check Individual Tablespace Size
col tablespace_name format a25
set pagesize 200
select
tablespace_name,
round(((tablespace_size) * (select value from v$parameter where name='db_block_size'))/1024/1024/1024,2) tablespace_size_GB,
round(((used_space) * (select value from v$parameter where name='db_block_size'))/1024/1024/1024,2) used_space_GB,
round(used_percent,2) as used_percent
from
DBA_TABLESPACE_USAGE_METRICS
where tablespace_name='&Tablespace_name'
order by 3;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Set lines 300
select
df.tablespace_name AS "Tablespace",
ROUND(totalusedspace,1) AS "Used GB",
ROUND((df.totalspace - tu.totalusedspace),1) AS "Free GB",
ROUND(df.totalspace,1) AS "Total GB",
ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) AS "Pct. Free",
count_datafiles AS "Count Datafiles"
from
(select tablespace_name,
sum(bytes) / (1024*1024*1024) TotalSpace,
count(*) AS count_datafiles
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name (+)
order by 1;
*********Create script to resize database which size less*****
select 'alter database datafile '||file_id||' resize 30G;' from dba_data_files where tablespace_name='UE0C_UBS_TS01_DATA' group by file_id,file_name having sum(bytes/1024/1024/1024)<30;
select OWNER,sum(bytes/1024/1024/1024) size_gb from dba_segments where TABLESPACE_NAME='&Tablespace' group by owner;
+++++++++++++++++++++++++++++++
col TABLESPACE_NAME for a15
col file_id for a10
col file_name for a50
select tablespace_name,file_id,file_name,sum(bytes/1024/1024/1024) size_gb from dba_data_files where tablespace_name in('FCDM_DATA') group by file_name,file_id,tablespace_name order by tablespace_name ;
alter tablespace Tablespace_name add datafile '+DATA' size 30G;
select owner,segment_name,segment_type,sum(bytes/1024/1024/1024) size_gb from dba_segments where TABLESPACE_NAME='TABLESPACE_NAME' group by owner,segment_name,segment_type having sum(bytes/1024/1024/1024)>0;
select file_id,file_name,sum(bytes/1024/1024/1024) size_gb from dba_data_files where tablespace_name='TABLESPACE_NAME' group by file_name,file_id;
Check User Status –
Check User Status--
col USERNAME for a30
col PROFILE for a30
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username like '&USERNAME';
SET LINESIZE 200 VERIFY OFF
col INITIAL_RSRC_CONSUMER_GROUP for a40
COLUMN username FORMAT A20
COLUMN account_status FORMAT A16
COLUMN default_tablespace FORMAT A15
COLUMN temporary_tablespace FORMAT A15
COLUMN profile FORMAT A15
SELECT username,
account_status,
TO_CHAR(lock_date, 'DD-MON-YYYY') AS lock_date,
TO_CHAR(expiry_date, 'DD-MON-YYYY') AS expiry_date,
default_tablespace,
temporary_tablespace,
TO_CHAR(created, 'DD-MON-YYYY') AS created,
profile,
initial_rsrc_consumer_group,
editions_enabled,
authentication_type
FROM dba_users
WHERE username LIKE UPPER('%&1%')
ORDER BY username;
Database Size
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 /
Archive log Generation
Daily Archive Log Generation ----------------------------- select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB, count(*) Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'),thread# order by 1; __________________________________ 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 v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Reason for RMAN backup failed
------------------------------------------- select session_key, input_type, status, input_bytes, output_bytes, to_char(start_time, 'dd.mm.yyyy hh24:mi:ss') start_time, to_char(end_time, 'dd.mm.yyyy hh24:mi:ss') end_time, output_device_type, elapsed_seconds from v$rman_backup_job_details; set linesize 300 pagesize 10 echo on COL STATUS FORMAT a15 COL hrs FORMAT 999.99 col START_TIME format a25 col END_TIME format a25 COL in_size FORMAT a10 COL out_size FORMAT a10 COL COMMAND_ID format a30 alter session set nls_date_format='mm/dd/yyyy hh24:mi'; SELECT SESSION_KEY, SESSION_RECID, SESSION_STAMP, INPUT_TYPE, STATUS,COMMAND_ID,START_TIME,END_TIME, round(ELAPSED_SECONDS/3600) hrs, round(INPUT_BYTES/1024/1024/1024) in_size_GB, round(output_bytes/1024/1024/1024) out_size_GB FROM V$RMAN_BACKUP_JOB_DETAILS --where INPUT_TYPE like 'DB INCR' ORDER BY SESSION_STAMP; ********************************************************************************** Oracle Database do generate the logs for RMAN and you can find the same by using the following query set lines 200 set pages 1000 select output from GV$RMAN_OUTPUT where session_recid = &SESSION_RECID and session_stamp = &SESSION_STAMP order by recid; all you need is SESSION_RECID and SESSION_STAMP,and you can get them from the following query select session_recid,session_stamp from v$rman_status where start_time like '27-May-23'; (change the date according to your needs for which you want to see the log)
USER and sql session detail in oracle database
USER and sql session detail in oracle database set linesize 1234 pagesize 300 col username format a20 col machine format a35 select username,machine,status,INST_ID,count(1) from gv$session group by username,machine,status,INST_ID order by status; set linesize 1234 pagesize 300 col username format a20 col machine format a35 select sid,username,machine,status,INST_ID,program,count(1) from gv$session where username is not null group by sid,username,machine,status,INST_ID,program order by status; set linesize 1234 pagesize 300 COLUMN spid FORMAT A10 COLUMN username FORMAT A15 COLUMN program FORMAT A65 SELECT s.inst_id, s.sid,s.serial#, p.spid, s.username, s.status, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND' order by s.status;
KILL Session-
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
--s.sql_id,
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.type != 'BACKGROUND';
ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
ALTER SYSTEM KILL SESSION '3988,12933' IMMEDIATE;
ALTER SYSTEM KILL SESSION '3988,12933,@4';
select sid, serial# from v$session where sid=3988;
ALTER SYSTEM KILL SESSION '3988,12933,@4'IMMEDIATE;