【聽海日誌】之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;