Oracle物理DataGuard:增加或删除联机日志文件

Changing the size and number of the online redo log files is sometimes done to tune
the database. You can add or drop online redo log file groups or
members to the primary database without affecting the standby database.
Similarly, you can drop log file groups or members from the primary
database without affecting your standby database. However, these changes
do affect the performance of the standby database after switchover.

For example, if the primary database has 10 online redo log files and
the standby database has 2, and then you switch over to the standby
database so that it functions as the new primary database, the new
primary database is forced to archive more frequently than the original
primary database.

Consequently, when you add or drop an online redo log file at the
primary site, it is important that you synchronize the changes in the
standby database by following these steps:

1. 
   

   If Redo Apply is running, you must cancel Redo Apply before you can change the log files.

   
   
2. 
   

   If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.

   
   
3. 
   

   Add or drop an online redo log file:

   
   
   • 
     

     To add an online redo log file, use a SQL statement such as this:

     
     

     SQL> ALTER DATABASE ADD LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log' SIZE 100M;
     

   • 
     

     To drop an online redo log file, use a SQL statement such as this:

     
     

     SQL> ALTER DATABASE DROP LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log';
     

   
   
4. 
   

   Repeat the statement you used in Step 3 on each standby database.

   
   
5. 
   

   Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.

At the primary site

=============

1. Set STANDBY_FILE_MANAGEMENT to MANUAL.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

2. Add the new redo logfile groups of the required sizes.
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('\\U01\\oracle\\modg1\\redo04.log') SIZE 50M;

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 2;

4. Set STANDBY_FILE_MANAGEMENT to AUTO.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

At the standby site

=============
1. Stop Redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Set STANDBY_FILE_MANAGEMENT to MANUAL.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

3. Add Redolog File Group:
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('\\U01\\oracle\\modg1\\redo04.log') SIZE 50M;

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

4. Set STANDBY_FILE_MANAGEMENT to AUTO.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

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.

6. Clear the Online Redo Logfile Group:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

7. Drop the Online Redo Logfile Group:
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

If you have skipped Step 4 then you will get ORA-01624 while droping the Online Redolog Group with Status CLEARING.

8. Set STANDBY_FILE_MANAGEMENT to AUTO.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

9. Start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;