【博客文章2024】在相同的误删除表空间的场景中，对RAC 19c进行三种不同处置的比较：不完全恢复、闪回和TSPITR
Author： Bo Tang
1. 相同的误删除表空间场景描述（这个场景将采用快照技术保存，供以下3种不同处置分支作为实验起点）：
一套拥有2个节点的RAC 19c环境（后续将被误删除users表空间）：
[root@station11 ~]# su - grid
Last login: Fri Apr 19 03:00:09 CST 2024
[grid@station11 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE station11 STABLE
ONLINE ONLINE station12 STABLE
ora.chad
ONLINE ONLINE station11 STABLE
ONLINE ONLINE station12 STABLE
ora.net1.network
ONLINE ONLINE station11 STABLE
ONLINE ONLINE station12 STABLE
ora.ons
ONLINE ONLINE station11 STABLE
ONLINE ONLINE station12 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE station11 STABLE
2 ONLINE ONLINE station12 STABLE
3 ONLINE OFFLINE STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE station11 STABLE
2 ONLINE ONLINE station12 STABLE
3 OFFLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE station11 STABLE
2 ONLINE ONLINE station12 STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE station11 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE station12 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE station12 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE station12 169.254.1.204 172.31
.118.12 172.31.118.2
12,STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE station11 Started,STABLE
2 ONLINE ONLINE station12 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE station11 STABLE
2 ONLINE ONLINE station12 STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE station12 STABLE
ora.mgmtdb
1 ONLINE ONLINE station12 Open,STABLE
ora.qosmserver
1 ONLINE ONLINE station12 STABLE
ora.racorcl.db
1 ONLINE ONLINE station11 Open,HOME=/u01/app/o
racle/product/19.3.0
/dbhome_1,STABLE
2 ONLINE ONLINE station12 Open,HOME=/u01/app/o
racle/product/19.3.0
/dbhome_1,STABLE
ora.scan1.vip
1 ONLINE ONLINE station11 STABLE
ora.scan2.vip
1 ONLINE ONLINE station12 STABLE
ora.scan3.vip
1 ONLINE ONLINE station12 STABLE
ora.station11.vip
1 ONLINE ONLINE station11 STABLE
ora.station12.vip
1 ONLINE ONLINE station12 STABLE
--------------------------------------------------------------------------------
|
该RAC 19c数据库本身是处于打开状态的：
[root@station11 ~]# su - oracle
Last login: Fri Apr 19 03:01:47 CST 2024 on pts/0
[oracle@station11 ~]$ srvctl status database -d racorcl -v
Instance racorcl1 is running on node station11. Instance status: Open.
Instance racorcl2 is running on node station12. Instance status: Open.
|
该RAC 19c数据库的闪回数据库功能是打开的：
[oracle@station11 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 19 03:09:30 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select inst_id, flashback_on from gv$database;
INST_ID FLASHBACK_ON
---------- ------------------
1 YES
2 YES
|
[oracle@station11 ~]$ rman target / catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 19 03:20:11 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACORCL (DBID=2908490689)
connected to recovery catalog database
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1479 3.02M DISK 00:00:00 07-MAR-24
BP Key: 1489 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.281.1162981855
List of Archived Logs in backup set 1479
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
2 1 2051424 07-MAR-24 2077931 07-MAR-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1480 161.54M DISK 00:00:00 07-MAR-24
BP Key: 1490 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.283.1162981855
List of Archived Logs in backup set 1480
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 2032613 07-MAR-24 2077927 07-MAR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1481 Incr 0 2.12M DISK 00:00:01 07-MAR-24
BP Key: 1491 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.279.1162981859
List of Datafiles in backup set 1481
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 0 Incr 2077995 07-MAR-24 NO +DATA/RACORCL/DATAFILE/undotbs2.301.1162980561
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1482 Incr 0 2.38M DISK 00:00:00 07-MAR-24
BP Key: 1492 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.264.1162981859
List of Datafiles in backup set 1482
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 0 Incr 2077998 07-MAR-24 NO +DATA/RACORCL/DATAFILE/users.303.1162980353
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1483 Incr 0 341.46M DISK 00:00:02 07-MAR-24
BP Key: 1493 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.280.1162981857
List of Datafiles in backup set 1483
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
4 0 Incr 2077993 07-MAR-24 NO +DATA/RACORCL/DATAFILE/undotbs1.288.1162980353
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1484 Incr 0 387.01M DISK 00:00:02 07-MAR-24
BP Key: 1494 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.285.1162981857
List of Datafiles in backup set 1484
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
3 0 Incr 2077991 07-MAR-24 NO +DATA/RACORCL/DATAFILE/sysaux.295.1162980337
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1485 Incr 0 779.59M DISK 00:00:02 07-MAR-24
BP Key: 1495 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.284.1162981857
List of Datafiles in backup set 1485
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 2077990 07-MAR-24 NO +DATA/RACORCL/DATAFILE/system.283.1162980303
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1486 9.50K DISK 00:00:00 07-MAR-24
BP Key: 1496 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.277.1162981865
List of Archived Logs in backup set 1486
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 2077927 07-MAR-24 2078008 07-MAR-24
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1487 9.50K DISK 00:00:00 07-MAR-24
BP Key: 1497 Status: AVAILABLE Compressed: NO Tag: 1D_WHOLE_INCR0
Piece Name: +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.276.1162981865
List of Archived Logs in backup set 1487
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
2 2 2077931 07-MAR-24 2078012 07-MAR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1488 Full 18.89M DISK 00:00:00 07-MAR-24
BP Key: 1498 Status: AVAILABLE Compressed: NO Tag: TAG20240307T103106
Piece Name: +FRA/RACORCL/AUTOBACKUP/2024_03_07/s_1162981866.278.1162981867
SPFILE Included: Modification time: 07-MAR-24
SPFILE db_unique_name: RACORCL
Control File Included: Ckp SCN: 2078045 Ckp time: 07-MAR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1610 Full 18.89M DISK 00:00:01 19-APR-24
BP Key: 1613 Status: AVAILABLE Compressed: NO Tag: TAG20240419T031041
Piece Name: +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166670641.270.1166670643
SPFILE Included: Modification time: 19-APR-24
SPFILE db_unique_name: RACORCL
Control File Included: Ckp SCN: 2093112 Ckp time: 19-APR-24
|
[oracle@station11 ~]$ rman target / catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 19 03:22:46 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACORCL (DBID=2908490689)
connected to recovery catalog database
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1445 1461 RACORCL 2908490689 PARENT 1 17-APR-19
1445 1446 RACORCL 2908490689 CURRENT 1920977 07-MAR-24
|
以下3个处置分支的实验起点都是以上展示的相同场景。
2. 不完全恢复的处置过程（要关停RAC数据库执行恢复）：
2.1 模拟故障：删除users表空间：
......
handle '+FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166687165.259.1166687165'
2024-04-19T07:46:16.710300+08:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
2024-04-19T07:51:46.262106+08:00
create tablespace example datafile size 20M
Completed: create tablespace example datafile size 20M
2024-04-19T07:52:25.564115+08:00
alter database default tablespace example
Completed: alter database default tablespace example
2024-04-19T07:52:42.080629+08:00
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 2 seq# 10 mem# 0: +DATA/RACORCL/ONLINELOG/group_2.290.1162980421
Current log# 2 seq# 10 mem# 1: +FRA/RACORCL/ONLINELOG/group_2.289.1162980421
2024-04-19T07:52:42.139040+08:00
ARC3 (PID:28652): Archived Log entry 18 added for T-1.S-9 ID 0xad5ba3c1 LAD:1
2024-04-19T07:52:47.545758+08:00
drop tablespace users including contents and datafiles
2024-04-19T07:52:54.329658+08:00
Deleted Oracle managed file +DATA/RACORCL/DATAFILE/users.303.1162980353
Completed: drop tablespace users including contents and datafiles
2024-04-19T07:53:13.790530+08:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
......
|
以上的alert日志里记录下删除users表空间的时间。 2.2 关停RAC数据库：
| [oracle@station11 ~]$ srvctl stop database -d racorcl |
2.3 连接Recovery Catalog，还原删除表空间之前的控制文件：
[oracle@station11 ~]$ rman target / catalog urcat/oracle_4U@rcat
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Apr 19 08:21:54 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup nomount
Oracle instance started
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 1040187392 bytes
Database Buffers 3892314112 bytes
Redo Buffers 7868416 bytes
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1488 Full 18.89M DISK 00:00:00 07-MAR-24
BP Key: 1498 Status: AVAILABLE Compressed: NO Tag: TAG20240307T103106
Piece Name: +FRA/RACORCL/AUTOBACKUP/2024_03_07/s_1162981866.278.1162981867
Control File Included: Ckp SCN: 2078045 Ckp time: 07-MAR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1773 Full 18.86M DISK 00:00:01 19-APR-24
BP Key: 1779 Status: AVAILABLE Compressed: NO Tag: LABS-14-CONTR-BEFORE-DROP
Piece Name: +FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-before-drop_0.258.1166687553
Control File Included: Ckp SCN: 2125410 Ckp time: 19-APR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1774 Full 18.89M DISK 00:00:00 19-APR-24
BP Key: 1780 Status: AVAILABLE Compressed: NO Tag: TAG20240419T075234
Piece Name: +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166687554.257.1166687555
Control File Included: Ckp SCN: 2125419 Ckp time: 19-APR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1775 Full 18.86M DISK 00:00:01 19-APR-24
BP Key: 1781 Status: AVAILABLE Compressed: NO Tag: LABS-14-CONTR-AFTER-DROP
Piece Name: +FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-after-drop_0.298.1166687583
Control File Included: Ckp SCN: 2126251 Ckp time: 19-APR-24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1776 Full 18.89M DISK 00:00:00 19-APR-24
BP Key: 1782 Status: AVAILABLE Compressed: NO Tag: TAG20240419T075303
Piece Name: +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166687583.299.1166687583
Control File Included: Ckp SCN: 2126261 Ckp time: 19-APR-24
RMAN> restore controlfile from tag 'LABS-14-CONTR-BEFORE-DROP';
Starting restore at 19-APR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=371 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=494 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=615 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=736 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=858 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=982 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1104 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=1226 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=1347 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=1469 instance=racorcl1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-before-drop_0.258.1166687553
channel ORA_DISK_1: piece handle=+FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-before-drop_0.258.1166687553 tag=LABS-14-CONTR-BEFORE-DROP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/RACORCL/CONTROLFILE/current.294.1162980417
output file name=+FRA/RACORCL/CONTROLFILE/current.291.1162980417
Finished restore at 19-APR-24
RMAN> alter database mount;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
released channel: ORA_DISK_9
released channel: ORA_DISK_10
Statement processed
|
2.4 执行set until time恢复： 我们将alert日志记录下的删除users表空间的时间减去1秒，作为set until time的时间：
RMAN> run {
2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
3> set until time '2024-04-19:07:52:46';
4> restore database;
5> recover database;
6> }
sql statement: alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''
executing command: SET until clause
Starting restore at 19-APR-24
Starting implicit crosscheck backup at 19-APR-24
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
allocated channel: ORA_DISK_3
allocated channel: ORA_DISK_4
allocated channel: ORA_DISK_5
allocated channel: ORA_DISK_6
allocated channel: ORA_DISK_7
allocated channel: ORA_DISK_8
allocated channel: ORA_DISK_9
allocated channel: ORA_DISK_10
Crosschecked 1 objects
Crosschecked 2 objects
Crosschecked 2 objects
Crosschecked 2 objects
Crosschecked 1 objects
Crosschecked 2 objects
Crosschecked 2 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Finished implicit crosscheck backup at 19-APR-24
Starting implicit crosscheck copy at 19-APR-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
Finished implicit crosscheck copy at 19-APR-24
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_10.287.1166687557
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_11.292.1166687557
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_12.293.1166687561
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_13.294.1166687561
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_9.295.1166687563
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_14.296.1166687563
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_15.297.1166687567
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_10.300.1166688577
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_16.301.1166689067
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_11.302.1166689221
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_12.303.1166689221
File Name: +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_13.304.1166689273
File Name: +FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-before-drop_0.258.1166687553
File Name: +FRA/RACORCL/BACKUPSET/2024_04_19/ncnnf0_labs-14-contr-after-drop_0.298.1166687583
File Name: +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166687554.257.1166687555
File Name: +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166687583.299.1166687583
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
creating datafile file number=2 name=+DATA/RACORCL/DATAFILE/example.300.1166687507
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATA/RACORCL/DATAFILE/undotbs2.301.1162980561
channel ORA_DISK_1: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.279.1162981859
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00007 to +DATA/RACORCL/DATAFILE/users.303.1162980353
channel ORA_DISK_2: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.264.1162981859
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00001 to +DATA/RACORCL/DATAFILE/system.283.1162980303
channel ORA_DISK_3: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.284.1162981857
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00003 to +DATA/RACORCL/DATAFILE/sysaux.295.1162980337
channel ORA_DISK_4: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.285.1162981857
channel ORA_DISK_5: starting datafile backup set restore
channel ORA_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_DISK_5: restoring datafile 00004 to +DATA/RACORCL/DATAFILE/undotbs1.288.1162980353
channel ORA_DISK_5: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.280.1162981857
channel ORA_DISK_1: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.279.1162981859 tag=1D_WHOLE_INCR0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.264.1162981859 tag=1D_WHOLE_INCR0
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_3: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.284.1162981857 tag=1D_WHOLE_INCR0
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:07
channel ORA_DISK_4: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.285.1162981857 tag=1D_WHOLE_INCR0
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:07
channel ORA_DISK_5: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.280.1162981857 tag=1D_WHOLE_INCR0
channel ORA_DISK_5: restored backup piece 1
channel ORA_DISK_5: restore complete, elapsed time: 00:00:07
Finished restore at 19-APR-24
Starting recover at 19-APR-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
starting media recovery
archived
log for thread 1 with sequence 7 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_7.261.1166687149
archived
log for thread 1 with sequence 8 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_8.256.1166687159
archived
log for thread 1 with sequence 9 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_9.295.1166687563
archived
log for thread 1 with sequence 10 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_10.300.1166688577
archived
log for thread 2 with sequence 3 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_03_07/thread_2_seq_3.271.1162981899
archived
log for thread 2 with sequence 4 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_4.267.1166687147
archived
log for thread 2 with sequence 5 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_5.266.1166687149
archived
log for thread 2 with sequence 6 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_6.262.1166687151
archived
log for thread 2 with sequence 7 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_7.263.1166687151
archived
log for thread 2 with sequence 8 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_8.265.1166687155
archived
log for thread 2 with sequence 9 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_9.282.1166687155
archived
log for thread 2 with sequence 10 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_10.287.1166687557
archived
log for thread 2 with sequence 11 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_11.292.1166687557
archived
log for thread 2 with sequence 12 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_12.293.1166687561
archived
log for thread 2 with sequence 13 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_13.294.1166687561
archived
log for thread 2 with sequence 14 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_14.296.1166687563
archived
log for thread 2 with sequence 15 is already on disk as file
+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_15.297.1166687567
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.277.1162981865
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=2 sequence=2
channel ORA_DISK_2: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.276.1162981865
channel ORA_DISK_1: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.277.1162981865 tag=1D_WHOLE_INCR0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_6.272.1166689535 thread=1 sequence=6
channel ORA_DISK_2: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.276.1162981865 tag=1D_WHOLE_INCR0
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_2.273.1166689535 thread=2 sequence=2
channel default: deleting archived log(s)
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_6.272.1166689535 RECID=34 STAMP=1166689535
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_7.261.1166687149 thread=1 sequence=7
channel default: deleting archived log(s)
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_2.273.1166689535 RECID=35 STAMP=1166689535
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_03_07/thread_2_seq_3.271.1162981899 thread=2 sequence=3
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_4.267.1166687147 thread=2 sequence=4
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_5.266.1166687149 thread=2 sequence=5
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_6.262.1166687151 thread=2 sequence=6
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_8.256.1166687159 thread=1 sequence=8
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_7.263.1166687151 thread=2 sequence=7
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_8.265.1166687155 thread=2 sequence=8
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_9.282.1166687155 thread=2 sequence=9
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_10.287.1166687557 thread=2 sequence=10
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_9.295.1166687563 thread=1 sequence=9
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_11.292.1166687557 thread=2 sequence=11
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_12.293.1166687561 thread=2 sequence=12
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_13.294.1166687561 thread=2 sequence=13
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_14.296.1166687563 thread=2 sequence=14
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_10.300.1166688577 thread=1 sequence=10
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_15.297.1166687567 thread=2 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-APR-24 |
2.5 完成了users表空间的恢复：
RMAN> alter database open resetlogs;
Statement processed
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> report schema;
Report of database schema for database with db_unique_name RACORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 900 SYSTEM YES +DATA/RACORCL/DATAFILE/system.283.1162980303
2 20 EXAMPLE NO +DATA/RACORCL/DATAFILE/example.300.1166687507
3 570 SYSAUX NO +DATA/RACORCL/DATAFILE/sysaux.295.1162980337
4 350 UNDOTBS1 YES +DATA/RACORCL/DATAFILE/undotbs1.288.1162980353
5 25 UNDOTBS2 YES +DATA/RACORCL/DATAFILE/undotbs2.301.1162980561
7 5 USERS NO +DATA/RACORCL/DATAFILE/users.303.1166689525
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 37 TEMP 32767 +DATA/RACORCL/TEMPFILE/temp.289.1162980427
RMAN> exit
Recovery Manager complete.
[oracle@station11 ~]$ srvctl start instance -i racorcl2 -d racorcl
[oracle@station11 ~]$ srvctl status database -d racorcl -v
Instance racorcl1 is running on node station11. Instance status: Open.
Instance racorcl2 is running on node station12. Instance status: Open. |
3.1 模拟故障：删除users表空间：
......
Current log# 2 seq# 10 mem# 0: +DATA/RACORCL/ONLINELOG/group_2.290.1162980421
Current log# 2 seq# 10 mem# 1: +FRA/RACORCL/ONLINELOG/group_2.289.1162980421
2024-04-20T07:22:11.430617+08:00
ARC0 (PID:24219): Archived Log entry 11 added for T-1.S-9 ID 0xad5ba3c1 LAD:1
2024-04-20T07:22:14.393932+08:00
Thread 1 cannot allocate new log, sequence 11
Checkpoint not complete
Current log# 2 seq# 10 mem# 0: +DATA/RACORCL/ONLINELOG/group_2.290.1162980421
Current log# 2 seq# 10 mem# 1: +FRA/RACORCL/ONLINELOG/group_2.289.1162980421
2024-04-20T07:22:15.055347+08:00
drop tablespace users including contents and datafiles
ORA-12919 signalled during: drop tablespace users including contents and datafiles...
2024-04-20T07:22:17.411153+08:00
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 1 seq# 11 mem# 0: +DATA/RACORCL/ONLINELOG/group_1.293.1162980421
Current log# 1 seq# 11 mem# 1: +FRA/RACORCL/ONLINELOG/group_1.290.1162980421
2024-04-20T07:22:17.471782+08:00
ARC1 (PID:24226): Archived Log entry 15 added for T-1.S-10 ID 0xad5ba3c1 LAD:1
2024-04-20T07:22:34.262219+08:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
2024-04-20T07:24:34.318008+08:00
/drop tablespace 4109,31 98%
...... |
以上的alert日志记录下删除users表空间的时间。 3.2 关停RAC数据库：
| [oracle@station11 ~]$ srvctl stop database -d racorcl |
3.3 启动一个实例到mount的状态，我们将alert日志记录下的删除users表空间的时间减去1秒，作为flashback database的时间： 我们观察到目标闪回时间落在OLDEST_FLASHBACK_TIME和当前时间形成的窗口内，这一点满足闪回的需求。但是删除表空间的场景是不能使用闪回数据库方式进行处理的。
[oracle@station11 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 20 07:29:43 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 1056964608 bytes
Database Buffers 3875536896 bytes
Redo Buffers 7868416 bytes
Database mounted.
SQL> desc v$flashback_database_log
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER
OLDEST_FLASHBACK_TIME DATE
RETENTION_TARGET NUMBER
FLASHBACK_SIZE NUMBER
ESTIMATED_FLASHBACK_SIZE NUMBER
CON_ID NUMBER
SQL> select to_char(OLDEST_FLASHBACK_TIME,'YYYY-MM-DD:HH24:MI:SS') from v$flashback_database_log;
TO_CHAR(OLDEST_FLAS
-------------------
2024-04-20:07:19:30
SQL> flashback database to timestamp to_timestamp('2024-04-20:07:22:14','YYYY-MM-DD:HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/RACORCL/DATAFILE/system.283.1162980303
+DATA/RACORCL/DATAFILE/sysaux.295.1162980337
+DATA/RACORCL/DATAFILE/undotbs1.288.1162980353
+DATA/RACORCL/DATAFILE/undotbs2.301.1162980561
/u01/app/oracle/product/19.3.0/dbhome_1/dbs/UNNAMED00007
|
出现uname0000X问题，说明删除表空间的场景是不能使用闪回数据库方式进行处理的。
4. TSPITR的处置过程（最佳的处置过程）：
4.1 模拟故障：删除users表空间：
......
Current log# 2 seq# 14 mem# 0: +DATA/RACORCL/ONLINELOG/group_2.290.1162980421
Current log# 2 seq# 14 mem# 1: +FRA/RACORCL/ONLINELOG/group_2.289.1162980421
2024-04-19T09:23:12.330757+08:00
ARC2 (PID:21721): Archived Log entry 13 added for T-1.S-13 ID 0xad5ba3c1 LAD:1
2024-04-19T09:23:15.235170+08:00
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 1 seq# 15 mem# 0: +DATA/RACORCL/ONLINELOG/group_1.293.1162980421
Current log# 1 seq# 15 mem# 1: +FRA/RACORCL/ONLINELOG/group_1.290.1162980421
2024-04-19T09:23:15.261927+08:00
ARC3 (PID:21723): Archived Log entry 14 added for T-1.S-14 ID 0xad5ba3c1 LAD:1
2024-04-19T09:23:15.658909+08:00
drop tablespace users including contents and datafiles
2024-04-19T09:23:21.867952+08:00
Deleted Oracle managed file +DATA/RACORCL/DATAFILE/users.303.1162980353
Completed: drop tablespace users including contents and datafiles
2024-04-19T09:23:30.961283+08:00
Control autobackup written to DISK device
handle '+FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166693010.282.1166693011'
2024-04-19T09:23:39.853067+08:00
ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
2024-04-19T09:34:15.669481+08:00
...... |
以上的alert日志记录下删除users表空间的时间。 4.2 我们将alert日志记录下的删除users表空间的时间减去1秒，换算成TSPITR的SCN：
[oracle@station11 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 20 02:09:36 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> select TIMESTAMP_TO_SCN(to_timestamp('2024-04-19:09:23:14','YYYY-MM-DD:HH24:MI:SS')) from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('2024-04-19:09:23:14','YYYY-MM-DD:HH24:MI:SS'))
-----------------------------------------------------------------------------
2087422
|
[oracle@station11 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 20 02:19:55 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACORCL (DBID=2908490689)
RMAN> recover tablespace users
2> until scn 2087422
3> auxiliary destination '+fra';
Starting recover at 20-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1348 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1471 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1714 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=130 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=496 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=620 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=738 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=862 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=985 instance=racorcl1 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=1108 instance=racorcl1 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Creating automatic instance, with SID='hpif'
initialization parameters used for automatic instance:
db_name=RACORCL
db_unique_name=hpif_pitr_RACORCL
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=4720M
processes=200
db_create_file_dest=+fra
log_archive_dest_1='location=+fra'
#No auxiliary parameter file used
starting up automatic instance RACORCL
Oracle instance started
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 956301312 bytes
Database Buffers 3976200192 bytes
Redo Buffers 7868416 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace USERS
contents of Memory Script:
{
# set requested point in time
set until scn 2087422;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 20-APR-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=46 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=130 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=148 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=193 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=215 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=235 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=256 device type=DISK
allocated channel: ORA_AUX_DISK_9
channel ORA_AUX_DISK_9: SID=277 device type=DISK
allocated channel: ORA_AUX_DISK_10
channel ORA_AUX_DISK_10: SID=298 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166692982.272.1166692983
channel ORA_AUX_DISK_1: piece handle=+FRA/RACORCL/AUTOBACKUP/2024_04_19/s_1166692982.272.1166692983 tag=TAG20240419T092302
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+FRA/RACORCL/CONTROLFILE/current.293.1166754089
Finished restore at 20-APR-24
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
contents of Memory Script:
{
# set requested point in time
set until scn 2087422;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 7 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 5, 3, 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +fra in control file
Starting restore at 20-APR-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +fra
channel ORA_AUX_DISK_1: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.279.1162981859
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_2: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.264.1162981859
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00001 to +fra
channel ORA_AUX_DISK_3: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.284.1162981857
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00004 to +fra
channel ORA_AUX_DISK_4: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.280.1162981857
channel ORA_AUX_DISK_5: starting datafile backup set restore
channel ORA_AUX_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_5: restoring datafile 00003 to +fra
channel ORA_AUX_DISK_5: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.285.1162981857
channel ORA_AUX_DISK_1: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.279.1162981859 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.264.1162981859 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_4: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.280.1162981857 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_4: restored backup piece 1
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_5: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.285.1162981857 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_5: restored backup piece 1
channel ORA_AUX_DISK_5: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_3: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/nnndn0_1d_whole_incr0_0.284.1162981857 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:08
Finished restore at 20-APR-24
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1166754104 file name=+DATA/RACORCL/DATAFILE/users.302.1166754097
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1166754104 file name=+FRA/RACORCL/DATAFILE/system.297.1166754097
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1166754104 file name=+FRA/RACORCL/DATAFILE/undotbs1.298.1166754097
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1166754104 file name=+FRA/RACORCL/DATAFILE/undotbs2.296.1166754097
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1166754104 file name=+FRA/RACORCL/DATAFILE/sysaux.299.1166754097
contents of Memory Script:
{
# set requested point in time
set until scn 2087422;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 7 online
Starting recover at 20-APR-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_DISK_9
using channel ORA_AUX_DISK_10
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_7.275.1166692461
archived log for thread 1 with sequence 8 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_8.274.1166692463
archived log for thread 1 with sequence 9 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_9.270.1166692985
archived log for thread 1 with sequence 10 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_10.269.1166692987
archived log for thread 1 with sequence 11 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_11.267.1166692989
archived log for thread 1 with sequence 12 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_12.266.1166692989
archived log for thread 1 with sequence 13 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_13.261.1166692993
archived log for thread 1 with sequence 14 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_14.262.1166692995
archived log for thread 1 with sequence 15 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_15.256.1166738503
archived log for thread 2 with sequence 3 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_03_07/thread_2_seq_3.271.1162981899
archived log for thread 2 with sequence 4 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_4.268.1166692987
archived log for thread 2 with sequence 5 is already on disk as file +FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_5.263.1166692995
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.277.1162981865
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=2 sequence=2
channel ORA_AUX_DISK_2: reading from backup piece +FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.276.1162981865
channel ORA_AUX_DISK_1: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.277.1162981865 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_20/thread_1_seq_6.300.1166754107 thread=1 sequence=6
channel ORA_AUX_DISK_2: piece handle=+FRA/RACORCL/BACKUPSET/2024_03_07/annnf0_1d_whole_incr0_0.276.1162981865 tag=1D_WHOLE_INCR0
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_20/thread_2_seq_2.301.1166754107 thread=2 sequence=2
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_20/thread_1_seq_6.300.1166754107 RECID=8 STAMP=1166754106
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_7.275.1166692461 thread=1 sequence=7
channel clone_default: deleting archived log(s)
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_20/thread_2_seq_2.301.1166754107 RECID=9 STAMP=1166754106
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_03_07/thread_2_seq_3.271.1162981899 thread=2 sequence=3
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_8.274.1166692463 thread=1 sequence=8
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_4.268.1166692987 thread=2 sequence=4
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_9.270.1166692985 thread=1 sequence=9
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_10.269.1166692987 thread=1 sequence=10
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_11.267.1166692989 thread=1 sequence=11
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_2_seq_5.263.1166692995 thread=2 sequence=5
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_12.266.1166692989 thread=1 sequence=12
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_13.261.1166692993 thread=1 sequence=13
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_14.262.1166692995 thread=1 sequence=14
archived log file name=+FRA/RACORCL/ARCHIVELOG/2024_04_19/thread_1_seq_15.256.1166738503 thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-APR-24
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace USERS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+fra''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+fra''";
}
executing Memory Script
sql statement: alter tablespace USERS read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+fra''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+fra''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_hpif_gjaB":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
EXPDP> Master table "SYS"."TSPITR_EXP_hpif_gjaB" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_hpif_gjaB is:
EXPDP> +FRA/tspitr_hpif_49267.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace USERS:
EXPDP> +DATA/RACORCL/DATAFILE/users.302.1166754097
EXPDP> Job "SYS"."TSPITR_EXP_hpif_gjaB" successfully completed at Sat Apr 20 02:22:33 2024 elapsed 0 00:00:34
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_hpif_rrEa" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_hpif_rrEa":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_hpif_rrEa" successfully completed at Sat Apr 20 02:23:13 2024 elapsed 0 00:00:32
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace USERS read write';
sql 'alter tablespace USERS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace USERS read write
sql statement: alter tablespace USERS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file +FRA/RACORCL/TEMPFILE/temp.304.1166754115 deleted
auxiliary instance file +FRA/RACORCL/ONLINELOG/group_4.303.1166754111 deleted
auxiliary instance file +FRA/RACORCL/ONLINELOG/group_3.302.1166754111 deleted
auxiliary instance file +FRA/RACORCL/ONLINELOG/group_2.300.1166754111 deleted
auxiliary instance file +FRA/RACORCL/ONLINELOG/group_1.301.1166754111 deleted
auxiliary instance file +FRA/RACORCL/DATAFILE/sysaux.299.1166754097 deleted
auxiliary instance file +FRA/RACORCL/DATAFILE/undotbs2.296.1166754097 deleted
auxiliary instance file +FRA/RACORCL/DATAFILE/undotbs1.298.1166754097 deleted
auxiliary instance file +FRA/RACORCL/DATAFILE/system.297.1166754097 deleted
auxiliary instance file +FRA/RACORCL/CONTROLFILE/current.293.1166754089 deleted
auxiliary instance file tspitr_hpif_49267.dmp deleted
Finished recover at 20-APR-24
|
4.4 完成了users表空间的恢复：
RMAN> report schema;
Report of database schema for database with db_unique_name RACORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 920 SYSTEM YES +DATA/RACORCL/DATAFILE/system.283.1162980303
2 20 EXAMPLE NO +DATA/RACORCL/DATAFILE/example.286.1166692613
3 630 SYSAUX NO +DATA/RACORCL/DATAFILE/sysaux.295.1162980337
4 350 UNDOTBS1 YES +DATA/RACORCL/DATAFILE/undotbs1.288.1162980353
5 25 UNDOTBS2 YES +DATA/RACORCL/DATAFILE/undotbs2.301.1162980561
7 0 USERS NO +DATA/RACORCL/DATAFILE/users.302.1166754097
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 38 TEMP 32767 +DATA/RACORCL/TEMPFILE/temp.289.1162980427 |
5. 总结：
不完全恢复：缺点是要关停RAC数据库执行恢复，restore数据库的时间较长，步骤较多（比如restore controlfile时在nomount状态执行，生产环境可能会需要Recovery Catalog的帮助从磁带机中找到controlfile的备份） TSPITR：优点是命令简单，不需要备份，不需要关停RAC数据库执行恢复。它使用内存脚本结合了克隆数据库和数据泵技术来一步到位地恢复误删除的表空间，这是一种推荐的高级恢复技术。 5.2 不可以成功恢复的处置方法：
删除表空间的场景不能使用flashback database的方法进行恢复。
|
