數(shù)據(jù)庫備份和恢復方案及步驟(精)

1、操作系統(tǒng)：GNU/Linux數(shù)據(jù)庫管理系統(tǒng)：Oracle10g一、安裝 Recovery Catalog1, 用 dbca創(chuàng)建一個 DATABASE catdb,用于存放 recovery catalog data oraclelocalhost $dbca2, 在catdb中創(chuàng)建一個名為rcat_ts的表空間作為用戶rman的默認表空間oraclelocalhost $sqlplus sys/oraclecatdb as sysdbaSQL create tablespace rcat_ts;SQL create user rma n ide ntified by oracle tempor

2、ary tablespace tempDefault tablespace rcat_ts quota un limited on rcat_ts;3, 授予catlog所有者rman recovery_catalog_owne角色，該角色提供給用戶所有 查詢和維護recovery catalog所必需的權(quán)限SQL grant conn ect,resource,recovery_catalog_ow ner to rma n;4, 啟動 rman,以catalog所有者 rman登錄oraclelocalhost $rma n catalog rman /oraclecatdbRMAN cr

3、eate catalog tablespace rcat_ts;5, 用rman同時連接到target數(shù)據(jù)庫、catalog數(shù)據(jù)庫，在新建的recovery catalog 中注冊target數(shù)據(jù)庫oraclelocalhost $rma n target sys/oracleorcl catalog rma n/oraclecatdbRMAN register database;6, 察看已在recovery catalog中注冊的target數(shù)據(jù)庫oraclelocalhost $sqlplus rman /oraclecatdbSQLselect * from db;二、配置RMAN1,

4、 同時連接target數(shù)據(jù)庫和recovery catalog數(shù)據(jù)庫oraclelocalhost $rma n target sys/oracleorcl catalog rman /oraclecatdb2, RMAN show all （檢查配置con figure default device type to disk （設(shè)置默認的備份的設(shè)備為磁盤CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;（設(shè)置備份的并行級別con figure cha nnel 1 device type disk format

5、 7home/new/backup_%U設(shè)置備份文 件格式con figure cha nnel 2 device type disk format 7home/oracle/backup_%U設(shè)置備份 文件格式con figure con trolfile autobackup on打開控制文件的自動備份con figure con trolfile autobackup format for device type disk to /home/new/ctl_%F （設(shè)置控制文件備份格式三. 備份測試備份全庫1, 同時連接target數(shù)據(jù)庫和recovery catalog數(shù)據(jù)庫oracl

6、elocalhost $rma n target sys/oracleorcl catalog rman /oraclecatdb2, RMAN backup database plus archivelog delete in put;3, 連接target數(shù)據(jù)庫oraclelocalhost $sqlplus sys/oracleorcl as sysdbaSQL co nn hr/hr;SQL select salary from employees where employee_id=157;SQL update employees set salary =2000 where emp

7、loyee_id=157;SQL commit;SQL select salary from employees where employee_id=157;四. 恢復測試復原及恢復全庫1, 連接target數(shù)據(jù)庫oraclelocalhost $sqlplus sys/oracleorcl as sysdba2, 把target數(shù)據(jù)庫先關(guān)閉，然后啟動到mount狀態(tài)SQL shutdow n immediate;SQL startup mount;3, 復原及恢復全庫RMAN restore databasecha nnel ORA_DISK_1: restore complete, ela

8、psed time: 00:05:10RMAN recover databasemedia recovery complete, elapsed time: 00:00:074, 連接target數(shù)據(jù)庫oraclelocalhost $sqlplus sys/oracleorcl as sysdba5, 打開target數(shù)據(jù)庫SQL alter database ope n;6, 查看已錄入的事物SQL co nn hr/hr;SQL select salary from employees where employee_id=157;假設(shè)備份了一個表空間users恢復表空間：rma n ta

9、rget rma n/rman test2006 catalog rma n/rman test2007sql alter tablespace users offline immediate使 TEST2006 的 USERS表空間脫機 進入 test2006select tablespace_name,status from dba_tablespace檢查 USERS 已經(jīng)脫機退出 RMAN刪除 D:oracleoradataTEST2006users01.dbfrma n target rma n/rma n test2006 catalog rma n/rma n test2007r

10、estore tablespace usersrecover tablespace userssql alter tablespace users online使 TEST2006 的 USERS 表空間在線進入 test2006select tablespace_name,status from dba_tablespace檢查 USERS 已經(jīng)在線三、物理（熱備份。Linux下備份及恢復步驟介紹切換服務(wù)器歸檔模式，如果已經(jīng)是歸檔模式可跳過此步：%sqlplus /nolog （啟動 sqlplusSQL conn / as sysdba以DBA身份連接數(shù)據(jù)庫SQL shutdow n im

11、mediate;立即關(guān)閉數(shù)據(jù)庫SQL startup mount啟動實例并加載數(shù)據(jù)庫，但不打開SQL alter database archivelog;更改數(shù)據(jù)庫為歸檔模式SQL alter database ope n;打開數(shù)據(jù)庫SQL alter system archive log start;啟用自動歸檔SQL exit （退出2、連接:%rman target=gti/gti123LANDF;（啟動恢復管理器3、基本設(shè)置：RMAN con figure default device type to disk;（設(shè)置默認的備份設(shè)備為磁盤RMAN con figure device t

12、ype disk parallelism 2;（設(shè)置備份的并行級別，通道數(shù)RMAN CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 7home/oracle/backup/data/backup_%U;設(shè)置備份的文件格式，只適用于磁盤設(shè)備RMAN CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 7home/oracle/backup/data1/backup_%U;設(shè)置備份的文件格式，只適用于磁盤設(shè)備RMAN con figure con trolfile autobackup on;（打開控制文件與服務(wù)器參數(shù)文件

13、 的自動備份RMAN con figure con trolfile autobackup format for device type disk to /home/oracle/backup/data/ctl_%F;設(shè)置控制文件與服務(wù)器參數(shù)文件自動備份的文件 格式4、查看所有設(shè)置：RMAN show all5、查看數(shù)據(jù)庫方案報表：RMAN report schema;6備份全庫：RMAN backup database plus archivelog delete in put;備份全庫及控制文件、服 務(wù)器參數(shù)文件與所有歸檔的重做日志，并刪除舊的歸檔日志7、備份表空間：熱備份（歸檔狀態(tài)下才有

14、效果1、alter tablespace tablespace_name start backup;2、拷貝你的該表空間的數(shù)據(jù)文件備份3、alter tablespace tablespace_name end backup;4、一個一個表空間做完，就備份完了RMAN backup tablespace system plus archivelog delete in put;備份指定表空間 及歸檔的重做日志，并刪除舊的歸檔日志RMAN backup tablespace UNDOTBS plus archivelog delete in put;備份指定表 空間及歸檔的重做日志，并刪除舊的歸

15、檔日志RMAN backup tablespace SYSAUX plus archivelog delete in put;備份指定表空 間及歸檔的重做日志，并刪除舊的歸檔日志RMAN backup tablespace TEMP plus archivelog delete in put;備份指定表空間 及歸檔的重做日志，并刪除舊的歸檔日志8、備份歸檔日志：RMAN backup archivelog all delete in put;9、復制數(shù)據(jù)文件：/*RMAN copy 7home/oracle/data/gti_u ndotbs.dbf 1 to*/10、查看備份和文件復本：RM

16、AN list backup;11、驗證備份：RMAN validate backupset 3;12、從自動備份中恢復服務(wù)器參數(shù)文件：RMAN shutdow n immediate;（立即關(guān)閉數(shù)據(jù)庫RMAN startup nomou nt;（啟動實例RMAN restore spfile to pfile 7backup1/i ni tgti.ora from autobackup;（從自動備份 中恢復服務(wù)器參數(shù)文件13、從自動備份中恢復控制文件：RMAN shutdow n immediate;（立即關(guān)閉數(shù)據(jù)庫RMAN startup nomou nt;（啟動實例RMAN restor

17、e con trolfile to 7backup1 from autobackup;（從自動備份中恢復控 制文件13、恢復和復原全數(shù)據(jù)庫：RMAN shutdow n immediate;（立即關(guān)閉數(shù)據(jù)庫RMAN exit （退出%rman target=gti/gti123LANDF;（啟動恢復管理器（指定初始化參 數(shù)文件啟動數(shù)據(jù)庫RMAN restore database;還原數(shù)據(jù)庫RMAN recover database;恢復數(shù)據(jù)庫RMAN alter database ope n;打開數(shù)據(jù)庫14、恢復和復原表空間：RMAN sql alter tablespace users of

18、fli ne immediate;將表空間脫機RMAN exit （退出恢復管理器將表空間重命名將 表空間重命名（表 空間重命名%rman target=gti/gti123LANDF;（啟動恢復管理器RMAN restore tablespace users;還原表空間RMAN recover tablespace users;恢復表空間RMAN sql alter tablespace users onlin e;將表空間聯(lián)機15、增量備份與恢復第一天的增量基本備份RMAN backup in creme ntal level=O database plus archivelog delete in put;第二天的增量差異備份：RMAN backup in creme ntal level=2 database plus archivelog delete in put;第三天的增量差異備份：RMAN backup in creme ntal level=2 database plus ar