1、查看当前的表空间和数据文件

RMAN> report schema;
Report of database schema for database with db_unique_name BOSTON
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /data/u01/app/oracle/oradata/boston/system01.dbf
2    1670     SYSAUX               ***     /data/u01/app/oracle/oradata/boston/sysaux01.dbf
3    70       UNDOTBS1             ***     /data/u01/app/oracle/oradata/boston/undotbs01.dbf
4    300      USERS                ***     /data/u01/app/oracle/oradata/boston/users01.dbf
5    100      TEST                 ***     /data/u01/app/oracle/oradata/boston/test001.dbf
6    100      TEST11               ***     /home/oracle/test11101.dbf
7    100      ZEVEN                ***     /home/oracle/zeven01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /data/u01/app/oracle/oradata/boston/temp01.dbf

2、查看数据库当前可用的备份

RMAN> RESTORE DATABASE PREVIEW SUMMARY;

Starting restore at 2019:04:14 23:39:42
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
644     B  0  A DISK        2019:04:14 23:36:43 1       1       YES        TAG20190414T233619
652     B  1  A DISK        2019:04:14 23:37:36 1       1       YES        TAG20190414T233735
645     B  0  A DISK        2019:04:14 23:36:56 1       1       YES        TAG20190414T233619
653     B  1  A DISK        2019:04:14 23:37:36 1       1       YES        TAG20190414T233735
641     B  0  A DISK        2019:04:14 23:36:24 1       1       YES        TAG20190414T233619
655     B  1  A DISK        2019:04:14 23:37:40 1       1       YES        TAG20190414T233735
640     B  0  A DISK        2019:04:14 23:36:21 1       1       YES        TAG20190414T233619
654     B  1  A DISK        2019:04:14 23:37:36 1       1       YES        TAG20190414T233735

archived logs generated after SCN 103084444 not found in repository
Media recovery start SCN is 103084444
Recovery must be done beyond SCN 103084453 to clear datafile fuzziness
Finished restore at 2019:04:14 23:39:42

3、完全恢复

startup force mount;
run {
restore database;
recover database;
alter database open;
}

4、根据scn值进行不完整恢复

startup force mount;
run {
restore database until scn 103233673;
recover database until scn 103233673;
alter database open resetlogs;
}

5、使用rman自带的Data Recovery Advisor进行诊断和修复问题

5.1、手动删除数据文件

[oracle@dax-mysql-slave boston]$ ls
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf   test101.dbf  undotbs01.dbf
redo01.log     redo03.log  system01.dbf  test001.dbf  test201.dbf  users01.dbf
[oracle@dax-mysql-slave boston]$ mv users01.dbf /tmp/

5.2、尝试关闭数据库，提示报错：

20:06:51 SYS@ boston> shutdown immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/data/u01/app/oracle/oradata/boston/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
20:06:59 SYS@ boston> exit

5.3、使用rman的Data Recovery Advisor功能去修复：

列出信息list failure

RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
98722      HIGH     OPEN      2019:04:18 20:07:00 One or more non-system datafiles are missing

查看失败建议advise failure;

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
98722      HIGH     OPEN      2019:04:18 20:07:00 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=590 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=6 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=201 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /data/u01/app/oracle/oradata/boston/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /data/u01/app/oracle/diag/rdbms/boston/boston/hm/reco_16185943.hm

修复失败问题repair failure;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /data/u01/app/oracle/diag/rdbms/boston/boston/hm/reco_16185943.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 2019:04:18 20:11:30
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

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 00004 to /data/u01/app/oracle/oradata/boston/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backuporacle/db_BOSTON_20190417_s7tv8kue_1_1
channel ORA_DISK_1: piece handle=/tmp/backuporacle/db_BOSTON_20190417_s7tv8kue_1_1 tag=TAG20190417T234621
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2019:04:18 20:11:37

Starting recover at 2019:04:18 20:11:37
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /data/u01/app/oracle/oradata/boston/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backuporacle/db_BOSTON_20190417_t6tv8ld5_1_1
channel ORA_DISK_1: piece handle=/tmp/backuporacle/db_BOSTON_20190417_t6tv8ld5_1_1 tag=TAG20190417T235413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_10_gchplpwh_.arc
archived log for thread 1 with sequence 11 is already on disk as file /data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_11_gcjrv31m_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /tmp/backuporacle/arch_BOSTON_20190417_939_1.bak
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_2: reading from backup piece /tmp/backuporacle/arch_BOSTON_20190417_940_1.bak
channel ORA_DISK_1: piece handle=/tmp/backuporacle/arch_BOSTON_20190417_939_1.bak tag=TAG20190417T235425
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_8_gcjtcwnn_.arc thread=1 sequence=8
channel default: deleting archived log(s)
archived log file name=/data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_8_gcjtcwnn_.arc RECID=380 STAMP=1005941500
channel ORA_DISK_2: piece handle=/tmp/backuporacle/arch_BOSTON_20190417_940_1.bak tag=TAG20190417T235425
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:02
archived log file name=/data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_9_gcjtcwsb_.arc thread=1 sequence=9
channel default: deleting archived log(s)
archived log file name=/data/u01/app/oracle/fast_recovery_area/BOSTON/archivelog/2019_04_18/o1_mf_1_9_gcjtcwsb_.arc RECID=381 STAMP=1005941501
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019:04:18 20:11:43

sql statement: alter database datafile 4 online
repair failure complete

RMAN> exit
Recovery Manager complete.

6、使用flashback闪回数据库

6.1、开启flashback闪回数据库，需要满足一下条件：

6.1.1、10g以上的库

6.1.2、数据库在归档模式

03:08:25 SYS@ boston> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

6.1.3、fast recovery area配置

03:09:58 SYS@ boston> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/u01/app/oracle/fast_reco
                                                 very_area

6.1.4、数据库下的所有表空间闪回功能必须都开启，有一个没有开启闪回，无没有办法闪回数据库

03:10:58 SYS@ boston>  select name,flashback_on from v$tablespace;
NAME                           FLA
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEMP                           YES
TEST                           YES
TEST11                         YES
ZEVEN                          YES
8 rows selected.
如果某个表空间没有开启闪回，使用以下命令开启：
alter tablespace users flashback on;（注意与alter database flashback on的先后顺序，如果是tablespace的闪回开启在database的闪回之后，需要先关系database闪回alter database flashback off;在开启database 闪回alter database flashback on; 否则闪回的时候会报错：
ORA-38753: Cannot flashback data file 4; no flashback log data.
ORA-01110: data file 4: '/data/u01/app/oracle/oradata/boston/users01.dbf'

6.1.5、数据库开启闪回（alter database … flashback on),查看是否开启v$database.flashback_on

03:11:39 SYS@ boston> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Elapsed: 00:00:00.00
03:12:01 SYS@ boston> alter database flashback on;
Database altered.
Elapsed: 00:00:02.06
03:12:08 SYS@ boston> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Elapsed: 00:00:00.00

6.1.6、flashback database不能用于恢复已经被修复的控制文件的前一个时间点，一旦一个数据库的控制文件被修复或者重新建立，那么存在的flashback log就作废了

6.2、闪回数据库

6.2.1、确保数据库处于mount状态

1 2	SHUTDOWN IMMEDIATE; STARTUP MOUNT;

6.2.2、闪回数据库

6.2.2.1、还原到某个scn号

FLASHBACK DATABASE TO SCN 103290531;

根据时间点查看scn
22:43:08 SYS@ boston> select timestamp_to_scn('18-Apr-19 10:41:41.000000000 PM') from dual;
TIMESTAMP_TO_SCN('18-APR-1910:41:41.000000000PM')
-------------------------------------------------
                                        103290531

6.2.2.2、还原到某个还原点

FLASHBACK DATABASE  TO RESTORE POINT good_data;

创建还原点：
CREATE RESTORE POINT good_data;
查看还原点：
22:47:04 SYS@ boston> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                                                        RESTORE_POINT_TIME           PRE NAME
---------- --------------------- --- ------------ ----------------------------------------------
 103328091                    19 NO             0 19-APR-19 10.47.04.000000000 PM                                                                          NO  GOOD_DATA

6.2.2.3 还原到某个时间点

FLASHBACK DATABASE  TO TIMESTAMP TO_DATE('18-Apr-19 10:41:41','DD-MON-YYYY HH24:MI:SS');

根据scn转换为对应的时间戳
SYS@ boston>  select to_char(scn_to_timestamp('103290531'))from dual;
TO_CHAR(SCN_TO_TIMESTAMP('10329053
----------------------------------
18-APR-19 10.41.39.000000000 PM

6.2.3、打开数据库查看数据内容是否是自己想要还原到的点

ALTER DATABASE OPEN READ ONLY;

6.2.4、如果查询结果是自己想要的，那么关闭数据库并重新打开

1
2
3

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

[Oracle] oracle完全恢复、不完全恢复、闪回数据库

1、查看当前的表空间和数据文件

2、查看数据库当前可用的备份

3、完全恢复

4、根据scn值进行不完整恢复

5、使用rman自带的Data Recovery Advisor进行诊断和修复问题

5.1、手动删除数据文件

5.2、尝试关闭数据库，提示报错：

5.3、使用rman的Data Recovery Advisor功能去修复：

6、使用flashback闪回数据库

6.1、开启flashback闪回数据库，需要满足一下条件：

6.1.1、10g以上的库

6.1.2、数据库在归档模式

6.1.3、fast recovery area配置

6.1.4、数据库下的所有表空间闪回功能必须都开启，有一个没有开启闪回，无没有办法闪回数据库

6.1.5、数据库开启闪回（alter database … flashback on),查看是否开启v$database.flashback_on

6.1.6、flashback database不能用于恢复已经被修复的控制文件的前一个时间点，一旦一个数据库的控制文件被修复或者重新建立，那么存在的flashback log就作废了

6.2、闪回数据库

6.2.1、确保数据库处于mount状态

6.2.2、闪回数据库

6.2.2.1、还原到某个scn号

6.2.2.2、还原到某个还原点

6.2.2.3 还原到某个时间点

6.2.3、打开数据库查看数据内容是否是自己想要还原到的点

6.2.4、如果查询结果是自己想要的，那么关闭数据库并重新打开