Oracle Database Administration

REM * REM * PROGRAM:     rahc_sec_profiles.sql REM * USAGE:       @rahc_sec_profiles.sql REM * REM * LANGUAGE:    SQL*Plus REM * REM * DESCRIPTION: Check FND security profile REM *    Please pay attention in the following profile REM *        Sign-On:Audit Level                   (A=None,B=User,C=Responsibility,D=Forms) REM *        Sign-On:Notification                  Y/N        REM *        Signon Password Custom                        custom function to encrypt/decrypt pass...

DBMS_SCHEDULER set feedback off set echo off set lines 205 column owner format a15 column job_name format a22 column program_name format a24 column status format a10 column state format a10 column last_start format a25 column last_start_norm format a18 column "DURATION (d:hh:mm:ss)" format a21 column next_run format a25 column next_run_norm format a18 var local_offset number begin    select extract(timezone_hour from systimestamp) into :local_offset from dual; end; / select dsj.owner,        dsj.job_name,        dsj.program_name,        dsjlmax.status,        dsj.state, --     to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,        to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,       ...

http://dbawiki.wordpress.com/ Oracle logs clearing on Linux hosts 06 Aug Every two weeks on Linux hosts with Oracle RAC runs this script (/home/oracle/log_backup.sh). It deletes logs older 14 days from /u01/app/ folder. Archives big current log files (alert.log, listener.log) to folder /u01/app/oracle/log_backup/ and nullifies them. It runs every second and fourth friday of every month at 1:15 am through crontab. The script’s logs are in u01/app/oracle/log_backup/. cd /u01/app/oracle mkdir log_backup chmod 700 /home/oracle/log_backup.sh chmod 700 /home/oracle/mv_log_backup.sh #Runs every second and fourth friday of every month at 1:15 am. crontab -l crontab -e 15 1 8-14,22-28 * Fri /home/oracle/log_backup.sh >> /u01/app/oracle/log_backup/log_backup.log 2>&1 15 3 8-14,22-28 * Fri /home/oracle/mv_log_backup.sh #log_backup.sh #Deletes logs older 14 days from /u01/app/ folder. Archives big current log files. export timestamp=$( date +%d.%m....

What are the queries that are running? select sesion.sid, sesion.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time, sql_text from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address and sesion.username is not null / Get the rows fetched, if there is difference it means processing is happening select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# = b.statistic# and sid =&sid and a.value != 0 and b.name like '%row%' Get the sql_hash_value select sql_hash_value from v$session where sid='&sid'; SQL> select sql_hash_value from v$session where sid='&sid'; Enter value for sid: 1075 old 1: select sql_hash_value from v$session where sid='&sid' new 1: select sql_hash_value from v$session where sid='1075' SQL_HASH_VALUE -------------- 928832585 Get the sql_Text SQL> select sql_t...

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