表空間TSPITR恢復-實驗

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(2222);

SCOTT@pdb1>select * from it;

        ID

----------

       9999

       8888

備份整個資料庫（原來有備份的情況，注意備份最佳化引數）

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

backup as compressed backupset incremental level 0  database format '/home/oracle/backup4/full_db_level0_%U_%T.bak' ;

sql 'alter system archive log current';

backup as compressed backupset archivelog all tag='arc_bak' format='/home/oracle/backup4/archivelog_%U_%T' skip inaccessible filesperset 6 not  backed up 1 times  delete input;

backup current controlfile format '/home/oracle/backup4/ctl_level0_%U_%T.bak';

backup spfile format '/home/oracle/backup4/spfile_level0_%U_%T.bak';

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

3、 刪除表空間pdb1:data

SYS@pdb1>drop tablespace data including contents and datafiles;

Tablespace dropped. 

SYS@pdb1>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

SYSTEM

SYSAUX

UNDOTBS1

TEMP01

4、 恢復表空間（透過alert.log刪除日誌，確定具體的刪除時間，以此來恢復）

2022-08-01T10:11:20.290861+08:00

2022-08-02T21:09:57.979142+08:00

TT00 (PID:57527): Error 12543 received logging on to the standby

2022-08-02T21:13:13.834835+08:00

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

2022-08-02T21:13:17.048894+08:00

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

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

21:09:572022-08-02T21:15:00.988777+08:00

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

備註：data表空間於2022-08-02T21:13:13.834835+08:00 刪除

恢復方法步驟：

①使PDB1處於mount狀態；

②rman基於時間點的恢復表空間；

具體操作：

SYS@pdb1>show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 PDB1                           READ WRITE NO

建立恢復臨時目錄：

[oracle@db1 ~]$ mkdir -p /home/oracle/aux

[oracle@db1 ~]$ ll /home/oracle/aux

使用命令：

recover tablespace pdb1:data until time "to_date('2022-08-02 21:13:13','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/aux';

備註：中間是恢復過程，文字太多，略，恢復過程中注意報錯資訊。

檢查恢復後data表空間狀態：

SYS@pdb1>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP01                         ONLINE

DATA                           OFFLINE

SYS@pdb1>alter tablespace data online;

SYS@pdb1>select owner,segment_name,bytes/1024 kb from dba_segments where segment_name='IT';

OWNER               SEGMENT_NAME          KB

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

SCOTT                     IT                   64

SYS@pdb1>select * from scott.it;

        ID

----------

      9999

      8888

總結：表空間的恢復，有2種方法，第一種是pitr基於時間點的恢復，這種恢復對現有的資料庫影響不大，但是有個限制條件是需要檢查表空間的關聯特性，涉及到哪些表空間，都需要恢復。

第二種：使用利用現有資料庫的備份，整個資料庫的回滾，如果這樣運算元據庫丟失的資料就很多了，不建議這麼做。

Yicheng16
22.08.02 

-- The End --