热备份(hot backup)和RMAN备份共存的系统问题

ALLSTARS_ORACLE

热备份(hot backup)和RMAN备份共存的系统问题

目的：

我们用这个例子来说明一下Hot backup 和RMAN backup 共存时候的风险和规避的办法。

测试用例:

1.首先我们确认属于该DB的所有的数据文件 :

[oracle@test1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 05:28:16 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/system01.dbf
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
/home/oracle/app/oracle/oradata/orcl/users01.dbf
/home/oracle/app/oracle/oradata/orcl/test2.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TEST2

6 rows selected.

2.我们用hotbackup来备份表空间 test2 ,在此之前我们创建对象：


SQL> create table hot_writting_t (col1 number(10)) tablespace test2;

Table created.

3.开始备份表空间 test2 :


SQL> alter tablespace test2 begin backup ;

Tablespace altered.

SQL> insert into hot_writting_t values (1);

1 row created.

SQL> commit;

Commit complete.


SQL> insert into hot_writting_t  select * from hot_writting_t;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> commit;

Commit complete.

4.检查当前的log sequence

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch1/chicago/
Oldest online log sequence     139
Next log sequence to archive   141
Current log sequence           141

----因为前边我们做了一次switch 所以这里我们可以知道，上一个日志的编号是  140

5.我们继续做几个切换

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch1/chicago/
Oldest online log sequence     141
Next log sequence to archive   143
Current log sequence           143


6.在Hot backup 期间我们启动RMAN开始数据库的全库备份，记住此时的log sequence 是143


[oracle@test1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 20 05:39:30 2012

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

connected to target database: ORCL (DBID=1236993297)

RMAN> backup database;

Starting backup at 20-JAN-12
using channel ORA_DISK_1
RMAN-06554: WARNING: file 5 is in backup mode
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/app/oracle/oradata/orcl/test2.dbf
input datafile fno=00002 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CHICAGO/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T054026_7kk3gclh_.bkp tag=TAG20120120T054026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:16
channel ORA_DISK_1: starting full 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 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CHICAGO/backupset/2012_01_20/o1_mf_ncsnf_TAG20120120T054026_7kk3r9cn_.bkp tag=TAG20120120T054026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-JAN-12

7.备份结束。

8.切换几次redo log 进行归档 :

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch1/chicago/
Oldest online log sequence     148
Next log sequence to archive   150
Current log sequence           150




9.此时我们结束hot backup，停掉数据库


SQL> alter tablespace test2 end backup ;

Tablespace altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

10.我们采取挪走数据文件的方式进行数据库结构的破坏 ;

[oracle@test1 ~]$ mv /home/oracle/app/oracle/oradata/orcl/test2.dbf /home/oracle/app/oracle/oradata/orcl/test2.dbf.bak

11.尝试启动数据库：


[oracle@test1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 05:58:57 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  1218412 bytes
Variable Size              79693972 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/test2.dbf'
ORA-01115: IO error reading block from file 5 (block # 1)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1


SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

12.截止到此，我们可以看到数据库的数据文件丢失了，我们需要进行一次恢复，这里我们采取全库恢复的方式：


13.如果不考虑热备的存在，此时我们只需要 全库备份 以及之后生成的所以的 archived log 就可以把数据库恢复到最新的状态需要：

1.“全库备份”
2.“全库备份之后生成的归档” 从sequence 143 开始的日志


14.准备11里的数据进行恢复

[oracle@test1 orcl]$ cd /home/oracle/arch1/chicago/
[oracle@test1 chicago]$ ls
1_137_709212561.arc  1_140_709212561.arc  1_143_709212561.arc  1_146_709212561.arc  1_149_709212561.arc
1_138_709212561.arc  1_141_709212561.arc  1_144_709212561.arc  1_147_709212561.arc
1_139_709212561.arc  1_142_709212561.arc  1_145_709212561.arc  1_148_709212561.arc

15.把我们认为rman不需要的归档挪走：

[oracle@test1 chicago]$ mkdir bffullbkp

[oracle@test1 chicago]$ mv 1_13* bffullbkp/
[oracle@test1 chicago]$ ls
1_140_709212561.arc  1_142_709212561.arc  1_144_709212561.arc  1_146_709212561.arc  1_148_709212561.arc  bffullbkp
1_141_709212561.arc  1_143_709212561.arc  1_145_709212561.arc  1_147_709212561.arc  1_149_709212561.arc
[oracle@test1 chicago]$ mv 1_140_709212561.arc  bffullbkp/
[oracle@test1 chicago]$ mv 1_141_709212561.arc  bffullbkp/
[oracle@test1 chicago]$ mv 1_142_709212561.arc  bffullbkp/
[oracle@test1 chicago]$ mv 1_143_709212561.arc  bffullbkp/

16.此时我们希望数据库可以进行恢复，看一下“the moment of truth”：


RMAN> restore database -force ;

Starting restore at 20-JAN-12
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: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/test2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/CHICAGO/backupset/2012_01_20
/o1_mf_nnndf_TAG20120120T054026_7kk3gclh_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/app/oracle/flash_recovery_area/CHICAGO/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T054026_7kk3gclh_.bkp tag=TAG20120120T054026
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 20-JAN-12

17.进行恢复;


RMAN> recover database;

Starting recover at 20-JAN-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 144 is already on disk as file /home/oracle/arch1/chicago/1_144_709212561.arc
archive log thread 1 sequence 145 is already on disk as file /home/oracle/arch1/chicago/1_145_709212561.arc
archive log thread 1 sequence 146 is already on disk as file /home/oracle/arch1/chicago/1_146_709212561.arc
archive log thread 1 sequence 147 is already on disk as file /home/oracle/arch1/chicago/1_147_709212561.arc
archive log thread 1 sequence 148 is already on disk as file /home/oracle/arch1/chicago/1_148_709212561.arc
archive log thread 1 sequence 149 is already on disk as file /home/oracle/arch1/chicago/1_149_709212561.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/20/2012 06:47:49
RMAN-06053: unable to perform. media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 143 lowscn 4630655 found to restore
RMAN-06025: no backup of log thread 1 seq 142 lowscn 4630615 found to restore
RMAN-06025: no backup of log thread 1 seq 141 lowscn 4630605 found to restore
RMAN-06025: no backup of log thread 1 seq 140 lowscn 4629109 found to restore


*******************************************************************************************
18.从这些信息里我们可以看到 140 ---143 仍然被rman需要进行全库的恢复;
*******************************************************************************************

原因：

这是因为在hot backup 的时候，数据文件00005里的信息并没有真正的被写入，而是生成了一份镜像，并记录在了redo log里了。而这些信息在全库备份之后的归档日志里并没有完全记录。
数据库如果要恢复到最新的状态，需要使用hot backup开始的信息来进行数据块的更新。

关于具体的hot backup如何记录数据块信息请参考My Oracle Support Note :What Happens During aHotBackup[Document 22956.1]

19.我们恢复之前的归档进行测试：

[oracle@test1 chicago]$ cd bffullbkp/
[oracle@test1 bffullbkp]$ mv *.arc ./..
RMAN> recover database;

Starting recover at 20-JAN-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 140 is already on disk as file /home/oracle/arch1/chicago/1_140_709212561.arc
archive log thread 1 sequence 141 is already on disk as file /home/oracle/arch1/chicago/1_141_709212561.arc
archive log thread 1 sequence 142 is already on disk as file /home/oracle/arch1/chicago/1_142_709212561.arc
archive log thread 1 sequence 143 is already on disk as file /home/oracle/arch1/chicago/1_143_709212561.arc
archive log thread 1 sequence 144 is already on disk as file /home/oracle/arch1/chicago/1_144_709212561.arc
archive log thread 1 sequence 145 is already on disk as file /home/oracle/arch1/chicago/1_145_709212561.arc
archive log thread 1 sequence 146 is already on disk as file /home/oracle/arch1/chicago/1_146_709212561.arc
archive log thread 1 sequence 147 is already on disk as file /home/oracle/arch1/chicago/1_147_709212561.arc
archive log thread 1 sequence 148 is already on disk as file /home/oracle/arch1/chicago/1_148_709212561.arc
archive log thread 1 sequence 149 is already on disk as file /home/oracle/arch1/chicago/1_149_709212561.arc
archive log filename=/home/oracle/arch1/chicago/1_140_709212561.arc thread=1 sequence=140
archive log filename=/home/oracle/arch1/chicago/1_141_709212561.arc thread=1 sequence=141
archive log filename=/home/oracle/arch1/chicago/1_142_709212561.arc thread=1 sequence=142
archive log filename=/home/oracle/arch1/chicago/1_143_709212561.arc thread=1 sequence=143
archive log filename=/home/oracle/arch1/chicago/1_144_709212561.arc thread=1 sequence=144
archive log filename=/home/oracle/arch1/chicago/1_145_709212561.arc thread=1 sequence=145
archive log filename=/home/oracle/arch1/chicago/1_146_709212561.arc thread=1 sequence=146
archive log filename=/home/oracle/arch1/chicago/1_147_709212561.arc thread=1 sequence=147
media recovery complete, elapsed time: 00:00:03
Finished recover at 20-JAN-12

RMAN> alter database open;

database opened

RMAN>

总结 :

        基于我们的测试案例，如果全库的备份发生时，DB current online log 包含在 "begin backup " and "end backup" 之间 ，那么begin backup 时刻之后使用的日志会被需要来实现本次的完全或者不完全恢复。这样对于一些执行完一次全库备份后就删除以前的归档日志的习惯的系统来说是存在风险的。所以对于有热备存在的系统要保留更多的归档。