oracle备库无法创建主库redo,Data Guard 环境下 主备库Redo log 的添加与删除

Standby redo的大小要和redo的一致,所以主备库都要调整。

一.主库操作

1.1查看redo信息

SQL> select group#,type, member from v$logfile;

GROUP# TYPEMEMBER

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

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 STATUSBYTES/1024/1024

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

11 YES INACTIVE50

21 NOCURRENT50

31 YES INACTIVE50

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# TYPEMEMBER

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

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# TYPEMEMBER

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

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 STATUSBYTES/1024/1024

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

11 YES INACTIVE50

21 NOCURRENT50

31 YES INACTIVE50

先处理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 logfilegroup 1 ('/u01/app/oracle/oradata/xezf/redo01.log') size 100M;

alter database add logfilegroup 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 logfilegroup 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 STATUSBYTES/1024/1024

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

11 YES UNUSED100

21 NOCURRENT50

31 YES INACTIVE50

group1搞定了。

SQL> alter database drop logfile group 3;

Database altered.

删除对应的物理文件,在添加

SQL> alter database add logfilegroup 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 STATUSBYTES/1024/1024

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

11 YES UNUSED100

21 NOCURRENT50

31 YES UNUSED100

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 STATUSBYTES/1024/1024

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

11 NOCURRENT100

21 YESACTIVE50

-- group正在归档,我们等会在看一下

31 YES UNUSED100

几分钟之后:

SQL> select group#,thread#,archived,status, bytes/1024/1024 from v$log;

GROUP#THREAD# ARC STATUSBYTES/1024/1024

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

11 NOCURRENT100

21 YES INACTIVE50

31 YES UNUSED100

SQL>alter database drop logfile group 2;

Database altered.

删除物理文件,在创建

SQL> alter database add logfilegroup 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 STATUSBYTES/1024/1024

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

11 NOCURRENT100

21 YES UNUSED100

31 YES UNUSED100

主库搞定。

二.备库操作

2.1查看信息

SQL> select group#,type, member from v$logfile;

GROUP# TYPEMEMBER

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

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 STATUSBYTES/1024/1024

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

11 YES CLEARING_CURRENT50

31 YES CLEARING50

21 YES CLEARING50

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# TYPEMEMBER

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

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;

System altered.

不然会报错:

SQL>alter database drop logfile group 2;

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.

在Oracle官网上搜了一下,找到了一篇文章:

How to Add/Drop/Resize Redo Log with Physical Standby in place. [ID 473442.1]

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

GROUP# STATUS

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

1 CLEARING_CURRENT

3 CLEARING

2 CLEARING

SQL>

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/xezf/redo02.log'

ORA-19527:physical standby redo log must be renamed

Cause:The CLEAR LOGFILE command was used at a physical standby database. This command cannot be used at a physical standby database unless the LOG_FILE_NAME_CONVERT initialization parameter is set. This is required to avoid overwriting the primary database's logfiles.

Action:Set the LOG_FILE_NAME_CONVERT initialization parameter.

我们需要设置LOG_FILE_NAME_CONVERT参数,才能使用clear logfile命令。 参考:

ORA-00313, ORA-00312, ORA-27037 in Standby Database [ID 601835.1]

解决方法如下:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

在pfile里添加参数:

语法:*.log_file_name_convert = '',''

*.log_file_name_convert ='/u01/app/oracle/oradata/xezf/','/u01/app/oracle/oradata/xezf/'

用pfile启动备库:

SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initxezf.ora';

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size2096632 bytes

Variable Size385876488 bytes

Database Buffers1207959552 bytes

Redo Buffers14680064 bytes

SQL> alter database mount standby database;

Database altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CLEARING

3 CLEARING

2 CLEARING_CURRENT

SQL> show parameter log_file_name_convert

NAMETYPEVALUE

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

log_file_name_convertstring/u01/app/oracle/oradata/xezf,/u01/app/oracle/oradata/xezf/

SQL>alter database clear logfile group 1;

Database altered.

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

System altered.

SQL> alter database drop logfile group 1;

Database altered.

删除物理文件:

[oracle@qs-xezf-db2 xezf]$ rm redo01.log

创建新的日志组:

SQL> alter database add logfilegroup 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 logfilegroup 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 STATUSBYTES/1024/1024

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

11 YES UNUSED100

31 YES UNUSED100

21 YES CLEARING_CURRENT50

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

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

物理删除文件:

[oracle@qs-xezf-db2 xezf]$ rm redo02.log

添加redo:

SQL> alter database add logfilegroup 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 STATUSBYTES/1024/1024

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

1 1 YES UNUSED100

31 YES CLEARING_CURRENT100

21 YES UNUSED100

搞定,最后启动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#)

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

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

SEQUENCE# APP

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

12 YES

14 YES

15 YES

14 rows selected.

同步正常。ok。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值