19 – 四月 – 2012 – 专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Data Guard主库出现如下错误
导致归档日志不同通过LOG_ARCHIVE_DEST_2传输到备库

Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 19:58:40 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:00:26 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 3113 when pinging standby standby.
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:18:18 2012
Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_6756.trc:
Thu Apr 19 20:33:27 2012
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned 

[oracle@localhost ~]$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
//          First, check for network problems and review the SQL*Net setup. 
//          Also, look in the alert.log file for any errors. Finally, test to 
//          see whether the server process is dead and whether a trace file
//          was generated at failure time.
提示连接错误

orcl_ora_6756.trc文件内容
这里没有得任何重要的有效信息

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      fcdb1
Release:        2.6.18-194.el5
Version:        #1 SMP Fri Apr 2 14:58:14 EDT 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6756, image: oracle@fcdb1 (TNS V1-V3)

*** 2012-04-19 19:51:32.033
*** ACTION NAME:(0000045 STARTED16) 2012-04-19 19:51:32.026
*** MODULE NAME:(backup incr datafile) 2012-04-19 19:51:32.026
*** SERVICE NAME:(SYS$USERS) 2012-04-19 19:51:32.026
*** SESSION ID:(1518.294) 2012-04-19 19:51:32.026
*** ACTION NAME:(0000062 STARTED68) 2012-04-19 19:58:40.083
*** MODULE NAME:(backup full datafile) 2012-04-19 19:58:40.083
*** 2012-04-19 19:58:40.083
*** ACTION NAME:(0000068 STARTED16) 2012-04-19 19:58:40.156
*** 2012-04-19 20:18:18.436
*** ACTION NAME:(0000118 STARTED16) 2012-04-19 20:18:18.436
*** MODULE NAME:(backup incr datafile) 2012-04-19 20:18:18.436

查看相关参数

SQL> show parameter archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(primary,standby)
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/arch
                                                 VALID_FOR=(ALL_LOGFILES,ALL_RO
                                                 LES) DB_UNIQUE_NAME=primary
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby 
log_archive_dest_state_1             string      ENABLE
log_archive_dest_state_2             string      ENABLE

测试TNS

[oracle@fcdb1 bdump]$ tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-APR-2012 20:47:51

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[oracle@fcdb1 bdump]$ sqlplus sys/oracle@standby as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 20:49:05 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

问题原因分析
从log_archive_dest_2 参数上可以看出默认是配置lgwr进程传输日志，但是因为备库没有配置standby redo log，所以使得启动arch传输日志，然后出现该问题，因为在传输过程中出现异常，导致arch不能继续和备库建立连接。

解决方法
查看了很多资料，给出的解决方法都是重启主库或者备库解决，我想既然是ARCH建立连接的失败，那么重启log_archive_dest_state_2参数，让arch进程重启。

SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

System altered.

SQL> alter system switch logfile;

System altered.


--alert日志
Thu Apr 19 20:51:12 2012
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Thu Apr 19 20:51:32 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
LNS1 started with pid=35, OS id=7012
Thu Apr 19 20:51:47 2012
Thread 1 advanced to log sequence 2025
  Current log# 2 seq# 2025 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Thu Apr 19 20:51:48 2012
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Apr 19 20:52:02 2012
Thread 1 advanced to log sequence 2026
  Current log# 3 seq# 2026 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log
Thread 1 cannot allocate new log, sequence 2027

这个时候，查看备库日志也已经传输过去，通过修改log_archive_dest_state_2解决

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

全备数据库

RMAN> backup as compressed backupset database  format '/u01/oracle/oradata/tmp/ora11g_0_%U';

Starting backup at 18-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-APR-12
channel ORA_DISK_1: finished piece 1 at 18-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1 tag=TAG20120418T234958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-APR-12

创建测试数据库验证恢复标准

SQL> conn chf/xifenfei
Connected.
SQL> drop table xifenfei purge;

Table dropped.

SQL> create table xifenfei as
  2  select * from dba_objects;

Table created.

SQL> insert into xifenfei
  2  select * from dba_objects;

74534 rows created.

SQL> /       

74534 rows created.

SQL> /

74534 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from xifenfei;

  COUNT(*)
----------
    298136

SQL> create table xifenfei01 as    
  2  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> create table chf.xifenfei02 as
  2  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

异机恢复库

RMAN> restore controlfile from '/u01/oracle/oradata/tmp/ora11g_0_08n8p93h_1_1';

Starting restore at 19-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/ora11g/control01.ctl
output file name=/u01/oracle/oradata/ora11g/control02.ctl
Finished restore at 19-APR-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    262.79M    DISK        00:01:08     18-APR-12      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20120418T234958
        Piece Name: /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/system01.dbf
  2       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/undotbs01.dbf
  4       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/users01.dbf
  6       Full 1155510    18-APR-12 /u01/oracle/oradata/ora11g/xifenfei02.dbf
--这里可以发现备份时的scn,增量备份时取这里的最小scn为起点


--为了排除影响,删除所有归档日志记录
RMAN> delete archivelog all;

RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> restore database;

Starting restore at 19-APR-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11g_0_07n8p916_1_1 tag=TAG20120418T234958
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 19-APR-12

[oracle@xifenfei oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 00:54:42 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1155510                             787897
         2           1155510                             787897
         3           1155510                             787897
         4           1155510                             787897
         6           1155510                             787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1155510
         2          1155510
         3          1155510
         4          1155510
         6          1155510

基于scn增量备份

RMAN> BACKUP INCREMENTAL FROM SCN 1155510 DATABASE
2> FORMAT '/u01/oracle/oradata/tmp/ora11_scn_%U' tag 'XIFENFEI';

Starting backup at 19-APR-12

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/ora11g/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

using channel ORA_DISK_1
backup will be obsolete on date 26-APR-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 19-APR-12
channel ORA_DISK_1: finished piece 1 at 19-APR-12
piece handle=/u01/oracle/oradata/tmp/ora11_scn_0cn8pbtq_1_1 tag=XIFENFEI comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-APR-12

增量恢复

RMAN> catalog start with '/u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1';

searching for all files that match the pattern /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

List of Files Unknown to the Database
=====================================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oracle/oradata/tmp/ora11_scn_09n8pa5h_1_1

RMAN> recover database;

Starting recover at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oracle/oradata/ora11g/system01.dbf
destination for restore of datafile 00002: /u01/oracle/oradata/ora11g/sysaux01.dbf
destination for restore of datafile 00003: /u01/oracle/oradata/ora11g/undotbs01.dbf
destination for restore of datafile 00004: /u01/oracle/oradata/ora11g/users01.dbf
destination for restore of datafile 00006: /u01/oracle/oradata/ora11g/xifenfei02.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1
channel ORA_DISK_1: piece handle=/u01/oracle/oradata/tmp/ora11_scn_0bn8pbsd_1_1.bak tag=XIFENFEI
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

starting media recovery

--这里表现出来的是利用归档日志恢复，实质是基于scn增量备份的备份集恢复
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_49_777766629.dbf thread=1 sequence=49
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_50_777766629.dbf thread=1 sequence=50
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_51_777766629.dbf thread=1 sequence=51
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_52_777766629.dbf thread=1 sequence=52
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_53_777766629.dbf thread=1 sequence=53
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_54_777766629.dbf thread=1 sequence=54
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_55_777766629.dbf thread=1 sequence=55
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_56_777766629.dbf thread=1 sequence=56
archived log file name=/u01/oracle/oradata/archivelog/ora11g/1_57_777766629.dbf thread=1 sequence=57
unable to find archived log
archived log thread=1 sequence=58
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/19/2012 00:55:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 58 and starting SCN of 1157379

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN                                RESETLOGS SCN
---------- ---------------------------------- ----------------------------------
         1           1157379                             787897
         2           1157379                             787897
         3           1157379                             787897
         4           1157379                             787897
         6           1157379                             787897

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN                              STOP_SCN
---------- -------------------------------- --------------------------------
         1          1157379
         2          1157379
         3          1157379
         4          1157379
         6          1157379

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from chf.xifenfei;

  COUNT(*)
----------
    298136

SQL> select TABLE_NAME from dba_tables where table_name LIKE 'XIFENFEI%';

TABLE_NAME
------------------------------------------------------------
XIFENFEI02
XIFENFEI01
XIFENFEI

使用rman基于scn实现数据库增量恢复是在dg中修复gap的时候常见的方法,其实该方法也可以使用常规的增量恢复,通过人工控制,实现数据库的某种特殊的业务需求(特殊的数据迁移).处理思路主要是获得备库的数据文件最小scn(这个scn可能是通过全备恢复或者增量恢复产生),然后基于该SCN实现数据库增量备份,然后利用该备份进行增量恢复.

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Daily Archives: 2012 年 04 月 19 日

记录一次ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned解决

利用scn增量备份实现数据库增量恢复

S	M	T	W	T	F	S
« Mar				May »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30