log添加 oracle redo_ORACLE DG添加redo日志成员

ORACLE DG在线日志添加日志成员

SQL>select SEQUENCE#,first_time,next_time,APPLIED, THREAD# from v$archived_log order by SEQUENCE#,THREAD#;#记录日志应用的位置

SQL> select GROUP#,BYTES/1024/1024 from v$log;

SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;

主库：

SQL> select * from v$logfile order by GROUP# ;

GROUP# STATUS TYPE MEMBER

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

1 ONLINE /data/app/oracle/oradata/BEIJING/redo01.log

2 ONLINE /data/app/oracle/oradata/BEIJING/redo02.log

3 ONLINE /data/app/oracle/oradata/BEIJING/redo03.log

4 STANDBY /data/app/oracle/oradata/BEIJING/standbyredo01.log

5 STANDBY /data/app/oracle/oradata/BEIJING/standbyredo02.log

6 STANDBY /data/app/oracle/oradata/BEIJING/standbyredo03.log

7 STANDBY /data/app/oracle/oradata/BEIJING/standbyredo04.log

7 rows selected.

备库:

SQL> select * from v$logfile order by GROUP# ;

GROUP# STATUS TYPE MEMBER

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

1 ONLINE /data/app/oracle/oradata/TIANJIN/redo01.log

2 ONLINE /data/app/oracle/oradata/TIANJIN/redo02.log

3 ONLINE /data/app/oracle/oradata/TIANJIN/redo03.log

4 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo01.log

5 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo02.log

6 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo03.log

7 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo04.log

7 rows selected.

1.备库添加standby log member

SQL>alter database recover managed standby database cancel;#取消APPLIED

alter database add standby logfile member '/data/app/oracle/oradata/TIANJIN/standbyredo01_2.log' to group 4;

alter database add standby logfile member '/data/app/oracle/oradata/TIANJIN/standbyredo02_2.log' to group 5;

alter database add standby logfile member '/data/app/oracle/oradata/TIANJIN/standbyredo03_2.log' to group 6;

alter database add standby logfile member '/data/app/oracle/oradata/TIANJIN/standbyredo04_2.log' to group 7;

2.主库添加standby log member

alter database add standby logfile member '/data/app/oracle/oradata/BEIJING/standbyredo01_2.log' to group 4;

alter database add standby logfile member '/data/app/oracle/oradata/BEIJING/standbyredo02_2.log' to group 5;

alter database add standby logfile member '/data/app/oracle/oradata/BEIJING/standbyredo03_2.log' to group 6;

alter database add standby logfile member '/data/app/oracle/oradata/BEIJING/standbyredo04_2.log' to group 7;

3.主库添加ONLINE redo log member

alter database add logfile member '/data/app/oracle/oradata/BEIJING/redo01_2.log' to group 1;

alter database add logfile member '/data/app/oracle/oradata/BEIJING/redo02_2.log' to group 2;

alter database add logfile member '/data/app/oracle/oradata/BEIJING/redo03_2.log' to group 3;

4.备库添加新的ONLINE redo log member

alter system set standby_file_management='MANUAL';

alter database add logfile member '/data/app/oracle/oradata/TIANJIN/redo01_2.log' to group 1;

alter database add logfile member '/data/app/oracle/oradata/TIANJIN/redo02_2.log' to group 2;

alter database add logfile member '/data/app/oracle/oradata/TIANJIN/redo03_2.log' to group 3;

5.在备库

修改standby_file_management模式

SQL> alter system set standby_file_management='AUTO';

System altered.

开启日志实时应用

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log; group4日志的状态为ACTIVE

GROUP# BYTES/1024/1024 STATUS

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

4 50 ACTIVE

5 50 UNASSIGNED

6 50 UNASSIGNED

7 50 UNASSIGNED

此时，alter日志信息，报313错误

Errors in file /data/app/oracle/diag/rdbms/TIANJIN/TIANJIN/trace/TIANJIN_pr00_6329.trc:

ORA-00313: open failed for members of log group 4 of thread 1

在主库切归档

SQL> alter system switch logfile;

System altered.

备库查询standby logfile状态

SQL> select * from v$logfile order by GROUP# ;

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

1 ONLINE /data/app/oracle/oradata/TIANJIN/redo01.log NO 0

1 INVALID ONLINE /data/app/oracle/oradata/TIANJIN/redo01_2.log NO 0

2 INVALID ONLINE /data/app/oracle/oradata/TIANJIN/redo02_2.log NO 0

2 ONLINE /data/app/oracle/oradata/TIANJIN/redo02.log NO 0

3 INVALID ONLINE /data/app/oracle/oradata/TIANJIN/redo03_2.log NO 0

3 ONLINE /data/app/oracle/oradata/TIANJIN/redo03.log NO 0

4 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo01.log NO 0

4 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo01_2.log NO 0

5 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo02_2.log NO 0

5 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo02.log NO 0

6 INVALID STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo03_2.log NO 0

GROUP# STATUS TYPE MEMBER IS_ CON_ID

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

6 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo03.log NO 0

7 INVALID STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo04_2.log NO 0

7 STANDBY /data/app/oracle/oradata/TIANJIN/standbyredo04.log NO 0

14 rows selected.

SQL> select GROUP#,BYTES/1024/1024,STATUS from v$standby_log;

GROUP# BYTES/1024/1024 STATUS

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

4 50 UNASSIGNED

5 50 ACTIVE

6 50 UNASSIGNED

7 50 UNASSIGNED