实验环境

操作系统 Redhat5.4 x86
数据库版本 oracle 11gR2 (11.2.0.1.0)
数据库为未开启归档模式

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release .. Production on Sun May  :: 

Copyright (c) , , Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     
Current log sequence           

SQL> select LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;

LOG_MODE     OPEN_MODE        FOR
------------ -------------------- ---
NOARCHIVELOG READ WRITE       NO

实验模拟

1.redo日志组中部分member文件丢失或损坏

redo日志组部分member文件丢失，无论当前日志组是inactive， active（非current，current）都不会造成数据丢失，恢复方法也相同，因此不做特别区分。

详细参见：http://blog.csdn.net/zhuke0203/article/details/71311993#t2

案例模拟

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.
SQL> select group#,sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  INACTIVE
                     NO  INACTIVE
                     NO  CURRENT

SQL> !rm /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.

--数据库可以正常启动，alert日志会有相关报错信息：
Sun May  :: 
db_recovery_file_dest_size of  MB is % used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.trc:
ORA-: log  of thread  is more recent than control file
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.trc:
ORA-: open failed for members of log group  of thread 
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information: 
Checker run found  new persistent data failures

案例恢复

由于损坏的只是日志组中的部分member，因此可以将损坏的member file文件删除以后重新添加进来：

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';
alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
*
ERROR at line :
ORA-: log  is the current log for thread  - cannot drop members
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'


SQL> alter system switch logfile;

System altered.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  ACTIVE
                     NO  CURRENT
                     NO  INACTIVE

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log' reuse to group ;

Database altered.

SQL> !ls -la /u01/app/oracle/oradata/PROD1/disk1/redo01a.log
-rw-r-----  oracle oinstall  May   : /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

这种情况数据库可以正常启动，不会造成数据丢失。

2.非active redo日志组中所有member文件都丢失或损坏

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  ACTIVE
                     NO  CURRENT
                     NO  INACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
ORA-: end-of-file on communication channel
Process ID: 
Session ID:  Serial number:

案例恢复

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;    

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  INACTIVE
                     NO  INACTIVE
                     NO  CURRENT

SQL> alter database clear logfile group ;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

这种情况数据库无法正常启动，恢复不会造成数据丢失。

3.active redo日志组中所有member文件都丢失或损坏

ACTIVE redo日志组无论是否为current状态，恢复方法相同。

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  CURRENT
                     NO  INACTIVE
                     NO  ACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
ORA-: open failed for members of log group  of thread 
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information:

案例恢复

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select * from v$logfile;  

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  CURRENT
                     NO  ACTIVE
                     NO  INACTIVE

--尝试clear日志组：
SQL> alter database clear logfile group ;
alter database clear logfile group 
*
ERROR at line :
ORA-: log  needed for crash recovery of instance PROD1 (thread )
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk4/redo03b.log'

--尝试recover database：
SQL> recover database using backup controlfile;
ORA-: change  generated at // :: needed for thread 
ORA-: suggestion : /u01/app/oracle/flash_recovery_area/PROD1/archivelog/_05_07/o1_mf_1_24_%u_.arc
ORA-: change  for thread  is in sequence #24


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-: cannot open archived log '/u01/app/oracle/flash_recovery_area/PROD1/archivelog/_05_07/o1_mf_1_24_%u_.arc'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information: 

--尝试resetlogs:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line :
ORA-: file  needs media recovery
ORA-: data file : '/u01/app/oracle/oradata/PROD1/disk1/system01.dbf'

--尝试设置隐藏参数，让数据库跳过一致性检查：
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

--将隐藏参数重置回默认值：
SQL> alter system reset "_allow_resetlogs_corruption";

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.
SQL> 
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, members, sequence#, archived, status from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
                      NO  CURRENT
                      YES UNUSED
                      YES UNUSED

--注：这种方法慎用，只有当其它方法都尝试了无效，作为最后一种尝试的时候使用，设置隐藏参数有可能造成意想不到的后果。

该情况下，数据库无法正常启动，会造成部分数据丢失。

ORACE 非归档模式redo文件恢复实验环境实验模拟

实验环境

实验模拟

1.redo日志组中部分member文件丢失或损坏

案例模拟

案例恢复

2.非active redo日志组中所有member文件都丢失或损坏

案例模拟

案例恢复

3.active redo日志组中所有member文件都丢失或损坏

案例模拟

案例恢复

继续阅读

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List