rhys

0 积分	1 好友	9 主题

发消息

[Exadata硬件] 数据库非归档模式，在线联机redo日志损坏，数据库异常宕机

1^#

发表于 2013-10-31 01:04:20 | 查看: 4104| 回复: 6

刘，您好：
数据库运行在非归档模式，在线联机日志损坏，数据库异常宕机。这种情况下怎么对数据进行恢复？
模拟过程如下：
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode             No Archive Mode
Automatic archival          Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    421
Current log sequence          424
SQL> set linesize 200
SQL> select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES  BLOCKSIZE MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
      1       1       424 52428800       512       1 NO  CURRENT             5787853 30-OCT-13 2.8147E+14
      2       1       421 52428800       512       1 YES INACTIVE             5730825 30-OCT-13    5751277 30-OCT-13
      3       1       422 52428800       512       1 YES INACTIVE             5751277 30-OCT-13    5762850 30-OCT-13
      4       1       423 52428800       512       1 YES INACTIVE             5762850 30-OCT-13    5787853 30-OCT-13

SQL> col member for a60
SQL> select group#,status,member from v$logfile;

GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------------------------
      3       /opt/app/oracle/oradata/RHYS/redo03.log
      2       /opt/app/oracle/oradata/RHYS/redo02.log
      1       /opt/app/oracle/oradata/RHYS/redo01.log
      4       /opt/app/oracle/oradata/RHYS/redo04.log

SQL> !rm /opt/app/oracle/oradata/RHYS/redo01.log

SQL> !ls -l /opt/app/oracle/oradata/RHYS/
total 3719424
-rw-r-----. 1 oracle oinstall 9977856 Oct 30 23:36 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Oct 30 23:34 example01.dbf
-rw-r-----. 1 oracle oinstall  62922752 Oct 30 23:34 index_tablespace.dbf
-rw-r-----. 1 oracle oinstall  52429312 Oct 30 23:34 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Oct 30 23:34 redo03.log
-rw-r-----. 1 oracle oinstall  52429312 Oct 30 23:34 redo04.log
-rw-r-----. 1 oracle oinstall  52429312 Oct 25 16:45 redo4_1.log
-rw-r-----. 1 oracle oinstall 524296192 Oct 30 23:34 Rhys_Amy.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 30 23:34 statspack.dbf
-rw-r-----. 1 oracle oinstall 671096832 Oct 30 23:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 870326272 Oct 30 23:34 system01.dbf
-rw-r-----. 1 oracle oinstall  44048384 Oct 30 22:02 temp01.dbf
-rw-r-----. 1 oracle oinstall 524296192 Oct 30 23:34 undotbs02.dbf
-rw-r-----. 1 oracle oinstall 5251072 Oct 30 23:34 users01.dbf

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  492707840 bytes
Fixed Size                2254544 bytes
Variable Size          318769456 bytes
Database Buffers       167772160 bytes
Redo Buffers             3911680 bytes
Database mounted.
SQL> select checkpoint_change#,controlfile_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
         5788411          5788474

SQL> select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES  BLOCKSIZE MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
      1       1       424 52428800       512       1 NO  CURRENT             5787853 30-OCT-13 2.8147E+14
      4       1       423 52428800       512       1 YES INACTIVE             5762850 30-OCT-13    5787853 30-OCT-13
      3       1       422 52428800       512       1 YES INACTIVE             5751277 30-OCT-13    5762850 30-OCT-13
      2       1       421 52428800       512       1 YES INACTIVE             5730825 30-OCT-13    5751277 30-OCT-13

SQL> select checkpoint_change#,controlfile_change#,controlfile_type from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROL
------------------ ------------------- -------
         5788411          5788474 CURRENT

SQL> select file#,status,checkpoint_change# from v$datafile_header;

   FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
      1 ONLINE          5788411
      2 ONLINE          5788411
      3 ONLINE          5788411
      4 ONLINE          5788411
      5 ONLINE          5788411
      6 ONLINE          5788411
      7 ONLINE          5788411
      8 ONLINE          5788411
      9 ONLINE          5788411
      10 ONLINE          5788411

10 rows selected.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select a.value || b.symbol || c.instance_name || '_ora_' || d.spid ||
  2       '.trc' trace_file_name
  3 from (select value from v$parameter where name = 'user_dump_dest') a,
  4       (select substr(value, -6, 1) symbol
  5          from v$parameter
  6          where name = 'user_dump_dest') b,
  7       (select instance_name from v$instance) c,
  8       (select spid
  9          from v$session s, v$process p, v$mystat m
10          where s.paddr = p.addr
11          and s.sid = m.sid
12          and m.statistic# = 0) d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_2114.trc

分享0