使用dgmgrl 切换报错 ORA-03113: end-of-file on communication channel

来源：互联网发布：2017社交聊天软件编辑：程序博客网时间：2024/06/06 02:06

DGMGRL> show database rac
Database - rac
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
rac
Database Status:
SUCCESS
DGMGRL> show database verbose rac
Database - rac
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
rac
Properties:
DGConnectIdentifier = 'rac'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/app/oracle/oradata/, +data_log/rac/datafile/, /u01/app/oracle/oradata/, +data_log/rac/tempfile/'
LogFileNameConvert = '/u01/app/oracle/oradata/, +data_log/rac/onlinelog/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac1'
SidName = 'rac'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RAC_DGMGRL)(INSTANCE_NAME=rac)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/archive'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

主备切换：

DGMGRL> switchover to orcl

Performing switchover NOW, please wait...
Error:
ORA-03113: end-of-file on communication channel
Process ID: 4090
Session ID: 52 Serial number: 16
Unable to switchover, primary database is still "rac"

DGMGRL> exit

查看日志：

主库日志：

Mon Aug 26 00:07:15 2013
Shutting down instance (abort)
License high water mark = 5
USER (ospid: 18656): terminating the instance
Instance terminated by USER, pid = 18656
Mon Aug 26 00:07:16 2013
Instance shutdown complete

备库的日志：

RFS[4]: Possible network disconnect with primary database
Mon Aug 26 20:51:02 2013
RFS[2]: Possible network disconnect with primary database
Mon Aug 26 20:51:02 2013
RFS[6]: Possible network disconnect with primary database
Mon Aug 26 20:51:02 2013
RFS[5]: Possible network disconnect with primary database
Mon Aug 26 20:51:48 2013

从上面的日志看出，主库直接关闭，而不是切换日志归档，并且传输给备库

解决方法：

Manually grant 'rwx'-Privileges for the Primary and/or Standby Database to the Grid Infrastructure User using crsctl (assuming the the Grid Infrastructure User is called 'grid'):

% crsctl modify resource ora.prim.db -attr
"ACL='owner:grid:rwx,pgrp:dba:rwx,other::r--,user:oracle:rwx'" -f -i
% crsctl modify resource ora.stby.db -attr
"ACL='owner:grid:rwx,pgrp:dba:rwx,other::r--,user:oracle:rwx'" -f -i

This is not a Problem in 11.2.0.2 anymore, too as Part of the Fix for
Internal Bug 9705202: [11202-LNX64-100428]DB STAT KEEP INTERMEDIATE AFTER SIHA INSTALLATION

通执行

crsctl modify resource ora.rac.db -attr "ACL='owner:grid:rwx,pgrp:oinstall:rwx,other::r--,user:oracle:rwx'" -f -i

并且重启数据库后切换成功，但是还是有点点小问题。

DGMGRL> switchover to orcl
Performing switchover NOW, please wait...
New primary database "orcl" is opening...
Operation requires shutdown of instance "rac" on database "rac"
Shutting down instance "rac"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rac" on database "rac"
Starting instance "rac"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up and mount instance "rac" of database "rac"

切换成功：

DGMGRL> show configuration
Configuration - dg_rac
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
rac - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

下面我们来解决

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

通过查看orcl库

DGMGRL> show database verbose orcl

Database - orcl

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
orcl
Properties:
DGConnectIdentifier = 'orcl'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+data_log/rac/datafile/, /u01/app/oracle/oradata/, +data_log/rac/tempfile/, /u01/app/oracle/oradata/'
LogFileNameConvert = '+data_log/rac/onlinelog/, /u01/app/oracle/oradata/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'localhost.localdomain'
SidName = 'orcl'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/archive'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

遗留问题： orcl_DGMGRL 服务的端口号如何确定，此监听服务在哪定义：

DGMGRL> edit database orcl set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
> ;
Property "staticconnectidentifier" updated

edit database rac set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=RAC_DGMGRL)(INSTANCE_NAME=rac)(SERVER=DEDICATED)))'

正常的切换日志：

Mon Aug 26 21:53:25 2013
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2771] (orcl)
Mon Aug 26 21:53:27 2013
Thread 1 advanced to log sequence 63 (LGWR switch)
Current log# 2 seq# 63 mem# 0: /u01/app/oracle/oradata/group_2.266.824406747
Waiting for all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 62 to be archived...
Mon Aug 26 21:53:30 2013
Archived Log entry 41 added for thread 1 sequence 62 ID 0x8fcb720a dest 1:
Mon Aug 26 21:53:38 2013
ORL for thread 1 sequence 62 has been archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized...
Mon Aug 26 21:53:39 2013
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_3919.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Mon Aug 26 21:53:48 2013
Active, synchronized Physical Standby switchover target has been identified
Mon Aug 26 21:53:48 2013
Thread 1 advanced to log sequence 64 (LGWR switch)
Current log# 3 seq# 64 mem# 0: /u01/app/oracle/oradata/group_3.265.824406749
ARCH: Standby redo logfile selected for thread 1 sequence 63 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 42 added for thread 1 sequence 63 ID 0x8fcb720a dest 1:
Mon Aug 26 21:53:48 2013
Stopping background process CJQ0
Mon Aug 26 21:53:48 2013
SMON: disabling tx recovery
Stopping background process QMNC
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCs: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCg: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCf: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCe: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCc: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC9: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC8: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC7: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC6: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC5: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC3: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC2: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC1: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARC0: Archival stopped
Mon Aug 26 21:53:52 2013
krso_proc_start_arch: Aborting starting ARCH processes, archiving disabled
ARC4: Becoming the 'no FAL' ARCH
ARC4: Becoming the 'no SRL' ARCH
ARC4: Archiving disabled
ARCH shutting down
ARC4: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCt: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCr: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCq: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCp: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCo: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCn: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCm: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCl: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCk: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCj: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCi: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCh: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCd: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCb: Archival stopped
Mon Aug 26 21:53:52 2013
ARCH shutting down
ARCa: Archival stopped
Thread 1 closed at log sequence 64
Successful close of redo thread 1
ARCH: Noswitch archival of thread 1, sequence 64
ARCH: End-Of-Redo Branch archival of thread 1 sequence 64
Mon Aug 26 21:53:54 2013
idle dispatcher 'D000' terminated, pid = (17, 1)
Mon Aug 26 21:53:58 2013
Archived Log entry 44 added for thread 1 sequence 64 ID 0x8fcb720a dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also applied all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_rsm0_2771.trc
Clearing standby activation ID 2412474890 (0x8fcb720a)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 64 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Mon Aug 26 21:53:59 2013
MRP0 started with pid=20, OS id=3927
MRP0: Background Managed Standby Recovery process started (orcl)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /archive/1_64_824406749.arc
Identified End-Of-Redo for thread 1 sequence 64
Resetting standby activation ID 0 (0x0)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 1145338
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
MRP0: Background Media Recovery process shutdown (orcl)
Switchover: Complete - Database shutdown required (orcl)