ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 应用开发 > IT综合 > COLD BACKUP(五)

COLD BACKUP(五)

原创 IT综合 作者：HuiYiSky 时间：2007-05-09 20:10:01 0 删除 编辑
不完全恢复![@more@]
-- 丟失數据庫對象(table)，基於時間點恢复（一般是在其它机器上恢复後導入到正式庫）


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

Table created.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 01:16:10

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

-- 繼續Insert
SYS@huiyi>insert into t01 values(sysdate);

1 row created.

SYS@huiyi>/

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 01:16:10
2007-04-18 01:28:12
2007-04-18 01:28:13

-- 還原的時間
SYS@huiyi>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') col01 from dual;

COL01
-------------------
2007-04-18 01:28:47

-- 誤刪除表
SYS@huiyi>drop table t01;

Table dropped.

-- 歸檔日誌
SYS@huiyi>alter system switch logfile;

System altered.

SYS@huiyi>/

System altered.

SYS@huiyi>/

System altered.

-- 還原數据文件
SYS@huiyi>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[huiyi.local@huiyi sh]$ cp /backup/cold/*.dbf $ORACLE_BASE/oradata/huiyi/
[huiyi.local@huiyi sh]$ chmod 666 $ORACLE_BASE/oradata/huiyi/*.dbf

-- 恢复
@>startup mount
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.

SYS@huiyi>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                       CHANGE# TIME
---------- ------- ------- ---------------------------------------- ---------- -------------------
         1 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         2 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         3 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         4 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         5 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         6 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         7 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         8 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
         9 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
        10 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58
        11 ONLINE  ONLINE                                               293793 2007-04-18 01:23:58

11 rows selected.

SYS@huiyi>select file#,last_change#,last_time from v$datafile;

     FILE# LAST_CHANGE# LAST_TIME
---------- ------------ -------------------
         1       298780 2007-04-18 01:49:33
         2       298780 2007-04-18 01:49:33
         3       298780 2007-04-18 01:49:33
         4       298780 2007-04-18 01:49:33
         5       298780 2007-04-18 01:49:33
         6       298780 2007-04-18 01:49:33
         7       298780 2007-04-18 01:49:33
         8       298780 2007-04-18 01:49:33
         9       298780 2007-04-18 01:49:33
        10       298780 2007-04-18 01:49:33
        11       298780 2007-04-18 01:49:33

11 rows selected.
SYS@huiyi>select name,first_change#,first_time from v$archived_log;

NAME                                               FIRST_CHANGE# FIRST_TIME
-------------------------------------------------- ------------- -------------------
/backup/archive/1_3.arc                                   217037 2007-04-16 17:02:22
/backup/archive/1_4.arc                                   217823 2007-04-16 17:07:16
/backup/archive/1_5.arc                                   242255 2007-04-17 23:32:34
/backup/archive/1_6.arc                                   265988 2007-04-18 00:39:59
/backup/archive/1_7.arc                                   288938 2007-04-18 01:00:06
/backup/archive/1_8.arc                                   296098 2007-04-18 01:37:29
/backup/archive/1_9.arc                                   296100 2007-04-18 01:37:30

7 rows selected.

v$recover_file:  記錄的是實際文件的信息
v$datafile:       控制文件中的信息
v$archived_log:  歸檔的信息
相對於恢复時間2007-04-18 01:28:47以及實際數据文件2007-04-18 01:23:58
可以得出恢复將從/backup/archive/1_7.arc一個文件中恢复

SYS@huiyi>recover database until time '2007-04-18 01:28:47';
ORA-00279: change 293793 generated at 04/18/2007 01:23:58 needed for thread 1
ORA-00289: suggestion : /backup/archive/1_7.arc
ORA-00280: change 293793 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 01:16:10
2007-04-18 01:28:12
2007-04-18 01:28:13

-- 查看alert.log
ALTER DATABASE RECOVER  database until time '2007-04-18 01:28:47'
Wed Apr 18 02:05:48 2007
Media Recovery Start
Wed Apr 18 02:05:48 2007
Restarting dead background process QMN0
Wed Apr 18 02:05:48 2007
Starting datafile 1 recovery in thread 1 sequence 7
Datafile 1: '/opt/oracle/oradata/huiyi/system01.dbf'
Starting datafile 2 recovery in thread 1 sequence 7
Datafile 2: '/opt/oracle/oradata/huiyi/undotbs01.dbf'
Starting datafile 3 recovery in thread 1 sequence 7
Datafile 3: '/opt/oracle/oradata/huiyi/drsys01.dbf'
Starting datafile 4 recovery in thread 1 sequence 7
Datafile 4: '/opt/oracle/oradata/huiyi/example01.dbf'
Starting datafile 5 recovery in thread 1 sequence 7
Datafile 5: '/opt/oracle/oradata/huiyi/indx01.dbf'
Starting datafile 6 recovery in thread 1 sequence 7
Datafile 6: '/opt/oracle/oradata/huiyi/odm01.dbf'
Starting datafile 7 recovery in thread 1 sequence 7
Datafile 7: '/opt/oracle/oradata/huiyi/tools01.dbf'
Starting datafile 8 recovery in thread 1 sequence 7
Datafile 8: '/opt/oracle/oradata/huiyi/users01.dbf'
Starting datafile 9 recovery in thread 1 sequence 7
Datafile 9: '/opt/oracle/oradata/huiyi/xdb01.dbf'
Starting datafile 10 recovery in thread 1 sequence 7
Datafile 10: '/opt/oracle/oradata/huiyi/tbs01-01.dbf'
Starting datafile 11 recovery in thread 1 sequence 7
Datafile 11: '/opt/oracle/oradata/huiyi/tbs01-02.dbf'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER  database until time '2007-...
QMN0 started with pid=9
Wed Apr 18 02:05:54 2007
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 18 02:05:54 2007
Media Recovery Log /backup/archive/1_7.arc
Terminal Recovery: done UNTIL CHANGE 294178
Media Recovery Complete




-- 誤刪表空間，基於時間的恢复(using Backup Controlfile)


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

Table created.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 04:35:21

-- 備份
SYS@huiyi>host
[huiyi.local@huiyi sh]$ pwd
/user/huiyi.local/sh
[huiyi.local@huiyi sh]$ sh cold_backup.sh

-- 繼續Insert
SYS@huiyi>insert into t01 values(sysdate);

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 04:35:21
2007-04-18 04:42:11

-- 誤刪
SYS@huiyi>drop tablespace tbs01 including contents and datafiles;

Tablespace dropped.

-- 還原(數据文件，控制文件)
SYS@huiyi>alter system switch logfile;

System altered.

SYS@huiyi>/

System altered.

SYS@huiyi>/

System altered.

SYS@huiyi>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@huiyi>host
[huiyi.local@huiyi sh]$ cp /backup/cold/*.dbf $ORACLE_BASE/oradata/huiyi/
[huiyi.local@huiyi sh]$ cp /backup/cold/*.ctl $ORACLE_BASE/oradata/huiyi/

-- 啟動
SYS@huiyi>startup mount
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.

-- 恢复
alert_log
Wed Apr 18 04:44:19 2007
drop tablespace tbs01 including contents and datafiles

SYS@huiyi>recover database until time '2007-04-18 04:44:19' using backup controlfile;
ORA-00279: change 326132 generated at 04/18/2007 04:38:10 needed for thread 1
ORA-00289: suggestion : /backup/archive/1_1.arc
ORA-00280: change 326132 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SYS@huiyi>alter database open resetlogs;


Database altered.

SYS@huiyi>SYS@huiyi>select * from t01;

COL01
-------------------
2007-04-18 04:35:21
2007-04-18 04:42:11




-- 歸檔日誌損坏，基於取消的恢复


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

Table created.

SYS@huiyi>select * from t;

COL01
-------------------
2007-04-18 08:43:04

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

-- 繼續Insert
SYS@huiyi>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup/archive
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SYS@huiyi>select * from t;

COL01
-------------------
2007-04-18 08:43:04

-- Archive log sequence 2
SYS@huiyi>insert into t values(sysdate);

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>alter system switch logfile;

System altered.

-- Archive log sequence 3
SYS@huiyi>insert into t values(sysdate);

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>alter system switch logfile;

System altered.

-- Archive log sequence 4
SYS@huiyi>insert into t values(sysdate);

1 row created.

SYS@huiyi>commit;

Commit complete.

SYS@huiyi>alter system switch logfile;

System altered.

SYS@huiyi>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /backup/archive
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SYS@huiyi>select * from t;

COL01
-------------------
2007-04-18 08:43:04
2007-04-18 08:51:01
2007-04-18 08:51:55
2007-04-18 08:52:07

-- 還原
SYS@huiyi>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[huiyi.local@huiyi backup]$ cp /backup/cold/*.dbf $ORACLE_BASE/oradata/huiyi/

-- 破坏(1_3.arc)
SYS@huiyi>startup mount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 byte

SYS@huiyi>host ls /backup/archive -l
total 1168
-rw-r-----  1 oracle oinstall 1095680 Apr 18 08:51 1_2.arc
-rw-r-----  1 oracle oinstall   69632 Apr 18 08:52 1_3.arc
-rw-r-----  1 oracle oinstall    4608 Apr 18 08:52 1_4.arc

SYS@huiyi>host mv /backup/archive/1_3.arc /backup/archive/1_3.tmp

SYS@huiyi>host ls /backup/archive -l
total 1168
-rw-r-----  1 oracle oinstall 1095680 Apr 18 08:51 1_2.arc
-rw-r-----  1 oracle oinstall   69632 Apr 18 08:52 1_3.tmp
-rw-r-----  1 oracle oinstall    4608 Apr 18 08:52 1_4.arc

-- 恢复
SYS@huiyi>recover database until cancel
ORA-00279: change 208304 generated at 04/18/2007 08:45:15 needed for thread 1
ORA-00289: suggestion : /backup/archive/1_2.arc
ORA-00280: change 208304 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 209118 generated at 04/18/2007 08:51:10 needed for thread 1
ORA-00289: suggestion : /backup/archive/1_3.arc
ORA-00280: change 209118 for thread 1 is in sequence #3
ORA-00278: log file '/backup/archive/1_2.arc' no longer needed for this recovery


ORA-00308: cannot open archived log '/backup/archive/1_3.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SYS@huiyi>alter database open resetlogs;

Database altered.

SYS@huiyi>select * from t;

COL01
-------------------
2007-04-18 08:43:04
2007-04-18 08:51:01

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8799875/viewspace-914233/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： COLD BACKUP(四)
下一篇： Hot Backup(一)
请登录后发表评论 登录
全部评论
HuiYiSky

注册时间：2007-12-14

  • 博文量
    39
  • 访问量
    338054

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593