[20150619]undo文件损坏或者丢失的恢复1-CSDN博客

[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,这样基本恢复完成了。