DBA学习笔记（学习RMAN）

RMAN（Recover Manager）是oracle数据库软件自带的备份恢复工具。

1、连接本地数据库：

SET ORACLE_SID=huangke

RMAN Target / 启动操作系统身份认证方式 或 RMAN Target sys/change_on_install

如果需要执行日志：

RMAN Target / 启动操作系统身份认证方式 或 RMAN Target sys/change_on_install log X:/rman_log.txt

2、连接远程数据库：

RMAN Target sys/change_on_install@RemoteDB

3、推出RMAN

EXIT

4、启动、关闭数据库

执行启动和关闭数据库命令与SQL*Plus环境相同。

关闭数据库

shutdown immediate

启动数据库

startup

5、执行操作系统命令

RMAN>Host

c:/exit

RMAN>

6、执行SQL语句

RMAN>SQL 'alter system archive log all';

注意RMAN中的SQL命令不能执行Select语句，或者说执行了，但是不显示结果。

7、RMAN命令执行方式

1）单个执行

RMAN>Backup database;

或

指定备份目录

RMAN>Backup database format 'f:/oradata/backup/bak_%U';

<-------------------------------------{传说中冥王的分割线}------------------------------------->

RMAN format 参数说明

FORMAT = 'format_string'
Specifies a filename to use for the backup piece. Any name that is legal as a sequential filename on the platform is allowed, if each backup piece has a unique name. If backing up to disk, then any legal disk filename is allowed, provided it is unique. If you do not specify the FORMAT parameter, RMAN stores the backup pieces in a port-specific directory (?/dbs on UNIX).

You can specify up to four FORMAT strings. RMAN uses the second, third, and fourth values only when BACKUP COPIES, SET BACKUP COPIES, or CONFIGURE ... BACKUP COPIES is in effect. When choosing the format for each backup piece, RMAN uses the first format value for copy 1, the second format value for copy 2, and so on. If the number of format values exceeds the number of copies, then the extra formats are not used. If the number of format values is less than the number of copies, then RMAN reuses the format values, starting with the first one.

Specify FORMAT in any of these places, listed in order of precedence:

The backupSpec clause
The BACKUP command
The ALLOCATE CHANNEL command
The CONFIGURE CHANNEL command
If specified in more than one of these places, then RMAN searches for the FORMAT parameter in the order shown.

Note: The entire FORMAT string is processed in a port-specific manner by the target instance to derive the final backup piece name. The following substitution variables are available in FORMAT strings to aid in generating unique filenames. The formatting of this information varies by platform.

%c
Specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not duplex a backup, then this variable is 1 for backup sets and 0 for proxy copies. If one of these commands is enabled, then the variable shows the copy number. The maximum value for %c is 256.

%d
Specifies the name of the database.

%D
Specifies the current day of the month from the Gregorian calendar in format DD.

%F
Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name.

%M
Specifies the month in the Gregorian calendar in format MM.

%n
Specifies the name of the database, padded on the right with x characters to a total length of eight characters. For example, if the prod1 is the database name, then the padded name is prod1xxx.

%p
Specifies the piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.

Note: If you specify PROXY, then the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.

%s
Specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.

%t
Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.

%T
Specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.

%u
Specifies an 8-character name constituted by compressed representations of the backup set number and the time the backup set was created.

%U
Specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, then RMAN uses %U by default.

%Y
Specifies the year in this format: YYYY.

%%
Specifies the '%' character. For example, %%Y translates to the string %Y.

<-------------------------------------{传说中冥王的分割线}------------------------------------->

2）批处理

RMAN>Run{

...................

}

控制RMAN环境变量或操作CATALOG的操作不能在RUN块中执行。

3）运行脚本

C:/RMAN Target / @backup_db.rman

或

C:/RMAN Target / cmdfile=backup_db.rman

或

RMAN>@backup_db.rman

或

RMAN>Run {Execute Script backup_whole_db};

 

<-------------------------------------{传说中冥王的分割线}------------------------------------->

RMAN>backup database

出现错误：

在备份数据文件时：

ora-19602 无法在NOARCHIVELOG模式备份或恢复活动文件。

备份控制文件正常执行。

说明数据库处在open状态无法进行备份数据文件，所以需要执行以下操作：

RMAN>exit;

1.  关闭数据库，注意需要使用immediate选项 来源oracle86中文网
SQL> shutdown immediate;  
2.  再次重启一次数据库，以防止在上次关闭过程中出现问题 
SQL> startup ; 
SQL> shutdown immediate;  
3. 挂载数据库

 

注：在noarchivelog模式下，如果要想使用RMAN备份数据库，

则数据库不能处于open状态. 如果处于OPEN状态，则只能备份控制文件和spfile,
SQL> startup mount; 

SQL> exit;

 

RMAN>backup database

 

修改数据库为archivelog模式

SQL> startup mount; 

SQL>alter database archivelog;

SQL>alter system set log_archive_dest_1 = "location=C:/Oracle/oradata/oradb/archive"; 注释：修改归档文件存储位置。

SQL>archive log list;

SQL>alter database open;

<-------------------------------------{传说中冥王的分割线}------------------------------------->

4）查看创建的全库备份

RMAN>LIST backup of database;

 

 

 

 

 <-------------------------------------{传说中冥王的分割线}------------------------------------->

spfile被误删或有错误，而且无备份时，恢复方法：

找到%%ORACLE_SID%%/admin/huangke/pfile目录，把init.ora.XXOO文件拷贝到%%ORACLE_SID%%/db_1/database目录下，更名为inithuangke.ora。

SQL>create spfile from pfile;

<-------------------------------------{传说中冥王的分割线}------------------------------------->