Thinking Out Loud

May 19, 2017

NOLOGGING Bad For Recovery

Filed under: 12c,oracle — mdinh @ 3:41 am

What doesn’t kill you makes you stronger.
I have gone through NOLOGGING hell and not sure if I am out yet.
Disk was lost from server and database was recovered.

Oracle Standard Edition (SE2) for Microsoft Windows x64 (64-bit).

RMAN> restore database validate preview;

  List of Archived Logs in backup set 4098
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15209   134553557  26-APR-17 134574622  26-APR-17
  1    15218   134681063  26-APR-17 134690751  27-APR-17
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 133224369
Recovery must be done beyond SCN 133235710 to clear datafile fuzziness

RMAN> spool log to recover.log
RMAN> recover database until sequence 15209;
RMAN> alter database open resetlogs;
RMAN> exit 

Bug 20315311 – RMAN-5119: recovery can not be done to a consistent state (Doc ID 20315311.8)
The content was last updated on: 17-FEB-2017

RMAN gives the following notification in the 'restore database preview' output even though a successful backup has been performed:
  RMAN-05119: recovery can not be done to a consistent state
 
Workaround
 None

SQL> select * from mytable;

select * from mytable
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 915)
ORA-26040: Data block was loaded using the NOLOGGING option

RMAN> validate tablespace USERS;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134981624

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134981561

Finished validate at 18-MAY-17

RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> exit

RMAN> backup validate check logical database;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134982083
  File Name: 

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134982061
  File Name: 

Finished validate at 18-MAY-17  

Basically, NOLOGGING operation occurred from backup before resetlogs recovery
and there is no datafile/database backup following NOLOGGING operations
since database backup is performed once a day.

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

However, NOLOGGING is intended for configurations in which media recovery 
or the recovery of the corresponding object is not important. 
Thus, if the disk or tape or storage media fails, 
you will not be able to recover your changes from the redo because the changes were never logged.  
SQL> select max(NONLOGGED_END_CHANGE#), max(NONLOGGED_END_TIME), max(RESETLOGS_CHANGE#), max(RESETLOGS_TIME) from v$nonlogged_block;
MAX(NONLOGGED_END_CHANGE#) MAX(NONLOGGED_END_TI MAX(RESETLOGS_CHANGE#) MAX(RESETLOGS_TIME)
-------------------------- -------------------- ---------------------- --------------------
                 134534115

SQL> select count(*) from v$nonlogged_block;
  COUNT(*)
----------
        64

-- NOTICE: this matched what RMAN reported.
SQL> select file#, count(*), sum(blocks) from v$nonlogged_block group by file#;
     FILE#   COUNT(*) SUM(BLOCKS)
---------- ---------- -----------
         5         54         334
         8         10         295

SQL> select resetlogs_change#, resetlogs_time, current_scn from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME       CURRENT_SCN
----------------- -------------------- -----------
        134553558 15-MAY-2017 18:28:53   135266743

SQL> select file#, block#, NONLOGGED_START_CHANGE# from v$nonlogged_block
  2  where NONLOGGED_START_CHANGE# > (select RESETLOGS_CHANGE# from v$database)
  3  ;

no rows selected

SQL>

Truncating table and reloading data resolve SELECT issues but block corruption (Free Block) still exists.
You can read more about Playing with Oracle Free Block Corruption

SQL> truncate table mytable;

Table truncated.

SQL> select * from mytable;

no rows selected

SQL> r
  1  SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$nonlogged_block c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 13  , greatest(f.block_id, c.block#) corr_start_block#
 14  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 15  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 16  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 17  , 'Free Block' description
 18  FROM dba_free_space f, v$nonlogged_block c
 19  WHERE f.file_id = c.file#
 20  AND f.block_id <= c.block# + c.blocks - 1 21 AND f.block_id + f.blocks - 1 >= c.block#
 22  order by file#, corr_start_block#
 23*

                                                                                      START     END
OWNER      SEGMENT_TYPE         SEGMENT_NAME         PARTITION_NAME        FILE#     BLOCK#  BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- -------------------- -------------------- -------------------- ------ ---------- ------- ---------------- ------------------------------
SCHEMA    TABLE                ########                                       5        219     219                1
SCHEMA    TABLE                ########                                       5        227     227                1
SCHEMA    TABLE                ########                                       5        235     235                1
SCHEMA    TABLE                ##################                             5        251     251                1
SCHEMA    TABLE                ##########                                     5        275     275                1
SCHEMA    TABLE                ############                                   5        283     283                1
SCHEMA    TABLE                #############                                  5        459     459                1
SCHEMA    TABLE                ##########                                     5        467     467                1
SCHEMA    TABLE                ################                               5        491     491                1
SCHEMA    TABLE                #############                                  5        499     499                1
SCHEMA    TABLE                ##################                             5        851     851                1
SCHEMA    TABLE                ########                                       5        875     875                1
SCHEMA    TABLE                #############                                  5        883     883                1
SCHEMA    TABLE                ################                               5        891     891                1
SCHEMA    TABLE                ##################                             5        899     903                5
SCHEMA    TABLE                ################                               5        907     907                1
SCHEMA    TABLE                ############                                   5        915     919                5
SCHEMA    TABLE                ############                                   5       1091    1091                1
SCHEMA    TABLE                ##########                                     5       1107    1107                1
SCHEMA    TABLE                ##############                                 5       1323    1327                5
SCHEMA    TABLE                ############                                   5       1331    1331                1
SCHEMA    TABLE                ##############                                 5       1347    1351                5
SCHEMA    TABLE                ############                                   5       1355    1355                1
SCHEMA    TABLE                ###########                                    5       1643    1643                1
SCHEMA    TABLE                ##########                                     5       1651    1651                1
SCHEMA    TABLE                ###########                                    5       2099    2103                5
SCHEMA    TABLE                ###############                                5       2531    2531                1
SCHEMA    TABLE                ##############                                 5       2539    2543                5
SCHEMA    TABLE                ##############                                 5       2547    2547                1
SCHEMA    TABLE                ###############                                5       2707    2707                1
SCHEMA    TABLE                ###########                                    5       2715    2715                1
SCHEMA    TABLE                ###############                                5       2875    2875                1
SCHEMA    TABLE                ##############                                 5       4211    4215                5
                                                                               5       4216    4218                3 Free Block
SCHEMA    TABLE                ###########                                    5       4611    4611                1
SCHEMA    TABLE                ########                                       5       4627    4627                1
                                                                               5       4872    4877                6 Free Block
                                                                               5       4880    4886                7 Free Block
                                                                               5       5064    5071                8 Free Block
                                                                               5       5073    5087               15 Free Block
                                                                               5       5089    5103               15 Free Block
                                                                               5       5105    5119               15 Free Block
                                                                               5       5121    5135               15 Free Block
                                                                               5       5137    5159               23 Free Block
                                                                               5       5161    5175               15 Free Block
                                                                               5       5177    5191               15 Free Block
                                                                               5       5193    5207               15 Free Block
                                                                               5       5209    5223               15 Free Block
                                                                               5       5225    5239               15 Free Block
                                                                               5       5241    5255               15 Free Block
                                                                               5       5257    5263                7 Free Block
                                                                               5       5328    5335                8 Free Block
                                                                               5       5337    5343                7 Free Block
                                                                               5     229506  229550               45 Free Block
                                                                               5     608697  608703                7 Free Block
                                                                               8    1120824 #######                5 Free Block
                                                                               8    1120872 #######                8 Free Block
                                                                               8    1274888 #######                8 Free Block
                                                                               8    1536681 #######               14 Free Block
                                                                               8    1536697 #######               11 Free Block
                                                                               8    1536713 #######                6 Free Block
                                                                               8    1791056 #######                8 Free Block
                                                                               8    2804571 #######               37 Free Block
                                                                               8    3213819 #######                5 Free Block
                                                                               8    3854212 #######              193 Free Block

65 rows selected.

Elapsed: 00:09:22.13
SQL>

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)
In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block

set lines 200 pages 1000 tab off trimsp on timing on
col owner for a10
col segment_type for a20
col segment_name for a20
col partition_name for a20
col partition_name for a20
col description for a30
col file# for 99999
col corr_start_block# heading 'CORRUPT|START|BLOCK#' for 999999999
col corr_end_block# heading 'CORRUPT|END|BLOCK#' for 999999999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#
;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: