Oracle Database Administration

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

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