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