一、前言
常言道-“有备无患”，备份对于一套数据库来说十分重要，当遇到天灾人祸的时候有效的备份是数据库最后的希望，也是DBA从业人员自救的最后手段。一款不错的备份软件，一个适用的备份策略，对于数据保护来说是十分必要的，对于数据库系统要定时做有效的全备，以便随时恢复到任意时间点。在不同环境下有多种恢复环境，不同环境也会使用不同的方式来恢复，有时候方式不止一种。总之，有效的备份和不同环境下的恢复手段对于DBA从业人员来说是十分重要的。
二、Oracle备份
1、Oracle冷备
冷备是指Oracle数据库处于冷状态（即停机状态）的备份，此类型的备份操作简单，但是不能恢复到任意时间点、每次备份需要停机且数据量大的话停机时间长，恢复不灵活不适用核心7*24小时的生产Oracle数据库。备份只需将数据库停机复制数据文件、控制文件和参数文件即可，生产环境使用罕见此处不再讨论。
[oracle@edsir4p1-PROD1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 14 08:39:25 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@edsir4p1-PROD1 ~]$ cd /u01/app/oracle/oradata/
[oracle@edsir4p1-PROD1 oradata]$ tar -zcvf PROD1.tar.gz PROD1
PROD1/
PROD1/tools.dbf
PROD1/oltp.dbf
PROD1/temp1.dbf
PROD1/test.dbf
PROD1/datafile/
PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf
PROD1/datafile/o1_mf_users_bpy12gjt_.dbf
PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
PROD1/datafile/o1_mf_temp_bpy15m62_.tmp
PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf
PROD1/datafile/o1_mf_system_bpy12gck_.dbf
PROD1/indx.dbf
PROD1/controlfile/
PROD1/controlfile/control01.ctl
PROD1/controlfile/control02.ctl
PROD1/onlinelog/
PROD1/onlinelog/o1_mf_2_bpy15d87_.log
PROD1/onlinelog/o1_mf_3_bpy15h47_.log
PROD1/onlinelog/o1_mf_1_bpy159nj_.log
PROD1/temp2.dbf
[oracle@edsir4p1-PROD1 oradata]$
[oracle@edsir4p1- PROD1]$ cd /u01/app/oracle/flash_recovery_area/PROD1/controlfile
[oracle@edsir4p1- controlfile]$ tar -zcvf control03.ctl.tar.gz control03.ctl
control03.ctl
[oracle@edsir4p1- controlfile]$2、Oracle热备
热备是相对于冷备来说的，是指Oracle数据库处于热状态（即运行状态）的备份，此类型的备份有很多方式实现，主要优点是数据库一直运行，适用于7*24小时的生产Oracle数据库环境。下边对热备份的一种常用方式进行讨论：
exp
exp是Oracle数据库集成的一种逻辑备份工具。可在服务端和客户端运行，备份会在指定的file路径下生成一个同数据库中表同等大小的dmp转储文件，转储文件是一个二进制的不可读文件，实际上exp命令会在Oracle后台运行PLSQL过程，将表中的数据逻辑导出。
①数据逻辑备份，不存在物理坏块。
②在备份过程中不影响生产，对数据库性能影响不大。
③只能将数据库恢复到备份时刻，不能恢复到任意时间点。
expdp
expdp是Oracle 10g以后集成的一种新型的逻辑备份工具，是exp的增强只能在服务端运行，备份同样会在directory下生成一个同数据库中表同等大小的dmp转储文件（一般比exp转储文件较小），同样也是二进制不可读文件，通过后台PLSQL过程逻辑导出表数据。
①数据逻辑备份，不存在物理坏块。
②导出速度比exp方式快同样对数据库影响不大。
③只能将数据库恢复到备份时刻，不能恢复到任意时间点。
rman
rman是Oracle集成的一种物理备份免安装工具，它通过调用sys.DBMS_RCVMAN和sys.DBMS_BACKUP_RESTORE两个数据包来进行备份，不依赖与Oracle运行，在备份过程中会进行块校验和块压缩，将数据块在pga中整合成备份集备份到磁带或磁盘中。
①恢复不依赖与Oracle运行，可在Oracle未运行状态下恢复数据库。
②可对数据库进行全备，增量备份，归档备份。并且恢复到任意时间点。
③备份过程中将数据块在pga中整合成备份集备份到磁带或磁盘中，备份方式更灵活。
④相比冷备简化了恢复的过程，在Oracle 11g引入了DRA（Data Recovery Advisor）更是简化了恢复的难度。
⑤由于物理备份会进行块校验和块压缩，可能会存在物理坏块的情况。
⑥由于rman备份需要分配内存写入磁盘，整个过程中对数据库性能有一定影响，一般都是在业务低峰期进行时间较长的备份（全量备份）。
archivelog模式：此模式下Oracle能够在open状态下进行一致性备份
[oracle@edsir4p1-PROD1 ~]$ cat rman.sh
rman target / <<EOF
run{
backup database format '/home/oracle/backup/full_%d_%U.bak';
sql 'alter system switch logfile';
backup archivelog all format '/home/oracle/backup/full_%d_%U.arch';
backup current controlfile format '/home/oracle/backup/full_%d_%U.ctl';
}
EOF
[oracle@edsir4p1-PROD1 ~]$ ./rman.sh
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 14 08:46:09 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015)
RMAN> 2> 3> 4> 5> 6>
Starting backup at 14-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=172 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=173 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/PROD1/tools.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_1: starting piece 1 at 14-DEC-19
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/test.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf
channel ORA_DISK_2: starting piece 1 at 14-DEC-19
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_3: starting piece 1 at 14-DEC-19
channel ORA_DISK_2: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0qujd02m_1_1.bak tag=TAG20191214T084613 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0pujd02m_1_1.bak tag=TAG20191214T084613 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_3: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0rujd02m_1_1.bak tag=TAG20191214T084613 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:26
Finished backup at 14-DEC-19
Starting Control File and SPFILE Autobackup at 14-DEC-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982001_gz98ck8t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-19
sql statement: alter system switch logfile
Starting backup at 14-DEC-19
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
skipping archived log file /home/oracle/arch1/1_23_881415849.dbf; already backed up 1 time(s)
skipping archived log file /home/oracle/arch1/1_24_881415849.dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=25 RECID=3 STAMP=1013263599
input archived log thread=1 sequence=26 RECID=4 STAMP=1015907299
input archived log thread=1 sequence=27 RECID=5 STAMP=1015912834
input archived log thread=1 sequence=28 RECID=6 STAMP=1015920206
input archived log thread=1 sequence=29 RECID=7 STAMP=1015927655
channel ORA_DISK_1: starting piece 1 at 14-DEC-19
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=8 STAMP=1015983209
input archived log thread=1 sequence=31 RECID=9 STAMP=1015995608
input archived log thread=1 sequence=32 RECID=10 STAMP=1016000056
input archived log thread=1 sequence=33 RECID=11 STAMP=1017805874
channel ORA_DISK_2: starting piece 1 at 14-DEC-19
channel ORA_DISK_3: starting archived log backup set
channel ORA_DISK_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=12 STAMP=1017813651
input archived log thread=1 sequence=35 RECID=13 STAMP=1018342838
input archived log thread=1 sequence=36 RECID=14 STAMP=1018343869
input archived log thread=1 sequence=37 RECID=15 STAMP=1018343906
input archived log thread=1 sequence=38 RECID=16 STAMP=1018352461
channel ORA_DISK_3: starting piece 1 at 14-DEC-19
channel ORA_DISK_4: starting archived log backup set
channel ORA_DISK_4: specifying archived log(s) in backup set
input archived log thread=1 sequence=39 RECID=17 STAMP=1018352621
input archived log thread=1 sequence=40 RECID=18 STAMP=1026981487
input archived log thread=1 sequence=41 RECID=19 STAMP=1026981604
input archived log thread=1 sequence=42 RECID=20 STAMP=1026982003
input archived log thread=1 sequence=43 RECID=21 STAMP=1026982003
channel ORA_DISK_4: starting piece 1 at 14-DEC-19
channel ORA_DISK_2: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0uujd03k_1_1.arch tag=TAG20191214T084643 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:06
channel ORA_DISK_3: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0vujd03k_1_1.arch tag=TAG20191214T084643 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_4: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_10ujd03m_1_1.arch tag=TAG20191214T084643 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_0tujd03j_1_1.arch tag=TAG20191214T084643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:19
Finished backup at 14-DEC-19
Starting backup at 14-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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 14-DEC-19
channel ORA_DISK_1: finished piece 1 at 14-DEC-19
piece handle=/home/oracle/backup/full_PROD1_11ujd046_1_1.ctl tag=TAG20191214T084702 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-DEC-19
Starting Control File and SPFILE Autobackup at 14-DEC-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982024_gz98d8sr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-19
RMAN>
Recovery Manager complete.
noarchivelog模式：此模式下Oracle不能再open状态下进行一致性备份，需要在mount状态下才能进行一致性备份。
SYS@PROD1> alter database open;
Database altered.
SYS@PROD1> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/arch1
Oldest online log sequence 15
Current log sequence 17
SYS@PROD1> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 05:00:31 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015)
RMAN> backup database format '/home/oracle/full_%s%t%d%U.bak';
Starting backup at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=166 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=160 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=18 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=167 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/15/2019 05:00:41
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 12/15/2019 05:00:41
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
RMAN-03009: failure of backup command on ORA_DISK_3 channel at 12/15/2019 05:00:41
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 12/15/2019 05:00:41
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
SYS@PROD1> shutdown immediate;
startup mount;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
SYS@PROD1> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 05:01:17 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015, not open)
RMAN> backup database format '/home/oracle/full_%s%t%d%U.bak';
Starting backup at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf
channel ORA_DISK_1: starting piece 1 at 15-DEC-19
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_2: starting piece 1 at 15-DEC-19
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/test.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/PROD1/oltp.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_3: starting piece 1 at 15-DEC-19
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_4: starting piece 1 at 15-DEC-19
channel ORA_DISK_1: finished piece 1 at 15-DEC-19
piece handle=/home/oracle/full_831027054888PROD12jujf798_1_1.bak tag=TAG20191215T050127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_2: finished piece 1 at 15-DEC-19
piece handle=/home/oracle/full_841027054888PROD12kujf798_1_1.bak tag=TAG20191215T050127 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:27
channel ORA_DISK_3: finished piece 1 at 15-DEC-19
piece handle=/home/oracle/full_851027054888PROD12lujf798_1_1.bak tag=TAG20191215T050127 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:27
channel ORA_DISK_4: finished piece 1 at 15-DEC-19
piece handle=/home/oracle/full_861027054889PROD12mujf799_1_1.bak tag=TAG20191215T050127 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:26
Finished backup at 15-DEC-19
Starting Control File and SPFILE Autobackup at 15-DEC-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054861_gzchl3h1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-19rman除了进行备份恢复以外，还能对数据库进行数据传输（dulplicate），坏块修复（recover block）甚至XTTS底层也是perl语言调用的rman包。可以说功能巨大。
关于rman详细可参照：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-architecture.html#GUID-8ECC1FB0-7D25-4883-B9A6-2593FDB35940 或https://blog.csdn.net/renyanjie123/article/details/103539981
copy
copy是rman里的一个数据文件复制命令，同样也可以做数据备份，它同rman备份的区别在与copy只是单纯的复制数据文件，并不会像rman备份那样生成一个备份集备份到磁带或磁盘中，但是copy由于不会产生备份集只是简单的cp命令，所以并没有rman那样消耗性能。同时此命令还可用于生产库更改数据文件，用法相同。
SYS@PROD1> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TEMP1 ONLINE
TEMP2 ONLINE
TEST ONLINE
INDX ONLINE
TOOLS ONLINE
OLTP ONLINE
12 rows selected.
SYS@PROD1> alter tablespace oltp read only;
Tablespace altered.
SYS@PROD1> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TEMP1 ONLINE
TEMP2 ONLINE
TEST ONLINE
INDX ONLINE
TOOLS ONLINE
OLTP READ ONLY
12 rows selected.
SYS@PROD1> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
6 /u01/app/oracle/oradata/PROD1/test.dbf
7 /u01/app/oracle/oradata/PROD1/indx.dbf
8 /u01/app/oracle/oradata/PROD1/tools.dbf
9 /u01/app/oracle/oradata/PROD1/oltp.dbf
9 rows selected.
SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 14 08:51:30 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015)
RMAN> copy datafile '/u01/app/oracle/oradata/PROD1/oltp.dbf' to '/home/oracle/backup/oltp_bak.dbf';
Starting backup at 14-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=26 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=175 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/u01/app/oracle/oradata/PROD1/oltp.dbf
output file name=/home/oracle/backup/oltp_bak.dbf tag=TAG20191214T085214 RECID=2 STAMP=1026982335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-DEC-19
Starting Control File and SPFILE Autobackup at 14-DEC-19
piece handle=/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982337_gz98p1j0_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-19
RMAN> exit
SYS@PROD1> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TEMP1 ONLINE
TEMP2 ONLINE
TEST ONLINE
INDX ONLINE
TOOLS ONLINE
OLTP ONLINE
12 rows selected.
[oracle@edsir4p1-PROD1 backup]$ ll
-rw-r----- 1 oracle oinstall 50339840 Dec 14 08:52 oltp_bak.dbf
[oracle@edsir4p1-PROD1 backup]$ ll /u01/app/oracle/oradata/PROD1/oltp.dbf
-rw-r----- 1 oracle oinstall 50339840 Dec 14 08:53 /u01/app/oracle/oradata/PROD1/oltp.dbf总结：以上四种备份方式，rman备份是最常用的，expdp/exp常用于数据导出，一般这两种方式配合使用对数据库进行备份。
三、Oracle冷备恢复
冷备恢复也是比较简单的一种恢复，它只能基于冷备份来恢复，若archivelog连续且完整并且redo没有损坏可以恢复到当前时间状态，若redo已经损坏那么只能恢复到down机较近的归档时间点，若archivelog丢失，只能恢复到有效冷备的时间点。而且需要注意的是，冷备情况下应用archivelog实际上是利用归档日志反写通过system中的数据库元数据来恢复数据，这个过程如果没有数据文件增加则不需要额外的操作就能正常恢复到open状态，若这个过程中有数据文件增加，那么就需要指定数据文件来恢复数据。
关闭数据库冷备，备份时间是10：28
[oracle@edsir4p1-PROD1 oradata]$ tar -zcvf PROD1.tar.gz PROD1
PROD1/
PROD1/tools.dbf
PROD1/oltp.dbf
PROD1/temp1.dbf
PROD1/test.dbf
PROD1/datafile/
PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf
PROD1/datafile/o1_mf_users_bpy12gjt_.dbf
PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
PROD1/datafile/o1_mf_temp_bpy15m62_.tmp
PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf
PROD1/datafile/o1_mf_system_bpy12gck_.dbf
PROD1/indx.dbf
PROD1/controlfile/
PROD1/controlfile/control01.ctl
PROD1/controlfile/control02.ctl
PROD1/onlinelog/
PROD1/onlinelog/o1_mf_2_bpy15d87_.log
PROD1/onlinelog/o1_mf_3_bpy15h47_.log
PROD1/onlinelog/o1_mf_1_bpy159nj_.log
PROD1/temp2.dbf
[oracle@edsir4p1-PROD1 oradata]$ ll
total 348232
drwxr-x--- 5 oracle oinstall 4096 Jul 6 10:08 PROD1
-rw-r--r-- 1 oracle oinstall 356225568 Dec 14 10:28 PROD1.tar.gz
drwxr-x--- 5 oracle oinstall 4096 Jun 3 2015 PROD2
[oracle@edsir4p1-PROD1 oradata]$ cd /u01/app/oracle/flash_recovery_area/PROD1/controlfile/
[oracle@edsir4p1-PROD1 controlfile]$ ls
control03.ctl control03.ctl.tar.gz
[oracle@edsir4p1-PROD1 controlfile]$ rm control03.ctl.tar.gz
[oracle@edsir4p1-PROD1 controlfile]$ tar -zcvf control03.ctl.tar.gz control03.ctl
control03.ctl
[oracle@edsir4p1-PROD1 controlfile]$ ll
total 9564
-rw-r----- 1 oracle oinstall 9748480 Dec 14 10:25 control03.ctl
-rw-r--r-- 1 oracle oinstall 25874 Dec 14 10:29 control03.ctl.tar.gz
模拟业务继续进行，创建表空间
SYS@PROD1> create tablespace tbs datafile '/u01/app/oracle/oradata/PROD1/tbs01.dbf' size 50m;
Tablespace created.
SYS@PROD1> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf
6 /u01/app/oracle/oradata/PROD1/test.dbf
7 /u01/app/oracle/oradata/PROD1/indx.dbf
8 /u01/app/oracle/oradata/PROD1/tools.dbf
9 /u01/app/oracle/oradata/PROD1/oltp.dbf
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf
10 rows selected.
SYS@PROD1> create table test (id int,name varchar2(20)) ;
Table created.
SYS@PROD1> insert into test values(1,'ren');
1 row created.
SYS@PROD1> commit;
Commit complete.
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 2 YES INACTIVE 1363368 14-DEC-19 1363371 14-DEC-19
2 1 5 52428800 512 2 YES INACTIVE 1363371 14-DEC-19 1363375 14-DEC-19
3 1 6 52428800 512 2 NO CURRENT 1363375 14-DEC-19 2.8147E+14
SYS@PROD1> alter system switch logfile;
System altered.
切换归档之后现在归档SEQUENCE#到6
[oracle@edsir4p1- arch1]$ ll
total 956
-rw-r----- 1 oracle oinstall 750592 Dec 14 10:31 1_1_1026987841.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 14 10:31 1_2_1026987841.dbf
-rw-r----- 1 oracle oinstall 2048 Dec 14 10:31 1_3_1026987841.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 14 10:31 1_4_1026987841.dbf
-rw-r----- 1 oracle oinstall 1024 Dec 14 10:31 1_5_1026987841.dbf
-rw-r----- 1 oracle oinstall 197120 Dec 14 10:34 1_6_1026987841.dbf
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 2 NO CURRENT 1363882 14-DEC-19 2.8147E+14
2 1 5 52428800 512 2 YES INACTIVE 1363371 14-DEC-19 1363375 14-DEC-19
3 1 6 52428800 512 2 YES ACTIVE 1363375 14-DEC-19 1363882 14-DEC-19
业务进行下去，突然宕机，该事物没有提交
SYS@PROD1> insert into test values(2,'li');
1 row created.
SYS@PROD1> select * from test;
ID NAME
---------- --------------------------------------------------------------------------------
1 ren
2 li
SYS@PROD1> shutdown abort
ORACLE instance shut down.
这里分解动作启动到mount手动恢复。
SYS@PROD1> startup mount;
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
冷备数据文件过早没有新加的表空间，需要应用归档恢复
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf 1363224 1363224
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf 1363224 1363224
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf 1363224 1363224
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf 1363224 1363224
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf 1363224 1363224
6 /u01/app/oracle/oradata/PROD1/test.dbf 1363224 1363224
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1363224 1363224
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1363224 1363224
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1363224 1363224
9 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1363224
2 1363224
3 1363224
4 1363224
5 1363224
6 1363224
7 1363224
8 1363224
9 1363224
9 rows selected.
SYS@PROD1> recover database using backup controlfile;
ORA-00279: change 1363224 generated at 12/14/2019 10:25:46 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_1_1026987841.dbf
ORA-00280: change 1363224 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1363360 generated at 12/14/2019 10:31:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_2_1026987841.dbf
ORA-00280: change 1363360 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arch1/1_1_1026987841.dbf' no longer needed for this recovery
ORA-00279: change 1363363 generated at 12/14/2019 10:31:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_3_1026987841.dbf
ORA-00280: change 1363363 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arch1/1_2_1026987841.dbf' no longer needed for this recovery
ORA-00279: change 1363368 generated at 12/14/2019 10:31:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_4_1026987841.dbf
ORA-00280: change 1363368 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arch1/1_3_1026987841.dbf' no longer needed for this recovery
ORA-00279: change 1363371 generated at 12/14/2019 10:31:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_5_1026987841.dbf
ORA-00280: change 1363371 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arch1/1_4_1026987841.dbf' no longer needed for this recovery
ORA-00279: change 1363375 generated at 12/14/2019 10:31:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_6_1026987841.dbf
ORA-00280: change 1363375 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arch1/1_5_1026987841.dbf' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD1/tbs01.dbf'
ORA-01112: media recovery not started
发现没有tbs01.dbf文件，查看数据文件，新增了一个UNNAMED00010在ORACLE_HOME/dbs下，实际上并没有这个文件
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf 1363375
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf 1363375
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf 1363375
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf 1363375
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf 1363375
6 /u01/app/oracle/oradata/PROD1/test.dbf 1363375
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1363375
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1363375
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1363375
10 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010 1363422
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1363427
2 1363427
3 1363427
4 1363427
5 1363427
6 1363427
7 1363427
8 1363427
9 1363427
10 0
10 rows selected.
[oracle@edsir4p1-PROD1 dbs]$ ll /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010
ls: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010: No such file or directory
[oracle@edsir4p1-PROD1 controlfile]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@edsir4p1-PROD1 dbs]$ ll
total 19128
-rw-rw---- 1 oracle oinstall 1544 Aug 27 2014 hc_DBUA0.dat
-rw-rw---- 1 oracle oinstall 1544 Aug 27 2014 hc_PROD1.dat
-rw-rw---- 1 oracle oinstall 1544 Aug 27 2014 hc_PROD2.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Jun 3 2015 lkPROD1
-rw-r----- 1 oracle oinstall 24 Jun 3 2015 lkPROD2
-rw-r----- 1 oracle oinstall 1536 Aug 10 04:25 orapwPROD1
-rw-r----- 1 oracle oinstall 1536 Jun 3 2015 orapwPROD2
drwx------ 2 oracle oinstall 4096 Aug 27 2014 peshm__0
drwx------ 2 oracle oinstall 4096 Aug 27 2014 peshm_DBUA0_0
drwx------ 2 oracle oinstall 4096 Aug 27 2014 peshm_PROD1_0
drwx------ 2 oracle oinstall 4096 Aug 27 2014 peshm_PROD2_0
-rw-r----- 1 oracle oinstall 9748480 Dec 14 10:24 snapcf_PROD1.f
-rw-r----- 1 oracle oinstall 9748480 Jul 10 14:06 snapcf_PROD2.f
-rw-r----- 1 oracle oinstall 3584 Dec 14 10:37 spfilePROD1.ora
-rw-r----- 1 oracle oinstall 3584 Sep 7 08:42 spfilePROD2.ora
实际上数据目录下也没有这个文件
[oracle@edsir4p1-PROD1 PROD1]$ ll
total 613040
drwxr-x--- 2 oracle oinstall 4096 Sep 7 09:12 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jun 3 2015 datafile
-rw-r----- 1 oracle oinstall 41951232 Dec 14 10:38 indx.dbf
-rw-r----- 1 oracle oinstall 50339840 Dec 14 10:38 oltp.dbf
drwxr-x--- 2 oracle oinstall 4096 Jun 3 2015 onlinelog
-rw-r----- 1 oracle oinstall 52436992 Sep 7 12:28 temp1.dbf
-rw-r----- 1 oracle oinstall 52436992 Dec 14 08:38 temp2.dbf
-rw-r----- 1 oracle oinstall 419438592 Dec 14 10:38 test.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 14 10:38 tools.dbf
重建数据文件之后再次查看，如果使用rman恢复这个过程是自动创建的。
SYS@PROD1> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00010' as '/u01/app/oracle/oradata/PROD1/tbs01.dbf';
Database altered.
[oracle@edsir4p1-PROD1 PROD1]$ ll
total 664304
drwxr-x--- 2 oracle oinstall 4096 Sep 7 09:12 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jun 3 2015 datafile
-rw-r----- 1 oracle oinstall 41951232 Dec 14 10:38 indx.dbf
-rw-r----- 1 oracle oinstall 50339840 Dec 14 10:38 oltp.dbf
drwxr-x--- 2 oracle oinstall 4096 Jun 3 2015 onlinelog
-rw-r----- 1 oracle oinstall 52436992 Dec 14 10:41 tbs01.dbf
-rw-r----- 1 oracle oinstall 52436992 Sep 7 12:28 temp1.dbf
-rw-r----- 1 oracle oinstall 52436992 Dec 14 08:38 temp2.dbf
-rw-r----- 1 oracle oinstall 419438592 Dec 14 10:38 test.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 14 10:38 tools.dbf
继续恢复查看，需要7号归档，这个归档实际上是当前数据，没有归档到归档目录。
SYS@PROD1> recover database using backup controlfile;
ORA-00279: change 1363882 generated at 12/14/2019 10:34:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_7_1026987841.dbf
ORA-00280: change 1363882 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
查看数据文件头和控制文件头已经不需要恢复了。
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_bpy12gck_.dbf 1363882
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_bpy12gfm_.dbf 1363882
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_bpy12ggz_.dbf 1363882
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_bpy12gjt_.dbf 1363882
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_bpy15nf0_.dbf 1363882
6 /u01/app/oracle/oradata/PROD1/test.dbf 1363882
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1363882
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1363882
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1363882
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1363882
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1363882
2 1363882
3 1363882
4 1363882
5 1363882
6 1363882
7 1363882
8 1363882
9 1363882
10 1363882
10 rows selected.
SYS@PROD1> recover database using backup controlfile until cancel;
ORA-00279: change 1363882 generated at 12/14/2019 10:34:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_7_1026987841.dbf
ORA-00280: change 1363882 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SYS@PROD1> alter database open resetlogs;
Database altered.
SYS@PROD1> select * from test;
ID NAME
---------- --------------------------------------------------------------------------------
1 ren
SYS@PROD1>发现此时已经恢复了，但是当前redo丢失导致数据丢失。
四、Oracle热备恢复
1、imp
利用dmp文件导入相关表
imp '\/ as sysdba\' file=xx.dmp log=xx.log fromuser=old_owner touser=new_owner buffer=6553600 ignore=y
2、impdp
利用dmp文件导入相关表
impdp '\/ as sysdba\' directory=d1 dumpfile=xx.dmp dumplog=xx.log
3、rman
rman恢复是生产中使用最多的数据恢复方式之一，它可以将有效的备份集恢复到任意时刻满足生产库数据不丢失的先决条件。在数据库中分为归档模式和非归档模式，此处不在详细描述这两者的区别，重点讨论两种不同模式下的rman恢复方式。
noarchive模式
此模式下在Oracleopen状态无法进行一致性备份，需将数据库置于mount状态才能进行一致性备份，在有有效rman备份集的情况下可正常进行介质恢复。由于归档模式Oracle会将在buffercache中的变化同时记录在log buffer中，通过lgwr进程将数据写出内存到online redo中，在通过arch进程将redo内容写到archivelog中，此日志用于Oracle数据恢复时使用。在非归档模式就不会有arch写出online redo这一动作。实例恢复是不需要备份介质的恢复方式，使用recover database 即可使用online redo来恢复当前数据库。在ORACLE RAC中，redo磁盘组存放在共享存储中，recover database 需要从online redo中恢复当前实例。所以在该环境下所有的rman恢复都是不完全恢复，一旦故障就意味着丢失一定量级的数据，在实际生产中使用非常少。
在非归档模式下由于没有连续的归档产生，历史备份数据较老，所以这种环境下一定是一个不完全恢复。
SYS@PROD1> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/arch1
Oldest online log sequence 1
Current log sequence 3
[oracle@edsir4p1-PROD1 PROD1]$ rm *.dbf
[oracle@edsir4p1-PROD1 oradata]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 04:12:12 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015, not open)
RMAN> restore database;
Starting restore at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
creating datafile file number=10 name=/u01/app/oracle/oradata/PROD1/tbs01.dbf
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 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_15ujf0a4_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_14ujf0a4_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf
channel ORA_DISK_3: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf
channel ORA_DISK_3: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_16ujf0a4_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_15ujf0a4_1_1.bak tag=TAG20191215T030228
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_14ujf0a4_1_1.bak tag=TAG20191215T030228
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:26
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_16ujf0a4_1_1.bak tag=TAG20191215T030228
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:26
Finished restore at 15-DEC-19
--查看当前数据文件头SCN和控制文件SCN，明显不一致，控制文件很新且中间归档丢失，只能进行不完全恢复
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1467965
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1467965
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1467965
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1467965
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1467965
6 /u01/app/oracle/oradata/PROD1/test.dbf 1467965
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1467965
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1467965
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1467965
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1467965
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1363091
2 1363095
3 1363093
4 1363093
5 1363095
6 1363093
7 1363091
8 1363091
9 1363095
10 1363468
10 rows selected.
--尝试恢复数据库
RMAN> recover database;
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/1_1_1027047663.dbf
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/1_2_1027047663.dbf
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/1_3_1027047663.dbf
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch1/1_4_1027047663.dbf
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/1_5_1027047663.dbf
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/1_6_1027047663.dbf
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/1_7_1027047663.dbf
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/1_8_1027047663.dbf
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch1/1_9_1027047663.dbf
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/arch1/1_10_1027047663.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcdtfll_.log
archived log file name=/home/oracle/arch1/1_1_1027047663.dbf thread=1 sequence=1
archived log file name=/home/oracle/arch1/1_2_1027047663.dbf thread=1 sequence=2
archived log file name=/home/oracle/arch1/1_3_1027047663.dbf thread=1 sequence=3
archived log file name=/home/oracle/arch1/1_4_1027047663.dbf thread=1 sequence=4
archived log file name=/home/oracle/arch1/1_5_1027047663.dbf thread=1 sequence=5
archived log file name=/home/oracle/arch1/1_6_1027047663.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch1/1_7_1027047663.dbf thread=1 sequence=7
archived log file name=/home/oracle/arch1/1_8_1027047663.dbf thread=1 sequence=8
archived log file name=/home/oracle/arch1/1_9_1027047663.dbf thread=1 sequence=9
archived log file name=/home/oracle/arch1/1_10_1027047663.dbf thread=1 sequence=10
RMAN-08187: WARNING: media recovery until SCN 1363915 complete
archived log file name=/home/oracle/arch1/1_10_1027047663.dbf thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/15/2019 04:20:19
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/home/oracle/arch1/1_10_1027047663.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01112: media recovery not started
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 1363915
--发现由于非归档模式日志缺失只能进行不完全恢复，将数据库恢复到sequence 10，发现恢复完scn为1363915
RMAN> recover database until sequence 10;
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcdtfll_.log
RMAN-08187: WARNING: media recovery until SCN 1363915 complete
Finished recover at 15-DEC-19
--由于进行不完全恢复，需要resetlogs重置lsn起库，关于RESETLOGS可详见：https://blog.csdn.net/renyanjie123/article/details/82935937
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1363915 1363915
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1363915 1363915
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1363915 1363915
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1363915 1363915
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1363915 1363915
6 /u01/app/oracle/oradata/PROD1/test.dbf 1363915 1363915
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1363915 1363915
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1363915 1363915
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1363915 1363915
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1363915 1363915
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1363915
2 1363915
3 1363915
4 1363915
5 1363915
6 1363915
7 1363915
8 1363915
9 1363915
10 1363915
10 rows selected.
RMAN> alter database open resetlogs;
database openedarchive模式
在此模式下rman不仅在open状态下能进行一致性备份。还能利用有效备份集进行介质恢复，在存在全量或增量备份集以后连续的归档文件并且不存在online redo损坏的前提下，可将数据库恢复到任意时刻。在ORACLE RAC中，各个节点都要有各个节点归档目录的读权限和对当前节点归档目录的写权限，在生产中各个节点事物变化都是串行的，当一节点或多节点down机时，也可通过recover database 通过读取所有节点的archIvelog来恢复，在介质恢复时是利用rman将介质中的备份集恢复到数据磁盘组中，在通过归档或增量备份恢复到当前时间点。
常用命令：
restore database from 'xxx';
recover database from 'xxx';
restore controlfile from 'xxx';
recover controlfile from 'xxx';
recover datafile 8 block 13;
关于Oraclerman恢复，这里简单讨论数据文件损坏的情况，后面会详细讨论不同类型文件损坏的恢复方式。
[oracle@edsir4p1-PROD1 PROD1]$ rm *.dbf
SYS@PROD1> startup mount;
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 04:37:13 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015, not open)
RMAN> restore database;
Starting restore at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
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 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_1uujf5mi_1_1.bak --从rman备份中恢复
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_1sujf5mi_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf
channel ORA_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf
channel ORA_DISK_3: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_1tujf5mi_1_1.bak
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf
channel ORA_DISK_4: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf
channel ORA_DISK_4: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_4: reading from backup piece /home/oracle/backup/full_PROD1_1vujf5mj_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_1uujf5mi_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_1sujf5mi_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:28
channel ORA_DISK_4: piece handle=/home/oracle/backup/full_PROD1_1vujf5mj_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:28
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_1tujf5mi_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:38
Finished restore at 15-DEC-19
RMAN> recover database;
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-DEC-19
--查看数据文件头scn和控制文件头scn是否一致，一致可尝试open数据库
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1385736 1385736
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1385736 1385736
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1385736 1385736
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1385736 1385736
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1385736 1385736
6 /u01/app/oracle/oradata/PROD1/test.dbf 1385736 1385736
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1385736 1385736
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1385736 1385736
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1385736 1385736
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1385736 1385736
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1385736
2 1385736
3 1385736
4 1385736
5 1385736
6 1385736
7 1385736
8 1385736
9 1385736
10 1385736
10 rows selected.
RMAN> alter database open;
database opened
在Oracle11g以后Oracle引入了DRA（Data Recovery Advisor），DRA简化了数据库的恢复步骤。
[oracle@edsir4p1-PROD1 PROD1]$ rm *.dbf
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD1/test.dbf'
SYS@PROD1>
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 04:40:24 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> list failure; --列出当前数据库的故障
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2345 HIGH OPEN 15-DEC-19 One or more non-system datafiles are missing
8 HIGH OPEN 15-DEC-19 One or more non-system datafiles need media recovery
2402 HIGH OPEN 15-DEC-19 Name for datafile 10 is unknown in the control file
RMAN> advise failure; --Oracle建议怎样恢复
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2345 HIGH OPEN 15-DEC-19 One or more non-system datafiles are missing
8 HIGH OPEN 15-DEC-19 One or more non-system datafiles need media recovery
2402 HIGH OPEN 15-DEC-19 Name for datafile 10 is unknown in the control file
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD1/test.dbf was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD1/indx.dbf was unintentionally renamed or moved, restore it
3. If file /u01/app/oracle/oradata/PROD1/tools.dbf was unintentionally renamed or moved, restore it
4. If file /u01/app/oracle/oradata/PROD1/oltp.dbf was unintentionally renamed or moved, restore it
5. If file /u01/app/oracle/oradata/PROD1/tbs01.dbf was unintentionally renamed or moved, restore it
6. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD1/test.dbf, then replace it with the correct one
7. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD1/indx.dbf, then replace it with the correct one
8. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD1/tools.dbf, then replace it with the correct one
9. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD1/oltp.dbf, then replace it with the correct one
10. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD1/tbs01.dbf, then replace it with the correct one
11. Rename datafile 10 to the name of the real file
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 6; Restore and recover datafile 7; Restore and recover datafile 8; ...
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_1722683907.hm
RMAN> repair failure; --使用顾问方式恢复
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod1/PROD1/hm/reco_1722683907.hm
contents of repair script:
# restore and recover datafile
restore datafile 6, 7, 8, 9, 10;
recover datafile 6, 7, 8, 9, 10;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_1uujf5mi_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_1tujf5mi_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_1vujf5mj_1_1.bak
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_1tujf5mi_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_1vujf5mj_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_1uujf5mi_1_1.bak tag=TAG20191215T043426
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-DEC-19
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch1/1_13_1027052494.dbf
archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/arch1/1_14_1027052494.dbf
archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/arch1/1_15_1027052494.dbf
archived log file name=/home/oracle/arch1/1_13_1027052494.dbf thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-DEC-19
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
RMAN>
恢复表空间
recover tablespace xxx from '/bak1/tablespace_bak_XXX.bak';
恢复数据文件
recover datafile 11 from '/bak1/back.bak';
恢复单个表
RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
五、恢复spfile
1、从备份中恢复spfile
① 设置dbid
SYS@PROD1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD1.ora
[oracle@edsir4p1-PROD1 PROD1]$ cd $ORACLE_HOME/dbs
[oracle@edsir4p1-PROD1 dbs]$ ls -lart
total 19684
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Aug 10 04:25 orapwPROD1
drwxr-xr-x 6 oracle oinstall 4096 Dec 15 05:01 .
-rw-r----- 1 oracle oinstall 4608 Dec 15 05:01 spfilePROD1.ora
[oracle@edsir4p1-PROD1 dbs]$ mv spfilePROD1.ora spfilePROD1.ora.bak
[oracle@edsir4p1-PROD1 dbs]$ ls -lart
total 19684
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
drwxr-xr-x 75 oracle oinstall 4096 Jul 10 13:03 ..
-rw-r----- 1 oracle oinstall 1536 Aug 10 04:25 orapwPROD1
-rw-r----- 1 oracle oinstall 4608 Dec 15 05:01 spfilePROD1.ora.bak
drwxr-xr-x 6 oracle oinstall 4096 Dec 15 05:23 .
删除参数文件之后并不会有报错，该参数文件只有在数据库启动的时候才会读取，删除参数文件对正常运行状态下的数据库不影响。
SYS@PROD1> alter system switch logfile;
System altered.
SYS@PROD1> alter system switch logfile;
System altered.
Thread 1 advanced to log sequence 18 (LGWR switch)
Current log# 3 seq# 18 mem# 0: /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_3_gzcf6jm9_.log
Current log# 3 seq# 18 mem# 1: /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log
Sun Dec 15 05:24:20 2019
Archived Log entry 110 added for thread 1 sequence 17 ID 0x8604367b dest 1:
Thread 1 advanced to log sequence 19 (LGWR switch)
Current log# 1 seq# 19 mem# 0: /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcf6h1o_.log
Current log# 1 seq# 19 mem# 1: /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcf6hdr_.log
Sun Dec 15 05:24:24 2019
Archived Log entry 111 added for thread 1 sequence 18 ID 0x8604367b dest 1:
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 05:31:57 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
--设置DBID
RMAN> set DBID = 387098034
executing command: SET DBID
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 79691880 bytes
Database Buffers 71303168 bytes
Redo Buffers 6688768 bytes
RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp';
Starting restore at 15-DEC-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-DEC-19
[oracle@edsir4p1-PROD1 dbs]$ ls -lart
total 19684
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
drwxr-xr-x 75 oracle oinstall 4096 Jul 10 13:03 ..
-rw-r----- 1 oracle oinstall 1536 Aug 10 04:25 orapwPROD1
-rw-r----- 1 oracle oinstall 4608 Dec 15 05:01 spfilePROD1.ora.bak
-rw-r----- 1 oracle oinstall 4608 Dec 15 05:32 spfilePROD1.ora
drwxr-xr-x 6 oracle oinstall 4096 Dec 15 05:23 .
--重启数据库
SYS@PROD1> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@PROD1>startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
Database opened.
SYS@PROD1>②使用默认spfile
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 05:39:04 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
--生成一个默认spfile
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 79691880 bytes
Database Buffers 71303168 bytes
Redo Buffers 6688768 bytes
RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp';
Starting restore at 15-DEC-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-DEC-19
RMAN> shutdown immediate
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
RMAN>2、从启动信息中获取spfile
在启动的时候在alert中会有启动信息，在这个信息中会有参数文件的一些信息，但是在长久稳定运行的数据库系统中这个信息是比较难找的。
cat /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/cat alert_PROD1.log
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD1.ora
System parameters with non-default values:
processes = 135
sessions = 300
sga_target = 608M
control_files = "/u01/app/oracle/oradata/PROD1/controlfile/control01.ctl"
control_files = "/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl"
control_files = "/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/home/oracle/arch1"
db_create_file_dest = "/u01/app/oracle/oradata"
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_management = "AUTO"
undo_tablespace = "UNDOTBS1"
undo_retention = 5400
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "US.ORACLE.COM"
dispatchers = "(PROTOCOL=TCP) (dispatcher=3)"
shared_servers = 10
max_shared_servers = 30
max_dispatchers = 10
shared_server_sessions = 200
local_listener = "PROD1"
local_listener = "PROD_S"
utl_file_dir = "/oracle/home/"
utl_file_dir = "/oracle/home/temp"
utl_file_dir = "/oracle/home/scripts"
job_queue_processes = 1000
cursor_sharing = "EXACT"
audit_file_dest = "/u01/app/oracle/admin/PROD1/adump"
audit_trail = "DB"
db_name = "PROD1"
open_cursors = 300
pga_aggregate_target = 202M
diagnostic_dest = "/u01/app/oracle"
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 2.0
Sun Dec 15 05:41:12 20193、从$ORACLE_HOME/dbs/init.ora中获取参数文件
若参数文件没有备份，且数据库长时间未启动还可从int.ora中获取参数文件
[oracle@edsir4p1-PROD1 dbs]$ cat init.ora
#
# $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $
#
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
# init.ora
# FUNCTION
# NOTES
# MODIFIED
# ysarig 05/14/09 - Updating compatible to 11.2
# ysarig 08/13/07 - Fixing the sample for 11g
# atsukerm 08/06/98 - fix for 8.1.
# hpiao 06/05/97 - fix for 803
# glavash 05/12/97 - add oracle_trace_enable comment
# hpiao 04/22/97 - remove ifile=, events=, etc.
# alingelb 09/19/94 - remove vms-specific stuff
# dpawson 07/07/93 - add more comments regarded archive start
# maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
# jloaiza 03/07/92 - change ALPHA to BETA
# danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
# ghallmar 02/03/92 - db_directory -> db_domain
# maporter 01/12/92 - merge changes from branch 1.8.308.1
# maporter 12/21/91 - bug 76493: Add control_files parameter
# wbridge 12/03/91 - use of %c in archive format is discouraged
# ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
# thayes 11/27/91 - Change default for cache_clone
# jloaiza 08/13/91 - merge changes from branch 1.7.100.1
# jloaiza 07/31/91 - add debug stuff
# rlim 04/29/91 - removal of char_is_varchar2
# Bridge 03/12/91 - log_allocation no longer exists
# Wijaya 02/05/91 - remove obsolete parameters
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'4、手动编辑spfile
如果整个dbs目录都损坏了，那么也还可以通过手动编辑参数文件来启动，只需编辑基础参数，然后在后期优化参数
六、恢复controlfile
1、冷备恢复
在实际生产环境中控制文件至少有2个，有些环境还存在控制文件多路复用，若一个控制文件损坏可将其他控制文件复制过来即可。
2、从备份中恢复控制文件
控制文件中记录了数据文件头信息，备份信息等诸多信息，在重建控制文件之后需重新进行全量备份
[oracle@edsir4p1-PROD1 controlfile]$ rm -rf control0*
[oracle@edsir4p1-PROD1 controlfile]$ rm /u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@PROD1>
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 07:40:25 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_28ujf5nl_1_1.ctl';
Starting restore at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 15-DEC-19
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1--查看此时数据文件头SCN和控制文件SCN，由于控制文件较老，在recover database的时候是根据数据文件来将现在的数据文件头信息写入控制文件中，不过此时控制文件已经不包含备份集时间点到宕机时间点的备份信息，在恢复控制文件之后需要重新全备。
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1365532
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1365534
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1365539
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1365534
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1365539
6 /u01/app/oracle/oradata/PROD1/test.dbf 1365536
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1365536
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1365534
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1365536
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1365539
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1411210
2 1411210
3 1411210
4 1411210
5 1411210
6 1411210
7 1411210
8 1411210
9 1411210
10 1411210
10 rows selected.
RMAN> recover database;
Starting recover at 15-DEC-19
Starting implicit crosscheck backup at 15-DEC-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 15-DEC-19
Starting implicit crosscheck copy at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 15-DEC-19
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985546_gz9fh3lc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985321_gz9cm9rk_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982337_gz98p1j0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026989209_gz9hdsdh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982024_gz98d8sr_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026984092_gz9bpmmc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026987847_gz9g28b0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054861_gzchl3h1_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027047781_gzc8m5og_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054729_gzchd9n2_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_2_gzcf6j5o_.log
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcf6hdr_.log
archived log file name=/home/oracle/arch1/1_15_1027052494.dbf thread=1 sequence=15
archived log file name=/home/oracle/arch1/1_16_1027052494.dbf thread=1 sequence=16
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_2_gzcf6j5o_.log thread=1 sequence=17
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log thread=1 sequence=18
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcf6hdr_.log thread=1 sequence=19
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-DEC-19
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1430609 1430609
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1430609 1430609
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1430609 1430609
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1430609 1430609
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1430609 1430609
6 /u01/app/oracle/oradata/PROD1/test.dbf 1430609 1430609
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1430609 1430609
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1430609 1430609
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1430609 1430609
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1430609 1430609
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1430609
2 1430609
3 1430609
4 1430609
5 1430609
6 1430609
7 1430609
8 1430609
9 1430609
10 1430609
10 rows selected.
--由于控制文件重建了，所以启动需要resetlogs，详细可参考：https://blog.csdn.net/renyanjie123/article/details/82935937
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/15/2019 07:42:23
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
SYS@PROD1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch1
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 13、基于数据文件生成控制文件
当控制文件损坏，而备份中有无有效的控制文件备份，还可通过手工创建控制文件起库，但是前提是数据文件必须完整，若数据文件损坏则不能通过手工创建控制文件来恢复。
CREATE CONTROLFILE
SET DATABASE prod1
LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
'/u01/oracle/prod/redo01_02.log'),
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
'/u01/oracle/prod/redo02_02.log'),
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
'/u01/oracle/prod/redo03_02.log')
RESETLOGS
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXLOGHISTORY 400
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;详见可参照：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-control-files.html#GUID-E4677C95-CDBE-4FB5-9A9E-A227B3547FAE
七、恢复数据文件
1、冷备恢复
冷备恢复数据文件是将某个表空间数据文件或者多个表空间恢复到备份时间点，这种情况下是没有进行热备份的环境，会丢失部分数据。
2、rman恢复
[oracle@edsir4p1-PROD1 PROD1]$ rm -rf tbs01.dbf
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 243270632 bytes
Database Buffers 385875968 bytes
Redo Buffers 5615616 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD1/tbs01.dbf'
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 08:16:49 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2102759015, not open)
RMAN> restore datafile 10;
Starting restore at 15-DEC-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
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 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_2eujfiht_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_2eujfiht_1_1.bak tag=TAG20191215T081348
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-DEC-19
--查看10号文件的数据文件头SCN比较小，需要recover
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1433545
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1433545
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1433545
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1433545
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1433545
6 /u01/app/oracle/oradata/PROD1/test.dbf 1433545
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1433545
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1433545
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1433545
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1433545
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1433545
2 1433545
3 1433545
4 1433545
5 1433545
6 1433545
7 1433545
8 1433545
9 1433545
10 1433375
10 rows selected.
RMAN> recover datafile 10;
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/1_1_1027064551.dbf
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/1_2_1027064551.dbf
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/1_3_1027064551.dbf
archived log for thread 1 with sequence 4 is already on disk as file /home/oracle/arch1/1_4_1027064551.dbf
archived log for thread 1 with sequence 5 is already on disk as file /home/oracle/arch1/1_5_1027064551.dbf
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/1_6_1027064551.dbf
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/1_7_1027064551.dbf
archived log for thread 1 with sequence 8 is already on disk as file /home/oracle/arch1/1_8_1027064551.dbf
archived log for thread 1 with sequence 9 is already on disk as file /home/oracle/arch1/1_9_1027064551.dbf
archived log for thread 1 with sequence 10 is already on disk as file /home/oracle/arch1/1_10_1027064551.dbf
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/arch1/1_11_1027064551.dbf
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/arch1/1_12_1027064551.dbf
archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch1/1_13_1027064551.dbf
archived log for thread 1 with sequence 14 is already on disk as file /home/oracle/arch1/1_14_1027064551.dbf
archived log for thread 1 with sequence 15 is already on disk as file /home/oracle/arch1/1_15_1027064551.dbf
archived log for thread 1 with sequence 16 is already on disk as file /home/oracle/arch1/1_16_1027064551.dbf
archived log for thread 1 with sequence 17 is already on disk as file /home/oracle/arch1/1_17_1027064551.dbf
archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/arch1/1_18_1027064551.dbf
archived log for thread 1 with sequence 19 is already on disk as file /home/oracle/arch1/1_19_1027064551.dbf
archived log file name=/home/oracle/arch1/1_1_1027064551.dbf thread=1 sequence=1
archived log file name=/home/oracle/arch1/1_2_1027064551.dbf thread=1 sequence=2
archived log file name=/home/oracle/arch1/1_3_1027064551.dbf thread=1 sequence=3
archived log file name=/home/oracle/arch1/1_4_1027064551.dbf thread=1 sequence=4
archived log file name=/home/oracle/arch1/1_5_1027064551.dbf thread=1 sequence=5
archived log file name=/home/oracle/arch1/1_6_1027064551.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch1/1_7_1027064551.dbf thread=1 sequence=7
archived log file name=/home/oracle/arch1/1_8_1027064551.dbf thread=1 sequence=8
archived log file name=/home/oracle/arch1/1_9_1027064551.dbf thread=1 sequence=9
archived log file name=/home/oracle/arch1/1_10_1027064551.dbf thread=1 sequence=10
archived log file name=/home/oracle/arch1/1_11_1027064551.dbf thread=1 sequence=11
archived log file name=/home/oracle/arch1/1_12_1027064551.dbf thread=1 sequence=12
archived log file name=/home/oracle/arch1/1_13_1027064551.dbf thread=1 sequence=13
archived log file name=/home/oracle/arch1/1_14_1027064551.dbf thread=1 sequence=14
archived log file name=/home/oracle/arch1/1_15_1027064551.dbf thread=1 sequence=15
archived log file name=/home/oracle/arch1/1_16_1027064551.dbf thread=1 sequence=16
archived log file name=/home/oracle/arch1/1_17_1027064551.dbf thread=1 sequence=17
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-DEC-19
--recover之后发现10号数据文件头SCN和控制文件中的scn已经一致，由于其他数据文件是read only状态，还没有应用日志。
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
select file#,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1433545
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1433545
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1433545
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1433545
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1433545
6 /u01/app/oracle/oradata/PROD1/test.dbf 1433545
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1433545
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1433545
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1433545
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1453587 1453587
10 rows selected.
SYS@PROD1>
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1433545
2 1433545
3 1433545
4 1433545
5 1433545
6 1433545
7 1433545
8 1433545
9 1433545
10 1453587
10 rows selected.
RMAN> alter database open;
database opened
RMAN>
--发现数据文件头SCN和控制文件SCN已经完全一致了
SYS@PROD1> set linesize 200;
SYS@PROD1> col name for a80;
SYS@PROD1> select file#,name,checkpoint_change#,last_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
1 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf 1453590
2 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf 1453590
3 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf 1453590
4 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf 1453590
5 /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf 1453590
6 /u01/app/oracle/oradata/PROD1/test.dbf 1453590
7 /u01/app/oracle/oradata/PROD1/indx.dbf 1453590
8 /u01/app/oracle/oradata/PROD1/tools.dbf 1453590
9 /u01/app/oracle/oradata/PROD1/oltp.dbf 1453590
10 /u01/app/oracle/oradata/PROD1/tbs01.dbf 1453590
10 rows selected.
SYS@PROD1> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1453590
2 1453590
3 1453590
4 1453590
5 1453590
6 1453590
7 1453590
8 1453590
9 1453590
10 1453590
10 rows selected.
SYS@PROD1>3、归档日志反写恢复数据文件
SYS@PROD1> create tablespace tbs2 datafile '/u01/app/oracle/oradata/PROD1/tbs02.dbf'size 10m;
Tablespace created.
SYS@PROD1> create table test1 (id int,name varchar2(20));
Table created.
SYS@PROD1> insert into test1 values(3,'a');
1 row created.
SYS@PROD1> commit;
Commit complete.
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 19 52428800 512 2 YES INACTIVE 1433541 15-DEC-19 1433545 15-DEC-19
2 1 20 52428800 512 2 YES INACTIVE 1433545 15-DEC-19 1453589 15-DEC-19
3 1 21 52428800 512 2 NO CURRENT 1453589 15-DEC-19 2.8147E+14
SYS@PROD1> alter system switch logfile;
System altered.
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 22 52428800 512 2 NO CURRENT 1454529 15-DEC-19 2.8147E+14
2 1 20 52428800 512 2 YES INACTIVE 1433545 15-DEC-19 1453589 15-DEC-19
3 1 21 52428800 512 2 YES ACTIVE 1453589 15-DEC-19 1454529 15-DEC-19
SYS@PROD1> insert into test1 values(3,'b');
1 row created.
SYS@PROD1> shutdown abort
ORACLE instance shut down.
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 239076328 bytes
Database Buffers 390070272 bytes
Redo Buffers 5615616 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf'
SYS@PROD1>
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 15 08:33:44 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_2lujfiiq_1_1.ctl';
Starting restore at 15-DEC-19
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 15-DEC-19
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 15-DEC-19
Starting implicit crosscheck backup at 15-DEC-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
Crosschecked 35 objects
Finished implicit crosscheck backup at 15-DEC-19
Starting implicit crosscheck copy at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 15-DEC-19
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985546_gz9fh3lc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985321_gz9cm9rk_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982337_gz98p1j0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026989209_gz9hdsdh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982024_gz98d8sr_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026984092_gz9bpmmc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026987847_gz9g28b0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054861_gzchl3h1_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027066460_gzcttwrd_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027047781_gzc8m5og_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054729_gzchd9n2_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_gzcd23sz_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcd268x_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_2eujfiht_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcd23sr_.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_2bujfiht_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcd242h_.dbf
channel ORA_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_gzcd23v7_.dbf
channel ORA_DISK_3: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_2cujfiht_1_1.bak
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_4: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_4: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_4: reading from backup piece /home/oracle/backup/full_PROD1_2dujfiht_1_1.bak
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_2bujfiht_1_1.bak tag=TAG20191215T081348
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_4: piece handle=/home/oracle/backup/full_PROD1_2dujfiht_1_1.bak tag=TAG20191215T081348
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:18
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_2eujfiht_1_1.bak tag=TAG20191215T081348
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_2cujfiht_1_1.bak tag=TAG20191215T081348
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:28
Finished restore at 15-DEC-19
RMAN> recover database;
Starting recover at 15-DEC-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/1_1_1027064551.dbf
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/1_2_1027064551.dbf
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcf6hdr_.log
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_2_gzcf6j5o_.log
archived log file name=/home/oracle/arch1/1_1_1027064551.dbf thread=1 sequence=1
archived log file name=/home/oracle/arch1/1_2_1027064551.dbf thread=1 sequence=2
archived log file name=/home/oracle/arch1/1_3_1027064551.dbf thread=1 sequence=3
archived log file name=/home/oracle/arch1/1_4_1027064551.dbf thread=1 sequence=4
archived log file name=/home/oracle/arch1/1_5_1027064551.dbf thread=1 sequence=5
archived log file name=/home/oracle/arch1/1_6_1027064551.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch1/1_7_1027064551.dbf thread=1 sequence=7
archived log file name=/home/oracle/arch1/1_8_1027064551.dbf thread=1 sequence=8
archived log file name=/home/oracle/arch1/1_9_1027064551.dbf thread=1 sequence=9
archived log file name=/home/oracle/arch1/1_10_1027064551.dbf thread=1 sequence=10
archived log file name=/home/oracle/arch1/1_11_1027064551.dbf thread=1 sequence=11
archived log file name=/home/oracle/arch1/1_12_1027064551.dbf thread=1 sequence=12
archived log file name=/home/oracle/arch1/1_13_1027064551.dbf thread=1 sequence=13
archived log file name=/home/oracle/arch1/1_14_1027064551.dbf thread=1 sequence=14
archived log file name=/home/oracle/arch1/1_15_1027064551.dbf thread=1 sequence=15
archived log file name=/home/oracle/arch1/1_16_1027064551.dbf thread=1 sequence=16
archived log file name=/home/oracle/arch1/1_17_1027064551.dbf thread=1 sequence=17
archived log file name=/home/oracle/arch1/1_18_1027064551.dbf thread=1 sequence=18
archived log file name=/home/oracle/arch1/1_19_1027064551.dbf thread=1 sequence=19
archived log file name=/home/oracle/arch1/1_20_1027064551.dbf thread=1 sequence=20
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log thread=1 sequence=21
creating datafile file number=11 name=/u01/app/oracle/oradata/PROD1/tbs02.dbf
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcf6jxh_.log thread=1 sequence=21
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcf6hdr_.log thread=1 sequence=22
archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_2_gzcf6j5o_.log thread=1 sequence=23
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-DEC-19
RMAN> alter database open resetlogs;
database opened
RMAN>八、恢复redo文件
1、恢复inactive redo
非在线redo损坏可将redo删掉后重建redo
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3156877312 bytes
Fixed Size 2217424 bytes
Variable Size 989858352 bytes
Database Buffers 2147483648 bytes
Redo Buffers 17317888 bytes
Database mounted.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
如果该联机日志还未完成归档，需使用如下命令删除
alter database clear unarchived logfile group 3;
2、恢复online redo
如果当前redo损坏就意味着一定会丢数据。
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 2 NO CURRENT 1475864 15-DEC-19 2.8147E+14
2 1 8 52428800 512 2 YES INACTIVE 1475857 15-DEC-19 1475860 15-DEC-19
3 1 9 52428800 512 2 YES INACTIVE 1475860 15-DEC-19 1475864 15-DEC-19
SYS@PROD1> insert into test1 values(4,'b');
1 row created.
SYS@PROD1> commit;
Commit complete.
SYS@PROD1> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 2 NO CURRENT 1475864 15-DEC-19 2.8147E+14
2 1 8 52428800 512 2 YES INACTIVE 1475857 15-DEC-19 1475860 15-DEC-19
3 1 9 52428800 512 2 YES INACTIVE 1475860 15-DEC-19 1475864 15-DEC-19
SYS@PROD1> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_3_gzcw6t13_.log NO
3 ONLINE /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_3_gzcw6t81_.log YES
2 ONLINE /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_2_gzcw6sk4_.log NO
2 ONLINE /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_2_gzcw6sr5_.log YES
1 ONLINE /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcw6s19_.log NO
1 ONLINE /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcw6s8v_.log YES
6 rows selected.
SYS@PROD1> alter system switch logfile;
System altered.
SYS@PROD1> shutdown immediate;
[oracle@edsir4p1-PROD1 PROD1]$ mv /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcw6s19_.log /u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcw6s19_.log.bak
[oracle@edsir4p1-PROD1 PROD1]$ mv /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcw6s8v_.log /u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcw6s8v_.log.bak
[oracle@edsir4p1-PROD1 PROD1]$
SYS@PROD1> startup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 239076328 bytes
Database Buffers 390070272 bytes
Redo Buffers 5615616 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 999
Session ID: 1 Serial number: 5
SYS@PROD1>
Sun Dec 15 08:53:17 2019
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_arc1_31402.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcw6s8v_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcw6s19_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_arc1_31402.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/flash_recovery_area/PROD1/onlinelog/o1_mf_1_gzcw6s8v_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_1_gzcw6s19_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SYS@PROD1> startup mount;
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 239076328 bytes
Database Buffers 390070272 bytes
Redo Buffers 5615616 bytes
Database mounted.
SYS@PROD1>
SYS@PROD1> recover database using backup controlfile until cancel;
ORA-00279: change 1475864 generated at 12/15/2019 08:51:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_10_1027067864.dbf
ORA-00280: change 1475864 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SYS@PROD1> alter database open resetlogs;
Database altered.九、隐含参数强拉数据库
在实际生产环境中不可避免会有一些极端情况，存储掉电，存储损坏，服务器宕机都会引起备份失效，归档丢失，redo损坏等不能进行恢复数据库的情况。那么就需要使用隐含参数来将数据库跳过一致性检查强拉数据库，此方式强拉数据库之后数据库会随时宕机，源库数据应及时导出。
create pfile='/home/oracle/pfile.ora' from spfile
vi /home/oracle/pfile.ora -在参数文件中加入
*._allow_resetlogs_corruption=TRUE
*._corrupted_rollback_segments=(_syssmu1$,_syssmu2$,_syssmu3$,_syssmu4$,_syssmu5$,_syssmu6$,_syssmu7$,_syssmu8$,_syssmu9$,_syssmu10$,_syssmu11$,_syssmu12$,_syssmu13$,_syssmu14$,_syssmu15$,_syssmu16$,_syssmu17$,_syssmu18$,_syssmu19$,_syssmu20$,_syssmu21$,_syssmu22$,_syssmu23$,_syssmu24$,_syssmu25$,_syssmu26$,_syssmu27$,_syssmu28$,_syssmu29$,_syssmu30$,_syssmu31$,_syssmu32$,_syssmu33$)
startup pfile='/oracle/pfile.ora' mount;
alter database open resetlogs;十、恢复数据块
1、物理坏块
物理坏块是由于硬件问题引发的磁盘损坏造成的，这也是比较常见的坏块现象，在alert中有明显报错，且该方式可以检查。
可使用rman修复
RMAN>backup validate datafile 4;
RMAN> run {blockrecover datafile 4 block 133;}
2、逻辑坏块
逻辑坏块是由于数据库逻辑层面的问题造成的坏块，在alert日志没有明显ora报错，可通过v$database_block_corruption查看;
同样依然可使用rman进行修复blockrecover datafile 5 block 2969045，也可直接通过DBMS_REPAIR.SKIP_CORRUPT_BLOCKS跳过坏块;
详见可参考：https://blog.csdn.net/renyanjie123/article/details/103551042
十一、Oracle不完全恢复(Incomplete recover)
Oracle不完全恢复是Oraclerman恢复的另一个比较灵活的恢复方式，他可以让DBA在有效备份的前提下，将数据库恢复到任意时间点，还可用于对于缺失某些归档文件或者online redo、某个过去时间点数据被破坏、误删除表空间的恢复环境中。
1、基于时间点
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 03:11:04 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_3iul3nb5_1_1.ctl';
Starting restore at 04-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 04-JAN-20
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
RMAN> run{
2> set until time "to_date('2020/01/04 02:54:50','YYYY/MM/DD HH24:MI:SS')";
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 04-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=161 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=10 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=160 device type=DISK
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 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_38ul3na8_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_gzcw3c5f_.dbf
channel ORA_DISK_2: restoring datafile 00011 to /u01/app/oracle/oradata/PROD1/tbs02.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_36ul3na7_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_gzcw3c6b_.dbf
channel ORA_DISK_3: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_h100prcj_.dbf
channel ORA_DISK_3: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_37ul3na7_1_1.bak
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_h100pr37_.dbf
channel ORA_DISK_4: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_gzcw3c61_.dbf
channel ORA_DISK_4: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_4: reading from backup piece /home/oracle/backup/full_PROD1_39ul3na8_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_38ul3na8_1_1.bak tag=TAG20200104T025358
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_36ul3na7_1_1.bak tag=TAG20200104T025358
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:26
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_37ul3na7_1_1.bak tag=TAG20200104T025358
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:26
channel ORA_DISK_4: piece handle=/home/oracle/backup/full_PROD1_39ul3na8_1_1.bak tag=TAG20200104T025358
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:26
Finished restore at 04-JAN-20
Starting recover at 04-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/1_2_1027069479.dbf
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/1_3_1027069479.dbf
archived log file name=/home/oracle/arch1/1_2_1027069479.dbf thread=1 sequence=2
archived log file name=/home/oracle/arch1/1_3_1027069479.dbf thread=1 sequence=3
archived log file name=/home/oracle/arch1/1_4_1027069479.dbf thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-JAN-20
RMAN> alter database open resetlogs;
database opened
RMAN>
[oracle@edsir4p1- arch1]$ ls -lart
total 33352
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:54 1_3_1027069479.dbf
-rw-r----- 1 oracle oinstall 34050048 Jan 4 02:54 1_2_1027069479.dbf
-rw-r----- 1 oracle oinstall 6656 Jan 4 02:55 1_4_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_5_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_8_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_7_1027069479.dbf
-rw-r----- 1 oracle oinstall 1536 Jan 4 02:55 1_6_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_9_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_10_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_11_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_12_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_14_1027069479.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 4 02:55 1_13_1027069479.dbf2、基于scn
SYS@PROD1> select current_scn from v$database;
CURRENT_SCN
-----------
1499926
SYS@PROD1> shutdown immediate;
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 03:31:37 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_4aul3pae_1_1.ctl';
Starting restore at 04-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 04-JAN-20
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database until SCN 1499880;
Starting restore at 04-JAN-20
Starting implicit crosscheck backup at 04-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
Crosschecked 34 objects
Crosschecked 50 objects
Finished implicit crosscheck backup at 04-JAN-20
Starting implicit crosscheck copy at 04-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 04-JAN-20
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985546_gz9fh3lc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985321_gz9cm9rk_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982337_gz98p1j0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026989209_gz9hdsdh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982024_gz98d8sr_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026984092_gz9bpmmc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026987847_gz9g28b0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054861_gzchl3h1_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027066460_gzcttwrd_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027047781_gzc8m5og_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054729_gzchd9n2_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2020_01_04/o1_mf_s_1028775272_h0zzm83v_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2020_01_04/o1_mf_s_1028777296_h101ljry_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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/PROD1/datafile/o1_mf_system_h100sw75_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/PROD1/tbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_3vul3p9i_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_41ul3p9i_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_h100sw8m_.dbf
channel ORA_DISK_3: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_h100swd1_.dbf
channel ORA_DISK_3: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_42ul3p9j_1_1.bak
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_h100sw7x_.dbf
channel ORA_DISK_4: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_h100sw94_.dbf
channel ORA_DISK_4: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_4: reading from backup piece /home/oracle/backup/full_PROD1_40ul3p9i_1_1.bak
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_41ul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_3vul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_42ul3p9j_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:27
channel ORA_DISK_4: piece handle=/home/oracle/backup/full_PROD1_40ul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:37
Finished restore at 04-JAN-20
RMAN> recover database until SCN 1499880;
Starting recover at 04-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/1_6_1028776938.dbf
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/1_7_1028776938.dbf
archived log file name=/home/oracle/arch1/1_6_1028776938.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch1/1_7_1028776938.dbf thread=1 sequence=7
archived log file name=/home/oracle/arch1/1_8_1028776938.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-JAN-20
RMAN> alter database open resetlogs;
database opened
RMAN>3、基于sequence
[oracle@edsir4p1-PROD1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 04:06:23 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_4aul3pae_1_1.ctl';
Starting restore at 04-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 04-JAN-20
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database until sequence 16 thread 1;
Starting restore at 04-JAN-20
Starting implicit crosscheck backup at 04-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=11 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=12 device type=DISK
Crosschecked 34 objects
Crosschecked 50 objects
Finished implicit crosscheck backup at 04-JAN-20
Starting implicit crosscheck copy at 04-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished implicit crosscheck copy at 04-JAN-20
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985546_gz9fh3lc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026985321_gz9cm9rk_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982337_gz98p1j0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026989209_gz9hdsdh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026982024_gz98d8sr_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026984092_gz9bpmmc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_14/o1_mf_s_1026987847_gz9g28b0_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054861_gzchl3h1_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027066460_gzcttwrd_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027053303_gzcfzqjg_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027047781_gzc8m5og_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2019_12_15/o1_mf_s_1027054729_gzchd9n2_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2020_01_04/o1_mf_s_1028777834_h1023bbr_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2020_01_04/o1_mf_s_1028775272_h0zzm83v_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PROD1/autobackup/2020_01_04/o1_mf_s_1028777296_h101ljry_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
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/PROD1/datafile/o1_mf_system_h100sw75_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/PROD1/tbs02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_PROD1_3vul3p9i_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/indx.dbf
channel ORA_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/PROD1/oltp.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full_PROD1_41ul3p9i_1_1.bak
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_undotbs1_h100sw8m_.dbf
channel ORA_DISK_3: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_example_h100swd1_.dbf
channel ORA_DISK_3: restoring datafile 00010 to /u01/app/oracle/oradata/PROD1/tbs01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full_PROD1_42ul3p9j_1_1.bak
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_sysaux_h100sw7x_.dbf
channel ORA_DISK_4: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/datafile/o1_mf_users_h100sw94_.dbf
channel ORA_DISK_4: restoring datafile 00008 to /u01/app/oracle/oradata/PROD1/tools.dbf
channel ORA_DISK_4: reading from backup piece /home/oracle/backup/full_PROD1_40ul3p9i_1_1.bak
channel ORA_DISK_2: piece handle=/home/oracle/backup/full_PROD1_41ul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_PROD1_3vul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_DISK_3: piece handle=/home/oracle/backup/full_PROD1_42ul3p9j_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:27
channel ORA_DISK_4: piece handle=/home/oracle/backup/full_PROD1_40ul3p9i_1_1.bak tag=TAG20200104T032745
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:27
Finished restore at 04-JAN-20
RMAN>recover database until sequence 16 thread 1;
Starting recover at 04-JAN-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
starting media recovery
archived log for thread 1 with sequence 6 is already on disk as file /home/oracle/arch1/1_6_1028776938.dbf
archived log for thread 1 with sequence 7 is already on disk as file /home/oracle/arch1/1_7_1028776938.dbf
archived log file name=/home/oracle/arch1/1_6_1028776938.dbf thread=1 sequence=6
archived log file name=/home/oracle/arch1/1_7_1028776938.dbf thread=1 sequence=7
archived log file name=/home/oracle/arch1/1_8_1028776938.dbf thread=1 sequence=8
archived log file name=/home/oracle/arch1/1_9_1028776938.dbf thread=1 sequence=9
archived log file name=/home/oracle/arch1/1_10_1028776938.dbf thread=1 sequence=10
archived log file name=/home/oracle/arch1/1_11_1028776938.dbf thread=1 sequence=11
archived log file name=/home/oracle/arch1/1_12_1028776938.dbf thread=1 sequence=12
archived log file name=/home/oracle/arch1/1_13_1028776938.dbf thread=1 sequence=13
archived log file name=/home/oracle/arch1/1_14_1028776938.dbf thread=1 sequence=14
archived log file name=/home/oracle/arch1/1_15_1028776938.dbf thread=1 sequence=15
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-JAN-20
RMAN> alter database open resetlogs;
database opened
RMAN>4、基于控制文件
--删除控制文件
[oracle@edsir4p1- controlfile]$ rm control0*.ctl
[oracle@edsir4p1- controlfile]$ rm /u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
SYS@PROD1> shutdown abort
sORACLE instance shut down.
SYS@PROD1> tartup
ORACLE instance started.
Total System Global Area 636100608 bytes
Fixed Size 1338392 bytes
Variable Size 260047848 bytes
Database Buffers 369098752 bytes
Redo Buffers 5615616 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@edsir4p1-PROD1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jan 6 14:08:06 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (not mounted)
RMAN> restore controlfile from '/home/oracle/backup/full_PROD1_6uula70u_1_1.ctl';
Starting restore at 06-JAN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
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/PROD1/controlfile/control01.ctl
output file name=/u01/app/oracle/oradata/PROD1/controlfile/control02.ctl
output file name=/u01/app/oracle/flash_recovery_area/PROD1/controlfile/control03.ctl
Finished restore at 06-JAN-20
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
SYS@PROD1> recover database using backup controlfile;
ORA-00279: change 1524061 generated at 01/06/2020 13:58:51 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_7_1028987512.dbf
ORA-00280: change 1524061 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1524106 generated at 01/06/2020 13:59:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_8_1028987512.dbf
ORA-00280: change 1524106 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/arch1/1_7_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524109 generated at 01/06/2020 13:59:38 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_9_1028987512.dbf
ORA-00280: change 1524109 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/arch1/1_8_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524114 generated at 01/06/2020 13:59:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_10_1028987512.dbf
ORA-00280: change 1524114 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/arch1/1_9_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524123 generated at 01/06/2020 13:59:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_11_1028987512.dbf
ORA-00280: change 1524123 for thread 1 is in sequence #11
ORA-00278: log file '/home/oracle/arch1/1_10_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524126 generated at 01/06/2020 13:59:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_12_1028987512.dbf
ORA-00280: change 1524126 for thread 1 is in sequence #12
ORA-00278: log file '/home/oracle/arch1/1_11_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524131 generated at 01/06/2020 13:59:58 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_13_1028987512.dbf
ORA-00280: change 1524131 for thread 1 is in sequence #13
ORA-00278: log file '/home/oracle/arch1/1_12_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524645 generated at 01/06/2020 14:06:41 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_14_1028987512.dbf
ORA-00280: change 1524645 for thread 1 is in sequence #14
ORA-00278: log file '/home/oracle/arch1/1_13_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524648 generated at 01/06/2020 14:06:42 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_15_1028987512.dbf
ORA-00280: change 1524648 for thread 1 is in sequence #15
ORA-00278: log file '/home/oracle/arch1/1_14_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524653 generated at 01/06/2020 14:06:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_16_1028987512.dbf
ORA-00280: change 1524653 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/arch1/1_15_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524656 generated at 01/06/2020 14:06:46 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_17_1028987512.dbf
ORA-00280: change 1524656 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/arch1/1_16_1028987512.dbf' no longer needed for this recovery
ORA-00279: change 1524660 generated at 01/06/2020 14:06:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_18_1028987512.dbf
ORA-00280: change 1524660 for thread 1 is in sequence #18
ORA-00278: log file '/home/oracle/arch1/1_17_1028987512.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/arch1/1_18_1028987512.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SYS@PROD1>host ls -lart
total 1272
drwxrwxrwx 27 oracle oinstall 4096 Jan 6 13:45 ..
-rw-r----- 1 oracle oinstall 258048 Jan 6 13:55 1_1_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 13:55 1_2_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 13:56 1_4_1028987512.dbf
-rw-r----- 1 oracle oinstall 8704 Jan 6 13:56 1_3_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 13:58 1_6_1028987512.dbf
-rw-r----- 1 oracle oinstall 16384 Jan 6 13:58 1_5_1028987512.dbf
-rw-r----- 1 oracle oinstall 8192 Jan 6 13:59 1_7_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 13:59 1_8_1028987512.dbf
-rw-r----- 1 oracle oinstall 1536 Jan 6 13:59 1_9_1028987512.dbf
-rw-r----- 1 oracle oinstall 1536 Jan 6 13:59 1_10_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 13:59 1_11_1028987512.dbf
-rw-r----- 1 oracle oinstall 1536 Jan 6 13:59 1_12_1028987512.dbf
-rw-r----- 1 oracle oinstall 619520 Jan 6 14:06 1_13_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 14:06 1_14_1028987512.dbf
-rw-r----- 1 oracle oinstall 3072 Jan 6 14:06 1_15_1028987512.dbf
-rw-r----- 1 oracle oinstall 312320 Jan 6 14:11 1_18_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 14:11 1_17_1028987512.dbf
-rw-r----- 1 oracle oinstall 1024 Jan 6 14:11 1_16_1028987512.dbf
drwxr-xr-x 2 oracle oinstall 4096 Jan 6 14:11 .
SYS@PROD1> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD1/datafile/o1_mf_system_h16cz62l_.dbf'
SYS@PROD1> recover database using backup controlfile;
ORA-00279: change 1524660 generated at 01/06/2020 14:06:48 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch1/1_18_1028987512.dbf
ORA-00280: change 1524660 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD1/onlinelog/o1_mf_3_gzcw6t13_.log
Log applied.
Media recovery complete.
SYS@PROD1> alter database open resetlogs;
Database altered.
SYS@PROD1>
十二、闪回恢复数据库
闪回数据库是Oracle10g以后引入的一个还原数据库的一个新特性，该特性在11g中得到完善，但实际生产中此种方式并不常用， 在闪回之后Oracle数据库或者某个表空间或者表会回到指定的时间点或者scn，同样会丢失这期间的所有数据，在生产中这种不可逆的方式是不被允许的。虽然Oracle推荐使用闪回区并给定大于数据库数据量大小的空间但实际上闪回区一般情况下是关闭的或者给定的特别小，闪回技术使用最多的也仅限于闪回查询（受限于undo retention的时间）。
开启闪回区：
alter system set db_recovery_file_dest='+fra' scope=both sid='*';
1、闪回数据库
还原至scn：
flashback database to scn 991824;
创建普通还原点：
CREATE RESTORE POINT test_POINT;
FLASHBACK DATABASE TO RESTORE point test_POINT;
还原至时间点：
FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('2018-01-17 08:26:00', 'YYYY-MM-DD HH24:MI:SS'));
for 12c
2、闪回表
flashback table ORIGINAL_SALARY to timestamp to_date('2016-07-12 02:17:33', 'yyyy-mm-dd hh24:mi:ss');
flashback table ORIGINAL_SALARY to scn 991824;
FLASHBACK table ORIGINAL_SALARY TO RESTORE point test_POINT;
3、闪回查询
select /*+ parallel(a 10)*/ * from test.tab_1 as of timestamp to_timestamp('2019-05-09 13:53:59.116842','YYYY-MM-DD HH24:MI:SSxFF');
select /*+ parallel(a 10)*/ * from test.tab_1 as of scn 432535432;
扫一扫
3215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
