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

#시나리오

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>