RMAN结合Read Only、Exclude的备份策略

启动数据库，我是通过一个批处理脚本来完成的：

E:\\>9i



E:\\>echo off

OracleServiceEEYGLE 服务正在启动 .........................................................

OracleServiceEEYGLE 服务已经启动成功。

登陆数据库，将部分表空间更改为Read Only：

E:\\>sqlplus "/ as sysdba"



SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:09:45 2009



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





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production



SQL> select name from v$datafile;



NAME

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

D:\\ORACLE\\ORADATA\\EEYGLE\\SYSTEM01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\UNDOTBS01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\CWMLITE01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\DRSYS01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\EXAMPLE01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\INDX01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\ODM01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\TOOLS01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\USERS01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\XDB01.DBF

D:\\ORACLE\\ORADATA\\EEYGLE\\PERFSTAT.DBF



11 rows selected.



SQL> select sum(bytes)/1024/1024/1024 from v$datafile;



SUM(BYTES)/1024/1024/1024

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

              1.65161133



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival            Disabled

Archive destination            D:\\oracle\\oradata\\EEYGLE\\ARCHIVE

Oldest online log sequence    52

Next log sequence to archive  53

Current log sequence          54



SQL> alter tablespace tools read only;



Tablespace altered.



SQL> alter tablespace perfstat read only;



Tablespace altered.





SQL> select 'alter tablespace '||tablespace_name ||' read only;' from dba_tablespaces;



'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'

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

alter tablespace SYSTEM read only;

alter tablespace UNDOTBS1 read only;

alter tablespace TEMP read only;

alter tablespace CWMLITE read only;

alter tablespace DRSYS read only;

alter tablespace EXAMPLE read only;

alter tablespace INDX read only;

alter tablespace ODM read only;

alter tablespace TOOLS read only;

alter tablespace USERS read only;

alter tablespace XDB read only;



'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'

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

alter tablespace PERFSTAT read only;



12 rows selected.



SQL> alter tablespace CWMLITE read only;



Tablespace altered.



SQL> alter tablespace DRSYS read only;



Tablespace altered.



SQL> alter tablespace EXAMPLE read only;



Tablespace altered.



SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

使用RMAN进行一次0级备份，注意根据skip readonly子句，Read ONLY表空间会被自动跳过：

E:\\>rman target /



Recovery Manager: Release 9.2.0.8.0 - Production



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



connected to target database: EEYGLE (DBID=1052376487)





RMAN> run{

2>    allocate channel c1 type disk;

3>    backup incremental level 0 tag 'db0' format 'e:\\temp\\db0%u_%s_%p'

4>    database skip readonly;

5>    sql 'alter system archive log current';

6>    backup filesperset 3 format 'e:\\temp\\arch%u_%s_%p' archivelog all delete input;

7>    release channel c1;

8> }



using target database controlfile instead of recovery catalog

allocated channel: c1

channel c1: sid=10 devtype=DISK



Starting backup at 21-APR-09

skipping read-only file 3

skipping read-only file 4

skipping read-only file 5

skipping read-only file 8

skipping read-only file 11

channel c1: starting incremental level 0 datafile backupset

channel c1: specifying datafile(s) in backupset

including current SPFILE in backupset

including current controlfile in backupset

input datafile fno=00002 name=D:\\ORACLE\\ORADATA\\EEYGLE\\UNDOTBS01.DBF

input datafile fno=00001 name=D:\\ORACLE\\ORADATA\\EEYGLE\\SYSTEM01.DBF

input datafile fno=00010 name=D:\\ORACLE\\ORADATA\\EEYGLE\\XDB01.DBF

input datafile fno=00009 name=D:\\ORACLE\\ORADATA\\EEYGLE\\USERS01.DBF

input datafile fno=00006 name=D:\\ORACLE\\ORADATA\\EEYGLE\\INDX01.DBF

input datafile fno=00007 name=D:\\ORACLE\\ORADATA\\EEYGLE\\ODM01.DBF

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\DB008KD10DI_8_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:04:08

Finished backup at 21-APR-09



sql statement: alter system archive log current



Starting backup at 21-APR-09

current log archived

channel c1: starting archive log backupset

channel c1: specifying archive log(s) in backup set

input archive log thread=1 sequence=51 recid=76 stamp=656093277

input archive log thread=1 sequence=52 recid=77 stamp=656093480

input archive log thread=1 sequence=53 recid=78 stamp=684753587

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\ARCH09KD10M8_9_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:09

channel c1: deleting archive log(s)

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00051.001 recid=76 stamp=656093277

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00052.001 recid=77 stamp=656093480

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00053.001 recid=78 stamp=684753587

channel c1: starting archive log backupset

channel c1: specifying archive log(s) in backup set

input archive log thread=1 sequence=54 recid=79 stamp=684753596

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\ARCH0AKD10MJ_10_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:17

channel c1: deleting archive log(s)

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00054.001 recid=79 stamp=684753596

channel c1: starting archive log backupset

channel c1: specifying archive log(s) in backup set

input archive log thread=1 sequence=55 recid=80 stamp=684753601

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\ARCH0BKD10N5_11_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:04

channel c1: deleting archive log(s)

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00055.001 recid=80 stamp=684753601

Finished backup at 21-APR-09



released channel: c1



RMAN> exit





Recovery Manager complete.

备份完成之后修改部分数据：

E:\\>sqlplus /nolog



SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:39:34 2009



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



SQL> connect eygle/eygle

Connected.



SQL> create table eygle as select * from dba_objects;



Table created.



SQL> alter system switch logfile;



System altered.



SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

进行Level 1级的增量备份：

E:\\>rman target /



Recovery Manager: Release 9.2.0.8.0 - Production



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



connected to target database: EEYGLE (DBID=1052376487)



RMAN> run{

2>    allocate channel c1 type disk;

3>    backup incremental level 1 tag 'db1' format 'e:\\temp\\db1%u_%s_%p'

4>    database skip readonly;

5>    sql 'alter system archive log current';

6>    backup filesperset 3 format 'e:\\temp\\arch%u_%s_%p'

7>    archivelog all delete input;

8>    release channel c1;

9> }



using target database controlfile instead of recovery catalog

allocated channel: c1

channel c1: sid=11 devtype=DISK



Starting backup at 21-APR-09

skipping read-only file 3

skipping read-only file 4

skipping read-only file 5

skipping read-only file 8

skipping read-only file 11

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current SPFILE in backupset

including current controlfile in backupset

input datafile fno=00002 name=D:\\ORACLE\\ORADATA\\EEYGLE\\UNDOTBS01.DBF

input datafile fno=00001 name=D:\\ORACLE\\ORADATA\\EEYGLE\\SYSTEM01.DBF

input datafile fno=00010 name=D:\\ORACLE\\ORADATA\\EEYGLE\\XDB01.DBF

input datafile fno=00009 name=D:\\ORACLE\\ORADATA\\EEYGLE\\USERS01.DBF

input datafile fno=00006 name=D:\\ORACLE\\ORADATA\\EEYGLE\\INDX01.DBF

input datafile fno=00007 name=D:\\ORACLE\\ORADATA\\EEYGLE\\ODM01.DBF

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\DB10CKD120F_12_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:01:07

Finished backup at 21-APR-09



sql statement: alter system archive log current



Starting backup at 21-APR-09

current log archived

channel c1: starting archive log backupset

channel c1: specifying archive log(s) in backup set

input archive log thread=1 sequence=56 recid=81 stamp=684755029

input archive log thread=1 sequence=57 recid=82 stamp=684755031

input archive log thread=1 sequence=58 recid=83 stamp=684755033

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\ARCH0DKD122P_13_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:05

channel c1: deleting archive log(s)

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00056.001 recid=81 stamp=684755029

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00057.001 recid=82 stamp=684755031

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00058.001 recid=83 stamp=684755033

Finished backup at 21-APR-09



released channel: c1



RMAN> exit





Recovery Manager complete.

再更改数据库内容：

E:\\>sqlplus "/ as sysdba"



SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 21 09:45:52 2009



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





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production



SQL> connect eygle/eygle

Connected.

SQL> drop table test;



Table dropped.



SQL> drop table eygle;



Table dropped.



SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

使用RMAN配置Exclude排除表空间，注意此处测试排除多个表空间的备份：

E:\\>rman target /



Recovery Manager: Release 9.2.0.8.0 - Production



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



connected to target database: EEYGLE (DBID=1052376487)



RMAN> configure exclude for tablespace INDX;



using target database controlfile instead of recovery catalog

tablespace INDX will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored



RMAN> show exclude;



RMAN configuration parameters are:

CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';



RMAN> configure exclude for tablespace ODM;



tablespace ODM will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored



RMAN> show exclude;



RMAN configuration parameters are:

CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';

CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';



RMAN> configure exclude for tablespace TOOLS;



tablespace TOOLS will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored



RMAN>



RMAN> configure exclude for tablespace XDB;



tablespace XDB will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored



RMAN> show exclude;



RMAN configuration parameters are:

CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';

CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';

CONFIGURE EXCLUDE FOR TABLESPACE 'TOOLS';

CONFIGURE EXCLUDE FOR TABLESPACE 'XDB';



RMAN> configure exclude for tablespace USERS;



tablespace USERS will be excluded from future whole database backups

new RMAN configuration parameters are successfully stored



RMAN> show exclude;



RMAN configuration parameters are:

CONFIGURE EXCLUDE FOR TABLESPACE 'INDX';

CONFIGURE EXCLUDE FOR TABLESPACE 'ODM';

CONFIGURE EXCLUDE FOR TABLESPACE 'TOOLS';

CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';

CONFIGURE EXCLUDE FOR TABLESPACE 'XDB';



RMAN> run{

2>    allocate channel c1 type disk;

3>    backup incremental level 1 tag 'db1' format 'e:\\temp\\db1%u_%s_%p'

4>    database skip readonly;

5>    sql 'alter system archive log current';

6>    backup filesperset 3 format 'e:\\temp\\arch%u_%s_%p'

7>    archivelog all delete input;

8>    release channel c1;

9> }



allocated channel: c1

channel c1: sid=10 devtype=DISK



Starting backup at 21-APR-09

skipping read-only file 3

skipping read-only file 4

skipping read-only file 5

file 6 is excluded from whole database backup

file 7 is excluded from whole database backup

file 8 is excluded from whole database backup

file 9 is excluded from whole database backup

file 10 is excluded from whole database backup

skipping read-only file 11

channel c1: starting incremental level 1 datafile backupset

channel c1: specifying datafile(s) in backupset

including current SPFILE in backupset

including current controlfile in backupset

input datafile fno=00002 name=D:\\ORACLE\\ORADATA\\EEYGLE\\UNDOTBS01.DBF

input datafile fno=00001 name=D:\\ORACLE\\ORADATA\\EEYGLE\\SYSTEM01.DBF

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\DB10EKD12B2_14_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:57

Finished backup at 21-APR-09



sql statement: alter system archive log current



Starting backup at 21-APR-09

current log archived

channel c1: starting archive log backupset

channel c1: specifying archive log(s) in backup set

input archive log thread=1 sequence=59 recid=84 stamp=684755357

input archive log thread=1 sequence=60 recid=85 stamp=684755360

channel c1: starting piece 1 at 21-APR-09

channel c1: finished piece 1 at 21-APR-09

piece handle=E:\\TEMP\\ARCH0FKD12D1_15_1 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:04

channel c1: deleting archive log(s)

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00059.001 recid=84 stamp=684755357

archive log filename=D:\\ORACLE\\ORADATA\\EEYGLE\\ARCHIVE\\ARC00060.001 recid=85 stamp=684755360

Finished backup at 21-APR-09



released channel: c1



RMAN>

通过排除多个表空间、跳过Read Only表空间，可以简化我们的备份策略，在特定条件下，有助于我们的数据库管理与维护。



-The End-