Monitoring and Maintainence of Jobs

Display database jobs and schedulder jobs:-

set lines 200
Set pages 200
col WHAT for a40
select JOB,WHAT,BROKEN,FAILURES from dba_jobs;


col OWNER for a15
col STATE for a10
col FAL for 999
select OWNER,JOB_NAME,STATE,FAILURE_COUNT FAL from dba_scheduler_jobs;


Displays scheduler information about job programs.

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40

SELECT owner,
       program_name,
       program_type,
       program_action,
       number_of_arguments,
       enabled,
       comments
FROM   dba_scheduler_programs
ORDER BY owner, program_name;


Displays scheduler information about job schedules

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40

SELECT owner,
       schedule_name,
       start_date,
       repeat_interval,
       end_date,
       comments
FROM   dba_scheduler_schedules
ORDER BY owner, schedule_name;


Displays scheduler information for running jobs.

SET LINESIZE 200

COLUMN owner FORMAT A20

SELECT owner,
       job_name,
       running_instance,
       elapsed_time
FROM   dba_scheduler_running_jobs
ORDER BY owner, job_name;

select * from dba_jobs_running;

--To check stats job history for 10G

set linesize 300
col JNM for a30
col JST for a25
col RUN_DURATION for a15
col SLAVE_PID for 99999
col STATUS for a10
select TO_CHAR(ACTUAL_START_DATE, 'YYYY-MM-DD_HH24:MI:SS') jst, ERROR#, JOB_NAME jnm, STATUS,SESSION_ID,RUN_DURATION,SLAVE_PID
from DBA_SCHEDULER_JOB_RUN_DETAILS  where JOB_NAME = 'GATHER_STATS_JOB' order by ACTUAL_START_DATE;

--To check stats job history for 11G

set linesize 300
col CNM for a32
col JOB_DURATION for a15
col jst for a20
col JOB_STATUS for a13

select CLIENT_NAME, STATUS from dba_autotask_client where client_name = 'auto optimizer stats collection';

select TO_CHAR(JOB_START_TIME, 'YYYY-MM-DD_HH24:MI:SS') jst, JOB_ERROR, CLIENT_NAME cnm, JOB_STATUS,JOB_DURATION
from dba_autotask_job_history  where client_name = 'auto optimizer stats collection' order by job_start_time;

Full Database Refresh Using Export Dump

1. Input from Requester :

1.1 Source and Target database Name.

If not present, check with requester.

1.2        Export dump timestamp.

2. Pre-Check :

2.1        Check for the existence/In-Scope of the Source and Target Databases in PAC.

2.2        Check for existence of Export dump in Source Database.

Check for successful completion of Export from export log.

2.3        Check for Database size in Source Database.

“Select tablespace_name,sum(bytes)/1024/1024 MB from dba_segments group by tablespace_name;”

2.4        Check for Space availability in Target Database.

( Available Space for refresh =  Total allotted space in Tablespace(s) ) check this file

Note : If there is not enough free space for refresh then tablespace(s)

can be resized. Disk space can also be increased by Opening GRS request to SA.

                                   

3. Activities in Source Database/Server:

3.1        Scp export dump from Source server to target server

scp target_unix_user@target_hostname:<$BACKUP/export/hcl/

     

4. Activities  in Target Database/Server:

( Default activity location $backup/export/hcl )

4.1        Restart database in Restricted Mode.

SQL> alter system enable restricted session;

System altered.

4.2        Take Structure export of the database. Take same script for schema refresh

R:\DBA\paresh\DB-TASKS\Database-Refresh\precheck_details_main_for_schema_SCRIPT.sql

exp  /  file=${ORACLE_SID}_norows.dmp log=${ORACLE_SID}_norows.log full=y rows=n statistics=none

4.3        Take backup of passwords of all the users.

Set line 160

Set pages 1000

spool user_passwd.sql

select 'alter user ' ||u.username|| ' identified by values '''||u.password||''';'

from   sys.dba_users u;

spool off

4.4        Drop all Schema except SYS,SYSTEM,OUTLN,RMAN,DBSNMP,

  SCOTT,PERFSTAT,FOGLIGHT,WMSYS AND EXTERNAL USER.

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users where username not in 

('SYS','SYSTEM','OUTLN','RMAN','DBSNMP','SCOTT',

'PERFSTAT','FOGLIGHT','WMSYS') and username not like 'OPS%';

spool off

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users

where username not in ('SYS','SYSTEM','OUTLN','RMAN',

'DBSNMP','ACCT_ADMIN','ACC_RELEASE','AUDTASK','AUD_OWNER','CM_PROD',

'CTXSRV','DBA_MON','DMG','SCOTT','PERFSTAT',

'FOGLIGHT','WMSYS','ANONYMOUS','CTXSYS','DB_TOOLS','DIP',

'ESMDBA','EXFSYS','HIDS_AUDITOR','MDDATA','MDSYS',

'MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS',

'OUTLN','SI_INFORMTN_SCHEMA','SYSMAN','TSMSYS','WKSYS',

'XDB','HEALTH_CHECKS','OLAP_USER','ORACLE','PUBLIC','QUEST_CENTRAL',

'TRACESVR','WEBSYS''WKUSER') and

username not like 'OPS%';

spool off

4.5        Make Sure sufficient space available for Full refresh ( step 2.3 and 2.4)

4.6        Start import

mknod imp_pipe  p

uncompress < > imp_pipe &

imp / file=imp_pipe buffer=10000000 full=y  log=imp_full_SID_mmddyy.log  feedback=10000

4.7        Moniter import ( tail –f  nohup.out )

4.8        Check import log for errors (if any) and resolve.

4.9        Compile invalid objects by running utlrp.sql as sysdba.

@?/rdbms/admin/utlrp.sql

4.10      Drop all database link.

set serveroutput on size 100000

spool drop_link.sql

 declare v_paswd  varchar2(30) ;

 begin

 for i in ( select distinct owner  from dba_db_links where owner != 'PUBLIC' ) loop

 select password into v_paswd from dba_users where username = i.owner;

 dbms_output.put_line('connect / ' ) ;

 dbms_output.put_line ( ' alter user '||i.owner||' identified by a; ') ;

 dbms_output.put_line('connect '||i.owner||'/a' ) ;

 dbms_output.put_line(' alter user '||i.owner||' identified by values '''||v_paswd||''' ;' );

    for ii in ( select db_link from dba_Db_links where owner=i.owner ) loop

     dbms_output.put_line(' drop database link '||ii.db_link||' ; ');

   end loop;

 end loop;

 end;

/

spool off

@drop_link.sql

4.11      Apply password from backup ( step 4.3).

4.12      Change database to normal mode.

                        Alter system disable restricted session;

OEM Unreachable

a.     Log on to the server where the database in concern is running.

b.    Check if the OEM agent is running by typing the following command:

$ ps –ef|grep emagent

c.     If step b shows that the emagent is running, do the following:

                                          i.    Change dir to /bin, which could be found out from previous step, the stand dir for is /data/oracle/product/agent10g . There are some non-standard installation that was installed before the standard is a available.

                                         ii.    Run following command to find out file system usage:

$ df –k

                                        iii.    If the percentage in use is more than or equal to 98%, add space

d.    If step b shows that there is no emagent running, do the following

                                        iv.    Find out . The standard is

/data/oracle/product/agent10g

If the OEM client is not installed under standard , use following os command to find out the agent10g under /data/oracle/product:

$ find  . –name agent10g

Change dir to /bin and run following command to start up agent manually:

                                        $ ./emctl start agent

Flashback Area Full

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE        PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------        ------------------------------------ ------------------------------------------------  ------------------------------
CONTROLFILE                   0                                                      0                                                    0
ONLINELOG                       0                                                      0                                                    0
ARCHIVELOG                     0                                                      0                                                    0
BACKUPPIECE                   0                                                      0                                                    0
IMAGECOPY                       0                                                      0                                                    0
FLASHBACKLOG             99.79                                                 0                                                 151
NOTE: The above example shows that the flashback area ONLY contains flashback logs and is currently 99.79% full.


SQL> select * from v$restore_point;


NAME                                 guarantee_flashback_database
=============================================================
BEFORE                                           YES

NOTE: If there are NO restore points created the above query will return NO rows.  The column guarantee_flashback_database shows whether it is a guaranteed restore point or just a normal restore point.  Both should be highlighted to the regional DBA team on transfer of the issue.

SQL> col FLASHBACK_SIZE format 9999999999999
SQL> col ESTIMATE_FLASHBACK_SIZE format 99999999999
SQL> select FLASHBACK_SIZE,RETENTION_TARGET, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

FLASHBACK_SIZE RETENTION_TARGET ESTIMATED_FLASHBACK_SIZE
-------------- ---------------- ------------------------
   85717303296             1440             346175422464


NOTE: Current DB standards are for a 24 hour flashback retention policy. The above shows that the flashback size is 85Gb but the required flashback area size is actually 85Gb+346Gb, so the flashback area / allocation is too small and flashback retention is not being met.  This requires further investigation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter system set db_flashback_retention_target = 6;
alter system set db_recovery_file_dest_size =20g;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For example if the db_flashback_retention_target is set to 60 and db_recovery_file_dest_size is set to 20 Gb and the Flashback area is almost full, reduce the db_flashback_retention_target to 5 minutes and db_recovery_file_dest_size to 16G to relieve the database. Use the syntax as below:

Max sessions/processes limit Reaching/Reached

Confirm max usage of sessions/process:-

SELECT * FROM V$RESOURCE_LIMIT where RESOURCE_NAME='processes';

select count(*) from v$process;

select count(*) from v$session;

################################################################################
################################################################################

List details of session/process per user and status:-

select username,count(*) from v$session group by username;

select username,status,count(*) from v$session group by username,status;

################################################################################
################################################################################

List Details about current database sessions sqls:-


SELECT  s.username,
         s.sid||':'||s.serial# sid,
         p.spid,
         s.logon_time,
        DECODE(sa.sql_text,NULL,'NOT RUNNING ACTIVE SQL',sa.sql_text) sql_text,
         s.program PROGRAM1,
         p.program PROGRAM2,
         s.terminal||':'||s.machine term_mach,
         s.osuser
FROM    v$session s,
        v$process p,
   v$sqlarea       sa
WHERE   s.paddr(+)=p.addr
AND        s.sql_address = sa.address(+)
AND        s.sql_hash_value = sa.hash_value(+)
ORDER BY 2;

#################################################################################
#################################################################################

Database Availability Alert Checks

1.1.1.   Instance/Database Down or Hanging or bouncing the database

Alert Format

·         A new connection to instance failed: ORA-01034: ORACLE not available ORA-27101: shared memory realm

·         Database <SID> on is hanging!!!

Action

1. If the host is alive, login to host otherwise raise a ticket with UNIX team to check server availability and escalate to Global Oracle DBA Team.
2. Check if cold backup is going on (ps –eaf | grep dbbackup | grep <SID>). If backup is running, check the backup.log for verification of cold backup.   In this case, keep it pending till the cold backup is completed and escalate to Global Oracle DBA Team to check why alert was raised during cold backup. Ideally it should not raise alert as dbbackup put database in maintenance mode before starting cold backup.

Check alert.log to locate any abnormal lines which caused database down or hanging. Confirm all SID associated file systems are mounted. If any file system is missing, raise a ticket with Unix Team immediately 

1.1.2.  Listener Down

Alert Format

• A new connection to instance failed: ORA-12541: TNS: no listener.

Action

1. Extract listener name and listener password, if any, from $TNS_ADMIN/admin/listener.ora if $TNS_ADMIN exists. Otherwise, extract them from $ORACLE_HOME/network/admin/listener.ora.
2. Check listener status, provide listener password if required.
3. If listener is down, start listener and inspect listener log file to find out any errors.

If listener is up, reload listener. If reload hangs check log/trace files.

Procedure to add space to tablespace

1. Check current freespace and list all the datafiles which belong to tablespace:-

set lines 120
set pages 200
col file_name for a70

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&1';

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='&1';

2. Before adding new datafile ensure that there is no datafile already exists with same name in the database.

Following query may be used to identify the duplicate name.
SELECT name AS full_name,
       SUBSTR( name, INSTR(name,'/',-1)+1 ) AS  base_name
  FROM v$datafile
 WHERE SUBSTR( name, INSTR(name,'/',-1)+1  ) =  :BaseFileName

3. ":BaseFileName" should be the bound or replaced with the  base filename to be added ( not pathed ) .
e.g. LNSRDBP1_SYSAUX_01.dbf – in exactly the case to be user *( upper/lower as appropriate)


4. Add datafile to tablespace with below sample command:-

Alter tablespace add datafile '' size ;

History of Past of Sessions

select T2.username,count(T2.username)
FROM gv$active_session_history t
,dba_users t2
WHERE
t.SAMPLE_TIME between to_timestamp('02/22/2014 20:30:00','MM/DD/YYYY HH24:MI:SS') and
to_timestamp('02/22/2014 20:35:00','MM/DD/YYYY HH24:MI:SS')
and t.user_id = t2.user_id
group by T2.username
order by count(T2.username)
;

Undo Tablespace Management

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - pre 10G

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. This ticket will be raised as a result of an UNDO tablespace apparently having space pressure. This may be a false alarm. 

2. Run the query below :

a. select  ssolderrcnt "Snapshot Too Old Errors",
nospaceErrCnt "Out of Space Errors"
from  v$undostat
where ( ssolderrcnt != 0 or  nospaceErrCnt != 0 );

3. If no rows are returned, no actions are required, check source of alert.

4. If this returns one or more rows, we may need to Tune the undo retention and/or increase the size of the UNDO tablespace.

5. Your should set the UNDO_RETENTION to    NO LESS THAN     than  the result of the query below:

a. select round(( max( maxQueryLen )* 1.2 ),0) 
as "Max Query Length in Seconds"

from v$undostat;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - 10g and later

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col status format a15
col description format a40 word_wrap
set serverout on size 999999 lines 150 verify off pages 50 echo off trimspool on
break on report
compute Sum LABEL SUM of undosize_MB on report
compute Sum LABEL SUM of block_count on report

col undo_alloc new_value undo_alloc noprint
col undo_pct format 9999
spool undo_info.log
SELECT SUM(bytes)/1024/1024 undo_alloc
FROM DBA_data_files
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'UNDO');

ttitle left '*********** REPORT 1 - Undo Block Status and Free Space Analysis ********************' skip 2

col Description format a40 word_wrap
col undo_pct format a5 head "UNDO|PCTGE"
SELECT status,
DECODE(status,'ACTIVE','UNDO BEING USED','EXPIRED','UNDO AVAILABLE FOR USE','UNEXPIRED','UNDO BEING RETAINED TO SUPPORT UNDO RETENTION. CAN BECOME EXPIRED IF SPACE BECOMES TIGHT') Description,
count(*) block_count,
round(sum(bytes)/1024/1024) undosize_MB,
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%' undo_pct
FROM dba_undo_extents
GROUP BY status
UNION
SELECT 'FREE SPACE',
'UNDO FREE SPACE AVAILABLE FOR USE',
COUNT(*),
ROUND(SUM(bytes)/1024/1024),
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%'
FROM dba_free_space
WHERE tablespace_name =
(SELECT value
FROM v$parameter
WHERE name = 'undo_tablespace')
GROUP BY 1
/

ttitle off
ttitle left '*********** REPORT 2 - Undo Health Check ********************' skip 2
DECLARE
tablespaceName varchar2(30);
tablespaceSize number;
autoExtend boolean;
autoExtendtf char(5);
undoRetention number;
retentionGuarantee boolean;
retentionGuaranteetf char(5);
autotuneEnabled boolean;
autotuneEnabledtf char(5);
longestQuery number;
longestQueryFormatted varchar2(30);
requiredRetention number;
requiredRetentionFormatted varchar2(20);
bestPossibleRetention number;
bestPossibleRetentionFormatted varchar2(20);
requireUndoSize number;
--
problem varchar2(100);
recommendation varchar2(100);
rationale varchar2(100);
retention number;
utbsize number;
nbr number;
undoAdvisor varchar2(100);
instanceNumber number;
ret boolean;
rettf char(5);
undoRetentionFormatted varchar2(50);
Recommended_undo_size number;
--
--
BEGIN

ret := sys.dbms_undo_adv.undo_info (tableSpaceName, tableSpaceSize, autoExtend, undoRetention, retentionGuarantee);

if ret
then rettf := 'TRUE';
else rettf := 'FALSE';
end if;

if autoextend
then autoextendtf := 'TRUE';
else autoextendtf := 'FALSE';
end if;

if retentionguarantee
then retentionguaranteetf := 'TRUE';
else retentionguaranteetf := 'FALSE';
end if;

SELECT to_char(trunc( max(undoRetention)/(60*60))||' hrs ')
|| trunc(to_char( ( max(undoRetention) - (3600 * trunc(max(undoRetention)/3600) ) )/60)) ||' mins '
INTO undoRetentionFormatted
FROM Dual;

longestquery := dbms_undo_adv.longest_query(sysdate-1,sysdate);
SELECT to_char(trunc( max(longestQuery)/(60*60))||' hrs ')
|| trunc(to_char( ( max(longestQuery) - (3600 * trunc(max(longestQuery)/3600) ) )/60)) ||' mins '
INTO longestQueryFormatted
FROM Dual;

-- dbms_output.put_line(' ');
-- dbms_output.put_line('--------------------------------------------------');
-- dbms_output.put_line('* UNDO Health *');
-- dbms_output.put_line
dbms_output.put_line( '*********** REPORT 2 - Undo Health Check ********************');
dbms_output.put_line(' ');
dbms_output.put_line(' ');
nbr := dbms_undo_adv.undo_health (problem, recommendation, rationale, retention, utbsize);

SELECT DECODE(utbsize,0,tableSpaceSize,utbsize)
INTO Recommended_undo_size
FROM dual;

dbms_output.put_line (RPAD('Problem',35,CHR(0))||' : '||problem);
dbms_output.put_line (RPAD('Recommendation',35,CHR(0))||' : '||recommendation);
dbms_output.put_line (RPAD('Rationale',35,CHR(0))||' : '||rationale);
dbms_output.put_line (RPAD('undo_retention (secs)',35,CHR(0))||' : '||undoRetention);
dbms_output.put_line (RPAD('undo_retention (hrs/mins)',35,CHR(0))||' : '||undoRetentionFormatted);
dbms_output.put_line (RPAD('Guaranteed Retention',35,CHR(0))||' : '||retentionGuaranteetf);
dbms_output.put_line (RPAD('Longest Run Query (secs)',35,CHR(0))||' : '||longestQuery );
dbms_output.put_line (RPAD('Longest Run Query (hrs/mins)',35,CHR(0))||' : '||longestQueryFormatted );
dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||Recommended_undo_size);
-- dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||utbsize);
dbms_output.put_line (RPAD('Current Undo T/S Size (MB)',35,CHR(0))||' : '||tableSpaceSize);
END;
/

col SSOLDERRCNT format 999999999 HEAD "SNAPSHOT|TOO OLD|ERROR|COUNT"
col NOSPACEERRCNT format 9999999 HEAD "NOSPACE|ERROR|COUNT"
ttitle off
ttitle left '*********** REPORT 3 - Current Undo Stats ********************' skip 2

SELECT BEGIN_TIME,
END_TIME,
UNDOBLKS,
MAXQUERYLEN,
MAXQUERYID,
SSOLDERRCNT,
NOSPACEERRCNT,
TUNED_UNDORETENTION
FROM v$undostat
WHERE BEGIN_TIME> sysdate-.090
ORDER BY 1;

ttitle off
ttitle left '*********** REPORT 4 - Undo Datafiles ********************' skip 2
col filename format a65
col TSPACENAME format a12
col CURRENT_SIZE_MB format 999,999 head 'CURRENT|SIZE(MB)'
col AUTOEXTEND_UP_TO_SIZE format 9,999,999 head 'AUTOEXTEND UP|TO SIZE(MB) '

SELECT d.file_name "FILENAME",
d.bytes/1024/1024 CURRENT_SIZE_MB,
d.maxbytes/1024/1024 AUTOEXTEND_UP_TO_SIZE,
t.tablespace_name "TSPACENAME",
CASE
WHEN d.autoextensible='YES' AND d.bytes>=d.maxbytes THEN 'WORKROUND IN PLACE'
WHEN d.autoextensible='YES' AND d.bytes WHEN d.autoextensible='NO' THEN 'NO AUTOEXTEND'
END AUTOEXTEND
FROM dba_data_files d,
dba_tablespaces t,
v$parameter p
WHERE d.tablespace_name = t.tablespace_name
AND d.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
/
ttitle off

ttitle left '*************** REPORT 5 - Current Undo Activity **************' skip 2
col dummy noprint
SELECT * from dual;

col userdet heading "OSUSER : |USERNAME" format A15
col procid heading "SID:SERIAL - |SPID" format A15
col terminal heading "TTY#" format A15
col program heading "PROGRAM NAME" format A20
col status heading "STATUS" format A10
col name heading "UNDO|SEGMENT" format a15
col sql_text heading "CURRENT SQL STATEMENT" format a100 word_wrap
col used_ublk heading "USED|UNDO" format a10
col start_date heading "START DATE"
SELECT unique
RPAD(vs.osuser,13,' ')||': '|| vs.username userdet,
RPAD(vs.sid||':'|| vs.serial#,13,' ') ||'- '|| vp.spid procid,
-- vs.terminal ,
vs.program ,
vs.status ,
vr.name ,
vt.used_ublk * TO_NUMBER(ts.block_size)/1024||'K' used_ublk,
vt.start_date ,
vsql.sql_text
FROM v$rollname vr,
v$transaction vt,
v$sql vsql,
v$process vp,
v$session vs,
(
SELECT dt.block_size
FROM v$parameter vp,
dba_tablespaces dt
WHERE vp.value = dt.tablespace_name
AND vp.name = 'undo_tablespace'
) ts
WHERE vs.paddr = vp.addr
-- AND NVL(vs.sql_id,vs.prev_sql_id) = vsql.sql_id
AND vs.sql_id = vsql.sql_id(+)
AND vs.taddr = vt.addr
AND vt.xidusn = vr.usn;
ttitle off

spool off

Temporary Tablespace Management

-----------------Total/Used/Free space by TEMP TABLESPACE----------------------

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
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.mb_total;

---------------- TEMP SEGMENTS UTLIZATION///Sort Space Usage by Session in case of TEMP TABLESPACE--------

col SID_SERIAL for a20
col PROGRAM for a20
set lin 200
col OSUSER for a10
col MODULE for a10
col MODULE for a20
col TABLESPACE for a10
col USERNAME for a10
col SID_SERIAL for a10
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

----------------- SOP TEMP UTILIZATION -------------------

set lines 300
set pagesize 20000
col USERNAME format a12 word_wrap
col OSUSER format a10 word_wrap
col TABLESPACE format a10 word_wrap
col sql_text format a50 word_wrap
break on tablespace on report
compute Sum LABEL SUM of temp_size_kb on tablespace
SELECT ses.username, ses.sid, ses.serial#, ses.osuser, tu.tablespace, (tu.blocks*temp_tspace.block_size)/1024 temp_size_kb, sql.sql_text
FROM v$session ses, v$tempseg_usage tu, v$sqlarea sql,
(
SELECT tablespace_name,
block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY'
) temp_tspace
WHERE ses.saddr = tu.session_addr
AND sql.address= ses.sql_address
AND sql.hash_value = ses.sql_hash_value
AND temp_tspace.tablespace_name = tu.tablespace
ORDER BY tu.tablespace, tu.blocks
/

------------ TEMP FILE-------------

set lin 200
col file_name for a60
select file_name , file_id, tablespace_name , status from dba_temp_files;

-----------TEMP TABLESPACE UTILIZATION--------
SELECT tablespace_name, SUM(bytes_used/1024/1024) "USED MB", SUM(bytes_free/1024/1024) "FREE MB"
FROM V$temp_space_header
GROUP BY tablespace_name;

alter database tempfile '/data/oracle/LNDBIBP1/temp01/temp/LNDBIBP1_TEMP_01.dbf' resize 6900M;

Connected Sessions:Details

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
column COMM_KILL for a60
column INS format 99
column MACHINE for a8
column USERNAME for a8
column STATUS for a8
column SID heading 'SID' for a6
column SERIAL# heading 'SERIAL#' for a7
column SQL_ID for a15
column OS_PID for a6
column RUN_SECS for a7
column SQL_TEXT for a16
column PLAN_HASH_VALUE for a10 for 9999999999999
column LOGON_TIME for a9
column OSUSER for a6
column PROGRAM for a10
column MODULE for a10
column TERMINAL for a10
column MACHINE for a10
column EVENT for a6
col PGA_MB for a8 for 99999999
col CPU_SECS for a5 for 99999
col UNDO_PCT_READ for a4 for 99999
col TEMP_MB for a8 for 99999999
column REDO_MB for a6 for 999999
column BLOCK_CHANGES for a8 for 99999999
set lines 250
set pages 100
--first line sql text for current sessions in RAC
--SELECT 'ALTER SYSTEM KILL SESSION '''||t2.SID || ',' || t2.SERIAL# || ',@ '||t2.INST_ID || ''' immediate;' COMM_KILL
--'ALTER SYSTEM KILL SESSION '''||t1.SID || ',' || t1.SERIAL# || ''' immediate;' COMM_KILL
--,
SELECT * FROM ( SELECT
SUBSTR(T3.SQL_TEXT,1,100) SQL_TEXT,T1.INST_ID INS,T1.MACHINE,T1.USERNAME
,T1.STATUS,TO_CHAR(T1.SID) SID,TO_CHAR(T1.SERIAL#) SERIAL#,T1.SQL_ID--,T3.PLAN_HASH_VALUE
,(SELECT TO_CHAR(P.SPID) OS_PID FROM GV$PROCESS P WHERE T1.PADDR=P.ADDR AND P.INST_ID = T1.INST_ID) OS_PID
--,PROCESS
,TO_CHAR(T1.LOGON_TIME,'YYYYMMDD HH24:MI:SS') LOGON_TIME
,TO_CHAR(DECODE(T1.STATUS,'ACTIVE',T1.LAST_CALL_ET,0)) RUN_SECS
,(SELECT A.VALUE/(1024*1024) PGA_MB FROM GV$SESSTAT A,GV$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
AND UPPER(B.NAME) LIKE 'SESSION PGA MEMORY' AND A.INST_ID = B.INST_ID AND B.INST_ID = T1.INST_ID AND A.SID=T1.SID ) PGA_MB
,(SELECT SUM(ROUND (value/100)) FROM gv$sesstat ss, gv$statname st WHERE name LIKE '%CPU used by this session%'
AND ss.statistic# = st.statistic# AND ss.INST_ID = st.INST_ID AND ss.sid = T1.SID and ss.INST_ID = T1.INST_ID) CPU_SECS
,(SELECT CEIL(DECODE(CONSISTENT_CHANGES,0,0,CONSISTENT_CHANGES*100/CONSISTENT_GETS)) FROM GV$SESS_IO SIO WHERE SIO.INST_ID = T1.INST_ID AND SIO.SID=T1.SID) UNDO_PC_RD
,NVL((select TEMP_MB FROM (SELECT SID,INST_ID, SUM(MB_USED) TEMP_MB FROM (
SELECT S.sid, SUM (su.blocks) * TBS.block_size/1024/1024 mb_used,TBS.tablespace_name,S.INST_ID
FROM gv$sort_usage su, gv$session S, dba_tablespaces TBS
WHERE su.session_addr = S.saddr AND su.tablespace = TBS.tablespace_name and su.INST_ID = S.INST_ID
GROUP BY S.sid,TBS.block_size,TBS.tablespace_name,S.INST_ID ) GROUP BY SID,INST_ID ) A WHERE A.SID = T1.SID AND A.INST_ID = T1.INST_ID),0) TEMP_MB
,(SELECT CEIL(VALUE/1024/1024) FROM GV$STATNAME N, GV$SESSTAT S
WHERE UPPER(N.NAME) = 'REDO SIZE' AND S.STATISTIC# = N.STATISTIC# AND S.INST_ID = T1.INST_ID AND S.SID = T1.SID) REDO_MB
--,(SELECT (CEIL(I.BLOCK_CHANGES)) FROM GV$SESS_IO I WHERE I.INST_ID = T1.INST_ID AND I.SID = T1.SID) BLOCK_CHANGES
,T1.OSUSER,T1.EVENT--,SQL_CHILD_NUMBER
--,T1.PROGRAM--,T1.MODULE--,T1.TERMINAL
FROM
GV$SESSION T1
,GV$SQL T3
WHERE
T1.SID != SYS_CONTEXT('USERENV','SID') AND T1.INST_ID = T3.INST_ID(+) AND T1.USERNAME IS NOT NULL AND T1.SQL_ID = T3.SQL_ID(+)
AND T1.SQL_CHILD_NUMBER = T3.CHILD_NUMBER(+)
--AND T1.SQL_ID = any( '5tztp1g1p2s6b') --AND T1.PROGRAM = 'JDBC THIN CLIENT'--AND T1.MACHINE = 'SUNAMERICA-LA\WHPWNORA2'
--AND T1.LAST_CALL_ET > 0
--AND T1.STATUS = 'ACTIVE'
--AND T1.SID = ANY(678)
--and T1.USERNAME is not null
--AND UPPER(T1.USERNAME) = ('DEPOTHIST01')
--AND UPPER(MACHINE) LIKE '%LONINDAPNP18%'--AND UPPER(T1.OSUSER) LIKE '%DIBYAJOTI%'
--AND T1.PROGRAM LIKE '%SQLPLUS%'--AND UPPER(T3.SQL_TEXT) LIKE '%CREATE%'--AND T3.SQL_TEXT IS NOT NULL
)

Tablespace: Free space management

set pagesize 100

set linesize 200
break on report

compute sum of free_mb on report
compute sum of act_size on report
compute sum of used_mb on report
col %used format 999.99
col %Free format 999.99
col act_size format 99,99,999.99
col free_mb format 99,99,999.99
col used_mb format 99,99,999.99
col largest_extent_MB format 99,99,999.99
col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,
free_mb*100/act_size "%FREE",
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,
max(bytes)/1024/1024 largest_extent_MB
FROM DBA_FREE_SPACE group by tablespace_name) a,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM
DBA_DATA_FILES group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
and a.tablespace_name in ('IOB_DATA_2013')
order by 6
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select file_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name='&tablespace_name' group by file_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set lines 120
col FILE_NAME for a70
select file_name,bytes/1024/1024,autoextensible,MAXBYTES/1024/1024 from dba_data_files
where tablespace_name in ('IOB_DATA_2013');

alter tablespace TDM_INDX01 add datafile '/data/oracle/LNSMISP1/data37/LNSMISP1_TDM_INDX01_50.dbf' size 6G autoextend off;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter database datafile '/export/data/oracle/NYARADP1/data01/NYARADP1_sysaux_01.dbf' resize 9000m;

ASH and AWR Performance Tuning Scripts

Listed below are some SQL queries that are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.
I would like to add that these queries that have been used and customised by me based on SQL scripts.

Top Recent Wait Events

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

Top Wait Events Since Instance Startup

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

Top CPU consuming queries since past one day

select * from (
select 
 SQL_ID, 
 sum(CPU_TIME_DELTA), 
 sum(DISK_READS_DELTA),
 count(*)
from 
 DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
 group by 
 SQL_ID
order by 
 sum(CPU_TIME_DELTA) desc)
where rownum

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

List Objects being accessed by a particular SID

set pagesize 300
select sid,serial#,username,status from v$session
WHERE USERNAME IS NOT NULL
order by STATUS DESC;
col sid format 999
col owner format a10
col object format a20
col type format a10
set linesize 300
/* ENTER THE SID TO CHECK */

select sid,owner,object,type
from v$access where sid = &sid
order by owner,type
;

List all recent DDL modifications

set pagesize 200 colsep ' '
col Owner format a20
col Object_Name format a30
col Last_DDL format a20
break on Owner
select Owner, Object_type
Object_Name,
Timestamp Last_DDL
from DBA_OBJECTS
where  SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1
order by Owner, Object_Name
/

Size of db

select

(select sum(bytes)/1024/1024/1024 data_size from dba_data_files)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select

(select sum(bytes)/1024/1024/1024 data_size from dba_segments)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pagesize 100

set linesize 200

break on report

compute sum of free_mb on report

compute sum of act_size on report

compute sum of used_mb on report

col %used format 999.99

col %Free format 999.99

col act_size format 99,99,999.99

col free_mb format 99,99,999.99

col used_mb format 99,99,999.99

col file_name for a60

col largest_extent_MB format 99,99,999.99

col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,

free_mb*100/act_size "%FREE",

act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB

from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,

max(bytes)/1024/1024 largest_extent_MB

FROM DBA_FREE_SPACE group by tablespace_name) a,

(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM

DBA_DATA_FILES group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name

order by 1

/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Backup Report from Catalog

I] Query the RMAN catalog to check backup status:

This script will query the RMAN catalog and report on the backup status of every database in the catalog.
 
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));

II]  Query the RMAN catalog to list backup completion status

Note – run this query connected as the owner of the RMAN catalog

set lines 80

set pages 250

ttitle "Daily Backup........"

select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,

NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP

from (

select a.name DB,dbid,

decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,

decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch

from rc_database a,bs b

where a.db_key=b.db_key

and b.bck_type is not null

and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE

is not null or AUTOBACKUP_SEQUENCE is not null)

and b.bs_key not in(select bs_key from rc_backup_spfile)

group by a.name,dbid,b.bck_type

) group by db,dbid

ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))

/

Backup Report from database side

Script to report on all backups - full, incremental and archivelog backups:

set linesize 2000

col STATUS format a9

col hrs format 999.99

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, STATUS from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Script to report on all backups - full, incrementals, not archivelog backups:

set linesize 2000

col STATUS format a9

col hrs format 999.99

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, STATUS from V$RMAN_BACKUP_JOB_DETAILS where input_type='DB INCR' order by session_key;

I] Script to report on all backupsets:

select decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,

incremental_level,

round(sum(original_input_bytes)/1024/1024,2) "MB in",

round(sum(output_bytes)/1024/1024,2) "MB out",

status,

min(start_time),

max(completion_time),

round((sum(output_bytes)/1024/1024)/((max(completion_time)-min(start_time))*86400), 1) "MB/s"

from v$backup_set_details

group by backup_type, incremental_level, status, session_key, session_recid, session_stamp

order by 6

/

II] Script to report on all backupsets:

 select ctime "Date"

 , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type

 , bsize "Size MB"

 from (select trunc(bp.completion_time) ctime

 , backup_type

 , round(sum(bp.bytes/1024/1024),2) bsize

 from v$backup_set bs, v$backup_piece bp

 where bs.set_stamp = bp.set_stamp

 and bs.set_count  = bp.set_count

 and bp.status = 'A'

 group by trunc(bp.completion_time), backup_type)

 order by 1, 2;

Oracle Database Silent Install On Linux: db_install.rsp

####################################################################
## Copyright(c) Oracle Corporation 1998,2008. All rights reserved.##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## can help to populate the variables with the appropriate        ##
## values.                                                        ##
##                                                                ##
## IMPORTANT NOTE: This file contains plain text passwords and    ##
## should be secured to have read permission only by oracle user  ##
## or db administrator who owns this installation.                ##
##                                                                ##
####################################################################
#------------------------------------------------------------------------------
# Do not change the following system generated value.
#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
#------------------------------------------------------------------------------
# Specify the installation option.
# It can be one of the following:
# 1. INSTALL_DB_SWONLY
# 2. INSTALL_DB_AND_CONFIG
# 3. UPGRADE_DB
#-------------------------------------------------------------------------------
oracle.install.option=INSTALL_DB_SWONLY
#-------------------------------------------------------------------------------
# Specify the hostname of the system as set during the install. It can be used
# to force the installation to use an alternative hostname rather than using the
# first hostname found on the system. (e.g., for systems with multiple hostnames
# and network interfaces)
#-------------------------------------------------------------------------------
ORACLE_HOSTNAME=HOST01.shilpa.com
#-------------------------------------------------------------------------------
# Specify the Unix group to be set for the inventory directory.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall
#-------------------------------------------------------------------------------
# Specify the location which holds the inventory files.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/u01/app/oraInventory
#-------------------------------------------------------------------------------
# Specify the languages in which the components will be installed.
#
# en   : English                  ja   : Japanese
# fr   : French                   ko   : Korean
# ar   : Arabic                   es   : Latin American Spanish
# bn   : Bengali                  lv   : Latvian
# pt_BR: Brazilian Portuguese     lt   : Lithuanian
# bg   : Bulgarian                ms   : Malay
# fr_CA: Canadian French          es_MX: Mexican Spanish
# ca   : Catalan                  no   : Norwegian
# hr   : Croatian                 pl   : Polish
# cs   : Czech                    pt   : Portuguese
# da   : Danish                   ro   : Romanian
# nl   : Dutch                    ru   : Russian
# ar_EG: Egyptian                 zh_CN: Simplified Chinese
# en_GB: English (Great Britain)  sk   : Slovak
# et   : Estonian                 sl   : Slovenian
# fi   : Finnish                  es_ES: Spanish
# de   : German                   sv   : Swedish
# el   : Greek                    th   : Thai
# iw   : Hebrew                   zh_TW: Traditional Chinese
# hu   : Hungarian                tr   : Turkish
# is   : Icelandic                uk   : Ukrainian
# in   : Indonesian               vi   : Vietnamese
# it   : Italian
#
# Example : SELECTED_LANGUAGES=en,fr,ja
#------------------------------------------------------------------------------
SELECTED_LANGUAGES=en
#------------------------------------------------------------------------------
# Specify the complete path of the Oracle Home.
#------------------------------------------------------------------------------
ORACLE_HOME=/u01/app/oracle/product/11.2.0
#------------------------------------------------------------------------------
# Specify the complete path of the Oracle Base.
#------------------------------------------------------------------------------
ORACLE_BASE=/u01/app/oracle
#------------------------------------------------------------------------------
# Specify the installation edition of the component.
#
# The value should contain only one of these choices.
# EE     : Enterprise Edition
# SE     : Standard Edition
# SEONE  : Standard Edition One
# PE     : Personal Edition (WINDOWS ONLY)
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
#------------------------------------------------------------------------------
# This variable is used to enable or disable custom install.
#
# true  : Components mentioned as part of 'customComponents' property
#         are considered for install.
# false : Value for 'customComponents' is not considered.
#------------------------------------------------------------------------------
oracle.install.db.isCustomInstall=false
#------------------------------------------------------------------------------
# This variable is considered only if 'IsCustomInstall' is set to true.
#
# Description: List of Enterprise Edition Options you would like to install.
#
#              The following choices are available. You may specify any
#              combination of these choices.  The components you choose should
#              be specified in the form "internal-component-name:version"
#              Below is a list of components you may specify to install.
#
#              oracle.rdbms.partitioning:11.2.0.1.0 - Oracle Partitioning
#              oracle.rdbms.dm:11.2.0.1.0 - Oracle Data Mining
#              oracle.rdbms.dv:11.2.0.1.0 - Oracle Database Vault
#              oracle.rdbms.lbac:11.2.0.1.0 - Oracle Label Security
#              oracle.rdbms.rat:11.2.0.1.0 - Oracle Real Application Testing
#              oracle.oraolap:11.2.0.1.0 - Oracle OLAP
#------------------------------------------------------------------------------
oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0
###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
#------------------------------------------------------------------------------
oracle.install.db.InstallEdition=EE
#------------------------------------------------------------------------------
# This variable is used to enable or disable custom install.
#
# true  : Components mentioned as part of 'customComponents' property
#         are considered for install.
# false : Value for 'customComponents' is not considered.
#------------------------------------------------------------------------------
oracle.install.db.isCustomInstall=false
#------------------------------------------------------------------------------
# This variable is considered only if 'IsCustomInstall' is set to true.
#
# Description: List of Enterprise Edition Options you would like to install.
#
#              The following choices are available. You may specify any
#              combination of these choices.  The components you choose should
#              be specified in the form "internal-component-name:version"
#              Below is a list of components you may specify to install.
#
#              oracle.rdbms.partitioning:11.2.0.1.0 - Oracle Partitioning
#              oracle.rdbms.dm:11.2.0.1.0 - Oracle Data Mining
#              oracle.rdbms.dv:11.2.0.1.0 - Oracle Database Vault
#              oracle.rdbms.lbac:11.2.0.1.0 - Oracle Label Security
#              oracle.rdbms.rat:11.2.0.1.0 - Oracle Real Application Testing
#              oracle.oraolap:11.2.0.1.0 - Oracle OLAP
#------------------------------------------------------------------------------
oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0
###############################################################################
#                                                                             #
# PRIVILEGED OPERATING SYSTEM GROUPS                                          #
# ------------------------------------------                                  #
# Provide values for the OS groups to which OSDBA and OSOPER privileges       #
# needs to be granted. If the install is being performed as a member of the   #
# group "dba", then that will be used unless specified otherwise below.       #
#                                                                             #
###############################################################################
#------------------------------------------------------------------------------
# The DBA_GROUP is the OS group which is to be granted OSDBA privileges.
#------------------------------------------------------------------------------
oracle.install.db.DBA_GROUP=dba
#------------------------------------------------------------------------------
# The OPER_GROUP is the OS group which is to be granted OSOPER privileges.
#------------------------------------------------------------------------------
oracle.install.db.OPER_GROUP=dba
#------------------------------------------------------------------------------
# Specify the cluster node names selected during the installation.
#------------------------------------------------------------------------------
oracle.install.db.CLUSTER_NODES=
#------------------------------------------------------------------------------
# Specify the type of database to create.
# It can be one of the following:
# - GENERAL_PURPOSE/TRANSACTION_PROCESSING
# - DATA_WAREHOUSE
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.type=
#------------------------------------------------------------------------------
# Specify the Starter Database Global Database Name.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.globalDBName=
#------------------------------------------------------------------------------
# Specify the Starter Database SID.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.SID=
#------------------------------------------------------------------------------
# Specify the Starter Database character set.
#
# It can be one of the following:
# AL32UTF8, WE8ISO8859P15, WE8MSWIN1252, EE8ISO8859P2,
# EE8MSWIN1250, NE8ISO8859P10, NEE8ISO8859P4, BLT8MSWIN1257,
# BLT8ISO8859P13, CL8ISO8859P5, CL8MSWIN1251, AR8ISO8859P6,
# AR8MSWIN1256, EL8ISO8859P7, EL8MSWIN1253, IW8ISO8859P8,
# IW8MSWIN1255, JA16EUC, JA16EUCTILDE, JA16SJIS, JA16SJISTILDE,
# KO16MSWIN949, ZHS16GBK, TH8TISASCII, ZHT32EUC, ZHT16MSWIN950,
# ZHT16HKSCS, WE8ISO8859P9, TR8MSWIN1254, VN8MSWIN1258
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.characterSet=WE8MSWIN1252
#------------------------------------------------------------------------------
# This variable should be set to true if Automatic Memory Management
# in Database is desired.
# If Automatic Memory Management is not desired, and memory allocation
# is to be done manually, then set it to false.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryOption=true
#------------------------------------------------------------------------------
# Specify the total memory allocation for the database. Value(in MB) should be
# at least 256 MB, and should not exceed the total physical memory available
# on the system.
# Example: oracle.install.db.config.starterdb.memoryLimit=512
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.memoryLimit=
#------------------------------------------------------------------------------
# This variable controls whether to load Example Schemas onto the starter
# database or not.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.installExampleSchemas=false
#------------------------------------------------------------------------------
# This variable includes enabling audit settings, configuring password profiles
# and revoking some grants to public. These settings are provided by default.
# These settings may also be disabled.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.enableSecuritySettings=true
###############################################################################
#                                                                             #
# Passwords can be supplied for the following four schemas in the             #
# starter database:                                                           #
#   SYS                                                                       #
#   SYSTEM                                                                    #
#   SYSMAN (used by Enterprise Manager)                                       #
#   DBSNMP (used by Enterprise Manager)                                       #
#                                                                             #
# Same password can be used for all accounts (not recommended)                #
# or different passwords for each account can be provided (recommended)       #
#                                                                             #
###############################################################################
#------------------------------------------------------------------------------
# This variable holds the password that is to be used for all schemas in the
# starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.ALL=
#-------------------------------------------------------------------------------
# Specify the SYS password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYS=
#-------------------------------------------------------------------------------
# Specify the SYSTEM password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSTEM=
#-------------------------------------------------------------------------------
# Specify the SYSMAN password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.SYSMAN=
#-------------------------------------------------------------------------------
# Specify the DBSNMP password for the starter database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.password.DBSNMP=
#-------------------------------------------------------------------------------
# Specify the management option to be selected for the starter database.
# It can be one of the following:
# 1. GRID_CONTROL
# 2. DB_CONTROL
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.control=DB_CONTROL
#-------------------------------------------------------------------------------
# Specify the Management Service to use if Grid Control is selected to manage
# the database.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
#-------------------------------------------------------------------------------
#-------------------------------------------------------------------------------
# This variable indicates whether to receive email notification for critical
# alerts when using DB control.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
#-------------------------------------------------------------------------------
# Specify the email address to which the notifications are to be sent.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.emailAddress=
#-------------------------------------------------------------------------------
# Specify the SMTP server used for email notifications.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=

###############################################################################
#                                                                             #
# SPECIFY BACKUP AND RECOVERY OPTIONS                                         #
# ------------------------------------                                        #
# Out-of-box backup and recovery options for the database can be mentioned    #
# using the entries below.                                                    #
#                                                                             #
###############################################################################
#------------------------------------------------------------------------------
# This variable is to be set to false if automated backup is not required. Else
# this can be set to true.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.enable=false
#------------------------------------------------------------------------------
# Regardless of the type of storage that is chosen for backup and recovery, if
# automated backups are enabled, a job will be scheduled to run daily at
# 2:00 AM to backup the database. This job will run as the operating system
# user that is specified in this variable.
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.osuid=
#-------------------------------------------------------------------------------
# Regardless of the type of storage that is chosen for backup and recovery, if
# automated backups are enabled, a job will be scheduled to run daily at
# 2:00 AM to backup the database. This job will run as the operating system user
# specified by the above entry. The following entry stores the password for the
# above operating system user.
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.automatedBackup.ospwd=
#-------------------------------------------------------------------------------
# Specify the type of storage to use for the database.
# It can be one of the following:
# - FILE_SYSTEM_STORAGE
# - ASM_STORAGE
#------------------------------------------------------------------------------
oracle.install.db.config.starterdb.storageType=
#-------------------------------------------------------------------------------
# Specify the database file location which is a directory for datafiles, control
# files, redo logs.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
#-------------------------------------------------------------------------------
# Specify the backup and recovery location.
#
# Applicable only when oracle.install.db.config.starterdb.storage=FILE_SYSTEM
#-------------------------------------------------------------------------------
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
#-------------------------------------------------------------------------------
# Specify the existing ASM disk groups to be used for storage.
#
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.diskGroup=
#-------------------------------------------------------------------------------
# Specify the password for ASMSNMP user of the ASM instance.
#
# Applicable only when oracle.install.db.config.starterdb.storage=ASM_SYSTEM
#-------------------------------------------------------------------------------
oracle.install.db.config.asm.ASMSNMPPassword=
#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username.
#
#  Example   : MYORACLESUPPORT_USERNAME=metalink
#------------------------------------------------------------------------------
MYORACLESUPPORT_USERNAME=
#------------------------------------------------------------------------------
# Specify the My Oracle Support Account Username password.
#
# Example    : MYORACLESUPPORT_PASSWORD=password
#------------------------------------------------------------------------------
MYORACLESUPPORT_PASSWORD=
#------------------------------------------------------------------------------
# Specify whether to enable the user to set the password for
# My Oracle Support credentials. The value can be either true or false.
# If left blank it will be assumed to be false.
#
# Example    : SECURITY_UPDATES_VIA_MYORACLESUPPORT=true
#------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
#------------------------------------------------------------------------------
# Specify whether user wants to give any proxy details for connection.
# The value can be either true or false. If left blank it will be assumed
# to be false.
#
# Example    : DECLINE_SECURITY_UPDATES=false
#------------------------------------------------------------------------------
DECLINE_SECURITY_UPDATES=true
#------------------------------------------------------------------------------
# Specify the Proxy server name. Length should be greater than zero.
#
# Example    : PROXY_HOST=proxy.domain.com
#------------------------------------------------------------------------------
PROXY_HOST=
#------------------------------------------------------------------------------
# Specify the proxy port number. Should be Numeric and atleast 2 chars.
#
# Example    : PROXY_PORT=25
#------------------------------------------------------------------------------
PROXY_PORT=
#------------------------------------------------------------------------------
# Specify the proxy user name. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_USER=username
#------------------------------------------------------------------------------
PROXY_USER=
#------------------------------------------------------------------------------
# Specify the proxy password. Leave PROXY_USER and PROXY_PWD
# blank if your proxy server requires no authentication.
#
# Example    : PROXY_PWD=password
#------------------------------------------------------------------------------
PROXY_PWD=