rman的备份与恢复

修改数据库为归档，并设置两个本地的归档路径

SQL> startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>alter system set archive_dest_1=’location:/u01/oracle/orcl_arch/’;

SQL>alter system set archive_dest_2=’location:/u01/oracle/orcl_arch2/’;

查看归档信息

SQL> archive log list

Database log mode             Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/arch_dest2

Oldest online log sequence      0

Next log sequence to archive   1

Current log sequence           1

利用rman工具简单连接登入数据库

[oracle@orcl10g orcl]$ export ORACLE_SID=orcl

[oracle@orcl10g orcl]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 18 19:43:57 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1362189063)

RMAN>

设置控制文件自动备份

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

设置自己的备份策略：

RMAN增差异量备份

周日： 0级备份

backup incremental level=0 database format "/u01/app/orcl_rman_back/rman_0bak_%T_%t.bak"

周一： 2级备份

backup incremental level=2 database format "/u01/app/orcl_rman_back/rman_2bak_%T_%t.bak"

周二： 2级备份

backup incremental level=2 database format "/u01/app/orcl_rman_back/rman_2bak_%T_%t.bak"

周三： 1级备份

backup incremental level=1 database format "/u01/app/orcl_rman_back/rman_1bak_%T_%t.bak"

周四： 2级备份

backup incremental level=2 database format "/u01/app/orcl_rman_back/rman_2bak_%T_%t.bak"

周五： 2级备份

backup incremental level=2 database format "/u01/app/orcl_rman_back/rman_2bak_%T_%t.bak"

周六： 2级备份

backup incremental level=2 database format "/u01/app/orcl_rman_back/rman_2bak_%T_%t.bak"

周日： 0级备份

backup incremental level=0 database format "/u01/app/orcl_rman_back/rman_0bak_%T_%t.bak"

创建一个完整的0级备份：

RMAN> backup incremental level=0 database format='/u01/app/orcl_rman_back/rman_0bak_%T_%t.bak';

Starting backup at 18-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tptest01.dbf

input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 18-FEB-14

channel ORA_DISK_1: finished piece 1 at 18-FEB-14

piece handle=/u01/app/orcl_rman_back/rman_0bak_20140218_839879834.bak tag=TAG20140218T195713 comment=NONE

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

Finished backup at 18-FEB-14

Starting Control File and SPFILE Autobackup at 18-FEB-14

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_02_18/o1_mf_s_839879919_9j6lqkdh_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-FEB-14

列出需要数据库需要备份的信息，无信息认为备份完全

RMAN> report need backup;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of files with less than 1 redundant backups

File #bkps Name

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

数据文件损坏的恢复：

登入数据库查找数据文件的路径

SQL> select t.name TNAME,d.name DNAME from v$datafile d,v$tablespace t where d.TS#=t.TS#;

TNAME                DNAME

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

SYSTEM               /u01/app/oracle/oradata/orcl/system01.dbf

UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf

SYSAUX               /u01/app/oracle/oradata/orcl/sysaux01.dbf

USERS                /u01/app/oracle/oradata/orcl/users01.dbf

EXAMPLE              /u01/app/oracle/oradata/orcl/example01.dbf

TPTEST               /u01/app/oracle/oradata/orcl/tptest01.dbf

切换测试用户插入数据：

SQL> select username,default_tablespace from dba_users where username = 'XTT';

USERNAME                       DEFAULT_TABLESPACE

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

XTT                            TPTEST

在xtt用户下创建测试数据

SQL> conn xtt/oracle

Connected.

SQL> create table test as select * from tab;

Table created.

SQL> insert into test select * from test;

SQL> commit;

Commit complete.

手动删除数据文件

SQL> ! rm -rf /u01/app/oracle/oradata/orcl/tptest01.dbf

清理缓存并再次查询

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> conn xtt/oracle

Connected.

SQL> select * from test;

select * from test

              *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tptest01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

数据文件丢失，开始恢复

不重启数据库的情况下：

先将数据文件脱机

SQL> alter database datafile 6 offline;

Database altered.

RMAN> restore datafile 6;

Starting restore at 18-FEB-14

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 00006 to /u01/app/oracle/oradata/orcl/tptest01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/orcl_rman_back/rman_0bak_20140218_839879834.bak

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/orcl_rman_back/rman_0bak_20140218_839879834.bak tag=TAG20140218T195713

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

Finished restore at 18-FEB-14

RMAN> recover datafile 6;

Starting recover at 18-FEB-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 18-FEB-14

SQL> alter database datafile 6 offline;

Database altered.

再次查看测试数据：

SQL> conn xtt/oracle

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

       128

若数据库已经关闭，无需修改数据文件为脱机，直接进行恢复即可，此处不做练习了

没有数据文件的备份，但是数据库处于归档模式那么还可以利用归档日志恢复数据库：

SQL> alter database datafile  '/u01/app/oracle/oradata/orcl/tptest01.dbf' offline;

SQL> alter database create datafile  '/u01/app/oracle/oradata/orcl/tptest01.dbf' as '/u01/app/oracle/oradata/orcl/tptest01.dbf'

SQL> recover datafile '/u01/app/oracle/oradata/orcl/tptest01.dbf';

ORA-00279: change 686864 generated at 02/18/2014 17:05:07 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch_dest2/1_30_834408329.dbf

ORA-00280: change 686864 for thread 1 is in sequence #30

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 707164 generated at 02/18/2014 17:16:40 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch_dest2/1_31_834408329.dbf

ORA-00280: change 707164 for thread 1 is in sequence #31

ORA-00278: log file '/u01/app/oracle/arch_dest2/1_30_834408329.dbf' no longer

needed for this recovery

Log applied.

Media recovery complete.

SQL>alter database datafile '/u01/app/oracle/oradata/orcl/tptest01.dbf' online;

恢复完成，课间归档日志有多重要。。。

查看测试数据是否恢复

SQL> conn xtt/oracle

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

 128

控制文件丢失的恢复：要求要有数据文件的备份，否则需要自己手动的去写控制文件，很难进行恢复

还是上次的数据，模拟损坏，删除数据文件

SQL> !rm -rf /u01/app/oracle/oradata/orcl/control*

SQL> shutdown immediate;

ORA-00210: cannot open the specified control file

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

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              67111156 bytes

Database Buffers           96468992 bytes

Redo Buffers                2973696 bytes

要注意上次rman连接是的DBID在恢复控制文件时十分重要

[oracle@orcl10g ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 18 20:33:31 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1362189063)

RMAN> set DBID=1362189063

executing command: SET DBID

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_02_18/o1_mf_s_839879919_9j6lqkdh_.bkp';

Starting restore at 18-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring control file

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

output filename=/u01/app/oracle/oradata/orcl/control01.ctl

output filename=/u01/app/oracle/oradata/orcl/control02.ctl

output filename=/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at 18-FEB-14

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

控制文件的恢复要重做一下日志

RMAN> alter database open resetlogs;

database opened

查看数据仍然存在

SQL> conn xtt/oracle

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

       128

联机重做日志丢失的恢复

非当前联机重做日志的丢失：

查看当前日志

SQL> select l.group#,lf.member,l.status from v$log l,v$logfile lf where l.group#=lf.group#

    GROUP# MEMBER                                 STATUS

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

         3 /u01/app/oracle/oradata/orcl/redo03.log            INACTIVE

         2 /u01/app/oracle/oradata/orcl/redo02.log            CURRENT

         1 /u01/app/oracle/oradata/orcl/redo01.log            INACTIVE

删除非当前日志

SQL> !rm -rf /u01/app/oracle/oradata/orcl/redo01.log

重启数据库发现报错

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              62916852 bytes

Database Buffers          100663296 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

数据库处于mounted状态

SQL> select status from v$instance;

STATUS

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

MOUNTED

重建改组日志

SQL> alter database clear logfile group 1;

Database altered.

若提示出错

SQL> alter database clear logfile group 1;

alter database clear logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

原因是丢失的日志没有归档，是在数据库开启状态将非联机重做日志删除的原因，加上参数unarchived即可

SQL> alter database clear unarchived logfile group 1;

Database altered.

打开数据库

SQL> alter database open;

Database altered.

当前联机日志的丢失

查看当前日志组

SQL> select l.group#,lf.member,l.status from v$log l,v$logfile lf where l.group#=lf.group#;

    GROUP# MEMBER                                             STATUS

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

         3 /u01/app/oracle/oradata/orcl/redo03.log            INACTIVE

         2 /u01/app/oracle/oradata/orcl/redo02.log            CURRENT

         1 /u01/app/oracle/oradata/orcl/redo01.log            INACTIVE

加入测试数据

SQL> select count(*) from test;

  COUNT(*)

----------

       128

SQL> insert into test select * from test;

128 rows created.

SQL> select count(*) from test;

  COUNT(*)

----------

       256

非一致性关闭数据库

SQL> shutdown abort;

ORACLE instance shut down.

删除当前联机重做日志

[oracle@orcl10g ~]$ rm -rf /u01/app/oracle/oradata/orcl/redo02.log

启动报错

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              62916852 bytes

Database Buffers          100663296 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

先尝试更新联机重做日志进行恢复会报错

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

只能进行不完全恢复

修改参数_allow_resetlogs_corruption为true允许在open时跳过一致性的检查

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

进行基于取消的恢复

SQL> recover database until cancel;

ORA-00308: cannot open archived log

'/u01/app/oracle/arch_dest2/1_2_839927705.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

ORA-01194: file 1 needs more recovery to be consistent

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

打开数据库

SQL> alter database open resetlogs;

Database altered.

查看测试数据

SQL> select count(*) from test;

  COUNT(*)

----------

       256

发现测试数据仍然存在，该方法有可能会导致数据库的不一致，会丢失未提交的一些数据