data guard中增加与删除主备数据库中的联机重做日志与备重做日志文件

Posted on by YongJing(敬勇)

原主备数据库中的联机重做日志有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 selected

 

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

添加standby redo

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/jytest/redo05.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE50 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

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

先处理inactive， 它表示已经完成规定的，可以删除。

但要记住必须要保留两组联机重做日志组

SQL> alter database drop logfile group 1;

Database altered

手工的把物理文件删除后，在创建：

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/jytest/redo01.log')SIZE50 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

group1 搞定了。

SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/jytest/redo03.log')SIZE50 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

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

上面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> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/jytest/redo02.log')SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE50 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

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') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('/u01/app/oracle/oradata/jytest/redo06.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/jytest/redo07.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('/u01/app/oracle/oradata/jytest/redo08.log') SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9('/u01/app/oracle/oradata/jytest/redo09.log') SIZE50 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')SIZE50 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')SIZE50 M;

Database altered

SQL> SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         3 CLEARING_CURRENT

         2 UNUSED

还有最后一个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.

（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')SIZE50 M;

Database altered

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/jytest/redo04.log')SIZE50 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

         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

同步了

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.