体验一下利用冷备份介质完成基于时间点的恢复过程。

1.冷备份数据库
1）关闭数据库实例
ora10g@secdb /db_backup$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 17 22:34:26 2011

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

sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2）备份数据库相关文件
将数据库实例相关的文件统一进行备份。
本例中由于所有文件均存放在ora10g中，因此可以简单的按照下面的方法完成冷备份。
ora10g@secdb /oracle/ora10gR2/oradata$ cp -r ora10g ora10g_backup

3）确认备份文件
ora10g@secdb /oracle/ora10gR2/oradata$ ls -ltr
total 8.0K
drwxr-x--- 2 oracle oinstall 4.0K Apr 17 09:07 ora10g/
drwxr-x--- 2 oracle oinstall 4.0K Apr 17 22:53 ora10g_backup/

2.创建测试数据
ora10g@secdb /oracle/ora10gR2/oradata$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 17 22:53:34 2011

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

Connected to an idle instance.

NotConnected@> startup;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
sys@ora10g> set time on
22:54:01 sys@ora10g> create table t (x date);

Table created.

22:54:04 sys@ora10g> insert into t values (sysdate);

1 row created.

22:54:09 sys@ora10g> commit;

Commit complete.

22:54:12 sys@ora10g> insert into t values (sysdate);

1 row created.

22:54:24 sys@ora10g> commit;

Commit complete.

22:54:29 sys@ora10g> insert into t values (sysdate);

1 row created.

22:54:44 sys@ora10g> commit;

Commit complete.

22:55:06 sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

22:55:15 sys@ora10g> select * from t;

X
-------------------
2011-04-17 22:54:09
2011-04-17 22:54:24
2011-04-17 22:54:44

注意，SQL生效时间一上面命令结束后下面的时间显示为准。

3.利用冷备份完成基于时间点的恢复
1）关闭数据库
sys@ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2）使用冷备份恢复数据库文件
ora10g@secdb /oracle/ora10gR2/oradata$ cd ora10g_backup/
ora10g@secdb /oracle/ora10gR2/oradata/ora10g_backup$ cp *.dbf ../ora10g
cp: overwrite `../ora10g/sysaux01.dbf'? y
cp: overwrite `../ora10g/system01.dbf'? y
cp: overwrite `../ora10g/tbs_sec_d_01.dbf'? y
cp: overwrite `../ora10g/temp01.dbf'? y
cp: overwrite `../ora10g/undotbs01.dbf'? y
cp: overwrite `../ora10g/users01.dbf'? y

一一确认覆盖对应的数据文件。
注意，日志文件不要覆盖。保留日志文件用户后面的恢复。

3）启动数据库到mount状态
ora10g@secdb /oracle/ora10gR2/oradata/ora10g_backup$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 17 22:59:19 2011

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

Connected to an idle instance.

NotConnected@> startup mount;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                7168000 bytes
Database mounted.

4）设置session级别的时间显示格式
NotConnected@> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

5）恢复到插入最后两条数据的中间时间点
NotConnected@> recover database until time '2011-04-17 22:54:30';
Media recovery complete.

6）以resetlogs方式开启数据库
NotConnected@> alter database open resetlogs;

Database altered.

7）确认数据恢复结果
sys@ora10g> select * from t;

X
-------------------
2011-04-17 22:54:09
2011-04-17 22:54:24

与创建的测试数据进行比较，已经恢复到“2011-04-17 22:54:44”之前的时间点。

4.小结
使用本文提到的方法进行数据恢复的场景不是很多，不过可以利用这个简单的演练理解利用日志文件恢复数据的过程。希望对大家有帮助。

Good luck.

secooler
11.04.17

-- The End --

ITPub博客

【备份恢复】利用冷备份完成基于时间点的恢复

最新文章