oracle11g-ocp培訓(xùn) oracle備份恢復(fù)

1、,10g10.2.0.511g11.2.0.412.1.0.2Red Hat 5,6 6u5OEL 6,,冷備歸檔熱備控制文件和參數(shù)文件的恢復(fù)RMAN四個完全恢復(fù)場景兩個不完全恢復(fù)場景,數(shù)據(jù)備份恢復(fù)的原因,Media FailuresUser ErrorsApplication Errors,備份重于一切恢復(fù)是DBA最重要的工作之一定期的恢復(fù)演練更加重要rm –rf $ORACLE_

2、BASE/rm –rf /rm /home/oracle/xxxx/xxx/rm /home/oracle/xxxx/xxx /,DBA警示錄,數(shù)據(jù)恢復(fù)的原理,SCN 數(shù)據(jù)庫對自身變化的一個標(biāo)記系統(tǒng)級（checkpoint_change# v$database）數(shù)據(jù)文件級 (file#,checkpoint_change# v$datafile)結(jié)束SCN(file#,last_change# v$dat

3、afile）數(shù)據(jù)文件頭部 (name,checkpoint_change# v$datafile_header),冷備,操作系統(tǒng)命令復(fù)制cpScpftprsync,歸檔模式,Startup mountAlter database archivelog;Alter database open;Archive log listShow parameter archiveShow parameter db_recove

4、r,熱備,Split blocksData block, os blockAlter tablespace xxxxx begin backup;Cp datafilesAlter tablespace xxxxx end backup;,控制文件和參數(shù)文件的恢復(fù),參數(shù)文件的恢復(fù)最簡單的參數(shù)從AWR中得到從alert日志中得到控制文件的恢復(fù)Backup trace controlfileImage copy重

5、建控制文件后的補(bǔ)充SQL> alter tablespace temp add tempfile '/u02/oracle/oradata/TEST10G/disk5/temp01.dbf' size 20M reuse;,rman備份恢復(fù),rman簡介rman的架構(gòu)rman的優(yōu)點(diǎn)和限制常用的備份配置還原（Restore)，恢復(fù)(Recover),,,優(yōu)點(diǎn)和限制,自動備份元數(shù)據(jù)備份數(shù)據(jù)文件，歸檔文件，

6、控制文件和spfile壓縮備份并行備份壞塊檢測增量備份,Rman的核心,recover.bsqDbms_rcvmanDbms_backup_restorerman target /,日志和可執(zhí)行腳本的使用,rman log appendrman cmdfile rman target / cmdfile=a.sql,Rman常用配置命令,SHOW ALL;CONFIGURE RETENTION POLICY

7、 TO REDUNDANCY 1;CONFIGURE RETENTION POLICY TO recovery window of 3 daysCONFIGURE DEFAULT DEVICE TYPE TO DISK;CONFIGURE CONTROLFILE AUTOBACKUP OFF|ON;,備份分片,RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 50

8、M;RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK maxpiecesize 100M format '/u02/ora11g/flash_recovery_area/TEST/full_bak_%U';,1個channel和多個channel,CONFIGURE CHANNEL 1 DEVICE TYPE DISK MAXPIECESIZE 50 M FORMAT &

9、#39;/u02/ora11g/flash_recovery_area/TEST/full_bak_%U';CONFIGURE CHANNEL 2 DEVICE TYPE DISK MAXPIECESIZE 50 M FORMAT '/u02/ora11g/flash_recovery_area/TEST/full_bak_%U';configure channel 2 device type disk

10、clear;,10g，11g的不同創(chuàng)建一個表空間，查看controlfile的備份情況 less TEST_m000_6627.trcStarting control autobackup*** 2015-08-01 21:11:08.014Control autobackup written to DISK device handle '/u02/ora11g/flash_recovery_area/

11、TEST/ctl_c-2182319634-20150801-10‘MMON后臺進(jìn)程延時處理完成隱含參數(shù)控制 是5分鐘。  1  select a.ksppinm name, b.ksppstvl value, a.ksppdesc description  2    from x$ksppi a, x$ksppcv b  3*  where a.

12、indx = b.indx and a.ksppinm like '%control%delay%',Autobackup controlfile的問題,,Show device typeShow default device typeconfigure channel 1 device type disk format '/home/oracle/%U’;CONFIGURE CHANNEL 1 DEVI

13、CE TYPE DISK clear;,,list backup;list expired backup;report schema;report need backup days 3;report need backup;report obsolete;configure channel device type show,list,report,,List backup of spfile;List backup o

14、f controlfileList backup of tablespace data;List backup of tablespace undotbs;List backup of datafile 1;,,壓縮備份 CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO COMPRESSED BACKUPSET; Oracle推薦的備份方式backup datab

15、ase plus archivelog skip inaccessible delete input;,Block change track,alter database enable block change tracking using file '/u01/app/oracle/change_tracking';alter database disable block change tracking ;,增量備

16、份,backup incremental level=0 databasebackup incremental level=1 databasebackup incremental level=2 database,增量備份,,,,rman還原數(shù)據(jù)庫,Startup nomountRestore controlfile from ‘xxxx’;Alter database mount;Catalog start with

17、‘xxxxx’;Restore database;,rman連接方式,rman target / rman target sys/oracleRMAN> connect target sys/oraclerman target sys/oracle@orcl catalog rman/rman@emrep,參數(shù)文件和控制文件的恢復(fù),參數(shù)文件：alert.logawr手工指定參數(shù),控制文件的恢復(fù),Resetlog

18、sNoresetlogs,4個完全恢復(fù)場景,數(shù)據(jù)庫open狀態(tài)，普通表空間損壞數(shù)據(jù)庫關(guān)閉狀態(tài)，系統(tǒng)表空間損壞數(shù)據(jù)庫關(guān)閉狀態(tài)，普通表空間損壞數(shù)據(jù)庫open狀態(tài)，未備份的數(shù)據(jù)文件恢復(fù),刪除用戶select systimestamp from dual;drop user newtest cascade; run> {> set until time "to_date('xxxx',&

19、#39;YYYY-MM-DD HH24:MI:SS')";> restore database;> recover database;> },不完全恢復(fù)場景-刪除用戶,不完全恢復(fù)場景2,刪除了所有的數(shù)據(jù)文件，日志文件，控制文件restore controlfile from autobackup; restore controlfile from '/u02/oracle/flash

20、_recovery_area/TEST10G/ctl_c-1135735312-20150802-0b'; run{2> restore database;3> recover database;4> }run{2> set until sequence 1;3> recover database;4> }alter database open resetlogs;,修改隱含