RMAN備份恢復測試指令碼

一、RMAN備份指令碼：

//全備份:

connect target SYS/***;

run{

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=oracle)';

backup incremental level 0 diskratio=0

(database include current controlfile format 'de_full%p%s%t');

sql 'alter system archive log current';

release channel t1;

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=oracle)';

backup diskratio=0 format 'de_ARCH%p%s%t'

archivelog all delete input;

release channel t1;

}

//差分備份:

connect target SYS/***;

run{

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=dif)';

backup incremental level 1 diskratio=0

(database include current controlfile format 'de_dif%p%s%t');

sql 'alter system archive log current';

release channel t1;

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=dif)';

backup diskratio=0 format 'de_ARCH%p%s%t'

archivelog all delete input;

release channel t1;

}

//增量備份:

connect target SYS/***;

run{

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=inc)';

backup incremental level 2 diskratio=0

(database include current controlfile format 'de_inc%p%s%t');

sql 'alter system archive log current';

release channel t1;

allocate channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=inc)';

backup diskratio=0 format 'de_ARCH%p%s%t'

archivelog all delete input;

release channel t1;

}

二、RMAN恢復指令碼例子：

說明：因為資料庫出現故障的情況有很多種，很難以固定指令碼的形式將所有的故障情況及相應的恢復策略以指令碼的形式固定下來，實際的恢復操作一般都需要依靠DBA的經驗來手工進行。我在下面分別給出了資料檔案及控制檔案的恢復例子以供參考。

1.資料庫中資料檔案損壞的恢復

select t.ts#,t.name,d.name from v$tablespace t,v$datafile d where t.ts#=d.ts#;

TS# NAME NAME_1

0 SYSTEM /oradata/webdev/system01.dbf

1 UNDOTBS1 /oradata/webdev/undotbs01.dbf

3 CWMLITE /oradata/webdev/cwmlite01.dbf

4 DRSYS /oradata/webdev/drsys01.dbf

5 INDX /oradata/webdev/indx01.dbf

6 TOOLS /oradata/webdev/tools01.dbf

7 USERS /oradata/webdev/users01.dbf

8 XDB /oradata/webdev/xdb01.dbf

9 TS_1A_WEBAPP /oradata/webdev/TS_1A_WEBAPP.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 102203568 bytes

Fixed Size 741552 bytes

Variable Size 79691776 bytes

Database Buffers 20971520 bytes

Redo Buffers 798720 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/oraebao/haijiang/newdata/users01.dbf'(說明users01.dbf已損壞)

SQL> exit

oracle570:/home/oracle$rman nocatalog

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/***

connected to target database: WEBDEV (DBID=1900767580)

using target database controlfile instead of recovery catalog

RMAN>run

2> {

3> allocate channel t1 type 'sbt_tape'

4> parms= 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=oracle)';

5> restore tablespace users;

6> release channel t1;

7> }

allocated channel: t1

channel t1: sid=12 devtype=SBT_TAPE

channel t1: NMO v4.2.0.0

Starting restore at 20-OCT-05

channel t1: starting datafile backupset restore

channel t1: specifying datafile(s) to restore from backup set

restoring datafile 00007 to /oradata/webdev/users01.dbf

channel t1: restored backup piece 1

piece handle=FULL1240572158125 tag=TAG20051020T044845 params=NULL

channel t1: restore complete

Finished restore at 20-OCT-05

released channel: t1

RMAN> exit

Recovery Manager complete.

oracle570:/oradata/webdev$ls

TS_1A_WEBAPP.dbf drsys01.dbf system01.dbf users01.dbf ----已經恢復被刪除的users01.dbf檔案

control01.ctl indx01.dbf temp01.dbf xdb01.dbf

control02.ctl redo01.log tools01.dbf

control03.ctl redo02.log undotbs01.dbf

cwmlite01.dbf redo03.log user01.dbf.bk

oracle570:/oradata/webdev$sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 20 15:44:21 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

SQL> recover tablespace users; (如提示缺水歸檔日誌31，則需要進行下面的恢復日誌檔案)

Media recovery complete.

SQL> alter database open;

Database altered.

------------------------------－－－－－－－－－－－－－－－－－－－－－－－－－－－－

恢復日誌檔案

$ rman target / nocatalog

Recovery Manager: Release 9.2.0.1.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORATEST (DBID=3095148714)

using target database controlfile instead of recovery catalog

RMAN> run

2> {

3> allocate channel t1 type 'sbt_tape'

4> parms= 'ENV=(NSR_SERVER=backup,NSR_CLIENT=oracle570_serv,NSR_DATA_VOLUME_POOL=oracle)';

5> restore archivelog from logseq 31;

6> release channel t1;

7> }

allocated channel: ch00

channel ch00: sid=11 devtype=SBT_TAPE

channel ch00: VERITAS NetBackup for Oracle - Release 5.1 (2004043014)

Starting restore at 01-JUN-05

archive log thread 1 sequence 32 is already on disk as file /oraebao/haijiang/newdata/1_32.dbf

archive log thread 1 sequence 33 is already on disk as file /oraebao/haijiang/newdata/1_33.dbf

channel ch00: starting archive log restore to default destination

channel ch00: restoring archive log

archive log thread=1 sequence=31

channel ch00: restored backup piece 1

piece handle=al_6_1_559854537 tag=TAG20050601T190856 params=NULL

channel ch00: restore complete

Finished restore at 01-JUN-05

released channel: ch00

RMAN> exit

Recovery Manager complete.

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Jun 1 20:27:44 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> recover tablespace users;

ORA-00279: change 117803 generated at 06/01/2005 19:07:01 needed for thread 1

ORA-00289: suggestion : /oraebao/haijiang/newdata/1_31.dbf

ORA-00280: change 117803 for thread 1 is in sequence #31

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 117902 generated at 06/01/2005 19:08:52 needed for thread 1

ORA-00289: suggestion : /oraebao/haijiang/newdata/1_32.dbf

ORA-00280: change 117902 for thread 1 is in sequence #32

ORA-00278: log file '/oraebao/haijiang/newdata/1_31.dbf' no longer needed for

this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

SQL> select * from tab

2 ;

select * from tab

*

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open;

Database altered.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

ACCESS$ TABLE

ALL_ALL_TABLES VIEW

ALL_APPLY VIEW

ALL_APPLY_CONFLICT_COLUMNS VIEW

ALL_APPLY_DML_HANDLERS VIEW

ALL_APPLY_ERROR VIEW

ALL_APPLY_KEY_COLUMNS VIEW

ALL_APPLY_PARAMETERS VIEW

ALL_APPLY_PROGRESS VIEW

ALL_ARGUMENTS VIEW

ALL_ASSOCIATIONS VIEW

.......

2374 rows selected.

SQL> select name from v$datafile;

NAME

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

/oraebao/haijiang/newdata/system01.dbf

/oraebao/haijiang/newdata/undotbs01.dbf

/oraebao/haijiang/newdata/drsys01.dbf

/oraebao/haijiang/newdata/indx01.dbf

/oraebao/haijiang/newdata/tools01.dbf

/oraebao/haijiang/newdata/users01.dbf

/oraebao/haijiang/newdata/xdb01.dbf

/oraebao/haijiang/newdata/veritas.dbf

8 rows selected.

//恢復臨時檔案

SQL> recover tablespace temp;

1.2控制檔案的備份恢復

備份控制檔案

方法一 alter database backup controlfile to 'd:\CONORCL05.ORA';

方法二 alter database backup controlfile to trace;

恢復

方法一 修改init檔案,再啟動資料庫recover database;

方法二 資料庫啟動到nomount，執行最新D:\oracle\admin\orcl\udump\orcl_ora_1416.trc檔案中第59-80行指令碼儲存到一個SQL檔案(即如下內容所示)後執行即會建立控制檔案，然後啟動到open

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 'D:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 50M,

GROUP 2 'D:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 50M,

GROUP 3 'D:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',

'D:\ORACLE\ORADATA\ORCL\DRSYS01.DBF',

'D:\ORACLE\ORADATA\ORCL\UNDOTBS02.DBF',

'D:\ORACLE\ORADATA\ORCL\INDX01.DBF',

'D:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',

'D:\ORACLE\ORADATA\ORCL\USERS01.DBF',

'D:\ORACLE\ORADATA\ORCL\XDB01.DBF'

CHARACTER SET ZHS32GB18030;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24558279/viewspace-754472/，如需轉載，請註明出處，否則將追究法律責任。