oracle-日志恢复-015_MySQL

没有备份删除表空间，需要日志恢复，但日志不能有断点
在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”