[20150619]undo文件损坏或者丢失的恢复1.txt
--昨天别人问一些undo文件损坏或者丢失的恢复，实际上如果正常关机，undo文件丢失，恢复是很容易的。
--这些以前应该也做过，重复做1个记录：
1.测试环境：
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> insert into t values (14,'ddd');
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select * from t where id=14;
ID NAME
------------ --------------------
14 ddd
SCOTT@test> select us#,name from sys.undo$;
US# NAME
------------ --------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 _SYSSMU11$
12 _SYSSMU12$
13 _SYSSMU13$
14 _SYSSMU14$
15 _SYSSMU15$
16 _SYSSMU16$
17 _SYSSMU17$
18 _SYSSMU18$
19 _SYSSMU19$
20 _SYSSMU20$
21 _SYSSMU21$
22 _SYSSMU22$
23 _SYSSMU23$
24 _SYSSMU24$
25 _SYSSMU25$
26 _SYSSMU26$
27 _SYSSMU27$
28 _SYSSMU28$
29 _SYSSMU29$
30 _SYSSMU30$
31 _SYSSMU31$
32 _SYSSMU32$
33 _SYSSMU33$
34 _SYSSMU34$
35 _SYSSMU35$
36 _SYSSMU36$
37 _SYSSMU37$
38 _SYSSMU38$
39 _SYSSMU39$
40 _SYSSMU40$
41 _SYSSMU41$
42 _SYSSMU42$
43 _SYSSMU43$
44 _SYSSMU44$
45 _SYSSMU45$
46 _SYSSMU46$
47 _SYSSMU47$
48 _SYSSMU48$
49 _SYSSMU49$
50 rows selected.
SCOTT@test> show parameter undo
NAME TYPE VALUE
----------------- -------- --------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
2.做一个冷备份：
--步骤忽略
$ cd /mnt/ramdisk/test/
$ mv undotbs01.dbf undotbs01.dbf_org
3.开始测试：
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
MOUNTED
SYS@test> create pfile from spfile ;
File created.
--修改2行。
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
--再次启动:
SYS@test> startup pfile=//u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/mnt/ramdisk/test/undotbs01.dbf'
SYS@test> select open_mode from v$database ;
OPEN_MODE
----------
MOUNTED
SYS@test> alter database datafile 2 offline drop;
Database altered.
SYS@test> alter database open;
Database altered.
--OK正常启动。
SYS@test> select * from scott.t where id=14;
ID NAME
------------ --------------------
14 ddd
SYS@test> insert into scott.t values (15,'eee');
insert into scott.t values (15,'eee')
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
--无法在普通表空间做dml操作。
--要建立新的undo表空间：
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/mnt/ramdisk/test/undotbs02.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
4.修改参数undo_tablespace=UNDOTBS2.
SYS@test> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@test> startup nomount
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
SYS@test> alter system set undo_tablespace=UNDOTBS2 scope=spfile;
System altered.
SYS@test> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 260046936 bytes
Database Buffers 201326592 bytes
Redo Buffers 10498048 bytes
Database mounted.
Database opened.
SYS@test> insert into scott.t values (15,'eee');
1 row created.
SYS@test> commit ;
Commit complete.
SYS@test> drop tablespace undotbs1;
Tablespace dropped.
--OK,这样基本恢复完成了。