'No Archive log mode Recovery'에 해당되는 글 3건

  1. 2010.03.15 Export / Import 종합 연습 문제 #2
  2. 2010.03.09 No Archive log mode Recovery 연습문제
  3. 2010.03.08 No Archive log mode Recovery

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

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

No Archive log mode Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 8. 12:49
No Archive log mode Recovery #1

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

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      OFFLINE   PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:11:10:04
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:11:11:07
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:11:10:38
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:11:12:16

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

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

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

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> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE STATUS    CONTENTS
---------- --------- ---------
SYSTEM     ONLINE    PERMANENT
UNDOTBS1   ONLINE    UNDO
SYSAUX     ONLINE    PERMANENT
TEMP       ONLINE    TEMPORARY
USERS      ONLINE    PERMANENT
EXAMPLE    ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #2 (해당 Tablespace가 지워져도 괜찮을 경우에 한하여 사용한다.)
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.
Database opened.
SQL> col name format a50
SQL> select tablespace_name, status, contents from dba_tablespaces;                      

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         4 /home/oracle/oradata/testdb/users01.dbf            NOT ACTIVE         2010-03-08:10:55:11
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

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

SQL> shutdown abort;
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 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oradata/testdb/users01.dbf'


SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name, status, contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT

6 rows selected.

SQL> select a.file#, a.name, b.status, TO_CHAR(b.time, 'YYYY-MM-DD:HH24:MI:SS') as time
  2  from v$datafile a, v$backup b
  3  where a.file# = b.file#;

     FILE# NAME                                               STATUS             TIME
---------- -------------------------------------------------- ------------------ -------------------
         1 /home/oracle/oradata/testdb/system01.dbf           NOT ACTIVE         2010-03-08:10:54:39
         2 /home/oracle/oradata/testdb/undotbs01.dbf          NOT ACTIVE         2010-03-08:10:55:09
         3 /home/oracle/oradata/testdb/sysaux01.dbf           NOT ACTIVE         2010-03-08:10:54:58
         5 /home/oracle/oradata/testdb/example01.dbf          NOT ACTIVE         2010-03-08:10:55:11

SQL> 


No Archive log mode Recovery #3
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.
Database opened.
SQL> !rm -rf /home/oracle/oradata/testdb/*

SQL> !ls /home/oracle/oradata/testdb/

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir -p /data/temp 

SQL> !cp -av /data/close/*.dbf /data/temp/
cp: cannot stat `/data/close/*.dbf': 그런 파일이나 디렉토리가 없음

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

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

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

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.
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

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

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                                     /data/temp/users01.dbf
SYSAUX                                240 /data/temp/sysaux01.dbf
UNDOTBS1                               35 /data/temp/undotbs01.dbf
SYSTEM                                480 /data/temp/system01.dbf
EXAMPLE                               100 /data/temp/example01.dbf

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

'Oracle 10g > 10g - 실습' 카테고리의 다른 글

Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05
Privileges(권한) 관리  (0) 2010.03.05

티스토리툴바