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
