归档无备份，丢失redo和undo文件后，该如何恢复？
环境：
oracle 11.2 linux 系统， Archive Mode 无备份 。
试验方式：
1.t1表先插入几行数据，commit， 切换日志，生成一个归档日志文件。
2.再插入几行数据，commit，写入redo文件，
3.更新t1表记录，不做commit，undo段有写入的信息。
4.模拟数据丢失，删除redo和undo文件。
5.使用_allow_resetlogs_corruption 和 _corrupted_rollback_segment 隐含参数强行打开数据库。
结果打开数据库还是失败。提示Undo有问题，请大家给看看。
下面是操作步骤:
1.建立测试数据：
SQL> create table test01.t1(id number,name varchar2(30));
Table created.
SQL>insert into test01.t1 select rownum,username from dba_users where rownum commit;
Commit complete.
SQL> archive log list;
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
/u02/archive/aux
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
切换日志，生成一个归档日志文件
SQL> alter system switch logfile;
System altered.
SQL> insert into test01.t1 select rownum+4,username from dba_users where rownum select * from test01.t1;
ID NAME
---------- ------------------------------
1 TEST01
2 SCOTT
3 ORACLE_OCM
4 XS$NULL
5 TEST01
6 SCOTT
7 ORACLE_OCM
8 XS$NULL
8 rows selected.
commit,写入redo文件。
SQL> commit;
Commit complete.
SQL> update test01.t1 set name=name||'1';
8 rows updated.
SQL>select * from test01.t1;
ID NAME
---------- ------------------------------
1 TEST011
2 SCOTT1
3 ORACLE_OCM1
4 XS$NULL1
5 TEST011
6 SCOTT1
7 ORACLE_OCM1
8 XS$NULL1
8 rows selected.
2.模拟数据丢失，删除所有redo 文件和 undo文件。
[oracle@kmed aux]$ ll
total 1711792
drwxr-xr-x 2 oracle oinstall4096 Nov 28 10:16 aux_colbk
-rw-r----- 1 oracle asmadmin10076160 Dec 17 19:20 control01.ctl
-rw-r----- 1 oracle asmadmin10076160 Dec 17 19:20 control02.ctl
-rw-r----- 1 oracle asmadmin10076160 Dec 17 19:20 control03.ctl
-rw-r----- 1 oracle asmadmin 104865792 Dec 17 19:16 example01.dbf
-rw-r----- 1 oracle asmadmin52429312 Dec 17 19:19 redo01.log
-rw-r----- 1 oracle asmadmin52429312 Dec 17 19:19 redo02.log
-rw-r----- 1 oracle asmadmin52429312 Dec 17 19:16 redo03.log
-rw-r----- 1 oracle asmadmin 534781952 Dec 17 19:18 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 723525632 Dec 17 19:18 system01.dbf
-rw-r----- 1 oracle asmadmin30416896 Dec 17 19:18 temp01.dbf
-rw-r----- 1 oracle asmadmin10493952 Dec 17 19:18 test01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Dec 17 19:16 test02.dbf
-rw-r----- 1 oracle asmadmin 5251072 Dec 17 19:16 test03.dbf
-rw-r----- 1 oracle asmadmin 5251072 Dec 17 19:16 test04.dbf
-rw-r----- 1 oracle asmadmin 5251072 Dec 17 19:13 test05.dbf
-rw-r----- 1 oracle oinstall 110108672 Dec 17 19:16 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Dec 17 19:16 users01.dbf
[oracle@kmed aux]$ rm *.log
[oracle@kmed aux]$ rm undotbs*
3.abort数据库
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4.修改参数文件,修改undo 和增加2个隐含参数。
*.streams_pool_size=12M
*.undo_retention=3600
*.undo_tablespace='SYSTEM'
*.undo_management=manual
*.utl_file_dir='*'
#*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
"initaux.ora" 178L, 6383C written
5.启动数据库报01110错误。
[oracle@kmed dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 17 19:20:29 2010
Copyright (c) 1982, 2009, Oracle.All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area255270912 bytes
Fixed Size
1335836 bytes
Variable Size
213913060 bytes
Database Buffers 37748736 bytes
Redo Buffers
2273280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
SQL> col name format a30;
SQL> select name,status from v$datafile;
NAME
STATUS
------------------------------ -------
/u02/oradata/aux/system01.dbfSYSTEM
/u02/oradata/aux/sysaux01.dbfONLINE
/u02/oradata/aux/undotbs01.dbf ONLINE
/u02/oradata/aux/users01.dbf ONLINE
/u02/oradata/aux/example01.dbf ONLINE
/u02/oradata/aux/test01.dbfONLINE
/u02/oradata/aux/test02.dbfONLINE
/u02/oradata/aux/test03.dbfONLINE
/u02/oradata/aux/test04.dbfONLINE
9 rows selected.
6.offline undo 表空间，打开数据还是报错误。
SQL> alter database datafile '/u02/oradata/aux/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/aux/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
7.应用所有日志。
SQL> recover database until cancel;
ORA-00279: change 958435 generated at 12/17/2010 19:16:55 needed for thread 1
ORA-00289: suggestion : /u02/archive/aux/1_1_737931497.dbf
ORA-00280: change 958435 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 958834 generated at 12/17/2010 19:19:21 needed for thread 1
ORA-00289: suggestion : /u02/archive/aux/1_2_737931497.dbf
ORA-00280: change 958834 for thread 1 is in sequence #2
ORA-00278: log file '/u02/archive/aux/1_1_737931497.dbf' no longer needed for
this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u02/archive/aux/1_2_737931497.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
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: '/u02/oradata/aux/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 958834 generated at 12/17/2010 19:19:21 needed for thread 1
ORA-00289: suggestion : /u02/archive/aux/1_2_737931497.dbf
ORA-00280: change 958834 for thread 1 is in sequence #2
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: '/u02/oradata/aux/system01.dbf'
ORA-01112: media recovery not started
7.resetlogs 打开数据库，还是错误，
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
Process ID: 4698
Session ID: 1 Serial number: 5
8. alert日志文件中的信息：
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Fri Dec 17 19:24:40 2010
ARC3 started with pid=23, OS id=4811
Archived Log entry 2 added for thread 1 sequence 1 ID 0x5ed9315b dest 2:
Errors in file /u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
Errors in file /u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
Error 704 happened during db open, shutting down database
USER (ospid: 4803): terminating the instance due to error 704
Instance terminated by USER, pid = 4803
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (4803) as a result of ORA-1092
Fri Dec 17 19:24:42 2010
ORA-1092 : opitsk aborting process
9.trace 文件中的信息：
[oracle@kmed ~]$ vi /u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc
Trace file /u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/app/oracle
System name:Linux
Node name:kmed
Release:2.6.18-128.el5
Version:#1 SMP Wed Jan 21 10:44:23 EST 2009
Machine:i686
Instance name: aux
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4803, image: oracle@kmed (TNS V1-V3)
*** 2010-12-17 19:24:39.384
*** SESSION ID:(1.5) 2010-12-17 19:24:39.384
*** CLIENT ID:() 2010-12-17 19:24:39.384
*** SERVICE NAME:() 2010-12-17 19:24:39.384
*** MODULE NAME:(sqlplus@kmed (TNS V1-V3)) 2010-12-17 19:24:39.384
*** ACTION NAME:() 2010-12-17 19:24:39.384
Thread 1 checkpoint: logseq 1, block 2, scn 958838
start recovery at logseq 1, block 2, scn 958838
*** 2010-12-17 19:24:39.517
Started writing zeroblks thread 1 seq 1 blocks 3-10
*** 2010-12-17 19:24:39.518
Completed writing zeroblks thread 1 seq 1
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 0Kb in 0.13s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.000ea177 (958839)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
"/u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc" 92L, 3254C
1,1 Top
Trace file /u02/app/diag/rdbms/aux/aux/trace/aux_ora_4803.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/app/oracle
System name:Linux
Node name:kmed
Release:2.6.18-128.el5
Version:#1 SMP Wed Jan 21 10:44:23 EST 2009
Machine:i686
Instance name: aux
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4803, image: oracle@kmed (TNS V1-V3)
*** 2010-12-17 19:24:39.384
*** SESSION ID:(1.5) 2010-12-17 19:24:39.384
*** CLIENT ID:() 2010-12-17 19:24:39.384
*** SERVICE NAME:() 2010-12-17 19:24:39.384
*** MODULE NAME:(sqlplus@kmed (TNS V1-V3)) 2010-12-17 19:24:39.384
*** ACTION NAME:() 2010-12-17 19:24:39.384
Thread 1 checkpoint: logseq 1, block 2, scn 958838
start recovery at logseq 1, block 2, scn 958838
*** 2010-12-17 19:24:39.517
Started writing zeroblks thread 1 seq 1 blocks 3-10
*** 2010-12-17 19:24:39.518
Completed writing zeroblks thread 1 seq 1
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 0Kb in 0.13s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.000ea177 (958839)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
1,1 Top
*** 2010-12-17 19:24:39.528
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
*** 2010-12-17 19:24:39.529
Completed redo application of 0.00MB
*** 2010-12-17 19:24:39.529
Completed recovery checkpoint
IR RIA: redo_size 0 bytes, time_taken 11 ms
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
Recovery sets nab of thread 1 seq 1 to 3 with 8 zeroblks
*** 2010-12-17 19:24:41.217
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 3 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 3 csec) -----
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/aux/undotbs01.dbf'
*** 2010-12-17 19:24:41.246
USER (ospid: 4803): terminating the instance due to error 704
|
