运行数据库，丢失所有redo文件，恢复数据库-CSDN博客

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
1 ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo01.log

1 ONLINE
/oradata/hb130000/redo01.rdo

3 ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo03.log

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/oradata/hb130000/redo03.rdo

2 ONLINE
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/redo02.log

2 ONLINE
/oradata/hb130000/redo02.rdo

6 rows selected.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
$ pwd
/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000
$ rm *.log
$ cd /oradata/hb130000/
$ rm *.rdo
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 23 14:52:50 2006

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-01012: not logged on
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 420970696 bytes
Fixed Size 741576 bytes
Variable Size 369098752 bytes
Database Buffers 50331648 bytes
Redo Buffers 798720 bytes
Database mounted.
SQL> recover database untile cancel;
ORA-00905: missing keyword

SQL> recover database until cancel;
ORA-00279: change 75299056 generated at 06/23/2006 14:51:40 needed for thread 1
ORA-00289: suggestion : /oradata/rmanbackup/archivehb130000_1_4.dbf
ORA-00280: change 75299056 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/hb130000/system01.dbf'

ORA-01112: media recovery not started

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00328: archived log ends at change 74932410, need later change 75299056
ORA-00334: archived log: '/oradata/rmanbackup/archivehb130000_1_4.dbf'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/hb130000/system01.dbf'

vi pfile，增加一行

*.aq_tm_processes=1
*.background_dump_dest='/oracle/oracle/app/oracle/admin/hb130000/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb130000/control01.ctl','/oracle/oracle/app/oracle/produc
/9.2.0.1/dbs/hb130000/control02.ctl','/oradata/hb130000/control03.ctl'
*.core_dump_dest='/oracle/oracle/app/oracle/admin/hb130000/cdump'
*.db_4k_cache_size=10
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain='hecz.cn'
*.db_file_multiblock_read_count=16
*.db_name='hb130000'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hb130000XDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='hb130000'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/oradata/rmanbackup/archive'
*.log_archive_format='hb130000_%t_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=419430400
*.shared_pool_size=117440512
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/oracle/app/oracle/admin/hb130000/udump'
*.utl_file_dir='/oradata/logminer'
*._allow_resetlogs_corruption=true 增加一行"pfile.ora" 37 lines, 1353 characters
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 23 14:56:36 2006

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=/oracle/pfile.ora
ORACLE instance started.

Total System Global Area 420970696 bytes
Fixed Size 741576 bytes
Variable Size 369098752 bytes
Database Buffers 50331648 bytes
Redo Buffers 798720 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL>

如果resetlogs方式打开有问题，在alert文件中有ORA-00600 [2662]信息，

Fri Jun 23 16:17:25 2006
Errors in file /u01/app/oracle/admin/test2k11/udump/test2k11_ora_2853.trc:
ORA-00600: internal error code, arguments: [2662], [0], [315631697], [0], [315781153], [8388617], [], []
Fri Jun 23 16:17:26 2006
Errors in file /u01/app/oracle/admin/test2k11/udump/test2k11_ora_2853.trc:
ORA-00600: internal error code, arguments: [2662], [0], [315631697], [0], [315781153], [8388617], [], []

就这样：

SQL> shutdown;
SQL>startup mount pfile='/tmp/init.ora';
SQL>ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1';
SQL>alter database open;

1：ORA-600 [2662] "Block SCN is ahead of Current SCN"，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了.

2：于是想到使用ADJUST_SCN事件来调整当前的SCN，使其大于dependent SCN.

3：此时我们可以通过Oracle的内部事件来调整SCN:

调整SCN有两种常用方法:

1.通过immediate trace name方式(在数据库Open状态下)

alter session set events 'IMMEDIATE trace name ADJUST_SCN level x';

2.通过10015事件(在数据库无法打开，mount状态下)

alter session set events '10015 trace name adjust_scn level x';

注:level 1为增进SCN 10亿 (1 billion) (1024*1024*1024),通常Level 1已经足够。也可以根据实际情况适当调整。

楼主的例子由于数据库无法打开，只能使用第二种方法。

http://www.itpub.net/showthread.php?s=&threadid=576771&perpage=10&pagenumber=1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/620862/viewspace-862277/，如需转载，请注明出处，否则将追究法律责任。