备份数据文件， --删除表空间 ， 备份控制文件， 恢复控制文件，恢复数据库
前期环境和准备：
[oracle@localhost backup]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 24 16:30:59 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn2jpxww_.dbf
/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn2jpxwz_.dbf
/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn2jpxy5_.dbf
/u02/app/oracle/oradata/GBK/datafile/tt.dbf
/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn2jpxy8_.dbf
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
2613419
开始数据库备份：
[oracle@localhost backup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 24 17:06:05 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: GBK (DBID=2319251477)
RMAN> backup as compressed backupset database format '/backup/rmanfull_%d_%T_%s_%p.bak' ;
Starting backup at 24-NOV-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn2jpxww_.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn2jpxwz_.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn2jpxy5_.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/GBK/datafile/tt.dbf
input datafile file number=00007 name=/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn2jpxy8_.dbf
channel ORA_DISK_1: starting piece 1 at 24-NOV-24
channel ORA_DISK_1: finished piece 1 at 24-NOV-24
piece handle=/backup/rmanfull_GBK_20241124_11_1.bak tag=TAG20241124T171313 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 24-NOV-24
Starting Control File and SPFILE Autobackup at 24-NOV-24
piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241124-01 comment=NONE
Finished Control File and SPFILE Autobackup at 24-NOV-24
RMAN>
查询当前的scn 恢复的时候就是恢复到这个scn：
select to_char(current_scn) from v$database;
删除表空间：
drop tablespace TT including contents and datafiles ;
SQL> drop tablespace TT including contents and datafiles ;
Tablespace dropped.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
备份控制文件：
RMAN> backup current controlfile format '/backup/control_%d_%T_%s_%p.bak';
Starting backup at 24-NOV-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=466 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-NOV-24
channel ORA_DISK_1: finished piece 1 at 24-NOV-24
piece handle=/backup/control_GBK_20241124_13_1.bak tag=TAG20241124T172305 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-24
Starting Control File and SPFILE Autobackup at 24-NOV-24
piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241124-02 comment=NONE
Finished Control File and SPFILE Autobackup at 24-NOV-24
删除数据文件开始恢复：
[oracle@localhost datafile]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 24 17:25:03 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1073739904 bytes
Fixed Size 8947840 bytes
Variable Size 327155712 bytes
Database Buffers 734003200 bytes
Redo Buffers 3633152 bytes
RMAN>
恢复控制文件
RMAN> RESTORE CONTROLFILE from '/backup/control_GBK_20241124_13_1.bak';
Starting restore at 24-NOV-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=426 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/app/oracle/oradata/GBK/controlfile/o1_mf_mmv8to3g_.ctl
Finished restore at 24-NOV-24
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
恢复数据文件
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rmanfull_GBK_20241122_1_1.bak RECID=1 STAMP=1185733282
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241122-00 RECID=2 STAMP=1185733357
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/backup/rmanfull_GBK_20241122_4_1.bak RECID=3 STAMP=1185733613
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241122-01 RECID=4 STAMP=1185733708
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241122-04 RECID=5 STAMP=1185735122
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241124-00 RECID=6 STAMP=1185899656
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/backup/rmanfull_GBK_20241124_11_1.bak RECID=7 STAMP=1185901993
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/app/oracle/product/19.9.0/db_1/dbs/c-2319251477-20241124-01 RECID=8 STAMP=1185902058
Crosschecked 8 objects
RMAN> delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /backup/rmanfull_GBK_20241122_1_1.bak
3 3 1 1 EXPIRED DISK /backup/rmanfull_GBK_20241122_4_1.bak
Do you really want to delete the above objects (enter YES or NO)?
Do you really want to delete the above objects (enter YES or NO)? yes
" yes" is an invalid response - please re-enter.
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/backup/rmanfull_GBK_20241122_1_1.bak RECID=1 STAMP=1185733282
deleted backup piece
backup piece handle=/backup/rmanfull_GBK_20241122_4_1.bak RECID=3 STAMP=1185733613
Deleted 2 EXPIRED objects
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=432 device type=DISK
specification does not match any archived log in the repository
RMAN>
RMAN> restore database ;
Starting restore at 24-NOV-24
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 /u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn2jpxww_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn2jpxwz_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn2jpxy5_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn2jpxy8_.dbf
channel ORA_DISK_1: reading from backup piece /backup/rmanfull_GBK_20241124_11_1.bak
channel ORA_DISK_1: piece handle=/backup/rmanfull_GBK_20241124_11_1.bak tag=TAG20241124T171313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 24-NOV-24
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
RMAN> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
RMAN> recover database until time '20241124 17:20:45';
Starting recover at 24-NOV-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /u02/app/oracle/product/19.9.0/db_1/dbs/arch1_6_1185735115.dbf
archived log file name=/u02/app/oracle/product/19.9.0/db_1/dbs/arch1_6_1185735115.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-NOV-24
RMAN> alter database open resetlogs;
Statement processed
至此数据库启动了
可以发现： 看不到被删除的数据文件
[root@localhost datafile]# ll -shrt
total 2.6G
1.3M -rw-r----- 1 oracle oinstall 31M Nov 24 16:25 o1_mf_temp_mn2jtgs7_.tmp
5.1M -rw-r----- 1 oracle oinstall 5.1M Nov 24 17:31 o1_mf_users_mn2jpxy8_.dbf
736M -rw-r----- 1 oracle oinstall 736M Nov 24 17:31 o1_mf_undotbs1_mn2jpxy5_.dbf
1.1G -rw-r----- 1 oracle oinstall 1.1G Nov 24 17:31 o1_mf_system_mn2jpxww_.dbf
781M -rw-r----- 1 oracle oinstall 781M Nov 24 17:31 o1_mf_sysaux_mn2jpxwz_.dbf
SQL> select name,file# from v$datafile;
NAME FILE#
---------------------------------------------------------------------------------------------------------------------------------- ----------
/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn7o4qkg_.dbf 1
/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn7o4qkh_.dbf 3
/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn7o4qkw_.dbf 4
/u02/app/oracle/product/19.9.0/db_1/dbs/MISSING00005 5
/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn7o4qkx_.dbf 7
恢复这个数据文件
run
{
allocate channel c0 type disk;
set newname for datafile 5 to '/u02/app/oracle/oradata/GBK/datafile/tt.dbf';
restore datafile 5;
release channel c0;
}
正常回复到drop前
RMAN> run
{
allocate channel c0 type disk;
set newname for datafile 5 to '/u02/app/oracle/oradata/GBK/datafile/tt.dbf';
restore datafile 5;
release channel c0;
}
2> 3> 4> 5> 6> 7>
released channel: ORA_DISK_1
allocated channel: c0
channel c0: SID=426 device type=DISK
executing command: SET NEWNAME
Starting restore at 24-NOV-24
channel c0: starting datafile backup set restore
channel c0: specifying datafile(s) to restore from backup set
channel c0: restoring datafile 00005 to /u02/app/oracle/oradata/GBK/datafile/tt.dbf
channel c0: reading from backup piece /backup/rmanfull_GBK_20241124_11_1.bak
channel c0: piece handle=/backup/rmanfull_GBK_20241124_11_1.bak tag=TAG20241124T171313
channel c0: restored backup piece 1
channel c0: restore complete, elapsed time: 00:00:01
Finished restore at 24-NOV-24
released channel: c0
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy "/u02/app/oracle/oradata/GBK/datafile/tt.dbf"
SQL> recover datafile 5;
ORA-00279: change 2616216 generated at 11/24/2024 17:13:13 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/product/19.9.0/db_1/dbs/arch1_6_1185735115.dbf
ORA-00280: change 2616216 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL>
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn2jpxww_.dbf
SYSTEM
/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn2jpxwz_.dbf
ONLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn2jpxy5_.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u02/app/oracle/oradata/GBK/datafile/tt.dbf
OFFLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn2jpxy8_.dbf
ONLINE
SQL> alter database datafile 5 online;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------------------------------------------------------------------------------------------------- -------
/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn7o4qkg_.dbf SYSTEM
/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn7o4qkh_.dbf ONLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn7o4qkw_.dbf ONLINE
/u02/app/oracle/oradata/GBK/datafile/tt.dbf OFFLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn7o4qkx_.dbf ONLINE
SQL> alter database datafile 5 online;
Database altered.
SQL> select name,status from v$datafile;
NAME STATUS
---------------------------------------------------------------------------------------------------------------------------------- -------
/u02/app/oracle/oradata/GBK/datafile/o1_mf_system_mn7o4qkg_.dbf SYSTEM
/u02/app/oracle/oradata/GBK/datafile/o1_mf_sysaux_mn7o4qkh_.dbf ONLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_undotbs1_mn7o4qkw_.dbf ONLINE
/u02/app/oracle/oradata/GBK/datafile/tt.dbf ONLINE
/u02/app/oracle/oradata/GBK/datafile/o1_mf_users_mn7o4qkx_.dbf ONLINE
SQL> select count(*) from zc.tt;
COUNT(*)
----------
73231
、、
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
