oracle rman备份和恢复,oracleRMAN备份与恢复实验

========================RMAN数据库全库备份=========================================

[oracle@localhost ~]$ rman target /

RMAN> backup incremental level =0 database format='/home/oracle/db_bak/%u';

Starting backup at 17-MAR-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oradata/TEST/system01.dbf

input datafile fno=00003 name=/oradata/TEST/sysaux01.dbf

input datafile fno=00005 name=/oradata/TEST/t_recover.dbf

input datafile fno=00002 name=/oradata/TEST/undotbs01.dbf

input datafile fno=00004 name=/oradata/TEST/users01.dbf

channel ORA_DISK_1: finished piece 1 at 17-MAR-11

piece handle=/home/oracle/db_bak/02m7e1h1 tag=TAG20110317T053737 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 17-MAR-11

channel ORA_DISK_1: finished piece 1 at 17-MAR-11

piece handle=/home/oracle/db_bak/03m7e1kl tag=TAG20110317T053737 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 17-MAR-11

或者使用脚本

#!/bin/bash

source ~/.bash_profile

$ORACLE_HOME/bin/rman target / <

run {

allocate channel C1 type disk FORMAT '/home/oracle/db_bak/%u';

allocate channel C2 type disk FORMAT '/home/oracle/db_bak/%u';

backup incremental level=0 database format='/home/oracle/db_bak/%u';

}

exit;

EOF

========================RMAN数据库增量备份=========================================

上一步进行了数据库0级的全备，我们接下来进行数据库的增量备份

RMAN> backup incremental level =1 database format='/home/oracle/db_bak/%u';

Starting backup at 17-MAR-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oradata/TEST/system01.dbf

input datafile fno=00003 name=/oradata/TEST/sysaux01.dbf

input datafile fno=00005 name=/oradata/TEST/t_recover.dbf

input datafile fno=00002 name=/oradata/TEST/undotbs01.dbf

input datafile fno=00004 name=/oradata/TEST/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-MAR-11

channel ORA_DISK_1: finished piece 1 at 17-MAR-11

piece handle=/home/oracle/db_bak/04m7ed0a tag=TAG20110317T085329 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 17-MAR-11

channel ORA_DISK_1: finished piece 1 at 17-MAR-11

piece handle=/home/oracle/db_bak/05m7ed30 tag=TAG20110317T085329 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 17-MAR-11

========================数据库重建=========================================

将原来出现问题的数据库删除，重新创建一个数据库

①进入/oradata目录下，将所有的目录和文件删除

②将/etc下的oratab中的最后一句“TEST:/oracle/app/oracle/product/10.2.0/db 1:Y”删除，否则在接下来创建TEST数据库时会报错

③然后运行dbca进行数据库的创建

========================不完全恢复数据库=========================================

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

SQL>select sysdate from dual;

①将数据库设置为归档模式

$mkdir -p /home/oracle/archiver01

$mkdir -p /home/oracle/archiver02

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter system set log_archive_dest_1='location=/home/oracle/archive01' scope=both;

SQL> alter system set log_archive_dest_2='location=/home/oracle/archive02' scope=both;

SQL> show parameter archive;

SQL> alter database open;

②对数据库进行全备

$ rman target /

RMAN> backup incremental level =0 database format='/home/oracle/db_bak/%u';

③删除一个表用于接下来的恢复测试

SQL> drop table emp;

④使用日志挖掘，确定表被误删除的时间

$mkdir -p /home/oracle/logmin

SQL> alter system set utl_file_dir='/home/oracle/logmin' scope=spfile;

SQL> shutdown immediate

SQL> startup

SQL> execute dbms_logmnr_d.build('shwdict.ora','/home/oracle/logmin');

SQL> execute dbms_logmnr.add_logfile('/home/oracle/archive01/1_3_746014228.dbf',dbms_logmnr.new);

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmin/shwdict.ora');

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

SQL> select SCN,TIMESTAMP,SQL_REDO,SQL_UNDO from V$LOGMNR_CONTENTS where SQL_REDO like'%drop%';

记录下误删除表的时间2011-03-17 10:50:51

⑤进行RMAN恢复

SQL>shutdown immediate

SQL>startup mount

RMAN>run{

set until time "to_date('2011-03-17 10:50:50','yyyy-mm-dd hh24:mi:ss')";

restore database;

recover database;

alter database open resetlogs;

}

⑥重新备份全部的数据库

RMAN> delete noprompt backup;

RMAN> backup database format='/home/oracle/db_bak/%u';

========================参数文件、数据文件、控制文件和日志文件丢失后数据库恢复============================

注：在恢复数据库之前，要在RMAN里查看参数文件和控制文件(RMAN>list backup)的备份文件在备份目录是哪个。

①创建最简初始化参数文件

$echo "db_name=TEST">initnew.ora

②使用最简参数文件启动数据库到nomount状态

SQL> startup nomount pfile=/oracle/app/oracle/product/10.2.0/db_1/dbs/initnew.ora;

③恢复参数文件

重新连接到RMAN

RMAN> restore spfile from '/home/oracle/db_bak/04m7elfd';

④使用数据库参数文件启动到数据库nomount状态进行恢复控制文件

SQL> startup nomount force

RMAN> restore controlfile from '/home/oracle/db_bak/04m7elfd';

⑤启动数据库到mount状态进行数据库的恢复

RMAN> alter database mount;

RMAN> restore database;执行这一步会报错，因为日志文件都被删除所以无法恢复，但虽然有错误信息不过没关系，因为不执行这一步的话就无法继续往下执行打开数据库

RMAN> alter database open resetlogs;到此数据库已恢复

⑥重新备份全部的数据库

RMAN> delete noprompt backup;

RMAN> backup database format='/home/oracle/db_bak/%u';

========================数据库备份恢复测试============================

为了保证备份文件可以正常使用，要经常测试备份文件是否完好，但运行的数据库又不能进行数据库的恢复(因为rman是在数据库mount状态下进行数据恢复的)，所以用到了恢复测试的方法，即在数据库打开的状态下进行测试：

RMAN> restore database validate;

Starting restore at 17-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset

channel ORA_DISK_1: reading from backup piece /home/oracle/db_bak/06m7enaq

channel ORA_DISK_1: restored backup piece 1

piece handle=/home/oracle/db_bak/06m7enaq tag=TAG20110317T114946

channel ORA_DISK_1: validation complete, elapsed time: 00:00:36

Finished restore at 17-MAR-11

注：此命令测试备份文件是否完好

RMAN> report need backup;

注：此命令查看那些文件需要备份