一、数据库闪回
sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
session altered.
elapsed: 00:00:00.00
sql> select sysdate from dual;
sysdate
-------------------
2023-06-09 16:53:34
elapsed: 00:00:00.00
sql> select count(*) from t;
count(*)
----------
72605
elapsed: 00:00:00.01
sql> truncate table t;
table truncated.
elapsed: 00:00:00.04
sql> commit;
commit complete.
elapsed: 00:00:00.00
sql> select count(*) from t;
count(*)
----------
0
elapsed: 00:00:00.00
sql> shutdown immediate
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount
oracle instance started.
total system global area 2415917880 bytes
fixed size 26594104 bytes
variable size 620756992 bytes
database buffers 1761607680 bytes
redo buffers 6959104 bytes
database mounted.
sql>
sql> flashback database to timestamp timestamp'2023-06-09 16:53:34';
flashback complete.
elapsed: 00:00:00.98
sql> alter database open resetlogs;
database altered.
elapsed: 00:00:30.73
sql> select count(*) from t;
count(*)
----------
72605
elapsed: 00:00:00.02
二、异机恢复
1、编造参数文件
[oracle@19c02 ~]$ vi pfile.ora
db_name=test
2、查找参数文件备份集
bs key type lv size device type elapsed time completion time
------- ---- -- ---------- ----------- ------------ ---------------
3 full 10.17m disk 00:00:01 12-jun-23
bp key: 3 status: available compressed: no tag: tag20230612t151253
piece name: /u01/app/oracle/fast_recovery_area/orcl/backupset/2023_06_12/o1_mf_ncnnf_tag20230612t151253_l8fkcplb_.bkp
control file included: ckp scn: 2142585 ckp time: 12-jun-23
bs key type lv size device type elapsed time completion time
------- ---- -- ---------- ----------- ------------ ---------------
4 full 10.20m disk 00:00:00 12-jun-23
bp key: 4 status: available compressed: no tag: tag20230612t151255
piece name: /u01/app/oracle/fast_recovery_area/orcl/autobackup/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp
spfile included: modification time: 12-jun-23
spfile db_unique_name: orcl
control file included: ckp scn: 2142594 ckp time: 12-jun-23
3、恢复参数文件
sql> startup nomount pfile='/home/oracle/pfile.ora';
oracle instance started.
total system global area 268434280 bytes
fixed size 8895336 bytes
variable size 201326592 bytes
database buffers 50331648 bytes
redo buffers 7880704 bytes
sql> disconnected from oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.3.0.0.0
[oracle@19c02 ~]$ rman target /
recovery manager: release 19.0.0.0.0 - production on mon jun 12 16:34:24 2023
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle and/or its affiliates. all rights reserved.
connected to target database: test (not mounted)
rman> restore spfile from '/u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp';
starting restore at 12-jun-23
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=20 device type=disk
channel ora_disk_1: restoring spfile from autobackup /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp
channel ora_disk_1: spfile restore from autobackup complete
finished restore at 12-jun-23
rman>
recovery manager complete.
4、恢复控制文件
[oracle@19c02 ~]$ cd /u01/app/oracle/fast_recovery_area/orcl/controlfile/
-bash: cd: /u01/app/oracle/fast_recovery_area/orcl/controlfile/: 没有那个文件或目录
[oracle@19c02 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl/controlfile/
[oracle@19c02 ~]$ cd /u01/app/oracle/oradata/orcl
-bash: cd: /u01/app/oracle/oradata/orcl: 没有那个文件或目录
[oracle@19c02 ~]$ mkdir -p /u01/app/oracle/oradata/orcl
rman> restore controlfile from '/u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325061_l8fk85q0_.bkp';
starting restore at 12-jun-23
using channel ora_disk_1
channel ora_disk_1: restoring control file
channel ora_disk_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/controlfile/o1_mf_l8fhnw9c_.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/controlfile/o1_mf_l8fhnw9w_.ctl
finished restore at 12-jun-23
rman> sql'alter database mount';
sql statement: alter database mount
released channel: ora_disk_1
5、注册备份文件
rman> catalog start with '/u01/app/oracle/oradata/2023_06_12';
searching for all files that match the pattern /u01/app/oracle/oradata/2023_06_12
list of files unknown to the database
=====================================
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t095642_l8dyttdk_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_annnn_tag20230612t115424_l8f5qjw6_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_ncnnf_tag20230612t151253_l8fkcplb_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139301435_l8dt5vg8_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139306217_l8dyv9hv_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139313265_l8f5qkyy_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325061_l8fk85q0_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp
do you really want to catalog the above files (enter yes or no)? yes
cataloging files...
cataloging done
list of cataloged files
=======================
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_ncnnf_tag20230612t151253_l8fkcplb_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325061_l8fk85q0_.bkp
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp
list of files which were not cataloged
=======================================
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t095642_l8dyttdk_.bkp
rman-07518: reason: foreign database file dbid: 1642746747 database name: orcl
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_annnn_tag20230612t115424_l8f5qjw6_.bkp
rman-07518: reason: foreign database file dbid: 1642746747 database name: orcl
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139301435_l8dt5vg8_.bkp
rman-07518: reason: foreign database file dbid: 1642746747 database name: orcl
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139306217_l8dyv9hv_.bkp
rman-07518: reason: foreign database file dbid: 1642746747 database name: orcl
file name: /u01/app/oracle/oradata/2023_06_12/o1_mf_s_1139313265_l8f5qkyy_.bkp
rman-07518: reason: foreign database file dbid: 1642746747 database name: orcl
6、还原数据库
rman> restore database;
starting restore at 12-jun-23
using channel ora_disk_1
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 00001 to /u01/app/oracle/oradata/orcl/datafile/o1_mf_system_l8fhl9b2_.dbf
channel ora_disk_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/datafile/o1_mf_sysaux_l8fhm2dj_.dbf
channel ora_disk_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/datafile/o1_mf_undotbs1_l8fhmkgh_.dbf
channel ora_disk_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/datafile/test01.dbf
channel ora_disk_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/datafile/o1_mf_users_l8fhmlj6_.dbf
channel ora_disk_1: reading from backup piece /u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp
channel ora_disk_1: piece handle=/u01/app/oracle/oradata/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp tag=tag20230612t151016
channel ora_disk_1: restored backup piece 1
channel ora_disk_1: restore complete, elapsed time: 00:00:03
finished restore at 12-jun-23
7、 恢复数据库
rman> run{
2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
3> recover database until time '2023-06-12 15:14:09';
4> }
sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
starting recover at 12-jun-23
using channel ora_disk_1
starting media recovery
channel ora_disk_1: starting archived log restore to default destination
channel ora_disk_1: restoring archived log
archived log thread=1 sequence=10
channel ora_disk_1: reading from backup piece /u01/app/oracle/oradata/2023_06_12/o1_mf_annnn_tag20230612t165350_l8fq8yrk_.bkp
channel ora_disk_1: piece handle=/u01/app/oracle/oradata/2023_06_12/o1_mf_annnn_tag20230612t165350_l8fq8yrk_.bkp tag=tag20230612t165350
channel ora_disk_1: restored backup piece 1
channel ora_disk_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fqdvhv_.arc thread=1 sequence=10
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fqdvhv_.arc recid=6 stamp=1139331355
media recovery complete, elapsed time: 00:00:00
finished recover at 12-jun-23
8、验证数据库
sql> alter database open resetlogs;
sql> select count(*) from cy.t;
count(*)
----------
72373
三、tspitr
11g以前只能按照表空间级别恢复到指定时间点，12.2版本之后可以对单表进行基于时间点的数据恢复
1、环境
sql> create table t as select * from dba_objects;
table created.
sql> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
session altered.
sql> select sysdate from dual;
sysdate
-------------------
2023-06-12 15:14:09
sql> select current_scn from v$database;
current_scn
-----------
2142781
sql> truncate table t;
table truncated.
sql>
2、恢复
[oracle@19c01 ~]$ rman target /
recovery manager: release 19.0.0.0.0 - production on mon jun 12 15:14:37 2023
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle and/or its affiliates. all rights reserved.
connected to target database: orcl (dbid=1667104156)
rman> recover table cy.t until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/' remap table cy.t:cy.t_bak;
starting recover at 12-jun-23
using target database control file instead of recovery catalog
current log archived
allocated channel: ora_disk_1
channel ora_disk_1: sid=41 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
creating automatic instance, with sid='snaa'
initialization parameters used for automatic instance:
db_name=orcl
db_unique_name=snaa_pitr_orcl
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=false
sga_target=2304m
processes=200
db_create_file_dest=/home/oracle/
log_archive_dest_1='location=/home/oracle/'
#no auxiliary parameter file used
starting up automatic instance orcl
oracle instance started
total system global area 2415917880 bytes
fixed size 8899384 bytes
variable size 520093696 bytes
database buffers 1879048192 bytes
redo buffers 7876608 bytes
automatic instance created
contents of memory script:
{
# set requested point in time
set until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')";
# 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';
}
executing memory script
executing command: set until clause
starting restore at 12-jun-23
allocated channel: ora_aux_disk_1
channel ora_aux_disk_1: sid=162 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 /u01/app/oracle/fast_recovery_area/orcl/autobackup/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp
channel ora_aux_disk_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl/autobackup/2023_06_12/o1_mf_s_1139325175_l8fkcqnr_.bkp tag=tag20230612t151255
channel ora_aux_disk_1: restored backup piece 1
channel ora_aux_disk_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/orcl/controlfile/o1_mf_l8fkhndq_.ctl
finished restore at 12-jun-23
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of memory script:
{
# set requested point in time
set until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')";
# 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 3 to new;
set newname for clone tempfile 1 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, 3;
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
renamed tempfile 1 to /home/oracle/orcl/datafile/o1_mf_temp_%u_.tmp in control file
starting restore at 12-jun-23
using channel ora_aux_disk_1
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 00001 to /home/oracle/orcl/datafile/o1_mf_system_%u_.dbf
channel ora_aux_disk_1: restoring datafile 00004 to /home/oracle/orcl/datafile/o1_mf_undotbs1_%u_.dbf
channel ora_aux_disk_1: restoring datafile 00003 to /home/oracle/orcl/datafile/o1_mf_sysaux_%u_.dbf
channel ora_aux_disk_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl/backupset/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp
channel ora_aux_disk_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl/backupset/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp tag=tag20230612t151016
channel ora_aux_disk_1: restored backup piece 1
channel ora_aux_disk_1: restore complete, elapsed time: 00:00:07
finished restore at 12-jun-23
datafile 1 switched to datafile copy
input datafile copy recid=4 stamp=1139325312 file name=/home/oracle/orcl/datafile/o1_mf_system_l8fkhssv_.dbf
datafile 4 switched to datafile copy
input datafile copy recid=5 stamp=1139325312 file name=/home/oracle/orcl/datafile/o1_mf_undotbs1_l8fkhssy_.dbf
datafile 3 switched to datafile copy
input datafile copy recid=6 stamp=1139325312 file name=/home/oracle/orcl/datafile/o1_mf_sysaux_l8fkhssw_.dbf
contents of memory script:
{
# set requested point in time
set until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')";
# 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 3 online";
# recover and open database read only
recover clone database tablespace "system", "undotbs1", "sysaux";
sql clone 'alter database open read only';
}
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 3 online
starting recover at 12-jun-23
using channel ora_aux_disk_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fkh9r0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fkh9r0_.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:00
finished recover at 12-jun-23
sql statement: alter database open read only
contents of memory script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/home/oracle/orcl/controlfile/o1_mf_l8fkhndq_.ctl'' comment=
''rman set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing memory script
sql statement: create spfile from memory
database closed
database dismounted
oracle instance shut down
connected to auxiliary database (not started)
oracle instance started
total system global area 2415917880 bytes
fixed size 8899384 bytes
variable size 520093696 bytes
database buffers 1879048192 bytes
redo buffers 7876608 bytes
sql statement: alter system set control_files = ''/home/oracle/orcl/controlfile/o1_mf_l8fkhndq_.ctl'' comment= ''rman set'' scope=spfile
oracle instance shut down
connected to auxiliary database (not started)
oracle instance started
total system global area 2415917880 bytes
fixed size 8899384 bytes
variable size 520093696 bytes
database buffers 1879048192 bytes
redo buffers 7876608 bytes
sql statement: alter database mount clone database
contents of memory script:
{
# set requested point in time
set until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 5 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 5;
switch clone datafile all;
}
executing memory script
executing command: set until clause
executing command: set newname
starting restore at 12-jun-23
allocated channel: ora_aux_disk_1
channel ora_aux_disk_1: sid=162 device type=disk
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 /home/oracle/snaa_pitr_orcl/datafile/o1_mf_test_%u_.dbf
channel ora_aux_disk_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl/backupset/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp
channel ora_aux_disk_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl/backupset/2023_06_12/o1_mf_nnndf_tag20230612t151016_l8fk6rmy_.bkp tag=tag20230612t151016
channel ora_aux_disk_1: restored backup piece 1
channel ora_aux_disk_1: restore complete, elapsed time: 00:00:01
finished restore at 12-jun-23
datafile 5 switched to datafile copy
input datafile copy recid=8 stamp=1139325383 file name=/home/oracle/snaa_pitr_orcl/datafile/o1_mf_test_l8fkl58z_.dbf
contents of memory script:
{
# set requested point in time
set until time "to_date('2023-06-12 15:14:09','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "test", "system", "undotbs1", "sysaux" delete archivelog;
alter clone database open resetlogs;
}
executing memory script
executing command: set until clause
sql statement: alter database datafile 5 online
starting recover at 12-jun-23
using channel ora_aux_disk_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fkh9r0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/orcl/archivelog/2023_06_12/o1_mf_1_10_l8fkh9r0_.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:01
finished recover at 12-jun-23
database opened
contents of memory script:
{
# create directory for datapump import
sql "create or replace directory tspitr_dirobj_dpdir as ''
/home/oracle/''";
# create directory for datapump export
sql clone "create or replace directory tspitr_dirobj_dpdir as ''
/home/oracle/''";
}
executing memory script
sql statement: create or replace directory tspitr_dirobj_dpdir as ''/home/oracle/''
sql statement: create or replace directory tspitr_dirobj_dpdir as ''/home/oracle/''
performing export of tables...
expdp> starting "sys"."tspitr_exp_snaa_pkxe":
expdp> processing object type table_export/table/table_data
expdp> processing object type table_export/table/statistics/table_statistics
expdp> processing object type table_export/table/statistics/marker
expdp> processing object type table_export/table/table
expdp> . . exported "cy"."t" 9.543 mb 72373 rows
expdp> master table "sys"."tspitr_exp_snaa_pkxe" successfully loaded/unloaded
expdp> ******************************************************************************
expdp> dump file set for sys.tspitr_exp_snaa_pkxe is:
expdp> /home/oracle/tspitr_snaa_41265.dmp
expdp> job "sys"."tspitr_exp_snaa_pkxe" successfully completed at mon jun 12 15:16:41 2023 elapsed 0 00:00:11
export completed
contents of memory script:
{
# shutdown clone before import
shutdown clone abort
}
executing memory script
oracle instance shut down
performing import of tables...
impdp> master table "sys"."tspitr_imp_snaa_vwze" successfully loaded/unloaded
impdp> starting "sys"."tspitr_imp_snaa_vwze":
impdp> processing object type table_export/table/table
impdp> processing object type table_export/table/table_data
impdp> . . imported "cy"."t_bak" 9.543 mb 72373 rows
impdp> processing object type table_export/table/statistics/table_statistics
impdp> processing object type table_export/table/statistics/marker
impdp> job "sys"."tspitr_imp_snaa_vwze" successfully completed at mon jun 12 15:16:57 2023 elapsed 0 00:00:12
import completed
removing automatic instance
automatic instance removed
auxiliary instance file /home/oracle/orcl/datafile/o1_mf_temp_l8fkj1tq_.tmp deleted
auxiliary instance file /home/oracle/snaa_pitr_orcl/onlinelog/o1_mf_3_l8fkl7lj_.log deleted
auxiliary instance file /home/oracle/snaa_pitr_orcl/onlinelog/o1_mf_2_l8fkl7gn_.log deleted
auxiliary instance file /home/oracle/snaa_pitr_orcl/onlinelog/o1_mf_1_l8fkl7bs_.log deleted
auxiliary instance file /home/oracle/snaa_pitr_orcl/datafile/o1_mf_test_l8fkl58z_.dbf deleted
auxiliary instance file /home/oracle/orcl/datafile/o1_mf_sysaux_l8fkhssw_.dbf deleted
auxiliary instance file /home/oracle/orcl/datafile/o1_mf_undotbs1_l8fkhssy_.dbf deleted
auxiliary instance file /home/oracle/orcl/datafile/o1_mf_system_l8fkhssv_.dbf deleted
auxiliary instance file /home/oracle/orcl/controlfile/o1_mf_l8fkhndq_.ctl deleted
auxiliary instance file tspitr_snaa_41265.dmp deleted
finished recover at 12-jun-23
四、fy_recover_data
黄炜大佬作品,原文网址
https://hellodba.com/reader.php?id=191&lang=en
sql> @fy_recover_data.pck
enter value for files:
old 30: -- 1. temp restore and recover tablespace & files ---
new 30: -- 1. temp restore and recover tablespace ---
package created.
package body created.
sql> exec fy_recover_data.recover_truncated_table('cy','t1')
pl/sql procedure successfully completed.
sql> select count(*) from cy.t1$;
count(*)
----------
0
sql> select count(*) from cy.t1$$;
count(*)
----------
72459
sql> insert into /* append */ cy.t1 select * from cy.t1$$;
72459 rows created.
sql> commit;
commit complete.
sql> select name from v$tablespace;
name
------------------------------------------------------------
sysaux
system
undotbs1
users
temp
test
fy_rec_data
fy_rst_data
8 rows selected.
sql> drop tablespace fy_rec_data including contents and datafiles;
tablespace dropped.
sql> drop tablespace fy_rst_data including contents and datafiles;
tablespace dropped.
bbed也可以恢复，但是操作过于危险系数较高，可以参考
https://www.modb.pro/db/15363
最后修改时间：2023-06-13 20:56:56
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。