dg环境备机起不来，请高手诊断，急！

smalltom30 · 发表于 2011-11-17 17:06

本帖最后由 smalltom30 于 2011-11-17 17:07 编辑

有意思的是我在主机上做了多次日志切换，但是standby log却只是在27，28号日志上切换
SQL>  select * from v$standby_log;

GROUP# DBID                                     THREAD#       SEQUENCE#    BYTES          USED ARC
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---
STATUS          FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ------------- ------------ ------------ ------------
      25 4291471271                                           1             133  104857600 33683968 YES
ACTIVE                5200132 10-NOV-11          5234818 10-NOV-11

      26 4291471271                                           1             164  104857600       1197056 YES
ACTIVE                5999090 16-NOV-11          5999510 16-NOV-11

      27 UNASSIGNED                                           1             0  104857600          512 NO
UNASSIGNED             0                      0

      28 4291471271                                           1             197 104857600       248832 YES
ACTIVE                7031906 17-NOV-11          7032086 17-NOV-11

SQL> /

GROUP# DBID                                     THREAD#       SEQUENCE#    BYTES          USED ARC
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---
STATUS          FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ------------- ------------ ------------ ------------
      25 4291471271                                           1             133  104857600 33683968 YES
ACTIVE                5200132 10-NOV-11          5234818 10-NOV-11

      26 4291471271                                           1             164  104857600       1197056 YES
ACTIVE                5999090 16-NOV-11          5999510 16-NOV-11

      27 4291471271                                           1          198  104857600       13312 YES
ACTIVE                7032992 17-NOV-11          7032996 17-NOV-11

      28 UNASSIGNED                                           1             0  104857600          512 NO
UNASSIGNED             0                      0

SQL> /

GROUP# DBID                                     THREAD#       SEQUENCE#    BYTES          USED ARC
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---
STATUS          FIRST_CHANGE# FIRST_TIME LAST_CHANGE# LAST_TIME
---------- ------------- ------------ ------------ ------------
      25 4291471271                                           1             133  104857600 33683968 YES
ACTIVE                5200132 10-NOV-11          5234818 10-NOV-11

      26 4291471271                                           1             164  104857600       1197056 YES
ACTIVE                5999090 16-NOV-11          5999510 16-NOV-11

      27 4291471271                                           1             210  104857600          512 YES
ACTIVE                7033078 17-NOV-11          7033079 17-NOV-11

      28 UNASSIGNED                                           1             0  104857600          512 NO
UNASSIGNED             0                      0

SQL>
25，26号是不会切换的

smalltom30 · 发表于 2011-11-17 18:32

standby_file_management 为 AUTO ，会不会是因为在主机上做了delete 归档日志文件133的操作，所以这个操作也复制到了备机，11g的alter日志文件实在是太难找了

smalltom30 · 发表于 2011-11-17 18:40

*** 2011-11-17 08:45:55.657
MRP0: Background Media Recovery cancelled with status 16037
ORA-16037: user requested cancel of managed recovery operation
*** 2011-11-17 08:45:55.658 1117 krsh.c
Managed Standby Recovery not using Real Time Apply
MRP: Prodding archiver at standby for thread 1 seq 133
----- Redo read statistics for thread 1 -----
Read rate (SYNC): 46827Kb in 73577.22s => 0.00 Mb/sec
Total redo bytes: 46827Kb Longest record: 14Kb, moves: 22/82095 moved: 0Mb (0%)
Longest LWN: 683Kb, reads: 28348
Last redo scn: 0x0000.004f5901 (5200129)
Change vector header moves = 9582/151713 (6%)
----------------------------------------------

*** 2011-11-17 08:45:55.672
Media Recovery drop redo thread 1

*** 2011-11-17 08:45:55.722
Completed Parallel Media Recovery
In-flux buffer recovery was not started because datafiles were fuzzy beyond in-flux recovery target.
Highest datafile fuzzy SCN: 0.5234818
In-flux buffer recovery target SCN: 0.5200132
*** 2011-11-17 08:45:55.727 1388 krsm.c
Managed Recovery: Not Active posted.

*** 2011-11-17 08:45:56.728
ORA-16037: user requested cancel of managed recovery operation
*** 2011-11-17 08:45:56.728 1117 krsh.c
MRP0: Background Media Recovery process shutdown
*** 2011-11-17 08:45:56.728 1388 krsm.c

smalltom30 · 发表于 2011-11-18 11:33

alter database recover managed standby database using current logfile disconnect from session
ORA-1153 signalled during: alter database recover managed standby database using current logfile disconnect from session...
Fri Nov 18 11:21:34 2011
alter database recover managed standby database using current logfile disconnect from session
ORA-1153 signalled during: alter database recover managed standby database using current logfile disconnect from session...
Fri Nov 18 11:21:56 2011
Standby crash recovery failed to bring standby database to a consistent
point because needed redo hasn't arrived yet.
MRP: Wait timeout: thread 1 sequence# 0
Standby crash recovery aborted due to error 16016.
Errors in file /opt/oracle/oradb/diag/rdbms/ossdbstb/ossdb/trace/ossdb_ora_8368.trc:
ORA-16016: archived log for thread 1 sequence# 133 unavailable
Shutting down recovery slaves due to error 16016
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed standby crash recovery.
ORA-10458 signalled during: ALTER DATABASE OPEN...
Fri Nov 18 11:22:34 2011
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ossdb)
Fri Nov 18 11:22:35 2011
MRP0 started with pid=19, OS id=24195
MRP0: Background Managed Standby Recovery process started (ossdb)
Fast Parallel Media Recovery enabled
Managed Standby Recovery starting Real Time Apply
parallel recovery started with 15 processes
Block change tracking file is current.
Starting background process CTWR
Fri Nov 18 11:22:41 2011
CTWR started with pid=43, OS id=24320
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database using current logfile disconnect from session
Recovery of Online Redo Log: Thread 1 Group 25 Seq 133 Reading mem 0
Mem# 0: /opt/oracle/oradb/flashback/OSSDBSTB/onlinelog/o1_mf_25_7bw2cns0_.log

smalltom30 · 发表于 2011-11-18 13:03

本帖最后由 smalltom30 于 2011-11-18 13:10 编辑

现在在主机的备份集中找到了133这个归档，但怎么这个归档在备机上恢复并应用呢
BS Key  Size    Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
90    267.74M DISK       00:00:54    13-NOV-11
      BP Key: 90 Status: AVAILABLE  Compressed: YES  Tag: TAG20111113T020151
      Piece Name: /opt/dbbackup/auto/20111113/level0/mob_L0_20111113_92_1_arch.bak

  List of Archived Logs in backup set 90
  Thrd Seq    Low SCN Low Time  Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
  1 111    4752474 08-NOV-11 4802611 08-NOV-11
  1 112    4802611 08-NOV-11 4833940 08-NOV-11
  1 113    4833940 08-NOV-11 4833976 08-NOV-11
  1 114    4833976 08-NOV-11 4834338 08-NOV-11
  1 115    4834338 08-NOV-11 4834346 08-NOV-11
  1 116    4834346 08-NOV-11 4835385 08-NOV-11
  1 117    4835385 08-NOV-11 4835392 08-NOV-11
  1 118    4835392 08-NOV-11 4835703 08-NOV-11
  1 119    4835703 08-NOV-11 4837006 08-NOV-11
  1 120    4837006 08-NOV-11 4872688 08-NOV-11
  1 121    4872688 08-NOV-11 4881326 08-NOV-11
  1 122    4881326 08-NOV-11 4923068 09-NOV-11
  1 123    4923068 09-NOV-11 4923752 09-NOV-11
  1 124    4923752 09-NOV-11 4923809 09-NOV-11
  1 125    4923809 09-NOV-11 4974281 09-NOV-11
  1 126    4974281 09-NOV-11 5023706 09-NOV-11
  1 127    5023706 09-NOV-11 5070431 09-NOV-11
  1 128    5070431 09-NOV-11 5078673 09-NOV-11
  1 129    5078673 09-NOV-11 5118547 10-NOV-11
  1 130    5118547 10-NOV-11 5144735 10-NOV-11
  1 131    5144735 10-NOV-11 5144806 10-NOV-11
  1 132    5144806 10-NOV-11 5200132 10-NOV-11
  1 133    5200132 10-NOV-11 5234838 10-NOV-11
  1 134    5234838 10-NOV-11 5268711 10-NOV-11
  1 135    5268711 10-NOV-11 5307597 11-NOV-11

smalltom30 · 发表于 2011-11-18 15:45

问题解决了，还是把过程贴一下吧，其实还是有很多东西没有搞清的，比如备机的133-149归档是怎么丢失的，要不是主机上有RMAN的备份集中带有这几个日志文件，那怎么办？
Recovery Manager complete.
oracle@mob26:~> ll /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak
-rw-r----- 1 oracle oinstall 280751104 2011-11-13 02:02 /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak
oracle@mob26:~> scp /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak mob27:/opt/dbbackup/manual
Warning: Permanently added 'mob27,10.85.139.52' (RSA) to the list of known hosts.
Password:
oracle@mob26:~> scp /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak mob27:/opt/dbbackup/manual
Password:
Password:
OSSDB_L0_20111113_92_1_arch.bak                                                          100%  268MB 7.7MB/s 00:35

Recovery Manager complete.
oracle@mob26:~> scp /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_93_1_arch.bak mob27:/opt/dbbackup/auto/20111113/level0/
Password:
OSSDB_L0_20111113_93_1_arch.bak                                                          100% 79MB 9.9MB/s 00:08
oracle@mob26:~> exit
logout
You have new mail in /var/mail/root
mob26:/var/VRTSvcs/log # su - oracle
oracle@mob26:~> scp /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_99_1_arch.bak mob27:/opt/dbbackup/auto/20111113/level0/
Password:
OSSDB_L0_20111113_99_1_arch.bak                                                          100% 36KB  36.0KB/s 00:00
oracle@mob26:~>
把包括133-149备份集传到备机一个手工创建的目录下

smalltom30 · 发表于 2011-11-18 17:11

oracle@mob27:/opt/dbbackup/auto/20111113/level0> rman nocatalog target / ###备机

Recovery Manager: Release 11.1.0.7.0 - Production on Fri Nov 18 14:45:02 2011

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

connected to target database: OSSDB (DBID=4291471271, not open)
RMAN> catalog backuppiece '/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak';

cataloged backup piece
backup piece handle=/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak RECID=49 STAMP=767544564

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name OSSDBSTB
=====================================================================

146 1 130 A 10-NOV-11
channel ORA_DISK_1: SID=815 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=133
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=134
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=135
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=136
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=137
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=138
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=139
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=140
channel ORA_DISK_1: reading from backup piece /opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak
channel ORA_DISK_1: piece handle=/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_92_1_arch.bak tag=TAG20111113T020151
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 18-NOV-11

RMAN> exit
可见133-140的已经restore了，restore的具体位置就是本地归档的地方
那还有141-149的没有恢复，继续从主机把备份集cope过来。。。

smalltom30 · 发表于 2011-11-18 17:15

RMAN> restore archivelog sequence between 133 and 149;

Starting restore at 18-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=815 device type=DISK
RMAN> catalog backuppiece '/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_93_1_arch.bak';

cataloged backup piece
backup piece handle=/opt/dbbackup/auto
RMAN> restore archivelog sequence between 140 and 149;  ####rman会自己判断这个backupset归档的seq

Starting restore at 18-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1081 device type=DISK
RMAN> catalog backuppiece '/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_99_1_arch.bak'; ###  再把这个cope过来，这个里面包含了149，可能要问怎么知道要恢复的是133-149的而不是别的归档文件呢？可以通过
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
来查看有哪些ARCH是没有apply的，然后在本地归档路径下对照一下，这些文件是否存在即可

cataloged backup piece
backup piece handle=/opt/dbbackup/auto/20111113/level0/OSSDB_L0_20111113_99_1_arch.bak RECID=51 STAMP=767545399

RMAN> restore archivelog sequence=149;
此时查看备机的日志应用情况，归档已经自动应用上了！！！

SQL> select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- ------------ ------------ ---------
131 10-NOV-11 10-NOV-11 YES
   132 10-NOV-11 10-NOV-11 YES
   133 10-NOV-11 10-NOV-11 YES
   134 10-NOV-11 10-NOV-11 YES
SQL> show parameter back

NAME       TYPE       VALUE
------------------------------------ -------------------------------- ------------------------------
background_core_dump       string       partial
background_dump_dest       string       /opt/oracle/oradb/diag/rdbms/o
      ssdbstb/ossdb/trace    ####这是alert*.log的位置

smalltom30 · 发表于 2011-11-18 17:15

本帖最后由 smalltom30 于 2011-11-27 22:02 编辑

Fri Nov 18 12:02:52 2011 t_tom
ALTER SYSTEM ARCHIVE LOG 123123123
Fri Nov 18 15:04:29 2011
RFS[4]: Assigned to RFS process 9928
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 18084
RFS[4]: Opened log for thread 1 sequence 164 dbid -3496025 branch 765969705
Archived Log entry 242 added for thread 1 sequence 164 rlc 765969705 ID 0xffce561a dest 2:
Fri Nov 18 15:04:36 2011
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[1]: Selected log 26 for thread 1 sequence 228 dbid -3496025 branch 765969705
Fri Nov 18 15:04:36 2011
Archived Log entry 243 added for thread 1 sequence 227 ID 0xffce561a dest 1:
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Register archivelog /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_133_765969705.dbf already exists
Fri Nov 18 15:19:34 2011
alter database register logfile '/opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_134_765969705.dbf'
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Register archivelog /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_134_765969705.dbf already exists ####手工注册日志文件，但是系统提示文件已经存在！
Fri Nov 18 15:19:45 2011
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_133_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_134_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_135_765969705.dbf
Fri Nov 18 15:19:49 2011
alter database register logfile '/opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_135_765969705.dbf'
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Register archivelog /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_135_765969705.dbf already exists
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_136_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_137_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_138_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_139_765969705.dbf
Media Recovery Log /opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_140_765969705.dbf
Fri Nov 18 15:19:56 2011 ### MRP 进程工作状态

至此，主备机的归档一致，备机日志应用正常

smalltom30 · 发表于 2011-11-18 17:45

疑点：
1.用RMAN进行归档恢复后，日志文件会自动存放到本地归档路下，这个很智能
2.alter database register logfile '/opt/o  都不用注册日志文件，
SQL> c/135/149
  1* alter database register logfile '/opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_149_765969705.dbf'
SQL> /
alter database register logfile '/opt/oracle/oradb/flash_recovery_area/ossdbstb/archivelog/1_149_765969705.dbf'
*
ERROR at line 1:
ORA-16089: archive log has already been registered

SQL> alter database recover managed standby database cancel;  ###这一步之后发生了什么就不清楚了

Database altered.

SQL> alter database recover managed standby database disconnect;  ##这一步应该是应用日志的，但是提示有一个进程在应用
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
只能说ORACLE现在是越来越自动化了，是11g

[HA] dg环境备机起不来，请高手诊断，急！