Category Archives: Database Administration
Difference between locks and latches
Difference between locks and latches
ORA-1578: ORACLE data block corrupted
Issue: Database is in Flashback Mode. Enabled No logging on some db Tables Recover the database. Truncate those no logging Temporary tables. Now we get error message ORA-1578: ORACLE data block corrupted Reason: Oracle says it is a behavior in 10g and fixed in 11g Rel1. Cannot Reuse a Corrupt Block in Flashback Mode, ORA-1578 [...]
Flashback Database disabled automatically
Issue 1: Intially Flashback Database was enabled but noticed Flashback was disabled automatically long time ago. Reason: It could be because the flashback area 100% Once Flashback Area become 100% full then oracle will log in Alert that Flashback will be disabled and it will automatically turn off Flash Back without user intervention.
ASM Limitation
ASM Limitation
How can I check if there is anything rolling back?
It depends on how you killed the process. If you did and alter system kill session you should be able to look at the used_ublk block in v$transaciton to get an estimate for the rollback being done. If you killed to server process in the OS and pmon is recovering the transaction you can look [...]
Views that displays information on locks
DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on DBA_DDL_LOCKS – Shows all DDL locks held or being requested DBA_DML_LOCKS – Shows all DML locks held or being requested DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock DBA_LOCKS – Shows [...]
Query to display blocked session and their blocking session details in Oracle 10g
SELECT sid, serial#, blocking_session_status, blocking_session FROM v$session WHERE blocking_session IS NOT NULL;
RMAN Restore Preview
The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the [...]
How to find out how much rollback a session has to do
select time_remaining from v$session_longops where sid =<sid of the session doing the rollback>; Usage of v$session_longops: =========================== SQL> desc v$session_longops; SID NUMBER Session identifier SERIAL# NUMBER Session serial number OPNAME VARCHAR2(64) Brief description of the operation TARGET VARCHAR2(64) The object on which the operation is carried out TARGET_DESC VARCHAR2(32) Description of the target SOFAR NUMBER [...]
I have attended Ask Tom Live seminar about 11g Rel2 at Tokyo, Japan. Do you believe it I have got an opportunity to take a snap with him. I am very happy to share with you.