Tablespace size and uses
set linesize 230
SELECT Total.name "TABLESPACE_NAME", nvl(Free_space, 0) FREE_SPACE, nvl(total_space-Free_space, 0) USED_SPACE, total_space,round((nvl(free_space,0)/nvl(total_space,0)*100),2) "PCTFREE" ,round((nvl(total_space-free_space,0)/nvl(total_space,0)*100),2) "PCTUSED" FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name ) Free, (select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name ) Total WHERE Free.Tablespace_name(+) = Total.name order by 6;
RMAN Catalog resync
$rman TARGET / RMAN> CONNECT CATALOG rman@SHA_PROD allocate channel for maintenance type disk; crosscheck backup of database; delete expired backup of archivelog all; change archivelog all crosscheck; RESYNC CATALOG; release channel;
Objects used information
select p.object_name c1,p.operation c2,p.options c3,p.sql_id c4,count(1) c5 from dba_hist_sql_plan p,dba_hist_sqlstat s where p.object_owner = 'HR' and p.operation like '%TABLE%' and p.sql_id = s.sql_id group by p.object_name,p.operation,p.options,p.sql_id order by 1,2,3;
SQL STATEMENT TIME wise
SELECT e.SID,SUBSTR(osuser,0,7) osuser,SUBSTR(username,0,8) username,substr(status,1,1) status,SUBSTR(machine,0,11) machine, TO_CHAR(ROUND(value/1024),9999) || ' KB' memory,TO_CHAR((sysdate-logon_time)*24*60,999999.99) minutes,q.sql_text FROM v$session e,v$sesstat s,v$statname n,v$sql q WHERE s.statistic# = n.statistic# AND n.name = 'session uga memory max' AND e.sid = s.sid AND q.hash_value(+) = e.sql_hash_value order by machine,minutes; Session information loginwise select s.username,s.module, s.osuser, p.program,s.logon_time,s.terminal, p.spid from v$session s, v$process p where s.paddr = p.addr order by 5;
select sid,serial#,username,status,to_char(logon_time,'dd-mm-yyyy:hh24:mi:ss') from v$session where status='ACTIVE' order by 4;
Active Session Information:
SET pagesize 999
SET linesize 150
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A30
SELECT s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.logon_time
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE s.type != 'BACKGROUND'
and s.username='SHA'
and s.status='ACTIVE';
Find Long Operation (program running in database)
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;
spool kill.sql
select 'kill -9 ' || spid from v$process where addr in (
select paddr from v$session where (username not like ' '
and username not like 'SYS') and program like 'XXX' and username like 'HR');
Setting DISPLAY in SUDO
1. Login as yourself (userid)
2. See your DISPLAY settings and xauth and note what it is:
$ echo $DISPLAY
$ xauth list
3. Change to another user (shadb)
$ sudo su – shadb
4. Set the display to the same as in step 2:
$ DISPLAY=’whatever_host:whatever_number’;export DISPLAY
5. Add this to the authority with xauth command (use the ‘xauth list’ output from step 2)
$ xauth add ‘copy/paste the output of xauth list before’
Find DB Link user password:
select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u where d.username = upper('&&username') and u.user# = d.user_id;
ADRCI Basic Command:
adrci> purge -age 10080 -type ALERT adrci> purge -age 10080 -type TRACE adrci> purge -age 10080 -type incident adrci> purge -age 10080 -type hm adrci> purge -age 10080 -type utscdmp adrci> purge -age 10080 -type cdump ** Also you may want to purge all files at once adrci> purge -age 10080
Find candidate Objects for Purge: select 'alter database '||a.name||' datafile '''||b.file_name||'''' || ' resize '||greatest(trunc(bytes_full/.7) ,(bytes_total-bytes_free)) from v$database a, dba_data_files b ,(Select tablespace_name,sum(bytes) bytes_full From dba_extents Group by tablespace_name) c ,(Select tablespace_name,sum(bytes) bytes_total From dba_data_files Group by tablespace_name) d ,(Select a.tablespace_name,a.file_id,b.bytes bytes_free From (select tablespace_name,file_id,max(block_id) max_data_block_id from dba_extents group by tablespace_name,file_id) a ,dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and b.block_id > a.max_data_block_id) e Where b.tablespace_name = c.tablespace_name And b.tablespace_name = d.tablespace_name And bytes_full/bytes_total < .7 And b.tablespace_name = e.tablespace_name And b.file_id = e.file_id; Create DDL for Purge: select 'alter database RTD1D datafile '''||b.file_name||'''' ||' resize '||greatest(trunc(bytes_full/.7),(bytes_total-bytes_free))||';'||chr(10)|| '--tablespace was '||trunc(bytes_full*100/bytes_total)||'% full now '||trunc(bytes_full*100/greatest(trunc(bytes_full/.7),(bytes_total-bytes_free)))||'%' from v$database a,dba_data_files b,(Select tablespace_name,sum(bytes) bytes_full From dba_extents Group by tablespace_name) c,(Select tablespace_name,sum(bytes) bytes_total From dba_data_files Group by tablespace_name) d,(Select a.tablespace_name,a.file_id,b.bytes bytes_free From (select tablespace_name,file_id,max(block_id) max_data_block_id from dba_extents group by tablespace_name,file_id) a,dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and b.block_id > a.max_data_block_id) e Where b.tablespace_name = c.tablespace_name And b.tablespace_name = d.tablespace_name And bytes_full/bytes_total < .7 And b.tablespace_name = e.tablespace_name And b.file_id = e.file_id; ;
Remove crontab enrty after taking backup of it: crontab -l > crontab.save crontab crontab_blank cat crontab.save > crontab crontab -l crontab crontab.save Kill session running longer then 600 sec: select 'kill -9 ' || p.spid from v$process p,v$session s where s.paddr=p.addr and s.username='HR' and status='INACTIVE' and LAST_CALL_ET > 600; PORT CHECK (Given port are open or not) telnet <host_name> <portnumner> --We have to run this from Client netstat -an | grep <portnumber> -- We have to run this from Server SQL> conn system/password@//sha.com:1522/sha1d -- Connect sample
ADRCI
adrci> purge -age 10080 -type ALERT
adrci> purge -age 10080 -type TRACE
adrci> purge -age 10080 -type incident
adrci> purge -age 10080 -type hm
adrci> purge -age 10080 -type utscdmp
adrci> purge -age 10080 -type cdump
#!/bin/ksh
#
# run adrci to shorten the ADR retention policies:
#
# SHORTP_POLICY = 168 hours (7 days) instead of the default of 720 hours (30 days) for automatic purging of:
# TRACE
# CDUMP
# UTSCDMP
# IPS
#
# LONGP_POLICY = 8760 hours (1 year) which is the default, for automatic purging of:
# ALERT
# INCIDENT
# SWEEP
# STAGE
# HM
#
#set -x
Base=$(basename $0 '.sh')
RunTime=$(date "+%Y%m%d%H%M")
RunLog=${DB_RUNLOGS}/${Base}.${RunTime}.out
exec > $RunLog 2>&1
adrci << EOF
set home diag/rdbms/sha1/sha1
show control
set control (SHORTP_POLICY = 168)
set control (LONGP_POLICY = 8760)
show control
Database parameter setting:
-- Reduce the retention of optimizer statistics history in the sysaux -- tablespace from a default of 31 days to 6 days. set echo on set termout on spool $DB_RUNLOGS/DbmsStatsAlterStatsHistoryRetention.out connect / as sysdba select dbms_stats.get_stats_history_retention from dual; exec dbms_stats.alter_stats_history_retention(6); select dbms_stats.get_stats_history_retention from dual; exit -- Reduce the retention of unused SQL plan baselines in the SQL management base -- in the sysaux tablespace from the default of 53 weeks to the minimum of -- 6 weeks. These SQL plan baselines consume a lot of space. The purge happens -- weekly as an automated task in the maintenance window. set echo on set termout on spool $DB_RUNLOGS/DbmsSpmConfigure.out connect / as sysdba select * from dba_sql_management_config; exec dbms_spm.configure('PLAN_RETENTION_WEEKS', 6); select * from dba_sql_management_config; exit -- Alter memory parameters. -- The parameter memory_target (or sga_max_size) should be smaller than the minimum of OS parameters -- "process.max-address-space" and "project.max-shm-memory". -- Otherwise you will get "ORA-27102: out of memory". set echo on set termout on spool one.out connect / as sysdba alter system set db_cache_size=512M scope=both; alter system set java_pool_size=0 scope=both; alter system set large_pool_size=64M scope=both; alter system set memory_max_target=6144M scope=spfile; alter system set memory_target=6144M scope=spfile; alter system set pga_aggregate_target=256M scope=both; alter system set shared_pool_size=512M scope=both; alter system set streams_pool_size=32M scope=both; spool $DB_RUNLOGS/AlterParameters.out connect / as sysdba alter system set audit_trail=none scope=spfile; alter system set control_file_record_keep_time=14 scope=both; alter system set cursor_sharing=force scope=both; --alter system reset dispatchers scope=spfile sid='*'; alter system set log_archive_dest='/opt/oracle/admin/sha1/arch' scope=both; alter system set log_archive_format='sha1_arch%s_%t_%r.log' scope=spfile; alter system set nls_date_format='DD-MON-YYYY' scope=spfile; alter system set open_cursors=500 scope=both; alter system set os_authent_prefix='' scope=spfile; alter system set processes=1500 scope=spfile; alter system set query_rewrite_integrity='trusted' scope=both; alter system set recyclebin='off' scope=spfile; alter system set undo_retention=1800 scope=both; exit
LOG MINER
DB Level:
---------
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
Table level:
------------
alter table '||owner||'.'||table_name||' add supplemental log data (primary key) columns;
alter table '||owner||'.'||table_name||' add supplemental log data (all) columns;
SQL> SELECT supplemental_log_data_min MIN,
supplemental_log_data_pk PK,
supplemental_log_data_ui UI,
supplemental_log_data_fk FK,
supplemental_log_data_all "ALL"
FROM v$database;
Specify a LogMiner dictionary.
Use the DBMS_LOGMNR_D.BUILD procedure or specify the dictionary when you start LogMiner (in Step 3), or both, depending on the type of dictionary you plan to use.
Specify a list of redo log files for analysis.
Use the DBMS_LOGMNR.ADD_LOGFILE procedure, or direct LogMiner to create a list of log files for analysis automatically when you start LogMiner (in Step 3).
Start LogMiner.
Use the DBMS_LOGMNR.START_LOGMNR procedure.
Request the redo data of interest.
Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)
End the LogMiner session.
Use the DBMS_LOGMNR.END_LOGMNR procedure.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => '01-Jan-2003 08:30:00', -
ENDTIME => '01-Jan-2003 08:45:00', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
'/oracle/database/', -
DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '11-Aug-2016 12:40:00',ENDTIME => '11-Aug-2016 15:40:00',OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +DBMS_LOGMNR.CONTINUOUS_MINE);
DICT_FROM_ONLINE_CATALOG — See Using the Online Catalog
DICT_FROM_REDO_LOGS — See Start LogMiner
CONTINUOUS_MINE — See Redo Log File Options
COMMITTED_DATA_ONLY — See Showing Only Committed Transactions
SKIP_CORRUPTION — See Skipping Redo Corruptions
NO_SQL_DELIMITER — See Formatting Reconstructed SQL Statements for Reexecution
PRINT_PRETTY_SQL — See Formatting the Appearance of Returned Data for Readability
NO_ROWID_IN_STMT — See Formatting Reconstructed SQL Statements for Reexecution
DDL_DICT_TRACKING — See Tracking DDL Statements in the LogMiner Dictionary
======================================================================================
SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
OPERATION = 'DELETE' AND USERNAME = 'RON';
Manual DB creation:
[oracle@srac1 dbs]$ cat create.sql
CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle123
USER SYSTEM IDENTIFIED BY oracle123
LOGFILE GROUP 1 ('/u02/datafile/orcl/redo01.log') SIZE 100M,
GROUP 2 ('/u02/datafile/orcl/redo02.log') SIZE 100M,
GROUP 3 ('/u02/datafile/orcl/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL DATAFILE '/u02/datafile/orcl/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/datafile/orcl/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users DATAFILE '/u02/datafile/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/datafile/orcl/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/datafile/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
PFILE:
[oracle@srac1 dbs]$ cat create.sql
CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle123
USER SYSTEM IDENTIFIED BY oracle123
LOGFILE GROUP 1 ('/u02/datafile/orcl/redo01.log') SIZE 100M,
GROUP 2 ('/u02/datafile/orcl/redo02.log') SIZE 100M,
GROUP 3 ('/u02/datafile/orcl/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL DATAFILE '/u02/datafile/orcl/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/datafile/orcl/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users DATAFILE '/u02/datafile/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/datafile/orcl/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u02/datafile/orcl/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
[oracle@srac1 dbs]$ cat initorcl.ora
orcl.__db_cache_size=1073741824
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=419430400
orcl.__sga_target=1476395008
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=352321536
orcl.__streams_pool_size=0
*.audit_file_dest='/u02/datafile/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/datafile/orcl/control01.ctl','/u02/datafile/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u02/datafile/orcl'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u02/datafile/orcl/adump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1468006400
*.undo_retention=1500
*.undo_tablespace='UNDOTBS1'
===============================================================================
set pages 50
set lines 1000
set pages 70
set heading on
set trims on
prompt**======================
prompt** **Database Current Status**
prompt**======================
set lines 300 pages 3000
select name,open_mode,database_role from v$database;
spool DB_link_details.log
prompt**======================
prompt** **DB Link Details**
prompt**======================
set lines 300 pages 3000
COL OWNER FORMAT a10
COL USERNAME FORMAT A20
COL DB_LINK FORMAT A30
COL HOST FORMAT A30
SELECT * FROM DBA_DB_LINKS;
spool off;
spool FAILED_Jobs_details.log
prompt**======================
prompt** **Failed jobs details**
prompt**======================
set lines 300
col job_name for a33
col owner for a13
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select JOB_NAME,OWNER,STATUS,
spool off;
spool multiplexing_control_files.log
prompt**======================
prompt** **Check Multiplexing control files on different mount points/File systems.**
prompt**======================
set lines 300 pages 3000
col NAME for a50
select name from v$controlfile;
spool off;
spool system_default_tablespace.log
prompt**======================
prompt** **Identify users having SYSTEM as default tablespace or temporary tablespace.**
prompt**======================
col profile for a15
col username for a15
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
select USERNAME,to_char(CREATED,’dd-
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘XDB’) and (temporary_tablespace=’SYSTEM’ or DEFAULT_TABLESPACE=’SYSTEM’);
select USERNAME,DEFAULT_TABLESPACE,
select USERNAME,DEFAULT_TABLESPACE,
spool off;
spool Top_utilized_tablespace.log
prompt**======================
prompt** **Identify top utilizaed tablespace s**
prompt**======================
select * From DBA_TABLESPACE_USAGE_METRICS;
spool off;
spool multiplexing_redo_log.log
prompt**======================
prompt** **Check multiplexing of Redo log at different location **
prompt**======================
col member for a45
select * from v$logfile;
spool off;
spool snapshot_too_old.log
prompt**======================
prompt** **Check for snapshot too old error.**
prompt**======================
set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,
show parameter undo;
spool off;
spool tablespace_locally_managed.log
prompt**======================
prompt** **Check that all tablespaces are locally managed**
prompt**======================
select TABLESPACE_NAME,SEGMENT_SPACE_
spool off;
spool temp_tablespace.log
prompt**======================
prompt** **Check for Temp tablespace extent size.**
prompt**======================
SELECT inst_id,tablespace_name ,sum(BYTES_CACHED)/1024/1024 “ALLOCATED(MB)”,sum(BYTES_
sum(BYTES_CACHED-BYTES_USED)/
FROM gv$TEMP_EXTENT_POOL group by inst_id,tablespace_name
/
col file_name for a45
col tablespace_name for a15
select file_id,file_name,tablespace_
spool off;
spool archive_mode.log
prompt**======================
prompt** **Check archive log mode for production databases.**
prompt**======================
archive log list
spool off;
spool archive_generation_for_month.
prompt**======================
prompt** **archive_generation_for_
prompt**======================
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB – SUM_ARCH_DEL.DELETED_MB “REMAINING_MB”
FROM ( SELECT TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’) DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = ‘YES’
GROUP BY TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’)) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’) DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = ‘YES’ AND DELETED = ‘YES’
GROUP BY TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’)) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, ‘DD/MM/YYYY’);
spool off;
spool datafile_status.log
prompt**======================
PROMPT** **Check all Datafile status**
prompt**======================
col NAME for a60
SELECT name,
FILE#,
STATUS,
CHECKPOINT_CHANGE# “CHECKPOINT”
FROM V$DATAFILE;
spool off;
spool database_component.log
prompt**======================
prompt** **Check Database component status**
prompt**======================
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;
spool off;
spool dba_directories.log
prompt**======================
prompt** **Check dba directories**
prompt**======================
col owner for a10
col directory_name for a40
col directory_path for a60
select owner,directory_name,
spool off;
spool recycle_bin.log
prompt**======================
prompt** **Check Database recyclebin status**
prompt**======================
SELECT Value FROM V$parameter WHERE Name = ‘recyclebin’;
spool off;
spool RMAN_backup_config.log
prompt**======================
prompt** **Identify DBs for which RMAN backup is not configured**
prompt**======================
col GB for9,999
col START_TIME for a20
col end_TIME for a20
col LEVEL for 99
col operation for a10
col status for a10
select stamp,ROW_LEVEL “LEVEL”,OPERATION,status,(
to_char(START_TIME,’DD-MON-
order by stamp ;
spool off;
spool archive_log_generation.log
prompt**======================
prompt** **Identify the DBs with high archive log generation to tune archive log backup frequency **
prompt**======================
SET PAGESIZE 6000
SET LINESIZE 300
SET VERIFY OFF
break on report
compute sum of TOTAL_ARCHIVES on report
compute sum of TotalArchive_sIZE_in_MB on report
col arch_date for a15
SELECT thread#,TO_CHAR(completion_
count(sequence#) TOTAL_ARCHIVES,
round(sum(blocks*block_size)/
from gv$archived_log where dest_id=1 and trunc(completion_time)>=trunc(
group by thread#,TO_CHAR(completion_
spool off;
spool dba_role.log
prompt**======================
prompt** **Identify non DBA users with DBA role**
prompt**======================
select GRANTEE,GRANTED_ROLE,ADMIN_
spool off;
spool elevated_pvilages.log
prompt**======================
prompt** **Identify non DBA/System users having elevated privilages, e..g user having access to V$ views, or any privileges granted to it, or privilege with admin option**
prompt**======================
select * from dba_sys_privs where PRIVILEGE like ‘%ANY%’ and GRANTEE not in(
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘IMP_FULL_DATABASE’,
‘EXP_FULL_DATABASE’,
‘DBA’,
‘DATAPUMP_IMP_FULL_DATABASE’,
‘AQ_ADMINISTRATOR_ROLE’,
‘JAVADEBUGPRIV’,
‘SCHEDULER_ADMIN’,
‘SYSMAN’,
‘XDB’) order by 1,2;
spool off;
spool default_user_account_status.
PROMPT**======================
prompt** **Identify Oracle default users with account status as .OPEN.**
prompt**======================
select username,account_status from dba_users where username in (
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘XDB’) order by 2,1;
spool off;
spool users_privilages_access_
prompt**======================
prompt** **Identify Users with privilege to access Metadata**
prompt**======================
select * from dba_role_privs where GRANTED_ROLE=’SELECT_CATALOG_
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘IMP_FULL_DATABASE’,
‘EXP_FULL_DATABASE’,
‘DBA’,
‘XDB’) order by 1;
spool off;
spool audit.log
prompt**======================
prompt** **Identify db audit**
prompt**======================
show parameter audit
spool off;
spool user_profile.log
prompt**======================
prompt** **Oracle user and profiles **
prompt**======================
set lines 300
col username for a20
col profile for a20
select username,profile from dba_users;
spool off;
spool supplied_packages.log
prompt**======================
prompt** **Oracle supplied packages**
prompt**======================
col object_name for a45
SELECT DISTINCT Owner, Object_Type, Object_Name,STATUS FROM DBA_Objects_AE
WHERE Owner IN (
‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’,
‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’,
‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’,
‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’,
‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’,
‘OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’,
‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’)
AND Object_Type IN (‘PACKAGE’)
ORDER BY Owner, Object_Type, Object_Name;
spool off;
spool sga.log
prompt**======================
prompt** **Check if allocated SGA is adequate **
prompt**======================
show parameter sga
select round((sum(decode(name, ‘free memory’, bytes, 0)) / sum(bytes))* 100,2) “SGA Free Memory” from v$sgastat;
spool off;
spool pga.log
prompt**======================
prompt** **Identify DBs performing heavy sorting, hash joining and configure PGA Separately**
prompt**======================
show parameter PGA
SELECT (1 – (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dictionary_Cache_Hit_Ratio FROM v$rowcache;
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 Library_Cache_Hit_Ratio FROM v$librarycache;
SELECT (1 – (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio FROM v$latch;
select (disk.value/mem.value) * 100 Disk_Sort_Ratio FROM v$sysstat disk,v$sysstat mem WHERE disk.name = ‘sorts (disk)’ AND mem.name = ‘sorts (memory)’;
spool off;
spool index.log
prompt**======================
prompt** **Check any unusable status of existing Indexes**
prompt**======================
select owner,index_name from dba_indexes WHERE STATUS = ‘UNUSABLE’
union all
select INDEX_OWNER, index_name from dba_ind_partitions WHERE STATUS = ‘UNUSABLE’
union all
select INDEX_OWNER, index_name from dba_ind_subpartitions WHERE STATUS = ‘UNUSABLE’;
spool off;
spool fragmention.log
prompt**======================
prompt** **Identify Tables with fragmentation**
prompt**======================
set lines 300 pages 3000
col TABLESPACE_NAME for a25
col TABLE_NAME for a25
break on report
compute sum of FRAGMENTED_SPACE on report;
select
owner,table_name,tablespace_
blocks,
num_rows,
avg_row_len,round(((blocks*8/
round((num_rows*avg_row_len/
round(((blocks*8/1024)-(num_
from dba_tables
where owner <> ‘SYS’
and round(((blocks*8/1024)-(num_
order by FRAGMENTED_SPACE desc;
select OWNER,TABLE_NAME,SEGMENT_NAME from dba_lobs where table_name in (select table_name from dba_tables where owner <> ‘SYS’ and round(((blocks*8/1024)-(num_
spool off;
spool row_chaining.log
prompt**======================
prompt** **Row chaining and recommend**
prompt**======================
set pages 9999;
column c1 heading “Owner” format a9;
column c2 heading “Table” format a12;
column c3 heading “PCTFREE” format 99;
column c4 heading “PCTUSED” format 99;
column c5 heading “avg row” format 99,999;
column c6 heading “Rows” format 999,999,999;
column c7 heading “Chains” format 999,999,999;
column c8 heading “Pct” format .99;
set heading off;
select ‘Tables with migrated/chained rows and no RAW columns.’ from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in (‘SYS’,’SYSTEM’)
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in (‘RAW’,’LONG RAW’,’CLOB’,’BLOB’,’NCLOB’)
)
and
chain_cnt > 0
order by chain_cnt desc
;
spool off;
spool tablespace_fragmentation.log
prompt**======================
prompt** **Tablespace Level Fragmentation**
prompt**======================
SELECT
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/
FROM
sys.dba_free_space
group by
tablespace_name
order by 2 desc, 1;
spool off;
spool stale_stas.log
prompt**======================
prompt** **Identify objects with stale statistics **
prompt**======================
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_
spool off;
spool invalid_object.log
prompt**======================
prompt** **Identify INVALID objects**
prompt**======================
set lines 300
col CREATED for a28
col LAST_DDL_TIME for a28
col object_name for a40
col object_type for a18
col owner for a16
col status for a19
select OWNER,OBJECT_NAME,OBJECT_TYPE,
spool off;
spool deadlock.log
prompt**======================
prompt** **Check the alertlog for Frequently Occurring Deadlocks.**
prompt**======================
set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,
spool off;
spool standby_sync.log
prompt**======================
prompt** **Check Primary and standby sync status .**
prompt**======================
set lines 300
col name for a10
col status for a10
select a.name,a.status,to_char(
spool off;
=================================================================
set pagesize 1100
set markup html on spool on
spool Dailycheck.html
set feedback off
set pages 50
set lines 1000
set pages 70
set heading on
PROMPT========================
prompt
PROMPT
PROMPT Daily Checkup Report
prompt ==============================
PROMPT REPORT DATE
select to_char(sysdate,’DD-MON-YYYY:
—
prompt DATABASE NAME
PROMPT ==============
select instance_name,host_name,
—
PROMPT DATABASE INFO
PROMPT =============
select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from v$database;
select status from v$instance;
prompt TABLESPACE INFO
prompt ===================
column tablespace_name for a30
column INITIAL_SIZE for 999999999.99
column tbfree for 99999.99
column Largest for 99999.99
column ratio for 9999.99
column FREE_SPACE for 99999.99
SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace – fs.freespace) used,
fs.freespace free,
100 * (fs.freespace / df.totalspace) pct_free
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name(+);
PROMPT SESSION INFORMATION
PROMPT ========================
select status,count(status) Count from v$session group by status;
PRoMPT HIT RATIOS
PROMPT =============
PROMPT SORT STATISTICS THIS SHOULD BE MORE THAN 95 %
SELECT (1-d.VALUE/m.value)*100 “SORT RATIO ” FROM V$SYSsTAT d,v$sysstat m
WHERE d.name =’sorts (disk)’ and m.name=’sorts (memory)’;
PROMPT DICTIONARY HIT RATIO
PROMPT Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora
PROMPT ======================
select (1-(sum(getmisses)/sum(gets)))
PROMPT THE OVER ALL HITRATIO OF THE LIBRARY CACHE
select sum(gethitratio)/count(*) *100 ” LIBRARY CACHE HIT RATIO ” from v$librarycache;
PROMPT BUFFER HIT RATIO
PROMPT =================
SELECT (1-PHY.VALUE/(cur.value+con.
where cur.name=’db block gets’
and con.name=’consistent gets’
and phy.name=’physical reads’;
PROMPT INDEX LOOK UP RATIO
PROMPT =====================
SELECT (1-l.VALUE/(l.value+s.value))*
WHERE s.name =’table scans (short tables)’ and l.name= ‘table scans (long tables)’;
—
PROMPT DATABASE SIZE
PROMPT ================
PROMPT TOTAL SIZE OF A DATABASE
select sum(bytes)/1024/1024/1024 “Physical Database Size” ,’ GB ‘
from dba_data_files ;
PROMPT ACTUAL SIZE OF DATABASE
select sum(bytes)/1024/1024/1024 “Actual Database Size”, ‘ GB ‘
from dba_segments ;
PROMPT INVALID OBJECTS
PROMPT =====================
Select count(*) “INVALID OBJECTS”,OWNER,object_type from all_objects where status=’INVALID’ group by owner,object_type order by 2;
—-
PROMPT REDO LOGS AND ARCHIVE STATUS
PROMPT ==============================
COLUMN member_name HEADING ‘Member_Name’;
COL MEMBER FOR A40;
SELECT vlf.member “member_name”,
vl.group# “Group”,
vl.status “Status”,
vl.archived “Archived”,
vl.bytes / 1024 “Size (K)”,
vl.sequence# “Sequence”
FROM v$logfile vlf,
v$log vl
WHERE vlf.group# = vl.group#
ORDER BY 1, vl.group#, vlf.member;
—
PROMPT Session I/O By User
PROMPT ==============================
select nvl(ses.USERNAME,’ORACLE PROC’) username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
—
prompt SEGMENTS HAVING LESS THAN 45 FREE EXTENTS
PROMPT ==============================
SELECT owner, segment_name, segment_type, extents, max_extents, next_extent,
initial_extent
FROM dba_segments
WHERE max_extents – extents < 45;
—
PROMPT LOCK INFORMATION
PROMPT ===================
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
‘MR’, ‘Media Recovery’,
‘RT’, ‘Redo Thread’,
‘UN’, ‘User Name’,
‘TX’, ‘Transaction’,
‘TM’, ‘DML’,
‘UL’, ‘PL/SQL User Lock’,
‘DX’, ‘Distributed Xaction’,
‘CF’, ‘Control File’,
‘IS’, ‘Instance State’,
‘FS’, ‘File Set’,
‘IR’, ‘Instance Recovery’,
‘ST’, ‘Disk Space Transaction’,
‘TS’, ‘Temp Segment’,
‘IV’, ‘Library Cache Invalidation’,
‘LS’, ‘Log Start or Switch’,
‘RW’, ‘Row Wait’,
‘SQ’, ‘Sequence Number’,
‘TE’, ‘Extend Table’,
‘TT’, ‘Temp Table’, type) lock_type,
decode(LMODE,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, lmode) lock_held,
decode(REQUEST,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, request) lock_requested,
decode(BLOCK,
0, ‘Not Blocking’,
1, ‘Blocking’,
2, ‘Global’, block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID;
—–
PROMPT HIGH RESOURCE CONSUMING SQL
PROMPT ===========================
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 – round(100 *
a.disk_reads/greatest(a.
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in (‘sys’,’system’)
order by 3 desc)
where rownum < 21;
PROMPT Scheduled Job Status
PROMPT ====================
set pagesize 1000
SELECT log_id, job_name, status,
EXTRACT(HOUR FROM RUN_DURATION)||’:’||EXTRACT(
to_char(log_date,’DD-MON-YYYY HH24:MI’) log_date FROM dba_scheduler_job_run_details
WHERE owner=’STAGING’ AND TO_CHAR(LOG_DATE,’DD-MON-YYY’) = TO_CHAR(SYSDATE, ‘DD-MON-YYY’);
spool off
exit