Troubleshoot > ORA-01555 , ORA-30036 , long rollbacks.
Administer > create , size , switch a undo tablespace
.
SAFETY > Never drop a active undo tablespace. Confirm the active transactions , rollback segment status , database role , recovery requirments before removing the undo.
##.
5. baseline undo health
parameters
Tablespace and files
undo extent status
current undo segment status
6. Create a table
after creating a table ,update some value but didn't rollback or commit , then rollback
7. Read consistency
start a quary and update and commit;
8. Undo retentions
check retention settings
Tuned retention
expired vs unexpired space
.
9. ORA - 01555 >
9.1 why ORA-01555 happens
> A query needs an older vertion of a block. if the required undo record has already been overwritten ,then Oracle cannot reconstract the consistent image and raises the ORA-01555.
9.2 Common causes > a. Long running queries.
b. High DML workload generating undo rapidly.
c. size of undo tablespace too small.
d. Undo retention is too low for business query.
e. frequent commint can block cleanout.
f. retention is not guaranteed and space force to reuse.
9.3 ORA-01555 history
9.4 find long quary
9.5 sql text
9.6 alert log search
$> adrci = Oracle and/or its affilites
10. Undo sizing
10.1 undo generate
10.2 calculate appx. undo MB per 10 mints.
10.3 Estimate required undo size
10.4 Comapre max query length to retention
11. create undo tablespace.
12. Active transaction
Undo consumer
13. Flashback and undo dependancy
flashback quary
14. SAFE UNDO
15. PRACTICAL ASSESSMENT
A. Explain UNDO
The undo data stores the old values of modified data . it lets a database rollback active changes during failure or abort.
ROLLBACK SUPPORT >
a. Saves old data = the system stores the original data to undo segment before any changes.
b. Undo changes= if an user cancels a transaction, the system reads the undo data to put old values back.
c. Recovery after loss= after power loss, the database uses the undo logs to put back the uncommited changes during startup.
READ CONSISTENT SUPPORT >
a. Prevents waiting = Readers do not block writer and writers do not block readers.
b. Build past images = when a quary starts, if notes the current time.
c. Reconstract data = when a active changes a modified row, the database uses undo logs to build the old , clean the version of this row.
B. Undo health check
C. Transaction rollback
after creating a table ,update some value but didn't rollback or commit , then rollback