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

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进行恢复。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ae92998538cdb2c513c7bab83a4b14f8.html