在文章开头本人说下结吧:
本文实验环境:11.2 。为了避免 备库ORA-01578和ORA-26040错误,主备 一定要开启 force logging
针对这些 nonlogged 的块,在 12c 以前的版本中,在 v$datafile 中的 FIRST_NONLOGGED_SCN (备库) 字段记录了相关信息,
从12c 开始,使用 v$nonlogged_block (备库)记录相关信息
实验 一:数据库开启 force_logging ,这种情况即使语句中指定了 nologging 的参数,也不会生效 ,会被 database 级别 force logging 覆盖
SQL> select force_logging from v$database;
FORCE_
------
YES
主库执行 :
SQL> create table bin nologging as select * from hlb;
Table created.
备库查询:
SQL> select * from bin;
ID
----------
1
1
2
3
实验 二:数据库关闭 force_logging,这种情况假如语句中指定了 nologging 参数,将导致备库 ORA-01578和ORA-26040错误,该错误具体如何处理 ,在本文中暂时不讨论了(这种方法在19c中不能造成备库逻辑坏块)
SQL> select FORCE_LOGGING from v$database;
FORCE_
------
NO
主库执行:
SQL> create table liang nologging as select * from hlb;
Table created.
备库查询:
SQL> select * from liang;
select * from liang
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 86281)
ORA-01110: data file 1: '/export/11g/oradata/orcl/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
实验三:19C 中制造备库 nologging 逻辑坏块, 使用:
impdp \'/ as sysdba\' full=y dumpfile=liang.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
从12.2 以后,修复这种备库的坏块非常简便:
DBA可以从备库的V$NONLOGGED_BLOCK这个视图查看到相关信息。不需要发送主库的整个数据文件,而是在RMAN执行一个简单的命令来恢复它们:
首先,在备库停止实时日志应用:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
备库登陆RMAN执行:
RECOVER DATABASE NONLOGGED BLOCK;
RMAN> recover database nonlogged block;
Starting recover at 06-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
starting recovery of nonlogged blocks
List of Datafiles
=================
File Status Nonlogged Blocks Blocks Examined Blocks Skipped
---- ------ ---------------- --------------- --------------
1 OK 0 0 136959
2 OK 0 0 84479
3 OK 0 0 111999
4 OK 0 1 31358
Details of nonlogged blocks can be queried from v$nonlogged_block view
recovery of nonlogged blocks complete, elapsed time: 00:02:12
Finished recover at 06-JUN-21
RMAN> select count(*) from V$NONLOGGED_BLOCK;
COUNT(*)
----------
0