-- 完全恢复(Windows)

-- 測試數据
SYS@HUIYI>create table t01 tablespace tbs01 as select sysdate col01 from dual;

Table created.

SYS@HUIYI>create table t02 tablespace users as select sysdate col01 from dual;

Table created.

SYS@HUIYI>select * from t01;

COL01
-------------------
2007-04-16 13:57:03

SYS@HUIYI>select * from t02;

COL01
-------------------
2007-04-16 13:57:12

-- 備份
SYS@HUIYI>@cold_backup

-- 繼續Insert數据(利用重做來恢复)
SYS@HUIYI>insert into t01 values(sysdate);

1 row created.

SYS@HUIYI>insert into t02 values(sysdate);

1 row created.

SYS@HUIYI>commit;

Commit complete.

SYS@HUIYI>select * from t01;

COL01
-------------------
2007-04-16 13:57:03
2007-04-16 14:00:06

SYS@HUIYI>select * from t02;

COL01
-------------------
2007-04-16 13:57:12
2007-04-16 14:00:10

-- 破坏
SYS@HUIYI>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@HUIYI>host del D:oracleoradataHUIYItbs*

-- 恢复
SYS@HUIYI>startup
ORACLE instance started.

Total System Global Area  147921840 bytes
Fixed Size                   453552 bytes
Variable Size             109051904 bytes
Database Buffers           37748736 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: 'D:ORACLEORADATAHUIYITBS01-01.DBF'


SYS@HUIYI>alter database datafile 10 offline drop;

Database altered.

SYS@HUIYI>alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'D:ORACLEORADATAHUIYITBS01-02.DBF'


SYS@HUIYI>alter database datafile 11 offline drop;

Database altered.

SYS@HUIYI>alter database open;

Database altered.

SYS@HUIYI>select * from t01;
select * from t01
              *
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: 'D:ORACLEORADATAHUIYITBS01-02.DBF'


SYS@HUIYI>select * from t02;

COL01
-----------
16-Apr-2007
16-Apr-2007

SYS@HUIYI>host copy d:backupcoldtbs* %oracle_base%oradatahuiyi
d:backupcoldTBS01-01.DBF
d:backupcoldTBS01-02.DBF
        2 file(s) copied.

SYS@HUIYI>recover datafile 10;
Media recovery complete.
SYS@HUIYI>recover datafile 11;
Media recovery complete.
SYS@HUIYI>alter database datafile 10 online;

Database altered.

SYS@HUIYI>alter database datafile 11 online;

Database altered.

SYS@HUIYI>select * from t01;

COL01
-------------------
2007-04-16 13:57:03
2007-04-16 14:00:06

SYS@HUIYI>select * from t02;

COL01
-------------------
2007-04-16 13:57:12
2007-04-16 14:00:10



-- 完全恢复(Linux)

-- 測試數据
SYS@huiyi>create table t01 tablespace tbs01 as select sysdate col01 from dual;

Table created.

SYS@huiyi>create table t02 tablespace users as select sysdate col02 from dual;

Table created.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 00:48:59

SYS@huiyi>select * from t02;

COL02
-------------------
2007-04-18 00:49:15

-- 備份
[huiyi.local@huiyi sh]$ sh cold_backup.sh

-- 重新Insert數据(利用重做來恢复)
SYS@huiyi>insert into t01 select sysdate from dual;

1 row created.

SYS@huiyi>/

1 row created.

SYS@huiyi>insert into t02 select sysdate from dual;

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 00:48:59
2007-04-18 00:56:38
2007-04-18 00:56:45

SYS@huiyi>select * from t02;

COL02
-------------------
2007-04-18 00:49:15
2007-04-18 00:57:05

-- 破坏
[huiyi.local@huiyi sh]$ rm $ORACLE_BASE/oradata/huiyi/tbs*.dbf

-- 啟動
SYS@huiyi>startup force
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/opt/oracle/oradata/huiyi/tbs01-01.dbf'

-- 恢复
SYS@huiyi>alter database datafile 10 offline drop;

Database altered.

SYS@huiyi>alter database datafile 11 offline drop;

Database altered.

SYS@huiyi>alter database open;

Database altered.

SYS@huiyi>host cp /backup/cold/tbs*.dbf $ORACLE_BASE/oradata/huiyi/

SYS@huiyi>host chmod 666 $ORACLE_BASE/oradata/huiyi/tbs*.dbf

SYS@huiyi>recover datafile 10;
Media recovery complete.
SYS@huiyi>recover datafile 11;
Media recovery complete.
SYS@huiyi>alter database datafile 10 online;

Database altered.

SYS@huiyi>alter database datafile 11 online;

Database altered.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 00:48:59
2007-04-18 00:56:38
2007-04-18 00:56:45

SYS@huiyi>select * from t02;

COL02
-------------------
2007-04-18 00:49:15
2007-04-18 00:57:05