事情来源于对目标库创建catalog,由ORA-00257可知,目标库归档已满,于是清理归档。
[quote]$ 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.[/quote]
归档清理完成之后,再次创建catalog时,出现ORA-00018
[quote]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[/quote]
查看目标库session参数,此参数为Oracle创建时默认参数,默认为(1.1 * PROCESSES) + 5。和processes一样[color=red]不可动态修改。[/color]
[quote]SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 170[/quote]
于是进一步查看其alert日志
[quote]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[/quote]
于是abort oracle,open oracle时错误出现:
[quote]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[/quote]
考虑到该目标库为测试库,暂时关闭闪回影响不大,再次尝试打开数据库,错误依然,事情不妙!
[quote]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[/quote]
再次尝试将数据库置为闪回模式时,报数据库需要recover,此错误很正常,因为我以abort模式关闭Oracle,于是手工recover database;事情走向越来越不妙,绕了一圈又错误依旧!
[quote]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
[/quote]
进一步尝试将数据库置为非归档模式,提示有保证的restore points。
[quote]SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points[/quote]
于是尝试闪回点删除
[quote]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[/quote]
检查操作系统闪回日志,可以看到此闪回日志不存在。
[quote][ora10g@mcprod udump]$ ls -l /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb
ls: /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb: [color=red]No such file or directory[/color][/quote]
事情到这一步,只能拿出终极手段,重建controlfile,从理论上来讲,重建controlfile时只要Oracle redolog保持完整,可以不用resetlogs打开数据库,即数据不会丢失。重建过程中,数据库闪回功能将自动关闭
[quote]SQL> alter database backup controlfile to trace;
Database altered.[/quote]
使用以下脚本重建控制文件,需要注意的是需要手动添加tempfile
[quote]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;
[color=red]ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/mcstar/mcstar/temp01.dbf' REUSE;[/color][/quote]
当然了,Oracle 10g已经做到足够人性化,在重建控制文件完成后会在alert日志,提示创建tempfile
[quote]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
*********************************************************************[/quote]
重建控制文件之后,顺利将数据打开,可以看到闪回自动关闭
[quote]SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO[/quote]
[quote]$ 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.[/quote]
归档清理完成之后,再次创建catalog时,出现ORA-00018
[quote]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[/quote]
查看目标库session参数,此参数为Oracle创建时默认参数,默认为(1.1 * PROCESSES) + 5。和processes一样[color=red]不可动态修改。[/color]
[quote]SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sessions integer 170[/quote]
于是进一步查看其alert日志
[quote]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[/quote]
于是abort oracle,open oracle时错误出现:
[quote]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[/quote]
考虑到该目标库为测试库,暂时关闭闪回影响不大,再次尝试打开数据库,错误依然,事情不妙!
[quote]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[/quote]
再次尝试将数据库置为闪回模式时,报数据库需要recover,此错误很正常,因为我以abort模式关闭Oracle,于是手工recover database;事情走向越来越不妙,绕了一圈又错误依旧!
[quote]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
[/quote]
进一步尝试将数据库置为非归档模式,提示有保证的restore points。
[quote]SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points[/quote]
于是尝试闪回点删除
[quote]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[/quote]
检查操作系统闪回日志,可以看到此闪回日志不存在。
[quote][ora10g@mcprod udump]$ ls -l /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb
ls: /Tbackup/MCSTAR/flashback/o1_mf_60tv013y_.flb: [color=red]No such file or directory[/color][/quote]
事情到这一步,只能拿出终极手段,重建controlfile,从理论上来讲,重建controlfile时只要Oracle redolog保持完整,可以不用resetlogs打开数据库,即数据不会丢失。重建过程中,数据库闪回功能将自动关闭
[quote]SQL> alter database backup controlfile to trace;
Database altered.[/quote]
使用以下脚本重建控制文件,需要注意的是需要手动添加tempfile
[quote]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;
[color=red]ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/mcstar/mcstar/temp01.dbf' REUSE;[/color][/quote]
当然了,Oracle 10g已经做到足够人性化,在重建控制文件完成后会在alert日志,提示创建tempfile
[quote]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
*********************************************************************[/quote]
重建控制文件之后,顺利将数据打开,可以看到闪回自动关闭
[quote]SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO[/quote]