Oracle Database Administration

Ref: http://select-star-from.blogspot.com.au/2013/06/rman-troubleshooting.html RMAN Troubleshooting RMAN Troubleshooting Queries at Database Level SET PAGESIZE 20000 SET LINESIZE 1000 SET TRIMSPOOL ON SET PAUSE OFF SET SERVEROUTPUT ON SET FEEDBACK ON SET ECHO ON SET NUMFORMAT 999999999999999 COL TABLESPACE_NAME FORMAT A50 COL FILE_NAME FORMAT A50 COL NAME FORMAT A50 COL MEMBER FORMAT A50 col DFILE_CHKP_CHANGE format a40 col DFILE_HED_CHKP_CHANGE format a40 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; ARCHIVE LOG LIST; SELECT * FROM gv$instance; SELECT * FROM v$recover_file; SELECT * FROM v$recovery_log; select distinct status from v$backup; select distinct(status) from v$datafile; select distinct (to_char(checkpoint_change#)) from v$datafile; select distinct (to_char(checkpoint_change#)) from v$datafile_header; select * from v$backup; SELECT dbid, name, TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created, open_mode...

Database Troubleshooting Assessing database availability issues quickly • Identifying system performance issues with OS utilities • Querying data dictionary views to display resource-intensive SQL statements • Using Oracle performance tools to identify resource-consuming SQL statements • Identifying and resolving locking issues • Troubleshooting open-cursor issues • Investigating issues with the undo and temporary tablespaces 1.Checking Database Availability 2.Investigating Disk Fullness 3.Identifying System Bottlenecks When inspecting the end of the alert.log, look for errors that indicate these types of issues: Archiver pr • ocess hung, owing to inadequate disk space • File system out of space • Tablespace out of space • Running out of memory in the buffer cache or shared pool    Media error indicating that a data file is missing or damaged • Error indicating an issue with writing an archive redo log; Using vmstat The vmstat utility displays real-...

check linux kernel version number uname -r uname -a $ cat /proc/version cat /etc/*release uname -m uname -ar  check the database status select name,open_mode,database_name,created,log_mode,platform_name,from v$database; Check the object's invalid SELECT owner,COUNT(*) FROM dba_objects WHERE status = 'INVALID' GROUP BY owner select count(*) from dba_objects  WHERE status ='INVALID'; check opatch version opatch -v $ORACLE_HOME/OPatch/opatch version opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE' check oraInst.loc file pointing to your current $ORACLE_HOME or not. check free space on $ORACLE_HOME  export opatch  export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin export PATH=$PATH:$ORACLE_HOME/OPatch opatch lsinventory apply patch check logs vi $ORACLE_BASE/cfgtoollogs/catbundle/catbundle_PSU__APPLY_.log vi $ORACLE_BASE/cfgtoollogs/catbundle/catbundle_PSU__GENERATE_.log count invalid objects SELECT ...

http://www.oracle-wiki.net/startsql#toc7 SQL Library Table LAST_ANALYZED set ver off set linesize 60 col table_name format a15 col last_analyzed format a40 select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where lower(TABLE_NAME)='&tname'; Displays Last Analyzed Details for a given Schema. (All schema owners if 'ALL' specified). -- SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 SELECT t.owner,        t.table_name AS "Table Name",         t.num_rows AS "Rows",         t.avg_row_len AS "Avg Row Len",         Trunc((t.blocks * p.value)/1024) AS "Size KB",         to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed" FROM   dba_tables t,        v$parameter p WHERE t.owner = Decode(Upper('&&Table_Owne...

Detail report user session COL orauser HEA " Oracle User " FOR a17 TRUNC COL osuser HEA " O/S User " FOR a10 TRUNC COL ssid HEA " Sid " FOR a4 COL sserial HEA " Serial# " FOR a7 COL ospid HEA " O/S Pid " FOR a7 COL slogon HEA " Logon Time " FOR a14 COL sstat HEA " Status " FOR a6 COL auth HEA " Auth " FOR a4 COL conn HEA " Con " FOR a3 SELECT ' ' || NVL ( s . username , ' ???? ' ) orauser , ' ' || s . osuser osuser , LPAD ( s . sid , 4 ) ssid , LPAD ( s . serial #, 6 ) sserial, LPAD ( p . spid , 6 ) ospid , INITCAP ( LOWER ( TO_CHAR ( logon_time , ' MONDD HH24:MI:SS ' ) ) ) slogon , DECODE ( s . status , ' ACTIVE ' , ' Busy ' , ' INACTIVE ' , ' Idle ' , ' KILLED ' , ' Kill ' , ' ?? ...