Oracle重做日志恢复数据模拟实验

107次阅读

共计 1973 个字符，预计需要花费 5 分钟才能阅读完成。

一系统环境：

1、操作系统：Oracle Linux 5.6
2、数据库：Oracle 11g

二 Oracle 重做日志的作用: [模拟介质恢复]
1. 关闭数据库归档模式：
[oracle@test ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 6 23:49:30 2017

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

SQL> conn /as sysdba
Connected.
SQL> show user
USER is “SYS”
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> start mount
SP2-0310: unable to open file “mount.sql”
SQL> startup mount
ORACLE instance started.

Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 478152776 bytes
Database Buffers 348127232 bytes
Redo Buffers 2433024 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

2. 创建测试表空间 hltest：

create tablespace hltest datafile
‘hltest.ora’ size 5M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
default storage (initial 128K next 1M pctincrease 0);
3. 创建测试用户与测试表：
drop user hl cascade;
create user hl identified by hl default tablespace hltest;
grant connect,resource to hl;
conn hl/hl;
—- 创建表并填充数据
create table a(a number);
begin
for i in 1..100000 loop
insert into a values(i);
end loop;
end;

commit;
4. 拷贝 test.ora 为 test1.ora 文件。
5. insert into a select * from a; –20 万条
6. 关闭数据库
shutdown immediate
7. 将文件 test1.ora 与 test.ora 名称互换。
8. 再次启动数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 830930944 bytes
Fixed Size 2217912 bytes
Variable Size 478152776 bytes
Database Buffers 348127232 bytes
Redo Buffers 2433024 bytes
Database mounted.
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8:
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/u01apporacleoradataorclhltest.ora’

9. 进行介质恢复：
SQL> recover database;
Media recovery complete.
SQL> alter database open ;

Database altered.

三、心得：
Oracle 联机重做日志（ONLINE REDO LOG FILE）主要用于数据库的介质恢复，比如数据文件的损坏。归档日志（ARCHIVED LOG FILE）其实就是对在线日志的备份，毕竟在线日志空间有限而仅能保存一定时间的重做日志数据。归档日志与全库备份文件的结合恢复效果更好。<br> 下一篇将进行模拟数据表删除通过 RMAN 进行恢复。