备份数据文件， --删除表空间  ， 备份控制文件，  恢复控制文件，恢复数据库 

前期环境和准备：

[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

、、