根据控制文件的信息恢复数据文件
根据控制文件的信息恢复数据文件
控制文件里的信息恢复数据文件
SQL> select * from v$dbfile;
FILE# NAME
--------------------------------------------------
4 /u01/tiger/oradata/orcl2/users01.dbf
3 /u01/tiger/oradata/orcl2/sysaux01.dbf
2 /u01/tiger/oradata/orcl2/undotbs01.dbf
1 /u01/tiger/oradata/orcl2/system01.dbf
5 /u01/tiger/oradata/orcl2/ind_tbs01.dbf
SQL> create tablespace tiger datafile'/u01/tiger/oradata/orcl2/tiger01.dbf' size 10m;
Tablespace created.
创建一张表
SQL> create table t1 tablespace tiger asselect * from emp;
切换一次日志使其记录到日志中
SQL> alter system switch logfile;
System altered.
SQL> !cp /etc/passwd/u01/tiger/oradata/orcl2/tiger01.dbf
SQL> select * from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2452 10
7839 KING PRESIDENT 17-NOV-81 5002 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1302 10
14 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2452 10
7839 KING PRESIDENT 17-NOV-81 5002 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1302 10
14 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at thistime
ORA-01110: data file 6:'/u01/tiger/oradata/orcl2/tiger01.dbf'
根据控制文件中的信息重建新的数据文件可以和损坏的同名也可以新命名建立好的文件是空文件需要建立以来的所有日志进行数据恢复
SQL> alter database create datafile 6;
或者也可以
SQL> alter database create datafile '/db254/wb.dbf'as '/db254/wb.dbf';
SQL> alter tablespacetiger online;
alter tablespace tiger online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6:'/u01/tiger/oradata/orcl2/tiger01.dbf'
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 RECOVER
6 rows selected.
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- --------------------------- ---------- ---------
6 OFFLINE OFFLINE 576956 06-SEP-12
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME
---------- ---------- ---------
ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------
1 23 06-SEP-12
/u01/tiger/flash_recovery_area/ORCL2/archivelog/2012_09_06/o1_mf_1_23_84kfjj5c_.arc
SQL> recover datafile 6;
SQL> alter tablespace tiger online;
Tablespace altered.
SQL> select * from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2452 10
7839 KING PRESIDENT 17-NOV-81 5002 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1302 10
14 rows selected.
评论暂时关闭