非RMAN热备份数据库和恢复

热备必须在归档模式下进行

用logmnr做日志挖掘需要用到最小补充日志

1、添加最小补充日志

idle>alter database add supplemental log data;

 

Database altered.

2、  查询一下数据文件及其表空间

 

idle>select file_name,file_id ,tablespace_name from dba_data_files;

 

FILE_NAME                         FILE_ID TABLESPACE_NAME

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

/u01/app/oracle/oradata/prod/u          4 USERS

sers01.dbf

 

/u01/app/oracle/oradata/prod/u          3 UNDOTBS1

ndotbs01.dbf

 

/u01/app/oracle/oradata/prod/s          2 SYSAUX

ysaux01.dbf

 

/u01/app/oracle/oradata/prod/s          1 SYSTEM

ystem01.dbf

 

/u01/app/oracle/oradata/prod/e          5 EXAMPLE

xample01.dbf

2、  热备份数据文件

 

alter tablespace system begin backup;

! cp /u01/app/oracle/oradata/prod/system01.dbf /software/hot_backup

alter tablespace system end backup;

alter tablespace sysaux begin backup;

! cp /u01/app/oracle/oradata/prod/sysaux01.dbf /software/hot_backup

alter tablespace sysaux end backup;

alter tablespace UNDOTBS1 begin backup;

! cp /u01/app/oracle/oradata/prod/undotbs01.dbf /software/hot_backup

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;

! cp /u01/app/oracle/oradata/prod/users01.dbf /software/hot_backup

alter tablespace USERS end backup;

alter tablespace example begin backup;

! cp /u01/app/oracle/oradata/prod/example01.dbf /software/hot_backup

alter tablespace example end backup;

2、  将控制文件备份出来

idle>alter database backup controlfile to '/software/hot_backup/control01.ctl'

  2  ;

 

Database altered.

因为是开库的状态，检查点在不断的写，所以不能直接拷贝

查看是否备份完成

[root@zhang\:/software/hot_backup]#ls

control01.ctl  example01.dbf  sysaux01.dbf   system01.dbf   undotbs01.dbf  users01.dbf

可见，5个数据文件和一个控制文件均已备份，至此，备份结束

2、  为安全起见，查看是否还有表空间处于热备份锁定状态

idle>select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE            1523197 17-APR-14

         2 NOT ACTIVE            1523224 17-APR-14

         3 NOT ACTIVE            1523248 17-APR-14

         4 NOT ACTIVE            1523268 17-APR-14

         5 ACTIVE                1523286 17-APR-14

可以发现，文件号为5的文件仍然处于热备份锁定状态，此时无法正常关库，非正常关库后也无法正常打开。

解决方法：将该文件所在表空间结束热备份

查询该文件号对应的表空间名和文件名

idle>select file_name ,tablespace_name from dba_data_files where file_id = 5;

 

FILE_NAME                      TABLESPACE_NAME

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

/u01/app/oracle/oradata/prod/e EXAMPLE

xample01.dbf

结束该文件的热备份锁定状态

 

idle>alter tablespace EXAMPLE end backup;

 

Tablespace altered.

查看是否解除热备份锁定

idle>select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE            1523197 17-APR-14

         2 NOT ACTIVE            1523224 17-APR-14

         3 NOT ACTIVE            1523248 17-APR-14

         4 NOT ACTIVE            1523268 17-APR-14

         5 NOT ACTIVE            1523286 17-APR-14

可见均处于Not active状态，至此热备份完全结束。

利用上述备份进行恢复

无论实例打开或关闭均回到mount状态修复，此为低可用性恢复

第一种情况：库中的所有数据文件被误删

[root@zhang\:/u01/app/oracle/oradata/prod]#ls

control01.ctl  example01.dbf  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf

control02.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf

[root@zhang\:/u01/app/oracle/oradata/prod]#rm *.dbf

[root@zhang\:/u01/app/oracle/oradata/prod]#ls

control01.ctl  control02.ctl  redo01.log     redo02.log     redo03.log

数据文件丢失后开库报以下错误

idle>startup

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             503318008 bytes

Database Buffers          348127232 bytes

Redo Buffers                2306048 bytes

Database mounted.

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

ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'

查看是否有文件需要恢复

idle>SELECT * FROM V$RECOVER_FILE;     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         1 ONLINE  ONLINE  FILE NOT FOUND                                                             0

         2 ONLINE  ONLINE  FILE NOT FOUND                                                             0

         3 ONLINE  ONLINE  FILE NOT FOUND                                                             0

         4 ONLINE  ONLINE  FILE NOT FOUND                                                             0

         5 ONLINE  ONLINE  FILE NOT FOUND                                                             0

此时可以发现所有的checkpoint_change#号为0，此为数据文件被删所导致查询不出文件头部的检查点号所致。

开始恢复

1、  关库并将备份文件转储到原库文件路径中

idle>shutdown immediate

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

idle>!

[oracle@zhang\:/export/home/oracle]$cp /software/hot_backup/*.dbf /u01/app/oracle/oradata/prod

[oracle@zhang\:/export/home/oracle]$cd /u01/app/oracle/oradata/prod/

[oracle@zhang\:/u01/app/oracle/oradata/prod]$ls

control01.ctl  example01.dbf  redo02.log     sysaux01.dbf   undotbs01.dbf

control02.ctl  redo01.log     redo03.log     system01.dbf   users01.dbf

[oracle@zhang\:/u01/app/oracle/oradata/prod]$exit

exit

2 、转储数据库，重新开库，查看是否有文件需要转储和修复

idle>startup

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             503318008 bytes

Database Buffers          348127232 bytes

Redo Buffers                2306048 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'

 

 

idle>select status from v$instance;

 

STATUS

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

MOUNTED

 

idle>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME

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

         1 ONLINE  ONLINE                                                                       1523197 17-APR-14

         2 ONLINE  ONLINE                                                                       1523224 17-APR-14

         3 ONLINE  ONLINE                                                                       1523248 17-APR-14

         4 ONLINE  ONLINE                                                                       1523268 17-APR-14

         5 ONLINE  ONLINE                                                                       1523286 17-APR-14

此时可以发现所有的数据文件的checkpoint_change#均不一致，此原因为开启热备份的时间不一致所导致的备份的一瞬间锁定的检查点号不一致所致。

3、恢复数据库

idle>recover database;

Media recovery complete.

4、查询恢复后的文件头部检查点号，并开库

idle>select name ,checkpoint_change# from v$datafile_header;

 

NAME                                               CHECKPOINT_CHANGE#

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

/u01/app/oracle/oradata/prod/system01.dbf                     1556027

/u01/app/oracle/oradata/prod/sysaux01.dbf                     1556027

/u01/app/oracle/oradata/prod/undotbs01.dbf                    1556027

/u01/app/oracle/oradata/prod/users01.dbf                      1556027

/u01/app/oracle/oradata/prod/example01.dbf                    1556027

idle>alter database open;

 

Database altered.

高可用性恢复，system、默认Undo表空间无法用高可用性进行恢复

模拟灾难

在库关闭状态下删除一个数据文件

 

[root@zhang\:/u01/app/oracle/oradata/prod]#ls

control01.ctl  example01.dbf  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf

control02.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf

[root@zhang\:/u01/app/oracle/oradata/prod]#rm example01.dbf

[root@zhang\:/u01/app/oracle/oradata/prod]#ls

control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf

control02.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf

启动数据库会发现报如下错误

idle>startup  

ORACLE instance started.

 

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             503318008 bytes

Database Buffers          348127232 bytes

Redo Buffers                2306048 bytes

Database mounted.

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

ORA-01110: data file 5: '/u01/app/oracle/oradata/prod/example01.dbf'

查看是否有数据文件需要修复

 

idle>select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME

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

         5 ONLINE  ONLINE  FILE NOT            0

                           FOUND

可以发现数据文件号为5的数据文件需要被修复

1、  将备份转储到库文件原所在路径下

idle>!      

[oracle@zhang\:/export/home/oracle]$cp /software/hot_backup/example01.dbf /u01/app/oracle/oradata/prod/

[oracle@zhang\:/export/home/oracle]$exit

Exit

2、  将上述损坏的文件脱机以打开数据库，使为损坏的数据文件能够继续使用

idle>alter database datafile 5 offline;

 

Database altered.

 

idle>select * from v$recover_file;

 

     FILE# ONLINE  ONLINE_ ERROR         CHANGE# TIME

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

         5 OFFLINE OFFLINE               1523286 17-APR-14

idle>alter database open;

 

Database altered.

3、  修复数据库文件5

idle>recover datafile 5;

Media recovery complete.

4、  将数据库文件5开机

idle>alter database datafile 5 online;

 

Database altered.

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

转载于:http://blog.itpub.net/29802484/viewspace-1417815/