eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 沧海桑田 - 记 纵贯线 2009 北京演唱会 | Blog首页 | 李宗盛、罗大佑 - 两个青春偶像挥不去 »

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-

历史上的今天...
    >> 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 |

2 Comments

在您的blog里经常可以看到这样非常基础却很实用的文章,这在别人的blog里是不常见的,对广大oracle的初学者来说您的blog是非常好的教科书,衷心感谢您一直在提携oraclers方面作出的表率,希望可以一直看到这样的好文章

嘿,谢谢支持,这样的一些小知识,是在工作中的随手记录,技术含量不高,但是有点实践价值,希望对大家有用:)


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com