Export / Import 종합 연습 문제 #2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 15. 12:32
실제서버위치 : /home/oracle/oradata/testdb/
복구서버위치 : /data/temp/

1. Tablespace 생성 : ts_c <- 없으면 적당히 생성하세요
2. auser 생성 : default Tablespace를 ts_c로 지정한다.
3. auser 로 로그인해서 Table 생성(tbl_test)하고 Data 입력 후 commit;
4. drop user cascade로 auser 삭제

미션 : 복구서버에서 auser의 tbl_test를 복구해서 Export 받은 후 실제서버의 scott 계정으로 Import 시키세요



저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #1  (0) 2010.03.15
Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10

Export / Import 종합 연습 문제 #1

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 15. 12:30
<장애 상황>
현재 운영중인 DB서버(이하 A서버)에서 며칠전에 scott의 테이블(tbl_wow)가 지워졌으니 복구해달라는 요청을 받았다.
해당 테이블이 있던 테이블스페이스도 삭제된 상황이다.
테이블 삭제 시간은 대략 알고 있으나 (drop table 후 시간 확인하세요) 현재 운영중인 A서버에서 작업을 할 수 없으므로 A 서버를 잠시 중지 시키고 모든 파일을 백업받아서 B서버에서 복구작업을 하기로 하였다.
<대략적인 작업 방법>
1. A 서버의 전체 파일들을 백업받아서 B서버로 옮긴다( /data/temp/ ).
2. B 서버에서 데이터 작업을 완료하고 알려준 시간으로 시간기반 불완전 복구를 한다.
3. B 서버에서 삭제된 테이블을 찾아 exp 받아서 현재 운영중인 A서버에 imp 시킨다.
4. A 서버에서 scott계정으로 확인하면 지워지기 전의 데이터 1,2,3 이 나와야 한다.
<작업 관련 사항>
1. 지워진 테이블 이름 : scott.tbl_wow (1,2,3 의 값이 입력되어 있었음)
2. 위 테이블이 들어있었던 테이블 스페이스 이름 : tbs_wow_01 <- 적당히 생성시키세요
3. A서버 원래디렉토리 : /home/oracle/oradata/testdb/
4. A서버 원본백업디렉토리 : /data/backup/close/
5. B서버 작업디렉토리 : /data/temp/




저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #2  (0) 2010.03.15
Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10

Control file 장애 종합 연습 문제

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 15. 11:55
장애상황
Data file들만 남아있고 다른 모든 File(Redo log file, Control file)이 삭제된 상황.
Control file은 오래전에 Backup 받은 파일만 있다.
예전에 Backup 해놓은 Control file을 이용해서 Control file 재 생성 Script를 만들고 이 장애를 복구한다.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile; 

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/home/oracle/disk4/redo01_a.log
/home/oracle/disk5/redo01_b.log
/home/oracle/disk4/redo02_a.log
/home/oracle/disk5/redo02_b.log
/home/oracle/disk4/redo03_a.log
/home/oracle/disk5/redo03_b.log

6 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

#연습문제와 같이 장애를 발생시키기 위해 Redo log files와 Control file을 삭제하고 삭제 여부를 확인한다.
SQL> !rm -rf /home/oracle/disk4/*.log                                                  

SQL> !ls /home/oracle/disk4/*.log
ls: /home/oracle/disk4/*.log: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /home/oracle/disk5/*.log

SQL> !ls /home/oracle/disk5/*.log
ls: /home/oracle/disk5/*.log: 그런 파일이나 디렉토리가 없음

SQL> !rm -rf /home/oracle/oradata/testdb/*.ctl

SQL> !ls /home/oracle/oradata/testdb/*.ctl
ls: /home/oracle/oradata/testdb/*.ctl: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#Control file과 Redo log file이 삭제된 상태에서 DB를 시작할 경우 아래와 같은 에러가 발생한다.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
#예전에 Backup 받아놓은 Control file을 Server Parameter에 설정된 경로로 복사한다.
SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control01.ctl

SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control02.ctl

SQL> !cp /data/backup/redo_close/control01.ctl /home/oracle/oradata/testdb/control03.ctl

#현재 Control file은 예전에 Backup 받았던  Control file이기 때문에 아래와 같이 old control file 에러가 발생한다.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'
ORA-01207: file is more recent than control file - old control file


#에러가 발생하면 Control file을 재 생성하는 Script를 생성한다.
SQL> alter database backup controlfile to trace as '/home/oracle/oradata/testdb/controlfile.sql';

Database altered.

#Control file 재 생성을 위해 Script 내용을 아래와 같이 수정한다.
SQL> !vi /home/oracle/oradata/testdb/controlfile.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/home/oracle/disk4/redo01_a.log',
    '/home/oracle/disk5/redo01_b.log'
  ) SIZE 5M,
  GROUP 2 (
    '/home/oracle/disk4/redo02_a.log',
    '/home/oracle/disk5/redo02_b.log'
  ) SIZE 5M,
  GROUP 3 (
    '/home/oracle/disk4/redo03_a.log',
    '/home/oracle/disk5/redo03_b.log'
  ) SIZE 5M
DATAFILE
  '/home/oracle/oradata/testdb/system01.dbf',
  '/home/oracle/oradata/testdb/undotbs01.dbf',
  '/home/oracle/oradata/testdb/sysaux01.dbf',
  '/home/oracle/oradata/testdb/users01.dbf',
  '/home/oracle/oradata/testdb/example01.dbf',
  '/home/oracle/oradata/testdb/ts_a01.dbf',
  '/home/oracle/oradata/testdb/ts_b01.dbf',
  '/home/oracle/oradata/testdb/ts_c01.dbf'
CHARACTER SET KO16KSC5601
;
"~/oradata/testdb/controlfile.sql" 31L, 894C written

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
#Control file 재 생성을 위해 DB를 mount 단계로 시작하기 위해 사용했던 이전 Control file은 삭제한다.
SQL> !ls /home/oracle/oradata/testdb/*.ctl    
/home/oracle/oradata/testdb/control01.ctl  /home/oracle/oradata/testdb/control03.ctl
/home/oracle/oradata/testdb/control02.ctl

SQL> !rm -rf /home/oracle/oradata/testdb/*.ctl   

SQL> !ls /home/oracle/oradata/testdb/*.ctl
ls: /home/oracle/oradata/testdb/*.ctl: 그런 파일이나 디렉토리가 없음

#Control file 재 생성을 위해 수정해놓은 /home/oracle/oradata/testdb/controlfile.sql Script 파일을 실행한다.
SQL> @/home/oracle/oradata/testdb/controlfile.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              83887696 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes

Control file created.

SQL> !ls /home/oracle/oradata/testdb/*.ctl
/home/oracle/oradata/testdb/control01.ctl  /home/oracle/oradata/testdb/control03.ctl
/home/oracle/oradata/testdb/control02.ctl

#DB를 open 상태로 전환하고 Data file, Redo log file, Control file을 확인한다.
SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/home/oracle/disk4/redo03_a.log
/home/oracle/disk5/redo03_b.log
/home/oracle/disk4/redo02_a.log
/home/oracle/disk5/redo02_b.log
/home/oracle/disk4/redo01_a.log
/home/oracle/disk5/redo01_b.log

6 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl
/home/oracle/oradata/testdb/control02.ctl
/home/oracle/oradata/testdb/control03.ctl

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #2  (0) 2010.03.15
Export / Import 종합 연습 문제 #1  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10

Incomplete Recovery - using backup controlfile 연습문제 #2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 12. 02:25
#시나리오
A. Data file, Control file을 open Backup 한다.
B. 새로운 ts_d Tablespace를 생성한다.
C. Tablespace ts_d에 scott.test Table을 생성하고 Data를 입력한다.
D. ts_d Tablespace를 삭제한다. rm 또는 drop tablespace 동일하다.
　 ※ ts_d Tablespace는 Backup 받은 Data file이 없다.

#연습문제
D에서 삭제된 ts_d Talespace를 복구한다.


#현재 Data file을 확인한다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#모든 Data file을 Open Backup 한다.
SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace ts_a begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_a01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/open/ts_a01.dbf'

SQL> alter tablespace ts_a end backup;

Tablespace altered.

SQL> alter tablespace ts_b begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_b01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/open/ts_b01.dbf'

SQL> alter tablespace ts_b end backup;

Tablespace altered.

SQL> alter tablespace ts_c begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/ts_c01.dbf /data/backup/open/
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/open/ts_c01.dbf'

SQL> alter tablespace ts_c end backup;

Tablespace altered.

#Data file을 Open Backup 했다면, 중요한 Control file 도 Backup 한다.
SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

#시나리오와 같이 ts_d Tablespace를 생성한다.
SQL> create tablespace ts_d datafile '/home/oracle/oradata/testdb/ts_d01.dbf' size 5m;

Tablespace created.

#ts_d Tablespace에 test Table을 생성하고 Data를 입력한다.
SQL> create table scott.test(no number) tablespace ts_d;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2
         3

#Data까지 입력하고 ts_d Tablespace를 삭제하기 전에 복구를 위해 현재 시간을 확인한다.
#rm 이 아닌 drop tablespace ts_d including contents and datafiles; 를 사용할 경우 alter_testdb.log 파일에서 drop tablespace ts_d including contents and datafiles 했던 시간을 확인할 수 있다.
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:02:02:12

#ts_d Tablespace를 삭제하고 삭제 되었는지 확인한다.
SQL> !rm /home/oracle/oradata/testdb/ts_d01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*.dbf
/home/oracle/oradata/testdb/ts_a01.dbf  /home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf

#Archive log를 만들기 위해 로그 스위치를 몇 차례 발생시킨다.
#주의사항 : 실습 또는 연습문제에서 로그 스위치를 발생시키지 않을 경우 Archive log가 생성되지 않아 복구할 수 없다.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:02:04:43

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir /home/oracle/oradata/testdb/temp

#복구시 실수로 인해 기존 DB까지 사용하지 못할 수 있다. 따라서 현재의 원본 Data file, Redo log file, Control file을 /data/backup/temp 디렉토리를 생성하고 Backup 받는다.
SQL> !mkdir /data/backup/temp

SQL> !cp -av /home/oracle/oradata/testdb/* /data/backup/temp/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/temp/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/temp/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/temp/control03.ctl'
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/temp/example01.dbf'
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/temp/redo03.log'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/temp/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/temp/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/temp/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/temp/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/temp/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/temp/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/temp/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/temp/users01.dbf'

#원본 Data file, Redo log file, Control file을 Backup 받은 후 이전에 Backup 받아놓은 Data file을 복원한다.
SQL> !cp -av /data/backup/open/*.dbf /home/oracle/oradata/testdb/
`/data/backup/open/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

#이전에 Backup 받아놓은 Control file을 복원한다.
SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control01.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control02.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'

SQL> !cp -av /data/backup/open/control01.ctl /home/oracle/oradata/testdb/control03.ctl
`/data/backup/open/control01.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

#복구를 위해 mount 단계로 시작한다.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> 
#ts_d Tablespace에 문제(삭제)가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;
ORA-00279: change 505394 generated at 03/12/2010 01:55:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 505394 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'


ORA-01112: media recovery not started

#ts_d01.dbf가 존재하지 않아 오류가 발생한다. 따라서 ts_d01.dbf를 복원해야 한다. 아래와 같이 현재 Data file을 재 확인하면 새로운 Data file("/home/oracle/product/10g/dbs/UNNAMED00009) 목록이 확인된다.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/product/10g/dbs/UNNAMED00009

9 rows selected.

#기존 Data file인 ts_d01.dbf를 복원하기 위해 아래와 같이 실행한다.
SQL> alter database create datafile 'UNNAMED00009' as '/home/oracle/oradata/testdb/ts_d01.dbf';

Database altered.

#위 과정을 통해 현재 Data file을 재 확인해 보면 ts_d01.dbf 파일로 변경된 것을 확인할 수 있다.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

#ts_d01.dbf가 복원 되었으니 다시한번 ts_d Tablespace에 문제가 발생하기 이전 시간으로 복구한다.
SQL> recover database until time '2010-03-12:02:02:12' using backup controlfile;               
ORA-00279: change 508678 generated at 03/12/2010 02:00:53 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 508678 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
#복구가 완료된 후 resetlogs 옵션을 이용해 open 단계로 변경한다.
SQL> alter database open resetlogs;

Database altered.

#select 문을 이용해 복구 여부를 확인한다.
SQL> select * from scott.test;

        NO
----------
         1
         2
         3

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Export / Import 종합 연습 문제 #1  (0) 2010.03.15
Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10

Incomplete Recovery - using backup controlfile 연습문제 #1

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 11. 20:55
#시나리오
A. DB를 전체 백업한다.
B. ts_d Tablesapce 생성하고, Control file을 Backup 한다.
C. ts_d에 scott.test Table을 생성하고 Data를 입력한다.
D. scott.test Table을 Drop 한다.
E. ts_d Tablespace를 Drop 한다.
F. 현재 시점

#연습문제
D 시점에서 삭제된 scott.test Table을 복구한다.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/close/
`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/close/example01.dbf'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/close/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/close/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/data/backup/close/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/data/backup/close/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/data/backup/close/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/data/backup/close/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/close/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/close/users01.dbf'

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/close/
`/home/oracle/oradata/testdb/control01.ctl' -> `/data/backup/close/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/data/backup/close/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/data/backup/close/control03.ctl'

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/close/
`/home/oracle/oradata/testdb/redo01.log' -> `/data/backup/close/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/data/backup/close/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/data/backup/close/redo03.log'

SQL> !cp -av $ORACLE_HOME/dbs /data/backup/close/
`/home/oracle/product/10g/dbs' -> `/data/backup/close/dbs'
`/home/oracle/product/10g/dbs/orapwtestdb' -> `/data/backup/close/dbs/orapwtestdb'
`/home/oracle/product/10g/dbs/init.ora' -> `/data/backup/close/dbs/init.ora'
`/home/oracle/product/10g/dbs/initdw.ora' -> `/data/backup/close/dbs/initdw.ora'
`/home/oracle/product/10g/dbs/spfiletestdb.ora' -> `/data/backup/close/dbs/spfiletestdb.ora'
`/home/oracle/product/10g/dbs/lkTESTDB' -> `/data/backup/close/dbs/lkTESTDB'
`/home/oracle/product/10g/dbs/hc_testdb.dat' -> `/data/backup/close/dbs/hc_testdb.dat'

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> create tablespace ts_d datafile '/home/oracle/oradata/testdb/ts_d01.dbf' size 5m;

Tablespace created.

SQL> alter database backup controlfile to '/data/backup/close/control01.bak';

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf
TS_D                          5 /home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:04:04

SQL> create table scott.test(no number) tablespace ts_d;

Table created.

SQL> insert into scott.test values (1);

1 row created.

SQL> insert into scott.test values (2);

1 row created.

SQL> insert into scott.test values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

        NO
----------
         1
         2
         3

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:04:25

SQL> drop table scott.test purge;

Table dropped.

SQL> alter system switch logfile;

System altered.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-12:01:05:54

SQL> drop tablespace ts_d including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> select * from scott.test;
select * from scott.test
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /home/oracle/oradata/testdb/temp

SQL> !cp -av /home/oracle/oradata/testdb/*.* /home/oracle/oradata/testdb/temp/
`/home/oracle/oradata/testdb/control01.ctl' -> `/home/oracle/oradata/testdb/temp/control01.ctl'
`/home/oracle/oradata/testdb/control02.ctl' -> `/home/oracle/oradata/testdb/temp/control02.ctl'
`/home/oracle/oradata/testdb/control03.ctl' -> `/home/oracle/oradata/testdb/temp/control03.ctl'
`/home/oracle/oradata/testdb/example01.dbf' -> `/home/oracle/oradata/testdb/temp/example01.dbf'
`/home/oracle/oradata/testdb/redo01.log' -> `/home/oracle/oradata/testdb/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/home/oracle/oradata/testdb/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/home/oracle/oradata/testdb/temp/redo03.log'
`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/home/oracle/oradata/testdb/temp/sysaux01.dbf'
`/home/oracle/oradata/testdb/system01.dbf' -> `/home/oracle/oradata/testdb/temp/system01.dbf'
`/home/oracle/oradata/testdb/temp01.dbf' -> `/home/oracle/oradata/testdb/temp/temp01.dbf'
`/home/oracle/oradata/testdb/ts_a01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_a01.dbf'
`/home/oracle/oradata/testdb/ts_b01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_b01.dbf'
`/home/oracle/oradata/testdb/ts_c01.dbf' -> `/home/oracle/oradata/testdb/temp/ts_c01.dbf'
`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/home/oracle/oradata/testdb/temp/undotbs01.dbf'
`/home/oracle/oradata/testdb/users01.dbf' -> `/home/oracle/oradata/testdb/temp/users01.dbf'

SQL> !cp -av /data/backup/close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control01.ctl

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control02.ctl

SQL> !cp /data/backup/close/control01.bak /home/oracle/oradata/testdb/control03.ctl

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> select name from v$datafile;

NAME
--------------------------------------------------
/home/oracle/oradata/testdb/system01.dbf
/home/oracle/oradata/testdb/undotbs01.dbf
/home/oracle/oradata/testdb/sysaux01.dbf
/home/oracle/oradata/testdb/users01.dbf
/home/oracle/oradata/testdb/example01.dbf
/home/oracle/oradata/testdb/ts_a01.dbf
/home/oracle/oradata/testdb/ts_b01.dbf
/home/oracle/oradata/testdb/ts_c01.dbf
/home/oracle/oradata/testdb/ts_d01.dbf

9 rows selected.

※ 명령어 확인 !!!
SQL> alter database create




SQL> recover database until time '2010-03-12:01:04:25' using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/home/oracle/oradata/testdb/ts_d01.dbf'


SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Control file 장애 종합 연습 문제  (0) 2010.03.15
Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10

Incomplete Recovery - Time Base 연습문제 2

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 12:11
drop user howkey cascade; 로 사용자를 삭제하고 select로 조회하면 jmember, jumsu Table이 삭제된 것을 확인할수 있다. 사용자를 삭제하기 전 상태로 복구한다.

SQL> create user howkey
  2  identified by howkey
  3  default tablespace ts_a
  4  temporary tablespace temp
  5  quota unlimited on ts_a;

User created.

SQL> grant connect, resource to howkey;

Grant succeeded.

SQL> conn howkey/howkey;
Connected.
SQL> create table jmember
  2  (  id      varchar2(10),
  3     name    varchar2(10),
  4     tel     varchar2(10));

Table created.

SQL> create table jumsu
  2  (  sub_name        varchar2(10),
  3     sub_prof        varchar2(10),
  4     sub_jumsu       number);

Table created.

SQL> insert into jmember values('kara1', '박규리', '1111-1111');

1 row created.

SQL> insert into jmember values('kara2', '한승연', '2222-2222');

1 row created.

SQL> insert into jmember values('kara3', '정니콜', '3333-3333');

1 row created.

SQL> insert into jmember values('kara4', '구하라', '4444-4444');

1 row created.

SQL> insert into jmember values('kara5', '강지영', '5555-5555');

1 row created.

SQL> insert into jumsu values('사회', '강석호', 100);

1 row created.

SQL> insert into jumsu values('수학', '차기봉', 99);

1 row created.

SQL> insert into jumsu values('영어', '앤써니양', 96);

1 row created.

SQL> insert into jumsu values('국어', '이은유', 93);

1 row created.

SQL> insert into jumsu values('과학', '장영식', 90);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from jmember;

ID         NAME       TEL
---------- ---------- ----------
kara1      박규리     1111-1111
kara2      한승연     2222-2222
kara3      정니콜     3333-3333
kara4      구하라     4444-4444
kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU
---------- ---------- ----------
사회       강석호            100
수학       차기봉             99
영어       앤써니양           96
국어       이은유             93
과학       장영식             90

#recover database until time에 적용할 시간을 확인한다.
SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-11:17:40:19

SQL> conn / as sysdba
Connected.
#연습문제 내용과 같이 cascade 옵션을 사용해 howkey 계정을 삭제한다.
SQL> drop user howkey cascade;

User dropped.

#위에서 howkey 계정을 삭제했기 때문에 접속할 수 없다.
SQL> conn howkey/howkey
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
#복구를 위해 shutdown 한다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#복구를 위해 Backup 받아놓은 Data file만 복사한다.
SQL> !cp -av /data/backup/cr_close/*.dbf /home/oracle/oradata/testdb/
`/data/backup/cr_close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/cr_close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/cr_close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/cr_close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'
`/data/backup/cr_close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/cr_close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/cr_close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/cr_close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/cr_close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
#위에서 확인해 두었던 시간을 적용해 recover database until time 명령어를 실행하고 DB를 open한다.
SQL> recover database until time '2010-03-11:17:40:19';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

#복구 여부를 확인하기 위해 삭제했던 howkey 계정으로 접속하고 select 문을 이용해 Data를 확인한다.
SQL> conn howkey/howkey
Connected.
SQL> select * from jmember;

ID         NAME       TEL
---------- ---------- ----------
kara1      박규리     1111-1111
kara2      한승연     2222-2222
kara3      정니콜     3333-3333
kara4      구하라     4444-4444
kara5      강지영     5555-5555

SQL> select * from jumsu;

SUB_NAME   SUB_PROF    SUB_JUMSU
---------- ---------- ----------
사회       강석호            100
수학       차기봉             99
영어       앤써니양           96
국어       이은유             93
과학       장영식             90

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile 연습문제 #2  (0) 2010.03.12
Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09

Incomplete Recovery - Time Base 연습문제 1

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 12:08
update 실수! update 이전 시점으로 복구하기

SQL> create table member
  2  (  no              number,
  3     name            varchar2(10),
  4     address         varchar2(10),
  5     hiredate        date)
  6  tablespace ts_c;

Table created.

SQL> insert into member values(1, '박규리', '서울', sysdate);

1 row created.

SQL> insert into member values(2, '한승연', '서울', sysdate);

1 row created.

SQL> insert into member values(3, '정니콜', '미국', sysdate);

1 row created.

SQL> insert into member values(4, '구하라', '광주', sysdate);

1 row created.

SQL> insert into member values(5, '강지영', '경기도', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     서울       2010-03-10:11:45:24
         2 한승연     서울       2010-03-10:11:45:28
         3 정니콜     미국       2010-03-10:11:45:31
         4 구하라     광주       2010-03-10:11:45:34
         5 강지영     경기도     2010-03-10:11:45:36

SQL> update member set address='대한민국';

5 rows updated.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     대한민국   2010-03-10:11:45:24
         2 한승연     대한민국   2010-03-10:11:45:28
         3 정니콜     대한민국   2010-03-10:11:45:31
         4 구하라     대한민국   2010-03-10:11:45:34
         5 강지영     대한민국   2010-03-10:11:45:36

SQL> commit;

Commit complete.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE
-------------------
2010-03-10:11:57:33

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp -av /data/backup/open/*.dbf /home/oracle/oradata/testdb/
`/data/backup/open/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             121636432 bytes
Database Buffers          159383552 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> recover database until time '2010-03-10:11:50:00';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select no, name, address, to_char(hiredate, 'YYYY-MM-DD:HH24:MI:SS') hiredate from member;

        NO NAME       ADDRESS    HIREDATE
---------- ---------- ---------- -------------------
         1 박규리     서울       2010-03-10:11:45:24
         2 한승연     서울       2010-03-10:11:45:28
         3 정니콜     미국       2010-03-10:11:45:31
         4 구하라     광주       2010-03-10:11:45:34
         5 강지영     경기도     2010-03-10:11:45:36

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - using backup controlfile 연습문제 #1  (0) 2010.03.11
Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09

Complete Recovery (다른 경로에 응급 복구)

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 10. 03:41
현재 서버의 모든 파일들을 아래와 같이 구성하기
/data/data1/system
/data/data2/sysaux, undotbs
/data/data3/users, example, ts_a, ts_b, ts_c
/data/data4/control01.ctl, redo1, redo2, redo3
/data/data5/control02.ctl, redo1, redo2, redo3

/data/data1, data2, data3을 각각 삭제하고 하나씩 복구하기
복구가 완료되면 모든 파일들을 /home/oracle/oradata/testdb 로 이동한다.


저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - Time Base 연습문제 2  (0) 2010.03.10
Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28

Complete Recovery 연습 문제

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 9. 12:13
#1 /home/oracle/oradata/testdb/undotbs01.dbf를 삭제하고 복구하기

SQL> !rm -rf /home/oracle/oradata/testdb/undotbs01.dbf

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/testdb/undotbs01.dbf'


SQL> !cp /data/backup/open/undotbs01.dbf /home/oracle/oradata/testdb/

SQL> recover datafile '/home/oracle/oradata/testdb/undotbs01.dbf';
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf
EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf
TS_A                          5 /home/oracle/oradata/testdb/ts_a01.dbf
TS_B                          5 /home/oracle/oradata/testdb/ts_b01.dbf
TS_C                          5 /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

SQL> 



#2 ts_a, ts_b, ts_c에 각각 test5, test6, test7 Table을 만들고 Data를 입력한 후 각 Data file을 삭제하고 3개의 Tablespace 전부 복구하기

SQL> create table test5 (no number) tablespace ts_a;

Table created.

SQL> insert into test5 values (5);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test5;

        NO
----------
         5

SQL> create table test6 (no number) tablespace ts_b;

Table created.

SQL> insert into test6 values (6);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test6;

        NO
----------
         6

SQL> create table test7 (no number) tablespace ts_c;

Table created.

SQL> insert into test7 values (7);

1 row created.

SQL> commit;  

Commit complete.

SQL> select * from test7;

        NO
----------
         7

SQL> !rm /home/oracle/oradata/testdb/ts_a01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_b01.dbf

SQL> !rm /home/oracle/oradata/testdb/ts_c01.dbf

SQL> !ls /home/oracle/oradata/testdb/ts_*
ls: /home/oracle/oradata/testdb/ts_*: 그런 파일이나 디렉토리가 없음

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/oradata/testdb/ts_a01.dbf'


SQL> !cp /data/backup/open/ts_a01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_b01.dbf /home/oracle/oradata/testdb/

SQL> !cp /data/backup/open/ts_c01.dbf /home/oracle/oradata/testdb/

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from test5;

        NO
----------
         5

SQL> select * from test6;

        NO
----------
         6

SQL> select * from test7;

        NO
----------
         7

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Incomplete Recovery - Time Base 연습문제 1  (0) 2010.03.10
Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
No Archive log mode Recovery 연습문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27

No Archive log mode Recovery 연습문제

Oracle 10g/10g - 연습문제
posted by I유령I 2010. 3. 9. 10:53
장애 상황
No archive log mode로 DB 운영 중 갑자기 데이터 파일이 삭제되면서 DB가 강제 종료되는 현상이 발생했다.
확인 결과 Data file, Control file, Redo log file이 저장되어있는 디스크 장애였다.
현재 Backup file은 아래와 같이 저장되어 있으며 긴급 복구를 해야하는 상황이다.
삭제된 파일은 users01.dbf 이다.

복구 경로 (아래의 경로에 각 파일들이 위치하도록 복구)
- Data file : /data/temp/data/*.dbf
- Redo log file : /data/temp/log/*.log
- Control file : /data/temp/control/*.ctl

[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 10 02:52:47 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
#Data file, Control file, Redo log file이 저장되어 있는 디스크 장애이므로 모든 파일을 삭제한다.
SQL> !rm -rf /home/oracle/oradata/testdb/*

#디스크 장애가 발생하면서 DB가 강제 종료되는 상황을 만들기 위해 shutdown abort; 명령어를 사용한다.
SQL> shutdown abort;
ORACLE instance shut down.
#Control file이 삭제되어 아래와 같이 Control file 관련 에러가 발생하며 alert log를 확인하라는 에러 메시지가 출력된다.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

#복구를 위해 일단 shutdown 한다.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
#연습문제 내용과 같이 복구 경로를 지정하기 위해 디렉토리를 생성하고 각 디렉토리로 복사한다.
SQL> !mkdir -p /data/temp/data /data/temp/log /data/temp/control

SQL> !cp -av /data/backup/close/*.dbf /data/temp/data/
`/data/backup/close/example01.dbf' -> `/data/temp/data/example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `/data/temp/data/sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `/data/temp/data/system01.dbf'
`/data/backup/close/temp01.dbf' -> `/data/temp/data/temp01.dbf'
`/data/backup/close/undotbs01.dbf' -> `/data/temp/data/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/data/temp/data/users01.dbf'

SQL> !cp -av /data/backup/close/*.log /data/temp/log/ 
`/data/backup/close/redo01.log' -> `/data/temp/log/redo01.log'
`/data/backup/close/redo02.log' -> `/data/temp/log/redo02.log'
`/data/backup/close/redo03.log' -> `/data/temp/log/redo03.log'

SQL> !cp -av /data/backup/close/*.ctl /data/temp/control/
`/data/backup/close/control01.ctl' -> `/data/temp/control/control01.ctl'
`/data/backup/close/control02.ctl' -> `/data/temp/control/control02.ctl'
`/data/backup/close/control03.ctl' -> `/data/temp/control/control03.ctl'

#Control file을 우선 복구하기 위해 Instance를 nomount 단계로 시작한다.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
#Server Parameter file에서 현재 복구할 Control file의 경로를 변경한다.
#기존 내용
#*.control_files='/home/oracle/oradata/testdb/control01.ctl',
#'/home/oracle/oradata/testdb/control02.ctl',
#'/home/oracle/oradata/testdb/control03.ctl'

#pfile 사용시
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora
*.control_files='/data/temp/control/control01.ctl',
'/data/temp/control/control02.ctl',
'/data/temp/control/control03.ctl'

#spfile 사용시
SQL> alter system set control_files='/data/temp/control/control01.ctl',
  2  '/data/temp/control/control02.ctl',
  3  '/data/temp/control/control03.ctl'
  4  scope=spfile;

System altered.

#spfile의 변경된 내용을 적용하고 Data file, Redo log file의 복구를 위해 Instance를 재 시작한다.
 위해 Instance를 mount 단계로 재 시작한다.
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
#Data file, Redo log file을 복구하기 위해 Control file의 정보를 변경한다.
SQL> alter database rename        
  2  file '/home/oracle/oradata/testdb/system01.dbf'
  3  to '/data/temp/data/system01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/sysaux01.dbf'
  3  to '/data/temp/data/sysaux01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/undotbs01.dbf'
  3  to '/data/temp/data/undotbs01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/users01.dbf'
  3  to '/data/temp/data/users01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/example01.dbf'
  3  to '/data/temp/data/example01.dbf';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo01.log'
  3  to '/data/temp/log/redo01.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo02.log'
  3  to '/data/temp/log/redo02.log';

Database altered.

SQL> alter database rename
  2  file '/home/oracle/oradata/testdb/redo03.log'
  3  to '/data/temp/log/redo03.log';

Database altered.

SQL> alter database open;

Database altered.

#복구 여부를 확인한다.
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME
-------------------- ---------- --------------------------------------------------
USERS                         5 /data/temp/data/users01.dbf
SYSAUX                      240 /data/temp/data/sysaux01.dbf
UNDOTBS1                     35 /data/temp/data/undotbs01.dbf
SYSTEM                      480 /data/temp/data/system01.dbf
EXAMPLE                     100 /data/temp/data/example01.dbf

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group# = b.group#
  4  order by a.group#, a.member;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /data/temp/log/redo01.log                                  50 NO  INACTIVE
         2 /data/temp/log/redo02.log                                  50 NO  INACTIVE
         3 /data/temp/log/redo03.log                                  50 NO  CURRENT

SQL> 
저작자표시 비영리 변경금지 (새창열림)

'Oracle 10g > 10g - 연습문제' 카테고리의 다른 글

Complete Recovery (다른 경로에 응급 복구)  (0) 2010.03.10
Complete Recovery 연습 문제  (0) 2010.03.09
종합 연습문제 (디스크 추가, Controlfile, Redo log file, Tablespace)  (0) 2010.02.28
종합 연습문제 (Controlfile, Redo log file, Tablespace)  (0) 2010.02.27
Controlfile, Redo log files 재구성 #2  (0) 2010.02.25

티스토리툴바