Data Guard 環境下 主備庫Redo log 的新增與刪除
Data Guard 環境下 主備庫Redo log 的新增與刪除
剛搭建完一個Data Gard 環境。 在伺服器上弄的，過幾天要上生產線。 安裝的時候redo 預設了50M。 而且standby redo 也是50M。 和同事討論之後，還是把改成100M。50M 確實小了點。
Standby redo的大小要和redo 的一致，所以主備庫都要調整。
一. 主庫操作
1.1 檢視redo 資訊
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/redo07.log
7 rows selected.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
1.2 修改standby redo
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
新增standby redo
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/xezf/std_redo04.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/xezf/std_redo05.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/xezf/std_redo06.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/xezf/std_redo07.log') size 100M;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/std_redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/std_redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/std_redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/std_redo07.log
7 rows selected.
1.3 修改Online redo
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
先處理inactive， 它表示已經完成規定的，可以刪除。
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance xezf
(thread 1)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/xezf/redo03.log'
-- 至少要2個redo組，看來還是隻能慢慢來了。
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/app/oracle/oradata/xezf/redo01.log' -
file cannot be created
ORA-27038: created file already exists
Additional information: 1
-- 物理檔案沒有刪除，手工的把物理檔案刪除後，在建立：
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
group1 搞定了。
SQL> alter database drop logfile group 3;
Database altered.
刪除對應的物理檔案，在新增
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/xezf/redo03.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
2 1 NO CURRENT 50
3 1 YES UNUSED 100
group3 搞定。
切換一下logfile，在刪除group2
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES ACTIVE 50
-- group 正在歸檔，我們等會在看一下
3 1 YES UNUSED 100
幾分鐘之後：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES INACTIVE 50
3 1 YES UNUSED 100
SQL> alter database drop logfile group 2;
Database altered.
刪除物理檔案，在建立
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/xezf/redo02.log') size 100M;
Database altered.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 NO CURRENT 100
2 1 YES UNUSED 100
3 1 YES UNUSED 100
主庫搞定。
二. 備庫操作
2.1 檢視資訊
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/redo07.log
7 rows selected.
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES CLEARING_CURRENT 50
3 1 YES CLEARING 50
2 1 YES CLEARING 50
2.2 處理standby redo
對於standby 上redo的處理之前，我們要先停掉redo 的apply：
SQL> alter database recover managed standby database cancel;
不然會報如下錯誤：
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
[oracle@qs-xezf-db2 xezf]$ rm redo04.log
[oracle@qs-xezf-db2 xezf]$ rm redo05.log
[oracle@qs-xezf-db2 xezf]$ rm redo06.log
[oracle@qs-xezf-db2 xezf]$ rm redo07.log
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/xezf/std_redo04.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/xezf/std_redo05.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/xezf/std_redo06.log') size 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/xezf/std_redo07.log') size 100M;
Database altered.
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/xezf/redo03.log
2 ONLINE /u01/app/oracle/oradata/xezf/redo02.log
1 ONLINE /u01/app/oracle/oradata/xezf/redo01.log
4 STANDBY /u01/app/oracle/oradata/xezf/std_redo04.log
5 STANDBY /u01/app/oracle/oradata/xezf/std_redo05.log
6 STANDBY /u01/app/oracle/oradata/xezf/std_redo06.log
7 STANDBY /u01/app/oracle/oradata/xezf/std_redo07.log
7 rows selected.
2.3 處理online redo
先將standby_file_management設為手動：
SQL> alter system set standby_file_management=manual;
System altered.
不然會報錯：
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.
在Oracle 官網上搜了一下，找到了一篇文章：
How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/08/6063677.aspx
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
SQL>
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/xezf/redo02.log'
ORA-19527: |
physical standby redo log must be renamed |
|
Cause: |
The CLEAR LOGFILE command was used at a physical standby database. This command cannot be used at a physical standby database unless the LOG_FILE_NAME_CONVERT initialization parameter is set. This is required to avoid overwriting the primary database's logfiles. |
|
Action: |
Set the LOG_FILE_NAME_CONVERT initialization parameter. |
我們需要設定LOG_FILE_NAME_CONVERT 引數，才能使用clear logfile命令。 參考：
ORA-00313, ORA-00312, ORA-27037 in Standby Database [ID 601835.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/08/6063749.aspx
解決方法如下：
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
在pfile裡新增引數：
語法：*.log_file_name_convert = '
*.log_file_name_convert ='/u01/app/oracle/oradata/xezf/','/u01/app/oracle/oradata/xezf/'
用pfile 啟動備庫：
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initxezf.ora';
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
3 CLEARING
2 CLEARING_CURRENT
SQL> show parameter log_file_name_convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string /u01/app/oracle/oradata/xezf, /u01/app/oracle/oradata/xezf/
SQL> alter database clear logfile group 1;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
System altered.
SQL> alter database drop logfile group 1;
Database altered.
刪除物理檔案：
[oracle@qs-xezf-db2 xezf]$ rm redo01.log
建立新的日誌組：
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING
2 CLEARING_CURRENT
處理下一個redo 日誌：
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
刪除物理檔案：
[oracle@qs-xezf-db2 xezf]$ rm redo03.log
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/xezf/redo03.log') size 100M;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 UNUSED
2 CLEARING_CURRENT
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
3 1 YES UNUSED 100
2 1 YES CLEARING_CURRENT 50
還有最後一個redo 組沒有處理，這個要先切換過來：
（1）在備庫啟動recover 程式：
SQL> alter database recover managed standby database disconnect from session;
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
（2）到主庫手動切換幾次redo
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
在檢視備庫的redo：
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING_CURRENT
2 CLEARING
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
物理刪除檔案：
[oracle@qs-xezf-db2 xezf]$ rm redo02.log
新增redo：
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/xezf/redo02.log') size 100M;
Database altered.
檢視：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARC STATUS BYTES/1024/1024
---------- ---------- --- ---------------- ---------------
1 1 YES UNUSED 100
3 1 YES CLEARING_CURRENT 100
2 1 YES UNUSED 100
搞定，最後啟動recover，驗證：
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
System altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主庫：
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15
備庫：
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
2 YES
3 YES
5 YES
4 YES
7 YES
6 YES
8 YES
9 YES
13 YES
10 YES
11 YES
SEQUENCE# APP
---------- ---
12 YES
14 YES
15 YES
14 rows selected.
同步正常。 ok。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27042095/viewspace-750938/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 主庫新增redo log或刪除redo log，這些資訊不會同步到備庫
- 主備庫記憶體不一致的Data Guard環境搭建記憶體
- Oracle Data Guard 主庫歸檔檔案刪除策略Oracle
- Oracle Data Guard 主庫 歸檔檔案 刪除策略Oracle
- Data Guard主備庫切換
- Oracle Data Guard 主庫 歸檔檔案 刪除策略--續Oracle
- data guard中增加與刪除主備資料庫中的聯機重做日誌與備重做日誌檔案資料庫
- ubuntu環境變數的新增與刪除Ubuntu變數
- Ubuntu環境變數——新增與刪除Ubuntu變數
- Dataguard環境修改主庫和standby庫online redo log&standby redo log大小
- 【轉載】Oracle Data Guard 主庫 歸檔檔案 刪除策略Oracle
- 邏輯Data Guard主備庫的轉換
- 【DG】Data Guard主備庫Switchover切換
- 【轉載】Oracle Data Guard 備庫 歸檔檔案 刪除指令碼Oracle指令碼
- Oracle Data Guard主庫備庫角色切換（Switchovers）Oracle
- 【DG】Data Guard主備庫Failove切換AI
- Oracle10g RAC環境OCR的新增、刪除、備份Oracle
- Oracle10g RAC環境VoteDisk的新增、刪除、備份Oracle
- 搭建Active Data Guard環境
- Ubuntu新增刪除環境變數Ubuntu變數
- CentOS新增、刪除環境變數CentOS變數
- linux下export命令新增刪除環境變數LinuxExport變數
- Windows環境下的Oracle Data Guard安裝和配置WindowsOracle
- Oracle RAC + Data Guard 環境搭建Oracle
- 刪除data guard歸檔日誌
- Windows環境下刪除MySQLWindowsMySql
- redo log全部刪除後，啟動資料庫資料庫
- Linux 新增環境變數和刪除環境變數Linux變數
- linux下export命令新增、刪除環境變數（轉載）LinuxExport變數
- ubuntu 快捷新增和刪除環境變數Ubuntu變數
- DATA GUARD物理備庫的SWITCHOVER切換
- linux環境變數顯示、新增、刪除Linux變數
- redo log 丟失（非歸檔模式,資料庫正常關閉，redo log 被誤刪除！）模式資料庫
- ORACLE RAC環境下刪除節點Oracle
- Data Guard 主庫建立表空間，備庫MRP無法啟動
- Redo log檔案被刪除恢復
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- linux下rac環境下刪除節點Linux