原主備數(shù)據(jù)庫中的聯(lián)機(jī)重做日志有3組備重做日志有4組,現(xiàn)在各增加一組
主庫操作
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
1ONLINE /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
1ONLINE /u01/app/oracle/oradata/jytest/redo01.log
SQL>
添加standby redo
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE 50 M;
Databasealtered
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
1ONLINE /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， 它表示已經(jīng)完成規(guī)定的，可以刪除。
但要記住必須要保留兩組聯(lián)機(jī)重做日志組
SQL>alter database drop logfile group 1;
Databasealtered
SQL>
手工的把物理文件刪除后，在創(chuàng)建：
SQL>ALTER DATABASE ADD LOGFILE GROUP 1('/u01/app/oracle/oradata/jytest/redo01.log')SIZE 50 M;
Databasealtered
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;
Databasealtered
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;
Systemaltered
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;
Databasealtered
SQL>
手工的把物理文件刪除后，在創(chuàng)建：
SQL>ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE50 M;
Databasealtered
SQL>ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE50 M;
Databasealtered
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
主數(shù)據(jù)庫的日志文件增加與刪除操作就完成了
備庫操作
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
1ONLINE /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
11rows 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;
ALTERDATABASE 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 ARCHIVEDSTATUS 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;
Databasealtered
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;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE 50 M;
Databasealtered
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE 50 M;
Databasealtered
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
1ONLINE /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 rowsselected
2.3 處理online redo
先將standby_file_management設(shè)為手動(dòng)：
SQL> alter system setstandby_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')SIZE50 M;
Databasealtered
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>
還有最后一個(gè)redo 組沒有處理，這個(gè)要先切換過來：
（1）在備庫啟動(dòng)recover 進(jìn)程：
SQL>alter database recover managed standby database disconnect from session;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
Systemaltered.
SQL>
（2）到主庫手動(dòng)切換幾次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已經(jīng)變成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
1ONLINE /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
4ONLINE /u01/app/oracle/oradata/jytest/redo04.log
9 rows selected
搞定，最后啟動(dòng)recover，驗(yàn)證：
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
System altered.
SQL>alter database recover managed standby database disconnect from session;
Databasealtered.
主庫：
SQL> alter system switch logfile;
System altered.
SQL> alter system switchlogfile;
System altered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
185
備庫：
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
185
同步了
