【Oracle 恢復表空間】 實驗

1、 資料庫版本

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

2、建立表空間 pdb1:data，錄入資料 scott.it表 8888

SYS@pdb1>create tablespace data datafile ‘/oradata/ORCL/pdb1/data01.dbf’ size 100m autoextend on maxsize 2g;

SYS@pdb1>alter user scott default tablespace data;

SYS@pdb1>conn scott/oracle@pdb1

Connected.

SCOTT@pdb1>insert into it values(3333);

16:33:39 SYS@pdb1>select * from scott.it;

        ID

----------

       999

       888

      1111

      2222

      3333

備份庫使用上一節備份資料；

3、刪除 PDB1 data表空間

16:34:09 SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           ONLINE

16:36:22 SYS@pdb1>drop tablespace data including contents and datafiles;

Tablespace dropped.

16:36:41 SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

 

檢視後臺 alert.log日誌

2022-08-06T16:36:38.296678+08:00

PDB1(3):drop tablespace data including contents and datafiles

2022-08-06T16:36:41.586729+08:00

PDB1(3):Deleted file /oradata/ORCL/pdb1/data.dbf

PDB1(3):Completed: drop tablespace data including contents and datafiles

4、恢復表空間及資料

關閉整個資料庫，恢復備份控制檔案，並啟動到 mount狀態；

[oracle@db1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 6 19:36:23 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/home/oracle/backup/ctl_level0_ov14gmkv_1_1_20220806.bak';

Starting restore at 06-AUG-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=379 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oradata/ORCL/control01.ctl

output file name=/flash_recovery_area/ORCL/control02.ctl

Finished restore at 06-AUG-22

RMAN> alter database mount;

run{

set until time "to_date('2022-08-06 19:32:43','yyyy-mm-dd hh24:mi:ss')";

restore database ;

recover database;

}

過程略：

Starting recover at 06-AUG-22

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /flash_recovery_area/ORCL/archivelog/2022_08_06/o1_mf_1_2_kgwn0rtj_.arc

archived log for thread 1 with sequence 3 is already on disk as file /oradata/ORCL/redo03.log

archived log file name=/flash_recovery_area/ORCL/archivelog/2022_08_06/o1_mf_1_2_kgwn0rtj_.arc thread=1 sequence=2

archived log file name=/oradata/ORCL/redo03.log thread=1 sequence=3

media recovery complete, elapsed time: 00:00:01

Finished recover at 06-AUG-22

SYS@orcl>alter database open resetlogs;

Database altered.

SYS@orcl>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

檢查表空間及資料：

SYS@pdb1>select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           ONLINE

SYS@pdb1>select file_name from dba_data_files;

FILE_NAME

-------------------------------------------------------------------------------

/oradata/ORCL/pdb1/undotbs01.dbf

/oradata/ORCL/pdb1/sysaux01.dbf

/oradata/ORCL/pdb1/system01.dbf

/oradata/ORCL/pdb1/data.dbf

SYS@pdb1>select * from scott.it;

        ID

----------

       999

       888

      1111

      2222

      3333

備註：到此為止，表空間及資料都得以恢復！產生了性的 incarnation，做一個全備份吧。

 

總結：表空間認為的刪除或者丟失，都會造成資料庫無法啟動或者出現其他的報錯，此種操作方法如果在資料及歸檔日誌完成情況下，不會造成資料丟失，操作時需要小心謹慎，否則可能造成資料丟失。

參考 weixin_39582569 第二種方法沒有做成功。

Yicheng16
22.08.06

-- The End --