ORACLE DBA

Table Auditing : CREATE TABLE delete_audit ( login_date DATE, hostnm  VARCHAR2(30), ipaddr  VARCHAR2(30), module VARCHAR2(50), client_info VARCHAR2(50), os_user VARCHAR2(30), terminal VARCHAR2(30)); CREATE OR REPLACE TRIGGER delete_audit AFTER DELETE ON . BEGIN     INSERT INTO delete_audit     (login_date,  hostnm, ipaddr, module, client_info, os_user, terminal)     VALUES     (SYSDATE,      SYS_CONTEXT('USERENV','HOST'),      sys_context('USERENV','IP_ADDRESS'),      sys_context('USERENV','module'),      sys_context('USERENV','client_info'),      sys_context('USERENV','os_user'),      sys_context('USERENV','terminal')      ); END delete_audit; /

Oracle 10g Enable Read Write On Physical Standby Database On Standby Enable Flashback Database. SQL> show parameter db_recovery NAME TYPE VALUE ---------------------- ----------- ----------------------------------- db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G SQL> alter system set db_recovery_file_dest_size=6g; System altered. SQL> alter system set db_flashback_retention_target=1440; System altered. Retention period is 24 hours. SQL> alter database recover managed standby database cancel; Database altered. SQL> select open_mode from v$database; OPEN_MODE ---------- MOUNTED Prepare standby for read write. Cancel Redo apply. SQL> alter database recover managed standby database cancel; Database altered. Create a restore point named before_open_standby SQL> create restore point before_open_standby guarantee flashback database;

Oracle 10g Using RMAN to Roll Forward a Physical Standby Database On Standby SQL>alter database recover managed standby database cancel; SQL> select current_scn from v$database; CURRENT_SCN ----------- 485739 Connect to the primary database using RMAN. [oracle@secondary ~]$ export ORACLE_SID=standby [oracle@secondary ~]$ rman target sys/sys@orcl Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jan 19 07:01:18 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1299658972) RMAN> backup incremental from scn 485739 database 2> format '/u01/app/oracle/backup/forstand_%U' tag 'forstand'; On Primary Copy from primary to standby machine. scp /u01/app/oracle/backup/forstand_* 192.168.1.162:/u01/app /oracle/backup/ On Standby [oracle@secondary ~]$ export ORACLE_SID=standby [oracle@secondary ~]$ rman target=/ RMAN> catalog start with '/u01/app/oracle/backup/forstand';

Gap Detection on Standby Database If the primary and standby databases are configured with FAL_SERVER and FAL_CLIENT parameters then gaps should not occur. But still if the gaps occur do the following. On the standby database. SQL> select * from v$archive_gap; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# ------- -------------- --------------- 1 30 40 The above query shows that their is a gap from log sequence number 30 to 40. On Primary Database. Identify the logs required for gap resolution. SQL> select name from v$archived_log where thread# = 1 and dest_id = 1 and sequence# between 30 and 40; NAME -------------------------------- /u01/app/oracle/arch/archt1_s30.dbf /u01/app/oracle/arch/archt1_s31.dbf /u01/app/oracle/arch/archt1_s32.dbf /u01/app/oracle/arch/archt1_s33.dbf /u01/app/oracle/arch/archt1_s34.dbf /u01/app/oracle/arch/archt1_s35.dbf /u01/app/oracle/arch/archt1_s36.dbf /u01/app/oracle/arch/archt1_

Create a System Trigger to Enable SQL TRACE for a session The following script will enable session trace for the user scott. [oracle@canada u01]$ cat trace_trigger.sql set echo on spool trace_trigger.log DROP TRIGGER SYS.trace_trigger; CREATE OR REPLACE TRIGGER sys.trace_trigger After logon on database Begin if ( user='SCOTT') then execute immediate 'alter session set sql_trace=true'; execute immediate 'alter session set timed_statistics=true'; execute immediate 'alter session set tracefile_identifier="SCOTT"'; execute immediate 'alter session set max_dump_file_size=unlimited'; execute immediate 'alter session set events ''10046 trace name context forever, level 12'''; End if; End; / spool off; SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%'; STATUS -

Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV) As per Doc ID 1580225.1 The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor . dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload Repository. The default retention of AWR is 7 days. Function longest_query : Returns the length of the longest query for a given period . Method 1 SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual; Method 2 (using Start/End time) SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual; Method 3 (using Begin/End AWR snapshot id) SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual; Function required_retention: returns the undo_retention value required for running the longest query. Method 1 select dbms_undo_adv.required_retention from dual; Method 2 (using Start/End time) SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) require

ADRCI Walkthrough Invoke adrci as the oracle user. > adrci ADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/opt/oracle" adrci> show home ADR Homes: diag/diagtool/user_oracle/host_365570786_11 diag/rdbms/orcl/ORCL diag/asm/+asm/+ASM4 diag/tnslsnr/orcl4/listener_cad Set the home adrci> set home diag/rdbms/orcl/ORCL To check the alert log use the following option. adrci> show alert -tail -f 2014-01-17 22:28:35.440000 +00:00 ALTER SYSTEM ARCHIVE LOG 2014-01-17 22:28:38.414000 +00:00 Thread 4 advanced to log sequence 1392 (LGWR switch) Current log# 11 seq# 1392 mem# 0: +REDO/orcl/redot4g11f1.rdo Current log# 11 seq# 1392 mem# 1: +REDO/orcl/redot4g11f2.rdo Archived Log entry 10628 added for thread 4 sequence 1391 ID 0xc5495541 dest 1: or adrci> show alert -tail or adrci> show alert To check for severe problems in database.

Flashback Data Archive (Oracle Total Recall) 11g. Starting from 11g Oracle has added another tool to its bundle of flashback technologies called Flashback Data Archive(Oracle Total Recall). With this feature previous state of a table can be viewed. But Unlike Flashback query and or Flashback transaction query this feature does not depend on the undo_retention of the database. Flashback Data Archive requires one or more tablespaces where it can store historical data for one or several tables. whenever the data in the table is modified its original unmodified value is written to the undo tablespace. From there the Flashback Data Archiver Process(FBDA) will collect it and write the data to flashback data archive. #Create a Tablespace for the Flashback Data Archive SQL> create tablespace fda datafile '/u01/fda01.dbf' size 500M autoextend on next 100M extent management local segment space management auto; SQL> select * from dba_sys_privs whe

Basics of the Oracle Database Architecture Here you will understand and demonstrate knowledge in the following areas:   The Oracle architecture   Starting and stopping the Oracle instance   Creating an Oracle database A major portion of your understanding of Oracle, both to be a successful Oracle DBA and to be a successful taker of the OCP Exam 2 for Oracle database administration, is understanding the Oracle database architecture. About 22 percent of OCP exam 2 is on material in these areas. Oracle in action consists of many different items, from memory structures, to special processes that make things run faster, to recovery mechanisms that allow the DBA to restore systems after seemingly unrecoverable problems. Whatever the Oracle feature, it’s all here. You should review this chapter carefully, as the concepts presented here will serve as the foundation for material covered in the rest of the book, certification series, and your day-to-day responsibilities as an Or