data guard中增加與刪除主備資料庫中的聯機重做日誌與備重做日誌檔案
原主備資料庫中的聯機重做日誌有3組備重做日誌有4組,現在各增加一組
主庫操作
1.1 檢視redo 資訊
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
4 STANDBY /u01/app/oracle/oradata/jytest/redo04a.log
4 STANDBY /u01/app/oracle/oradata/jytest/redo04b.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05a.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05b.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06a.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06b.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07a.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07b.log
11 rows selecte
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
SQL>
1.2 修改standby redo
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;
Database altered
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 5;
Database altered
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 6;
Database altered
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 7;
Database altered
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
SQL>
新增standby redo
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE 50 M;
Database altered
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log
8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log
9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log
8 rows selected
SQL>
1.3 修改Online redo
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
SQL>
先處理inactive， 它表示已經完成規定的，可以刪除。
但要記住必須要保留兩組聯機重做日誌組
SQL> alter database drop logfile group 1;
Database altered
SQL>
手工的把物理檔案刪除後，在建立：
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/jytest/redo01.log')SIZE 50 M;
Database altered
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES UNUSED 50
2 1 NO CURRENT 50
3 1 YES INACTIVE 50
SQL>
group1 搞定了。
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/jytest/redo03.log')SIZE 50 M;
Database altered
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES UNUSED 50
2 1 NO CURRENT 50
3 1 YES UNUSED 50
SQL>
Group3 搞定了。
切換一下logfile，在刪除group2
SQL> alter system switch logfile;
System altered
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 NO CURRENT 50
2 1 YES ACTIVE 50
3 1 YES UNUSED 50
SQL>
上面group2正在歸檔
幾分鐘之後：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 NO CURRENT 50
2 1 YES INACTIVE 50
3 1 YES UNUSED 50
SQL>
SQL> alter database drop logfile group 2;
Database altered
SQL>
手工的把物理檔案刪除後，在建立：
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE 50 M;
Database altered
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES INACTIVE 50
2 1 YES INACTIVE 50
3 1 NO CURRENT 50
4 1 YES UNUSED 50
主資料庫的日誌檔案增加與刪除操作就完成了
備庫操作
2.1 檢視日誌資訊
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
4 STANDBY /u01/app/oracle/oradata/jytest/redo04a.log
4 STANDBY /u01/app/oracle/oradata/jytest/redo04b.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05a.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05b.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06a.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06b.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07a.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07b.log
11 rows selected
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES CLEARING 50
3 1 YES CLEARING_CURRENT 50
2 1 YES CLEARING 50
SQL>
2.2 處理standby redo
對於standby 上redo的處理之前，我們要先停掉redo 的apply：
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;
ALTER DATABASE drop STANDBY LOGFILE GROUP 4
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/jytest/redo04a.log'
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/jytest/redo04b.log'
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ -----------
4 3836176504 1 182 52428800 3580928 YES ACTIVE 1236181 2012-12-4 1 1238785 2012-12-4 1
顯示group 4 status為active
SQL> alter database clear logfile group 4;
Database altered
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES USED ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- -------- ---------- ------------- ----------- ------------ -----------
4 UNASSIGNED 1 0 52428800 0 YES UNASSIGNED 1236181 2012-12-4 1 1239074 2012-12-4 1
SQL> ALTER DATABASE drop STANDBY LOGFILE GROUP 4;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE 50 M;
Database altered
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log
8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log
9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log
8 rows selected
2.3 處理online redo
先將standby_file_management設為手動：
SQL> alter system set standby_file_management='MANUAL' ;
System altered.
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING
3 CLEARING_CURRENT
2 CLEARING
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/jytest/redo01.log')SIZE 50 M;
Database altered
SQL> alter database clear logfile group 2;
Database altered
SQL> alter database drop logfile group 2;
Database altered
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE 50 M;
Database altered
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING_CURRENT
2 UNUSED
SQL>
還有最後一個redo 組沒有處理，這個要先切換過來：
（1）在備庫啟動recover 程式：
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
System altered.
SQL>
（2）到主庫手動切換幾次redo
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
在檢視備庫的redo：
SQL> SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 CLEARING
2 CLEARING_CURRENT
原來group3已經變成clearing了
SQL> alter database clear logfile group 3;
Database altered
SQL> alter database drop logfile group 3;
Database altered
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/jytest/redo03.log')SIZE 50 M;
Database altered
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE 50 M;
Database altered
SQL>
檢視：
SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;
GROUP# THREAD# ARCHIVED STATUS BYTES/1024/1024
---------- ---------- -------- ---------------- ---------------
1 1 YES UNUSED 50
4 1 YES UNUSED 50
3 1 YES UNUSED 50
2 1 YES CLEARING_CURRENT 50
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/jytest/redo03.log
2 ONLINE /u01/app/oracle/oradata/jytest/redo02.log
1 ONLINE /u01/app/oracle/oradata/jytest/redo01.log
5 STANDBY /u01/app/oracle/oradata/jytest/redo05.log
6 STANDBY /u01/app/oracle/oradata/jytest/redo06.log
7 STANDBY /u01/app/oracle/oradata/jytest/redo07.log
8 STANDBY /u01/app/oracle/oradata/jytest/redo08.log
9 STANDBY /u01/app/oracle/oradata/jytest/redo09.log
4 ONLINE /u01/app/oracle/oradata/jytest/redo04.log
9 rows selected
搞定，最後啟動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#)
--------------
185
備庫：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
185
同步了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2129577/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 聯機重做日誌、歸檔日誌、備用重做日誌
- oracle 聯機重做日誌檔案Oracle
- 重做日誌檔案中的SCN
- 【備份與恢復】重建受損的聯機重做日誌檔案成員
- 【REDO】刪除聯機重做日誌檔案組的注意事項
- Oracle11g redo log 建立、新增、刪除（重做日誌組，重做日誌檔案）Oracle
- 聯機重做日誌檔案的恢復
- 備份之歸檔重做日誌備份
- 【備份與恢復】恢復受損的複用聯機重做日誌檔案
- noarchivelog模式下冷備時沒有備份聯機重做日誌檔案Hive模式
- 【REDO】刪除聯機重做日誌檔案組成員的注意事項
- 刪除data guard歸檔日誌
- 【REDO】刪除REDO LOG重做日誌組後需要手工刪除對應的日誌檔案
- 丟失聯機重做日誌檔案的恢復
- Backup And Recovery User's Guide-備份資料庫-備份歸檔重做日誌檔案GUIIDE資料庫
- 重做日誌
- 一個刪除重做日誌檔案的參考指令碼指令碼
- Backup And Recovery User's Guide-備份後刪除歸檔重做日誌GUIIDE
- Backup And Recovery User's Guide-備份資料庫-聯機重做日誌切換GUIIDE資料庫
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- Oracle資料庫重做日誌及歸檔日誌的工作原理說明Oracle資料庫
- Oracle 聯機重做日誌檔案(ONLINE LOG FILE)Oracle
- Oracle重做日誌檔案基礎Oracle
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌- 多路複用聯機重做日誌Oracle
- 線上修改重做日誌檔案的大小
- 修改Oracle重做日誌檔案的大小Oracle
- 重做日誌檔案的相關操作
- 重做日誌管理
- 聯機重做日誌丟失的恢復
- 刪除日誌檔案組與日誌檔案成員
- Oracle的重做日誌Oracle
- Oracle歸檔日誌比聯機重做日誌小很多的情況總結Oracle
- 刪除重做日誌檔案組的四大限制條件
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份歸檔重做日誌GUIIDE資料庫
- 重做日誌檔案損壞測試
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-練習：多路複用聯機重做日誌Oracle
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- Backup And Recovery User's Guide-備份歸檔重做日誌檔案GUIIDE