ora-凯发app官方网站

问题复现
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 操作引起的坏块 - 错误解释和凯发app官方网站的解决方案 (doc id 1623284.1)

阅读(496) | 评论(0) | 转发(0) |

上一篇：ocw release update 没有升级可以吗？

下一篇：估算创建索引的空间大小

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们