Oracle Rman

最新推荐文章于 2024-05-20 07:30:00 发布

liuzhaomin

最新推荐文章于 2024-05-20 07:30:00 发布

阅读量187

点赞数

本文链接：https://blog.csdn.net/liuzhaomin/article/details/83850691

版权

Oracle9i,10g备份恢复文档 Rman

1 概述：

1.1 Oracle数据库备份类型、特点：

Oracle数据库备份主要有3种类型：冷备份、热备份、逻辑备份：

冷备份（Cold Backup）主要指在关闭数据库的状态下进行的数据库完全备份，备份内容包括所有数据文件、控制文件、联机日志文件、ini文件，当数据库被关闭时，被数据库使用的每一个文件都被备份下来。这些文件因而保持着数据关闭时的完整的映象。

热备份（Hot Backup）指在数据库处于运行状态下，对数据文件和控制文件进行备份，要使用热备份必须将数据库运行在(Archive Log)归档方式下，这涉及到将每个表空间设置为备份状态，然后备份其数据文件，最后将表空间恢复成正常的状态。数据库可以从这个备份中完全地恢复过来，也可以通过归档的重做日志回滚到前面时间的任一个点上。

逻辑备份（Export）是按数据库中某个表、某个用户或整个数据库来导出，并且支持全部、累计、增量三种方式。使用这种方法，数据库必须处于打开状态，而且如果数据库不是在restrict状态将不能保证导出数据的一致性，exp是以Oracle的内部格式保存为二进制文件。相反，利用Import则可将数据从二进制文件放回到Oracle数据库中。但是导出（EXPORT）备份机制不能提供时间点恢复，而且不能和归档重做日志文件一起使用。

1.2 Oracle数据库运行状态

要对Oracle数据库备份与恢复有清晰的认识，首先有必要对数据库的几种运行状态有充分的了解。Oracle数据库的运行状态主要分为3种

1） Nomount(非安装)Oracle只是读取ini文件中的配置信息，并初始化SGA区；

2） Mount(安装)Oracle除了需要读取ini文件还要读取控制文件，并从中获取有关数据库的物理结构等信息；

3） Open(打开)数据库要检查所有文件处于同一时间点，对错误进行恢复对未完成事务回滚，并最终可以允许用户访问。

2 数据库确定为归档模式

数据库使用归档模式来完成rman的备份：

检查当前数据库是否为归档模式如果不是则置为归档模式：

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /home/oracle/product/9.2.0.4/dbs/arch

Oldest online log sequence 7

Current log sequence 9

2.1 归档模式模式和自动归档：

需要将数据库启动到mount状态下操作：

SQL> startup mount;

ORACLE instance started.

……

Database mounted.

SQL>

SQL> alter database archivelog; //设置为归档

Database altered.

SQL> alter system archive log start; //启用自动归档,9i必须执行，10g不需要

System altered.

SQL>

SQL> archive log list //检查是否为归档

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /home/oracle/product/9.2.0.4/dbs/arch

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

注意，如果由于某些情况需要将数据库切换为非归档的模式则需要使用以下：

SQL> alter database noarchivelog;--->must mount status,do it 注意：如果数据库已经开始闪回，需要先关闭闪回才能切换为非归档模式. alter database flashback off; 在alter database noarchivelog

Database altered.

SQL>

2.2 初始化文件修改

修改初试化参数：

log_archive_start=true //启动自动归档，9i必须执行，10g不需要

log_archive_format=ARC%T%S%r.arc //归档文件格式,9I: ARC%T%S.arc

log_archive_dest_n=/home/oracle/arch //归档存放路径,10g:归档路径默认在闪回区

_allow_resetlogs_corruption=true

关于//归档存放路径一些注意事项：

总结：

1.db_recovery_file_dest和log_archive_dest同时设置时，会在这两个目录中同时产生归档日志

2.db_recovery_file_dest和log_archive_dest_n同时设置时，只有log_archive_dest_n的目录产生日志。

3.db_recovery_file_dest和log_archive_dest_n不是同时设置

4.LOG_ARCHIVE_FORMAT的设置只对log_archive_dest和log_archive_dest_n生效，10G增加了%r参数，记录resetlogs的ID。

sql>alter system set log_archive_dest_1="location=/disk1/app/oracle/arch/";

3 确定rman的环境及配置：

数据库使用归档模式来完成rman的备份：

3.1 创建恢复目录步骤：

创建恢复目录的目的：一是可以存储脚本，二是可以记载较长时间的备份恢复操作

1）建立rman表空间：

CREATE TABLESPACE "RMAN"

LOGGING

DATAFILE '/home/oracle/oradata/rac1/rman.dbf' SIZE 512M EXTENT

MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

2）建立rman用户并授权：

CREATE USER "RMAN" PROFILE "DEFAULT"

IDENTIFIED BY rman DEFAULT TABLESPACE "RMAN"

TEMPORARY TABLESPACE "TEMP"

ACCOUNT UNLOCK;

GRANT RESOURCE,CONNECT,RECOVERY_CATALOG_OWNER TO RMAN;

ALTER USER RMAN DEFAULT ROLE ALL;

3）

[oracle@ce163 ~]$ rman catalog rman/rman

……

connected to recovery catalog database

recovery catalog is not installed

RMAN> create catalog;

recovery catalog created

RMAN>

3.2 连接rman并注册数据库到恢复目录

1）连接到rman：

[oracle@ce163 ~]$

rman target sys/oracle catalog rman/rman

……

connected to target database: RAC1 (DBID=505317296)

connected to recovery catalog database

RMAN>

2）注册数据库到恢复目录：

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

3.3 rman基本配置

设置默认的备份设备为磁盘：

RMAN>configure default device type to disk; //---->修改为磁带 CONFIGURE DEFAULT DEVICE TYPE to sbt;

设置备份的并行级别，通道数（设置备份的并行度，使多个cpu同时工作，提高备份速度）：

RMAN>configure device type disk parallelism 2;//---->CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

打开控制文件与服务器参数文件的自动备份：

RMAN>configure controlfile autobackup on;

设置保存的时间为2天：

CONFIGURE RETENTION POLICY TO REDUNDANCY 2 (如果设置了这个参数就只好保留2份备份集）

RMAN>

configure controlfile autobackup format for device type disk to '%F';

设置备份的文件格式，只适用于磁盘设备

一次备份到多个路径下，启动多通道实现：

设置控制文件与服务器参数文件自动备份的文件格式：

RMAN> configure channel 1 device type disk

format '/home1/rman/backup_%U';

RMAN>configure channel 2 device type disk format

'/home1/rman/backup_%U';

RMAN>CONFIGURE MAXSETSIZE TO UNLIMITED;

检查配置后的所有设置：

RMAN> show all;

4. 备份格式：

备份文件自定义格式：

%d 数据库名称

%c 备份片的拷贝数

%D 位于该月的第几天

%M 位于该年的第几月

%F 一个基于dbid唯一的名称，这个格式的形式比较全面 c-iiiiiiiiii-yyyymmdd-qq iiiiiiiiii:dbid yyyymmdd为日期 qq：1－256的序列号

%n 数据库名称，向右补足到最大8个字符

%U 一个唯一的文件名，代表%u_%p_%c

%u 一个八个字符的名称代表备份集于创建时间

%p 该备份集的备份片号，从1开始到创建的文件数

%t 备份集的时间戳

%T yyyymmdd

%U %d %F

5. 维护RMAN

RMAN的维护工作主要是检查RMAN的备份信息，对RMAN信息进行同步等。

5.1 查看基本信息：

查看数据库物理结构

RMAN> report schema;

Report of database schema

显示rman当前配置：

RMAN> show all

显示对应物信息：

RMAN> list incarnation;

使用目标数据库控制文件替代恢复目录

数据库原型列表

DB 关键字 Inc 关键字 DB 名 DB ID STATUS 重置 SCN 重置时间

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

1 1 SOLO 1494945426 PARENT 1 30-8月 -05

2 2 SOLO 1494945426 PARENT 534907 04-11月-09

3 3 SOLO 1494945426 CURRENT 561474 04-11月-09

DB_NAME: 数据库名称(v$database.name)

DB ID: DB ID(v$database.dbid)

CUR 是否为当前，如果数据库有被多次用RESETLOGS打开的话，这里会有多条记录

RESET SCN：RESETLOGS的SCN

RESET TIME： RESETLOGS的TIME

5.2 查看备份信息：

列出已经过时的备份：

RMAN>report obsolete;

如果删除过期的备份则使用下面delete：

RMAN>delete noprompt obsolete;

列出过期（失效）备份：

RMAN>list expired backup;

列出不可恢复的数据备份：

RMAN> report unrecoverable;

列出备份信息：

RMAN>list backup;

列出归档日志备份信息：

RMAN>list archivelog all;

RMAN>list backup of database;

RMAN>list backup of tablespace table_name;

RMAN>list backup of controlfile;

RMAN>list backup of spfile;

删除过时的备份信息：

RMAN> allocate channel for maintenance type disk;

RMAN> change backupset id delete;

RMAN> release channel;

5.3 Rman信息同步：

当手工删除了备份文件或归档文件时，需要把RMAN CATALOG数据库的信息与手工删除的信息同步；

如果目标数据库物理对象发生了变化，如添加了一个数据文件，需要用如下命令同步：

RMAN>resync catalog;

如果目标数据库reset了数据库，需要用如下命令同步：

RMAN>reset database;

当手工删除了数据库的归档文件后，要执行以下脚本（同步归档文件的脚本）同步：

RMAN>allocate channel for maintenance type disk;

RMAN>change archivelog all crosscheck;

RMAN>delete noprompt expired archivelog all;

RMAN>release channel;

当手工删除了数据库的RMAN备份后，要执行以下脚本（同步RMAN备份文件的脚本）来同步：

RMAN>allocate channel for maintenance type disk;

RMAN>crosscheck backup;

RMAN>delete expired backup;

RMAN>release channel;

5.4 校验备份信息：

RMAN>crosscheck backup;

RMAN>crosscheck backup of database;

RMAN>crosscheck backup of tablespace system;

RMAN>crosscheck backup of controlfile;

RMAN>crosscheck backup of spfile;

RMAN>crosscheck copy;

备份策略：

fullback.sh

export ORACLE_SID=up

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=/opt/oracle/product/10g

rq=` date +"20%y%m%d%H%M%S" `

/opt/oracle/product/10g/bin/rman nocatalog target rman/rman log=/oracle/rman /backup_log/backupall_$rq.log<<EOF

run

{

allocate channel ch1 device type disk ;

allocate channel ch2 device type disk ;

crosscheck backup;

crosscheck archivelog all;

sql 'alter system archive log current';

backup database format '/opt/oracle/bak/db_%d_%T_%U';

sql 'alter system archive log current';

backup archivelog all format '/opt/oracle/bak/arch_%t_%s' delete all input;

backup format '/opt/oracle/bak/con_%s_%p' current controlfile;

crosscheck backup;

crosscheck archivelog all;

delete noprompt expired backup;

delete noprompt obsolete;

delete noprompt backup of database completed before 'sysdate -15';

delete noprompt archivelog all;

delete noprompt backup of archivelog all completed before 'sysdate -15';

release channel ch1;

release channel ch2;

}

EOF

backuparchive.sh

export ORACLE_SID=up

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=/opt/oracle/product/10g

rq=` date +"20%y%m%d%H%M%S" `

/opt/oracle/product/10g/bin/rman nocatalog target rman/rman log=/oracle/rman /backup_log/backupall_$rq.log<<EOF

run

{

allocate channel ch1 device type disk ;

allocate channel ch2 device type disk ;

sql 'alter system archive log current';

backup archivelog all format '/opt/oracle/bak/arch_%t_%s_%U' delete all input;

crosscheck archivelog all;

release channel ch1;

release channel ch2;

}

EOF

crontab -l

0 0 * * 0 sh /oracle/rman/backup_script_bak/backupall.sh 2>&1

0,5,10,15,20,25,30,35,40,45,50,55 0,4,8,18,21,22 * * * sh /oracle/rman/backup_script_bak/backuparchive.sh 2>&1

查看rman备份数据文件量的sql，留此备忘。

比较某2天的数据文件的备份量

select file#,recid,to_char(creation_time,'yyyymmdd hh24:mi:ss'),datafile_blocks,blocks,to_char(completion_time,'yyyymmdd hh24:mi:ss') from v$backup_datafile where trunc(completion_time)=to_date('20060126','yyyymmdd') or trunc(completion_time)=trunc(sysdate) order by file#,recid;

查看某天数据文件总的备份量

select sum(blocks) from v$backup_datafile where trunc(completion_time)=to_date('20090430','yyyymmdd');

数据文件已使用块大小:

select sum1-sum2 from (select sum(bytes/1024/1024) sum1 from dba_data_files),(select sum(bytes/1024/1024) sum2

from dba_free_space);

也就是rman备份的这些块，rman只备份使用过的块,最后备份出来的文件大小要小于这个数字，备份出来的肯定要小

就像是自己的硬盘，并不是每个数据块都填满了数据，但是也按一个块算，备份出来全是数据，没有碎片了，当然要小

dispatchers此参数是rac下，控制并发

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

只删除数据库的记录，逻辑上的

rman中物理删除归档路径下的日至，通过RMAN> backup archivelog all delete input 释放空间

注意这里，如果是不加all,那么只会删除备份走的日至，如果加all,

会删除所有存在于 v$archived_log;试图里的日至

某移动备份例子：

增量备份归档日志，并自动删除2天前的已备份的日志

run{

allocate channel dev1 type disk;

backup format 'D:\rman_backup\log_d%d_t%t_s%s_p%p'

archivelog all not backed up;

delete NOPROMPT archivelog until time "sysdate-3";

release channel dev1;

}

注意：rman中restore还原是从备份集中把文件还原到本来的位置，物理上的操作

recover恢复是逻辑上同步数据库

rman备份内容记录在视图 v$backup_set;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:/c_%F';

路径必须必须指定一个 "%F" 格式说明符

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFORCL.ORA';

在备份期间，将产生一个控制文件的快照，用于控制文件的读一致

备份检查

我们可以通过Validate命令来检查是否能备份，如数据文件是否存在，是否存在坏块不能被备份，如：

BACKUP VALIDATE DATABASE;

BACKUP VALIDATE DATABASE ARCHIVELOG ALL

Crosscheck命令

检查磁盘或磁带上的备份或拷贝是否正确，并更新备份或者拷贝的状态

如果不正确，将标记为expired（过期）

Crosscheck backup;

Crosscheck archivelog all;

Delete [noprompt] expired backup命令删除过期备份

也可以用List来查看相应的报告

LIST EXPIRED BACKUP;

LIST EXPIRED BACKUP SUMMARY;

List命令一般用来查看备份与拷贝信息，如

查看备份信息

List backup

查看备份汇总信息

List backup summary

查看文件拷贝的信息

List copy

查看具体的备份信息

List backup of datafile ‘file name’

list incarnation of database;

报告数据库的所有能备份数据文件对象

Report schema

报告过期了的数据文件或者不可用的备份与拷贝

Report obsolete

RMAN> report need backup;

RMAN 保留策略将应用于该命令

将 RMAN 保留策略设置为冗余 10(show all)

文件冗余备份少于10个

文件 #bkps 名称

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

1 3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2 3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3 2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

4 2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

测试：

在磁盘中将备份文件删除几个后

crosscheck backup;

交叉校验备份段: 找到为 'EXPIRED'

备份段 handle=C:\LIFE\BACKUP19JDFVLB recid=10 stamp=651689646

交叉校验备份段: 找到为 'EXPIRED'

备份段 handle=C:\LIFE\BACKUP1AJDFVLC recid=11 stamp=651689660

交叉校验备份段: 找到为 'EXPIRED'

备份段 handle=C:\LIFE\BACKUP18JDFVLB recid=12 stamp=651689646

交叉校验备份段: 找到为 'EXPIRED'

备份段 handle=C:\LIFE\BACKUP17JDFVLB recid=13 stamp=651689643

交叉校验备份段: 找到为 'EXPIRED'

备份段 handle=C:\C_C-1178867333-20080410-00 recid=14 stamp=651689715

已交叉检验的 5 对象

被删除的文件被标记为 'EXPIRED' 失效了

RMAN> list expired backup;

备份集列表

===================

BS 关键字类型 LV 大小设备类型经过时间完成时间

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

34 Full 17.85M DISK 00:00:21 10-4月 -08

BP 关键字: 10 状态: EXPIRED 已压缩: NO 标记: TAG20080410T165402

段名:C:\LIFE\BACKUP19JDFVLB

备份集 34 中的数据文件列表

文件 LV 类型 Ckp SCN Ckp 时间名称

将列出失效的对象

RMAN> delete expired backup; 删除这些失效的对象

可以大致的监控到RMAN备份进行的程度

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK;

对于异常结束了的备份，很多人可能不想再重新开始备份了吧，特别是备份到90%以

上，因为异常原因终止了该备份，那怎么办呢？RMAN 提供一个重新开始备份的方法，通

过简单的命令，你就可以只备份那不到10%的数据了。

RMAN> BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-14'

DATABASE PLUS ARCHIVELOG;

使用 CLEAR 命令将任何永久性设置重新设置为默认值：

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE MAXSETSIZE CLEAR;

RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;

RMAN> CONFIGURE RETENTION POLICY clear;

如果rman部署后，手动rm了系统中的oracle归档日志文件，并且这些归档文件并没有被rman备份走，那么rman备份时候，还是要找那些物理上被rm的归档文件.

这时候需要crosscheck archivelog all; delete archivelog all;

section 3

数据库备份与恢复

--归档模式：

mount-->alter database archivelog;

对recovery的OMF参数没有设置。

catalog database: 用repos去备份pod.

sqlplus sys/oracle@POD as sysdba

SQL> create tablespace cata datafile '/u01/oracle/oradata/POD/cata.dbf' size 120m;

SQL> create user ct1 identified by oracle default tablespace cata;

SQL> grant connect,resource,recovery_catalog_owner to ct1;

rman target sys/oracle@pod catalog ct1/oracle@repos

RMAN> create catalog tablespace cata;

RMAN> register database;

每次连接到catalog建议都做,

RMAN> report schema;

或

RMAN> resync catalog;

---可能要求修改的参数

RMAN> show all;

--备份策略

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

--自动备份/快照

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/10g/dbs/cf_snap.f';

--并行，压缩备份

RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

--加密备份

RMAN>CONFIGURE ENCRYPTION FOR DATABASE ON;

RMAN>CONFIGURE ENCRYPTION ALGORITHM 'AES128';

SQL> ho mkdir /u01/app/oracle/admin/orcl/wallet

SQL> echo "WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl/wallet)))">>/u01/app/oracle/10g/network/admin/sqlnet.ora

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle";

SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "oracle";

注意: 在创建WALLET时必须保证$ORACLE_HOME/admin/$ORACLE_SID下有个空目录叫wallet,否则将报错.

或者配置sqlnet.ora文件,添加如下行:

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/orcl/wallet)))

--多通道，多路经

RMAN>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u01/oracle/DB_%U';

RMAN>CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/home/oracle/DB_%U';

--默认通道

RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;

--优化备份

RMAN>CONFIGURE BACKUP OPTIMIZATION ON;

RMAN>backup database plus archivelog;

RMAN>backup full databade plus archivelog delete input;

--注意控制文件丢失的恢复 [手工建库控制文件是多路复用的]

SQL>ho cp <存在的> <丢失的>

SQL>startup mount

SQL>alter database open;

--注意数据文件丢失的恢复，特别是system

RMAN>shutdown immediate

RMAN>startup mount

RMAN>restore database;

RMAN>recover database;

--注意日志文件丢失的恢复，看alert日志找到丢失的日志，解决丢失的日志。[日志是多工分开存放的]

SQL>alter database drop logfile group <>;

SQL>alter database add logfile group <> ('</dir/logfilename>','/dir/logfilename') size 100m;

SQL>alter database open resetlogs;

RMAN>backup database;

--闪回数据库

mount--->alter database flashback on;

常规还原与恢复

RMAN 的整个恢复过程可以分为还原（restore）与恢复（recover），他们在含义上是有很大差别的，一个是指物理意义的文件的还原与拷贝，一个是指数据库一致性的恢复，所以，正确的理解这两个概念，有助于正确的恢复数据库。

对于RMAN 的备份，还原操作只能是在用 RMAN 或 RMAN 包来做了，对于恢复操作

则是很灵活的了，除了 RMAN，也可以在 SQLPLUS 中完成。还原与恢复一个数据库，可

以用如下两个简单的命令完成

RMAN>restore database;

RMAN>recover database;

eg:

1.全库做恢复

RMAN> backup database；

SQL> create table ffrrsolo (a int);

SQL> shutdown immediate

SQL> startup mount;

C:\Documents and Settings\Administrator>rman target sys/oracle

RMAN> restore database；

RMAN> recover database;

SQL> alter database open;

SQL> desc ffrrsolo

名称是否为空? 类型

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

A NUMBER(38)

2.恢复一个表空间，或者恢复一个数据文件，相对比较恢复数据库可能花费更少的时间

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

RMAN> sql 'alter tablespace users offline immediate';

C:\Documents and Settings\Administrator>rman target sys/oracle

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> sql 'alter tablespace users online';

3.块级别的恢复

块的错误信息保存在 V$DATABASE_BLOCK_CORRUPTION

可以用如下的命令来恢复坏块。

BLOCKRECOVER datafile 2 BLOCK 12, 13 datafile 7 BLOCK 5, 98, 99 datafile 9 BLOCK 19;

4.还原检查与恢复测试

RMAN> RESTORE DATABASE VALIDATE;

RMAN> VALIDATE BACKUPSET 218;

课上测试：

一，A Datafile is Lost

Problem: A datafile is lost, the problem tablespace has more than one datafile.

Backup: RMAN online full database backup

eg:

1,RMAN> backup database;

2,SQL> select file#,name from v$datafile;

rm 一个.dbf

3,RMAN> run{

2> allocate channel c1 type disk;

3> sql 'alter database datafile 4 offline';

4> restore datafile 4;

5> recover datafile 4;

6> sql 'alter database datafile 4 online';

7> }

sql> alter database open;

4,SQL> select status from v$datafile where file# =4;

STATUS

-------

ONLINE

二，A Tablespace is Lost

Problem: The users tablespace has been moved to another disk.(in our case, it’s oradata1 directory). Afterward the users tablespace is lost due to disk crash, and the disk is not repaired yet. Database is closed.

Backup: RMAN online full database backup

rm .dbf

1,SQL> select t.name,d.name,r.error from v$tablespace t,v$datafile d,v$recover_file r where t.ts#=d.ts# and r.file#=d.file#;

ts_name NAME ERROR ------------ ----------------------------------- -------------------------

USERS …/oradata1/users1.dbf FILE NOT FOUND

USERS …/oradata1/users2.dbf FILE NOT FOUND

SQL> shutdown immediate

RMAN> run{

startup mount;

allocate channel c1 type disk;

sql "alter database datafile 4 offline";

sql "alter database open";

set newname for datafile 4 to '/u01/app/oracle/oradata/user1.dbf';

restore tablespace users;

switch datafile 4;

recover tablespace users;

sql "alter tablespace users online";}2> 3> 4> 5> 6> 7> 8> 9> 10>

RMAN> report schema;

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/up/system01.dbf

/u01/app/oracle/oradata/up/undotbs01.dbf

/u01/app/oracle/oradata/up/sysaux01.dbf

/u01/app/oracle/oradata/user1.dbf

/u01/app/oracle/oradata/up/example01.dbf

/u01/app/k31.dbf

6 rows selected.

select status from dba_tablespaces where tablespace_name='USERS';SQL>

STATUS

---------

ONLINE

RMAN> run

2> {

3> shutdown immediate;

4> startup force mount;

5> set newname for datafile 1 to 'd:\DEMO\SYSTEM01.DBF';

6> set newname for datafile 2 to 'd:\DEMO\UNDOTBS01.DBF';

7> set newname for datafile 3 to 'd:\DEMO\SYSAUX01.DBF';

8> set newname for datafile 4 to 'd:\DEMO\USERS01.DBF';

9> restore database;

10> switch datafile all;

11> recover database;

12> sql 'alter database open';

13> }

三，Loss of System Tablespace

SQL> SQL> startup

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220820 bytes

Variable Size 218107692 bytes

Database Buffers 381681664 bytes

Redo Buffers 2969600 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/up/system01.dbf'

SQL> select t.name,d.name,r.error from v$tablespace t,v$datafile d,v$recover_file r where t.ts#=d.ts# and r.file#=d.file#;

NAME

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

NAME

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

ERROR

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

SYSTEM

/opt/oracle/oradata/up/system01.dbf

FILE NOT FOUND

[oracle@local u01]$ rman target /

RMAN> run{

2> allocate channel c1 type disk;

3> restore datafile 1;

4> recover datafile 1;

5> sql "alter database open";}

SQL> startup force

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL> select t.name,d.name,r.error from v$tablespace t,v$datafile d,v$recover_file r where t.ts#=d.ts# and r.file#=d.file#;

no rows selected

四，All Controlfiles Are Lost

Problem : All controlfiles have been lost. And the database is closed now. The database has a read-only tablespace.

RMAN backup: RMAN online full database backup.

注意：当把控制文件的内容删除后，数据库无法mount,show all 自动备份控制文件会关闭

恢复后，也跟着on

SQL> select open_mode from v$database;

select open_mode from v$database

ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/up/control01.ctl'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> grant recovery_catalog_owner to scott;

Grant succeeded.

[oracle@local u01]$ rman catalog scott/tiger

RMAN> create catalog;

[oracle@local u01]$ rman target / catalog scott/tiger

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 15 11:52:16 2009

connected to target database: UP (DBID=2257039312)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

[oracle@local u01]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 15 12:08:13 2009

connected to target database: up (not mounted)

RMAN> run {

2> allocate channel d1 type disk;

3> restore controlfile from '/u01/app/oracle/flash_recovery_area/UP/autobackup/2009_09_15/o1_mf_s_697637096_5by4m9m3_.bkp';

4> alter database mount;

5> recover database;

6> alter database open resetlogs;}

SQL> startup force

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220820 bytes

Variable Size 226496300 bytes

Database Buffers 373293056 bytes

Redo Buffers 2969600 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

五，Loss of All Datafiles, One Has No Backup

Problem: All datafiles are lost. The database has a read-only tablespace.

RMAN backup: The latest RMAN online full database backup does not include a new datafiles. However we have all archived logs since this new datafile has been created.

SQL> select file#,name from v$datafile;

RMAN> backup database;

SQL> create tablespace lijh datafile 'C:\ORADATA\SOLO\lijh.dbf' size 20m;

mv * /u01/app/oracle/oradata(not controlfile)

SQL> startup force

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220820 bytes

Variable Size 230690604 bytes

Database Buffers 369098752 bytes

Redo Buffers 2969600 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/up/system01.dbf'

RMAN> run {

2> startup mount;

3> allocate channel d1 type disk;

4> restore datafile 1,2,3,4;

5> sql "alter database create datafile ''C:\ORADATA\SOLO\LIJH.DBF'' as ''C:\ORADATA\SOLO\LIJH.DBF''";

6> recover database;

7> alter database open;}

SQL> conn sys as sysdba

输入口令:

已连接。

SQL> select file#,name from v$datafile;

六.不完全恢复：

6.1

RMAN> startup nomount

Oracle 实例已启动

系统全局区域总计 289406976 字节

Fixed Size 1248576 字节

Variable Size 96469696 字节

Database Buffers 184549376 字节

Redo Buffers 7139328 字节

RMAN> restore controlfile from autobackup;

启动 restore 于 2009-11-04 18:36:00

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=157 devtype=DISK

恢复区域目标: C:/flash_recovery_area

用于搜索的数据库名 (或数据库的唯一名称): SOLO

通道 ORA_DISK_1: 在恢复区域中找到自动备份

通道 ORA_DISK_1: 已找到的自动备份: C:\FLASH_RECOVERY_AREA\SOLO\AUT

1_04\O1_MF_S_702066846_5H2P50SW_.BKP

通道 ORA_DISK_1: 从自动备份复原控制文件已完成

输出文件名=C:\ORADATA\SOLO\CONTROL01.CTL

输出文件名=C:\ORADATA\SOLO\CONTROL02.CTL

输出文件名=C:\ORADATA\SOLO\CONTROL03.CTL

完成 restore 于 2009-11-04 18:36:14

RMAN> alter database mount;

RMAN> restore database;

启动 restore 于 2009-11-04 18:37:16

启动 implicit crosscheck backup 于 2009-11-04 18:37:16

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=156 devtype=DISK

已交叉检验的 4 对象

完成 implicit crosscheck backup 于 2009-11-04 18:37:19

启动 implicit crosscheck copy 于 2009-11-04 18:37:19

使用通道 ORA_DISK_1

完成 implicit crosscheck copy 于 2009-11-04 18:37:19

搜索恢复区域中的所有文件

[oracle@stat ~]$ rman target /

RMAN> sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss'' ";

sql 语句: alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss''

RMAN> recover database until time ' 2009.11.04 18:40:00';

RMAN> alter database open resetlogs;

6.2 不完全恢复在 RMAN 中还可以用基于日志的恢复

RMAN> RUN {

2> SET UNTIL SEQUENCE 120 THREAD 1;

3> ALTER DATABASE MOUNT;

4> RESTORE DATABASE;

5> RECOVER DATABASE;

6> ALTER DATABASE OPEN RESESTLOGS;

7> }

run{

sql "alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss'' ";

set until time '2010.04.08 15:38:00';

restore database;

recover database;

}

恢复一张表：

1.source :

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

[oracle@db61 OCP]$ rman target /

RMAN> backup format '/opt/oracle/bak/full_%U' database plus archivelog;

2.source:

SQL> drop table SALGRADE;

3.source:

total 578892

-rw-r----- 1 oracle oinstall 29681152 Apr 11 15:09 full_05las4u0_1_1

-rw-r----- 1 oracle oinstall 548200448 Apr 11 15:10 full_06las4u2_1_1

-rw-r----- 1 oracle oinstall 7143424 Apr 11 15:10 full_07las4v5_1_1

-rw-r----- 1 oracle oinstall 45056 Apr 11 15:10 full_08las4v8_1_1

[oracle@db61 bak]$ pwd

/opt/oracle/bak

[oracle@db61 bak]$ scp * oracle@192.168.20.193:/opt/oracle/bak/

oracle@192.168.20.193's password:

detination:

建立该路径方自动控制文件备份

/opt/oracle/flash_recovery_area/OCP/autobackup/2010_04_11

[oracle@db61 2010_04_11]$ scp * oracle@192.168.20.193:/opt/oracle/flash_recovery_area/OCP/autobackup/2010_04_11/

oracle@192.168.20.193's password:

o1_mf_s_716053113_5w2z3sh3_.bkp 100% 6976KB 6.8MB/s 00:01

4.destination:

scp source's pfile ;

[oracle@sam 2010_04_11]$ export ORACLE_SID=ocp

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initocp.ora';

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1268896 bytes

Variable Size 163578720 bytes

Database Buffers 436207616 bytes

Redo Buffers 2924544 bytes

5.[oracle@sam 2010_04_11]$ rman target /

RMAN> restore controlfile from '/opt/oracle/flash_recovery_area/OCP/autobackup/2010_04_11/o1_mf_s_716053113_5w2z3sh3_.bkp';

Starting restore at 11-APR-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output filename=/opt/oracle/oradata/ocp/control01.ctl

output filename=/opt/oracle/oradata/ocp/control02.ctl

output filename=/opt/oracle/oradata/ocp/control03.ctl

Finished restore at 11-APR-10

RMAN> sql 'alter database mount';

RMAN> restore database;

Starting restore at 11-APR-10

Starting implicit crosscheck backup at 11-APR-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 11-APR-10

Starting implicit crosscheck copy at 11-APR-10

using channel ORA_DISK_1

Finished implicit crosscheck copy at 11-APR-10

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /opt/oracle/flash_recovery_area/OCP/backupset/2010_04_11/bk_04las3fb_1_1

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/oracle/oradata/ocp/system01.dbf

restoring datafile 00002 to /opt/oracle/oradata/ocp/undotbs01.dbf

restoring datafile 00003 to /opt/oracle/oradata/ocp/sysaux01.dbf

restoring datafile 00004 to /opt/oracle/oradata/ocp/users01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/OCP/backupset/2010_04_11/o1_mf_nnndf_TAG20100411T142835_5w2v0mpt_.bkp

RMAN> sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";

sql statement: alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''

RMAN> recover database until time '2010-04-11 15:45:00';

Starting recover at 11-APR-10

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=4

channel ORA_DISK_1: reading from backup piece /opt/oracle/bak/full_08las4v8_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/bak/full_08las4v8_1_1 tag=TAG20100411T151032

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

archive log filename=/opt/oracle/flash_recovery_area/OCP/archivelog/2010_04_11/o1_mf_1_4_5w2xyl1w_.arc thread=1 sequence=4

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/opt/oracle/oradata/ocp/system01.dbf'

channel default: deleting archive log(s)

archive log filename=/opt/oracle/flash_recovery_area/OCP/archivelog/2010_04_11/o1_mf_1_4_5w2xyl1w_.arc recid=1 stamp=716051922

media recovery complete, elapsed time: 00:00:00

Finished recover at 11-APR-10

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

SQL> select * from scott.emp;

<基于时间点的不完全恢复>

基于时间点的不完全恢复：

测试一：

RMAN> set dbid=1494945426 --------- (实例启动情况下使用)

正在执行命令: SET DBID

RMAN> startup nomount;

Oracle 实例已启动

系统全局区域总计 289406976 字节

Fixed Size 1248576 字节

Variable Size 113246912 字节

Database Buffers 167772160 字节

Redo Buffers 7139328 字节

RMAN> restore controlfile from autobackup; (如果没有configure controlfile autobackup on,那么就from 其它控制文件备份）

启动 restore 于 09-4月 -10

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=157 devtype=DISK

恢复区域目标: C:/flash_recovery_area

用于搜索的数据库名 (或数据库的唯一名称): SOLO

通道 ORA_DISK_1: 在恢复区域中找到自动备份

通道 ORA_DISK_1: 已找到的自动备份: C:\FLASH_RECOVERY_AREA\SOLO\AUTOBACKUP\2010_0

4_09\O1_MF_S_715886578_5VXWHQRF_.BKP

通道 ORA_DISK_1: 从自动备份复原控制文件已完成

输出文件名=C:\ORADATA\SOLO\CONTROL01.CTL

输出文件名=C:\ORADATA\SOLO\CONTROL02.CTL

输出文件名=C:\ORADATA\SOLO\CONTROL03.CTL

完成 restore 于 09-4月 -10

RMAN> alter database mount;

RMAN> sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ";

sql 语句: alter session set nls_date_format=''yyyy.mm.dd hh24:mi:ss''

RMAN> recover database until time '2010-04-09 23:10:00';

SQL> alter database open resetlogs;

注意: resetlogs 后做一次全备份，之前备份无效

测试二：

1.ho date

2.SQL> drop user scott cascade;----删除两个用户及相关表后

3.rman>shutdown immediate

4.rman>startup mount

RMAN> sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ";

RMAN> restore database;

RMAN> recover database until time '2010-04-09 23:30:00';

RMAN> alter database open resetlogs;

rman>list incarnation;

注意: resetlogs 后做一次全备份，之前备份无效

测试三：

1.ho date

2.drop table emp;

drop table dept;

3.rman>shutdown immediate

rman >startup mount

4.RMAN> run {

4> set until time='2010-04-10:21:19:00';

5> restore database;

6> recover database;

9> sql 'alter database open resetlogs'

10> ;}

desc scott.emp;

desc scott.dept;

测试五：

rman> alter database mount;

rman>run{

set until SEQUENCE 120 THREAD 1;

RESTORE DATABASE;

RECOVER DATABASE; # recovers through log 119

ALTER DATABASE OPEN RESESTLOGS;

}

<常见错误>

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of show command at 08/10/2007 09:36:39

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of full resync command on default channel at 08/10/2007 09:36:39

ORA-01580: error creating control backup file /data1/app/oracle/admin/SHFMSPRD/snapctl/

ORA-27037: unable to obtain file status

SVR4 Error: 20: Not a directory

Additional information: 6

方法一：

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME clear；

RMAN 配置参数已成功重置为默认值

方法二：

Cause

By default snapshot controlfile name is '$ORACLE/HOME/dbs/snapcf_$ORACLE_SID.f'.

Instead of snapshot controlfile name only the directory is given.

Example :

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/ora10g/dbs';

Note: File name is not given, only the location is specified in the configure snapshot controlfile name.

Solution

Connect to RMAN with nocatalog option and then set the snapshot controlfile to the proper location

and name.

Example :

$ rman target / nocatalog

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/product/ora10g/dbs/snapcf_ora10g.f';

Once the snapshot controlfile name is set properly; the backups/maintenance can be done.

How to Move RMAN Catalog To A Different Database [ID 351918.1]

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

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6

Information in this document applies to any platform.

Oracle Database.

Goal

How to move the RMAN catalog database from one server to another.

Solution

Available Options

1. You can take a cold backup of the catalog database and move it to the different server.

2. Export and Import the RMAN Schema (catalog owner). You can only import the catalog into a supported version of the Oracle database server. In general, you can import the catalog into a database of the same release or later.

Considerations When Moving Catalog Data using export and import:

You should only import the recover catalog into a schema that does not already contain a recovery catalog schema. In other words, the user who will own the imported recovery-catalog-schema should not already own a catalog schema.

For example, if user RMAN1 owns the recovery catalog on database CATDB1, and you want to export the recovery catalog on CATDB1 and import it into database CATDB2, then RMAN1 in CATDB2 should not already own a recovery catalog. You should either create a new recovery catalog owner (say RMAN2)on CATDB2, or drop the current user RMAN1 on CATDB2 and then re-create the user. You cannot merge a recovery catalog into an existing recovery catalog.

Exporting the recovery catalog

The basic steps for exporting a catalog from a primary database and importing the catalog into a secondary database are as follows:

Execute the Export utility at the operating system command line, making sure to do the following:

Connect as the owner of the recovery catalog

For example, if the owner of the catalog in database CATDB1 is RMAN1, you can issue the following at the UNIX command line to export the catalog to file cat.dmp:

% exp rman1/passwd@catdb1 FILE=cat.dmp OWNER=rman1

Examine the output to make sure you were successful, The expected end ouput should be,

Export terminated successfully without warnings.

Preparing the new catalog database

After choosing the recovery catalog database, following steps needs to be performed.

a. need to create necessary space,

b. then create the owner of the recovery catalog

c. and grant this user necessary privileges.

Assume the following background information for the instructions in the following sections:

A tablespace called TOOLS on the recovery catalog database stores the recovery catalog.

Note that, A temporary tablespace in the recovery catalog database.

To create the recovery catalog schema (RMAN2) in the recovery catalog database:

--- Create a user and schema for the recovery catalog. For example, enter:

sqlplus /nolog

CONNECT SYS/passwod

CREATE USER RMAN2 IDENTIFIED BY RMAN2

DEFAULT TABLESPACE TOOLS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON TOOLS;

--- Grant the RECOVERY_CATALOG_OWNER role and other desired privileges.

GRANT RECOVERY_CATALOG_OWNER TO RMAN2;

GRANT CONNECT, RESOURCE,CREATE VIEW TO RMAN2;

Importing the Recovery Catalog

Now use the Import utility to import it into CATDB2 database.

For example, assume the following:

The old owner of the catalog in database CATDB1 is RMAN1. The user in the new recovery catalog database CATDB2 is RMAN2. The file containing the export of the catalog is cat.dmp. The command is then as follows:

% imp USERID=rman2/passwd@catdb2 FILE=cat.dmp FROMUSER=rman1 TOUSER=rman2

Use the imported catalog data for restore and recovery of your target database.

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

Products

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

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

Keywords

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

FROMUSER; IMPORT AND EXPORT; RECOVERY CATALOG; TOUSER