add Redo logs/dorp redo logs in Physical Standby
1. Add Redologs on Standby
2. Drop Redologs on Standby
Add Redo Logs on Standby
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\stdby10g\redo04.log') SIZE 100M;
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. Start Redo Apply (Managed Recovery):
1. Check the Status of the Online Redolog Group.
Even if the online redo logs are not used on a physical standby database, the status within v$log will be updated through the standby control file. They are results of actions on the primary database eg. logswitches.
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
If Status is CLEARING_CURRENT or CURRENT then you cannot drop Online Redolog Group, please use "Sync Redo Logs on Primary and Standby" Section in this Case. You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT or CURRENT.
You can also change this status by doing a ALTER SYSTEM SWITCH LOGFILE; on primary.
For Status CLEARING, UNUSED, INACTIVE please follow below steps.
2. Stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Set STANDBY_FILE_MANAGEMENT to MANUAL.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
4. Clear the Online Redo Logfile Group:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
5. 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.
6. Set STANDBY_FILE_MANAGEMENT to AUTO.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
7. Start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
1. Add Redologs on Standby
2. Drop Redologs on Standby
Add Redo Logs on Standby
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\stdby10g\redo04.log') SIZE 100M;
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. Start Redo Apply (Managed Recovery):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
1. Check the Status of the Online Redolog Group.
Even if the online redo logs are not used on a physical standby database, the status within v$log will be updated through the standby control file. They are results of actions on the primary database eg. logswitches.
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
If Status is CLEARING_CURRENT or CURRENT then you cannot drop Online Redolog Group, please use "Sync Redo Logs on Primary and Standby" Section in this Case. You will get ORA-01623 if you try to drop a Redolog Group with Status CLEARING_CURRENT or CURRENT.
You can also change this status by doing a ALTER SYSTEM SWITCH LOGFILE; on primary.
For Status CLEARING, UNUSED, INACTIVE please follow below steps.
2. Stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Set STANDBY_FILE_MANAGEMENT to MANUAL.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
4. Clear the Online Redo Logfile Group:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
5. 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.
6. Set STANDBY_FILE_MANAGEMENT to AUTO.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
7. Start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;