[20150619]undo檔案損壞或者丟失的恢復1

[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,這樣基本恢復完成了。