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