백업 되지 않는 datafile 삭제

주로 사용자의 실수로 datafile 삭제

-- 전제 조건 아카이브 모드 상태.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log sequence     83
Next log sequence to archive   85
Current log sequence           85
SQL> 

 

-- 상태 확인 
SQL> select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                               STATUS  CREATION_TIM
---------- -------------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                       SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                       ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                      ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                        ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                        ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                          ONLINE  22-JAN-24

-- 장애 상황 발생 
[oracle@ora19c ORA19C]$ rm users02.dbf 
[oracle@ora19c ORA19C]$ exit

-- 로그 스위칭 도중 db down 발생
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5028
Session ID: 154 Serial number: 38319

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
-- DB 마운트 모드 기동
[ora19c:/home/oracle]> ss

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 14 14:52:56 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3137336112 bytes
Fixed Size                  8930096 bytes
Variable Size             671088640 bytes
Database Buffers         2449473536 bytes
Redo Buffers                7843840 bytes
Database mounted.
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME             CON_ID
---------- ------- ------- -------------------- ---------- ------------ ----------
         5 ONLINE  ONLINE  FILE NOT FOUND                0                       0
 
SQL>  select file#,name,status,CREATION_TIME from v$datafile;
     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.
-- 삭제 파일  껍데기 만들어 줌
SQL> alter database create datafile '/oradata/ORA19C/users02.dbf';

Database altered.
-- 복구 
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.
-- db open 완료
SQL> alter database open;

Database altered. 

-- 복구 완료 확인

SQL> select * from v$recover_file;

no rows selected

SQL> col file_name for a45
SQL> set lines 200
SQL>  select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                     BYTES/1024/1024
------------------------------ --------------------------------------------- ---------------
USERS                          /oradata/ORA19C/users01.dbf                             93.75
UNDOTBS1                       /oradata/ORA19C/undotbs01.dbf                             605
SYSTEM                         /oradata/ORA19C/system01.dbf                             1150
SYSAUX                         /oradata/ORA19C/sysaux01.dbf                              910
USERS                          /oradata/ORA19C/users02.dbf                           36.5625
SOE                            /oradata/ORA19C/soe01.dbf                                2419

6 rows selected.

SQL>  select file#,name,status,CREATION_TIME from v$datafile;

     FILE# NAME                                          STATUS  CREATION_TIM
---------- --------------------------------------------- ------- ------------
         1 /oradata/ORA19C/system01.dbf                  SYSTEM  17-APR-19
         3 /oradata/ORA19C/sysaux01.dbf                  ONLINE  17-APR-19
         4 /oradata/ORA19C/undotbs01.dbf                 ONLINE  17-APR-19
         5 /oradata/ORA19C/users02.dbf                   ONLINE  14-FEB-24
         7 /oradata/ORA19C/users01.dbf                   ONLINE  17-APR-19
         8 /oradata/ORA19C/soe01.dbf                     ONLINE  22-JAN-24

6 rows selected.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           OPEN

 

※ DB가  다운 되지 않았다면 삭제한 datafile offline 후 alter database create datafile  생성 부터 

진행 하면 된다.