CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Breaking Oracle
Version 11.1.0.6
 
General Information
The demos on this page are taken from Jeremiah Wilton's paper presented at UKOUG '08 in Birmingham UK by Dan Morgan.

Given the fact that Linux/UNIX are superior operating systems to Windows if you choose to try some of these demos with Windows expect that you may need to reboot your server.


Useful Views
SELECT view_name FROM dba_views
WHERE view_name LIKE '%V%WAIT%'
AND owner = 'SYS'
ORDER BY 1;

VIEW_NAME
------------------------------
DBA_HIST_SERVICE_WAIT_CLASS
GV_$SERVICE_WAIT_CLASS
GV_$SESSION_WAIT
GV_$SESSION_WAIT_CLASS
GV_$SESSION_WAIT_HISTORY
GV_$SYSTEM_WAIT_CLASS
GV_$WAITCLASSMETRIC
GV_$WAITCLASSMETRIC_HISTORY
GV_$WAITSTAT
V_$SERVICE_WAIT_CLASS
V_$SESSION_WAIT
V_$SESSION_WAIT_CLASS
V_$SESSION_WAIT_HISTORY
V_$SYSTEM_WAIT_CLASS
V_$WAITCLASSMETRIC
V_$WAITCLASSMETRIC_HISTORY
V_$WAITSTAT
V_$WAIT_CHAINS

Tailing the alert log
-- open a terminal window
adrci> show home

adrci> set homepath diag dbms\orabase\orabase

adrci> show alert -tail -f

-- to verify
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
Hangs

Hang I/O calls by processes that can't time out
root@dbhost# mount -F nfs -o rw localhost:/opt/oracle/oradata/od08/bct/mnt/orabct

alter database enable block change tracking using file  '/mnt/orabct/bct.ora';

user@dbclient$ ./charbench

root@dbhost# /etc/init.d nfs.server stop

col program format a15 trunc
col event format a45

SELECT sid, program, event, state, seconds_in_wait, blocking_session
FROM v$session
WHERE type != 'BACKGROUND';
 
Spins

Hang and spin in regular expression search
SELECT 1 FROM dual
WHERE regexp_like(' ','^*[ ]*a');

oracle@dbhost$ ps -eo pid,pcpu,args | sort -n +1 | tail -10

SQL> @waits

Spinning background procs can't always be killed without terminating the instance
oracle@db02$ ps -eo pid,s,args | grep ora_arc
oracle@db02$ kill -STOP `ps -eo pid,args | grep ora_arc | grep -v grep | awk '{print $1}'`

oracle@db02$ ps -eo pid,s,args | grep ora_arc

SELECT group#, sequence#, archived, status
FROM v$log
ORDER BY sequence#;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

oracle@db02 $ ps -eo pid,pcpu,args | sort -n +1 | tail -10

col event format a45

SELECT event, state, seconds_in_wait
FROM v$session
WHERE type = 'BACKGROUND'
AND program LIKE '%LGWR%';
 
Crashes

Forcing a generic ORA-00600
DECLARE
 a EXCEPTION;
 PRAGMA EXCEPTION_INIT(a, -600);
BEGIN
  RAISE a;
END;
/
Forcing an ORA-00600 with arguments SQL> oradebug unit_test dbke_test dde_flow_kge_ora ouch! 0 0

Bug 6073325: SELECT QUERY with CONNECT BY PRIOR fails with ORA-00600 [KKQCBYDRV:1]
SELECT 1
FROM sys.table_privileges tp, user_objects uo
WHERE tp.grantee IN (
  SELECT 1
  FROM sys.dba_role_privs
  CONNECT BY PRIOR prior granted_role = grantee
  START WITH with grantee = 'scott');

ORA-07445 Simple Case
SELECT spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.sid = sys_context('USERENV','SID');

oracle@db02$ kill -SEGV 2513

ORA-07445 Using PL/SQL
DECLARE
 a EXCEPTION;
 PRAGMA EXCEPTION_INIT(a, -7445);
BEGIN
  RAISE a;
END;
/

Bug #6244173 producing ORA-07445
CREATE TABLE t1(
c1 varchar2(60),
c2 varchar2(1),
c3 varchar2(60),
c4 varchar2(60));

CREATE TABLE t2(
col1 varchar2(60));

EXPLAIN PLAN FOR
SELECT 1
FROM t1 a, t2 b ,t1 c
WHERE b.col1 = 'xxslc_department'
AND a.c1 NOT BETWEEN c.c3 AND c.c4
START WITH a.c2='p'
CONNECT BY PRIOR a.c1 BETWEEN a.c3 AND a.c4;
 
Instance Crashes
Simple case: kill an essential background process oracle@db02$ ps -eo pid,args | grep ora_ckpt | grep -v grep
oracle@db02$ kill -KILL <pid>
Simple case: send a SIGSEGV or SIGBUS to an essential background process oracle@db02$ ps -eo pid,args | grep ora_dbrm | grep -v grep
oracle@db02$ kill -SEGV <pid>
Cause fatal errors in essential background processes SELECT pid, program, background
FROM v$process
WHERE background = 1;

oradebug setorapid 16

oradebug call kgeasnmierr 4455547624 18446744071472029760 18446744071562043788 2 1 1
 
Corruption

Simple example: garbage into a block based on finding a block in a known table
SELECT MIN(dbms_rowid.rowid_block_number(rowid))
FROM soe.customers;

SELECT customer_id, cust_email
FROM soe.customers
WHERE dbms_rowid.rowid_block_number(rowid) = 12;

oracle@db02$ dd if=/opt/oradata/od08/soe.dbf bs=8192 iseek=12 count=1 | strings | grep Sachin.Neeson@oracle.com

oracle@db02$ dd if=$ORACLE_HOME/bin/oracle \
of=/opt/oradata/od08/soe.dbf bs=8192 oseek=12 count=1 \ conv=notrunc

1+0 records in
1+0 records out

ALTER SYSTEM CHECKPOINT;

Check the alert log - no errors!
Read the block
SELECT customer_id, cust_email
FROM soe.customers
WHERE dbms_rowid.rowid_block_number(rowid) = 12;

alter system flush buffer_cache;

SELECT customer_id, cust_email
FROM soe.customers
WHERE dbms_rowid.rowid_block_number(rowid) = 12;
Restore data block (read again) RMAN> blockrecover datafile '/opt/oradata/od08/od08/soe.dbf' block 12;
 
Logical Corruption

User oops: missing where clause
UPDATE customers
SET cust_first_name = 'Nimrod'
WHERE rownum < 1000;

COMMIT;

SELECT versions_startscn, versions_endscn, versions_xid
FROM customers
VERSIONS BETWEEN timestamp sysdate-(.25/24) and sysdate
WHERE cust_first_name = 'Nimrod';

SELECT undo_sql
FROM flashback_transaction_query
WHERE xid = '00090015000003A1'
 
BreakDB Source Code

PSOUG RMAN Class Finals Framework

This framework  reports the action to be taken using dbms_output.put_line. To make this work for your environment comment out this line and replace with the appropriate command to drop or corrupt the resource.
CREATE OR REPLACE PROCEDURE break_db (breakval PLS_INTEGER) IS
 fname VARCHAR2(513);
 i PLS_INTEGER;
 b BOOLEAN := FALSE;
BEGIN
  dbms_output.put_line(TO_CHAR(breakval));

  SELECT COUNT(*)
  INTO i
  FROM v$backup_files
  WHERE completion_time > SYSDATE-4/24
  AND keep_until > SYSDATE;

  -- does it appear there is a current backup?
  IF i > 0 THEN
    b := TRUE;
  END IF;

  IF breakval = 0 THEN
    dbms_output.put_line('You were lucky this time: Try again');
  ELSIF breakval = 1 THEN -- drop a control file
    SELECT value
    INTO fname
    FROM gv$parameter
    WHERE name = 'control_files';

    fname := SUBSTR(fname,1,INSTR(fname,',',1,1)-1);

    dbms_output.put_line('Dropping Control File ' || fname);
  ELSIF breakval = 2 THEN -- drop an inactive log file
    SELECT MAX(member)
    INTO fname
    FROM gv$logfile lf, gv$log lg
    WHERE lf.group# = lg.group#
    AND lg.status = 'INACTIVE';

    dbms_output.put_line('Dropping Inactive Log File Member ' || fname);
  ELSIF breakval = 3 THEN -- drop active/current log file
    SELECT MAX(member)
    INTO fname
    FROM gv$logfile lf, gv$log lg
    WHERE lf.group# = lg.group#
    AND lg.status IN ('ACTIVE', 'CURRENT');

    dbms_output.put_line('Dropping Active or Current Log File Member ' || fname);
  ELSIF breakval = 4 THEN -- dropping log group
    SELECT MIN(group#)
    INTO i
    FROM gv$log;

    FOR rec IN (SELECT member FROM gv$logfile) LOOP
      dbms_output.put_line('Dropping Log Group ' || TO_CHAR(i) || ' File: ' || rec.member);
    END LOOP;
  ELSIF breakval = 5 THEN -- drop data file
    SELECT MAX(tablespace_name)
    INTO fname
    FROM (
      SELECT tablespace_name, COUNT(*)
      FROM dba_data_files
      GROUP BY tablespace_name
      HAVING COUNT(*) = 1)
    WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP');

    SELECT MAX(file_name)
    INTO fname
    FROM dba_data_files
    WHERE tablespace_name = fname;

    dbms_output.put_line('Dropping Data File ' || fname || ' From Tablespace');
  ELSIF breakval = 6 THEN -- drop tablespace
    SELECT MAX(tablespace_name)
    INTO fname
    FROM dba_tablespaces
    WHERE contents = 'PERMANENT'
    AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX');

    dbms_output.put_line('Dropping Data Tablespace ' || fname);
  ELSIF breakval = 7 THEN -- drop temporary tablespace
    SELECT tablespace_name
    INTO fname
    FROM dba_tablespaces
    WHERE contents = 'TEMPORARY';

    dbms_output.put_line('Dropping Temporary Tablespace ' || fname);
  ELSIF breakval = 8 THEN -- drop undo tablespace
    SELECT tablespace_name
    INTO fname
    FROM dba_tablespaces
    WHERE contents = 'UNDO';

    dbms_output.put_line('Dropping Undo Tablespace ' || fname);
  ELSIF breakval = 9 THEN -- drop system or sysaux tablespace
    dbms_output.put_line('Dropping System or SysAux Tablespace. Have a nice day!');
  ELSE
    dbms_output.put_line('Now you''ve done it!');
  END IF;

  IF NOT b THEN
    dbms_output.put_line('Next Time Use RMAN');
  END IF;
END break_db;
/

SQL> set serveroutput on

SQL> exec break_db(TO_NUMBER(SUBSTR(dbms_crypto.randominteger,3,1)));
 
 
Related Topics
ADR
Exception Handling
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [61 users online]    © 2010 psoug.org