//
you're reading...
Database, Oracle RMAN

Oracle RMAN Command Summary

1、Connect to the target database (Don’t need to recover the catalog database):

Rman nocatalog targetsys/change_on_isntall@testdb
Rman catalogrman_user/rman_user@ora9rctargetsys/change_on_install@testdb

2、Display the RMAN configuration in the target database

RMAN> show all;

3、REPORT command:
Report the physical schema of the target database

RMAN> report schema;

Report the data files haven’t been backed up within last 3 days

RMAN> report need backup days=3;

Report the data files haven’t been backed up in the ‘users’ tablespace within last 3 days

RMAN> report need backup days 3 tablespace users;

Report the data file that need more than 3 incremental backup sets (报告恢复数据文件需要的增量备份个数超过3次的数据文件;)

RMAN> report need backup incremental 3;

Report the data file that need no more than 2 backup sets ( 报告备份文件低于2份的所有数据文件)

RMAN> report need backup redundancy 2 database;

or

RMAN> report need backup redundancy=2;

Report the data files recover need the archive log files from more than  6 days (报告文件报表的恢复需要超过6天的归档日志的数据文件;)

RMAN> report need backup recovery window of 6 days;
RMAN> report unrecoverable;

Report the backup sets has been done for more than 2 times

RMAN> report obsolete redunndancy 2;

Report the obsoleted backups

RMAN> report obsolete;

4、LIST Command:list the backup information
List all database backups

RMAN> list backup;

List all expired database backups

RMAN> list expired backup;

List all backup for datafiles

RMAN> list backup of database;

List all backups for specified tablespace

RMAN> list backup of tablespace user01;

List all backups for control files

RMAN> list backup of controlfile;

List all archive log  backups

RMAN> list backup of archivelog all;

List all archive log information

RMAN> list archivelog all;

List the backup of the spfile

RMAN> list backup of spfile;

List the backup copy of the data file specified

RMAN> list copy of datafile 5;

List the backup copy of the control files

RMAN> list copy of controlfile;

List the backup copy of archivelog file

RMAN> list copy of archivelog all;

List the backup copy of the database

RMAN> list incarnation of database;

List all backup copy can be used

RMAN> list backup summary;
'B' means backup
'F' means FULL
'A' means archive log
'0' and '1' mean incremental backup
S means the status of the backup(A AVAILABLE   X EXPIRED )

10、RMAN>list backup by file    按备份类型列出备份;
按照数据文件备份,归档日志备份,控制文件备份,服务器参数文件备份 列出

5、CROSSCHECK command:crosscheck backup information

RMAN> crosscheck backup;
RMAN> crosscheck backup of database;
RMAN> crosscheck backup of tablespace users;
RMAN> crosscheck backup of datafile 4;
RMAN> crosscheck backup of controlfile;
RMAN> crosscheck backup of spfile;
RMAN> crosscheck backup of archivelog sequence 3;
RMAN> crosscheck copy;
RMAN> crosscheck copy of database;
RMAN> crosscheck copy of tablespace users;
RMAN> crosscheck copy of datafile 6;
RMAN> crosscheck copy of archivelog sequence 4;
RMAN> crosscheck copy of controlfile;
RMAN> crosscheck backup tag='SAT_BACKUP';
RMAN> crosscheck backup completed after 'sysdate - 2';
RMAN> crosscheck backup completed between 'sysdate - 5' and 'sysdate -2 ';
RMAN> crosscheck backup device type sBT;
RMAN> crosscheck archivelog all;
RMAN> crosscheck archivelog like '%ARC00012.001';
RMAN> crosscheck archivelog from sequence 12;
RMAN> crosscheck archivelog until sequence 522;

6、DELETE:delete backups
delete old backups

RMAN> delete obsolete;

delete EXPIRED backups

RMAN> delete expired backup;

delete EXPIRED copy

RMAN> delete expired copy;

delete specified backup;

RMAN> delete backupset 19;

delete specified backup piece

RMAN> delete backuppiece '/backup/rman/DEMO_19.bak';
RMAN> delete datafilecopy '/backup/rman/DEMO_19.bak';
RMAN> delete copy;
RMAN> delete archivelog all delete input;

delete all objects after deleting backups

RMAN> delete backupset 22 format = '/backup/rman/%u.bak' delete input;
RMAN> delete backupset id;

7、CHANGE command:chang the status of the backup

RMAN> change backupset 22 unavailable;
RMAN> change datafilecopy 'd:\backup\demo_37.bak' unavailable;

使备份集156永久保留

RMAN> change backupset 156 keep forever logs;

备份集198保留60天

RMAN> change backupset 198 keep until 'sysdate+60' logs;
RMAN> change backupset 156 nokeep;
change backupset 117,118 delete;
change backuppiece 1304 delete;
change archivelog until logseq =544 delete;

8、CONFIGURE command
1.1 show current RMAN configuration

RMAN> show all;

1.2 查询RMAN设置中非默认值

SQL> select name,value from v$rman_configuration;

2. common configure option
2.1 retention policy

configure retention policy to recovery window of 3 days;
configure retention policy to redundancy 3;
configure retention policy clear;

2.2 backup optimization

configure backup optimization on;
configure backup optimization off;
configure backup optimization clear;

2.3 default device type

configure default device type to disk;
configure default device type to stb;
configure default device type clear;

2.4 controlfile

configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/cfs01/backup/conf/conf_%F';
configure controlfile autobackup clear;
configrue controlfile autobackup format for device type disk clear;
configrue snapshot controlfile name to '/cfs01/backup/snapcf/scontrofile.snp';
configrue snapshot controlfile name clear;

2.5 device type disk|stb parallelism n;

configure device type disk|stb parallelism 2;
configure device type disk|stb clear;
configure channel device type disk format 'e/:rmanback_%U';
configure channel device type disk maxpiecesize 100m
configure channel device type disk rate 1200K
configure channel 1 device type disk format 'e/:rmanback_%U';
configure channel 1 device type disk maxpiecesize 100m;

2.6 datafile|archivelog backup copies

configure datafile backup copies for device type disk|stb to 3;
configure archivelog backup copies for device type disk|stb to 3;
configure datafile|archivelog backup copies for device type disk|stb clear
backup device type disk database
format '/disk1/backup/%U', '/disk2/backup/%U', '/disk3/backup/%U';

2.7 exclude

configure exclude for tablespace 'users';
configrue exclude clear;

2.8 maxsetsize

configure maxsetsize to 1G|1000M|1000000K|unlimited;
configure maxsetsize clear;

2.9 auxiliary

CONFIGURE AUXNAME FOR DATAFILE 1 TO '/oracle/auxfiles/aux_1.f';
CONFIGURE AUXNAME FOR DATAFILE 2 TO '/oracle/auxfiles/aux_2.f';
CONFIGURE AUXNAME FOR DATAFILE 3 TO '/oracle/auxfiles/aux_3.f';
CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oracle/auxfiles/aux_4.f';
CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR;

9、CATALOG command can register the RMAN Backup to controlfile and CATALOG database

RMAN> catalog datafilecopy '/backup/rman/user01.bak';
RMAN> catalog controlfile '/backup/rman/user01demo.ctl';
RMAN> catalog archivelog '/backup/rman/user01demo_25.bak';
RMAN> catalog backuppiece '/backup/rman/user01\demo_40.dbf';
RMAN> catalog start with '/backup/bak';
RMAN> change datafilecopy '/backup/rman/user01/user01.bak' uncatalog;
RMAN> change controlfilecopy '/backup/rman/user01/demo.ctl' uncatalog;
RMAN> change backuppiece '/backup/rman/user01/demo_25.bak' uncatalog;

10、Using FORMAT parameter, we can use wildcard to replace all variables

%d:The database unique name;
%D:Day in the month;
%M:Month in the year;
%F:DBID,the format is 'c-IIIIIIIIII-YYYYMMDD-QQ', the 'IIIIIIIIII' is the database unique ID - DBID,
'YYYYMMDD' is the date,QQ is a sequence number between 1-256;
%n:database name,the max length of the database name is 8. Using 'x' to fill the unused space;
%u:It is a set of numbers and the establishment after 8 -character name consisting of the time compressed backup.Use '%u' can generate a unique name for each backup set;
%p:It represents the number of backup pieces in the backup set numbering from 1;
%c:The copy number of the backup piece(numbering begins at 1);
%U: shorthand of the '%u_%p_%c' ,Use it to generate a unique name for each backup piece(a disk file),
most common name method;
%s:the nubmer of the backup sets;
%t:the timestamp of the backup sets;
%T:Year Month Day format (YYYYMMDD);

If DBA don’t setup the FORMAT,RMAN will use the default as %U as the backup sets name

11、validate comand

1、'validate' command to check whether the backup set can restore the database;
2、list backup summary; get the backup set ID, in this case is 40,validate backupset 40;

12、Recovery the deleted catalog records

1. The script $Oracle_HOME/rdbms/admin/prgrmanc.sql will delete the catalog records with 'DELETED' status (prgrmanc.sql script periodically delete recovery catalog records state having DELETED )
2. To delete old records counterpart incarnation. Must remove these counterparts from DBINC table,
Use RC_DATABASE_INCARNATION view to determine to delete counterpart. To delete a record DBINC_KEY each counterpart,then using SQL*Plus, execute the query 
'delete from dbinc where dbinc_key=2';

13、Synchronize rman catalog manually

RMAN> resync catalog;

14、Creating the RMAN Script (RMAN script in shell script)
1) connect to the target database
2) Create the script

RMAN> create script. my_bk_script
RMAN> {backup database plus archivelog;}

3) print the script

RMAN> print script. my_bk_script;

4) run the script to backup the target database

RMAN> run {execute scipt my_bk_script;}

5) Delete the script

RMAN> delete script. my_bk_script;

15、archivelog mode complete recovery
step1:

set oracle_sid=testdb
rman target rman_user/password
configure controlfile autobackup on;

step2:

backup database plus archivelog delete input;

step3:

shutdown immediate;

step4:

rename all datafile and control file, do not rename archivelog file

step5:

startup nomount;
set DBID=****
restore controlfile from autobackup;
alter database mount;
step6: restore database;
recover database;
alter database open resetlogs;

16、Tablespace Recovery

sql"alter tablespace users offline";
sql"alter tablespace tools offline";
restore tablespace users,tools;
recover tablespace users,tools;
sql"alter tablespace users online";
sql"alter tablespace tools online";

17、Data Files Recovery

sql"alter database datafile 3 offline";
sql"alter database datafile '/oradata/testdb/users01.dbf' offline";
restore datafile 3
restore datafile '/oradata/testdb/users01.dbf'; 
recover datafile 3 recover datafile '/oradata/testdb/users01.dbf'; 
sql"alter database datafile 3 online"; 
sql"alter database datafile '/oradata/testdb/users01.dbf'online";

18、RMAN Advanced Recovery

1) RMAN Recover to a point of time

run
{
  set until time "to_date('20/10/14 17:00:00','mm/dd/yy hh24:mi:ss')"
  restore database;
  recover database;
  alter database open resetlogs;
}

2) RMAN Recover base on SCN

startup mount;
restore database until SCN 10000;
recover database until SCN 10000;
alter database open resetlogs;

3) RMAN recover base on the archive log sequence number

startup mount;
restore database until SEQUENCE 100 thread 1;
recover database until SEQUENCE 100 thread 1;
alter database open resetlogs;
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: