Nologging操作对standby的影响
1.primary 首先要设置为 force_log mode ,然后再做备份,在应用到备库上。
2.switch over 之前需要检查v$database_block_corruption 视图 ,看看有没有坏块,
前提是该数据库已经做全备份。
RMAN reports it in v$database_block_corruption with CORRUPTION_TYPE=NOLOGGING
3.如何验证 Nologging方法
Nologging操作对standby的影响
一、实验环境
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> select force_logging from v$database;
FOR
---
NO
二、Nologging操作
1.在primary上执行
SQL> create table test nologging tablespace test as select * from all_objects where rownum<1001;
Table created.
2.归档当前日志
SQL> alter system archive log current;
System altered.
3.在备库,等刚才传过来的日志应用后,启动到read only
SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database open read only;
Database altered.
4.执行查询
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 10)
ORA-01110: data file 8: 'D:/ORACLE/ORADATA/TEST/TEST01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
可以看到,主库的nologging操作不会无法传递到备库来,这样就会造成主备库的数据不一致,丢失了数据。
三、处理方法
在主库上,将受nologging操作影响的datafile做个热备,然后copy到备库覆盖原来的,再重新应用日志
1.查找受nologging操作影响的datafile
SQL> select a.file_name,a.tablespace_name,b.unrecoverable_change# from dba_data_
files a,v$datafile b where a.file_id=b.file#;
FILE_NAME TABLESPACE UNRECOVERABLE_CHANGE#
---------------------------------------- ---------- ---------------------
D:/ORACLE/ORADATA/NING/SYSTEM01.DBF SYSTEM 0
D:/ORACLE/ORADATA/NING/UNDOTBS01.DBF UNDOTBS1 0
D:/ORACLE/ORADATA/NING/CWMLITE01.DBF CWMLITE 0
D:/ORACLE/ORADATA/NING/EXAMPLE01.DBF EXAMPLE 0
D:/ORACLE/ORADATA/NING/INDX01.DBF INDX 0
D:/ORACLE/ORADATA/NING/TOOLS01.DBF TOOLS 0
D:/ORACLE/ORADATA/NING/USERS01.DBF USERS 0
D:/ORACLE/ORADATA/NING/TEST01.DBF TEST 738934
8 rows selected.
2.关闭备库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3.从主库备份受影响的test01.dbf到备库
SQL> alter tablespace test begin backup;
Tablespace altered.
在操作系统中复制文件
SQL> alter tablespace test end backup;
Tablespace altered.
4.归档当前日志
SQL> alter system archive log current;
System altered.
5.启动备库并恢复
注意将上一不归档的日志全部copy到备库的归档目的地,需要end backup操作的日志才能将数据库恢复到一致状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01195: online backup of file 8 needs more recovery to be consistent
ORA-01110: data file 8: 'D:/ORACLE/ORADATA/TEST/TEST01.DBF'
SQL> recover standby database;
ORA-00279: change 738991 generated at 11/22/2006 10:46:44 needed for thread 1
ORA-00289: suggestion : D:/ORACLE/ARCH/TEST/ARC00100.001
ORA-00280: change 738991 for thread 1 is in sequence #100
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 739327 generated at 11/22/2006 10:53:30 needed for thread 1
ORA-00289: suggestion : D:/ORACLE/ARCH/TEST/ARC00101.001
ORA-00280: change 739327 for thread 1 is in sequence #101
ORA-00278: log file 'D:/ORACLE/ARCH/TEST/ARC00100.001' no longer needed for
this recovery
ORA-00308: cannot open archived log 'D:/ORACLE/ARCH/TEST/ARC00101.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database open read only;
Database altered.
6.再在备库执行查询
SQL> select count(1) from test;
COUNT(1)
----------
1000
四、将主库置于force logging模式
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
最新文章
- 1ms引发的问题
- Android进程整理
- VS2013 ViewData ViewBag Ajax等关键词报错(当前上下文不存在名称)而且不提示也点不出来,但是可以正常运行,
- 《BI那点儿事》Microsoft 时序算法——验证神奇的斐波那契数列
- 使用nginx lua实现网站统计中的数据收集
- Oracle数据库表的备份和数据表的删除操作
- IOS-day01_OC中类的创建以及使用
- MVC——母版与分部
- java.util 中的property
- HDU4496(并查集)
- centos7.2下编译安装apache2.4
- 码农的奋斗之路 CTO说 读后感
- linux下搭建lamp环境以及安装swoole扩展
- VsVim - Shortcut Key (快捷键)
- [daily][cgroup] 使用cgroup限制进程的CPU占用
- dockerfile debian 和pip使用国内源
- js 只允许输入数字
- Android studio快捷键设置
- HDU 2009
- 新式类 VS 经典类
热门文章
- 第一个 IronPython 的 ASP.NET 程序
- UILabel UiButton 文字下面加下划线
- GPIO编程1:用文件IO的方式操作GPIO
- Myeclipse如何使用Maven添加jar包
- ueditor1.4.3jsp版在上传图片报";未找到上传文件";解决方案
- Java探索之旅(18)——多线程(2)
- nfs(Network FileSystem)的简单配置
- 10、Perl5中19个最重要的文件系统工具
- PAM认证
- cookie 、Session 和自定义分页