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 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