注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

主备库Redo log 的添加与删除(转)  

2011-03-28 12:51:05|  分类: Data Guard |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 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;
SQL> alter database recover managed standby database cancel;

System altered.

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

 

    GROUP# STATUS

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

         1 CLEARING_CURRENT

         3 CLEARING

         2 CLEARING

添加如下的参数

 

SQL> alter database clear logfile group 1;
Database 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;
SQL> alter database drop logfile group 2;
物理删除文件:
[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;
查看:

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';
SQL> alter database recover managed standby database disconnect from session;
主库:

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
        12 YES
        14 YES
        15 YES
同步正常。 ok。


实例操作:
date -s "17:30:00 2011-03-25"

SELECT GROUP#, STATUS FROM V$LOG;

alter system set standby_file_management=manual;
alter database recover managed standby database cancel;


alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 4;
alter database add logfile  group 1 ('/u01/app/oracle/oradata/blade2/redo01.log','/u01/app/oracle/oradata/blade2/redo01_02.log') size 50M;
alter database add logfile  group 2 ('/u01/app/oracle/oradata/blade2/redo02.log','/u01/app/oracle/oradata/blade2/redo02_02.log') size 50M;
alter database add logfile  group 4 ('/u01/app/oracle/oradata/blade2/redo04.log','/u01/app/oracle/oradata/blade2/redo04_02.log') size 50M;

alter system set standby_file_management=auto;
alter database recover managed standby database disconnect from session;

主库切换日志,直到status变成inactive

alter system set standby_file_management=auto;
alter database recover managed standby database disconnect from session;


alter database drop logfile group 3;
alter database drop logfile group 5;

alter database add logfile  group 3 ('/u01/app/oracle/oradata/blade2/redo03.log','/u01/app/oracle/oradata/blade2/redo03_02.log') size 50M;
alter database add logfile  group 5 ('/u01/app/oracle/oradata/blade2/redo05.log','/u01/app/oracle/oradata/blade2/redo05_02.log') size 50M;

  评论这张
 
阅读(396)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018