운영 중인 DB 에서 tablespace 삭제했을 경우

# 테이블 스페이스 생성 
SQL> create tablespace insa_tbs
datafile '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf' size 10m
autoextend on next 1m maxsize 20m
extent management local uniform size 1m
segment space management auto; 

# 확인
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;

# trace 생성 
SQL> alter database backup controlfile to trace as '/home/oracle1/control_20240119.sql';

SQL> !
[oracle1@oracle arch]$ cd
[oracle1@oracle ~]$ ls
arch1                 database   new_control.sql                  Templates
arch2                 Desktop    p13390677_112040_LINUX_1of7.zip  userdata
backup                Documents  p13390677_112040_LINUX_2of7.zip  Videos
control_20240119.sql  Downloads  Pictures
create_control.sql    Music      Public

[oracle1@oracle ~]$ vi control_20240119.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 7 '/u01/app/oracle/oradata/ora11g/redo07.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 8 '/u01/app/oracle/oradata/ora11g/redo08.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 9 '/u01/app/oracle/oradata/ora11g/redo09.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ora11g/system01.dbf',
  '/u01/app/oracle/oradata/ora11g/sysaux01.dbf',
  '/u01/app/oracle/oradata/ora11g/insa_tbs01.dbf',
  '/u01/app/oracle/oradata/ora11g/users01.dbf',
  '/u01/app/oracle/oradata/ora11g/example01.dbf',
  '/u01/app/oracle/oradata/ora11g/undo01.dbf'
CHARACTER SET AL32UTF8
;

# select * from v$log;

# 확인
SQL> ! ls /home/oracle1/arch1

# 로그 스위치 발생
SQL> alter system switch logfile;

# redo log 정보 확인
SELECT a.group#, b.sequence#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
FROM v$logfile a, v$log b
WHERE a.group#=b.group#
ORDER BY 1;

SQL> select systimestamp from dual;

# 테이블 스페이스 삭제
=> 운영중인 테이블 스페이스를 지웠다는 상황!

=> 
Fri Jan 19 01:12:26 2024
drop tablespace insa_tbs including contents and datafiles
Deleted file /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf
Completed: drop tablespace insa_tbs including contents and datafiles

# 테이블 생성 , 확인
SQL> create table hr.emp_60 as select * from hr.employees where department_id = 60;

SQL> select count(*) from hr.emp_60;

# 테이블 확인
select e.segment_name ,f.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP_60'
and e.owner = 'HR';

[복구 방법]
# DB 내리기
SQL> shutdown immediate

# data file, control file을 restore 해야 함
SQL> !
[oracle1@oracle arch]$ cd cold_20240119
[oracle1@oracle cold_20240119]$ ls
control01.ctl         example01.dbf            redo07.log  sysaux01.dbf  undo01.dbf
control_20240119.sql  initora11g_20240119.ora  redo08.log  system01.dbf  users01.dbf
data01.dbf            insa_tbs01.dbf           redo09.log  temp01.dbf
[oracle1@oracle cold_20240119]$ cp -av *.dbf /u01/app/oracle/oradata/ora11g/
[oracle1@oracle cold_20240119]$ cp -av *.ctl /u01/app/oracle/oradata/ora11g/

# oracle 접속 후 startup mount단계까지 올리기
[oracle1@oracle cold_20240119]$ sqlplus / as sysdba
SQL> startup mount

# 날짜 형식 변경
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

# drop 하기 전 
-> drop tablespace 잘못했을 경우, drop user, truncate table
SQL> recover database until time '2024-01-19 01:10:26'

SQL> alter database open resetlogs;

# 확인
select a.file#, a.name file_name, b.name file_name, b.name tbs_name, a.status, a.checkpoint_change# 
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
1 /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM SYSTEM SYSTEM 843626
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX SYSAUX ONLINE 843626
3 /u01/app/oracle/oradata/ora11g/insa_tbs01.dbf INSA_TBS INSA_TBS ONLINE 843626
4 /u01/app/oracle/oradata/ora11g/users01.dbf USERS USERS ONLINE 843626
5 /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE EXAMPLE ONLINE 843626
9 /u01/app/oracle/oradata/ora11g/undo01.dbf UNDO1 UNDO1 ONLINE 843626

# 테이블 확인 -> 데이터 손실 발생할 수 있다.
select e.segment_name ,f.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'EMP_60'
and e.owner = 'HR';

tip) resetlogs 로 DB 오픈시 과거 백업본으로는 재사용불가이니, 다시 백업 받아야 한다.