Data Guard 环境下主备库Redo log 的添加与删除
来源：互联网 发布：sem seo怎么做 编辑：程序博客网 时间：2024/05/29 02:37
原创：http://blog.csdn.net/tianlesoftware/article/details/6064121
Standby redo的大小要和redo 的一致，所以主备库都要调整。
一. 主库操作
1.1 查看redo信息
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/redo07.log
7 rows selected.
SQL> select group#,thread#,archived,status, bytes/1024/1024 M from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
1.2 修改standby redo
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
#rm -rf redo04.log redo05.log redo06.log redo07.log
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
添加standby redo
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/xezf/std_redo04.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/xezf/std_redo05.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/xezf/std_redo06.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/xezf/std_redo07.log') size 100M;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/std_redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/std_redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/std_redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/std_redo07.log
7 rows selected.
1.3 修改Online redo
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
先处理inactive，它表示已经完成规定的，可以删除。
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance xezf
(thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/xezf/redo03.log'
-- 至少要2个redo组，看来还是只能慢慢来了。
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/app/oracle/oradata/xezf/redo01.log' -
file cannot be created
ORA-27038: created file already exists
Additional information: 1
-- 物理文件没有删除，手工的把物理文件删除后，在创建：
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
group1 搞定了。
SQL> alter database drop logfile group 3;
Database altered.
删除对应的物理文件，在添加
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/xezf/redo03.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
2 1 NO CURRENT 50
3 1 YES UNUSED 100
group3 搞定。
切换一下logfile，在删除group2
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES ACTIVE 50
-- group 正在归档，我们等会在看一下
3 1 YES UNUSED 100
几分钟之后：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES INACTIVE 50
3 1 YES UNUSED 100
SQL> alter database drop logfile group 2;
Database altered.
删除物理文件，在创建
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/xezf/redo02.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES UNUSED 100
3 1 YES UNUSED 100
主库搞定。
二. 备库操作
2.1 查看信息
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/redo07.log
7 rows selected.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES CLEARING_CURRENT 50
3 1 YES CLEARING 50
2 1 YES CLEARING 50
2.2 处理standby redo
对于standby 上redo的处理之前，我们要先停掉redo 的apply：
SQL> alter database recover managed standby database cancel;
不然会报如下错误：
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
$ rm redo04.log
$ rm redo05.log
$ rm redo06.log
$ rm redo07.log
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/xezf/std_redo04.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/xezf/std_redo05.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/xezf/std_redo06.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/xezf/std_redo07.log') size 100M;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/std_redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/std_redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/std_redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/std_redo07.log
7 rows selected.
2.3 处理online redo
先将standby_file_management设为手动：
SQL> alter system set standby_file_management=manual;
System altered.
不然会报错：
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.
在Oracle 官网上搜了一下，找到了一篇文章：
How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/08/6063677.aspx
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
SQL>
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/xezf/redo02.log'
ORA-19527:
physical standby redo log must be renamed
Cause:
The CLEAR LOGFILE command was used at a physical standby database. This command cannot be used at a physical standby database unless the LOG_FILE_NAME_CONVERT initialization parameter is set. This is required to avoid overwriting the primary database's logfiles.
Action:
Set the LOG_FILE_NAME_CONVERT initialization parameter.
我们需要设置LOG_FILE_NAME_CONVERT参数，才能使用clear logfile命令。 参考：
ORA-00313, ORA-00312, ORA-27037 in Standby Database [ID 601835.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/08/6063749.aspx
解决方法如下：
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
在pfile里添加参数：
语法：*.log_file_name_convert = '<location on primary>','<location on standby>'
*.log_file_name_convert ='/u01/app/oracle/oradata/xezf/','/u01/app/oracle/oradata/xezf/'
用pfile启动备库：
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initxezf.ora';
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
3 CLEARING
2 CLEARING_CURRENT
SQL> show parameter log_file_name_convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string /u01/app/oracle/oradata/xezf, /u01/app/oracle/oradata/xezf/
SQL> alter database clear logfile group 1;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
System altered.
SQL> alter database drop logfile group 1;
Database altered.
删除物理文件：
$ rm redo01.log
创建新的日志组：
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING
2 CLEARING_CURRENT
处理下一个redo日志：
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
删除物理文件：
$ rm redo03.log
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/xezf/redo03.log') size 100M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 UNUSED
2 CLEARING_CURRENT
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
3 1 YES UNUSED 100
2 1 YES CLEARING_CURRENT 50
还有最后一个redo组没有处理，这个要先切换过来：
（1）在备库启动recover进程：
SQL> alter database recover managed standby database disconnect from session;
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
（2）到主库手动切换几次redo
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
在查看备库的redo：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING_CURRENT
2 CLEARING
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
物理删除文件：
[oracle@qs-xezf-db2 xezf]$ rm redo02.log
添加redo：
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/xezf/redo02.log') size 100M;
Database altered.
查看：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
3 1 YES CLEARING_CURRENT 100
2 1 YES UNUSED 100
搞定，最后启动recover，验证：
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
System altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主库：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15
备库：
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
5 YES
4 YES
7 YES
6 YES
8 YES
9 YES
13 YES
10 YES
11 YES
SEQUENCE# APP
---------- ---
12 YES
14 YES
15 YES
14 rows selected.
同步正常。 ok。
- Data Guard 环境下主备库Redo log 的添加与删除
- Data Guard 环境下 主备库Redo log 的添加与删除
- Data Guard 环境下 主备库Redo log 的添加与删除
- Data Guard 环境下 主备库Redo log 的添加与删除
- Data Guard 配置 Standby Redo Log
- 物理Data Guard 下Failover 时Redo 的处理问题
- 物理Data Guard 下Failover 时Redo 的处理问题
- 物理Data Guard 下Failover 时Redo 的处理
- 物理Data Guard 下Failover 时Redo 的处理问题~
- 物理Data Guard中处理Redo GAP的进程
- 高可用之4——Data Guard 配置Standby Redo Log
- REDO LOG 与 UNDO LOG的区别
- 恢复误删除的redo log
- Oracle 11g Data Guard Redo应用服务
- Monitor Data Guard Log Shipping
- Redo Log Data Dictionary Views
- 关于redo log的修改与重建
- Innodb redo log 与 binlog 的区别
- 【7gyy】小编分享全部的windows7系统运行命令
- POJ 1631 / HDU 1950 / ZOJ 1986 / Northwestern Europe 2003 Bridging signals (DP&LIS)
- 单链表之插入删除结点
- 互斥对象、事件对象、关键代码段的应用和比较
- 实用的开放接口收集
- Data Guard 环境下主备库Redo log 的添加与删除
- 对SendMessage与PostMessage的理解
- Mysql存储过程（二）——多结果集返回与java获取
- Java 计算倒计时
- 黑白图像（DFS）
- jsp web.xml文件的作用及基本配置
- 使用openssl库EVP编程计算sha1值
- FreeMicaps更新（2013.7.30）：FreeMicaps V0.3.0
- 客户信息系统开发小结
