Oracle Database Administration

Here is a simple manual process of copy an Oracle database. Ref: http://proora.com/oracle/copy_database 1. Copy $ORACLE_HOME/dbs/initSOURCE.ora to $ORACLE_HOME/dbs/initTARGET.ora change in initTARGET.ora:   db_name,   control_files,   user_dump_dest,   background_dump_dest,   core_dump_dest and may be audit_file_dest,log_archive_dest   Create the bdump, udump and cdump directories 2. Create the script that will re-create the controlfile ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/somedir/cre_controlfile.sql'; Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement. Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE. On the same line, modify the database name changing it from SOURCE to TARGET. On the same line, change the ...

REF: http://www.arunsankar.in/2013/06/performance-issue-basic-checks.html?m=1 Oracle Performance Issues - Basic Checks What are the basic checks for a junior DBA when slowness reports in the system? Taking user inputs: This is the very first step for troubleshooting any performance issues, get user inputs.You may use the below tips for this. Is application is slow or any particular batch processing is slow? Slowness is observed through out the system or only few or one user Is it happening in some particular timing ? Is it slow right now? By collecting these information we will get an outline of what needs to be checked. Now login to system and start investigation. Check the resource utilization: You can check the CPU,Load,Memory utilization, use top or topas command in unix. Check any single process is holding the CPU for long time -- note the process ID. Press 'c' in top command, it will give you the time and process which is consuming more CPU. ...

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