使用Oracle RMAN脚本

为什么要使用脚本?

为什么要使用RMAN命令脚本呢? 这里有2个主要原因:

  1. 绝大多数RMAN操作都是批量的同时也是自动化的。举例来说,备份数据库是一个反反复复的操作而非每次执行都要费一番功夫写命令。
  2. 脚本提供了一致性。在临时性的抑或者说一次性的操作,譬如从备份中恢复数据库,一般都不适用自动化。但是,操作本身是相同的,无论DBA在何种环境下。

Oracle11g中有两种脚本形式

  1. 使用命令文件: 即文件系统上的一个文本文件,也叫平面文件。
  2. 使用存储的脚本,该脚本存储在Oracle恢复目录中,由RMAN命令行调用。

命令文件形式

Oracle 命令文件是一种文本解释文件类似于UNIX下的SHELL脚本和Windows中的批处理作业BAT文件。代码演示1中展示了一个非常简单的例子,用以备份USERS表空间。其中扩展名.rman是非必要的,但有助于帮您理清文件用途。

代码演示1:备份USERS表空间的脚本

connect target /

connect catalog rman/secretpass@rmancat

run {

allocate channel c1 type disk format ‘/orabak/%U’;

backup tablespace users;

}

你可以通过多种方式调用脚本,例如自RMAN提示行中:

RMAN> @backup_ts_users.rman

注意调用使用的符号@

您也可以在SHELL中调用脚本,例如:

rman @backup_ts_users.rman

这种方式十分有用,若不想使用@符号,用以下方式替代:

rman cmdfile=backup_ts_users.rman

注意CONNECT子句是放在backup_ts_users.rman命令文件中的,故再次无需提供用户名与密码,也保障了没有泄露密码的危险。

传递参数: backup_ts_users.rman命令文件运行地不错,但他太固定了。他将备份输出到某个特定目录且只备份一个表空间(USERS)。若你想要备份到另一处或其他表空间是,你需要创建新的脚本。

一个较好的策略是使用参数驱动的RMAN脚本。比起硬编码来,参数传递脚本灵活得多。代码演示2中展示了修改了的backup_ts_users .rman脚本。

代码演示2: 参数驱动脚本

connect target /

connect catalog rman/secretpass@rmancat

run {

allocate channel c1 type disk format ‘&1/%U’;

backup tablespace &2;

}

另一个SHELL脚本,名为backup_ts_generic.sh调用以上脚本,传递参数/tmp作为备份目录以及USERS为备份表空间对象。

$ORACLE_HOME/bin/rman <<EOF

@backup_ts_generic.rman “/tmp” USERS

EOF

你还可以使之更灵活,你可以将backup_ts_generic.sh第二行做以下修改:

@backup_ts_generic.rman “/tmp” $1

则您可以将参数传递给sh脚本,在以/tmp为固定备份目录的情况下。例如您要备份MYTS表空间:

backup_ts_generic.sh MYTS1

日志设置: 当你通过某种自动设置来运行RMAN脚本时,实际没有人在观察命令窗口,那我们如何找到RMAN的输出呢?这些输出日志使我们了解脚本的运行结果故之分重要,为了获取日志,我们可以使用log选项。

rman cmdfile=backup_ts_users.rman log=backup_ts_users.log

现在backup_ts_users.rman脚本的输出日志将会记录在名为backup_ts_users.log 的文件中而非出现在屏幕上。

存储脚本

虽然脚本文件可以满足大多数情况,但他们也有部分缺点。脚本文件总是存放在服务器上,这显得并不十分安全,因为只要有阅读该文件的权限就可以获取SYS等账号的密码。

解决方法就是使用RMAN的存储脚本,存储脚本保存在了Oracle恢复目录中,而非直接存放在服务器上。代码演示3展示了调用存储脚本的例子。

代码演示3

RMAN> run { execute script

backup_ts_users; }

C:\> rman

RMAN> connect target /

RMAN> connect catalog rman/secretpass@rmancat

RMAN> create script backup_ts_users

2> comment ‘Tablespace Users Backup’

3> {

4>      allocate channel c1 type disk format ‘c:\temp\%U’;

5>      backup tablespace users;

6> }

在代码演示3中存储脚本backup_ts_users仅在用户连接到目标数据库是可见。同时存储脚本也可以做到传递参数,如代码演示4

代码演示4:参数驱动的存储脚本:

RMAN> create script backup_ts_any

2> comment ‘Any Tablespace Backup’

3> {

4>      allocate channel c1 type disk format ‘c:\temp\%U’;

5>      backup tablespace &1;

Enter value for 1: users

users;

6> }

7>

调用参数驱动存储脚本时,我们需要使用USING 子句。如下例,使用存储脚本备份SYSTEM表空间:

run { execute script

backup_ts_any using ‘SYSTEM’; }

管理存储脚本:RMAN工具提供了管理存储脚本的必要方式。

可以用一下命令列出存储的脚本:

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

Scripts of Target Database ARUPLAP1

Script Name

Description

————

backup_ts_any

Any Tablespace Backup

backup_ts_users

Tablespace Users Backup

以上命令会列出脚本的本地和全局名。

若只需要列出全局名,可以使用以下命令:

RMAN> list global script names;

若要显示某个脚本的具体内容,例如back_ts_any,可以使用以下命令:

RMAN> print global script

backup_ts_level1_any;

删除一个存储脚本:

RMAN> delete global script

backup_ts_level1_any;

你也可以通过一个脚本文件创建存储脚本,如以下:

RMAN> create script backup_ts_users

from file ‘backup_ts_users.rman’;

当然你也可以通过存储脚本还原出一个平面脚本文件:

RMAN> print script backup_ts_users

to file ‘backup_ts_users.rman’;

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. RMAN – Sample Backup Scripts 10g
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.1 – Release: 10.1 to 10.2
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
    Information in this document applies to any platform.
    Goal
    Audience: Novice RMAN users.

    The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
    Solution
    RMAN – Sample Backup Scripts 10g

    • Backup up Whole Database Backups with RMAN
    • Backing up Individual Tablespaces with RMAN
    • Backing up Individual Datafiles and Datafile Copies with RMAN
    • Backing up Control Files with RMAN
    • Backing up Server Parameter Files with RMAN
    • Backing up Archived Redo Logs with RMAN
    • Backing up the Whole database including archivelogs
    =====================================================================================

    Making Whole Database Backups with RMAN

    You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:

    RMAN> backup database;
    In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.

    How to check if the RFA has been setup:

    SQL> show parameter recovery_file_dest

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string /recovery_area
    db_recovery_file_dest_size big integer 50G

    If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.

    Note 305648.1 What is a Flash Recovery Area and how to configure it ?

    If you wish to place your backup outside the FRA then following RMAN syntax may be used.

    RMAN> backup database format ‘/backups/PROD/df_t%t_s%s_p%p’;

    • Backing Up Individual Tablespaces with RMAN

    RMAN allows individual tablespaces to be backed up with the database in open or mount stage.

    RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;

    • Backing Up Individual Datafiles and Datafile Copies with RMAN

    The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.

    RMAN> backup datafile 2;

    RMAN> backup datafile 2 format ‘/backups/PROD/df_t%t_s%s_p%p’;

    RMAN> backup datafile 1,2,3,6,7,8;

    RMAN> backup datafile ‘/oradata/system01.dbf’;
    • Backing Up the current controlfile & Spfile

    The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.

    It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.

    If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as ‘autobackup of controlfile’.

    Refer to the manual for more information regarding this feature.

    RMAN> backup current controlfile;

    RMAN> backup current controlfile format ‘/backups/PROD/df_t%t_s%s_p%p’;

    RMAN> backup spfile;

    • Backing Up Archivelogs

    It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.

    RMAN> backup archivelog all;

    RMAN> backup archivelog from time ‘sysdate-30’ until time ‘sysdate-7’;

    RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format ‘/backups/PROD/%d_archive_%T_%u_s%s_p%p’;
    • Backing up the Whole database including archivelogs

    Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.

    RMAN> backup database plus archivelog delete input;

    RMAN> backup database plus archivelog delete input format ‘/backups/PROD/df_t%t_s%s_p%p’;

  2. RMAN – Sample Backup Scripts 10g
    Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.1 – Release: 10.1 to 10.2
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
    Information in this document applies to any platform.
    Goal
    Audience: Novice RMAN users.

    The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
    Solution
    RMAN – Sample Backup Scripts 10g

    • Backup up Whole Database Backups with RMAN
    • Backing up Individual Tablespaces with RMAN
    • Backing up Individual Datafiles and Datafile Copies with RMAN
    • Backing up Control Files with RMAN
    • Backing up Server Parameter Files with RMAN
    • Backing up Archived Redo Logs with RMAN
    • Backing up the Whole database including archivelogs
    =====================================================================================

    Making Whole Database Backups with RMAN

    You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:

    RMAN> backup database;
    In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.

    How to check if the RFA has been setup:

    SQL> show parameter recovery_file_dest

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string /recovery_area
    db_recovery_file_dest_size big integer 50G

    If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.

    Note 305648.1 What is a Flash Recovery Area and how to configure it ?

    If you wish to place your backup outside the FRA then following RMAN syntax may be used.

    RMAN> backup database format ‘/backups/PROD/df_t%t_s%s_p%p’;

    • Backing Up Individual Tablespaces with RMAN

    RMAN allows individual tablespaces to be backed up with the database in open or mount stage.

    RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;

    • Backing Up Individual Datafiles and Datafile Copies with RMAN

    The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.

    RMAN> backup datafile 2;

    RMAN> backup datafile 2 format ‘/backups/PROD/df_t%t_s%s_p%p’;

    RMAN> backup datafile 1,2,3,6,7,8;

    RMAN> backup datafile ‘/oradata/system01.dbf’;
    • Backing Up the current controlfile & Spfile

    The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.

    It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.

    If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as ‘autobackup of controlfile’.

    Refer to the manual for more information regarding this feature.

    RMAN> backup current controlfile;

    RMAN> backup current controlfile format ‘/backups/PROD/df_t%t_s%s_p%p’;

    RMAN> backup spfile;

    • Backing Up Archivelogs

    It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.

    RMAN> backup archivelog all;

    RMAN> backup archivelog from time ‘sysdate-30’ until time ‘sysdate-7’;

    RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format ‘/backups/PROD/%d_archive_%T_%u_s%s_p%p’;
    • Backing up the Whole database including archivelogs

    Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.

    RMAN> backup database plus archivelog delete input;

    RMAN> backup database plus archivelog delete input format ‘/backups/PROD/df_t%t_s%s_p%p’;

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569