熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

數據庫備份與恢復測試(8)

2013-11-13 15:48:52  來源: Oracle 

  ######################################################################################
#                                []回滾段恢復                                      #
######################################################################################

  參見

################################
# []正常shutdown回滾數據文件丟失
#       有備份回滾段恢復
################################

  SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL> archive log list
數據庫日志模式            存檔模式
自動存檔             啟用
存檔終點            C:\oracle\oradata\testdb\archive
最早的概要日志序列    
下一個存檔日志序列  
當前日志序列          

  SQL> select us# name status$ from undo$;

  US# NAME                              STATUS$

         SYSTEM                                 
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
        _SYSSMU$                             

  已選擇

  SQL> select SEGMENT_NAMESTATUS from dba_rollback_segs;

  SEGMENT_NAME                   STATUS

SYSTEM                         ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                     ONLINE

  已選擇

  RMAN>  backup format c:\noarch_%s%pbak database;

  啟動 backup 於
使用通道 ORA_DISK_
通道 ORA_DISK_: 正在啟動 full 數據文件備份集
通道 ORA_DISK_: 正在指定備份集中的數據文件
在備份集中包含當前的 SPFILE
備份集中包括當前控制文件
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\SYSTEMDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\INDXDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\USERSDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\TOOLSDBF
通道 ORA_DISK_: 正在啟動段
通道 ORA_DISK_: 已完成段
段 handle=C:\NOARCH_BAK comment=NONE
通道 ORA_DISK_: 備份集已完成 經過時間:::
完成 backup 於

  RMAN>

  RMAN>

  RMAN> copy current controlfile to c:\currcontctl;

  啟動 copy 於
使用通道 ORA_DISK_
通道 ORA_DISK_: 已復制當前的控制文件
輸出文件名=C:\CURRCONTCTL
完成 copy 於

  RMAN> exit

  恢復管理器完成

  SQL> create table arch (status varchar()) tablespace users;

  表已創建

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select ok from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> insert into arch select * from arch;

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> commit;

  提交完成

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select no from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> select GROUP#SEQUENCE#archived STATUS from v$log;

  GROUP#  SEQUENCE# ARC STATUS

                   NO  CURRENT
                   YES ACTIVE
                   NO  ACTIVE

  SQL> shutdown  >>這裡是正常shutdown所以會做檢查點當前的數據文件不需要恢復狀態
數據庫已經關閉
已經卸載數據庫
ORACLE 例程已經關閉

  ~~~~~~~~~~~~~~~~~~~~~~~~
模擬刪除UNDOTBSDBF
這時其他文件都是一致性的
只需要UNDOTBSDBF恢復
~~~~~~~~~~~~~~~~~~~~~~~~
刪除UNDOTBSDBF
還原一個UNDOTBSDBF的備份

  SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
ORA: 文件 需要介質恢復
ORA: 數據文件 : C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF

  SQL> recover database ;
完成介質恢復

  SQL> alter database open;

  數據庫已更改

  SQL> select count(*)status from arch group by status;

  COUNT(*) ST

      no
     ok
    
    
################################
# []shutdown abort(或者使用了別的老的數據文件)
#       回滾數據文件丟失有備份回滾段恢復
#       數據庫沒有活動事務
################################

也能完全恢復
~~~~~~~~~~~~~~~~~~~~~~
雖然是abort但是只是需要做一個檢查點
將數據寫到數據文件中但這部分數據直接
在在線日志中可以拿到了只需要前滾
不需要進行回滾
~~~~~~~~~~~~~~~~~~~~~~

  ################################
# []shutdown abort(或者使用了別的老的數據文件)
#       回滾數據文件丟失有備份回滾段恢復
#       數據庫有活動事務
################################

  SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL> archive log list
數據庫日志模式            存檔模式
自動存檔             啟用
存檔終點            C:\oracle\oradata\testdb\archive
最早的概要日志序列    
下一個存檔日志序列  
當前日志序列          

  RMAN>  backup format c:\noarch_%s%pbak database;

  啟動 backup 於
使用通道 ORA_DISK_
通道 ORA_DISK_: 正在啟動 full 數據文件備份集
通道 ORA_DISK_: 正在指定備份集中的數據文件
在備份集中包含當前的 SPFILE
備份集中包括當前控制文件
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\SYSTEMDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\INDXDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\USERSDBF
輸入數據文件 fno= name=C:\ORACLE\ORADATA\TESTDB\TOOLSDBF
通道 ORA_DISK_: 正在啟動段
通道 ORA_DISK_: 已完成段
段 handle=C:\NOARCH_BAK comment=NONE
通道 ORA_DISK_: 備份集已完成 經過時間:::
完成 backup 於

  RMAN>

  RMAN>

  RMAN> copy current controlfile to c:\currcontctl;

  啟動 copy 於
使用通道 ORA_DISK_
通道 ORA_DISK_: 已復制當前的控制文件
輸出文件名=C:\CURRCONTCTL
完成 copy 於

  RMAN> exit

  恢復管理器完成

  SQL> create table arch (status varchar()) tablespace users;

  表已創建

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select ok from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> insert into arch select * from arch;

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> commit;

  提交完成

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select no from dba_objects; 這裡沒有commit

  已創建

  提交完成

  SQL> select GROUP#SEQUENCE#archived STATUS from v$log;

  GROUP#  SEQUENCE# ARC STATUS

                   NO  CURRENT
                   YES ACTIVE
                   NO  ACTIVE

  SQL> shutdown  abort>>這裡是正常shutdown所以會做檢查點當前的數據文件不需要恢復狀態
數據庫已經關閉
已經卸載數據庫
ORACLE 例程已經關閉

  ~~~~~~~~~~~~~~~~~~~~~~~~
模擬刪除UNDOTBSDBF
這時其他文件都是一致性的
只需要UNDOTBSDBF恢復
~~~~~~~~~~~~~~~~~~~~~~~~
刪除UNDOTBSDBF
還原一個UNDOTBSDBF的備份

  SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
ORA: 文件 需要介質恢復
ORA: 數據文件 : C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF

  SQL> recover database ;
完成介質恢復

  SQL> alter database open;

  數據庫已更改

  SQL> select count(*)status from arch group by status;

  COUNT(*) ST

     ok 
    
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
i如果有活動的事務那麼恢復回滾表空間時
在前滾後後滾時要讀自己回滾段裡的數據
而自己的數據文件已經損壞了所以就不允許的
所以在mount前將初始化參數
rollback_segments=(system)
_corrupted_rollback_segments=(rbsrbsrbs……)
I測試中回撤表空間數據文件恢復跟常規表空間恢復一樣
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  

  ################################
# []shutdown abort(或者使用了別的老的數據文件)
#       回滾數據文件丟失沒有備份回滾段恢復
#       數據庫有活動事務
################################
SQL> create table arch (status varchar()) tablespace users;

  表已創建

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select ok from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> insert into arch select * from arch;

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> commit;

  提交完成

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select no from dba_objects;

  已創建

  SQL> shutdown abort
ORACLE 例程已經關閉
SQL>
丟失undo文件
SQL> startup mount;
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes

  SQL> alter database datafile C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF offline;

  數據庫已更改

  SQL> alter database open;

  數據庫已更改

  SQL> select US#NAMEts#STATUS$FLAGS from undo$ where STATUS$ <> ;

  US# NAME                                  TS#    STATUS$      FLAGS

         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
        _SYSSMU$                                       
        _SYSSMU$                                       

  SQL> select count(*) from arch;  這裡表是不能讀取一致性了
select count(*) from arch
                     *
ERROR 位於第 行:
ORA: ????????
ORA: ???? : C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF

  SQL> shutdown
數據庫已經關閉
已經卸載數據庫
ORACLE 例程已經關閉
SQL>

  編輯INITtestdb_undoORA
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$)
_offline_rollback_segments=true

  SQL> startup pfile=C:\oracle\ora\database\INITtestdb_undoORA;
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes

  SQL> alter database open;

  數據庫已更改

  SQL> select count(*) from arch;

  COUNT(*)

   

  再創建新的undo表空間
drop 壞的undo tablespace

  最好做一個整庫的導出並導入到新系統

  ################################
# []shutdown abort(或者使用了別的老的數據文件)
#       回滾數據文件丟失沒有備份但控制文件有創建
#       該回撤段數據文件的所有歷史日志回滾段恢復
#       數據庫有活動事務
################################
SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL> show parameter undo

  NAME                                 TYPE        VALUE

undo_management                      string      AUTO
undo_retention                       integer    
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS
SQL> alter tablespace UNDOTBS add datafile C:\ORACLE\ORADATA\TESTDB\UNDOTBS
DBF size m;

  表空間已更改

  SQL>
SQL>
SQL> create table arch (status varchar()) tablespace users;

  表已創建

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select ok from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> insert into arch select * from arch;

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> commit;

  提交完成

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select no from dba_objects;

  已創建

  SQL> SHUTDOWN ABORT
ORACLE 例程已經關閉

  刪除UNDOTBS

  SQL> STARTUP
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
ORA: 無法標識/鎖定數據文件 請參閱 DBWR 跟蹤文件
ORA: 數據文件 : C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF

  SQL> ALTER DATABASE CREATE DATAFILE C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF;

  數據庫已更改

  SQL> RECOVER DATABASE;
完成介質恢復
SQL> ALTER DATABASE OPEN;

  數據庫已更改

  SQL> COL NAME FORMAT A;
SQL> SELECT NAMESTATUS FROM V$DATAFILE;

  NAME                                               STATUS

C:\ORACLE\ORADATA\TESTDB\SYSTEMDBF              SYSTEM
C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF             ONLINE
C:\ORACLE\ORADATA\TESTDB\INDXDBF                ONLINE
C:\ORACLE\ORADATA\TESTDB\TOOLSDBF               ONLINE
C:\ORACLE\ORADATA\TESTDB\USERSDBF               ONLINE
C:\ORACLE\ORADATA\TESTDB\UNDOTBSDBF             ONLINE

  已選擇

  ################################
# []數據庫打開時回撤表空間文件
#       損壞沒有備份
################################

  SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL> create table arch (status varchar()) tablespace users;

  表已創建

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select ok from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> insert into arch select * from arch;

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> /

  已創建

  SQL> commit;

  提交完成

  SQL> alter system switch logfile;

  系統已更改

  SQL> insert into arch select no from dba_objects;

  已創建

  SQL> commit;

  提交完成

  SQL> select GROUP#SEQUENCE#archived STATUS from v$log;

  GROUP#  SEQUENCE# ARC STATUS

                   NO  CURRENT
                   YES INACTIVE
                   YES ACTIVE

  SQL>
SQL>
SQL>
SQL> select GROUP#SEQUENCE#archived STATUS from v$log;

  GROUP#  SEQUENCE# ARC STATUS

                   NO  CURRENT
                   YES INACTIVE
                   YES ACTIVE

  SQL> select us# name status$ from undo$;

  US# NAME                              STATUS$

         SYSTEM                                 
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
        _SYSSMU$                             

  已選擇

  SQL> select SEGMENT_NAMESTATUS from dba_rollback_segs;

  SEGMENT_NAME                   STATUS

SYSTEM                         ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                      ONLINE
_SYSSMU$                     ONLINE

  已選擇

  SQL> show parameter undo

  NAME                                 TYPE        VALUE

undo_management                      string      AUTO
undo_retention                       integer    
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS

  ~~~~~~~~~~~~~~~~~~~~~
這裡模擬回撤數據文件損壞
~~~~~~~~~~~~~~~~~~~~~
用ultraedit破壞UNDOTBSDBF

  SQL> shutdown 因為要使用回滾但回滾失敗
ORA: ORACLE 服務器會話因致命錯誤而終止

  SQL> startup
ORA: 未初始化服務句柄
ORA: 內部錯誤hostdef 擴展名不存在
SQL> exit
從Oraclei Enterprise Edition Release Production
With the Partitioning OLAP and Oracle Data Mining options
JServer Release Production中斷開

  C:\Documents and Settings\lifengfang>sqlplus sys/sunsdl as sysdba

  SQL*Plus: Release Production on 星期五 ::

  Copyright (c) Oracle Corporation  All rights reserved

  已連接
SQL> startup
ORA: cannot start alreadyrunning ORACLE shut it down first
SQL> shutdown
ORA: shutdown in progress connection is not permitted
SQL> shutdown abort  只有abort才可以關閉數據庫因為不需要回滾
ORACLE 例程已經關閉
SQL> startup
ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL> select SEGMENT_NAMESTATUS from dba_rollback_segs;

  SEGMENT_NAME                   STATUS

SYSTEM                         ONLINE
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                      NEEDS RECOVERY
_SYSSMU$                     NEEDS RECOVERY

  SQL> select us# name status$ from undo$;

  US# NAME                              STATUS$

         SYSTEM                                 
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
         _SYSSMU$                              
        _SYSSMU$                             

  已選擇

  ~~~~~~~~~~~~~~~~~~~~~
這裡我們注意能夠打開數據庫
但是這裡狀態是需要恢復
~~~~~~~~~~~~~~~~~~~~~~
SQL> create pfile=c:\undopfileora from spfile;

  文件已創建

  SQL> create undo tablespace UNDOTBS datafile C:\oracle\oradata\testdb\UNDOTBDBF size m;

  表空間已創建

  SQL> select count(*)status from arch group by status;

  COUNT(*) ST

      no
     ok
    
SQL> insert into arch select af from dba_objects;
insert into arch select af from dba_objects
            *
ERROR 位於第 行:
ORA: 非系統表空間USERS無法使用系統回退段  

  
SQL> shutdown
數據庫已經關閉
已經卸載數據庫
ORACLE 例程已經關閉

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
編輯c:\undopfileora
將設置*undo_tablespace=UNDOTBS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> startup pfile=c:\undopfileora

  ORACLE 例程已經啟動

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
數據庫裝載完畢
數據庫已經打開
SQL>  insert into arch select af from dba_objects;

  已創建

  SQL> select
          trunc(id/power()) rbs
          bitand(idto_number(ffffxxxx))+ slot
          id seq
          lmoderequest
     from v$lockv$session
    where v$locktype = TX
      and v$locksid = v$sessionsid
      and v$sessionusername is not null;

  RBS       SLOT        SEQ      LMODE    REQUEST

                                               
       

  SQL> select SEGMENT_NAMETABLESPACE_NAMESTATUS from dba_rollback_segs;

  SEGMENT_NAME                   TABLESPACE_NAME                STATUS

SYSTEM                         SYSTEM                         ONLINE
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                      UNDOTBS                       NEEDS RECOVERY
_SYSSMU$                     UNDOTBS                       NEEDS RECOVERY

  SEGMENT_NAME                   TABLESPACE_NAME                STATUS

_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE
_SYSSMU$                     UNDOTBS                       ONLINE 

  SQL> select US#NAMEts#STATUS$FLAGS from undo$;

  US# NAME                                  TS#    STATUS$      FLAGS

         SYSTEM                                           
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
         _SYSSMU$                                        
        _SYSSMU$                                       

  US# NAME                                  TS#    STATUS$      FLAGS

        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                          插入數據的回滾信息在這
        _SYSSMU$                                       
        _SYSSMU$                                       
        _SYSSMU$                                       

  已選擇

  這些非online的回撤段也需要在初始化參數中強制_corrupted_rollback_segments
且_offline_rollback_segments=true

  這裡跟一樣設置初始化文件
編輯INITtestdb_undoORA
*undo_management=AUTO
*undo_retention=
*undo_tablespace=UNDOTBS
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$_SYSSMU$)
_offline_rollback_segments=true

  然後打開數據庫

  看看TOM的例子
sys@ORAUSORACLECOM> shutdown
Database closed
Database dismounted
ORACLE instance shut down

  sys@ORAUSORACLECOM> exit
Disconnected from Oraclei Enterprise Edition Release Production
With the Partitioning OLAP and Oracle Data Mining options
JServer Release Production

  [ora@tkytepcisdn ora]$ ls
controlctl  cwmlitedbf  indxdbf  redolog  redoBlog    tempdbf  
  usersdbf
controlctl  drsysdbf    odmdbf   redolog  redoClog    toolsdbf 
  xdbdbf
controlctl  exampledbf  redolog  redoAlog  systemdbf 
undotbsdbf

  [ora@tkytepcisdn ora]$ mv undotbsdbf undotbsdbfxxx

  [ora@tkytepcisdn ora]$ sqlplus /nolog

  SQL*Plus: Release Production on Sat Sep ::

  Copyright (c) Oracle Corporation  All rights reserved

  idle> connect / as sysdba
Connected to an idle instance
idle> startup
ORACLE instance started

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size             bytes
Database Buffers           bytes
Redo Buffers                 bytes
Database mounted
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file :
/usr/oracle/ora/OraHome/oradata/ora/undotbsdbf

  idle> show parameter undo

  NAME                                 TYPE        VALUE

undo_management                      string      AUTO
undo_retention                       integer    
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS

  idle> alter system set undo_management = manual scope=spfile;

  System altered

  Note: disabling the undo tablespace here thisll let us go back to rollback
segments and well use the system RBS to get back on our feet

  idle> shutdown
ORA: database not open

  Database dismounted
ORACLE instance shut down

  idle> exit
Disconnected from Oraclei Enterprise Edition Release Production
With the Partitioning OLAP and Oracle Data Mining options
JServer Release Production

  [ora@tkytepcisdn ora]$ !sql
sqlplus /nolog

  SQL*Plus: Release Production on Sat Sep ::

  Copyright (c) Oracle Corporation  All rights reserved

  idle> connect / as sysdba;
Connected to an idle instance
idle> startup
ORACLE instance started

  Total System Global Area  bytes
Fixed Size                   bytes
Variable Size             bytes
Database Buffers           bytes
Redo Buffers                 bytes
Database mounted
ORA: cannot identify/lock data file see DBWR trace file
ORA: data file :
/usr/oracle/ora/OraHome/oradata/ora/undotbsdbf

  Note: we still have to get rid of that thing

  idle> alter database datafile 
/usr/oracle/ora/OraHome/oradata/ora/undotbsdbf
    offline drop;

  Database altered

  idle> alter database open;

  Database altered

  idle> drop tablespace undotbs;

  Tablespace dropped

  idle> create UNDO tablespace undotbs
    datafile /usr/oracle/ora/OraHome/oradata/ora/undotbsdbf size
m
    autoextend on next m maxsize m;

  Tablespace created

  idle> alter system set undo_management = auto scope=spfile;

  System altered

  Now weve put the undo tablespace back by creating a new one  Just
bounce

  idle> shutdown
Database closed
Database dismounted
ORACLE instance shut down
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    


From:http://tw.wingwit.com/Article/program/Oracle/201311/17338.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.