| |
datafile이 소실된 상황에서 백업된 datafile이 없을 때 아카이브된
조건하에 복구가 가능하다.. 만약 datafile생성시기가 아카이브되기
시작한 시점보다 앞선다면 불가능...
SQL> startup
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/human/oradata/HUMAN/new_data.dbf'
SQL> @LABS/tbsinfo2;
SQL> select t.name tablespace_name, f.name file_name, f.status, f.enabled
2 from v$tablespace t, v$datafile f
3 where t.ts# = f.ts#;
TABLESPACE_NAME FILE_NAME STATUS ENABLED
-------------------- ---------------------------------------- ------- ----------
SYSTEM /home/human/oradata/HUMAN/system01.dbf SYSTEM READ WRITE
UNDOTBS1 /home/human/oradata/HUMAN/undotbs01.dbf ONLINE READ WRITE
DRSYS /home/human/oradata/HUMAN/drsys01.dbf ONLINE READ WRITE
INDX /home/human/oradata/HUMAN/indx01.dbf ONLINE READ WRITE
TOOLS /home/human/oradata/HUMAN/tools01.dbf ONLINE READ WRITE
USERS /home/human/oradata/HUMAN/users01.dbf ONLINE READ WRITE
XDB /home/human/oradata/HUMAN/xdb01.dbf ONLINE READ WRITE
NANUNE01 /home/human/oradata/nanune01.dbf ONLINE READ WRITE
ISAYA01 /home/human/oradata/HUMAN/isaya01.dbf ONLINE READ WRITE
QUERY_DATA /home/human/oradata/HUMAN/query01.dbf ONLINE READ ONLY
NEW_DATA /home/human/oradata/HUMAN/new_data.dbf ONLINE READ WRITE
SQL> alter database datafile '$HOME/oradata/HUMAN/new_data.dbf' offline;
Database altered.
SQL> @LABS/tbsinfo2;
SQL> select t.name tablespace_name, f.name file_name, f.status, f.enabled
2 from v$tablespace t, v$datafile f
3 where t.ts# = f.ts#;
TABLESPACE_NAME FILE_NAME STATUS ENABLED
-------------------- ---------------------------------------- ------- ----------
SYSTEM /home/human/oradata/HUMAN/system01.dbf SYSTEM READ WRITE
UNDOTBS1 /home/human/oradata/HUMAN/undotbs01.dbf ONLINE READ WRITE
DRSYS /home/human/oradata/HUMAN/drsys01.dbf ONLINE READ WRITE
INDX /home/human/oradata/HUMAN/indx01.dbf ONLINE READ WRITE
TOOLS /home/human/oradata/HUMAN/tools01.dbf ONLINE READ WRITE
USERS /home/human/oradata/HUMAN/users01.dbf ONLINE READ WRITE
XDB /home/human/oradata/HUMAN/xdb01.dbf ONLINE READ WRITE
NANUNE01 /home/human/oradata/nanune01.dbf ONLINE READ WRITE
ISAYA01 /home/human/oradata/HUMAN/isaya01.dbf ONLINE READ WRITE
QUERY_DATA /home/human/oradata/HUMAN/query01.dbf ONLINE READ ONLY
NEW_DATA /home/human/oradata/HUMAN/new_data.dbf RECOVER READ WRITE
SQL> alter database open;
Database altered.
SQL>
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
DRSYS ONLINE
INDX ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
NANUNE01 ONLINE
NANUNETEMP ONLINE
ISAYA01 ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
QUERY_DATA READ ONLY
NEW_DATA ONLINE
13 rows selected.
SQL> alter tablespace new_data offline immediate;
Tablespace altered.
SQL>
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
DRSYS ONLINE
INDX ONLINE
TOOLS ONLINE
USERS ONLINE
XDB ONLINE
NANUNE01 ONLINE
NANUNETEMP ONLINE
ISAYA01 ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
QUERY_DATA READ ONLY
NEW_DATA OFFLINE
13 rows selected.
SQL> alter database create datafile '$HOME/oradata/HUMAN/new_data.dbf'
2 as '$HOME/LABS/new_data.dbf'; <-- 생성되는 파일
Database altered.
SQL> select name,error,status from v$datafile_header;
NAME
----------------------------------------------------------------------
-----------------------------------------------------
ERROR STATUS
------------------ -------
/home/human/oradata/HUMAN/system01.dbf
ONLINE
/home/human/oradata/HUMAN/undotbs01.dbf
ONLINE
/home/human/LABS/new_data.dbf
OFFLINE
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ---------
3 OFFLINE OFFLINE 922996 24-JUL-03
SQL>
SQL> recover automatic tablespace new_data;
Media recovery complete.
SQL> select * from v$recover_file;
no rows selected
SQL>
SQL> alter tablespace new_data online ;
Tablespace altered.
|