RMAN 常用场景及命令

1. Rman catalog

 Catalog 命令可用于注册特定的备份集片。 如：
RMAN>catalog backuppiece 'F:/BACKUP/ORCL_11LH4449_1_1.BAK';
注册归档的重做日志
RMAN>catalog archivelog 'D:/archivelog/ORCL_1_102_719615012.ARC';
 
如文件太多
（1）recovery area 或者 db_recovery_file_dest
（2）Start with
RMAN>catalog start with 'D:/archivelog';
 
recovery area 和 db_recovery_file_dest 功能相同，他们是RMAN对整个FRA 编目录，如果RMAN 发现已经对一些文件编目录，它就会跳过这些文件并继续对控制文件中的剩余文件编目录。如：
RMAN>catalog recovery area;

2. 常用场景

2.1 数据文件损坏

RMAN>run {
startup force mount;
sql  'alter database datafile 4 offline';
sql  'alter database open';
set newname for datafile 4 to 'c:\demo\user01.dbf';
restore datafile 4;
switch datafile 4;
recover datafile 4;
Sql  'alter database datafile 4 online';  }
 

2.2 数据文件丢失

RMAN>run {
sql 'alter database datafile 4 offline';
restore datafile 4;
recover datafile 4;
sql 'alter database datafile 4 online';
} 

2.3 恢复表空间

RMAN> run {
sql 'alter tablespace users offline for recover';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace users online'; }

2.4 数据块恢复

RMAN> blockrecover  datafile 5 block 20,21,100;

RMAN> recover datafile 1 block 233,236 datafile 2 block 100 to 200; 

2.5 不完全恢复

run {

startup force mount;

sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

set until time='2010-09-09 13:00:00';     #基于SCN:  set until scn=123456;   基于日志: set until sequence=58;
restore database;
recover database;
sql 'alter database open resetlogs';  }

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

启动到mount 
run {
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

restore database until time '2015-05-14 14:00:00';

recover database until time '2015-05-14 14:00:00';

sql 'alter database open resetlogs';

}

 

2.6 恢复spfile或controlfile到指定位置

restore spfile  to '/home/oracle/spfile' from '/opt/oracle/db10/dbs/0ipbc7o9_1_1';
restore controlfile to '/home/oracle/control'  from  '/opt/oracle/db10/dbs/0hpbc75v_1_1';

2.7 全库备 

并发为 2 的场景

RMAN> run 

{

allocate channel c1 type disk maxpiecesize=2000m;

allocate channel c2 type disk maxpiecesize=2000m;

sql 'alter system archive log current';

backup database tag='full_20150902'   format '/home/oracle/rmanbak/fullback_%U';

backup archivelog all format '/home/oracle/rmanbak/log_%U';

release channel c1;

release channel c2;

}

压缩备份片 替换backup database为 backup as compressed backupset  database

跳过脱机，不可存取或只读文件

backup database skip readonly;

backup database skip offline;

backup database skip inaccessible;

3. 日志的备份和恢复

backup ARCHIVELOG all delete input;   
backup archivelog from time "sysdate-1" delete input;
 
-- 恢复日志
run 

{
allocate channel ch1 type disk;
restore archivelog from logseq 9;
release channel ch1;

}
 
Run

{ 

set archivelog destination to '/orabak/archive';
sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
restore archivelog time between '2010-11-11 09:00:00' and '2010-11-11 22:00:00';    

}
 
-- 删除归档日志：
$ cat /oradata/backup/scripts/deletearch.sh
source ~/.bash_profile
rman target / log=del_arch.log <<EOF
run{
allocate channel c1 type disk ;
delete noprompt archivelog until time "sysdate-1";
release channel c1;
}
exit;
EOF

4.RMAN LIST

List incarnation;
List backup summary;
List backup by file;
List backup of tablespace users;
List backup of datafile 3;
List archivelog all;
List backup of controlfile;
list archivelog from time 'sysdate-1';
list archivelog from sequence 7;

 

Report schema;

report obsolete;

 

5.Delete backups

Deleting Obsolete RMAN backups Based on Retention Policy
Delete obsolete redundancy=3;
Delete obsolete recovery window of 7 days;

6.Monitoring RMAN Through V$ Views

Status information for jobs in progress and completed jobs is stored in V$RMAN_STATUS.  
V$RMAN_OUTPUT contains the text ouptut of all RMAN jobs.
To see status information on jobs in V$RMAN_STATUS use the following query:
SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;
 
To correlate a channel with a process, run the following query in SQL*Plus while the RMAN job is executing:
SQL> COLUMN CLIENT_INFO FORMAT a30
SQL> COLUMN SID FORMAT 999
SQL> COLUMN SPID FORMAT 9999
SQL> SELECT s.SID, p.SPID, s.CLIENT_INFO  FROM V$PROCESS p, V$SESSION s  WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%';
To calculate the progress of an RMAN job, run the following query in SQL*Plus  while the RMAN job is executing:
SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,  ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
 

7. Validate Database Files

Validate all database files:

>    BACKUP VALIDATE CHECK LOGICAL DATABASE ;

Validate datafile even blocks:

>    VALIDATE DATAFILE 4 BLOCK 10 TO 13;

>    VALIDATE BACKUPSET 3;

 

8. Command File

# my_command_file.txt

CONNECT TARGET /

BACKUP DATABASE PLUS ARCHIVELOG;

EXIT;

Use command file:

$ rman @/my_dir/my_command_file.txt

rman > @/my_dir/my_command_file.txt 

 

9. Rman操作 输出到日志

$ rman log /tmp/rman.log   命令的输出直接到日志，不在终端显示

$ rman | tee rman.log      输出即到日志，同时也在终端显示

 

10. $ rman checksyntax  使用rman的语法检查功能

RMAN> run { backup current controlfile;}

run { backup current controlfile;}

The command has no syntax errors

 

$ rman CHECKSYNTAX @cmdfilename

 

11. RMAN 的格式化串

%c 备份片的拷贝数

%d 数据库名称

%D 位于该月中的第几天 (DD)

%M 位于该年中的第几月 (MM)

%n 数据库名称，向右填补到最大八个字符

%u 一个八个字符的名称代表备份集与创建时间

%p 该备份集中的备份片号，从1开始到创建的文件数

%U 一个唯一的文件名，代表%u_%p_%c

%s 备份集的号

%t 备份集时间戳

%T 年月日格式(YYYYMMDD)