Complete Recovery 연습 문제

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