SQL> drop tablespace users including contents and datafiles cascade constraint

Tablespace dropped.


System altered.

SQL> shutdown immediate;

2) Recovery
- current controlfile에는 USERS tablespace에 대한 정보가 이미 없어졌으므로 Current Controlfile을 이용할 수 없다.
그래서 직전의 Backup Controlfile을 이용한다.

$ vi alert_HUMAN.log  <-- Drop tablespace의 시점 확인
        Fri Jul 25 10:36:27 2003
        drop tablespace users including contents and datafiles  cascade constraint
        Fri Jul 25 10:36:28 2003
        Deleted file /home/human/oradata/HUMAN/users01.dbf
        Completed: drop tablespace users including contents and dataf

- 예전 backup으로부터 Binary Controlfile Backup과 모든 datafile을 Restore


$ cp ONBACKUP/backup_controlfile.bak /home/human/oradata/HUMAN/control0
control01.ctl  control02.ctl  control03.ctl 
$ cp ONBACKUP/backup_controlfile.bak /home/human/oradata/HUMAN/control01.ctl
$ cp ONBACKUP/backup_controlfile.bak /home/human/oradata/HUMAN/contro
l02.ctl
$ cp ONBACKUP/backup_controlfile.bak /home/human/oradata/HUMAN/contro
l03.ctl

$ cp ONBACKUP/*.dbf /home/human/oradata/HUMAN/    <--모든 Datafile들을 restore

$ sqlplus '/as sysdba'

SQL> startup mount

SQL> set autorecovery on

SQL> recover database until time '2003-07-25:10:36:27' using backup controlfile;  <-- alertlog에서 확인한 time
.......
Log applied.
Media recovery complete.

SQL> create temporary tablespace temp2
  2  tempfile '$HOME/oradata/HUMAN/temp02.dbf' size 15m;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> !rm $HOME/oradata/HUMAN/temp01.dbf

SQL> create temporary tablespace temp
  2  tempfile '$HOME/oradata/HUMAN/temp01.dbf' size 15m;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped.

SQL> select * from v$tempfile;

SQL> select * from dba_temp_files;

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
DRSYS
INDX
TOOLS
USERS

3) 정상인지 확인

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