Redo log file Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 12. 11:16
1. 실습 환경 설정
실습을 위해 아래와 같이 Redo log group 및 member 을 다중화 한다.

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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED
         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED
         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.


2. Group의 한 Member 1개 장애 (Active, Inactive 공통)
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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES UNUSED
         2 /home/oracle/disk5/redo02_b.log                             5 YES UNUSED
         3 /home/oracle/disk4/redo03_a.log                             5 YES UNUSED
         3 /home/oracle/disk5/redo03_b.log                             5 YES UNUSED

6 rows selected.

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 YES INACTIVE
         1 /home/oracle/disk5/redo01_b.log                             5 YES INACTIVE
         2 /home/oracle/disk4/redo02_a.log                             5 NO  CURRENT
         2 /home/oracle/disk5/redo02_b.log                             5 NO  CURRENT
         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> alter database drop logfile member '/home/oracle/disk5/redo01_b.log';

Database altered.

SQL> alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES ACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES ACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 YES ACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES ACTIVE

6 rows selected.

SQL> 

※ alert_testdb.log 내용
Fri Mar 12 11:26:22 2010
Thread 1 advanced to log sequence 7
  Current log# 2 seq# 7 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 7 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:26:22 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:26:22 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thread 1 advanced to log sequence 8
  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
  Current log# 3 seq# 8 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 8 mem# 1: /home/oracle/disk5/redo03_b.log
Fri Mar 12 11:26:32 2010
Thread 1 advanced to log sequence 9
  Current log# 1 seq# 9 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 9 mem# 1: /home/oracle/disk5/redo01_b.log
Fri Mar 12 11:27:18 2010
Thread 1 advanced to log sequence 10
  Current log# 2 seq# 10 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 10 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:27:18 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:18 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc1_3196.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thread 1 advanced to log sequence 11
  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 cannot allocate new log, sequence 12
Checkpoint not complete
  Current log# 3 seq# 11 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 11 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 advanced to log sequence 12
  Current log# 1 seq# 12 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 12 mem# 1: /home/oracle/disk5/redo01_b.log
Fri Mar 12 11:27:30 2010
Thread 1 advanced to log sequence 13
  Current log# 2 seq# 13 mem# 0: /home/oracle/disk4/redo02_a.log
  Current log# 2 seq# 13 mem# 1: /home/oracle/disk5/redo02_b.log
Fri Mar 12 11:27:30 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:30 2010
Errors in file /home/oracle/admin/testdb/bdump/testdb_arc0_3194.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/disk5/redo01_b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 12 11:27:30 2010
Expanded controlfile section 11 from 28 to 205 records
Requested to grow by 177 records; added 7 blocks of records
Fri Mar 12 11:27:52 2010
alter database drop logfile member '/home/oracle/disk5/redo01_b.log'
Fri Mar 12 11:27:52 2010
Completed: alter database drop logfile member '/home/oracle/disk5/redo01_b.log'
Fri Mar 12 11:28:12 2010
alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1
Fri Mar 12 11:28:12 2010
Completed: alter database add logfile member '/home/oracle/disk5/redo01_b.log' to group 1
Fri Mar 12 11:28:27 2010
Thread 1 advanced to log sequence 14
  Current log# 3 seq# 14 mem# 0: /home/oracle/disk4/redo03_a.log
  Current log# 3 seq# 14 mem# 1: /home/oracle/disk5/redo03_b.log
Thread 1 advanced to log sequence 15
  Current log# 1 seq# 15 mem# 0: /home/oracle/disk4/redo01_a.log
  Current log# 1 seq# 15 mem# 1: /home/oracle/disk5/redo01_b.log


3. Inactive한 Group 장애
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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 YES INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 YES INACTIVE

6 rows selected.

SQL> !rm -rf /home/oracle/disk4/redo03_a.log

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 YES INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 YES INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance testdb (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/home/oracle/disk4/redo03_a.log'
ORA-00312: online log 3 thread 1: '/home/oracle/disk5/redo03_b.log'


SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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 1, 2;

    GROUP# MEMBER                                                     MB ARC STATUS
---------- -------------------------------------------------- ---------- --- ----------------
         1 /home/oracle/disk4/redo01_a.log                             5 NO  CURRENT
         1 /home/oracle/disk5/redo01_b.log                             5 NO  CURRENT
         2 /home/oracle/disk4/redo02_a.log                             5 NO  INACTIVE
         2 /home/oracle/disk5/redo02_b.log                             5 NO  INACTIVE
         3 /home/oracle/disk4/redo03_a.log                             5 NO  INACTIVE
         3 /home/oracle/disk5/redo03_b.log                             5 NO  INACTIVE

6 rows selected.

SQL> 


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

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

Export / Import - Clone DB로 삭제된 데이터 복구하는 방법  (0) 2010.03.16
Control file 장애  (0) 2010.03.15
Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09

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 - using backup controlfile

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 11. 12:36
실습 순서
1. Tablespace 생성 후 DB 전체를 Backup 한다.
2. test 테이블을 1번에서 생성한 tablespace에 생성한다.
3. drop tablespace로 1번에서 생성한 tablespace를 삭제한다.
4. 로그스위치를 수차례 발생시킨다.
5. 1번 과정에서 Backup 받았던 Data files과 Control files를 복원한다.
6. alert_testdb.log 파일에서 3번 작업을 수행했던 시간을 확인한다.
7. DB를 mount 단계로 시작한다.
8. recover database until time 'YYYY-MM-DD:HH24:MI:SS' using backup controlfile; 실행한다.
9. 8번 실행 후 alter database open resetlogs; 실행한다.
10. 복구가 완료 되었는지 확인한다.

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

Table created.

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

1 row created.

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

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from scott.test;

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

SQL> drop tablespace ts_c including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

#복구 중 실수로 문제가 발생한 DB 이외에 기존 DB 까지 사용하지 못하는 경우가 발생될 수 있다.
#따라서 문제가 발생한 Data file까지 포함해 모든 Data file, Redo log file, Control file까지 Backup을 받아놓는다.
SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/temp
SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/temp

#위와 같이 Data file, Redo log file, Control file까지 Backup을 받아놓은 후 아래와 같이 복구를 시작한다.
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> !cp -av /data/backup/cr_close/*.ctl /home/oracle/oradata/testdb/
`/data/backup/cr_close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'
`/data/backup/cr_close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'
`/data/backup/cr_close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> startup mount;
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.

#alert_testdb.log 에서 drop tablespace ts_c including contents and datafiles 작업 수행 시간을 확인한다.
#/home/oracle/admin/testdb/bdump/alert_testdb.log 내용 중 일부
Thu Mar 11 12:27:55 2010
drop tablespace ts_c including contents and datafiles
Thu Mar 11 12:27:58 2010
Deleted file /home/oracle/oradata/testdb/ts_c01.dbf
Completed: drop tablespace ts_c including contents and datafiles

SQL> recover database until time '2010-03-11:12:27:50' using backup controlfile;
ORA-00279: change 502969 generated at 03/11/2010 11:14:17 needed for thread 1
ORA-00289: suggestion : /data/arc2/4_1_710077424.arc
ORA-00280: change 502969 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test;

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

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

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

Control file 장애  (0) 2010.03.15
Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - Time Base  (0) 2010.03.10
Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08

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

Incomplete Recovery - Time Base

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 10. 11:00

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

Table created.

SQL> insert into test7 values (1, sysdate);

1 row created.

SQL> insert into test7 values (2, sysdate);

1 row created.

SQL> insert into test7 values (3, sysdate);

1 row created.

SQL> insert into test7 values (4, sysdate);

1 row created.

SQL> insert into test7 values (5, sysdate);

1 row created.

#commit 하지 않은 insert data는 복구가 불가능하다.
SQL> commit;

Commit complete.

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

SYSDATE
-------------------
2010-03-10:10:58:07

SQL> drop table test7;

Table dropped.

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


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

SQL> !cp -av /data/backup/open/*.dbf /data/temp_time/
`/data/backup/open/example01.dbf' -> `/data/temp_time/example01.dbf'
`/data/backup/open/sysaux01.dbf' -> `/data/temp_time/sysaux01.dbf'
`/data/backup/open/system01.dbf' -> `/data/temp_time/system01.dbf'
`/data/backup/open/ts_a01.dbf' -> `/data/temp_time/ts_a01.dbf'
`/data/backup/open/ts_b01.dbf' -> `/data/temp_time/ts_b01.dbf'
`/data/backup/open/ts_c01.dbf' -> `/data/temp_time/ts_c01.dbf'
`/data/backup/open/undotbs01.dbf' -> `/data/temp_time/undotbs01.dbf'
`/data/backup/open/users01.dbf' -> `/data/temp_time/users01.dbf'

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             113247824 bytes
Database Buffers          167772160 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

8 rows selected.

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

Database altered.

SQL> 

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/data/temp_time/system01.dbf
/data/temp_time/undotbs01.dbf
/data/temp_time/sysaux01.dbf
/data/temp_time/users01.dbf
/data/temp_time/example01.dbf
/data/temp_time/ts_a01.dbf
/data/temp_time/ts_b01.dbf
/data/temp_time/ts_c01.dbf

8 rows selected.

SQL> recover database until time '2010-03-10:10:58:07';
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

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

        NO HIREDATE
---------- -------------------
         1 2010-03-10:10:56:18
         2 2010-03-10:10:56:20
         3 2010-03-10:10:56:24
         4 2010-03-10:10:56:26
         5 2010-03-10:10:56:28

SQL> 


#실습문제 설명
10:01:00 insert into test7 values (1, sysdate);
10:02:00 insert into test7 values (2, sysdate);
10:03:00 insert into test7 values (3, sysdate);
10:03:30 commit;
10:04:00 insert into test7 values (4, sysdate);
10:05:00 insert into test7 values (5, sysdate);

recover database until time '2010-03-10:10:03:15';
-> commit; 이전으로 복구하는 부분이기 때문에 모든 데이터는 복구되지 않는다.
recover database until time '2010-03-10:10:03:45';
-> commot; 이전 1, 2, 3 데이터만 복구가 되며, commit; 이후 데이터는 복구되지 않는다.

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

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

Redo log file Recovery  (0) 2010.03.12
Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Archive log mode Complete Recovery  (0) 2010.03.09
No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08

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

Archive log mode Complete Recovery

Oracle 10g/10g - 실습
posted by I유령I 2010. 3. 9. 12:09
Non System Tablespace Recovery

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> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/arc2
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL> create tablespace ts_a
  2  datafile '/home/oracle/oradata/testdb/ts_a01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_b
  2  datafile '/home/oracle/oradata/testdb/ts_b01.dbf' size 5m;

Tablespace created.

SQL> create tablespace ts_c
  2  datafile '/home/oracle/oradata/testdb/ts_c01.dbf' size 5m;

Tablespace created.

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> create table test1 (no number) tablespace ts_c;

Table created.

SQL> insert into test1 values (1);

1 row created.

SQL> insert into test1 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

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

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.

SQL> alter database backup controlfile to '/data/backup/open/control01.ctl';

Database altered.

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

SQL> alter tablespace ts_c offline;

Tablespace altered.

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


SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/home/oracle/oradata/testdb/ts_c01.dbf'


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                            /home/oracle/oradata/testdb/ts_c01.dbf

8 rows selected.

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

SQL> recover tablespace ts_c;
Media recovery complete.
SQL> alter tablespace ts_c online;

Tablespace 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> select * from test1;

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

SQL> 



System Tablespace Recovery

#1 recover database;
SQL> create table test3 (no number) tablespace system;

Table created.

SQL> insert into test3 values (1);

1 row created.

SQL> insert into test3 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

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

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

SQL> !ls /home/oracle/oradata/testdb/system01.dbf    
ls: /home/oracle/oradata/testdb/system01.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 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


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

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

Database altered.

SQL> select * from test3;

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

SQL> 


#2 recover tablespace tablespace_name;
SQL> truncate table test3;

Table truncated.

SQL> select * from test3;

no rows selected

SQL> insert into test3 values (3);

1 row created.

SQL> insert into test3 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO
----------
         3
         4

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

SQL> !ls /home/oracle/oradata/testdb/system01.dbf
ls: /home/oracle/oradata/testdb/system01.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 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


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

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

Database altered.

SQL> select * from test3;

        NO
----------
         3
         4

SQL> 

#3 recover datafile '/home/oracle/oradata/testdb/system01.dbf';
SQL> truncate table test3;

Table truncated.

SQL> insert into test3 values (5);

1 row created.

SQL> insert into test3 values (6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO
----------
         5
         6

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

SQL> !ls /home/oracle/oradata/testdb/system01.dbf
ls: /home/oracle/oradata/testdb/system01.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 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'


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

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

Database altered.

SQL> select * from test3;

        NO
----------
         5
         6

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

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

Incomplete Recovery - using backup controlfile  (0) 2010.03.11
Incomplete Recovery - Time Base  (0) 2010.03.10
No Archive log mode Recovery  (0) 2010.03.08
Cold Backup & Hot Backup  (0) 2010.03.08
Archive log mode 설정 및 활성화/비활성화  (0) 2010.03.05

티스토리툴바