RMAN筆記

1. 連接RMAN

SQL> create user rman identified by jansondors;
SQL> grant resource,connect,dba to rman;
$ rman target rman/jansondors

2. 配置RMAN參數(shù)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORACENT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # 保留備份副本的數(shù)量
CONFIGURE BACKUP OPTIMIZATION OFF; # 配置備份優(yōu)化，如果已經(jīng)備份了某個(gè)文件的相同版本，則不會(huì)再備份該文件
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # 備份的數(shù)據(jù)文件保留至服務(wù)器磁盤上
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # 配置是否啟用控制文件的自動(dòng)備份
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # 配置控制文件自動(dòng)備份的格式
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # 備份并行度伟叛，通道數(shù)量越多西采，任務(wù)執(zhí)行時(shí)間越短刨啸；備份文件類型為備份集
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/data/oracle/product/11.2.0/db_1/dbs/snapcf_oracent.f'; # default

RMAN> configure default device type to sbt;
RMAN> configure default device type to disk；
RMAN> configure backup optimization on;
RMAN> configure controlfile autobackup on;

-> 備份路徑遵循的原則
statement format ->configure format ->FLASH_RECOVER_ARER ->$ORACLE_HOME/dbs

3. 配置RMAN脫機(jī)備份

$ sqlplus rman/jansondors as sysdba
SQL> select file_type,percent_space_used,percent_space_reclaimable,number_of_files from v$flash_recovery_area_usage;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=10g;  
SQL> show parameter db_recovery_file_dest;

RMAN> backup database;
RMAN> backup as compressed backupset database;
RMAN> sql 'alter database open';

4. 配置RMAN聯(lián)機(jī)備份

開啟歸檔

$ sqlplus rman/jansondors as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog/noarchivelog;
SQL> alter database open;
SQL> archive log list;

備份數(shù)據(jù)庫

RMAN> backup as compressed backupset database plus archivelog delete all input;

RMAN> run{
          allocate channel ch1 device type disk format '/home/oracle/Downloads/backup_ctl_file/ch1_%U'；
          allocate channel ch2 device type disk format '/home/oracle/Downloads/backup_ctl_file/ch2_%U'；
          backup as backupset
          (datafile 1,4 channel ch1)
          (datafile 2,3,5 channel ch2);
          sql 'alter system archive log current';}
          
RMAN> run{
          allocate channel ch1 device type disk朴下；
          allocate channel ch2 device type disk；
          backup as backupset format '/home/oracle/Downloads/backup_ctl_file/%U'
          (datafile 1,4 channel ch1)
          (datafile 2,3,5 channel ch2);
          sql 'alter system archive log current';}

備份表空間

RMAN> backup tablespace users;
RMAN> backup as compressed backupset tablespace users;

備份數(shù)據(jù)文件

RMAN>  backup as backupset datafile 1 format '/home/oracle/Downloads/backup_ctl_file/datafile_1_%U';

備份控制文件

->手工備份
RMAN> backup current controlfile format '/home/oracle/Downloads/backup_ctl_file_%U.dbf';
RMAN> backup current controlfile ;

->自動(dòng)備份
RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/Downloads/backup_ctl_file/%F';
RMAN> configure controlfile autobackup on ;

備份壞塊處理

默認(rèn)情況下苦蒿，RMAN會(huì)檢查數(shù)據(jù)庫是否發(fā)生物理損壞殴胧，不會(huì)檢查邏輯損壞
->關(guān)閉物理損壞檢查
RMAN> backup nochecksum tablespace users tag='weekly backup';
->啟用邏輯損壞檢查
RMAN> backup check logical tablespace users;
->設(shè)置maxcorrupt,當(dāng)壞塊個(gè)數(shù)超過指定數(shù)量時(shí)，backup進(jìn)程會(huì)停止
RMAN-> run{
           set maxcorrupt for datafile 2,4 to 10;
           backup database;}

5. 配置RMAN增量備份

Level 0增量備份

RMAN> backup incremental level 0 database;

Level 1增量備份

RMAN> backup incremental level 1 database;

快速增量備份

SQL> shutdown immediate;
SQL> startup mount;

SQL> select filename,status,bytes from v$block_change_tracking;
SQL> alter database enable block change tracking using file '/data/oracle/product/11.2.0/oradata/chtrack.log';
SQL> alter database disable block change tracking;
SQL> alter database rename file '/data/oracle/product/11.2.0/oradata/chtrack.log' to '/data/oracle/product/11.2.0/oradata/test/chtrack.log';

應(yīng)用增量備份

# 將增量備份添加到鏡像副本上

RMAN> run{
          backup incremental level 1 for recover of copy with tag 'incr_copy_backup' database;
          recover copy of database with tag 'incr_copy_backup';}

6. 配置恢復(fù)目錄

SQL> create tablespace rcat_tbs datafile '/data/oracle/oradata/ORACENT/rcat_tbs01.dbf' size 100m;
SQL> create user rcat_owner identified by jansondors default tablespace rcat_tbs temporary tablespace temp;
SQL> grant recovery_catalog_owner to rcat_owner;
SQL> grant connect,resource to rcat_owner;

RMAN> rman catalog rman/jansondors target systemn/jansondors@targetOrcl

7. RMAN腳本

create script rman_backup{
 sql 'alter system checkpoint';
 backup database format '/home/oracle/Downloads/offline_backup/backup_$u.dbf';
 backup current controlfile format '/home/oracle/Downloads/offline_backup/backup_ctl_$u.dbf';
}

8. 非歸檔完全恢復(fù)

聯(lián)機(jī)重做日志是循環(huán)使用的佩迟，一個(gè)日志寫滿之后會(huì)切換到下一個(gè)团滥，新的循環(huán)會(huì)覆蓋掉部分變化的數(shù)據(jù)，非歸檔恢復(fù)是一種不完全恢復(fù)

數(shù)據(jù)文件报强、控制文件以及重做日志文件全部丟失

# 數(shù)據(jù)庫處于非歸檔模式
$ sqlplus rman/jansondors as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;

# 備份文件存儲目錄為默認(rèn)的快閃恢復(fù)區(qū)
RMAN> configure controlfile autobackup format for device type disk clear;
RMAN> show all;

# 開啟控制文件自動(dòng)備份
RMAN> configure controlfile autobackup on;
RMAN> show all;

# 控制文件灸姊、數(shù)據(jù)文件、重做日志默認(rèn)位置
SQL> show parameter control_files;
SQL> col name for a30
SQL> select file#,name,status from v$datafile;
SQL> select group#, status, member from v$logfile;

-> 1. 數(shù)據(jù)庫開啟掛載模式
SQL> startup mount;

-> 2. 新增測試數(shù)據(jù)
SQL> alter database open;
SQL> create table test123 as select * from dba_segments;

-> 3. 模擬文件丟失
SQL> shutdown immediate;
$ pwd
$ ls    
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf
$ rm -rf *.*

-> 4. 恢復(fù)數(shù)據(jù)
SQL> startup
ORACLE instance started.
Total System Global Area  759943168 bytes
Fixed Size          2217224 bytes
Variable Size         503319288 bytes
Database Buffers      251658240 bytes
Redo Buffers            2748416 bytes
ORA-00205: error in identifying control file, check alert log for more info
RMAN> restore controlfile from '/data/oracle/flash_recovery_area/ORACENT/autobackup/2017_05_17/o1_mf_s_944236355_dkr0s40f_.bkp';
SQL> alter database mount;
Database altered.
RMAN> restore database;     
RMAN> recover database noredo;
SQL> alter database open resetlogs;
SQL> select group#,sequence#,status from v$log;
SQL> select count(*) from test123;

數(shù)據(jù)文件丟失

-> 1. 模擬USERS表空間丟失
SQL> shutdown immediate
$ pwd
/data/oracle/oracent
$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
$ rm -rf users01.dbf
-> 2. RMAN恢復(fù)
SQL> select file#,name from v$datafile;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
#重做日志被覆蓋
RMAN> recover datafile 4 until cancel;

重做日志文件丟失

-> 1. 模擬文件丟失
SQL> shutdown immediate;
$ pwd
/data/oracle/oracent
$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
$ rm -rf *.dbf
$ rm -rf *.log
-> 2. 恢復(fù)
RMAN> restore database;
RMAN> recover database until cancel;
SQL> alter database open;
SQL> alter database open resetlogs;

遷移數(shù)據(jù)文件

SQL> start mount
RMAN> run{
    set newname for datafile
    'A/system01.dbf' to 'B/system01.dbf';
    set newname for datafile
    'A/users01.dbf' to 'B/users01.dbf';
    restore database from tag=TAG20170517T131491;
    switch datafile all;
}

9. 歸檔完全恢復(fù)

數(shù)據(jù)庫一直處于歸檔模式下秉溉，且歸檔文件和重做日志文件損壞的情況下厨钻，可以在聯(lián)機(jī)狀態(tài)下恢復(fù)數(shù)據(jù)庫文件

非系統(tǒng)表空間損壞

-> 1. 模擬環(huán)境
$ pwd
/data/oracle/oracent
$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
$ rm -rf users01.dbf

-> 2. 恢復(fù)
SQL> alter database datafile 4 offline;
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> alter database datafile 4 online;
SQL> select file_name,tablespace_name,status,online_status from dba_data_files;
RMAN> run{
    sql 'alter database datafile 4 offline';
    restore tablespace users;
    recover tablespace users;
    sql 'alter database datafile 4 online';
}

系統(tǒng)表空間損壞

# system表空間損壞時(shí)，數(shù)據(jù)庫無法啟動(dòng)坚嗜；需要把數(shù)據(jù)庫啟動(dòng)至mount狀態(tài)，使用RMAN進(jìn)行數(shù)據(jù)庫恢復(fù)工作
SQL> startup mount
RMAN> run{
    sql 'alter database datafile 1 offline';
    restore datafile 1 ;
    recover datafile 1;
    sql 'alter database datafile 1 online';
}
SQL> alter database open;

所有數(shù)據(jù)文件丟失

# 在所有數(shù)據(jù)文件丟失诗充，但控制文件和重做日志文件都完好的情況下苍蔬，可以通過RMAN進(jìn)行數(shù)據(jù)庫恢復(fù)工作
RMAN> run{
    restore database;
    recover database;
    sql 'alter database open';
}

10. RMAN恢復(fù)數(shù)據(jù)塊

-> 1. 備份整個(gè)數(shù)據(jù)庫
RMAN> backup database plus archivelog;

-> 2. 模擬環(huán)境
SQL> shutdown immediate;
# 人為修改dbf文件
# 此時(shí)startup指令無效，提示報(bào)錯(cuò)

-> 3. 恢復(fù)
RMAN> backup validate datafile 6;
SQL> select * from v$database_block_corruption;
RMAN> blockrecover datafile 6 block #BLOCK_NUM# from backupset;
RMAN> recover datafile 6;
SQL> alter database open;

11. RMAN備份維護(hù)指令

validate backupset

RMAN> validate backupset 5;

restore...validate

RMAN> restore tablespace users validate;
RMAN> restore datafile '/data/oracle/oracent/system01.dbf' validate;

restore preview

RMAN> restore database preview
RMAN> restore tablespace sysaux preview;
RMAN> restore datafile 5 preview;

list

RMAN> list;
RMAN> list backupset;
RMAN> list backupset 5;
RMAN> list backup of tablespace users;
RMAN> list backup of datafile 1;
RMAN> list backup of archivelog all;
RMAN> list backup of archivelog from time ='sysdate-2';
RMAN> list backup of controlfile;
RMAN> list backup of spfile;
RMAN> list copy of controlfile;
RMAN> list backup summary;

report

RMAN> report schema;
RMAN> report need backup;

最后編輯于：2017.12.07 07:29:43

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末蝴蜓，一起剝皮案震驚了整個(gè)濱河市碟绑，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌茎匠，老刑警劉巖格仲，帶你破解...
沈念sama閱讀 218,607評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異诵冒，居然都是意外死亡凯肋，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,239評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門汽馋，熙熙樓的掌柜王于貴愁眉苦臉地迎上來侮东，“玉大人圈盔，你說我怎么就攤上這事∏难牛” “怎么了驱敲？”我有些...
開封第一講書人閱讀 164,960評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長宽闲。經(jīng)常有香客問我众眨，道長，這世上最難降的妖魔是什么容诬？我笑而不...
開封第一講書人閱讀 58,750評論 1贊 294
?港島之戀（遺憾婚禮）
正文為了忘掉前任娩梨，我火速辦了婚禮，結(jié)果婚禮上放案，老公的妹妹穿的比我還像新娘姚建。我一直安慰自己，他們只是感情好吱殉，可當(dāng)我...
茶點(diǎn)故事閱讀 67,764評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布掸冤。她就那樣靜靜地躺著，像睡著了一般友雳。火紅的嫁衣襯著肌膚如雪稿湿。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,604評論 1贊 305
城市分裂傳說
那天押赊，我揣著相機(jī)與錄音饺藤，去河邊找鬼。笑死流礁，一個(gè)胖子當(dāng)著我的面吹牛涕俗，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播神帅，決...
沈念sama閱讀 40,347評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼再姑，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了找御？” 一聲冷哼從身側(cè)響起元镀，我...
開封第一講書人閱讀 39,253評論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎霎桅，沒想到半個(gè)月后栖疑，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,702評論 1贊 315
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡滔驶，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,893評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年遇革，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 40,015評論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡澳淑，死狀恐怖比原，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情杠巡，我是刑警寧澤量窘，帶...
沈念sama閱讀 35,734評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站氢拥，受9級特大地震影響蚌铜，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜嫩海，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,352評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一冬殃、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧叁怪，春花似錦审葬、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,934評論 0贊 22
一樁弒父案涣觉，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至血柳，卻和暖如春官册，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背难捌。一陣腳步聲響...
開封第一講書人閱讀 33,052評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工膝宁，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人根吁。一個(gè)月前我還...
沈念sama閱讀 48,216評論 3贊 371
代替公主和親
正文我出身青樓员淫，卻偏偏與公主長得像，于是被迫代替她去往敵國和親击敌。傳聞我的和親對象是個(gè)殘疾皇子介返，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,969評論 2贊 355