예제 : 1. create table space test -> create table guest tablespace test -> insert into guest -> rm test datafile -> drop table guest
temp 폴더에서 사용하던 오라클을 temp2 폴더로 임시로 지정하여 guest 테이블이 나오는지 본다.
[oracle@server122 temp2]$ cp -av /data/temp/control01.ctl ./
`/data/temp/control01.ctl' -> `./control01.ctl'
[oracle@server122 temp2]$ cp -av /data/temp/redo* ./
`/data/temp/redo01_a.rdo' -> `./redo01_a.rdo'
`/data/temp/redo01_b.rdo' -> `./redo01_b.rdo'
`/data/temp/redo01_c.rdo' -> `./redo01_c.rdo'
`/data/temp/redo02_a.rdo' -> `./redo02_a.rdo'
`/data/temp/redo02_b.rdo' -> `./redo02_b.rdo'
`/data/temp/redo02_c.rdo' -> `./redo02_c.rdo'
[oracle@server122 temp2]$ cp -av /data/backup/close/*.dbf ./
`/data/backup/close/example01.dbf' -> `./example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `./sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `./system01.dbf'
`/data/backup/close/temp01.dbf' -> `./temp01.dbf'
`/data/backup/close/temp_web01.dbf' -> `./temp_web01.dbf'
`/data/backup/close/test01.dbf' -> `./test01.dbf'
`/data/backup/close/ts_web01.dbf' -> `./ts_web01.dbf'
`/data/backup/close/ts_web_idx01.dbf' -> `./ts_web_idx01.dbf'
`/data/backup/close/undotbs01.dbf' -> `./undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `./users01.dbf'
백업된 datafile과 현재 사용하던 control file & redo log file 을 temp2 로 폴더로 복사한다.
백업된 Control file 을 쓰면 어떻게 될까?_?
[oracle@server122 temp2]$ cd ~
[oracle@server122 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 4 17:40:35 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SYS> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 272632728 bytes
Database Buffers 142606336 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS> @df
TS_NAME FILE_NAME MB STATUS
---------- -------------------------------------------------- ----- -------
SYSTEM /data/temp2/system01.dbf 2010 SYSTEM
SYSAUX /data/temp2/sysaux01.dbf 520 ONLINE
UNDOTBS1 /data/temp2/undotbs01.dbf 90 ONLINE
USERS /data/temp2/users01.dbf 5 ONLINE
EXAMPLE /data/temp2/example01.dbf 346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf 100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf 10 OFFLINE
TEST /data/temp/test01.dbf 0 ONLINE
TS_NEW /data/temp/ts_new.dbf 10 RECOVER
9 rows selected.
SYS> !
[oracle@server122 ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora
[oracle@server122 ~]$ exit
exit
SYS> alter database create datafile '/data/temp/test01.dbf'
2 as '/data/temp2/test01.dbf';
Database altered.
test01.dbf 파일이 지워졌으니 대체품을 새로 만든다.
SYS> @df
TS_NAME FILE_NAME MB STATUS
---------- -------------------------------------------------- ----- -------
SYSTEM /data/temp2/system01.dbf 2010 SYSTEM
SYSAUX /data/temp2/sysaux01.dbf 520 ONLINE
UNDOTBS1 /data/temp2/undotbs01.dbf 90 ONLINE
USERS /data/temp2/users01.dbf 5 ONLINE
EXAMPLE /data/temp2/example01.dbf 346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf 100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf 10 OFFLINE
TEST /data/temp2/test01.dbf 5 ONLINE
TS_NEW /data/temp/ts_new.dbf 10 RECOVER
9 rows selected.
temp2 로 모두 변경 완료
SYS> @log
GROUP# MEMBER MB SEQ# STATUS ARC
------ --------------------------------------------- ---- ---- -------- -----
1 /data/temp/redo01_a.rdo 10 1 CURRENT NO
1 /data/temp/redo01_b.rdo 10 1 CURRENT NO
1 /data/temp/redo01_c.rdo 10 1 CURRENT NO
2 /data/temp/redo02_a.rdo 10 0 UNUSED YES
2 /data/temp/redo02_b.rdo 10 0 UNUSED YES
2 /data/temp/redo02_c.rdo 10 0 UNUSED YES
6 rows selected.
SYS> alter database rename file '/data/temp/redo01_a.rdo'
2 to '/data/temp2/redo01_a.rdo';
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo01_b.rdo'
2* to '/data/temp2/redo01_b.rdo'
SYS> /
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo01_c.rdo'
2* to '/data/temp2/redo01_c.rdo'
SYS> /
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo02_c.rdo'
2* to '/data/temp2/redo02_c.rdo'
SYS> /
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo02_b.rdo'
2* to '/data/temp2/redo02_b.rdo'
SYS> /
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo02_a.rdo'
2* to '/data/temp2/redo02_a.rdo'
SYS> /
Database altered.
SYS> ed
Wrote file afiedt.buf
1 alter database rename file '/data/temp/redo02_a.rdo'
2* to '/data/temp2/redo02_a.rdo'
SYS> @log
GROUP# MEMBER MB SEQ# STATUS ARC
------ --------------------------------------------- ---- ---- -------- -----
1 /data/temp2/redo01_a.rdo 10 1 CURRENT NO
1 /data/temp2/redo01_b.rdo 10 1 CURRENT NO
1 /data/temp2/redo01_c.rdo 10 1 CURRENT NO
2 /data/temp2/redo02_a.rdo 10 0 UNUSED YES
2 /data/temp2/redo02_b.rdo 10 0 UNUSED YES
2 /data/temp2/redo02_c.rdo 10 0 UNUSED YES
6 rows selected.
temp2 로 리두 파일들도 변경 완료
SYS> @df
TS_NAME FILE_NAME MB STATUS
---------- -------------------------------------------------- ----- --------
SYSTEM /data/temp2/system01.dbf 2010 SYSTEM
SYSAUX /data/temp2/sysaux01.dbf 520 ONLINE
UNDOTBS1 /data/temp2/undotbs01.dbf 90 ONLINE
USERS /data/temp2/users01.dbf 5 ONLINE
EXAMPLE /data/temp2/example01.dbf 346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf 100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf 10 OFFLINE
TEST /data/temp2/test01.dbf 5 ONLINE
TS_NEW /data/temp/ts_new.dbf 10 RECOVER
9 rows selected.
SYS> recover database time until '2013-07-04:16:44:00' ; (미리 알고 있는 시간으로 복구)(시간모를땐 어케하지?ㅠㅠ)
ORA-00905: missing keyword
SYS> recover database until time '2013-07-04:16:44:00';
ORA-00279: change 1271471 generated at 07/04/2013 12:37:04 needed for thread 1
ORA-00289: suggestion : /data/arc1/1_1_819894588.arc
ORA-00280: change 1271471 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SYS> alter database open resetlogs;
Database altered.
SYS> select * from guest;
NO
----------
3
6
7
9
