- 浏览: 961172 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
事情来源于对目标库创建catalog,由ORA-00257可知,目标库归档已满,于是清理归档。
归档清理完成之后,再次创建catalog时,出现ORA-00018
查看目标库session参数,此参数为Oracle创建时默认参数,默认为(1.1 * PROCESSES) + 5。和processes一样不可动态修改。
于是进一步查看其alert日志
于是abort oracle,open oracle时错误出现:
考虑到该目标库为测试库,暂时关闭闪回影响不大,再次尝试打开数据库,错误依然,事情不妙!
再次尝试将数据库置为闪回模式时,报数据库需要recover,此错误很正常,因为我以abort模式关闭Oracle,于是手工recover database;事情走向越来越不妙,绕了一圈又错误依旧!
进一步尝试将数据库置为非归档模式,提示有保证的restore points。
于是尝试闪回点删除
检查操作系统闪回日志,可以看到此闪回日志不存在。
事情到这一步,只能拿出终极手段,重建controlfile,从理论上来讲,重建controlfile时只要Oracle redolog保持完整,可以不用resetlogs打开数据库,即数据不会丢失。重建过程中,数据库闪回功能将自动关闭
使用以下脚本重建控制文件,需要注意的是需要手动添加tempfile
当然了,Oracle 10g已经做到足够人性化,在重建控制文件完成后会在alert日志,提示创建tempfile
重建控制文件之后,顺利将数据打开,可以看到闪回自动关闭
引用
$ rman catalog mcdbra/mcdbra@drb200
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jun 13 09:35:28 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-00257: archiver error. Connect internal only, until freed.
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jun 13 09:35:28 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database: ORA-00257: archiver error. Connect internal only, until freed.
归档清理完成之后,再次创建catalog时,出现ORA-00018
引用
RMAN> create catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00018: maximum number of sessions exceeded
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00018: maximum number of sessions exceeded
查看目标库session参数,此参数为Oracle创建时默认参数,默认为(1.1 * PROCESSES) + 5。和processes一样不可动态修改。
引用
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 170
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 170
于是进一步查看其alert日志
引用
Sun Jun 13 08:08:35 2010
Errors in file /ora10g/app/admin/mcstar/bdump/mcstar_j000_11612.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
Sun Jun 13 08:08:50 2010
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61897
knlldmm: objv=1
knlldmm: scn=10782945184381
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61902
knlldmm: objv=1
knlldmm: scn=10782945186624
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61905
knlldmm: objv=1
knlldmm: scn=10782945187855
Errors in file /ora10g/app/admin/mcstar/bdump/mcstar_j000_11612.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
Sun Jun 13 08:08:50 2010
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61897
knlldmm: objv=1
knlldmm: scn=10782945184381
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61902
knlldmm: objv=1
knlldmm: scn=10782945186624
knllgobjinfo: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=MCSTAR
knlldmm: objn=61905
knlldmm: objv=1
knlldmm: scn=10782945187855
于是abort oracle,open oracle时错误出现:
引用
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1260624 bytes
Variable Size 251659184 bytes
Database Buffers 50331648 bytes
Redo Buffers 15515648 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1260624 bytes
Variable Size 251659184 bytes
Database Buffers 50331648 bytes
Redo Buffers 15515648 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database
考虑到该目标库为测试库,暂时关闭闪回影响不大,再次尝试打开数据库,错误依然,事情不妙!
引用
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
再次尝试将数据库置为闪回模式时,报数据库需要recover,此错误很正常,因为我以abort模式关闭Oracle,于是手工recover database;事情走向越来越不妙,绕了一圈又错误依旧!
引用
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
进一步尝试将数据库置为非归档模式,提示有保证的restore points。
引用
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
于是尝试闪回点删除
引用
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
STANDBY_20100517105118_1_1
STANDBY_20100518091959_1_1
STANDBY_20100520075557_1_1
STANDBY_20100520122225_1_1
SQL> drop restore point STANDBY_20100517105118_1_1;
drop restore point STANDBY_20100517105118_1_1
*
ERROR at line 1:
ORA-38701: Flashback database log 64 seq 64 thread 1:
"/Tbackup/MCSTAR/flashback/o1_mf_5z3sy2o2_.flb"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
NAME
--------------------------------------------------------------------------------
STANDBY_20100517105118_1_1
STANDBY_20100518091959_1_1
STANDBY_20100520075557_1_1
STANDBY_20100520122225_1_1
SQL> drop restore point STANDBY_20100517105118_1_1;
drop restore point STANDBY_20100517105118_1_1
*
ERROR at line 1:
ORA-38701: Flashback database log 64 seq 64 thread 1:
"/Tbackup/MCSTAR/flashback/o1_mf_5z3sy2o2_.flb"
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
检查操作系统闪回日志,可以看到此闪回日志不存在。
引用
[ora10g@mcprod udump]$ ls -l /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb
ls: /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb: No such file or directory
ls: /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb: No such file or directory
事情到这一步,只能拿出终极手段,重建controlfile,从理论上来讲,重建controlfile时只要Oracle redolog保持完整,可以不用resetlogs打开数据库,即数据不会丢失。重建过程中,数据库闪回功能将自动关闭
引用
SQL> alter database backup controlfile to trace;
Database altered.
Database altered.
使用以下脚本重建控制文件,需要注意的是需要手动添加tempfile
引用
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MCSTAR" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4674
LOGFILE
GROUP 4 '/oradata/mcstar/mcstar/redo04.log' SIZE 500M,
GROUP 5 '/oradata/mcstar/mcstar/redo05.log' SIZE 500M,
GROUP 6 '/oradata/mcstar/mcstar/redo06.log' SIZE 500M
-- STANDBY LOGFILE
-- GROUP 1 '/oradata/mcstar/mcstar/std01.log' SIZE 500M,
-- GROUP 2 '/oradata/mcstar/mcstar/std02.log' SIZE 500M,
-- GROUP 3 '/oradata/mcstar/mcstar/std03.log' SIZE 500M
DATAFILE
'/oradata/mcstar/mcstar/system01.dbf',
'/oradata/mcstar/mcstar/undotbs01.dbf',
'/oradata/mcstar/mcstar/sysaux01.dbf',
'/oradata/mcstar/mcstar/users01.dbf',
'/oradata/mcstar/mcstar/company.dbf',
'/oradata/mcstar/mcstar/streams.dbf',
'/oradata/mcstar/mcstar/zhou01.dbf',
'/oradata/mcstar/mcstar/mcdbra01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/mcstar/mcstar/temp01.dbf' REUSE;
CREATE CONTROLFILE REUSE DATABASE "MCSTAR" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4674
LOGFILE
GROUP 4 '/oradata/mcstar/mcstar/redo04.log' SIZE 500M,
GROUP 5 '/oradata/mcstar/mcstar/redo05.log' SIZE 500M,
GROUP 6 '/oradata/mcstar/mcstar/redo06.log' SIZE 500M
-- STANDBY LOGFILE
-- GROUP 1 '/oradata/mcstar/mcstar/std01.log' SIZE 500M,
-- GROUP 2 '/oradata/mcstar/mcstar/std02.log' SIZE 500M,
-- GROUP 3 '/oradata/mcstar/mcstar/std03.log' SIZE 500M
DATAFILE
'/oradata/mcstar/mcstar/system01.dbf',
'/oradata/mcstar/mcstar/undotbs01.dbf',
'/oradata/mcstar/mcstar/sysaux01.dbf',
'/oradata/mcstar/mcstar/users01.dbf',
'/oradata/mcstar/mcstar/company.dbf',
'/oradata/mcstar/mcstar/streams.dbf',
'/oradata/mcstar/mcstar/zhou01.dbf',
'/oradata/mcstar/mcstar/mcdbra01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/mcstar/mcstar/temp01.dbf' REUSE;
当然了,Oracle 10g已经做到足够人性化,在重建控制文件完成后会在alert日志,提示创建tempfile
引用
Sun Jun 13 08:28:31 2010
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
重建控制文件之后,顺利将数据打开,可以看到闪回自动关闭
引用
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
FLASHBACK_ON
------------------
NO
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 524BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 443Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 4262019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 789某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1368性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 480从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 1929数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 558Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 817LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1199“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1094在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 535问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 896即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 862查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3934操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 63811g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 763故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2494由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1683数据库中的log file sync等待事件指的是,当user ...
相关推荐
OrOracle 10g 闪回恢复区Oracle 10g 闪回恢复区Oracle 10g 闪回恢复区Oracle 10g 闪回恢复区Oracle 10g 闪回恢复区Oracle 10g 闪回恢复区Oracle 10g 闪回恢复区
oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回oracle 闪回
很好,实用的闪回技术实践,对深入学习oracle,玩转oracle,有很大帮助作用,《Oracle10G-闪回技术》实验手册,主要讲述10G之后相关主要闪回技术玩法,很经典,主要为实践居多
Oracle10G培训日志 是北京动力节点出的关于Oracle10G的培训内容,主要介绍使用Oracle10G
Oracle 10g闪回技术浅析.pdf
Oracle 10g闪回技术及实现.pdf
oracle10G和11G的OCI.dll,主要用于navicat工具;oracle10G和11G的OCI.dll,主要用于navicat工具
Oracle 10g 有一项新功能称为:自动的基于磁盘的备份与恢复( Automatic ...闪回恢复区是 Oracle 10g 中的新事物。简单的说,闪回恢复区是一块用以存储恢复相关的文件的存储空间。允许用户集中存储所有恢复相关的文件。
oracle,Oracle10g在Oracle10g上的安装与配置
Oracle10g-linux安装包
10g 回闪试验文档, 可以体验10g的回收功能。
oracle数据库驱动。包含了oracle oracle10g、11g、12c、19c的jar驱动包。解压密码是123456。
redhat5 下安装oracle10g redhat6下安装oracle11g 详细到装虚拟机,文件配置,安装详细过程
Linux下Oracle 10g安装 有不少网友需要,学习在Linux上从头安装 Oracle 数据库 10g 的基础知识(仅用于评估)。
racle10gjdbc驱动是一款的数据库驱动软件。大家在使用racle10gjdbc程序之前需要在电脑端安装这个驱动,...驱动介绍racle10gjdbc驱动程序,运用在java连接oracle10g。oracle10gjdbc驱动包存放位置为E:\orac,欢迎下载体验
中文名: Oracle 10g Client 快速安装包英文名: Oracle 10g Client别名: Oracle10g Client资源格式: 压缩包版本: Oracle10g地区: 大陆语言: 简体中文简介: Oracle10g Client的快速安装包,本人使用过几次,都是外出...
第1章 Oracle Database 10g体系结构 第2章 安装Oracle Database 10g和创建数据库 第3章 升级到Oracle Database 10g 第4章 规划Oracle应用程序——方法、风险和标准 第Ⅱ部分 SQL和SQL*Plus 第5章 SQL中的基本语法 第...
ORACLE10G安装步骤ORACLE10G安装步骤ORACLE10G安装步骤