ORA-00349|激活 ADG 备库时遇到的问题及处理方法

--主库

--主库设置为 defer, 取消备库日志应用，关库启动到 mount 状态进行。

show parameter log_archive_dest_state_2

alter system set log_archive_dest_state_2=defer scope=both sid='*';

--备库

alter database recover managed standby database cancel;

shu immediate

startup mount

--强制拉成主库，很遗憾报错 ORA-00349

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER DATABASE ACTIVATE STANDBY DATABASE

*

ERROR at line 1:

ORA-00349: failure obtaining block size for '+JIEKE_DATA'

ORA-15001: diskgroup "JIEKE_DATA" does not exist or is not mounted

ORA-15001: diskgroup "JIEKE_DATA" does not exist or is not mounted

GROUP# Member

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

   5 +JIEKE_DATA

   5 +JIEKE_DATA

   6 +JIEKE_DATA

   6 +JIEKE_DATA

  23 +JIEKE_DATA

  23 +JIEKE_DATA

  11 +JIEKER_DATA/jiekexu/onlinelog/group_11.1621.1065127343

  11 +JIEKER_ARCH/jiekexu/onlinelog/group_11.389.1065127355

  12 +JIEKER_DATA/jiekexu/onlinelog/group_12.1620.1065127363

  12 +JIEKER_ARCH/jiekexu/onlinelog/group_12.395.1065127371

  13 +JIEKER_DATA/jiekexu/onlinelog/group_13.1619.1065127381

  SELECT to_char(f.group#) groupno, to_char(l.thread#) thread, f.member member, f.type redo_file_type, l.status group_status, f.status member_status, l.bytes/1024/1024 bytes, l.archived archived

FROM v$logfile f, v$log l WHERE f.group# = l.grSQL> oup# ORDER BY f.group#, f.member;

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2

Group Thread Member  Redo Type Group Status Member Status Size(M) Archived

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

5 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

6 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

23 1 +JIEKE_DATA  ONLINE CLEARING_CUR INVALID  4,096 YES

       RENT

  1 +JIEKE_DATA  ONLINE CLEARING_CUR INVALID  4,096 YES

       RENT

27 2 +JIEKER_DATA/jiekexu/ ONLINE UNUSED    4,096 YES

   onlinelog/group_27.1

   741.1065129955

28 2 +JIEKER_DATA/jiekexu/ ONLINE UNUSED    4,096 YES

   onlinelog/group_28.1

   742.1065129973

SQL> alter database drop logfile group 27;

alter database drop logfile group 27

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

--由于开启了日志应用进程，直接删除会报错，故需要停止日志应用，修改参数 standby_file_management 为手动。

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter system set standby_file_management= 'manual' scope=both sid= '*' ;

Database altered.

SQL> show parameter standby_file_management

NAME      TYPE   VALUE

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

standby_file_management  string   manual

SQL> alter database drop logfile group 27;

Database altered.

--由于日志组不能少于 2 个，故 日志组 28 不不能够删除。

SQL> alter database drop logfile group 28;

alter database drop logfile group 28

*

ERROR at line 1:

ORA-01567: dropping log 28 would leave less than 2 log files for instance JIEKEXU2 (thread 2)

ORA-00312: online log 28 thread 2: '+JIEKER_DATA/jiekexu/onlinelog/group_28.1742.1065129973'

--重启备库删除日志组 6、23，由于是当前日志组无法删除，庆幸日志组 5 成功删除了。

SQL> alter database drop logfile group 6;

alter database drop logfile group 6

*

ERROR at line 1:

ORA-01623: log 6 is current log for instance JIEKEXU2 (thread 2) - cannot drop

ORA-00312: online log 6 thread 2: '+JIEKE_DATA'

ORA-00312: online log 6 thread 2: '+JIEKE_DATA'

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 23;

alter database drop logfile group 23

*

ERROR at line 1:

ORA-01623: log 23 is current log for instance jiekexu (thread 1) - cannot drop

ORA-00312: online log 23 thread 1: '+JIEKE_DATA'

ORA-00312: online log 23 thread 1: '+JIEKE_DATA'

--只剩两组日志组也是当前日志组，则当前日志组无法删除，尝试进行 rename 操作，但也是无效或者缺失命令。

SQL> alter database rename '+JIEKE_DATA' to '+JIEKER_DATA' ;

alter database rename '+JIEKE_DATA' to '+JIEKER_DATA'

    *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database rename '+JIEKE_DATA' to '+JIEKER_DATA/jiekexu/onlinelog/group_6.dbf' ;

alter database rename '+JIEKE_DATA' to '+JIEKER_DATA/jiekexu/onlinelog/group_6.dbf'

    *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

--当然继续激活为主库肯定也是报错。那就继续开启日志同步模式，先保持备库同步吧。

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE ;

ALTER DATABASE ACTIVATE STANDBY DATABASE

*

ERROR at line 1:

ORA-00349: failure obtaining block size for '+JIEKE_DATA'

ORA-15001: diskgroup "JIEKE_DATA" does not exist or is not mounted

ORA-15001: diskgroup "JIEKE_DATA" does not exist or is not mounted

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

--主库：

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 23;

Database altered.

rman target /

RMAN> backup current controlfile for standby format '/home/oracle/backup20210303%d_%I_%s_%p.ctl' ;

scp /home/oracle/backup20210303%d_%I_%s_%p.ctl jiekeadg:/home/oracle/

--备库

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shu immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.0689E+11 bytes

Fixed Size    2265864 bytes

Variable Size   4.2144E+10 bytes

Database Buffers  6.4425E+10 bytes

Redo Buffers  323678208 bytes

SQL> exit

jiekeadg:/home/oracle(jiekexu)>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 3 10:07:34 2021

Copyright (c) 1982, 2011, Oracle and / or its affiliates. All rights reserved.

connected to target database : JIEKEXU ( not mounted)

RMAN> restore standby controlfile from '/home/oracle/backup20210303JIEKEXU_1797812601_106_1.ctl' ;

Starting restore at 2021-03-03 10:08:03

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3009 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time : 00:00:01

output file name =+JIEKER_DATA/jiekexu/controlfile/ current .1739.1065125909

output file name =+JIEKER_ARCH/jiekexu/controlfile/ current .323.1065125911

Finished restore at 2021-03-03 10:08:04

RMAN> sql 'alter database mount' ;

sql statement: alter database mount

released channel: ORA_DISK_1

RMAN> exit

-- SQLPLUS 里应用 MRP0 进程同步数据。

jiekeadg:/home/oracle(jiekexu)>sqlplus / as sysdba

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> /

NAME   VALUE  UNIT    TIME_COMPUTED

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

transport lag +00 0  day (2) to second (0) interval 03/03/2021 10:19:08

apply lag +00 0  day (2) to second (0) interval 03/03/2021 10:19:08

SELECT to_char(f. group #) groupno, to_char(l.thread#) thread, f.member member, f.type redo_file_type, l.status group_status, f.status member_status, l.bytes/1024/1024 bytes, l.archived archived

FROM v$logfile f, v$log l WHERE f. group # = l. group # ORDER BY f. group #, f.member;SQL>

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2

Group Thread Member  Redo Type Group Status Member Status Size (M) Archived

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

1 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

2 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

3 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

4 1 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

5 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

7 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

8 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

9 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

10 2 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

24 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> show parameter standby_file_management

NAME      TYPE   VALUE

------------------------------------ ---------------------- ------------------------------standby_file_management  string   manual

SQL>

SQL> select inst_id, GROUP #,THREAD#, SEQUENCE #,USED,ARCHIVED,STATUS from gv$standby_log;

  INST_ID GROUP # THREAD# SEQUENCE # USED Archived STATUS

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

   1  11  1  0  0 YES UNASSIGNED

   1  12  1  0  0 YES UNASSIGNED

   1  13  1  0  0 YES UNASSIGNED

   1  14  1  0  0 YES UNASSIGNED

   1  15  1  0  0 YES UNASSIGNED

alter database drop logfile group 11;

alter database drop logfile group 12;

alter database drop logfile group 13;

alter database drop logfile group 14;

alter database drop logfile group 15;

ALTER DATABASE ADD standby LOGFILE THREAD 1 ( '+JIEKER_DATA' , '+JIEKER_ARCH' ) SIZE 4G;

ALTER DATABASE ADD standby LOGFILE THREAD 1 ( '+JIEKER_DATA' , '+JIEKER_ARCH' ) SIZE 4G;

ALTER DATABASE ADD standby LOGFILE THREAD 1 ( '+JIEKER_DATA' , '+JIEKER_ARCH' ) SIZE 4G;

ALTER DATABASE ADD standby LOGFILE THREAD 1 ( '+JIEKER_DATA' , '+JIEKER_ARCH' ) SIZE 4G;

ALTER DATABASE ADD standby LOGFILE THREAD 1 ( '+JIEKER_DATA' , '+JIEKER_ARCH' ) SIZE 4G;

SQL> select inst_id, GROUP #,THREAD#, SEQUENCE #,USED,ARCHIVED,STATUS from gv$standby_log;

  INST_ID GROUP # THREAD# SEQUENCE # USED Archived STATUS

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

   1  6  1 4797 3026358272 YES ACTIVE

   1  11  1  0  0 YES UNASSIGNED

   1  12  1  0  0 YES UNASSIGNED

   1  13  1  0  0 YES UNASSIGNED

   1  14  1  0  0 YES UNASSIGNED

SQL> alter system set standby_file_management= 'AUTO' scope=both sid= '*' ;

SQL> alter database open ;

alter database recover managed standby database using current logfile disconnect from session;

Database altered.

jiekeadg:/home/oracle(jiekexu)>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 3 10:52:04 2021

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to :

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> show parameter db_create_online_log_dest_

NAME      TYPE VALUE

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

db_create_online_log_dest_1  string +JIEKE_DATA

db_create_online_log_dest_2  string +JIEKE_DATA

db_create_online_log_dest_3  string

db_create_online_log_dest_4  string

db_create_online_log_dest_5  string

SQL> alter system set db_create_online_log_dest_1= '+JIEKER_DATA' scope=spfile;

System altered.

SQL> alter system set db_create_online_log_dest_2= '+JIEKER_DATA' scope=spfile;

System altered.

SELECT to_char(f. group #) groupno, to_char(l.thread#) thread, f.member member, f.type redo_file_type, l.status group_status, f.status member_status, l.bytes/1024/1024 bytes, l.archived archived

FROM v$logfile f, v$log l WHERE f. group # = l. group # ORDER BY f. group #, f.member;SQL>

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2

Group Thread Member  Redo Type Group Status Member Status Size (M) Archived

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

1 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

2 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

3 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

4 1 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

5 1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  1 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

7 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

8 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

9 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

10 2 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CURRENT INVALID  4,096 YES

24 2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

  2 +JIEKE_DATA  ONLINE CLEARING INVALID  4,096 YES

20 rows selected.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.0689E+11 bytes

Fixed Size    2265864 bytes

Variable Size   4.2144E+10 bytes

Database Buffers  6.4425E+10 bytes

Redo Buffers  323678208 bytes

Database mounted.

SQL>

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select DATABASE_ROLE from v$ database ;

DATABASE_ROLE

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

PRIMARY

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.0689E+11 bytes

Fixed Size    2265864 bytes

Variable Size   4.2144E+10 bytes

Database Buffers  6.4425E+10 bytes

Redo Buffers  323678208 bytes

Database mounted.

Database opened.

SQL> exit