归档模式误删除文件无备份恢复--无数据丢失

最新推荐文章于 2019-08-02 09:48:46 发布

ora600

最新推荐文章于 2019-08-02 09:48:46 发布

阅读量854

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/heizistudio/article/details/8690649

版权

oracle 专栏收录该内容

96 篇文章 0 订阅

订阅专栏

----------归档模式误删除数据无备份恢复--------

前提建立表空间以来所有归档日志全部存在

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /media/standby_redo
Oldest online log sequence     557
Next log sequence to archive   559
Current log sequence           559
SQL> create tablespace test01 datafile '/opt/oracle/oradata/duxiu/test01.dbf' size 50M;

Tablespace created.

SQL> create table test_001 tablespace test01 as select * from scott.emp;

Table created.

SQL> select * from test_001;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17        818                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20       1618        300         30
      7521 WARD       SALESMAN        7698 1981/02/22       1268        500         30
      7566 JONES      MANAGER         7839 1981/04/02       2993                    20
      7654 MARTIN     SALESMAN        7698 1981/09/28       1268       1400         30
      7698 BLAKE      MANAGER         7839 1981/05/01       2868                    30
      7782 CLARK      MANAGER         7839 1981/06/09       2468                    10
      7839 KING       PRESIDENT            1981/11/17       5018                    10
      7844 TURNER     SALESMAN        7698 1981/09/08       1518          0         30
      7900 JAMES      CLERK           7698 1981/12/03        968                    30
      7902 FORD       ANALYST         7566 1981/12/03       3018                    20
      7934 MILLER     CLERK           7782 1982/01/23       1818                    10

12 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /media/standby_redo
Oldest online log sequence     558
Next log sequence to archive   560
Current log sequence           560
SQL> update test_001 set sal=1000;

12 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /media/standby_redo
Oldest online log sequence     559
Next log sequence to archive   561
Current log sequence           561

SQL> host rm /opt/oracle/oradata/duxiu/test01.dbf

SQL> host ls /opt/oracle/oradata/duxiu/test01.dbf
ls: /opt/oracle/oradata/duxiu/test01.dbf: 没有那个文件或目录

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              67110504 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/duxiu/test01.dbf'

SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
5 FILE NOT FOUND

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database create datafile '/opt/oracle/oradata/duxiu/test01.dbf' as '/opt/oracle/oradata/duxiu/test01.dbf';

Database altered.

SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
5

SQL> recover datafile 5;
ORA-00279: change 11835873 generated at 03/19/2013 10:51:30 needed for thread 1
ORA-00289: suggestion : /media/duxiulog/1_559_777229099.dbf
ORA-00280: change 11835873 for thread 1 is in sequence #559

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SQL> select FILE_NAME,FILE_ID,STATUS from dba_data_files where TABLESPACE_NAME='TEST01';

FILE_NAME FILE_ID STATUS
---------------------------------------- ---------- ---------
/opt/oracle/oradata/duxiu/test01.dbf 5 AVAILABLE

SQL> select * from test_001;
select * from test_001
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/opt/oracle/oradata/duxiu/test01.dbf'

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST01                         ONLINE

6 rows selected.

SQL> select file#,error from v$recover_file;

no rows selected

SQL> alter database datafile 5 online;

Database altered.

SQL> select * from test_001;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17       1000                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20       1000        300         30
      7521 WARD       SALESMAN        7698 1981/02/22       1000        500         30
      7566 JONES      MANAGER         7839 1981/04/02       1000                    20
      7654 MARTIN     SALESMAN        7698 1981/09/28       1000       1400         30
      7698 BLAKE      MANAGER         7839 1981/05/01       1000                    30
      7782 CLARK      MANAGER         7839 1981/06/09       1000                    10
      7839 KING       PRESIDENT            1981/11/17       1000                    10
      7844 TURNER     SALESMAN        7698 1981/09/08       1000          0         30
      7900 JAMES      CLERK           7698 1981/12/03       1000                    30
      7902 FORD       ANALYST         7566 1981/12/03       1000                    20
      7934 MILLER     CLERK           7782 1982/01/23       1000                    10

12 rows selected.

SQL>

-------------------------------------------------------------------------------------------

恢复完毕，但是不清楚为什么 mounted之前的 datafile 5 offline

打开之后都是online呢，还要alter database datafile 5 online后才可以使用

ORA-00376错误的一个解释：

Possible causes and solutions summary:
=====================================

  A. Tablespace or Datafile offline.
  B. Datafile does not exist at the OS level.
  C. Datafile locked by Backup Software.
  D. Incorrectly set ULIMIT on UNIX.
  E. Rollback Segment with active transactions is unavailable
  F. Possible Other Causes.