| 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\rdbms\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 generic ORA-00600s |
DECLARE
a EXCEPTION;
PRAGMA EXCEPTION_INIT(a, -600);
BEGIN
RAISE a;
END;
/
-- or
SELECT current_scn FROM v$database;
SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, action#
FROM trigger$
VERSIONS BETWEEN SCN 79023381 AND 79024381; |
| Forcing an ORA-00600 with arguments |
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 |
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 that line and replace with the
appropriate command to drop or corrupt the resource. One simple way to do it is with UTL_FILE.
I have intentionally not included the destructive code here but if you take my disaster avoidance and recovery class ... you will find the numbers scrambled,
the code wrapped, and the damage unmistakeable. |
CREATE OR REPLACE PROCEDURE break_db (breakval PLS_INTEGER) AUTHID DEFINER 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))); |
