rman备份恢复

recover [automatic] [from 'location']
[database | tablespace tablespace_name | datafile datafile_name]

automatic:进行自动恢复,自动读取默认归档路径
from 'localtion': 从指定归档路径指定恢复
样例:
1. SQL> RECOVER AUTOMATIC database;
2. SQL> RECOVER AUTOMATIC tablespace users;
3. SQL> RECOVER AUTOMATIC datafile '/opt/oracle/...user.dbf';

归档模式下数据库的不完全恢复:
基于time的不完全恢复:
基于cancel的不完全恢复:
基于scn的不完全恢复

recover [automatic] [from 'location'] [database]
[UNTIL TIME time|cancel|change scn]
[USING BACKUP controlfile]
1.查询动态性能视图v$archived_log和v$recovery_log确定哪些归档重做日志文件是必须的
2.startup mount
3.将数据文件修复到原来的位置alter database rename file '/db/..user.dbf' to '/opt/..user.dbf'; 
4.alter database datafile '/opt..user.dbf' online;
5.执行恢复
SQL> RECOVER AUTOMATIC DATABASE UNTIL time '2020-02-01 12:00:00';
SQL> RECOVER AUTOMATIC DATABASE UNTIL cancel;
SQL> RECOVER AUTOMATIC DATABASE until 8726957;
SQL> RECOVER AUTOMATIC DATABASE until cancel using backup controlfile;


##备份##
1.物理备份:
    1).镜像复制
    2).备份集
2.逻辑备份:
    1.数据泵
 注意: 以物理备份为主,逻辑备份为辅

 一致性备份:
 不一致备份:
 完全备份:
     1).backup database;
     2).backup as copy database 
     3).backup as copy current controlfile
 部分备份:
     1).表空间备份:
         1-1).backup tablespace
     2).控制文件备份:
         2-1).backup current controlfile
         2-2).backup as copy current controlfile
         2-3).alter database backup controlfile to trace
     3).数据文件备份:
         3-1).backup datafile
         3-2).backup as copy datafile
     4).归档重做日志文件备份:
         4-1).backup archivelog 
         4-2).backup plus archivelog
         4-3).backup as copy archivelog
联机备份
    SQL> select tablespace_name,file_name from dba_data_files;
    alter tablespace ... begin backup;
    ...
    alter tablespace ... end backup;
    SQL>alter system archive log current;

 
            SQL> select name,b.status from v$datafile d,v$backup b where d.file#=b.file#;

            NAME                                                                                                 STATUS
            ---------------------------------------------------------------------------------------------------- ------------------------------------
            /opt/oracle/oradata/dg/system.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/sysaux.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/undotbs.dbf                                                                   NOT ACTIVE
            /opt/oracle/oradata/dg/users.dbf                                                                     NOT ACTIVE

            SQL> alter tablespace users begin backup;

            Tablespace altered.

            SQL> select name,b.status from v$datafile d,v$backup b where d.file#=b.file#;

            NAME                                                                                                 STATUS
            ---------------------------------------------------------------------------------------------------- ------------------------------------
            /opt/oracle/oradata/dg/system.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/sysaux.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/undotbs.dbf                                                                   NOT ACTIVE
            /opt/oracle/oradata/dg/users.dbf                                                                     ACTIVE

            SQL> alter tablespace users end backup;

            Tablespace altered.

            SQL> select name,b.status from v$datafile d,v$backup b where d.file#=b.file#;

            NAME                                                                                                 STATUS
            ---------------------------------------------------------------------------------------------------- ------------------------------------
            /opt/oracle/oradata/dg/system.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/sysaux.dbf                                                                    NOT ACTIVE
            /opt/oracle/oradata/dg/undotbs.dbf                                                                   NOT ACTIVE
            /opt/oracle/oradata/dg/users.dbf                                                                     NOT ACTIVE

            SQL> 

脱机备份
    1).SQL> SELECT name FROM v$datafile;
    2).SQL> select name from v$controlfile;
    3).SQL> select member from v$logfile;
    4).SQL> show parameter spfile;
    5).关闭数据库,物理复制以上文件

增量备份


归档模式用户管理备份:
1.表空间脱机
SQL> alter tablespace users offline;
2.操作系统复制表空间
3.表空间联机
SQL> alter tablespace users online;
4.对未归档的联机重做日志文件进行归档
SQL> alter system archive log current;




完全恢复
1.查询哪些数据库文件需要恢复

SQL>select file#,error,online_status,change#,time from v$recover_file;

SQL>select r.file# df#,d.name df_name,t.name tbsp_name,d.status,r.error,r.change#,r.time
from v$recover_file r,v$datafile d,v$tablespace t
where t.ts#=d.ts# and d.file#=r.file#; 



不完全恢复
1.归档重做日志文件部分丢失,只能应用未丢失,序列号连续的归档重做日志文件
2.控制文件全部丢失,无法重建完全反应数据库当前状态的控制文件
3.联机重做日志文件损坏或丢失
4.用户错误操作,导致数据丢失


###
rman备份恢复

RMAN> RUN{ 
   allocate channel c1 device type disk;
   backup database;
   }     

rman target rman/rman @/opt/.../backup_db.rman

rman target rman/rman cmdfile=/opt/.../backup_db.rman

RMAN> @/opt/.../backup_db.rman

RMAN> RUN {
    @/opt/.../backup_db.rman
}

--脚本存储在catalog中
RMAN> RUN {execute script backup_db};

--不使用恢复目录连接目标数据库
rman target user/password@orcl [nocatalog]

--连接目标数据库
RMAN>connect target sys/oracle@net_orcl

--连接恢复目录数据库
RMAN>connect catalog rman/rman@net_catalog

--创建恢复目录
1.创建恢复目录数据库oracle,创建一个恢复目录的表空间
SQL> create tablespace recovery_catalog datafile '/opt/...rman.dbf' size 100M autoextend on;

2.在恢复目录数据库oracle中创建用户rman
SQL> create user rman identified by rman 
 default tablespace recovery_catalog 
 temporary tablespace temp;

3.为rman用户授予权限,授予recovery_catalog_owner系统权限
SQL>grant connect,resource,recovery_catalog_owner to rman;

4.使用rman用户连接恢复目录数据库,创建恢复目录
RMAN> connect catalog rman/rman@net_oracle
RMAN> create catalog tablespace recovery_catalog;

5.在恢复目录中对目标数据库进行注册,将目标数据库的控制文件信息转移到恢复目录数据库
RMAN> connect target sys/oracle@net_orcl
RMAN> connect catalog rman/rman@net_oracle
RMAN> register database;
RMAN> resync catalog; --当目标数据库结构发生变化,需要同步目标数据库控制文件到恢复目录中.

注意:
  RMAN> drop catalog --当不适用恢复目录不再使用,可以删除

--rman下执行sql语句
RMAN> sql 'alter system archive log current';

--将某个配置参数恢复到默认值
RMAN> configure default device type clear;

--list
RMAN> list copy of tablespace users;  --你出表空间备份信息
RMAN> list backup of tablespace users;
RMAN> list backup of database;   --列出数据库所有文件信息
RMAN> list backup of datafile 4;  --列出指定的数据文件
RMAN> report schema;  --获取资料档案库信息
RMAN> report obsolete; --根据备份策略查询废弃的备份信息
RMAN>host;  --切换到终端

RMAN工具可以备份的对象包括:
    1. 整个数据库
    2. 数据文件
    3. 表空间
    4. 归档重做日志文件
    5. 控制文件
    6. 服务器初始化参数文件
    7. 数据文件的镜像复制
    8. 控制文件的镜像复制
    9. 备份集

    增量备份:
        1.级别0:后续所有增量备份的基础,与网球备份完全相同
        2.级别1:

增量备份
    1.差异增量备份(每次从上一次增量开始):以最近级别为0或1的增量备份的基础,复制所有被修改的数据块.

    2.累积增量备份(每次从上一次级别0(即,全备)的增量开始):以最近级别为0的增量备份为基础,复制所有被修改的数据块


RMAN> backup device type disk copies 3 datafile 7 format '/opt/%U','/opt/a/%U','/di/%U';

RMAN> configure default device type to disk;

RMAN> RUN {
    set backup copies 2;
    backup device type disk format '/opt/1/%U','/opt/2/%U' tablespace users,system;
}

--将磁盘上的备份集备份到磁带上
RMAN> RUN {
    backup device type disk as backupset database plus archivelog;
    backup device type sbt backupset all;
}

RMAN> backup (datafile 1,2,3 filesperset=1 channel ora_disk 1)
             (datafilecopy '/opt../control01.ctl' filesperset=2 channel ora_disk_2)
             (archivelog from sequence 400 until sequence 403 thread 1 filesperset=3 channel ora_disk_3)


---
语法:
BACKUP [backup_option] backup_object [PLUS ARCHIVELOG] [backup_object_option];

--backup_option解释:
as backupset:以备份集形式备份数据库
as copy: 以进行复制形式备份数据库
copies integer: 指定备份副本的个数
cumulative: 进行累计的增量备份
filesperset integer: 每个备份集中最多可以包含备份文件的个数
full:完全备份
incremental level integer: 进行级别为0或1的差异增量备份
incremental from scn integer: 对指定的数据文件进行差异增量备份,
备份SCN值大于或等于指定SCN的,被修改过的数据库
skip inaccessiable | offline | readonly: 备份时候跳过不可访问,离线,只读文件

--backup_object选项解释
archivelog all:备份所有可用的归档重做日志文件
archivelog [from scn] | [scn between integer and] | [until scan] integer: 基于SCN备份归档重做日志文件
archivelog [from sequence] | [sequence] | [sequence between integer and] | [until sequence] integer thread integer:基于日志序列号备份归档重做日志文件
archivelog [from time] | [time bwteween date_string and]|[until time] date_string:基于时间备份归档重做日志文件
copy of [database] | [datafile datefileSpec] | [tablespace tablespace]:备份已经存在的数据库,数据文件,表空间

--backup_object_option选项解释:

delete [all] input : 成功备份归档重做日志文件,数据文件,镜像复制文件或备份集后,删除源文件
include current controlfile:同时备份当前的控制文件

RMAN> backup database plus archivelog; --备份数据库时候加上归档日志文件

RMAN> backup archivelog all; 备份所有可用的归档重做日志文件,包括当前联机重做日志文件,系统会自动进行一次日志切换,将当前的联机重做日志文件归档

RMAN> backup archivelog all delete all input; --备份所有可用的归档重做日志文件,然后删除归档重做日志文件

RMAN> backup archivelog from sequence 400 until sequence 405; --备份指定归档日志序列号归档日志

RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7'; 备份指定时间段的归档重做日志文件

RMAN> backup archivelog all not backed up 2 times; 根据备份策略需要备份归档重做日志文件 

RMAN> list backup of database; 查看备份集与备份片信息

RMAN> backup tablespace users including current controlfile;

RMAN> backup copies 2 tablespace users format '/opt/1/%U','/opt/2/%U';

RMAN> list backup summary  --查看所有备份的副本个数

## 增量备份:默认增量备份时差异增量备份
1. 级别0的增量备份
RMAN> backup incremental level=0 database format '/opt/%U';

2.对单独的表空间或单独的数据文件进行级别0或者1的增量备份
RMAN> backup incremental level=1 tablespace system datafile 4;

累积增量备份方法:
    RMAN> backup incremental level=1 cumulative tablespace users;

镜像复制:默认形式是创建备份集,即BACKUP as backupset
RMAN> backup as copy database format '/opt/%U';
RMAN> backup as copy datafile ...
RMAN> backup as copy tablespace ...
RMAN> backup as copy dataFileCopy '/opt/...users.dbf' format '/opt/2/users.dbf';
RMAN> backup as copy current controlfile format '/opt/control.bkp';
RMAN> backup as copy archivelog all; 

RMAN> list copy of archivelog all;
RMAN> list copy of database;

##恢复
restore (restore_object [restore_spc_option]) [restore_option];
样例:
RMAN> restore database;
RMAN> restore controlfile from autobackup;
RMAN> restore datafile 4;
RMAN> restore tablespace users;

1) restore_object选项
archivelog
archivelog from scn 修复从指定scn开始的归档重做日志文件
archivelog scn between ... and ...; 修复指定scn范围的归档重做日志文件
controlfile to filename 修复控制文件到新的位置
database skip tablespace tablespace_name 修复整个数据库,跳过指定的表空间
datafile datafileSpec 修复指定的数据文件
spfile  修复服务器初始化参数文件
spfile to filename 修复服务器初始化参数文件到新的位置
tablespace tablespace_name  修复指定表空间

##recover 对修复后的数据进行恢复操作
recover [device type disk|sbt] recover_object [recover_option]

recover_option选项解释:
delete archivelog 恢复操作结束后删除利用备份修复的归档重做日志文件
from backupset 利用备份集进行恢复操作
noRedo 恢复过程中不产生重做日志信息

RMAN> RECOVER DATABASE delete archivelog;
RMAN> recover controlfile;
RMAN> recover datafile 4;
RMAN> recover tablespace users;

##############完全恢复#############

样例: 利用备份完全恢复数据库
RMAN> shutdown immediate
RMAN> startup mount
RMAN> restore database;
RMAN> recover database Delete Archivelog skip tablespace temp;
--delete archivelog 表示在恢复完成后自动删除在恢复过程中使用的归档重做日志文件,但不会删除restore之前的归档重做日志文件
--skip tablespace temp 跳过temp表空间

RMAN> run {
    allocate channel ch1 type disk;
    allocate channel ch2 type disk;
    restore database;
    recover database delete archivelog skip tablespace temp;
}


样例: 数据文件恢复--当表空间只有一个数据文件的时候也是这样恢复的.
1. 连接目标数据库,连接恢复目录
2. 将损坏的数据文件设置为脱机状态
RMAN> sql 'alter database datafile 4 offline';
3. 重建数据文件
RMAN> restore datafile 4;
RMAN> recover datafile 4;
RMAN> sql 'alter database datafile 4 online';

样例: 表空间多个数据文件同时损坏,对整个表空间进行完全恢复
1. 连接目标数据库,连接恢复目录
2. 将损坏的数据文件所属表空间设置为脱机状态
RMAN> sql 'alter tablespace users offline immediate';
3. 对表空间进行修复和恢复操作
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';

##############不完全恢复#############
1. 基于时间
2. 基于SCN
3. 基于日志序列号

1).查看日志历史
SQL> select recid,sequence#,first_change#,next_change#,first_time from v$log_history;

2).启动rman并连接目标数据库,如果使用恢复目录,还需要连接恢复目录
3).将数据库设置为加载状态
RMAN> shutdown immediate;
RMAN> startup mount;

4) 利用set until命令设置恢复终止标记,然后进行数据库的修复与恢复操作
RMAN> sql "alter session set NLS_LANGUAGE=''AMEIRICAN''";
RMAN> sql "alter session set NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''";

--基于时间的不完全恢复
RMAN>run{
    set until time '2020-2-2 10:00:00';
    restore database;
    recover database;
}

--基于SCN的不完全恢复
RMAN>run{
    set until scn 914700;
    restore database;
    recover database;
}

--基于日志序列号的不完全恢复
RMAN>run{
    set until sequence 431;
    restore database;
    recover database;
}

5).以resetlogs方式打开数据库
RMAN>alter database open resetlogs;

6).立即全备份数据库

样例: 控制文件恢复
所有的控制文件丢失,没有恢复目录

1). 配置静态监听

2). 将目标数据库启动到nomount状态
    SQL> startup nomount;

3). 启动RMAN,但不连接目标数据库
[oracle@class191 dg]$ rman
RMAN>

4).利用set dbid命名设置目标数据库的标识符
RMAN> set dbid 111111  --11111为目标数据库标识符

5).连接目标数据库
RMAN> connect target sys/oracle@net_orcl;

6).利用自动备份修复控制文件
RMAN>run{
    set controlfile autobackup format '/opt/%U.ctl';
    allocate channel ch1 device type disk;
    restore controlfile from autobackup;
}
注意: 如果有之前的备份
    RMAN>run{
        allocate channel ch1 device type disk;
        restore controlfile from '/opt/oracle/dg.ctl'; --dg.ctl为备份的控制文件
    }

7).加载数据库,进行数据库的恢复
RMAN>alter database mount;
RMAN>restore database;
RMAN>recover database;
RMAN>alter database open resetlogs;

delete expired:  删除的是那些本来RMAN以为存在但是实际上在磁盘或者
                 磁带上已经被删除了的信息，删除的只是RMAN资料库中的记录；

delete obsolete: 则删除旧于备份保留策略定义的备份数据同时也更新RMAN资料库以及控制文件。

obsolete:出现主要是由于违背备份的保留策略【废弃】而导致的，删除需用delete obsolete，

expired:是在crosscheck检查备份集是否有效，主要表现在只是系统层面上删了备份，
       而没有删除备份信息，【失效状态】，删除需用delete expired；
       
RMAN> list backup of backup;

RMAN> list backup of archivelog all;

RMAN> list backup of controlfile;

--将当前所有的归档日志作为一个备份集备份(backup archivelog all)，删除之前所有已归档日志文件(delete all input)
RMAN> backup archivelog all delete all input;

--不提示删除废弃的,保留备份集中最后一份，其他删除
RMAN> delete noprompt obsolete;  



1、核对所有备份集       RMAN> crosscheck backup;   

2、核对所有数据文件的备份集       RMAN> crosscheck backup of database;

3、核对特定表空间的备份集       RMAN> crosscheck backup of tablespace users;

4、核对特定数据文件的备份集       RMAN> crosscheck backup of datafile 4;

5、核对控制文件的备份集       RMAN> crosscheck backup of controlfile; 

6、核对SPFILE的备份集       RMAN> crosscheck backup of spfile; 

7、核对归档日志的备份集       RMAN> crosscheck backup of archivelog sequence 3;

------------------------------------------------------------------------------------

8、核对所有映像副本       RMAN> crosscheck copy;

9、核对所有数据文件的映像副本       RMAN> crosscheck copy of database;

10、核对特定表空间的映像副本       RMAN> crosscheck copy of tablespace users;

11、核对特定数据文件的映像副本       RMAN> crosscheck copy of datafile 6;

12、核对归档日志的映像副本       RMAN> crosscheck copy of archivelog sequence 4;

13、核对控制文件的映像副本       RMAN> crosscheck copy of controlfile;

crosscheck copy;
或者
crosscheck backup;
然后
delete expired copy;



前面测试使用的命令如下：

delete expired backup;
delete expired archivelog all;




RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8055 instance=racdb2 device type=DISK
specification does not match any archived log in the repository

RMAN>

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      2013-05-10 14:28:30 /opt/backup/control01.ctl
Control File Copy     2      2013-10-15 10:41:06 /tmp/control01.ctl
Control File Copy     4      2013-10-21 16:25:03 +RECOVERY/racdb/snapshot/snapcf_racdb2.f

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      2013-05-10 14:28:30 /opt/backup/control01.ctl
Control File Copy     2      2013-10-15 10:41:06 /tmp/control01.ctl
Control File Copy     4      2013-10-21 16:25:03 +RECOVERY/racdb/snapshot/snapcf_racdb2.f

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: WARNING: 3 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /opt/backup/control01.ctl
RMAN-06214: Datafile Copy   /tmp/control01.ctl
RMAN-06214: Datafile Copy   +RECOVERY/racdb/snapshot/snapcf_racdb2.f


RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      2013-05-10 14:28:30 /opt/backup/control01.ctl
Control File Copy     2      2013-10-15 10:41:06 /tmp/control01.ctl
Control File Copy     4      2013-10-21 16:25:03 +RECOVERY/racdb/snapshot/snapcf_racdb2.f

RMAN> crosscheck backupset ;

RMAN>

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      2013-05-10 14:28:30 /opt/backup/control01.ctl
Control File Copy     2      2013-10-15 10:41:06 /tmp/control01.ctl
Control File Copy     4      2013-10-21 16:25:03 +RECOVERY/racdb/snapshot/snapcf_racdb2.f

RMAN> delete expired backup;

using channel ORA_DISK_1
specification does not match any backup in the repository

RMAN> REPORT OBSOLETE;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     1      2013-05-10 14:28:30 /opt/backup/control01.ctl
Control File Copy     2      2013-10-15 10:41:06 /tmp/control01.ctl
Control File Copy     4      2013-10-21 16:25:03 +RECOVERY/racdb/snapshot/snapcf_racdb2.f