½Ã³ª¸®¿À13: Drop µÈ tablespaceÀÇ º¹±¸-Backup controlfileÀ» ÀÌ¿ëÇÑ º¹±¸-ºÒ¿ÏÀü º¹±¸
ÀÛ¼ºÀÚ °ü¸®ÀÚ ÀÛ¼º½Ã°£ 2003-07-25 11:08:48
 

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;


¸ñ·Ï | ÀÔ·Â | ¼öÁ¤ | ´äº¯ | »èÁ¦