[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做好相关文档,而不是边写边做,很少出现这样的错误.
[20171211]ora-16014 11g.txt的更多相关文章
-
[20190219]那个更快(11g).txt
[20190219]那个更快(11g).txt --//前几天测试11g Query Result Cache RC Latches时,链接http://blog.itpub.net/267265/v ...
-
[20180819]关于父子游标问题(11g).txt
[20180819]关于父子游标问题(11g).txt --//sql语句存在父子游标,子游标堆6在父游标堆0里面.--//如果存在许多子游标的情况下,父游标堆0是否大小是发生变化呢.测试看看.--/ ...
-
[20170612]FOR ALL COLUMNS SIZE repeat(11g).txt
[20170612]FOR ALL COLUMNS SIZE repeat(11g).txt --//昨天看了https://jonathanlewis.wordpress.com/2017/06/0 ...
-
[20171120]理解v$session的state字段(11G).txt
[20171120]理解v$session的state字段(11G).txt --//https://blogs.oracle.com/database4cn/vsession-%e4%bd%a0%e ...
-
[20170914]tnsnames.ora的管理.txt
[20170914]tnsnames.ora的管理.txt --//昨天朋友讲tnsnams.ora的内容太长了,而且许多不需要的.管理不方便.我记得以前写[20150409]tnsnames.ora ...
-
单机 Oracle 11g(11.2.0.4)手动打补丁PSU(11.2.0.4.8)
环境说明:database : 11.2.0.4 x64os: centos6.7 x64 准备内容:OPatch : p6880880_112000_Linux-x86-64.zipDB PSU : ...
-
异构GoldenGate 12c 单向复制配置(支持DDL复制)
1.开始配置OGG支持DDL复制(在source端操作) 1.1 赋予权限 SQL> conn /as sysdba 已连接. SQL> grant execute on utl_file ...
-
不同操作系统上屏蔽oracle的操作系统认证方式
windows系统上>如果不想用户通过操作系统验证方式登录,可以修改 sqlnet.ora文件,把 SQLNET.AUTHENTICATION_SERVICES=NTS 前面加#注释掉就可以了. ...
-
Python--DBUtil
Python--DBUtil包 1 简介 DBUtils是一套Python数据库连接池包,并允许对非线程安全的数据库接口进行线程安全包装.DBUtils来自Webware for Python. DB ...
随机推荐
-
HTML中哪些标签的值会被提交到服务器呢？
<form> <input name="">标签 <select name="">标签 <textarea name= ...
-
使用for循环遍历数组
package review20140419;/* * 遍历数组 */public abstract class Test1 { //程序入口 public static void mai ...
-
这些年正Android - 身在他乡
“从不敢想到想去做到,做到我想的,事实证明我并不像他们想象的那样脆弱.我只是需要一盏灯,一架钢琴,一支麦克风.曾经想象过做一名医生救死扶伤,也曾想过做律师,做记者,做奥运冠军,但是都没有结果.因为我最 ...
-
Linux下添加硬盘，分区，格式化详解
2005-10-17 在我们添加硬盘前,首先要了解linux系统下对硬盘和分区的命名方法. 在Linux下对IDE的设备是以hd命名的,第一个ide设备是hda,第二个是hdb.依此类推 我们一般主板 ...
-
[c++][语言语法]函数模板和模板函数 及参数类型的运行时判断
参考:http://blog.csdn.net/beyondhaven/article/details/4204345 参考:http://blog.csdn.net/joeblackzqq/arti ...
-
Creating Icon Overlay Handlers / 创建图标标记 Handlers (翻译自MSDN) / VC++, Windows, DLL, ATL, COM
创建图标标记 Handlers Creating Icon Overlay Handlers 图标标记是放在代表着某个 Shell 对象的图标之左下角的小图像.它们通常被加在一个对象的图标的身上来提供 ...
-
as3中使用stage ,root ,this 区别详解
stage:最顶层舞台root:stage的下一级舞台,属于第二层舞台(继承自DisplayObject)this:当前的对象(如果是主时间轴上的this,那它就是root) 继承方面:Stage - ...
-
Session简介
摘要:虽然session机制在web应用程序中被采用已经很长时间了,但是仍然有很多人不清楚session机制的本质,以至不能正确的应用这一技术.本文将详细讨论session的工作机制并且对在Java ...
-
NewLife.Net——网络压测单机1.88亿tps
NewLife.Net压力测试,峰值4.2Gbps,50万pps,消息大小24字节,消息处理速度1.88亿tps! 共集合20台高配ECS参与测试,主服务器带宽6Gbps.100万pps,16核心64 ...
-
RCNN--目标检测
原博文:http://www.cnblogs.com/soulmate1023/p/5530600.html 文章简要介绍RCNN的框架,主要包含: 原图-->候选区域生成-->对每个候选 ...