一朋友公司環境，因測試庫、生產庫錯連，造成大批量資料丟失，通過Oracle資料庫回收站方式已找回絕大部分資料，還有部分資料丟失， 檢視dg備端，發現備端歸檔都在，但沒有應用到資料庫裡。 可通過指定時間方式，先恢復資料庫到刪除之前的節點。 參考命令如下：
``` 
#示例為19c dg
#因操作不當，誤刪除了一個pdb，根據刪除時間，通過備端進行恢復
2021-07-22T15:55:44.291222+08:00
drop pluggable database testpdb including  datafiles
2021-07-22T15:55:55.145297+08:00
Deleted file /u01/app/oracle/oradata/MYDB19C/testpdb/testpdb_user01.dbf
2021-07-22T15:55:56.436102+08:00
Deleted file /u01/app/oracle/oradata/MYDB19C/testpdb/testpdb_temp01.dbf

#dg備端為mount狀態，通過rman恢復
RMAN> recover database until time "to_date('2021-07-22 15:51:00','YYYY-MM-DD HH24:MI:SS')";

Starting recover at 22-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

starting media recovery

archived log for thread 1 with sequence 153 is already on disk as file /arch/1_153_1029107282.arc
archived log for thread 1 with sequence 154 is already on disk as file /arch/1_154_1029107282.arc
archived log for thread 1 with sequence 155 is already on disk as file /arch/1_155_1029107282.arc
archived log for thread 1 with sequence 156 is already on disk as file /arch/1_156_1029107282.arc
archived log for thread 1 with sequence 157 is already on disk as file /arch/1_157_1029107282.arc
archived log for thread 1 with sequence 158 is already on disk as file /arch/1_158_1029107282.arc
archived log for thread 1 with sequence 159 is already on disk as file /arch/1_159_1029107282.arc
archived log for thread 1 with sequence 160 is already on disk as file /arch/1_160_1029107282.arc
archived log for thread 1 with sequence 161 is already on disk as file /arch/1_161_1029107282.arc
archived log for thread 1 with sequence 162 is already on disk as file /arch/1_162_1029107282.arc
archived log for thread 1 with sequence 163 is already on disk as file /arch/1_163_1029107282.arc
archived log for thread 1 with sequence 164 is already on disk as file /arch/1_164_1029107282.arc
archived log file name=/arch/1_153_1029107282.arc thread=1 sequence=153
archived log file name=/arch/1_154_1029107282.arc thread=1 sequence=154
archived log file name=/arch/1_155_1029107282.arc thread=1 sequence=155
archived log file name=/arch/1_156_1029107282.arc thread=1 sequence=156
archived log file name=/arch/1_157_1029107282.arc thread=1 sequence=157
archived log file name=/arch/1_158_1029107282.arc thread=1 sequence=158
archived log file name=/arch/1_159_1029107282.arc thread=1 sequence=159
archived log file name=/arch/1_160_1029107282.arc thread=1 sequence=160
archived log file name=/arch/1_161_1029107282.arc thread=1 sequence=161
archived log file name=/arch/1_162_1029107282.arc thread=1 sequence=162
archived log file name=/arch/1_163_1029107282.arc thread=1 sequence=163
archived log file name=/arch/1_164_1029107282.arc thread=1 sequence=164
media recovery complete, elapsed time: 00:02:16
Finished recover at 22-JUL-21
```
檢查備端情況
``` sql
--開啟資料庫

SQL> alter database open;

Database altered.

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 TESTPDB			  MOUNTED
	 4 MYPDB			  MOUNTED
--如果開啟recover，後續就刪除了testpdb
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> show pdbs

CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 MYPDB			  MOUNTED
```

另，注意，Oracle19c 在mount資料庫時，dg備端會自動開啟recover模式，應用歸檔日誌。 alert日誌參考：
```
ARCc started with pid=64, OS id=88513 
2021-07-22T16:10:49.636837+08:00
MRP0 (PID:88499): Managed Standby Recovery starting Real Time Apply
2021-07-22T16:10:49.672074+08:00
ARCd started with pid=65, OS id=88518 
2021-07-22T16:10:49.672676+08:00
Starting background process ARCe
2021-07-22T16:10:51.903729+08:00
max_pdb is 8
2021-07-22T16:10:55.411248+08:00
Starting background process ARCf
2021-07-22T16:10:55.420715+08:00
ARCe started with pid=66, OS id=88524 
2021-07-22T16:10:57.850452+08:00
Starting background process ARCg
2021-07-22T16:10:57.967273+08:00
ARCf started with pid=67, OS id=88528 
2021-07-22T16:11:01.664535+08:00
stopping change tracking
2021-07-22T16:11:01.682638+08:00
MRP0 (PID:88499): Media Recovery Log /arch/1_151_1029107282.arc
2021-07-22T16:11:02.562368+08:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2021-07-22T16:11:03.176818+08:00
ARCg started with pid=68, OS id=88532 
2021-07-22T16:11:03.177282+08:00
```

【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料

相關文章