备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20171211]ora-16014 11g.txt

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

[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
1144 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
1265 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171208]ORA-16014(10G).txt
[20171208]ORA-16014(10G).txt --//别人配置dg遇到的问题,当时没有记录.在自己的环境做一个演示.这个问题相对隐藏很深.主要是自己不注意这些细节.
lfreeali
1175 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
1430 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
1377 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
1292 0
lfreeali
|
测试技术
[20160311]ora-01732.txt
[20160311]ora-01732.txt --今天工作出现ora-01732错误,做一个测试例子,做一个记录: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION     BANNER    ...
lfreeali
959 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
823 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
755 0
lfreeali
|
Oracle 关系型数据库 数据库
[20150529]ORA-16664.txt
[20150529]ORA-16664.txt --今天在例行检查中.执行 dgmgrl 出现ora-16664错误. --检查drcdbcn1.log文件,发现如下错误.
lfreeali
973 0

热门文章

最新文章

  • 1
    随机生成UserAgent的python库（fake-useragent库）
  • 2
    使用zxing识别一幅包含多个二维码的图片
  • 3
    阿里云的SLB，植入cookie和重写cookie有什么区别？
  • 4
    基于Flink的实时日志分析系统实践
  • 5
    如何在服务器上跑python程序
  • 6
    电池技术多年没有较大发展，成为移动设备最大制约
  • 7
    Spread for WinRT 7新功能使用指南
  • 8
    codeforces Soldier and Number Game（dp+素数筛选）
  • 9
    九度oj-1001-Java
  • 10
    Android:随笔——对页面的View进行截图
  • 1
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势2
    9
  • 2
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势法1
    9
  • 3
    灰色关联分析(Grey Relation Analysis,GRA)中国经济社会发展指标
    9
  • 4
    极值分析：分块极大值BLOCK-MAXIMA、阈值超额法、广义帕累托分布GPD拟合降雨数据时间序列
    9
  • 5
    GARCH-DCC模型和DCC（MVT）建模估计
    8
  • 6
    ARIMA、ARIMAX、 动态回归和OLS 回归预测多元时间序列
    10
  • 7
    R语言预测期货波动率的实现：ARCH与HAR-RV与GARCH，ARFIMA模型比较
    10
  • 8
    spss modeler用决策树神经网络预测ST的股票
    9
  • 9
    K-means和层次聚类分析癌细胞系微阵列数据和树状图可视化比较
    8
  • 10
    matlab中使用VMD(变分模态分解)对信号去噪
    8

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4