ITPub博客

首页 > 数据库 > Oracle > 基于归档的热备份完全恢复

基于归档的热备份完全恢复

原创 Oracle 作者:nathanzhn 时间:2014-02-19 15:47:12 0 删除 编辑

原理与基于归档的冷备份完全恢复基本相同,场景为某数据文件损坏或丢失,需要做归档模式下的完全恢复:

完全恢复的条件:

有最近的一次数据文件备份

控制文件和联机日志文件没有损坏

从备份时间开始到数据文件损坏时的所有归档日志文件完好

一、            首先做热备份

Dfd

SYS@PROD>alter database begin backup;

 

Database altered.

SYS@PROD>! cp /u01/app/oracle/oradata/PROD/disk1/system001.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/example1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/indx1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/tools1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/users1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf /home/oracle/hotbackup/20140125-1

SYS@PROD>alter database end backup;

 

Database altered.

二、            以下三-六为四种恢复场景及其对应的恢复步骤,主要故障为表空间tbs_tommie对应的数据文件tbs_tommie01.dbf被删除了,数据库启动报错

 [oracle@odd-oelr4u8 disk1]$ mv tbs_tommie01.dbf tbs_tommie01.dbf.bak

SYS@PROD>startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              75498896 bytes

Database Buffers          234881024 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

 

三、            关闭数据库时恢复

a)        在数据库mount状态下恢复数据库文件,该方法可以恢复system、undo 或整个数据库。

b)        恢复步骤:

                        i.              还原所有归档日志-归档日志可能还在归档目录下,也可能被备份到了其他目录,而归档目录下的归档已被清除,此时需要还原

                      ii.              装入数据库-startup mount

                    iii.              还原损坏数据文件-cp 某datafile

                     iv.              恢复数据文件(数据库)-recover AUTOMATIC DATAFILE ‘.dbf’或recover database;

                       v.              打开数据库-alter database open;

Fdd

SYS@PROD>select open_mode from v$database;

 

OPEN_MODE

--------------------

MOUNTED

 

[oracle@odd-oelr4u8 disk1]$ cp /home/oracle/hotbackup/20140125-1/tbs_tommie01.dbf /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf

SYS@PROD>recover datafile 9;

Media recovery complete.

 

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>conn timmie/timmie

Connected.

TIMMIE@PROD>select * from t1;

 

         A

----------

         1

         2

         3

四、            数据库在打开状态下恢复

a)        在数据库open 状态下恢复表空间的数据文件,该方法不可以恢复system、undo或整个数据库。

b)        恢复步骤:

                        i.              还原所有归档日志

                      ii.              将表空间或数据文件脱机

                    iii.              还原损坏数据文件

                     iv.              恢复表空间或数据库文件

                       v.              数据文件或表空间联机

                     vi.              打开数据库

    [oracle@odd-oelr4u8 disk1]$ rm tbs_tommie01.dbf

建表报错数据文件找不到了

TIMMIE@PROD>create table t2(b int);

create table t2(b int)

*

ERROR at line 1:

ORA-01116: error in opening database file 9

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

[oracle@odd-oelr4u8 disk1]$ mv tbs_tommie01.dbf.bak2 tbs_tommie01.dbf

SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf' offline;

SYS@PROD>alter tablespace tbs_tommie offline;

 

SYS@PROD>recover datafile 9;

SYS@PROD>recover tablespace tbs_tommie;

Media recovery complete.

SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf' online;

 

SYS@PROD>alter tablespace tbs_tommie online;

SYS@PROD>conn timmie/timmie

Connected.

TIMMIE@PROD>create table t2(b int);

 

Table created.

五、            在数据库打开后恢复

a)        在数据库mount状态下使损坏数据文件脱机,将数据库打开后再进行恢复

b)        恢复步骤:

                        i.              还原所有归档日志

                      ii.              装入数据库

                    iii.              数据文件脱机

                     iv.              打开数据库

                       v.              还原损坏数据文件

                     vi.              恢复数据库文件

                   vii.              数据文件联机

TIMMIE@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate

ORA-01116: error in opening database file 9

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@PROD>shutdown abort

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SYS@PROD>alter database datafile 9 offline;

 

Database altered.

 

SYS@PROD>alter tablespace tbs_tommie offline;

alter tablespace tbs_tommie offline

*

ERROR at line 1:

ORA-01109: database not open

 

 

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>alter database datafile 9 online;

alter database datafile 9 online

*

ERROR at line 1:

ORA-01113: file 9 needs media recovery

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

 

 

SYS@PROD>recover datafie 9 ;

ORA-00905: missing keyword

 

 

SYS@PROD>recover datafile 9 ;

Media recovery complete.

SYS@PROD>alter database datafile 9 online;

 

Database altered.

六、            恢复没有备份的数据文件

a)        如果数据文件没有备份,而从创建该表空间以来所有日志都存在,可以在打开数据库的状态下进行恢复。

b)        恢复步骤:

                        i.              还原所有归档日志

                      ii.              将表空间或数据文件脱机

                    iii.              创建新的数据文件

                     iv.              恢复数据库文件

                       v.              数据表空间联机

该场景为关机后,数据文件丢失,然后重建,则不需要数据文件脱机和联机的步骤

 

SYS@PROD>create tablespace tbs_amy datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' size 10m;

 

Tablespace created.

 

SYS@PROD>create user amy identified by amy default tablespace tbs_amy;

 

User created.

 

SYS@PROD>grant connect,resource to amy;

 

Grant succeeded.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy01(a int);

 

Table created.

 

 

AMY@PROD>insert into t_amy01 values(10);

 

1 row created.

 

AMY@PROD>commit;

 

Commit complete.

 

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>alter system switch logfile;

 

System altered.

n  数据10进入归档

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>insert into t_amy01 values(20);

 

1 row created.

 

AMY@PROD>commit;

 

Commit complete.

n  数据20在联机日志里

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate –该场景为关机后,数据文件丢失,然后重建

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' as '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

 

Database altered.

 

SYS@PROD>recover datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

Media recovery complete.

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>select * from t_amy01;

 

         A

----------

        10

            20

 

--该场景为数据库打开时数据文件丢失且无备份,然后进行恢复

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy02(b int);

create table t_amy02(b int)

*

ERROR at line 1:

ORA-01116: error in opening database file 10

ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

 

 

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate

ORA-01116: error in opening database file 10

ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@PROD>alter database datafile 10 offline;

 

Database altered.

 

SYS@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' as '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

 

Database altered.

 

SYS@PROD>recover datafile 10;

Media recovery complete.

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>alter database datafile 10 online;

 

Database altered.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy02(b int);

 

Table created.

理解:ORA-01178: file 9 created before last CREATE CONTROLFILE, cannot recreate

在操作datafile 9,表空间tbs_tommie对应的数据文件tbs_tommie01.dbf的时候出现过这个错误,导致无法create datafile。总结一下应该是该datafile 9创建后做过create controlfile的操作。因此如果恢复过数据库,最好还是马上做一下全库备份。

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

下一篇: 楠叔的心声
请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    262689