[20171211]ora-16014 11g.txt

[20171211]ora-16014 11g.txt

–//上午测试了10g下备库log_archive_dest_1参数配置VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)的错误.在11G也测试看看:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

–//在备库执行:
create pfile=’/tmp/@.ora’ from spfile ;

–//修改/tmp/bookdg.ora中参数:
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bookdg’
–//修改为:
*.log_archive_dest_1=’LOCATION=/u01/app/oracle/archivelog/book MANDATORY VALID_FOR=(ONLINELOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bookdg’

2.重启备库:

SYS@bookdg> startup mount pfile=’/tmp/bookdg.ora’
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.

–//检查alert.log出现如下:
Mon Dec 11 16:20:31 2017
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance bookdg – Archival Error
ORA-16014: log 4 sequence# 695 not archived, no available destinations
ORA-00312: online log 4 thread 1: ‘/mnt/ramdisk/book/redostb01.log’
Mon Dec 11 16:20:31 2017
Archiver process freed from errors. No longer stopped
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (bookdg)
Mon Dec 11 16:20:42 2017
MRP0 started with pid=28, OS id=23531
MRP0: Background Managed Standby Recovery process started (bookdg)
 started logmerger process
Mon Dec 11 16:20:48 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 695 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 695 Reading mem 0
  Mem# 0: /mnt/ramdisk/book/redostb01.log
Completed: alter database recover managed standby database using current logfile disconnect

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#    SEQUENCE#       BLOCK#       BLOCKS   DELAY_MINS
——— ——- ———— ——– —— ——- ———— ———— ———— ————
ARCH        23506 CONNECTED    ARCH     N/A          0            0            0            0            0
ARCH        23508 CONNECTED    ARCH     N/A          0            0            0            0            0
ARCH        23510 CONNECTED    ARCH     N/A          0            0            0            0            0
ARCH        23512 CONNECTED    ARCH     N/A          0            0            0            0            0
RFS         23533 IDLE         ARCH     N/A          0            0            0            0            0
RFS         23523 IDLE         UNKNOWN  N/A          0            0            0            0            0
RFS         23521 IDLE         UNKNOWN  N/A          0            0            0            0            0
RFS         23525 IDLE         LGWR     3            1          697          212            1            0
MRP0        23531 APPLYING_LOG N/A      N/A          1          697          212       102400            0
9 rows selected.

–//但是MRP0可以应用,而且是实时的.

SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
—— ———- ——- ——— ———– ——— ———— — ———- ————- ——————- ———— ——————- ———— ——————-
     4 1337401710       1       695    52428800       512        78848 NO  ACTIVE       13276910949 2017-02-28 14:40:12  13276931102 2017-12-11 16:19:45  13276911100 2017-02-28 14:42:36
     5 1337401710       1       696    52428800       512     11649536 NO  ACTIVE       13276931102 2017-12-11 16:19:45  13276933607 2017-12-11 16:20:30  13276933604 2017-12-11 16:20:30
     6 1337401710       1       697    52428800       512       230400 YES ACTIVE       13276933607 2017-12-11 16:20:30                                   13276933962 2017-12-11 16:26:24
     7 UNASSIGNED       0         0    52428800       512            0 YES UNASSIGNED

–//可以发现GROUP#=6,LAST_CHANGE#一直在变化.

3.如果在主库切换日志呢?
–//主库:
SYS@book> alter system archive log current ;
System altered.

–//备库:
SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
—— ———- ——- ——— ———– ——— ———— — ———- ————- ——————- ———— ——————- ———— ——————-
     4 1337401710       1       695    52428800       512        78848 NO  ACTIVE       13276910949 2017-02-28 14:40:12  13276931102 2017-12-11 16:19:45  13276911100 2017-02-28 14:42:36
     5 1337401710       1       696    52428800       512     11649536 NO  ACTIVE       13276931102 2017-12-11 16:19:45  13276933607 2017-12-11 16:20:30  13276933604 2017-12-11 16:20:30
     6 1337401710       1       697    52428800       512       358912 NO  ACTIVE       13276933607 2017-12-11 16:20:30  13276934205 2017-12-11 16:30:11  13276934201 2017-12-11 16:30:11
     7 1337401710       1       698    52428800       512         4096 YES ACTIVE       13276934205 2017-12-11 16:30:11                                   13276934211 2017-12-11 16:30:17

–//主库:
SYS@book> alter system archive log current ;
System altered.

–//备库:
SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
—— ———- ——- ——— ———– ——— ———— — ———- ————- ——————- ———— ——————- ———— ——————-
     4 1337401710       1       695    52428800       512        78848 NO  ACTIVE       13276910949 2017-02-28 14:40:12  13276931102 2017-12-11 16:19:45  13276911100 2017-02-28 14:42:36
     5 1337401710       1       696    52428800       512     11649536 NO  ACTIVE       13276931102 2017-12-11 16:19:45  13276933607 2017-12-11 16:20:30  13276933604 2017-12-11 16:20:30
     6 1337401710       1       697    52428800       512       358912 NO  ACTIVE       13276933607 2017-12-11 16:20:30  13276934205 2017-12-11 16:30:11  13276934201 2017-12-11 16:30:11
     7 1337401710       1       698    52428800       512      1022464 NO  ACTIVE       13276934205 2017-12-11 16:30:11  13276934424 2017-12-11 16:32:48  13276934424 2017-12-11 16:32:48

–//standby log已经接收满了.seq= 699.现在在哪里呢?

$ ls -l /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1*
-rw-r—–  1 oracle oinstall 52429312 2017-12-11 16:38:56 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_699_896605872.dbf

–//注意看alert.log文件,出现如下这行.
$ grep -i standby_archive_dest alert_bookdg.log
Using STANDBY_ARCHIVE_DEST parameter default value as ?/dbs/arch

3.一旦出现这样的情况MRP0停止实时应用日志:
SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS     CLIENT_P GROUP# THREAD# SEQUENCE#       BLOCK#       BLOCKS   DELAY_MINS
——— ——- ———- ——– —— ——- ——— ———— ———— ————
ARCH        23506 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23508 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23510 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23512 CONNECTED  ARCH     N/A          0         0            0            0            0
RFS         23533 IDLE       ARCH     N/A          0         0            0            0            0
RFS         23523 IDLE       UNKNOWN  N/A          0         0            0            0            0
RFS         23521 IDLE       UNKNOWN  N/A          0         0            0            0            0
RFS         23525 IDLE       LGWR     2            1       699         1472            3            0
MRP0        23531 WAIT_FOR_L N/A      N/A          1       699            0            0            0
                  OG
9 rows selected.

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS     CLIENT_P GROUP# THREAD# SEQUENCE#       BLOCK#       BLOCKS   DELAY_MINS
——— ——- ———- ——– —— ——- ——— ———— ———— ————
ARCH        23506 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23508 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23510 CONNECTED  ARCH     N/A          0         0            0            0            0
ARCH        23512 CONNECTED  ARCH     N/A          0         0            0            0            0
RFS         23533 IDLE       ARCH     N/A          0         0            0            0            0
RFS         23523 IDLE       UNKNOWN  N/A          0         0            0            0            0
RFS         23521 IDLE       UNKNOWN  N/A          0         0            0            0            0
RFS         23525 IDLE       LGWR     2            1       699         1550            1            0
MRP0        23531 WAIT_FOR_L N/A      N/A          1       699            0            0            0
                  OG
9 rows selected.

–//注意看MRP0那行,现在BLOCK#=0.RFS (SEQUENCE#=699那行) ,block#在变化.也就是现在已经不是实时应用.
–//standby log保存SEQUENCE#=695,696,697,698的归档.剩下的归档放在缺省的?/dbs/arch目录.
–//也就是现在设置备库log_archive_dest_1参数无效.

3.改正会话后:
SYS@bookdg> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SYS@bookdg> startup mount
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS     CLIENT_P GROUP# THREAD# SEQUENCE#       BLOCK#       BLOCKS   DELAY_MINS
——— ——- ———- ——– —— ——- ——— ———— ———— ————
ARCH        23665 CONNECTED  ARCH     N/A          0         0            0            0            0
RFS         23670 IDLE       UNKNOWN  N/A          0         0            0            0            0
RFS         23674 IDLE       ARCH     N/A          0         0            0            0            0
RFS         23672 IDLE       LGWR     3            1       700          126            1            0
ARCH        23659 CLOSING    ARCH     4            1       699            1         1809            0
ARCH        23663 CLOSING    ARCH     6            1       697            1          701            0
ARCH        23661 CLOSING    ARCH     7            1       698            1         1997            0
MRP0        23676 APPLYING_L N/A      N/A          1       700          126       102400            0
                  OG
8 rows selected.

SYS@bookdg> select * from v$standby_log;
GROUP# DBID       THREAD# SEQUENCE#       BYTES BLOCKSIZE         USED ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME           LAST_CHANGE# LAST_TIME
—— ———- ——- ——— ———– ——— ———— — ———- ————- ——————- ———— ——————- ———— ——————-
     4 UNASSIGNED       1         0    52428800       512            0 NO  UNASSIGNED
     5 1337401710       1       700    52428800       512        85504 YES ACTIVE       13276935995 2017-12-11 16:45:24                                   13276936149 2017-12-11 16:47:54
     6 UNASSIGNED       1         0    52428800       512            0 NO  UNASSIGNED
     7 UNASSIGNED       1         0    52428800       512            0 NO  UNASSIGNED

–//可以发现seq=697,698,699归档后关闭.现在可以指派并应用日志到standby log.

$ grep -i standby_archive_dest alert_bookdg.log
Using STANDBY_ARCHIVE_DEST parameter default value as ?/dbs/arch
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archivelog/book

–//出现这种问题,估计上面的提示可能容易定位一些.做一个记录.实际上做dg做好相关文档,而不是边写边做,很少出现这样的错误.