新控制文件恢复旧的备份（表空间被删除新控制文件里面没有）

备份数据文件， --删除表空间  ， 备份控制文件，  恢复控制文件，恢复数据库  前期环境和准备： [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 、、