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

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

完全恢复的条件：

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

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

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

一、            首先做热备份

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26521853/viewspace-1083933/