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#
;
