oracle备份方式汇总

GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 11827524288001 YES ACTIVE104866595 28-APR-13 21828524288001 NOCURRENT1

GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

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

11827524288001 YES ACTIVE104866595 28-APR-13

21828524288001 NOCURRENT104866661 28-APR-13

31826524288001 YES INACTIVE104866547 28-APR-13

--查看group 3日志存放路径

select * from v$logfile

GROUP# STATUSTYPEMEMBERIS_

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

3ONLINE/u01/oradata/orcl/redo03.logNO

2ONLINE/u01/oradata/orcl/redo02.logNO

1ONLINE/u01/oradata/orcl/redo01.logNO

--删除3号在线重做日志

[root@bbk0110g ~]# rm -f /u01/oradata/orcl/redo03.log

--重新启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1218992 bytes

Variable Size104859216 bytes

Database Buffers176160768 bytes

Redo Buffers2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

只能启动到mount状态

SQL> select instance_name,status from v$instance;

INSTANCE_NAMESTATUS

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

orclMOUNTED

2）、恢复方法

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database open;

Database altered.

active的日志损坏不完全恢复

SQL> select * from v$log;

GROUP#THREAD#SEQUENCE#BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

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

11827524288001 YES INACTIVE104866595 28-APR-13

21828524288001 YES INACTIVE104866661 28-APR-13

31829524288001 NOCURRENT104867241 28-APR-13

SQL> select * from v$logfile;

GROUP# STATUSTYPEMEMBERIS_

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

3ONLINE/u01/oradata/orcl/redo03.logNO

2ONLINE/u01/oradata/orcl/redo02.logNO

1ONLINE/u01/oradata/orcl/redo01.logNO

--删除当前联机重做日志group3

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1218992 bytes

Variable Size104859216 bytes

Database Buffers176160768 bytes

Redo Buffers2973696 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

2）、恢复方法，注意与inactive的区别

SQL> alter database clear unarchived logfile group 3;

alter database clear unarchived logfile group 3

*

ERROR at line 1:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: '/u01/oradata/orcl/redo03.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> recover database until cancel

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

rman全备脚本：