【Oracle数据库】手滑删错数据，一步步教你如何挽救？

sqlplus /as sysdba
## 设置归档路径
alter system set log_archive_dest_1='LOCATION=/archivelog';## 重启开启归档模式
shutdown immediate
startup mount
alter database archivelog；
## 打开数据库
alter database open;

sqlplus /as sysdba
## 创建表空间
create tablespace lucifer datafile '/oradata/orcl/lucifer01.dbf' size 10M autoextend off;create tablespace ltest datafile '/oradata/orcl/ltest01.dbf' size 10M autoextend off;## 创建用户
create user lucifer identified by lucifer;grant dba to lucifer;## 创建表
conn lucifer/lucifer
createtable lucifer(id number notnull,name varchar2(20)) tablespace lucifer;## 插入数据
insertinto lucifer values(1,'lucifer');insertinto lucifer values(2,'test1');insertinto lucifer values(3,'test2');commit;

rman target /## 进入 rman 后执行以下命令
run {allocate channel c1 device type disk;allocate channel c2 device type disk;crosscheck backup;crosscheck archivelog all;sql"alter system switch logfile";delete noprompt expired backup;delete noprompt obsolete device type disk;backup database include current controlfile format '/backup/backlv0_%d_%T_%t_%s_%p';backup archivelog all DELETE INPUT;release channel c1;release channel c2;}

sqlplus /as sysdba
conn lucifer/lucifer
deletefrom lucifer where id=1;update lucifer set name='lucifer'where id=2;commit;

sqlplus /as sysdba
## 查找UNDO快照数据是否正确
select*from lucifer.luciferas of timestamp to_timestamp('2021-06-17 18:05:00','YYYY-MM-DD HH24:MI:SS');## 将UNDO快照数据捞至新建表中
createtable lucifer.lucifer_0617asselect*from lucifer.luciferas of timestamp to_timestamp('2021-06-17 18:05:00','YYYY-MM-DD HH24:MI:SS');

sqlplus /as sysdba
## 查询误更新数据表对应表空间
select owner,tablespace_name from dba_segments where segment_name='LUCIFER';## 查询所有表空间
select tablespace_name from dba_tablespaces;

## 生成pfile参数文件
sqlplus /as sysdba
create pfile='/home/oracle/pfile.ora'from spfile;exit;## 拷贝至新主机
su - oracle
scp /home/oracle/pfile.ora10.211.55.112:/tmp
scp $ORACLE_HOME/dbs/orapworcl 10.211.55.112:$ORACLE_HOME/dbs
## 新主机根据实际情况修改参数文件并且创建目录
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /oradata/orcl/mkdir -p /archivelog
chown -R oracle:oinstall /archivelog
chown -R oracle:oinstall /oradata

sqlplus /as sysdba
startup nomount pfile='/tmp/pfile.ora';

rman target /list backup of controlfile;exit;## 拷贝备份文件至新主机
scp /backup/backlv0_ORCL_20210617_107548592*10.211.55.112:/tmp
scp /u01/app/oracle/product/11.2.0/db/dbs/0c01l775_1_1 10.211.55.112:/tmp
## 新主机恢复控制文件并开启到mount状态
rman target /restore controlfile from'/tmp/backlv0_ORCL_20210617_1075485924_9_1';alter database mount;

## 新主机注册备份集
rman target /catalog start with '/tmp/backlv0_ORCL_20210617_107548592';crosscheck backup;delete noprompt expired backup;delete noprompt obsolete device type disk;## 恢复表空间LUCIFER和系统表空间，指定时间点 `2021/06/1718:06:00`
run {sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:06:00';allocate channel ch01 device type disk;allocate channel ch02 device type disk;restore tablespace SYSTEM,SYSAUX,UNDOTBS1,USERS,LUCIFER;release channel ch01;release channel ch02;}

rman target /run {sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:06:00';allocate channel ch01 device type disk;recover database skip tablespace LTEST,EXAMPLE;release channel ch01;}

sqlplus /as sysdba
## 将恢复跳过的表空间都offline drop掉，执行以下查询结果
select'alter database datafile '|| file_id ||' offline drop;'from dba_data_files where tablespace_name in('LTEST','EXAMPLE');## 再次开启数据库
alter database open read only;

sqlplus /as sysdba
## 查询恢复需要的归档日志号时间 
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";select first_time,sequence# from v$archived_log where sequence#='7';exit;## 通过备份RESTORE吐出所需的归档日志 
rman target /catalog start with '/tmp/0c01l775_1_1';crosscheck archivelog all;run {allocate channel ch01 device type disk;SET ARCHIVELOG DESTINATION TO '/archivelog';restore ARCHIVELOG SEQUENCE 7;release channel ch01;}## 再次recover进行恢复至指定时间点 2021-06-1718:06:00run {sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:06:00';allocate channel ch01 device type disk;recover database skip tablespace LTEST,EXAMPLE;release channel ch01;}

sqlplus /as sysdba
alter database open read only;

sqlplus /as sysdba
select*from lucifer.lucifer;

## 关闭数据库
sqlplus /as sysdba
shutdown immediate;## 开启数据库到mount状态
startup mount pfile='/tmp/pfile.ora';## 重复 第7步，往前推进1分钟，调整时间点为 `2021/06/0818:07:00`
rman target /run {sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';set until time '2021-06-17 18:07:00';allocate channel ch01 device type disk;recover database skip tablespace LTEST,EXAMPLE;release channel ch01;}

sqlplus /as sysdba
## 创建dblinnk
CREATE PUBLIC DATABASE LINK ORCL112
CONNECT TO lucifer
IDENTIFIED BY lucifer
USING '(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.211.55.112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl))))';## 通过dblink捞取数据
createtable lucifer.lucifer_0618asselect/*+full(lucifer)*/*from lucifer.lucifer@ORCL112;select*from lucifer.lucifer_0618;