How to find objects that are performing unrecoverable/nologgging option?

Using the steps below one can find the object that is unrecoverable from UNRECOVERABLE_CHANGE# in the v$datafile, one would use this to find the object that is unrecoverable since the last backup.

In this scenario datafile# 5 had unrecoverable/nologging as when we query v$datafile it shows and UNRECOVERABLE_TIME was greater than when the last backup was done.

SQL> column UNRECOVERABLE_CHANGE# format 99999999999999

SQL> select file#, UNRECOVERABLE_CHANGE# from v$datafile where file# = 5;

FILE# UNRECOVERABLE_CHANGE#
———- ———————
5 37640948153

— find the archive log that contains the sequence#
SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 < next_change#;

SEQUENCE# NAME
———- ——————————————————————————–
2108 /u01/TESTDB/arch/arch_1_2108.arc

— This will create file in user_dump
SQL> alter system dump logfile '/u01/TESTDB/arch/arch_1_2108.arc' layer 19;

In 8i the object id is not included but in the 10g trace file the object id is included in the trace file
/u01/admin/TESTDB/udump $ grep DBA ora_168168_testdb.trc


CHANGE #1 INVLD AFN:5 DBA:0x01401e0a BLKS:0x0020 SCN:0x0008.c39349b9 SEQ: 1 OP:19.2

# to find the file# and block
SET SERVEROUTPUT ON
DECLARE
l_dba NUMBER := TO_NUMBER (‘01401e0a’,’XXXXXXXX’);
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘File : ‘||l_file);
DBMS_OUTPUT.PUT_LINE (‘Block : ‘||l_block);
END;
/

Output:
File : 5
Block : 7690

SQL> SELECT owner, segment_name FROM dba_extents where file_id = 5 and block_id = 7690;

OWNER SEGMENT_NAME
—————————— —————-
SCOTT TEST_TABLE

Advertisements

One comment

  1. Hi Amin,

    This is an interesting document. Can you pls confirm if the below SQL is complete or correct as it gives error

    ERROR at line 1:
    ORA-00920: invalid relational operator

    – find the archive log that contains the sequence#
    SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 ;

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.