无备份归档模式丢失全部日志member的恢复

最新推荐文章于 2024-09-20 17:14:37 发布

cnk45946

最新推荐文章于 2024-09-20 17:14:37 发布

阅读量159

点赞数

文章标签：数据库操作系统

一描述

10g 042课件中讲到了redo log group丢失某个member的恢复实验.redo log group记录了数据库的全部操作.当全部log group member丢失时,数据库的操作记录是否还有保留.实验记之.此文档试验全部member丢失时的恢复操作.

二操作环境

OS

$cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.6 (Tikanga)
$uname -a
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
$

DB

SQL> set lines 150
COL PRODUCT FORMAT A55
COL VERSION FORMAT A15
COL STATUS FORMAT A15
SELECT * FROM PRODUCT_COMPONENT_VERSION;
archive log list;SQL> SQL> SQL> SQL>
PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  10.2.0.4.0      Production
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      Prod
PL/SQL                                                  10.2.0.4.0      Production
TNS for Linux:                                          10.2.0.4.0      Production

SQL>
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle
Oldest online log sequence     150
Next log sequence to archive   153
Current log sequence           153
SQL>

other

三过程设计

1.删除全部redo log group member.
2.在scott用户下创建测试表member(id number),并插入测试数据.
3.切换日志组
4.关闭数据库(一致性,非一致性)
5.启动数据库
6.恢复

四详细步骤操作

1.SQL> select '!rm '||member from v$logfile;

'!RM'||MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log

8 rows selected.

SQL>
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log
SQL> !rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log
!rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log
SQL>

2.连接scott用户,创建测试表,并添加测试数据,切换日志.
SQL> conn scott/oracle
Connected.
SQL>
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
BONUS                          TABLE
SALGRADE                       TABLE
BIN$wYLdeuG+twjgQAB/AQA5CA==$0 TABLE

SQL> create table member (id number);

Table created.

SQL> insert into member values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> r
1* alter system switch logfile

System altered.

SQL> r
1* alter system switch logfile

System altered.

SQL> r
1* alter system switch logfile

--注:此时alter system switch logfile操作hang住不动.

3.查看alert日志信息
Mon Jun 11 00:53:17 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_arc0_4619.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jun 11 00:53:17 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_arc0_4619.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
一直报找不到log 5的文件 :) 嘿嘿, 一定找不到了. redo log group全部的member都没了.

4.数据库hang住不动,另开一个terminal看看用户还能不能连接进去.
$sqlplus scott/oracle

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 11 00:51:47 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

Enter user-name:
得,普通用户进不去了.只能以sys用户连接维护了.

5.再新开一个terminal,以sys用户连接进去.试试能不能删日志组,结果不让删,然后用重建日志组命令了.

SQL> set lines 160
SQL> r
1* select * from v$log

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        156   52428800          2 NO CURRENT                1503957 11-JUN-12
         2          1        155   52428800          2 NO INACTIVE               1503952 11-JUN-12
         3          1        154   52428800          2 NO INACTIVE               1503950 11-JUN-12
         5          1        153   62914560          2 NO INACTIVE               1501935 11-JUN-12

SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-00350: log 5 of instance testb (thread 1) needs to be archived
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log'
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00359: logfile group 4 does not exist

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 5;

Database altered.

SQL>

全部5组redo log都重新创建成功,第2步中被hang住的alter system switch logfile操作也被释放了. 看看alert日志中的内容:
Mon Jun 11 01:04:48 2012
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/udump/testb_ora_5167.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jun 11 01:05:07 2012
Completed: alter database clear unarchived logfile group 5

(当执行到clear group 5 的时侯恢复正常)

6.查看刚刚的scott用户下的操作是否丢失
SQL> r
1* alter system switch logfile

System altered.

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn scott/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
BONUS                          TABLE
SALGRADE                       TABLE
BIN$wYLdeuG+twjgQAB/AQA5CA==$0 TABLE
MEMBER                         TABLE

SQL> select * from member;

        ID
----------
         1

SQL>

五个人总结

在过程设计中原来设计的是把数据库关闭的,结果没想到在切换日志的时候就被hang住了.这时不需要关闭数据库,在开库状态就需要恢复数据库的操作.看来归档模式下不关闭数据库的日志恢复相较还是容易些.而且数据库没有关闭,数据也没有丢失.

另注:如果关闭数据库后,启动数据库只能启动到mount状态.此时对current状态的redo group 是不能直接执行alter database clear unarchived logfile group X; 进行日志重建的,需要首先执行 recover database until cancel; 然后 alter database resetlogs;将库open,再进行日志切换alter system switch logfile;最后alter database clear unarchived logfile group X;执行日志重建. 注中实验是以shutdown immediate的形式关闭数据库的,因此open数据库查数据时没有发生数据丢失的情况.

不知道注中以shutdown abort的方式关闭数据库后是否数据仍在.