How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]
| Modified 12-AUG-2009 Type HOWTO Status REVIEWED |
|
In this Document
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5
Information in this document applies to any platform.
Goal
To Add/Drop/Resize online redo logs in the primary site with physical standby database in place. What are the steps to be done on standby if any?
Solution
Changing the size of redo involves adding the new sized log and dropping the unwanted old log. There is no resize command as such. 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 a role transition. So it is best advised whenever you add an online redo log file to the primary database, you should add corresponding online and standby redo
log files to the standby database.
To resize redo logs the steps are as follows:
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;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15747463/viewspace-758229/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How to Dump Redo Log File Information --metalinkORM
- Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)
- Oracle RAC+DG 調整redo/standby log fileOracle
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- Performing a Failover to a Physical Standby DatabaseORMAIDatabase
- 【DG】Data Guard搭建(physical standby)
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- undo log和redo log
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- mysql之 redo logMySql
- MySQL的Redo log 以及Bin logMySql
- MySQL Undo Log和Redo Log介紹MySql
- MySQL中的redo log和undo logMySql
- How can I add a site title refiner
- ORA-01623: log is current log for thread - cannot dropthread
- 【Mysql】三大日誌 redo log、bin log、undo logMySql
- Centos/RHEL :How to add,delete and display LVM tagsCentOSdeleteLVM
- How to add sub-repo as sub-module
- redo log file 最佳化
- MySQL redo log最佳化MySql
- MySQL重做日誌(redo log)MySql
- 搭建windows到linux的oracle 12c physical standby備庫WindowsLinuxOracle
- How To Turn SNMP On/Off ? [ID 472530.1]
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- Oracle redo解析之-1、oracle redo log結構計算Oracle Redo
- How to Add a New Disk new partition in centos7CentOS
- [Laragon Tutorial] How to add another PHP version (PHP 8.3.4)GoPHP
- InnoDB文件筆記(二)—— Redo Log筆記
- MySQL中的redo log和checkpointMySql
- mysql日誌:redo log、binlog、undo log 區別與作用MySql
- MySQL中redo log、undo log、binlog關係以及區別MySql
- 硬核乾貨!一文掌握 binlog 、redo log、undo log
- 深入理解MySQL系列之redo log、undo log和binlogMySql
- How to add the cracked dlls in GAC---let me tell u
- MySQL 日誌系統 redo log、binlogMySql
- MySQL學習之change buffer 和 redo logMySql
- MySQL如何計算統計redo log大小MySql
- 基於Redo Log和Undo Log的MySQL崩潰恢復流程MySql
- What is OPAQUE_TRANSFORM Hint and how to Control it [ID 780503.1]OpaqueORM