Oracle Database Administration

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  ,      ...

 resolve the reasons for the hung database How to Collect Diagnostics for Database Hanging Issues [ID 452358.1] use the command sqlplus -prelim "/ as sysdba" to log in with the -prelim option. execute the oradebug hanganalyze command to analyze a hung database SQL>oradebug hanganalyze 3 In RAC SQL> oradebug setinst all SQL> oradebug -g def hanganalyze 3 oradebug hanganalyze command a couple of times to generate dump files for varying process states. get a systemstate dump from a non-RAC system by executing the following set of commands. SQL> oradebug setmypid Statement processed. SQL> oradebug dump systemstate 266 Statement processed. SQL> Issue the following commands to get a systemstate dump in a RAC environment: SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug -g all dump systemstate 266 take the systemstate dumps a few times, You can also gather a hanganalyze trace file to identify hung sessions with SQL*Plus when connecte...

To verify this run the following query. It will show the size of the recovery area and how full it is: set lines 100 col name format a60 select name , floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used  / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name /   To fix the problem, you need to either make the flash recovery area larger, or remove some files from it . If you have the disk space available, make the recovery area larger:  alter system set db_recovery_file_dest_size=<size> scope=both / In RAC ALTER SYSTEM SET db_recovery_file_dest_size='6G' SCOPE=BOTH SID='*'; qlplus "/as sysdba" archive log list; !df -h or !df -kgt show parameter db_recovery; NAME                      TYPE         VALUE ----                      ----         ----- db_recovery_file_dest ...