Oracle Database Administration

ASH and AWR Performance Tuning Scripts Ref: http://gavinsoorma.com/2012/11/ash-and-awr-performance-tuning-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...

Oracle Database 11gR2 Metalink Notes Doc ID 1385682.1 The New My Oracle Support User Interface Doc ID 1371759.1 How To Migrate A Huge ASM Database From Windows 64 bit To Linux 64 bit With The Minimal Down Time? Doc ID 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration Doc ID 252219.1 Document TitleSteps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa Doc ID 369644.1 Document TitleFrequently Asked Questions about Restoring Or Duplicating Between Different Versions And Platforms Doc ID 881421.1 Using Active Database Duplication to Create Cross Platform Data Guard Setup (Windows/Linux) Doc ID 988222.1 Oracle Database 11g Release 2 Information Center Doc ID 785351.1 Oracle 11gR2 Upgrade Companion Doc ID 958181.1 Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes Doc ID 881421.1 Using Active Database Duplication to Create Cross Platform Data Guard ...

SELECT 'Last Applied : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES' ) UNION SELECT 'Last Received : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log ); -- Check that Archive Logs are being Shipped -- This query needs to be run on the Primary database SET PAGESIZE 124 COL DB_NAME FORMAT A8 COL HOSTNAME FORMAT A12 COL LOG_ARCHIVED FORMAT 999999 COL LOG_APPLIED FORMAT 999999 COL LOG_GAP FORMAT 9999 COL APPLIED_TIME FORMAT A12 SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM ( SELECT NAME DB_NAME FROM V$DATABASE ), ( SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')...

EManager scripts to monitor set linesize 200 col BEGIN_INTERVAL_TIME format a70 select * from (select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3; --------------------------------------------- Set pages 1000 Set lines 75 Select a.execution_end, b.type, b.impact, d.rank, d.type, 'Message           : '||b.message MESSAGE, 'Command To correct: '||c.command COMMAND, 'Action Message    : '||c.message ACTION_MESSAGE From dba_advisor_tasks a, dba_advisor_findings b, Dba_advisor_actions c, dba_advisor_recommendations d Where a.owner=b.owner and a.task_id=b.task_id And b.task_id=d.task_id and b.finding_id=d.finding_id And a.task_id=c.task_id and d.rec_id=c.rec_Id And a.task_name like 'ADDM%' and a.status='COMPLETED' and a.description like '%4782%' Order by b.impact, d.rank; -----------------------------------------------------------  cat get_addm_report.sql wh...

User Privilege SELECT USERNAME, ACCOUNT_STATUS,CREATED,profile FROM DBA_USERS WHERE USERNAME ='&A'; SELECT USERNAME, ACCOUNT_STATUS,CREATED FROM DBA_USERS WHERE USERNAME LIKE '%&A%'; SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='&A'; select owner,table_name, privilege from dba_tab_privs where grantee='&a'; select * from dba_sys_privs where grantee='&A'; SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='&A'; SELECT DISTINCT GRANTEE,OWNER,TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE OWNER='NORSNAPADM' AND GRANTEE LIKE 'SELECT_%_%' AND TABLE_NAME IN ('DEALER_PROFILE'); ------------------------------------------------------ select * from dba_role_privs where granted_role in ('ADMIN_ROLE','CONNECT','RESOURCE') and grantee not in ('SYS','SYSTEM'); Log on  in trigger CREATE OR REPLACE TRIGGER sysadm_logon_trg_jay after logon on Raj.schem...

ASM Administration select path, disk_number, mount_status, header_status, mode_status, state, name,label from v$asm_disk Select mount_status, header_status, mode_status, state, total_mb, free_mb, name, path, create_date from v$asm_disk set wrap off set lines 120 set pages 999 col “Group Name”   form a25 col “Disk Name”    form a30 col “State”  form a15 col “Type”   form a7 col “Free GB”   form 9,999  select group_number  "Group",  name "Group Name",state  "State" ,type  "Type",total_mb/1024 "Total GB" , free_mb/1024  "Free GB" from   v$asm_diskgroup  / col “Instance” form a8  select c.group_number  "Group"  ,      g.name          "Group Name"  ,      c.instance_name "Instance"  from   v$asm_client c  ,      ...