Oracle 10g 闪回日志引发的血案 - Dbcooker's blog -
来源:百度文库 编辑:神马文学网 时间:2024/03/29 14:08:06
Oracle 10g 闪回日志引发的血案
文章分类:数据库事情来源于对目标库创建catalog,由ORA-00257可知,目标库归档已满,于是清理归档。引用$ 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.
归档清理完成之后,再次创建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
查看目标库session参数,此参数为Oracle创建时默认参数,默认为(1.1 * PROCESSES) + 5。和processes一样不可动态修改。
引用SQL> show parameter sessions
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
于是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
考虑到该目标库为测试库,暂时关闭闪回影响不大,再次尝试打开数据库,错误依然,事情不妙!
引用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
再次尝试将数据库置为闪回模式时,报数据库需要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
进一步尝试将数据库置为非归档模式,提示有保证的restore points。
引用SQL> alter database noarchivelog;
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
检查操作系统闪回日志,可以看到此闪回日志不存在。
引用[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
事情到这一步,只能拿出终极手段,重建controlfile,从理论上来讲,重建controlfile时只要Oracle redolog保持完整,可以不用resetlogs打开数据库,即数据不会丢失。重建过程中,数据库闪回功能将自动关闭
引用SQL> alter database backup controlfile to trace;
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;
当然了,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
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
Oracle 10g 闪回日志引发的血案 - Dbcooker's blog -
Oracle 10g 闪回日志引发的血案 - Dbcooker's blog - Java...
[Oracle 10g] 闪回恢复区 (Flash Recovery Area)
一头猪引发的血案
eygle‘s Oracle Blog: 靓颖来公司的访谈-靓颖靓照
十个兔子引发的血案
十个兔子引发的血案
校园血案引发的思考
十个兔子引发的血案
《一个馒头引发的血案》
假种子引发的血案
Oracle 10G和Oracle 11G有什么分别?
oracle 10g中的ROWID
ORACLE 10g 安装教程
oracle 10g 行列转换
ORACLE 10g 安装教程
oracle 10g 中单个数据表的导入、导出
修改Oracle 10g Express Edition的字符集()
详解 Oracle 10g 中 MERGE 功能的增强
Oracle 10g数据库的最大容量 - magic007的Oracle空间 - ITPU...
Oracle 11g 闪回数据归档 | dbtan 谈DB
Oracle 10g绿色客户端 plus PL/SQL Developer-搭建方便的Oracle客户端使用环境
Oracle 10g绿色客户端 plus PL/SQL Developer-搭建方便的Oracle客户端使用
一份“山寨版”奏稿引发的惊天血案 - Qzone日志