Lesson 05
============================================================================
01. Diagnosing the database
- Data Recovery Advisor (DRA)
Cannot detect data block
corruption
Only for single
instance, not support RAC
Support failover to
standby, but not analysis and repair of standby database
* Create standby
database by using data guard
* RMAN> change failure = close impact or change priority, command
"change failure 5 priority low;"
* RMAN> list failure [All | critical | high | low | closed];, default
is all option [exclude failure (failnum)]
* RMAN> advise failure [All | critical | high | low | closed];,
default is all option [exclude failure (failnum)]
* RMAN> repair failure [using advise option failnum {preview |
noprompt}];
DRA view: v$ir_failure, v$ir_manual_checklist, v$ir_repair,
v$ir_failure_set
- HM automate run and save report, DRA read it by "list
failure" to check the db health status
- Proactive check: use validate command "validate database plus
archivelog;" for data block corruption
02. Data failure examples
- not accessible components:
Missing datafile at O/S,
incorrect permission, offline tbs and deleted.
- physical corruption
8KB data block, 4K for
checksum: checksum failure may caused by invalid block header (dbf from
different platform)
- logical corruption
Related to schema object,
corrupt index, corrupt transaction inconsistencies
- controlfile inconsistencies
- I/O failures, reach limit on max open files in O/S
03. Block corruption detect
- for physical corruption by command "validate database plus
archivelog;" will check database all data block
- for logical corruption by command "validate database plus
archivelog check logical;" will check index, schema object
- ORA-01578 appear in alert.log , v$database_block_corruption and
validate command and caused by hardware issue.
- don't run any defragment tools in oracle database, it will cause
corruption
- Analyze command can check error is permanent or not.
collect statistics for
table, index ….. by command "analyze [table|index] 'name' compute
statistics; "
Parameter to auto detect corruption
- db_block_checking <- default
is false, oracle recommend "full" [off | false | low | medium | full
or true] 10% overhead
(after update, insert, it will run block
header and object contents check except index)
- db_block_checksum <- default
is typical, oracle recommend typical [OFF | TYPICAL | FULL]
- db_lost_write_protect <- default is none, for standby database
only, the committed transaction will also effect in standby
- db_ultra_safe <-
use this to tune upon 3 option by [OFF | DATA_ONLY | DATA_AND_INDEX]
db_ultra_safe
|
OFF
|
DATA_ONLY
|
DATA_AND_INDEX
|
db_block_checking
|
OFF / FALSE
|
MEDIUM
|
FULL / TRUE
|
db_block_checksum
|
TYPICAL
|
FULL
|
FULL
|
db_lost_write_protect
|
TYPICAL
|
TYPICAL
|
TYPICAL
|
04. Block media recovery example
- check v$database_block_corruption (record by validate command)
01. offline tbs, corrupt
some block by hex tools then online tbs (may have error)
02. RMAN> validate
tablespace 'name' check logical;
03. Read alert.log and
select * from v$database_block_corruption;
04. RMAN> recover
corruption list;
05. RMAN> recover
tablespace 'name'; and online the tbs
05. Automatic diagnostic
Critical error -> ADR <-
1. diagnostic_dest
<-
2. $ORACLE_BASE
<-
3. $ORACLE_HOME/log
- 11g not use core_dump, user_dump parameter anymore
- can check from EM database first page
- check by adrci (v$diag_info)
Show incident, show
problem , show alert ….. e.g.
06. Health Monitor (HM)
- v$hm_check (check which option can use to run diagnostic)
- view the HM report by v$hm_run, dbms_hm, adrci , EM
- run HM by dbms_hm or EM
Example:
exec dbms_hm.run_check('check_name','name');
select
dbms_hm.get_run_report('name') from dual;
or
adrci -> show hm_run
07.
Flashback
- Most depend by
undo tablespace (undo management auto, retention, and guarantee)
- parallel insert
will take more undo resource
- under sys schema
cannot flashback
Object Level
|
Scenario examples
|
Flashback Technology
|
Depends on
|
Affects data
|
Database
|
Truncate table,
made multi-table change
|
Database
|
Flashback logs
|
True
|
Table
|
Drop table
|
Drop
|
Recycle bin
|
True
|
Update with the
wrong where clause
|
Table
|
Undo data
|
True
|
|
Compare current
data from past
|
Query
|
Undo data
|
False
|
|
Compare version
of a row
|
Version
|
Undo data
|
False
|
|
Keep historical
transaction data
|
Data archive
|
Undo data
|
True
|
|
Transaction
|
Investigate and
back out suspect transaction
|
transaction
|
Undo/ redo
archivelog
|
True
|
- flashback query
1. Query all data at a specified point
in time
2. only for DML, not support DDL
e.g. select
* from emp as of timestamp to_timestamp('nnnnnnnn','YYYYMMDD:HH24:MI:SS');
select * from emp as scn
xxxxxxxx;
- flashback
version query (will not show haven't committed history)
1. see all version of a row between two
times.
2. see the transactions that changed the
row.
3. not support external tables, temp
tables, fixed tables (sys.table / X$table), view.
4. not support DDL
e.g. select
versions_xid, versions_startscn, versions_endscn, versions_operation, ename,
sal from
emp versions between scn xxxxx
and xxxxx;
or
select versions_xid,
versions_startscn, versions_endscn, versions_operation, ename, sal from
emp versions between scn
minvalue and maxvalue; (enable supplement log)
reverse row
update emp set sal=(select sal
from emp as of timestamp to_timestamp('nnnnnnnnn','yyddmm hh24miss')
where empno=2000) where
empno=2000;
- flashback table
1. not support sys schema
2. EM -> Perform recovery -> table
-> recover
3. enable row movement
e.g. alter table emp enable row movement;
grant flashback any table to
scott;
grant select,insert,update,
delete on emp to scott;
flashback table emp to scn
XXXXXXX;
- flashback transaction
1. see all changes made by a transaction
statement (query by versions_XID)
e.g. select * from
flashback_transaction_query where xid='xxxxxxxx';
2. enable supplemental log
alter database add supplemental
log data;
alter database add supplemental
log data (primary key) columns;
3. each transaction only can perform
once
3. consider transaction_backout option
<- transaction dependency.
Let's
understand a concept before use the option: Transaction Dependence.
For example, 2 transactions TX1 and TX2,
if match below any point that means TX2 dependence TX1:
01. WAW (Write After Write),
means TX1 modify some row and TX2 modify same row again.
02. Primary Key dependence means
the table with primary key. TX1 delete the row and TX2 insert a new row
with same primary key.
03. Foreign Key dependence means
TX1 made new foreign key data after insert or update and TX2 insert or update rows use new foreign key
data.
Understand transaction dependence can help to solve the problem when
conflict. Example for point 02, if you want to reverse TX1 but don't care
TX2, the table will have duplicate primary key.
- [ NOCASCADE ]: TX1 cannot have
dependence by other transaction, otherwise cancel operation and error.
- [ CASCADE ]: TX1 and TX2 reverse
together.
- [ NOCASCADE_FORCE ]: ignore TX2,
reverse TX1 if constraint no conflict the operation will success, otherwise the
conflicted constraint will error and operation fail.
- [ NONCONFILICT_ONLY ]: reverse TX1
without influence TX2. Not same with NOCASCADE_FORCE, it will filter
TX1 reverse SQL and make sure the change not affect TX2.
Example details for point 01: WAW, for a
table only has 3 rows and no constraint:
Case 1: Original
|
Case 2: TX1
update 3 rows
|
Case 3: TX2
update 2 rows
|
ID
|
ID
|
ID
|
---------------
|
---------------
|
---------------
|
1
|
11
|
11
|
2
|
22
|
222
|
3
|
33
|
333
|
This is typical WAW example, TX2
dependence TX1
Now try to reverse TX1 with different
backout option will have different result:
[NOCASCADE] will cause "ORA-55504:
Transaction conflicts in NOCASCADE mode" , table status in Case 3
[CASCADE] will reverse table to Case 1
[NOCASCADE_FORCE], TX2 transaction not
affect, but TX1 will reverse to below
ID
|
---------------
|
1
|
222
|
333
|
Here has some strange from the rule of [
NOCASCADE_FORCE ]: the reverse should ignore TX2 and perform on all rows, but why row 2 & 3 didn't
reverse ? Because in this sample, reverse SQL the where clause include ROWID for constraint because database enabled
supplemental log.
[NONCONFILICT_ONLY] the result is same
as [NOCASCADE_FORCE]. However, even the result is same, but the process is different. The rows reverse
SQL which related to TX2 will be filter first.
Real world
example:
Under HR schema, manger wants to
increase Michael salary 10%.
SQL> select FIRST_NAME, SALARY from
hr.employees where employee_id=201;
FIRST_NAME SALARY
-------------------- ----------
Michael 13000
However, by user mistake, all staff
increase salary 500% and committed
SQL> update hr.employees set
salary=salary*5;
106 rows updated.
SQL> commit;
Then HR on progress increase Michael 10%
SQL> update hr.employees set
salary=salary*1.1 where employee_id=201;
1 row updated.
SQL> commit;
Manger just wants Michael salary from
13000 to 14300. However, by the user mistake, Michael salary is 71500.
After few minutes, HR discovered all
staff salary abnormal and contact DBA investigation. DBA decide use flashback_transaction_query to find out
the problem on last 15 minutes.
SQL> select distinct xid, commit_scn
from flashback_transaction_query where table_owner='HR' and table_name='EMPLOYEES' and
commit_timestamp > systimestamp - interval '15' minute order by commit_scn;
DBA found last 15 minutes have 2 transactions
XID COMMIT_SCN
---------------- ----------
0300080081090000 3062978
01001D00AE080000 3063594
DBA check XID 0300080081090000 and found
abnormal bulk salary update
SQL> select undo_sql from
flashback_transaction_query where xid='0300080081090000'
UNDO_SQL
----------------------------------------------------------------------------------------------------
update
"HR"."EMPLOYEES" set "SALARY" = '10000' where
ROWID = 'AAAVTFAAFAAAADOAAJ';
update
"HR"."EMPLOYEES" set "SALARY" = '8300' where
ROWID = 'AAAVTFAAFAAAADOAAI';
update
"HR"."EMPLOYEES" set "SALARY" = '10000' where
ROWID = 'AAAVTFAAFAAAADOAAG';
update
"HR"."EMPLOYEES" set "SALARY" = '3000' where
ROWID = 'AAAVTFAAFAAAADNABh';
..
…
Now DBA decide use transaction_backout with
nocascade option to rollback that transaction
exec dbms_flashback.transaction_backout(1,xid_array('0300080081090000'),dbms_flashback.nocascade);
*
ERROR at line 1:
ORA-55504: Transaction conflicts in
NOCASCADE mode
The result show dependence, so for the reasonable
option should be rollback all change by cascade option.
exec
dbms_flashback.transaction_backout(1,xid_array('0300080081090000'),dbms_flashback.cascade);
PL/SQL procedure successfully completed.
After run the flashback transaction, dba
check the report and found both transaction are rollback
SQL> select
xid,dependent_xid,backout_mode from dba_flashback_txn_state;
XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ----------------
01001D00AE080000 CASCADE
0300080081090000 01001D00AE080000 CASCADE
Finally, DBA check Michael salary
rollback to original.
- parallel DML
enhance performance
e.g. alter
session enable parallel dml;
insert into emp select * from
emp;
commit;
alter session disable parallel
dml;
or
select /*+ parallel */ count(*)
from sys.all_objects;
沒有留言:
張貼留言