AI 助理
备案控制台
开发者社区 数据库 文章 正文

[20171211]ora-16014 11g.txt

2017-12-11 1210
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

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

文章标签：
数据库备份
关系型数据库
Oracle
Linux
lfreeali
目录
相关文章
lfreeali
|
Oracle 关系型数据库
[20180502]ORA-01580.txt
[20180502]ORA-01580.txt RMAN> backup current controlfile; Starting backup at 2018-05-02 15:36:03 using channel ORA_DISK_1 using...
lfreeali
1179 0
lfreeali
|
关系型数据库 Oracle Linux
[20180321]ORA-08180.txt
[20180321]ORA-08180.txt $ oerr ora 08180 08180, 00000, "no snapshot found based on specified time" // *Cause: Could not match the time to an SCN from the mapping table.
lfreeali
1300 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171208]ORA-16014(10G).txt
[20171208]ORA-16014(10G).txt --//别人配置dg遇到的问题,当时没有记录.在自己的环境做一个演示.这个问题相对隐藏很深.主要是自己不注意这些细节.
lfreeali
1207 0
lfreeali
|
关系型数据库 Oracle Linux
[20170628]12C ORA-54032.txt
[20170628]12C ORA-54032.txt https://hourim.wordpress.com/2017/06/24/ora-54032-column-to-be-renamed-is-used-in-a-virtual-column-expres...
lfreeali
1463 0
lfreeali
|
SQL 网络协议 Oracle
[20170419]ora-28547.txt
[20170419]ora-28547.txt --//测试环境，莫名奇妙使用toad登陆出现如下错误(使用ezconnect方式)。 ORA-28547: connection to server failed, probable Oracle Net adm...
lfreeali
1419 0
lfreeali
[20160623]ora-00445.txt
[20160623]ora-00445.txt Wed Jun 22 07:38:25 2016 Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_smco_3976.
lfreeali
1327 0
lfreeali
|
测试技术
[20160311]ora-01732.txt
[20160311]ora-01732.txt --今天工作出现ora-01732错误,做一个测试例子,做一个记录: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION     BANNER    ...
lfreeali
1001 0
lfreeali
|
SQL Oracle 关系型数据库
[20151215]ORA-60014.txt
[20151215]ORA-60014: invalid MAXSIZE storage option value.txt SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        ...
lfreeali
850 0
lfreeali
|
SQL
[20150707]ORA-00932.txt
[20150707]ORA-00932: inconsistent datatypes: expected - got CLOB.txt --自己写一个通过sql_id查看sql语句的脚本： column sqltext format a200 sele...
lfreeali
781 0
lfreeali
|
Oracle 关系型数据库 数据库
[20150529]ORA-16664.txt
[20150529]ORA-16664.txt --今天在例行检查中.执行 dgmgrl 出现ora-16664错误. --检查drcdbcn1.log文件,发现如下错误.
lfreeali
1004 0