基于时间点异机恢复数据库（处理11G单表误删除）

上周发生一起误操作将一个小表的数据全删除的事件，发现当时已经过去16个小时，表闪回查询已超出范围
后利用备份新建了个实例进行恢复的。查看rman备份，当天凌晨做了全备，而恢复该表需要上个周期的1个全备+6个增备数据

基于已有的备份，实施基于时间点的恢复

1. 概况

删除表的时间是：2024-05-10 16:34:17
发现时间： 2024-05-11 09:06:33
环境：oracle 11.2.0.4 ，CentOS 8C32G

一发现问题，尝试闪回查询，报错ORA-08180

select * from test_task as of timestamp to_timestamp('2024-05-10 16:34:17','yyyy-mm-dd hh24:mi:ss');

ORA-08180: 未找到基于指定时间的快照

2. 新实例准备

实例存放在云上，使用11号的镜像恢复实例，并删除数据库实例，只留下oracle工具（相当于是复制了原机的系统盘和oracle工具），节省安装oracle及环境变量配置时间，当然也可以用提前准备好的镜像（临时恢复，有啥用啥）

3. 加载pfile文件

sqlplus / as sysdba
create spfile from pfile='pfile20240416-4C32G-OK.ora' ;

pfile文件内容如下：

vir.__db_cache_size=20065550336
vir.__java_pool_size=268435456
vir.__large_pool_size=268435456
vir.__oracle_base='/mnt/oracle'#ORACLE_BASE set from environment
vir.__pga_aggregate_target=5368709120
vir.__sga_target=22011707392
vir.__shared_io_pool_size=0
vir.__shared_pool_size=25501368320
vir.__streams_pool_size=2147483648
*._bloom_pruning_enabled=TRUE
*._db_block_prefetch_limit=0
*._db_block_prefetch_quota=10
*._db_file_noncontig_mblock_read_count=11
*._optim_peek_user_binds=FALSE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_use_feedback=TRUE
*._PX_use_large_pool=FALSE
*._resource_manager_always_off=TRUE
*._resource_manager_always_on=FALSE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.aq_tm_processes=0
*.audit_file_dest='/mnt/oracle/admin/vir/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'
*.control_file_record_keep_time=31
*.control_files='/mnt/oracle/oradata/vir/control01.ctl','/mnt/oracle/oradata/vir/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=2000
*.db_name='vir'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/mnt/oracle'
*.disk_asynch_io=TRUE
*.dispatchers=''
*.enable_goldengate_replication=TRUE
*.event='28401 trace name context forever,level 1'
*.fal_client='vir'
*.fal_server='vir_st'
*.log_archive_config='dg_config=(vir,vir_st)'
*.log_archive_dest_1='location=/mnt/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=vir'
*.log_archive_dest_2='service=vir_st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=vir_st'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='defer'
*.log_archive_format='ARC%S_%R.%T.arc'
*.open_cursors=1000
*.pga_aggregate_target=5368709120
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=800
*.session_max_open_files=50
*.sessions=3205
*.sga_max_size=22011707392
*.sga_target=22011707392
*.standby_file_management='auto'
*.streams_pool_size=2147483648
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

4. 加载控制文件

rman target /
alter database nomount;
restore controlfile from  '/backup/ctr_20240513_32RNQT9.BKP';

5. 注册备份文件

catalog start with '/backup/virbak/240504';
catalog start with '/backup/virbak/240505';
catalog start with '/backup/virbak/240506';
catalog start with '/backup/virbak/240507';
catalog start with '/backup/virbak/240508';
catalog start with '/backup/virbak/240509';
catalog start with '/backup/virbak/240510';

6. 基于时间点，恢复数据库

6.1 恢复脚本：

#!/bin/bash
. $HOME/.bash_profile
DATE=`date +%y%m%d%H`;export DATE
RMAN_LOG_FILE=/mnt/fullbackup/recover_time_$DATE.log
WEEK_DAILY=`date +%a`
rman target /  msglog $RMAN_LOG_FILE  <<EOF
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
set until time "to_date('2024-05-10 16:30:17','yyyy-mm-dd hh24:mi:ss')";
restore database;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
exit
EOF
DATE=`date +%y%m%d`;export DATE

6.2 执行恢复：

nohup sh recover_time.sh &

查看恢复的日志文件

...
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1135 device type=DISK

allocated channel: c2
channel c2: SID=1701 device type=DISK

allocated channel: c3
channel c3: SID=2267 device type=DISK

allocated channel: c4
channel c4: SID=2833 device type=DISK

allocated channel: c5
channel c5: SID=3400 device type=DISK

allocated channel: c6
channel c6: SID=3963 device type=DISK

executing command: SET until clause

Starting restore at 2024:05:11 13:43:14

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /mnt/oradata/system01.dbf
channel c1: restoring datafile 00041 to /mnt/oradata/VIR_X_12.dbf
channel c1: restoring datafile 00126 to /mnt/oradata/UNDOTBS1_2.dbf
channel c1: restoring datafile 00128 to /mnt/oradata/UNDOTBS1_4.dbf
channel c1: restoring datafile 00138 to /mnt/oradata/VIR_T_27.dbf
channel c1: reading from backup piece /kybackup/fullbak_r52pu7o6_1_1_20240504
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00003 to /mnt/oradata/UNDOTBS1_1.DBF
channel c2: restoring datafile 00005 to /mnt/oradata/VIR_T_01.dbf
channel c2: restoring datafile 00006 to /mnt/oradata/VIR_T_02.dbf
channel c2: restoring datafile 00008 to /mnt/oradata/VIR_T_04.dbf
channel c2: reading from backup piece /kybackup/fullbak_r82pu7o6_1_1_20240504

...省了2000行+

channel c2: errors found reading piece handle=/kybackup/fullbak_4a2qe24c_1_1_20240510
channel c2: failover to piece handle=/backup/virbak/240510/fullbak_4a2qe24c_1_1_20240510 tag=FULL_BAK
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:02:05

starting media recovery

archived log for thread 1 with sequence 453014 is already on disk as file /mnt/archivelog/ARC0000003014_0888888888.0001.arc
archived log for thread 1 with sequence 453015 is already on disk as file /mnt/archivelog/ARC0000003015_0888888888.0001.arc
archived log for thread 1 with sequence 453016 is already on disk as file /mnt/archivelog/ARC0000003016_0888888888.0001.arc
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=453012
channel c1: reading from backup piece /kybackup/archbak_4i2qe261_1_1_20240510
channel c2: starting archived log restore to default destination
channel c2: restoring archived log
archived log thread=1 sequence=453013
channel c2: reading from backup piece /kybackup/archbak_4j2qe261_1_1_20240510
channel c2: errors found reading piece handle=/kybackup/archbak_4j2qe261_1_1_20240510
channel c2: failover to piece handle=/backup/virbak/240510/archbak_4j2qe261_1_1_20240510 tag=ARC_BAK
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:01
channel c1: errors found reading piece handle=/kybackup/archbak_4i2qe261_1_1_20240510
channel c1: failover to piece handle=/backup/virbak/240510/archbak_4i2qe261_1_1_20240510 tag=ARC_BAK
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
archived log file name=/mnt/archivelog/ARC0000003012_0888888888.0001.arc thread=1 sequence=453012
archived log file name=/mnt/archivelog/ARC0000003013_0888888888.0001.arc thread=1 sequence=453013
archived log file name=/mnt/archivelog/ARC0000003014_0888888888.0001.arc thread=1 sequence=453014
archived log file name=/mnt/archivelog/ARC0000003015_0888888888.0001.arc thread=1 sequence=453015
media recovery complete, elapsed time: 00:11:52
Finished recover at 2024:05:11 19:17:04

released channel: c1

released channel: c2

released channel: c3

released channel: c4

released channel: c5

released channel: c6

RMAN> 

Recovery Manager complete.

查看恢复日志可以清晰的看到，同一个文件，经过了全备+6个增备的迭代，被恢复了7次

7. resetlog方式打开数据库（不完整恢复）

alter database open resetlogs;

8. 数据验证，登录表owner用户，查询并创建备份表

PS： 创建备份表，是防止导入时覆盖掉生产的表

select count(1) from test_task；
  COUNT(1)
----------
    243008

-- 确认无误后，创建备份表
create table test_task_bak20240510 as select * from test_task；

9. expdp导出需要恢复的表

expdp vir_user/password directory=DATADUMP dumpfile=test_task_20240510_2.dump logfile=test_task_20240510_2.log tables=test_task_bak20240510

10. impdp 导入表到生产

impdp vir_user/password directory=DATADUMP dumpfile=test_task_20240510_2.dump logfile=test_task_20240510_2.log  remap_schema=vir_user:vir_user

11. 比对备份表与生产表数据，确认备份表数据在生产中表中都不存在后，直接插入的表

select * from test_task
where id in (select id from test_task_bak20240510 );
---------------------------------------------------

-- 插入备份表数据到生产表
 insert into test_task select * from  test_task_bak20240510 

到此，表恢复完成。确认无误后，清理掉恢复测试的实例（谨慎：一定看清 要清理的服务器IP）