本文共 9916 字，大约阅读时间需要 33 分钟。
[20171211]ora-16014 11g.txt
--//上午测试了10g下备库log_archive_dest_1参数配置VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)的错误.在11G也测试看看:
1.环境:
SCOTT@book> @ &r/ver1PORT_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 bytesVariable Size 197133392 bytesDatabase Buffers 427819008 bytesRedo Buffers 7524352 bytesDatabase mounted.SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.--//检查alert.log出现如下:
Mon Dec 11 16:20:31 2017ARCH: Archival stopped, error occurred. Will continue retryingORACLE Instance bookdg - Archival ErrorORA-16014: log 4 sequence# 695 not archived, no available destinationsORA-00312: online log 4 thread 1: '/mnt/ramdisk/book/redostb01.log'Mon Dec 11 16:20:31 2017Archiver process freed from errors. No longer stoppedalter database recover managed standby database using current logfile disconnectAttempt to start background Managed Standby Recovery process (bookdg)Mon Dec 11 16:20:42 2017MRP0 started with pid=28, OS id=23531MRP0: Background Managed Standby Recovery process started (bookdg) started logmerger processMon Dec 11 16:20:48 2017Managed Standby Recovery starting Real Time ApplyParallel Media Recovery started with 4 slavesWaiting 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.logCompleted: alter database recover managed standby database using current logfile disconnectSYS@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 0ARCH 23508 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23510 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23512 CONNECTED ARCH N/A 0 0 0 0 0RFS 23533 IDLE ARCH N/A 0 0 0 0 0RFS 23523 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23521 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23525 IDLE LGWR 3 1 697 212 1 0MRP0 23531 APPLYING_LOG N/A N/A 1 697 212 102400 09 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.logUsing STANDBY_ARCHIVE_DEST parameter default value as ?/dbs/arch3.一旦出现这样的情况MRP0停止实时应用日志:
SYS@bookdg> @ &r/dg/dgPROCESS PID STATUS CLIENT_P GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS--------- ------- ---------- -------- ------ ------- --------- ------------ ------------ ------------ARCH 23506 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23508 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23510 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23512 CONNECTED ARCH N/A 0 0 0 0 0RFS 23533 IDLE ARCH N/A 0 0 0 0 0RFS 23523 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23521 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23525 IDLE LGWR 2 1 699 1472 3 0MRP0 23531 WAIT_FOR_L N/A N/A 1 699 0 0 0OG
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 0ARCH 23508 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23510 CONNECTED ARCH N/A 0 0 0 0 0ARCH 23512 CONNECTED ARCH N/A 0 0 0 0 0RFS 23533 IDLE ARCH N/A 0 0 0 0 0RFS 23523 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23521 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23525 IDLE LGWR 2 1 699 1550 1 0MRP0 23531 WAIT_FOR_L N/A N/A 1 699 0 0 0OG
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 openDatabase dismounted.ORACLE instance shut down.SYS@bookdg> startup mount
ORACLE instance started.Total System Global Area 634732544 bytesFixed Size 2255792 bytesVariable Size 197133392 bytesDatabase Buffers 427819008 bytesRedo Buffers 7524352 bytesSYS@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 0RFS 23670 IDLE UNKNOWN N/A 0 0 0 0 0RFS 23674 IDLE ARCH N/A 0 0 0 0 0RFS 23672 IDLE LGWR 3 1 700 126 1 0ARCH 23659 CLOSING ARCH 4 1 699 1 1809 0ARCH 23663 CLOSING ARCH 6 1 697 1 701 0ARCH 23661 CLOSING ARCH 7 1 698 1 1997 0MRP0 23676 APPLYING_L N/A N/A 1 700 126 102400 0OG
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/archUsing STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/archivelog/book--//出现这种问题,估计上面的提示可能容易定位一些.做一个记录.实际上做dg做好相关文档,而不是边写边做,很少出现这样的错误.
转载地址：http://vlcpa.baihongyu.com/