主頁 > 知識庫 > oracle備份之備份測試腳本的方法（冷備、熱備、rman)

oracle備份之備份測試腳本的方法（冷備、熱備、rman)
熱門標簽：服務外包 Linux服務器 鐵路電話系統(tǒng) 呼叫中心市場需求 AI電銷 地方門戶網(wǎng)站 百度競價排名 網(wǎng)站排名優(yōu)化

1、數(shù)據(jù)庫環(huán)境

數(shù)據(jù)庫DBID及打開模式

SQL> select dbid,open_mode from v$database; 
DBID OPEN_MODE
---------- ----------
4106451204 READ WRITE

數(shù)據(jù)文件:

SQL> select file#,status,enabled,name from v$datafile;

FILE# STATUS ENABLED NAME
---------- ------- ---------- --------------------------------------------------------------------------------
1 SYSTEM READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf
2 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/undotbs01.dbf
3 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf
4 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf
5 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf
6 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf
7 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf

控制文件：

SQL> select status,name from v$controlfile;

STATUS NAME
------- --------------------------------------------------------------------------------
/u01/app/oracle/ocmdb/cfile/ora_control1
/u01/app/oracle/ocmdb/cfile/ora_control2
/u01/app/oracle/ocmdb/cfile/control3

聯(lián)機日志：

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo01.log
1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo11.log
3 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo03.log
2 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo02.log
5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo05.log
5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo55.log
4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo04.log
4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo44.log

2、數(shù)據(jù)庫備份腳本

冷備份腳本

#rem script:coldbak.sh
#rem creater:chon
#rem date:2011
#rem desc:offline full backup database

#--connect database
sqlplus / as sysdba EOF
#--shutdown database
shutdown immediate;
#--Copy Data file
!cp /u01/app/oracle/oradata/ocmdb/dfile/*.dbf /backup
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
#--Copy Control file
!cp /u01/app/oracle/ocmdb/cfile/* /backup
#--Copy Log file
!cp /u01/app/oracle/oradata/ocmdb/lfile/*.log /backup
#--Copy archive log
!cp -R /u01/app/oracle/rmanbak/OCMDB/archivelog/* /backup
#--startup database
startup;

說明：
1、以上腳本在數(shù)據(jù)庫關閉狀態(tài)下備份數(shù)據(jù)庫所有的數(shù)據(jù)文件，聯(lián)機日志，控制文件，歸檔日志（在一個目錄下），如果成功備份，所有文件是一致的。
2、沒有備份參數(shù)文件，參數(shù)文件可以另外備份，沒有必要每次都備份，只需要在改變設置后備份一次。
3、如果以上命令沒有成功依次執(zhí)行，那么備份將是無效的，如連接數(shù)據(jù)庫不成功，那么肯定關閉數(shù)據(jù)庫也不成功，那么備份則無效
4、冷備份建議下人工干預下執(zhí)行。

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

OS熱備份
查看表空間和數(shù)據(jù)文件對應關系

SQL> select file_id,tablespace_name,file_name from dba_data_files;

FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ --------------------------------------------------------------------------------
7 RMAN_TS /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf
6 BIG_TBS /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf
5 TBS3 /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf
4 TBS_1 /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf
3 SYSAUX /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf
2 UNDOTBS /u01/app/oracle/oradata/ocmdb/undotbs01.dbf
1 SYSTEM /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf

數(shù)據(jù)庫OS熱全備份腳本

#rem script:hotbak.sh
#rem creater:chon
#rem date:2011
#rem desc:backup all database datafile in archive

#--connect database
sqlplus / as sysdba EOF

#--archive
alter system archive log current;

#--start hotbak
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf /backup
alter tablespace system end backup;

alter tablespace UNDOTBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
alter tablespace UNDOTBS end backup;

alter tablespace SYSAUX begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;

alter tablespace TBS_1 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf /backup
alter tablespace TBS_1 end backup;

alter tablespace TBS3 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf /backup
alter tablespace TBS3 end backup;

alter tablespace BIG_TBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf /backup
alter tablespace BIG_TBS end backup;

alter tablespace RMAN_TS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf /backup
alter tablespace RMAN_TS end backup;
#--end

#--bak control file
#--binary
alter database backup controlfile to '/backup/controlbinbak.000';
#--ascii
alter database backup controlfile to trace;

alter system archive log current;

說明：
1、熱備份必須在數(shù)據(jù)庫歸檔方式下才可以運行
2、以上腳本可以在數(shù)據(jù)庫運行狀態(tài)下備份數(shù)據(jù)庫所有的數(shù)據(jù)文件（除了臨時數(shù)據(jù)文件），沒有必要備份聯(lián)機日志。
3、歸檔日志至少需要一次完整備份之后的所有日志。
4、如果以上命令沒有成功依次執(zhí)行，那么備份也是無效的，如連接數(shù)據(jù)庫不成功，那么備份則無效

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

RMAN備份腳本
--本地

創(chuàng)建文件夾

mkdir /home/oracle/backup
mkdir /home/oracle/backup/script
mkdir /home/oracle/backup/log
mkdir /home/oracle/backup/export

--備份目的地

mkdir /home/oracle/rman_backup

創(chuàng)建備份表空間

create tablespace rman_tbs datafile '/oradata/luke/rman_tbs01.dbf' size 1024M;

創(chuàng)建備份用戶

create user rman identified by rman default tablespace rman_tbs temporary tablespace temp;
grant connect,resource ,recovery_catalog_owner to rman;

rman catalog rman/rman
create catalog tablespace rman_tbs;
connect target sys/lukewhx@luke
register database;
report schema;

configure retention policy to redundancy 2;
configure retention policy to recovery window of 7 days;

--倒出RMAN用戶數(shù)據(jù)腳本exp_rman.par (即備份catalog庫）
userid=rman/rman
file=/home/oracle/backup/export/rman.dmp
log=/home/oracle/backup/log/rman.log

-- 倒出RMAN數(shù)據(jù)SHELL腳本exp_rman.sh
#!/bin/bash
cd $HOME
. .bash_profile
cd $HOME/backup/script
exp parfile=exp_rman.par

-- 零級備份RMAN腳本level0_backup.rcv
connect catalog rman/rman
connect target sys/unumall@unu2
run {

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; 
CONFIGURE CONTROLFILE AUTOBACKUP ON; 
allocate channel d1 type disk;
allocate channel d2 type disk;
backup incremental level 0 database format '/home/oracle/rman_backup/level0_%d_%s_%p_%u.bak'
tag='level 0' include current controlfile;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d2;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;

--零級備份SHELL腳本的level0_backup.sh
#!/bin/bash
cd $HOME
. .bash_profile
cd $HOME/backup/script
rman cmdfile=level0_backup.rcv msglog=$HOME/backup/log/level0_backup.log
. /home/oracle/backup/script/exp_rman.sh


--一級差異增量備份RMAN腳本 level1_backup.rcv
connect catalog rman/rman
connect target sys/luke@luke
run {
allocate channel d1 type disk;
backup incremental level 1 format '/home/oracle/rman_backup/level1_%d_%s_%p_%u.bak' tag = 'level 1' database;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;


--一級差異增量備份SHELL腳本level1_backup.sh


#!/bin/bash
cd $HOME
. .bash_profile
cd /home/oracle/backup/script
rman cmdfile=level1_backup.rcv msglog=$HOME/backup/log/level1.log
. /home/oracle/backup/script/exp_rman.sh


--二級差異增量備份RMAN腳本 level2_backup.rcv
connect catalog rman/rman
connect target sys/luke@luke
run {
allocate channel d1 type disk;
backup incremental level 2 format '/home/oracle/rman_backup/level2_%d_%s_%p_%u.bak' tag = 'level 2' database;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;

--二級差異增量備份SHELL腳本level2_backup.sh

#!/bin/bash
cd $HOME
. .bash_profile
cd /home/oracle/backup/script
rman cmdfile=level2_backup.rcv msglog=$HOME/backup/log/level2.log
. /home/oracle/backup/script/exp_rman.sh

提高 RMAN增量備份性能

alter database enable block change tracking using file
'/u01/app/oracle/admin/devdb/bdump/luke.log';

desc v$block_change_tracking;

linux下定時執(zhí)行備份腳本

crontab -e -u oracle
SHELL=/bin/bash --以下腳本在bash下執(zhí)行
MAILTO=oracle --執(zhí)行日志以郵件形式郵給oracle用戶，可以/var/spool/mail/oracle下查收
10 1 * * 0 /home/oracle/backup/script/level0_backup.sh
10 1 * * 1 /home/oracle/backup/script/level2_backup.sh
10 1 * * 2 /home/oracle/backup/script/level2_backup.sh
10 1 * * 3 /home/oracle/backup/script/level1_backup.sh
10 1 * * 4 /home/oracle/backup/script/level2_backup.sh
10 1 * * 5 /home/oracle/backup/script/level2_backup.sh
10 1 * * 6 /home/oracle/backup/script/level2_backup.sh

一周差異備份策略：
備份目標庫和catalog庫
周日0級全備，周一周二為2級，周三為1級，周四周五周六為2級。
每天凌晨1點10分開始備份

零級備份

backup incremental level 0 database;

一級差異增量 差異增量是默認增量備份方式

backup incremental level 1 database;

一級累計增量

backup incremental level 1 cumulative database;

總結

到此這篇關于oracle備份之備份測試腳本的方法（冷備、熱備、rman)的文章就介紹到這了,更多相關oracle備份測試腳本內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:
  • mysql數(shù)據(jù)庫和oracle數(shù)據(jù)庫之間互相導入備份
  • Oracle數(shù)據(jù)庫自動備份腳本分享（超實用）
  • Python讀寫及備份oracle數(shù)據(jù)庫操作示例
  • Windows系統(tǒng)下Oracle數(shù)據(jù)庫每天自動備份
  • Linux 自動備份oracle數(shù)據(jù)庫詳解
  • PL/SQL遠程備份和恢復Oracle數(shù)據(jù)庫
  • Oracle自動備份及自動備份步驟

標簽：湘潭 衡水 仙桃 湖南 黃山 銅川 蘭州 崇左

巨人網(wǎng)絡通訊聲明：本文標題《oracle備份之備份測試腳本的方法（冷備、熱備、rman)》，本文關鍵詞  ；如發(fā)現(xiàn)本文內(nèi)容存在版權問題，煩請?zhí)峁┫嚓P信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡，涉及言論、版權與本站無關。
  • 相關文章
  • 收縮
    • 微信客服
    • 微信二維碼

    • 電話咨詢

    • 400-1100-266