ORA-26040 复现以及v$nonlogged_block何时有数据

1

问题复现
col force_logging for a15
select force_logging,LOG_MODE from v$database;
 NO   ARCHIVELOG

set lines 100
col name for a40
select name, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
select FILE#,BLOCK#,NONLOGGED_START_CHANGE# from v$nonlogged_block;

先备份数据库
rman target /
validate database;
report unrecoverable;
目前为止没有任何异常，为了通过recover 复现问题，先备份全库
backup database format '/arch/db_%U';

--DROP USER a CASCADE;
create user a identified by a;
grant connect,resource,select_catalog_role to a;
alter user a quota unlimited on users;
conn a/a

create table t1 nologging as select * from dba_users;

COL TABLE_NAME FOR A30
select table_name,LOGGING from user_tables where LOGGING='NO';
创建的nologging表已经能看到

set lines 100
col name for a40
select name, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
查询v$datafile有不为0的，但是查询表本身数据没事
select count(0) from a.t1;

检查当前没有坏块
rman target /
report unrecoverable;
提示有不可修复的了

validate database;
这时还是看不到异常，需要recover后才能看到问题

select FILE#,BLOCK#,NONLOGGED_START_CHANGE# from v$nonlogged_block;
无异常，v$nonlogged_block应该是只有recover database后，再validate database，如果发现无法修复的块才有数据

整体来说，数据查询没影响，但是能看到数据库内部已经能察觉到问题了，report unrecoverable和 v$datafile的UNRECOVERABLE_CHANGE#
即使此时删除用户a，v$datafile仍然有异常信息（UNRECOVERABLE_CHANGE#不为空）

rman target /
shutdown immediate
startup mount
restore database;
recover database;
alter database open;

select FILE#,BLOCK#,NONLOGGED_START_CHANGE# from v$nonlogged_block;
有数据了！


结论：
nologging 的表不恢复数据库，v$nonlogged_block中看不出来

nologging 检测方法：

col OWNER for a32
COL TABLE_NAME FOR A30
select owner,table_name,LOGGING from dba_tables where LOGGING='NO' and owner not in ('SYS','SYSTEM','DBSNMP','GSMADMIN_INTERNAL','MDSYS','DVSYS','WMSYS','ORDDATA','XDB') order by 1,2;

set lines 100
col name for a40
select name, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;

rman target /
report unrecoverable;

解决方法：
备份相关文件

参考：
ORA-1578  ORA-26040 - NOLOGGING 操作引起的坏块 - 错误解释和解决方案 (Doc ID 1623284.1)