[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做好相关文档,而不是边写边做,很少出现这样的错误.

时间： 2024-09-05 03:07:30

[20171211]ora-16014 11g.txt的相关文章

[20170615]直方图-高度直方图(11g).txt

[20170615]直方图-高度直方图(11g).txt --//昨天看了一些直方图的资料,重新看jonathanlewis写<CBO>书籍,在测试时遇到一些与原来书讲的不一样的地方. --//自己重复测试看看. 1.环境以及测试建立: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --

[20170914]tnsnames.ora的管理.txt

[20170914]tnsnames.ora的管理.txt --//昨天朋友讲tnsnams.ora的内容太长了,而且许多不需要的.管理不方便.我记得以前写[20150409]tnsnames.ora与IFILE.txt.链接 --//http://blog.itpub.net/267265/viewspace-1561107/ --//这样你可以按照某种分类管理.实际上这个我也是以前看别人的机器学来的,很简单就是建立多个tnsnames配置文件. --//使用参数IFILE=/path/xxx

[20140210]一条sql语句的优化(11g).txt

  [20140210]一条sql语句的优化(11g).txt 今天下午看生产系统数据库,无意中发现一个错误,同时优化也有点小问题,写一个测试脚本. 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -

[20111220]tnsnames.ora的定位.txt

[20111220]tnsnames.ora的定位.txt 1.跟踪在linux下sqlplus的执行过程,可以很容易定位tnsnames.ora的定位过程. $ export TNS_ADMIN=/tmp$ strace -o  findtnsnames.txt sqlplus scott/xxxx@noexist $ grep -i tnsname findtnsnames.txtaccess("/home/oracle/.tnsnames.ora", F_OK) = -1 ENO

[20140909]oracle cluster index (11g).txt

[20140909]oracle cluster index (11g).txt --应用中除了堆表,很少使用cluser表,也就仅仅在生产系统使用IOT索引组织表. --实际上系统表中许多都是cluster表.比如SYS.TAB$,SYS.COL$等都建立在cluster中. --没事,简单研究一下其存储结构. 1.建立测试环境: SCOTT@test> @ver BANNER ----------------------------------------------------------

[20150924]tnsnames.ora是否可以带斜线.txt

[20150924]tnsnames.ora是否可以带斜线.txt --10g开始oracle支持ezconnect简单连接方式建立与数据库的连接. d:\tools\sqltemp>sqlplus scott/xxxxxx@192.168.100.40:1521/test.com SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 24 08:32:43 2015 Copyright (c) 1982, 2013, Oracle.  All r

[20171208]ORA-16014(10G).txt

[20171208]ORA-16014(10G).txt --//别人配置dg遇到的问题,当时没有记录.在自己的环境做一个演示.这个问题相对隐藏很深.主要是自己不注意这些细节. 1.环境: SYS@orcl> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------

生产环境oracle10g升级至11g准备工作

最近需要生产系统从10.2.0.5.升级到11.2.0.2.0 做了不少的准备工作,硬是在周末自己搭了测试环境,照着自己准备的升级步骤练习了一番. 除过基本的检查,从Metalink上下载了最新的psu,和公司的资深dba确认后,提供了给了客户.这样数据库就算是升级到11.2.0.2.10 主要有以下的步骤 : 1.new ORACLE_HOME(11g), old ORACLE_HOME (10g) --这些需要提前提供给客户,作为基本的约定 2.install oracle software

[20170705]lsnrctl status LISTENER_SCAN1

[20170705]lsnrctl status LISTENER_SCAN1.txt --//前几天在解决问题时遇到的问题,现在分析看看.当时没注意操作用户名,选择错误的用户执行(oracle用户执行). 1.环境: --//rac SYS@dbcn2> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------