« 沧海桑田 - 记 纵贯线 2009 北京演唱会 | Blog首页 | 李宗盛、罗大佑 - 两个青春偶像挥不去 »
RMAN结合Read Only、Exclude的备份策略
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2009/04/rman_readonly_exclude.html
启动数据库，我是通过一个批处理脚本来完成的：链接：https://www.eygle.com/archives/2009/04/rman_readonly_exclude.html
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-
历史上的今天...
>> 2020-04-22文章:
>> 2016-04-22文章:
>> 2013-04-22文章:
>> 2008-04-22文章:
>> 2006-04-22文章:
>> 2005-04-22文章:
By eygle on 2009-04-22 10:01 | Comments (2) | Backup&Recovery | 2259 |
在您的blog里经常可以看到这样非常基础却很实用的文章，这在别人的blog里是不常见的，对广大oracle的初学者来说您的blog是非常好的教科书，衷心感谢您一直在提携oraclers方面作出的表率，希望可以一直看到这样的好文章
嘿，谢谢支持，这样的一些小知识，是在工作中的随手记录，技术含量不高，但是有点实践价值，希望对大家有用：）