| 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
[email protected]
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))); |
