Home >Database >Mysql Tutorial >oracle-日志恢复-015_MySQL

oracle-日志恢复-015_MySQL

WBOY
WBOYOriginal
2016-06-01 13:45:18903browse

bitsCN.com

 没有备份删除表空间,需要日志恢复,但日志不能有断点
在sys用户下查看日志是否处于归档模式下
sql>archive log list
如果处于非归档模式需要sys用户在数据库的mount状态下修改为归档模式
sql>alter database archive
 
 
1.创建用户
SQL> create tablespace lits datafile 'e:/dbf/li01.dbf' size 50m;
 
Tablespace created.
 
SQL> create user li identified by li default tablespace lits;
 
User created.
 
SQL> grant connect to li;
 
Grant succeeded.
--允许用户li任意使用lits表空间
SQL> alter user li quota unlimited on lits;
 
User altered.
 
SQL>
 
 
2.关库,删除dbf文件,模拟数据文件丢失或者损坏
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--删除li01.dbf
 
 
3.重启
SQL> startup
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'E:/DBF/LI01.DBF'
--数据文件丢失只能启动到mount模式,如果要系统正常启动,可以先把丢失的文件offline,其他正常的数据文件正常启动数据库
SQL> select file#,ts#,name from v$datafile;
 
     FILE#        TS# NAME
---------- ---------- ----------------------------------------------------------
         1          0 E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSTEM01.DBF
         2          1 E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/UNDOTBS01.DBF
         3          2 E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSAUX01.DBF
         4          4 E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/USERS01.DBF
         5          6 E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/EXAMPLE01.DBF
         6          7 E:/DBF/MYTABLESPACE.DBF
         7          7 E:/DBF/MYTABLESPACE01.DBF
         8          7 E:/DBF/MY02
         9          8 E:/DBF/MYTS03
        10          9 E:/ORACLE/RMAN/RMANTS01.DBF
        11         10 E:/DBF/LI01.DBF
 
11 rows selected.
 
 
4.datafile offline启动系统
 
SQL> alter database datafile 11 offline;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL>
SQL>
 
 
 
5.删除原来损坏的新建dbf,li01.dbf必须是原来的同名
--还有另外一种恢复方式,在备份前提下 把备份拷贝到datafile同名的数据文件进行恢复
SQL> alter database create datafile 'e:/dbf/li01.dbf';
 
Database altered.
 
 
 
6.把日志同步到新的dbf
SQL> recover datafile 'e:/dbf/li01.dbf';
Media recovery complete.
 
--查不到表
SQL> select * from li.li01;
select * from li.li01
              *
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: 'E:/DBF/LI01.DBF'
SQL>
 
 
7.datafile online
SQL> alter database datafile 11 online;
 
Database altered.
 
SQL> select * from li.li01;
 
       LID
----------
         1
         2
         3
 
SQL>
--查询结果是commit之前的结果。
SQL>

作者“fly@lwy”

bitsCN.com
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn