Dataguard 增加redo log方法

最近在整理数据库发现dataguard主库的在线日志切换非常频繁，检查发现是因为建库的时候把redo log设置的太小造成的，为了使日志切换不太频繁，所以手工添加了大的日志文件，然后删除了旧的日志文件，主要流程如下：

一、At the primary site
1.1 设置STANDBY_FILE_MANAGEMENT为手工管理

Set STANDBY_FILE_MANAGEMENT to MANUAL:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

1.2 增加新日志组

Add the new redo logfile groups of the required sizes:

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/opt/oracle/oradata/dbserver1/redo01.log') SIZE 512M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/opt/oracle/oradata/dbserver1/redo02.log') SIZE 512M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/opt/oracle/oradata/dbserver1/redo03.log') SIZE 512M;

1.3 删除旧日志组

Drop the old groups, this may involve performing log switches to ensure the old logs have been archived correctly or could be performed later once oracle has finished with the logs.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

1.4 重新设置STANDBY_FILE_MANAGEMENT为自动管理

Set STANDBY_FILE_MANAGEMENT to AUTO.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

二、At the standby site
2.1 停止redo apply service

Stop Redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.2 修改STANDBY_FILE_MANAGEMENT为手工管理

Set STANDBY_FILE_MANAGEMENT to MANUAL:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

2.3 增加日志组

Add Redolog File Group:

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/home/oracle/oracle/oradata/dbserver1/redo04.log') SIZE 50M;

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 SIZE 50M;  

注：如果不指定文件名，系统会默认生成一个日志组成员，名字系统自己生成，以后自己添加的日志组成员也不能指定名字，否则会显示无效。所以DG中最好用第一种方法添加日志组。

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

SQL> Add as many Redo Logfile Groups (or Members) you want to add.

2.4 修改STANDBY_FILE_MANAGEMENT为自动管理

Set STANDBY_FILE_MANAGEMENT to AUTO :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

2.5 检查在线日志组

Check the Status of the Online Redolog Group:

SQL> SELECT GROUP#, STATUS FROM V$LOG;

GROUP# STATUS

---------- ----------------

1 CLEARING_CURRENT

3 CLEARING

2 CLEARING

If Status is CLEARING_CURRENT then you cannot drop Online Redolog Group. You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT.

For Status CLEARING, UNUSED, INACTIVE please follow below steps.

2.6 清理在线日志组

Clear the Online Redo Logfile Group:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

2.7 删除在线日志组

Drop the Online Redo Logfile Group:

SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

If you have skipped ‘Set STANDBY_FILE_MANAGEMENT to AUTO’ then you will get ORA-01624 while droping the Online Redolog Group with Status CLEARING. Then set STANDBY_FILE_MANAGEMENT to AUTO:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

2.8 开启redo apply service

Start Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;