'recover database until time using backup controlfile'에 해당되는 글 2건

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

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

티스토리툴바