一、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

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

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

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

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博客

RMAN备份恢复测试脚本

最新文章