|
原主备数据库中的联机重做日志有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
同步了 |
|
|
来自： 浸心阁 > 《主备库联机重做日志管理》
