外汇【DATAGUARD】物理dg配置客户端无缝切换 (八.1)–Data Guard Broker 的部署

tns_oradg11g

 
(DESCRIPTION =

   
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
(CONNECT_DATA =

     
(SERVER = DEDICATED)

     
(SERVICE_NAME = oradg11g)

   
)

 
)

一.3.2.2  建立一个仓储过程,用于调用service,确保单独当主库运行

咱创建一个囤过程来贯彻此目的,如果手上数据库是主库它就是开动这个服务,如果是备库就告一段落。

 

主库执行:

14:54:58
SQL> create or replace procedure dg_taf_proc_lhr is

14:59:46  
2    v_role VARCHAR(30);

14:59:46  
3  begin

14:59:46  
4    select DATABASE_ROLE into v_role from V$DATABASE;

14:59:46  
5    if v_role = ‘PRIMARY’ then

14:59:46  
6      DBMS_SERVICE.START_SERVICE(‘dg_taf_lhr’);

14:59:46  
7    else

14:59:46  
8      DBMS_SERVICE.STOP_SERVICE(‘dg_taf_lhr’);

14:59:46  
9    end if;

14:59:46 
10  end;

14:59:46 
11  /

 

经过都开立。

 

都用时: 
00: 00: 00.07

 

一.5  总结

 

本篇为第一省,请查看第二节省:
Fast-Start Failover 的布

 

 

一.4  总结

 

本篇为老三节约,请查看第四节:
dgmgrl维护着的广泛错误。

 

 

tns_oradg11g_DGMGRL

 
(DESCRIPTION =

   
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
(CONNECT_DATA =

     
(SERVER = DEDICATED)

     
(SERVICE_NAME = oradg11g_DGMGRL.lhr.com)

   
)

 
)

(ADDRESS_LIST

(address=(protocol=tcp)(host=192.168.1.44)(port=1521))

(address=(protocol=tcp)(host=192.168.1.45)(port=1521))

(address=(protocol=tcp)(host=192.168.1.46)(port=1521))

(load_balance=yes)//表示是否负载均衡 
)

【DATAGUARD】物理dg配置客户端无缝切换
(八.1)–Data Guard Broker 的配备

SID_LIST_LISTENER

(SID_LIST =

  
(SID_DESC =

    
(GLOBAL_DBNAME = dave)

        
(ORACLE_HOME
=D:\app\Administrator\product\11.2.0\dbhome_1)

    
(SID_NAME = NEWCCS)

   
)

 
)

 

Tnsnames.ora

一.4.1  实验目标

 

Data
Guard Broker
的安排并就实验。

 

 

(failover_mode

(type=session)

(method=basic)

(retries=5)

(delay=15)

)

)

)


加上注释后底TNS连接串 
jdbc:oracle:thin:@

(description=

一.2.4  本文简介

 

本篇blog是基于cuug的公开课内容,我要好进行实施的操作,视频可以参照:http://blog.itpub.net/26736162/viewspace-1624453/
,简介我便未多写了,把cuug的内容一直copy过来吧,觉得要于可行之。

 

其一技术如果你莫掌握,不克算是ORACLE高手

本条技术如果您不了解,就非能够说您晤面DataGuard

这技能使您莫懂得,……

 

本次网络课程,研讨当主备库发生切换时,如何在主库启动一个service,保证客户端的连天能够继承,而且还会继承select查询操作,而不论主备库是当哪台服务器上;同时确保新的客户连接没有其余的问题。按部就班学科网络及的例证不多,陈先生花了靠近一年的时间人肉搜索,最近才找到,急不可待的比方分享给大家。

 

1、DataGuard的配置(快速)

2、创建service

3、创建触发器

4、主备库切换测试

 

由于内容比较多,我打算分为4个章节来共享于大家,贴单图,不要奇怪,还有一个章节是实验过程被配到的题目迎刃而解。

外汇 1 

 

 

一.3.4.2  不使用tnsname

jdbc:oracle:thin:@ 
(description=

tns_oradgphy_DGMGRL

 
(DESCRIPTION =

   
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
(CONNECT_DATA =

     
(SERVER = DEDICATED)

     
(SERVICE_NAME = oradgphy_DGMGRL.lhr.com)

   
)

 
)

 

一.3.4  题外话:Java中之配备

 

一.4.2  前提条件

留意前提条件,Data
Guard已经打响安排好。

1. 装primary和standby启动时参数文件呢spfile

2. 配置DG_BROKER_CONFIG_FILEn
DG_BROKER_CONFIG_FILEn代表2个参数,分别为DG_BROKER_CONFIG_FILE1,DG_BROKER_CONFIG_FILE2,它们是Data
Guard
Broker的布置文件称。它们都有默认值(windows下是当ORACLE_HOME/database目录下,linux下是在ORACLE_HOME/dbs目录下),所以一般我们可以免进行特别设置。

3. 设置listener
于Data
Guard Broker环境中,需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重开数据库的时光用到。通过DGMGRL重开数据库时DMON进程会先拿数据库关闭,然后DGMGRL用是service_name通过listener连接到数据库,发出启动命令。

4. 设置DG_BROKER_START为TRUE
即同步是开行Data
Guard Broker monitor(DMON)进程,需要以2个数据库方面还运行下面的授命

alter
system set dg_broker_start=true scope=both;

 

主库操作:

[oracle@rhel6_lhr
lhr]$ sqlplus / as sysdba

 

SQL*Plus:
Release 11.2.0.3.0 Production on 星期一 9月 28 10:17:04 2015

 

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

 

 

连接到:

Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit
Production

With
the Partitioning, OLAP, Data Mining and Real Application Testing
options

 

10:17:04
SQL> show parameter name

 

NAME                                
TYPE        VALUE



db_file_name_convert                
string      oradglg, oradg11g

db_name                             
string      oradg11g

db_unique_name                      
string      oradg11g

global_names                        
boolean     FALSE

instance_name                       
string      oradg11g

lock_name_space                     
string

log_file_name_convert               
string      oradglg, oradg11g

processor_group_name                
string

service_names                       
string      oradg11g

10:17:08
SQL> show parameter spfile

 

NAME                                
TYPE        VALUE



spfile                              
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/spfileoradg11g.o

                                                
ra

10:17:14
SQL>  show parameter DG_BROKER_CONFIG_FILE

 

NAME                                
TYPE        VALUE



dg_broker_config_file1              
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/dr1oradg11g.dat

dg_broker_config_file2              
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/dr2oradg11g.dat

10:20:43
SQL> set line 9999

10:20:44
SQL> col name format a10

10:20:44
SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:20:44
SQL> select
dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status
from v$database;

 

     
DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL    
DATABASE_ROLE    FOR OPEN_MODE           
SWITCHOVER_STATUS





1403587593
ORADG11G       2336836 MAXIMUM PERFORMANCE  MAXIMUM
PERFORMANCE 
PRIMARY          YES READ WRITE           TO STANDBY

 

都为此时间: 
00: 00: 00.01

10:20:44
SQL> SELECT d.DBID,

10:20:44  
2         d.DB_UNIQUE_NAME,

10:20:44  
3         d.FORCE_LOGGING,

10:20:44  
4         d.FLASHBACK_ON,

10:20:44  
5         d.FS_FAILOVER_STATUS,

10:20:44  
6         d.FS_FAILOVER_CURRENT_TARGET,

10:20:44  
7         d.FS_FAILOVER_THRESHOLD,

10:20:44  
8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:20:44  
9         d.FS_FAILOVER_OBSERVER_HOST

10:20:44 
10    FROM v$database d;

 

     
DBID DB_UNIQUE_NAME                 FOR FLASHBACK_ON      
FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET    
FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER





1403587593
oradg11g                       YES YES               
DISABLED                                                                 
0

 

现已就此时: 
00: 00: 00.00

10:20:45
SQL>

10:20:45
SQL> ! ps -ef|grep dmon

oracle   
7936  7895  0 10:21 pts/4    00:00:00 /bin/bash -c  ps -ef|grep
dmon

oracle   
7938  7936  0 10:21 pts/4    00:00:00 grep dmon

 

10:21:17
SQL> show parameter dg_broker

 

NAME                                
TYPE        VALUE



dg_broker_config_file1              
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/dr1oradg11g.dat

dg_broker_config_file2              
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/dr2oradg11g.dat

dg_broker_start                     
boolean     FALSE

10:21:39
SQL> show parameter dg_broker_start

 

NAME                                
TYPE        VALUE



dg_broker_start                     
boolean     FALSE

10:21:53
SQL>
alter system set dg_broker_start=true scope=both;

 

系统已经变更。

 

曾经用时: 
00: 00: 00.02

10:21:56
SQL> ! ps -ef|grep dmon

oracle   
7975     1  0 10:21 ?        00:00:00 ora_dmon_oradg11g

oracle   
7976  7895  0 10:22 pts/4    00:00:00 /bin/bash -c  ps -ef|grep
dmon

oracle   
7978  7976  0 10:22 pts/4    00:00:00 grep dmon

 

10:22:02
SQL>

10:22:02
SQL> show parameter dg_broker_start

 

NAME                                
TYPE        VALUE



dg_broker_start                     
boolean     TRUE

10:23:48
SQL> set line 9999

10:23:58
SQL> col name format a10

10:23:58
SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:23:58
SQL> select
dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status
from v$database;

 

     
DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL    
DATABASE_ROLE    FOR OPEN_MODE           
SWITCHOVER_STATUS





1403587593
ORADG11G       2337043 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE 
PRIMARY          YES READ WRITE           TO STANDBY

 

业已为此时间: 
00: 00: 00.01

10:23:58
SQL> SELECT d.DBID,

10:23:58  
2         d.DB_UNIQUE_NAME,

10:23:58  
3         d.FORCE_LOGGING,

10:23:58  
4         d.FLASHBACK_ON,

10:23:58  
5         d.FS_FAILOVER_STATUS,

10:23:58  
6         d.FS_FAILOVER_CURRENT_TARGET,

10:23:58  
7         d.FS_FAILOVER_THRESHOLD,

10:23:58  
8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:23:58  
9         d.FS_FAILOVER_OBSERVER_HOST

10:23:58 
10    FROM v$database d;

 

     
DBID DB_UNIQUE_NAME                 FOR FLASHBACK_ON      
FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET    
FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER





1403587593
oradg11g                       YES YES               
DISABLED                                                                 
0

 

早已就此时间: 
00: 00: 00.00

10:24:00
SQL>

 

报警日志:

Mon
Sep 28 10:21:56 2015

ALTER
SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

Mon
Sep 28 10:21:56 2015

DMON
started with pid=37, OS id=7975

Starting
Data Guard Broker (DMON)

Mon
Sep 28 10:22:04 2015

INSV
started with pid=38, OS id=7984

 

备库操作:

 

[oracle@rhel6_lhr
~]$ sqlplus / as sysdba

 

SQL*Plus:
Release 11.2.0.3.0 Production on 星期一 9月 28 10:25:25 2015

 

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

 

 

连接到:

Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit
Production

With
the Partitioning, OLAP, Data Mining and Real Application Testing
options

 

10:25:25
SQL> show parameter name

 

NAME                                
TYPE        VALUE



db_file_name_convert                
string      oradg11g, oradgphy

db_name                             
string      oradg11g

db_unique_name                      
string      oradgphy

global_names                        
boolean     FALSE

instance_name                       
string      oradgphy

lock_name_space                     
string

log_file_name_convert               
string      oradg11g, oradgphy

processor_group_name                
string

service_names                       
string      oradgphy

10:25:27
SQL>  show parameter spfile

 

NAME                                
TYPE        VALUE



spfile   
                          
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/spfileoradgphy.o

                                                
ra

10:25:35
SQL> show parameter DG_BROKER_CONFIG_FILE

 

NAME                                
TYPE        VALUE



dg_broker_config_file1
             
string      /u01/app/oracle/product/11.2.0

                      
                         
/dbhome_1/dbs/dr1oradgphy.dat

dg_broker_config_file2
             
string      /u01/app/oracle/product/11.2.0

                                                
/dbhome_1/dbs/dr2oradgphy.dat

10:25:43
SQL> set line 9999

10:25:51
SQL> col name format a10

10:25:51
SQL> col FS_FAILOVER_OBSERVER_HOST format a20

10:25:51
SQL> select
dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status
from v$database;

SELECT
d.DBID,

      
d.DB_UNIQUE_NAME,

      
d.FORCE_LOGGING,

      
d.FLASHBACK_ON,

      
d.FS_FAILOVER_STATUS,

 

     
DBID NAME       CURRENT_SCN PROTECTION_MODE      PROTECTION_LEVEL    
DATABASE_ROLE    FOR OPEN_MODE           
SWITCHOVER_STATUS





1403587593
ORADG11G       2337499 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE 
PHYSICAL
STANDBY YES
READ ONLY WITH APPLY NOT ALLOWED

 

曾为此时间: 
00: 00: 00.11

10:25:52
SQL> 10:25:52   2  10:25:52   3  10:25:52   4  10:25:52   5 
10:25:52   6         d.FS_FAILOVER_CURRENT_TARGET,

10:25:52  
7         d.FS_FAILOVER_THRESHOLD,

10:25:52  
8         d.FS_FAILOVER_OBSERVER_PRESENT,

10:25:52  
9         d.FS_FAILOVER_OBSERVER_HOST

10:25:52 
10    FROM v$database d;

 

     
DBID DB_UNIQUE_NAME                 FOR FLASHBACK_ON      
FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET    
FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER





1403587593
oradgphy                       YES NO                
DISABLED                                                                 
0

 

就就此时: 
00: 00: 00.00

10:25:56
SQL>  ! ps -ef|grep dmon

oracle   
7975     1  0 10:21 ?        00:00:00 ora_dmon_oradg11g

oracle   
8461  8410  0 10:26 pts/5    00:00:00 /bin/bash -c  ps -ef|grep
dmon

oracle   
8463  8461  0 10:26 pts/5    00:00:00 grep dmon

 

10:26:07
SQL>  show parameter dg_broker_start

 

NAME                                
TYPE        VALUE



dg_broker_start                     
boolean     FALSE

10:26:39
SQL>
alter system set dg_broker_start=true scope=both;

 

网曾改成。

 

都用时: 
00: 00: 00.05

10:26:46
SQL>  ! ps -ef|grep dmon

oracle   
7975     1  0 10:21 ?        00:00:00 ora_dmon_oradg11g

oracle   
8481     1  0 10:26 ?        00:00:00 ora_dmon_oradgphy

oracle   
8501  8410  0 10:26 pts/5    00:00:00 /bin/bash -c  ps -ef|grep
dmon

oracle   
8503  8501  0 10:26 pts/5    00:00:00 grep dmon

 

10:26:50
SQL>

 

(ADDRESS_LIST

(address=(protocol=tcp)(host=192.168.1.44)(port=1521))

(address=(protocol=tcp)(host=192.168.1.45)(port=1521))

(address=(protocol=tcp)(host=192.168.1.46)(port=1521))

(load_balance=yes)

)

一.4.3  dgmgrl中配置broker并启用

[oracle@rhel6_lhr
~]$ dgmgrl

DGMGRL
for Linux: Version 11.2.0.3.0 – 64bit Production

 

Copyright
(c) 2000, 2009, Oracle. All rights reserved.

 

迎使用
DGMGRL, 要拿走有关信息请键入 “help”。

DGMGRL>
help

 

可是运以下命令:

 

add           
在中介安排中补充加备用数据库

connect       
连接至 Oracle 数据库实例

convert       
将数据库从平栽类型转换为任何一样种植

create        
创建中介安排

disable       
禁用配置, 数据库或快启动故障转移

edit          
编辑配置, 数据库或实例

enable        
启用配置, 数据库或快启动故障转移

exit          
退出程序

failover      
将备用数据库更改为主数据库

help          
显示命令的求证与语法

quit          
退出程序

reinstate     
将符号为还原的数据库更改为使得的备用数据库

rem           
DGMGRL 会忽略注释

remove        
删除配置, 数据库或实例

show          
显示有关部署, 数据库或实例的音信

shutdown      
关闭时在运作的 Oracle 数据库实例

sql           
执行 SQL 语句

start         
启动快速启动故障转移观察程序

startup       
启动 Oracle 数据库实例

stop          
停止快速启动故障转移观察程序

switchover    
在主数据库和备用数据库中切换角色

 

使用
“help <command>” 可以查各个命令的语法

 

DGMGRL>
connect
sys/lhr@tns_oradg11g_dgmgrl

已连接。

DGMGRL>

DGMGRL>
show
configuration

ORA-16532:
Data Guard 中介安排不设有

 

安排详细资料不能够由
DGMGRL 确定

DGMGRL>
help create 

 

开创中介安排

 

语法:

 

 
CREATE CONFIGURATION <configuration name> AS

   
PRIMARY DATABASE IS <database name>

   
CONNECT IDENTIFIER IS <connect identifier>;

 

DGMGRL>
create
configuration ‘fsf_oradg11g_lhr’ as

>
primary database is ‘oradg11g’

>
connect identifier is tns_oradg11g_dgmgrl;

业已创造布局
“fsf_oradg11g_lhr”, 其中主数据库为 “oradg11g”

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradg11g – 主数据库

 

高效启动故障转移:
DISABLED

 

布状态:

DISABLED

 

DGMGRL>
add database ‘oradgphy’ as

>
connect identifier is tns_oradgphy_dgmgrl

>
maintained as physical;

就长数据库
“oradgphy”

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradg11g – 主数据库

   
oradgphy – 物理备用数据库

 

疾启动故障转移:
DISABLED

 

配置状态:

DISABLED

 

DGMGRL>

 

DGMGRL>
enable configuration

已启用。

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradg11g – 主数据库

   
oradgphy – 物理备用数据库

 

迅速启动故障转移:
DISABLED

 

安排状态:

SUCCESS

 

DGMGRL>

 

DGMGRL>

DGMGRL>

show database verbose oradg11g

 

数据库

  • oradg11g

 

 
角色:            PRIMARY

 
预期状态:        TRANSPORT-ON

 
实例:

   
oradg11g

 

 
属性:

   
DGConnectIdentifier             = ‘tns_oradg11g_dgmgrl’

   
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               = ‘oradgphy, oradg11g’

   
LogFileNameConvert              = ‘oradgphy, oradg11g’

   
FastStartFailoverTarget         = ”

   
InconsistentProperties          = ‘(monitor)’

   
InconsistentLogXptProps         = ‘(monitor)’

   
SendQEntries                    = ‘(monitor)’

   
LogXptStatus                    = ‘(monitor)’

   
RecvQEntries                    = ‘(monitor)’

   
SidName                         = ‘oradg11g’

   
StaticConnectIdentifier         =
‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(INSTANCE_NAME=oradg11g)(SERVER=DEDICATED)))’

   
StandbyArchiveLocation          =
‘USE_DB_RECOVERY_FILE_DEST’

   
AlternateLocation               = ”

   
LogArchiveTrace                 = ‘0’

   
LogArchiveFormat                = ‘%t_%s_%r.dbf’

   
TopWaitEvents                   = ‘(monitor)’

 

数据库状态:

SUCCESS

 

DGMGRL> 
show
database verbose oradgphy

 

数据库

  • oradgphy

 

 
角色:            PHYSICAL STANDBY

 
预期状态:        APPLY-ON

 
传输滞后:        0 秒

 
应用滞后:        0 秒

 
实时查询:        ON

 
实例:

   
oradgphy

 

 
属性:

   
DGConnectIdentifier             = ‘tns_oradgphy_dgmgrl’

   
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               = ‘oradg11g, oradgphy’

   
LogFileNameConvert              = ‘oradg11g, oradgphy’

   
FastStartFailoverTarget         = ”

   
InconsistentProperties          = ‘(monitor)’

   
InconsistentLogXptProps         = ‘(monitor)’

   
SendQEntries                    = ‘(monitor)’

   
LogXptStatus                    = ‘(monitor)’

   
RecvQEntries                    = ‘(monitor)’

   
SidName                         = ‘oradgphy’

   
StaticConnectIdentifier         =
‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel6_lhr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oradgphy_DGMGRL.lhr.com)(INSTANCE_NAME=oradgphy)(SERVER=DEDICATED)))’

   
StandbyArchiveLocation          =
‘USE_DB_RECOVERY_FILE_DEST’

   
AlternateLocation               = ”

   
LogArchiveTrace                 = ‘0’

   
LogArchiveFormat                = ‘%t_%s_%r.dbf’

   
TopWaitEvents                   = ‘(monitor)’

 

数据库状态:

SUCCESS

 

DGMGRL>

DGMGRL>

 

 

主库告警日志:

 

Mon
Sep 28 15:20:28 2015

NSV1
started with pid=30, OS id=25660

Mon
Sep 28 15:20:54 2015

ALTER
SYSTEM SET log_archive_config=’dg_config=(oradg11g,oradgphy)’
SCOPE=BOTH;

Mon
Sep 28 15:20:57 2015

ALTER
SYSTEM SET log_archive_dest_2=’service=”tns_oradgphy_dgmgrl”‘,’LGWR
ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0
max_connections=1 reopen=300 db_unique_name=”oradgphy”
net_timeout=30′,’valid_for=(all_logfiles,primary_role)’
SCOPE=BOTH;

ALTER
SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID=’oradg11g’;

ALTER
SYSTEM SET log_archive_format=’%t_%s_%r.dbf’ SCOPE=SPFILE
SID=’oradg11g’;

ALTER
SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID=’*’;

ALTER
SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET db_file_name_convert=’oradgphy’,’oradg11g’
SCOPE=SPFILE;

ALTER
SYSTEM SET log_file_name_convert=’oradgphy’,’oradg11g’
SCOPE=SPFILE;

ALTER
SYSTEM ARCHIVE LOG

Mon
Sep 28 15:20:57 2015

NSA2
started with pid=34, OS id=25706

 

 

备库告警日志:

 

NSV0
started with pid=21, OS id=25696

Mon
Sep 28 15:20:57 2015

ALTER
SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID=’oradgphy’;

ALTER
SYSTEM SET log_archive_format=’%t_%s_%r.dbf’ SCOPE=SPFILE
SID=’oradgphy’;

ALTER
SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID=’*’;

ALTER
SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET db_file_name_convert=’oradg11g’,’oradgphy’
SCOPE=SPFILE;

ALTER
SYSTEM SET log_file_name_convert=’oradg11g’,’oradgphy’
SCOPE=SPFILE;

ALTER
SYSTEM SET fal_server=’tns_oradg11g_dgmgrl’ SCOPE=BOTH;

Mon
Sep 28 15:21:01 2015

Primary
database is in MAXIMUM PERFORMANCE mode

RFS[4]:
Assigned to RFS process 25708

RFS[4]:
Selected log 4 for thread 1 sequence 145 dbid 1403587593 branch
886695024

 

 

【DATAGUARD】物理dg配置客户端无缝切换
(八.3)–客户端TAF 配置

一.4  实验部分

 

一.3.4.1  使用tnsnames 配置

原稿链接:http://aijuans.iteye.com/blog/1488998

 

 

格式一: 
Oracle JDBC Thin using a ServiceName:

jdbc:oracle:thin:@//<host>:<port>/<service_name>

Example:
jdbc:oracle:thin:@//192.168.2.1:1521/XE

 

注意这里的格式,@后面有//,
这是与利用SID的最主要分。

这种格式是Oracle
推荐的格式,因为对此集群来说,每个节点的SID
是未相同的,但是SERVICE_NAME 确可以蕴涵有节点。

 

格式二:
Oracle JDBC Thin using an SID:

jdbc:oracle:thin:@<host>:<port>:<SID>

Example:
jdbc:oracle:thin:192.168.2.1:1521:X01A

Note:
Support for SID is being phased out. Oracle recommends that users switch
over to usingservice names.

 

 

格式三:Oracle
JDBC Thin using a TNSName:

jdbc:oracle:thin:@<TNSName>

Example:
jdbc:oracle:thin:@GL

Note:

Support
for TNSNames was added in the driver release 10.2.0.1

 

 

 

二.测试

 

2.1
准备工作:

 

Oracle
是11gR2

 

Listener.ora

一.1  BLOG文档结构图

外汇 2 

 

 

一.2.4  本文简介

 

本篇blog是基于cuug的公开课内容,我好开展实施的操作,视频可以参考:http://blog.itpub.net/26736162/viewspace-1624453/
,简介我就不多写了,把cuug的情节一直copy过来吧,觉得还是比实用之。

 

以此技能如果你免晓,不可知算是ORACLE高手

这个技术如果你不知情,就未可知说您会DataGuard

斯技术如果你免掌握,……

 

此次网络课程,研究当主备库发生切换时,如何以主库启动一个service,保证客户端的连年能够继承,而且还能持续select查询操作,而无论是主备库是在哪台服务器上;同时确保新的客户连接没有外的题目。以学科网络达到的事例不多,陈先生花了邻近一年的日人肉搜索,最近才找到,急不可待的而享受给大家。

 

1、DataGuard的配置(快速)

2、创建service

3、创建触发器

4、主备库切换测试

 

鉴于内容比较多,我打算分为4单章来共享于大家,贴个图,不要奇怪,还有一个章节是尝试过程遭到配至之问题解决。

外汇 3 

 

本篇为第二节约,Fast-Start
Failover
的布置。

 

 

 

一.2.1  导读

诸君技术爱好者,看了本文后,你得操纵如下的技术,也可学到一些另你所不亮堂的学问,~O(∩_∩)O~:

① Data
Guard Broker 的配置


Fast-Start Failover 的配置


Oracle
DataGuard 之客户端TAF 配置


使用DGMGRL 来治本数据库

⑤ 物理dg管理与保障的组成部分sql


DataGuard 客户端特级配置

 

留神:本篇BLOG中代码有要专门关爱的地方我都因此黄色背景以及辛亥革命字体来表示,比如下面的例证中,thread
1的卓绝老归档日志号为33,thread
2的不过深归档日志号为43凡要特别关注之地方。

 
List of Archived Logs in backup set 11

 
Thrd Seq     Low SCN    Low Time            Next SCN   Next
Time

 



 
1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29
11:15:48

 
1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29
11:15:58

 
2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29
11:15:49

 
2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29
11:15:53

 

 

 

 

 

正文如产生左或无完美的地方要大家多多指正,ITPUB留言或QQ皆可,您的批评指正是本人写作的最深动力。

(connect_data

(service_name=ORACMS)

一.4.4  测试Data Guard Broker 功能

当布置broker完成后,我们尽管好采用broker完成switchover了。在DGMGRL中仅仅需要简单的一个指令。
switchover
to standby-database
(standby-database:
此参数是db_unique_name,大小写敏感。如果名称是大写的,需要因此引号。表示切换standby-database为主机,原主机转换为备机状态)

 

 

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradg11g – 主数据库

   
oradgphy – 物理备用数据库

 

飞启动故障转移:
DISABLED

 

配备状态:

SUCCESS

 

DGMGRL>
switchover
to oradgphy;

眼看执行切换,
请稍候…

初的主数据库
“oradgphy” 正在打开…

操作要求关闭实例
“oradg11g” (在数据库 “oradg11g” 上)

刚好于关门实例
“oradg11g”…

ORACLE
例程已经关门。

操作要求启动实例
“oradg11g” (在数据库 “oradg11g” 上)

巧以起步实例
“oradg11g”…

ORACLE
例程已经起步。

数据库装载了。

数据库都打开。

切换成,
新的主数据库为 “oradgphy”

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradgphy – 主数据库

   
oradg11g – 物理备用数据库

 

敏捷启动故障转移:
DISABLED

 

布置状态:

SUCCESS

 

DGMGRL>

 

 

重复切换一软,回到最初状态:

DGMGRL>
switchover to oradg11g;

立实施切换,
请稍候…

新的主数据库
“oradg11g” 正在打开…

操作要求关闭实例
“oradgphy” (在数据库 “oradgphy” 上)

刚刚于关闭实例
“oradgphy”…

ORACLE
例程已经关闭。

操作要求启动实例
“oradgphy” (在数据库 “oradgphy” 上)

正于启动实例
“oradgphy”…

ORACLE
例程已经起步。

数据库装载完毕。

数据库都打开。

切换成,
新的主数据库为 “oradg11g”

DGMGRL>
show configuration

 

配置

  • fsf_oradg11g_lhr

 

 
保护模式:        MaxPerformance

 
数据库:

   
oradg11g – 主数据库

   
oradgphy – 物理备用数据库

 

快捷启动故障转移:
DISABLED

 

部署状态:

SUCCESS

 

DGMGRL>

 

率先软切换的早晚告警日志情况:

主库告警日志:

 

 

 

Mon
Sep 28 15:26:24 2015

ALTER
DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION
SHUTDOWN

ALTER
DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 23943]
(oradg11g)

Mon
Sep 28 15:26:24 2015

Thread
1 advanced to log sequence 146 (LGWR switch)

 
Current log# 2 seq# 146 mem# 0:
/u01/app/oracle/oradata/oradg11g/redo02.log

Mon
Sep 28 15:26:24 2015

Stopping
background process CJQ0

Stopping
background process QMNC

Stopping
Job queue slave processes, flags = 27

Mon
Sep 28 15:26:27 2015

Archived
Log entry 501 added for thread 1 sequence 145 ID 0x5495956b dest
1:

Waiting
for Job queue slaves to complete

Job
queue slave processes stopped

All
dispatchers and shared servers shutdown

CLOSE:
killing server sessions.

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

Active
process 25846 user ‘oracle’ program ‘oracle@rhel6_lhr
(W000)’

CLOSE:
all sessions shutdown successfully.

Waiting
for all non-current ORLs to be 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…

Active,
synchronized Physical Standby switchover target has been
identified

Switchover
End-Of-Redo Log thread 1 sequence 146 has been fixed

Switchover:
Primary highest seen SCN set to 0x0.0x262c94

ARCH:
Noswitch archival of thread 1, sequence 146

ARCH:
End-Of-Redo Branch archival of thread 1 sequence 146

ARCH:
LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH:
Standby redo logfile selected for thread 1 sequence 146 for destination
LOG_ARCHIVE_DEST_2

Archived
Log entry 502 added for thread 1 sequence 146 ID 0x5495956b dest
1:

ARCH:
Archiving is disabled due to current logfile archival

Primary
will check for some target standby to have received alls
redo

Final
check for a synchronized target standby. Check will be made
once.

Archive
destination LOG_ARCHIVE_DEST_3 invalidated

DB_UNIQUE_NAME
oradglg is not in the Data Guard configuration

Archive
destination LOG_ARCHIVE_DEST_4 invalidated

DB_UNIQUE_NAME
oradgss is not in the Data Guard configuration

LOG_ARCHIVE_DEST_2
is a potential Physical Standby switchover target

Active,
synchronized target has been identified

Target
has also received all redo

Backup
controlfile written to trace file
/u01/app/oracle/diag/rdbms/oradg11g/oradg11g/trace/oradg11g_rsm0_23943.trc

Clearing
standby activation ID 1419089259 (0x5495956b)

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 146 required for standby recovery

Switchover:
Primary controlfile converted to standby controlfile
succesfully.

Switchover:
Complete – Database shutdown required

Completed:
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION
SHUTDOWN

Mon
Sep 28 15:26:37 2015

Performing
implicit shutdown abort due to switchover to physical
standby

Shutting
down instance (abort)

License
high water mark = 8

USER
(ospid: 26018): terminating the instance

Instance
terminated by USER, pid = 26018

Mon
Sep 28 15:26:38 2015

Instance
shutdown complete

ORA-1092
: opitsk aborting process

Mon
Sep 28 15:26:38 2015

Starting
ORACLE instance (normal)

LICENSE_MAX_SESSION
= 0

LICENSE_SESSIONS_WARNING
= 0

Picked
latch-free SCN scheme 3

Archive
destination LOG_ARCHIVE_DEST_3 invalidated

DB_UNIQUE_NAME
oradglg is not in the Data Guard configuration

Archive
destination LOG_ARCHIVE_DEST_4 invalidated

DB_UNIQUE_NAME
oradgss is not in the Data Guard configuration

Autotune
of undo retention is turned on.

IMODE=BR

ILAT
=27

LICENSE_MAX_USERS
= 0

SYS
auditing is disabled

Starting
up:

Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit
Production

With
the Partitioning, OLAP, Data Mining and Real Application Testing
options.

ORACLE_HOME
= /u01/app/oracle/product/11.2.0/dbhome_1

System
name:    Linux

Node
name:      rhel6_lhr

Release:       
2.6.32-504.16.2.el6.x86_64

Version:       
#1 SMP Tue Apr 21 08:37:59 PDT 2015

Machine:       
x86_64

VM
name:        VMWare Version: 6

Using
parameter settings in server-side spfile
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoradg11g.ora

System
parameters with non-default values:

 
processes                = 150

 
memory_target            = 300M

 
memory_max_target        = 400M

 
control_files            =
“/u01/app/oracle/oradata/oradg11g/control01.ctl”

 
control_files            =
“/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl”

 
db_file_name_convert     = “oradgphy”

 
db_file_name_convert     = “oradg11g”

 
log_file_name_convert    = “oradgphy”

 
log_file_name_convert    = “oradg11g”

 
db_block_size            = 8192

 
compatible               = “11.2.0.0.0”

 
log_archive_dest_1       = “LOCATION=USE_DB_RECOVERY_FILE_DEST
db_unique_name=oradg11g
valid_for=(ALL_LOGFILES,ALL_ROLES)”

 
log_archive_dest_2       =
“service=”tns_oradgphy_dgmgrl””

 
log_archive_dest_2       = “LGWR ASYNC NOAFFIRM delay=0 optional
compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name=”oradgphy” net_timeout=30″

 
log_archive_dest_2       =
“valid_for=(all_logfiles,primary_role)”

 
log_archive_dest_3       = “SERVICE=tns_oradglg LGWR ASYNC
db_unique_name=oradglg
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)”

 
log_archive_dest_4       = “SERVICE=tns_oradgss LGWR ASYNC
db_unique_name=oradgss
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)”

 
log_archive_dest_state_1 = “ENABLE”

 
log_archive_dest_state_2 = “ENABLE”

 
log_archive_dest_state_3 = “defer”

 
log_archive_dest_state_4 = “defer”

 
log_archive_min_succeed_dest= 1

 
fal_client               = “oradg11g”

 
fal_server               = “tns_oradgphy_dgmgrl”

 
log_archive_trace        = 0

 
log_archive_config       =
“dg_config=(oradg11g,oradgphy)”

 
log_archive_format       = “%t_%s_%r.dbf”

 
log_archive_max_processes= 4

 
archive_lag_target       = 0

 
db_recovery_file_dest    =
“/u01/app/oracle/flash_recovery_area”

 
db_recovery_file_dest_size= 4122M

 
standby_file_management  = “AUTO”

 
undo_tablespace          = “UNDOTBS1”

 
remote_login_passwordfile= “EXCLUSIVE”

 
db_domain                = “lhr.com”

 
global_names             = TRUE

 
dispatchers              = “(PROTOCOL=TCP)
(SERVICE=oradg11gXDB)”

 
audit_file_dest          =
“/u01/app/oracle/admin/oradg11g/adump”

 
audit_trail              = “DB”

 
db_name                  = “oradg11g”

 
db_unique_name           = “oradg11g”

 
open_cursors             = 300

 
dg_broker_start          = TRUE

 
diagnostic_dest          = “/u01/app/oracle”

Mon
Sep 28 15:26:38 2015

PMON
started with pid=2, OS id=26102

Mon
Sep 28 15:26:38 2015

PSP0
started with pid=3, OS id=26104

Mon
Sep 28 15:26:39 2015

Using
STANDBY_ARCHIVE_DEST parameter default value as
USE_DB_RECOVERY_FILE_DEST

destination
database instance is ‘started’ not ‘mounted’

Mon
Sep 28 15:26:39 2015

VKTM
started with pid=4, OS id=26112 at elevated priority

VKTM
running at (1)millisec precision with DBRM quantum (100)ms

Mon
Sep 28 15:26:39 2015

GEN0
started with pid=5, OS id=26116

Mon
Sep 28 15:26:39 2015

DIAG
started with pid=6, OS id=26118

Mon
Sep 28 15:26:40 2015

DBRM
started with pid=7, OS id=26120

Mon
Sep 28 15:26:40 2015

DIA0
started with pid=8, OS id=26122

Mon
Sep 28 15:26:40 2015

MMAN
started with pid=9, OS id=26124

Mon
Sep 28 15:26:40 2015

DBW0
started with pid=10, OS id=26126

Mon
Sep 28 15:26:40 2015

LGWR
started with pid=11, OS id=26128

Mon
Sep 28 15:26:40 2015

CKPT
started with pid=12, OS id=26130

Mon
Sep 28 15:26:40 2015

SMON
started with pid=13, OS id=26132

Mon
Sep 28 15:26:40 2015

RECO
started with pid=14, OS id=26134

Mon
Sep 28 15:26:40 2015

MMON
started with pid=15, OS id=26136

Mon
Sep 28 15:26:40 2015

MMNL
started with pid=16, OS id=26138

starting
up 1 dispatcher(s) for network address
‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…

starting
up 1 shared server(s) …

ORACLE_BASE
from environment = /u01/app/grid

Mon
Sep 28 15:26:40 2015

DMON
started with pid=19, OS id=26145

Mon
Sep 28 15:26:40 2015

alter
database  mount

ARCH:
STARTING ARCH PROCESSES

Mon
Sep 28 15:26:44 2015

ARC0
started with pid=22, OS id=26157

ARC0:
Archival started

ARCH:
STARTING ARCH PROCESSES COMPLETE

ARC0:
STARTING ARCH PROCESSES

Successful
mount of redo thread 1, with mount id 1419115888

Allocated
3981120 bytes in shared pool for flashback generation buffer

Starting
background process RVWR

Mon
Sep 28 15:26:45 2015

ARC1
started with pid=23, OS id=26159

Mon
Sep 28 15:26:45 2015

RVWR
started with pid=24, OS id=26161

Mon
Sep 28 15:26:45 2015

ARC2
started with pid=25, OS id=26163

Mon
Sep 28 15:26:45 2015

ARC3
started with pid=26, OS id=26165

ARC1:
Archival started

ARC2:
Archival started

ARC1:
Becoming the ‘no FAL’ ARCH

ARC1:
Becoming the ‘no SRL’ ARCH

ARC2:
Becoming the heartbeat ARCH

Physical
Standby Database mounted.

Lost
write protection disabled

ARC2:
Becoming the active heartbeat ARCH

Completed:
alter database  mount

alter
database  open

Data
Guard Broker initializing…

Data
Guard Broker initialization complete

AUDIT_TRAIL
initialization parameter is changed to OS, as DB is NOT compatible for
database opened with read-only access

Beginning
standby crash recovery.

Serial
Media Recovery started

Managed
Standby Recovery starting Real Time Apply

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_145_c0ktx3cj_.arc

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_146_c0ktx6mn_.arc

Identified
End-Of-Redo (switchover) for thread 1 sequence 146 at SCN
0x0.262c94

Resetting
standby activation ID 0 (0x0)

Incomplete
Recovery applied until change 2501780 time 09/28/2015
15:26:30

Completed
standby crash recovery.

SMON:
enabling cache recovery

Mon
Sep 28 15:26:46 2015

RFS[1]:
Assigned to RFS process 26177

RFS[1]:
Opened log for thread 1 sequence 148 dbid 1403587593 branch
886695024

Archived
Log entry 504 added for thread 1 sequence 148 rlc 886695024 ID
0x54960fb7 dest 2:

Dictionary
check beginning

Mon
Sep 28 15:26:46 2015

RFS[2]:
Assigned to RFS process 26179

RFS[2]:
Opened log for thread 1 sequence 147 dbid 1403587593 branch
886695024

Archived
Log entry 505 added for thread 1 sequence 147 rlc 886695024 ID
0x54960fb7 dest 2:

Dictionary
check complete

Database
Characterset is ZHS16GBK

No
Resource Manager plan active

replication_dependency_tracking
turned off (no async multimaster replication found)

Physical
standby database opened for read only access.

RFS[2]:
Selected log 4 for thread 1 sequence 149 dbid 1403587593 branch
886695024

Completed:
alter database  open

Archived
Log entry 506 added for thread 1 sequence 149 ID 0x54960fb7 dest
1:

Mon
Sep 28 15:26:46 2015

db_recovery_file_dest_size
of 4122 MB is 6.96% used. This is a

user-specified
limit on the amount of space that will be used by this

database
for recovery-related files, and does not reflect the amount
of

space
available in the underlying filesystem or ASM diskgroup.

Mon
Sep 28 15:26:46 2015

Primary
database is in MAXIMUM PERFORMANCE mode

RFS[3]:
Assigned to RFS process 26185

RFS[3]:
Selected log 4 for thread 1 sequence 150 dbid 1403587593 branch
886695024

ARC3:
Archival started

ARC0:
STARTING ARCH PROCESSES COMPLETE

Starting
Data Guard Broker (DMON)

Mon
Sep 28 15:26:48 2015

INSV
started with pid=30, OS id=26191

Mon
Sep 28 15:26:51 2015

NSV1
started with pid=31, OS id=26199

Mon
Sep 28 15:26:55 2015

RSM0
started with pid=32, OS id=26208

ALTER
SYSTEM SET log_archive_dest_state_1=’ENABLE’ SCOPE=BOTH;

ALTER
SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID=’oradg11g’;

ALTER
SYSTEM SET log_archive_format=’%t_%s_%r.dbf’ SCOPE=SPFILE
SID=’oradg11g’;

ALTER
SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID=’*’;

ALTER
SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET db_file_name_convert=’oradgphy’,’oradg11g’
SCOPE=SPFILE;

ALTER
SYSTEM SET log_file_name_convert=’oradgphy’,’oradg11g’
SCOPE=SPFILE;

ALTER
SYSTEM SET fal_server=’tns_oradgphy_dgmgrl’ SCOPE=BOTH;

ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER
DISCONNECT  USING CURRENT LOGFILE

Attempt
to start background Managed Standby Recovery process
(oradg11g)

Mon
Sep 28 15:26:59 2015

MRP0
started with pid=33, OS id=26214

MRP0:
Background Managed Standby Recovery process started
(oradg11g)

started logmerger process

Mon
Sep 28 15:27:04 2015

Managed
Standby Recovery starting Real Time Apply

Parallel
Media Recovery started with 2 slaves

Waiting
for all non-current ORLs to be archived…

All
non-current ORLs have been archived.

Clearing
online redo logfile 1
/u01/app/oracle/oradata/oradg11g/redo01.log

Clearing
online log 1 of thread 1 sequence number 150

Clearing
online redo logfile 1 complete

Clearing
online redo logfile 2
/u01/app/oracle/oradata/oradg11g/redo02.log

Clearing
online log 2 of thread 1 sequence number 146

Mon
Sep 28 15:27:05 2015

Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER
DISCONNECT  USING CURRENT LOGFILE

Clearing
online redo logfile 2 complete

Clearing
online redo logfile 3
/u01/app/oracle/oradata/oradg11g/redo03.log

Clearing
online log 3 of thread 1 sequence number 149

Clearing
online redo logfile 3 complete

Mon
Sep 28 15:27:07 2015

RFS[3]:
Selected log 5 for thread 1 sequence 151 dbid 1403587593 branch
886695024

Mon
Sep 28 15:27:07 2015

Archived
Log entry 507 added for thread 1 sequence 150 ID 0x54960fb7 dest
1:

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_147_c0ktxp86_.arc

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_148_c0ktxp70_.arc

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_149_c0ktxpgt_.arc

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_09_28/o1_mf_1_150_c0ktycgh_.arc

Media
Recovery Waiting for thread 1 sequence 151 (in transit)

Recovery
of Online Redo Log: Thread 1 Group 5 Seq 151 Reading mem 0

 
Mem# 0:
/u01/app/oracle/oradata/oradg11g/standby_redo05.log

 

 

 

 

 

备库告警日志:

 

 

 

Mon
Sep 28 15:26:24 2015

Archived
Log entry 128 added for thread 1 sequence 145 ID 0x5495956b dest
1:

Mon
Sep 28 15:26:24 2015

Media
Recovery Waiting for thread 1 sequence 146

Mon
Sep 28 15:26:30 2015

RFS[6]:
Assigned to RFS process 25998

RFS[6]:
Selected log 4 for thread 1 sequence 146 dbid 1403587593 branch
886695024

Mon
Sep 28 15:26:30 2015

Archived
Log entry 129 added for thread 1 sequence 146 ID 0x5495956b dest
1:

Mon
Sep 28 15:26:30 2015

ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

MRP0:
Background Media Recovery cancelled with status 16037

Errors
in file
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_24252.trc:

ORA-16037:
user requested cancel of managed recovery operation

Managed
Standby Recovery not using Real Time Apply

Recovery
interrupted!

Mon
Sep 28 15:26:31 2015

MRP0:
Background Media Recovery process shutdown (oradgphy)

Managed
Standby Recovery Canceled (oradgphy)

Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database
not available for switchover

 
End-Of-REDO archived log file has not been recovered

 
Incomplete recovery SCN:0:2481770 archive SCN:0:2501780

ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER
NODELAY

Media
Recovery Start: Managed Standby Recovery (oradgphy)

started logmerger process

Mon
Sep 28 15:26:32 2015

Managed
Standby Recovery not using Real Time Apply

Parallel
Media Recovery started with 2 slaves

Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_09_28/o1_mf_1_146_c0ktx6n6_.arc

Identified
End-Of-Redo (switchover) for thread 1 sequence 146 at SCN
0x0.262c94

Resetting
standby activation ID 1419089259 (0x5495956b)

Media
Recovery End-Of-Redo indicator encountered

Media
Recovery Applied through change 2501780

Media
Recovery Complete: End-Of-REDO (oradgphy)

Attempt
to set limbo arscn 0:2501780 irscn 0:2501780

Completed:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH LAST SWITCHOVER
NODELAY

ALTER
DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION
SHUTDOWN

ALTER
DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum
wait for role transition is 15 minutes.

krsv_proc_kill:
Killing 2 processes (all RFS)

All
dispatchers and shared servers shutdown

CLOSE:
killing server sessions.

CLOSE:
all sessions shutdown successfully.

Mon
Sep 28 15:26:34 2015

SMON:
disabling cache recovery

Backup
controlfile written to trace file
/u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_rsm0_24244.trc

SwitchOver
after complete recovery through change 2501780

Online
log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was
previously cleared

Online
log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was
previously cleared

Online
log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was
previously cleared

Standby
became primary SCN: 2501778

AUDIT_TRAIL
initialization parameter is changed back to its original value as
specified in the parameter file.

Switchover:
Complete – Database mounted as primary

Completed:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION
SHUTDOWN

Mon
Sep 28 15:26:36 2015

idle
dispatcher ‘D000’ terminated, pid = (17, 1)

ALTER
DATABASE OPEN

Data
Guard Broker initializing…

Mon
Sep 28 15:26:37 2015

Assigning
activation ID 1419120567 (0x54960fb7)

Mon
Sep 28 15:26:37 2015

ARC3:
Becoming the ‘no SRL’ ARCH

Thread
1 advanced to log sequence 148 (thread open)

ARC0:
Becoming the ‘no SRL’ ARCH

Thread
1 opened at log sequence 148

 
Current log# 2 seq# 148 mem# 0:
/u01/app/oracle/oradata/oradgphy/redo02.log

Successful
open of redo thread 1

MTTR
advisory is disabled because FAST_START_MTTR_TARGET is not
set

SMON:
enabling cache recovery

Archived
Log entry 130 added for thread 1 sequence 147 ID 0x54960fb7 dest
1:

ARC3:
Becoming the ‘no SRL’ ARCH

Mon
Sep 28 15:26:37 2015

NSA2
started with pid=17, OS id=26022

Mon
Sep 28 15:26:37 2015

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

[24244]
Successfully onlined Undo Tablespace 2.

Undo
initialization finished serial:0 start:21508174 end:21508224 diff:50 (0
seconds)

Dictionary
check beginning

TNS-12537:
TNS:connection closed

TNS-12537:
TNS:connection closed

   
ns secondary err code: 12560

   
nt main err code: 507

       
ns secondary err code: 12560

 

   
nt main err code: 507

TNS-00507:
Connection closed

       
nt secondary err code: 0

 

   
nt OS err code: 0

TNS-00507:
Connection closed

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

TNS-12537:
TNS:connection closed

   
ns secondary err code: 12560

   
nt main err code: 507

   

TNS-00507:
Connection closed

   
nt secondary err code: 0

   
nt OS err code: 0

Dictionary
check complete

Verifying
file header compatibility for 11g tablespace encryption..

Verifying
11g file header compatibility for tablespace encryption
completed

SMON:
enabling tx recovery

Database
Characterset is ZHS16GBK

Starting
background process SMCO

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

TNS-12537:
TNS:connection closed

   
ns secondary err code: 12560

   
nt main err code: 507

   

TNS-00507:
Connection closed

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

TNS-12537:
TNS:connection closed

   
ns secondary err code: 12560

   
nt main err code: 507

   

TNS-00507:
Connection closed

   
nt secondary err code: 0

   
nt OS err code: 0

Error
12537 received logging on to the standby

FAL[server,
ARC3]: Error 12537 creating remote archivelog file
‘tns_oradg11g_dgmgrl’

FAL[server,
ARC3]: FAL archive failed, see trace file.

ARCH:
FAL archive failed. Archiver continuing

ORACLE
Instance oradgphy – Archival Error. Archiver continuing.

Mon
Sep 28 15:26:37 2015

SMCO
started with pid=26, OS id=26033

 

 

***********************************************************************

 

Fatal
NI connect error 12537, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGMGRL.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:37

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12537

   

TNS-12537:
TNS:connection closed

   
ns secondary err code: 12560

   
nt main err code: 507

   

TNS-00507:
Connection closed

   
nt secondary err code: 0

   
nt OS err code: 0

Error
12537 received logging on to the standby

PING[ARC2]:
Heartbeat failed to connect to standby ‘tns_oradg11g_dgmgrl’. Error is
12537.

No
Resource Manager plan active

Starting
background process QMNC

Mon
Sep 28 15:26:37 2015

QMNC
started with pid=27, OS id=26036

LOGSTDBY:
Validating controlfile with logical metadata

LOGSTDBY:
Validation complete

Completed:
ALTER DATABASE OPEN

ALTER
SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID=’oradgphy’;

ALTER
SYSTEM SET log_archive_format=’%t_%s_%r.dbf’ SCOPE=SPFILE
SID=’oradgphy’;

Starting
background process CJQ0

Mon
Sep 28 15:26:37 2015

CJQ0
started with pid=32, OS id=26050

ALTER
SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID=’*’;

ALTER
SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH
SID=’*’;

ALTER
SYSTEM SET db_file_name_convert=’oradg11g’,’oradgphy’
SCOPE=SPFILE;

ALTER
SYSTEM SET log_file_name_convert=’oradg11g’,’oradgphy’
SCOPE=SPFILE;

ARC2:
STARTING ARCH PROCESSES

ALTER
SYSTEM SET log_archive_dest_state_2=’ENABLE’ SCOPE=BOTH;

ALTER
SYSTEM ARCHIVE LOG

Mon
Sep 28 15:26:38 2015

ARC4
started with pid=28, OS id=26052

ARC4:
Archival started

ARC2:
STARTING ARCH PROCESSES COMPLETE

PING[ARC2]:
Heartbeat failed to connect to standby ‘tns_oradg11g_dgmgrl’. Error is
16058.

Thread
1 advanced to log sequence 149 (LGWR switch)

 
Current log# 3 seq# 149 mem# 0:
/u01/app/oracle/oradata/oradgphy/redo03.log

Archived
Log entry 131 added for thread 1 sequence 148 ID 0x54960fb7 dest
1:

Mon
Sep 28 15:26:41 2015

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:41

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:41

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(INSTANCE_NAME=oradg11g)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:41

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:42

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:42

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

 

 

***********************************************************************

 

Fatal
NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oradg11g_DGB.lhr.com)(CID=(PROGRAM=oracle)(HOST=rhel6_lhr)(USER=grid))))

 

 
VERSION INFORMATION:

       
TNS for Linux: Version 11.2.0.3.0 – Production

       
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 –
Production

 
Time: 28-SEP-2015 15:26:42

 
Tracing not turned on.

 
Tns error struct:

   
ns main err code: 12564

   

TNS-12564:
TNS:connection refused

   
ns secondary err code: 0

   
nt main err code: 0

   
nt secondary err code: 0

   
nt OS err code: 0

ARC3:
Becoming the ‘no SRL’ ARCH

Mon
Sep 28 15:26:44 2015

ARC0:
Becoming the ‘no SRL’ ARCH

Shutting
down archive processes

ARCH
shutting down

ARC4:
Archival stopped

Mon
Sep 28 15:26:46 2015

ALTER
SYSTEM SET log_archive_dest_state_2=’ENABLE’ SCOPE=MEMORY
SID=’*’;

ARC0:
Becoming the ‘no SRL’ ARCH

ARC3:
Becoming the ‘no SRL’ ARCH

Thread
1 advanced to log sequence 150 (LGWR switch)

 
Current log# 1 seq# 150 mem# 0:
/u01/app/oracle/oradata/oradgphy/redo01.log

ARC3:
Becoming the ‘no SRL’ ARCH

Archived
Log entry 134 added for thread 1 sequence 149 ID 0x54960fb7 dest
1:

ARC0:
Becoming the ‘no SRL’ ARCH

ARC0:
Standby redo logfile selected for thread 1 sequence 149 for destination
LOG_ARCHIVE_DEST_2

******************************************************************

LGWR:
Setting ‘active’ archival for destination
LOG_ARCHIVE_DEST_2

******************************************************************

LNS:
Standby redo logfile selected for thread 1 sequence 150 for destination
LOG_ARCHIVE_DEST_2

Mon
Sep 28 15:27:05 2015

ALTER
SYSTEM ARCHIVE LOG

Mon
Sep 28 15:27:05 2015

Thread
1 cannot allocate new log, sequence 151

Checkpoint
not complete

 
Current log# 1 seq# 150 mem# 0:
/u01/app/oracle/oradata/oradgphy/redo01.log

Mon
Sep 28 15:27:07 2015

ARC3:
Becoming the ‘no SRL’ ARCH

Thread
1 advanced to log sequence 151 (LGWR switch)

 
Current log# 2 seq# 151 mem# 0:
/u01/app/oracle/oradata/oradgphy/redo02.log

Archived
Log entry 137 added for thread 1 sequence 150 ID 0x54960fb7 dest
1:

Mon
Sep 28 15:27:07 2015

LNS:
Standby redo logfile selected for thread 1 sequence 151 for destination
LOG_ARCHIVE_DEST_2

 

 

 

 

 

迄今为止,Data
Guard Broker配置了。

 

 

 

 

 

一.1  BLOG文档结构图

外汇 4 

 

 

一.4.2.1  配置db_domain 和  listener 及 tnsnames

目标:
配置db_domain 和 静态的listener
,listener中的GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain

 

主备库:

14:05:05
SQL>  show parameter db_domain 

 

NAME                                
TYPE        VALUE



db_domain                           
string

14:13:46
SQL> alter
system set db_domain=’lhr.com’ scope=spfile;

 

 

 

主备库listener的部署,注意红色的呢新增长的内容:

[oracle@rhel6_lhr
admin]$ lsnrctl status

 

LSNRCTL
for Linux: Version 11.2.0.3.0 – Production on 28-9月 -2015
15:05:27

 

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

 

正在连接到
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521)))

LISTENER
的 STATUS


别名                     
LISTENER

版本                     
TNSLSNR for Linux: Version 11.2.0.3.0 – Production

启动日期                 
28-9月 -2015 14:32:08

例行运转时刻             
0 天 0 小时 33 分 18 秒

跟踪级别                 
off

安全性                   
ON: Local OS Authentication

SNMP                     
OFF

监听程序参数文件         
/u01/app/grid/11.2.0/network/admin/listener.ora

监听端点概要…

 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521)))

劳务摘要..

服务
“+ASM” 包含 1 个实例。

 
实例 “+ASM”, 状态 READY, 包含此服务之 1 只处理程序…

服务
“PLSExtProc” 包含 1 个实例。

 
实例 “PLSExtProc”, 状态 UNKNOWN, 包含此服务之 1
单处理程序…

服务
“ora11g” 包含 1 个实例。

 
实例 “ora11g”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradg11g” 包含 1 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务的 1 只处理程序…

服务
“oradg11g.lhr.com” 包含 2 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

 
实例 “oradg11g”, 状态 READY, 包含此服务之 1 独处理程序…

服务
“oradg11gXDB.lhr.com” 包含 2 个实例。

 
实例 “oradg11g”, 状态 READY, 包含此服务的 1 单处理程序…

 
实例 “oradgphy”, 状态 READY, 包含此服务的 1 个处理程序…

服务
“oradg11g_DGB.lhr.com” 包含 1 个实例。

 
实例 “oradg11g”, 状态 READY, 包含此服务之 1 只处理程序…

服务
“oradg11g_DGMGRL.lhr.com” 包含 1 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务之 1 单处理程序…

服务
“oradglg” 包含 1 个实例。

 
实例 “oradglg”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradgphy” 包含 1 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务的 1 单处理程序…

服务
“oradgphy.lhr.com” 包含 2 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

 
实例 “oradgphy”, 状态 READY, 包含此服务之 1 只处理程序…

服务
“oradgphy_DGB.lhr.com” 包含 1 个实例。

 
实例 “oradgphy”, 状态 READY, 包含此服务之 1 单处理程序…

服务
“oradgphy_DGMGRL.lhr.com” 包含 1 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradgss” 包含 1 个实例。

 
实例 “oradgss”, 状态 UNKNOWN, 包含此服务的 1 单处理程序…

服务
“orclasm.lhr.com” 包含 1 个实例。

 
实例 “orclasm”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

命令执行成功

[oracle@rhel6_lhr
admin]$ more
/u01/app/grid/11.2.0/network/admin/listener.ora

#
listener.ora Network Configuration File:
/u01/app/grid/11.2.0/network/admin/listener.ora

#
Generated by Oracle configuration tools.

 

DVD

(DESCRIPTION =

  
(ADDRESS_LIST =

    
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

   
)

  
(CONNECT_DATA =

    
(SERVICE_NAME = dave)

   
)

 
)

 

2.3
使用 service_name:dave

以2.2
节的dbUrl 改化如下:

String
dbUrl = “jdbc:oracle:thin:@//127.0.0.1:1521/dave”;

 

出口结果:

MGMT_VIEW–97

ANQING–94

DVD–93

SYSMAN–95

 

要当11g里赶上如下错误:

测试运行Java
类,报错:

java.sql.SQLException:
The Network Adapter could not establish the connection

 

可以品味换对应之
jdbc connection driver,官网的验证如下:

 

JDBC
Thin Driver 11g Causes”Java.Sql.Sqlexception: Io Exception: The Network
Adapter Could NotEstablish The Connection” While Connecting to Oracle
Database 11g [ID947653.1]

 

Change
the JDBC connection driver class inyour application server
from:

oracle.jdbc.driver.OracleDriver

to

oracle.jdbc.OracleDriver

 

 

2.4
使用TNS name: dvd

String
dbUrl = “jdbc:oracle:thin:@dvd”;

 

报错如下:

java.sql.SQLException:
Unknown host specified

 

欠问题是坐JVM
没有oracle.net.tns_admin的system property。 解决方法来2栽:

 

计同样:在开行VM
时添加如下参数:

-Doracle.net.tns_admin=D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
外汇 5

艺术二:在java
代码里长:

System.setProperty(“oracle.net.tns_admin”,”D:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\NETWORK\\ADMIN”);

 

加上以后,就可以正常在JDBC中使用tnsnama了。

 

 

 

Java代码  外汇 6

1. 2.2
测试1,使用SID:newccs 

2.   

3. [java]
view plaincopy 

4.  

5. import
java.sql.*;   

6.     

7. public class
jdbc {   

8.    
String dbUrl = “jdbc:oracle:thin:@127.0.0.1:1521:newccs”;   

9.    
String theUser = “dave”;   

10.    
String thePw = “dave”;   

11.    
Connection c = null;   

12.    
Statement conn;   

13.    
ResultSet rs = null;   

14.     

15.    
public
jdbc() {   

16.       
try
{   

17.           
Class.forName(“oracle.jdbc.driver.OracleDriver”).newInstance();   

18.           
c = DriverManager.getConnection(dbUrl, theUser, thePw);   

19.           
conn = c.createStatement();   

20.       
} catch
(Exception e) {   

21.           
e.printStackTrace();   

22.       
}   

23.    
}   

24.     

25.    
public boolean
executeUpdate(String sql) {   

26.       
try
{   

27.           
conn.executeUpdate(sql);   

28.           
return true;   

29.       
} catch
(SQLException e) {   

30.           
e.printStackTrace();   

31.           
return false;   

32.       
}   

33.    
}   

34.     

35.    
public
ResultSet executeQuery(String sql) {   

36.       
rs = null;   

37.       
try
{   

38.           
rs = conn.executeQuery(sql);   

39.       
} catch
(SQLException e) {   

40.           
e.printStackTrace();   

41.       
}   

42.       
return
rs;   

43.    
}   

44.     

45.    
public void
close() {   

46.       
try
{   

47.           
conn.close();   

48.           
c.close();   

49.       
} catch
(Exception e) {   

50.           
e.printStackTrace();   

51.       
}   

52.    
}   

53.     

54.    
public static void
main(String[] args) {   

55.       
ResultSet rs;   

56.       
jdbc conn = new
jdbc();   

57.       
rs = conn.executeQuery(“select
* from dave where rownum<5”);   

58.       
try
{   

59.           
while
(rs.next()) {   

60.              
System.out.println(rs.getString(“username”)+”–“+rs.getString(“user_id”));   

61.           
}   

62.       
} catch
(Exception e) {   

63.           
e.printStackTrace();   

64.       
}   

65.    
}   

66. }   

67.     

68. —输出正常:   

69. MGMT_VIEW–97   

70. ANQING–94   

71. DVD–93   

72. SYSMAN–95   

 

 

SID_LIST_LISTENER

 
(SID_LIST =

   
(SID_DESC =

     
(GLOBAL_DBNAME = orclasm.lhr.com)

     
(SID_NAME = orclasm)

     
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
)

   
(SID_DESC =

     
(SID_NAME = PLSExtProc)

     
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

     
(PROGRAM = extproc)

   
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradg11g)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradg11g)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradgphy)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradgphy)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradglg)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradglg)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradgss)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradgss)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = ora11g)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= ora11g)

 
)

   
(SID_DESC =

   
(GLOBAL_DBNAME = oradg11g.lhr.com)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradg11g)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradgphy.lhr.com)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradgphy)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradg11g_DGMGRL.lhr.com)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradg11g)

 
)

 
(SID_DESC =

   
(GLOBAL_DBNAME = oradgphy_DGMGRL.lhr.com)

   
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

   
(SID_NAME= oradgphy)

 
)

 
)

 

LOGGING_LISTENER
= OFF

 

一.2.2  实验环境介绍

 

 项目

主库

dg库

db 类型

单实例

单实例

db version

11.2.0.3

11.2.0.3

db 存储

FS type

FS type

ORACLE_SID

oradg11g

oradgphy

db_name

oradg11g

oradg11g

主机IP地址:

192.168.59.130

192.168.59.130

OS版本及kernel版本

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

OS hostname

rhel6_lhr

rhel6_lhr

 

 

 

一.2  前言部分

 

一.3.2.1  首先在主库上安排一个TAF的service

以此服务在数据库出现故障时会发送通知受客户端,允许查询语句以故障转移产生后连续运行。

每当主库端运行:

14:51:45
SQL> begin

14:52:10  
2    DBMS_SERVICE.CREATE_SERVICE(service_name        =>
‘dg_taf_lhr’,

14:52:10  
3                                network_name        =>
‘dg_taf_lhr’,

14:52:10  
4                                aq_ha_notifications =>
TRUE,

14:52:10  
5                                failover_method     =>
‘BASIC’,

14:52:10  
6                                failover_type       =>
‘SELECT’,

14:52:10  
7                                failover_retries    =>
30,

14:52:10  
8                                failover_delay      =>
5);

14:52:10  
9  end;

14:52:10 
10  /

 

PL/SQL
过程已经成完成。

 

一度为此时间: 
00: 00: 00.07

14:52:10
SQL>

 

 

 

tns_oradgphy

 
(DESCRIPTION =

   
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
(CONNECT_DATA =

     
(SERVER = DEDICATED)

     
(SERVICE_NAME = oradgphy)

   
)

 
)

一.5  About Me

 

…………………………………………………………………………………………………………………………………………………………………….

正文作者:小麦胚芽,只注意让数据库的技术,更看得起技术之使

ITPUB
BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1811944/

本文pdf版:http://yunpan.cn/cdEQedhCs2kFz
(提取码:ed9b)

QQ:642808185
若加QQ请注明你所正读的文章标题

著时地点:2015-09-28
09:00~
2015-09-29 23:00
于外汇交易中心完成

<版权所有,文章允许转载,但得为链接方式注明源地址,否则追究法律责任!>

…………………………………………………………………………………………………………………………………………………………………….

 

 

一.6  About Me

 

…………………………………………………………………………………………………………………………………………………………………….

本文作者:小麦胚芽,只在意于数据库的技巧,更重技术的用

ITPUB
BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1811839/

本文pdf版:http://yunpan.cn/cdEQedhCs2kFz

(提取码:ed9b)

QQ:642808185
若加QQ请注明你所正读之章标题

著时间地点:2015-09-28
09:00~
2015-09-29 23:00
于外汇交易中心完成

<版权所有,文章允许转载,但得为链接方式注明源地址,否则追究法律责任!>

…………………………………………………………………………………………………………………………………………………………………….

 

 

一.3  实验部分

 

一.3  相关知识点扫盲

 

  

oracle
Data
Guard,以压低资本实现高的数据保护。在硬件及没有特殊要求,普通PC机即可实现。

概括的吧,Data
Guard,就是机关创建及维护生产数据库(或主数据库)的一个或者多只业务一致的副本(备用数据库)。如果主数据库不可用(因为故障、维护或不幸),那么可激活一个备用数据库并要的承担主数据库的角色。然而,在安排好Data
Guard后,若需实现主备数据库里的切换,需要以主数据库及备用数据库及分别输入多单令,切换步骤稍显麻烦。所以,一般情形下,DBA会将满切换过程编辑成脚本,以便自动运行,进行状态切换。当然,oracle
也提供了工具Data Guard
Broker,仅于决定端输入一个指令就会方便实现主备数据库中的切换。

于Data
Guard Broker的基本功及,配置并启用Fast-Start
Failover,就会自动检测发现主机故障,实现主备切换,故障转移。

 

Data
Guard Broker是建立在Data Guard基础及的一个对Data
Guard配置,集中管理操作的一个平台。Broker的推出是为了简化DG复杂的军事管制过程,它最好酷之用意就是是集中化的联保管。

布局Data
Guard
Broker使用到的客户端工具是DGMGRL。它是一个命令行管理工具。

 

 

一.2  前言部分

 

tns_oradglg

 
(DESCRIPTION =

   
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
(CONNECT_DATA =

     
(SERVER = DEDICATED)

     
(SERVICE_NAME = oradglg)

   
)

 
)

 

 

一.3.2.3  创建1个触发器来保证服务可运行

创两独触发器,让数据库在启动暨角色转换时运行此存储过程。用于当数据库open时,不需再次开数据库,如果是主库则实施存储过程。当数据库切换后,如果是主库则实行存储过程。

 

主库执行:

14:59:47
SQL> create or replace TRIGGER dg_taf_trg_startup_lhr

14:59:53  
2    after startup or db_role_change on database

14:59:53  
3  begin

14:59:53  
4    dg_taf_proc_lhr;

14:59:53  
5  end;

14:59:53  
6  /

 

触发器已开立

 

曾为此时间: 
00: 00: 00.26

14:59:54
SQL>

 

一.2.3  相关参考文章链接

 

dg的多元文章参考:

【DATAGUARD】
基于与一个主机建立物理备库和逻辑备库(一): http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】
基于同一个主机建立物理备库和逻辑备库(二 ):  http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】
基于同一个主机建立物理备库和逻辑备库(三 ):  http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】
基于同一个主机建立物理备库和逻辑备库 (四)–添加一个物理dg节点
:http://blog.itpub.net/26736162/viewspace-1484878/

【DATAGUARD】物理dg的switchover切换(五)
:http://blog.itpub.net/26736162/viewspace-1753111/

【DATAGUARD】物理dg的failover切换(六):  
http://blog.itpub.net/26736162/viewspace-1753130/

【DATAGUARD】物理dg在主库丢失归档文件的景况下的还原(七) : http://blog.itpub.net/26736162/viewspace-1780863/

 

 

 

(connect_data

//(server
= dedicated)//该参数表示专用服务器模式 
(service_name=ORACMS)//要操作数据库的服务名 
(failover_mode
=//连接失败后甩卖的点子 
(type=session)//TYPE
=SESSION表示当一个连好的对话的实例发生故障,系统会活动将会晤讲话切换到外可用的实例,前台应用无须再发起连接,但对话正在履行的SQL
需要更履行。 
(method=basic)//表示开始连接就连一个接点 
(retries=5)//连接失败后重试连接的次数 
(delay=15)//连接失败后重试的延迟时间(以秒为单位)

)

)

)

 

 

 

 

 

一.2.2  实验环境介绍

 

 项目

主库

dg库

db 类型

单实例

单实例

db version

11.2.0.3

11.2.0.3

db 存储

FS type

FS type

ORACLE_SID

oradg11g

oradgphy

db_name

oradg11g

oradg11g

主机IP地址:

192.168.59.130

192.168.59.130

OS版本及kernel版本

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64

OS hostname

rhel6_lhr

rhel6_lhr

 

 

 

一.3.2.5  在备库查询,确认触发器和存过已经以及备库

 

15:21:21
SQL> select trigger_name, trigger_name

15:22:09  
2    from dba_triggers where trigger_name =
‘DG_TAF_TRG_STARTUP_LHR’;

 

TRIGGER_NAME                  
TRIGGER_NAME



DG_TAF_TRG_STARTUP_LHR        
DG_TAF_TRG_STARTUP_LHR

 

既因此时: 
00: 00: 00.09

15:22:09
SQL>

15:24:28
SQL> select d.owner,d.OBJECT_NAME

15:24:54  
2    from dba_procedures d

15:24:54  
3   where d.OBJECT_NAME = ‘DG_TAF_PROC_LHR’;

 

OWNER                         
OBJECT_NAME



SYS                           
DG_TAF_PROC_LHR

 

一度为此时间: 
00: 00: 00.05

15:24:54
SQL>

 

 

LISTENER

 
(DESCRIPTION_LIST =

   
(DESCRIPTION =

     
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT =
1521))

   
)

 
)

 

ADR_BASE_LISTENER
= /u01/app/grid

 

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER
= ON

 

#TRACE_LEVEL_LISTENER
= OFF

[oracle@rhel6_lhr
admin]$

 

主备库tnsnames的布局,注意红色的吧新增长的情节:

[oracle@rhel6_lhr
admin]$ more tnsnames.ora

#
tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

#
Generated by Oracle configuration tools.

 

一.2.3  相关参考文章链接

 

dg的多级文章参考:

【DATAGUARD】
基于与一个主机建立物理备库和逻辑备库(一): http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】
基于与一个主机建立物理备库和逻辑备库(二 ):  http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】
基于同一个主机建立物理备库和逻辑备库(三 ):  http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】
基于同一个主机建立物理备库和逻辑备库 (四)–添加一个物理dg节点
:http://blog.itpub.net/26736162/viewspace-1484878/

【DATAGUARD】物理dg的switchover切换(五)
:http://blog.itpub.net/26736162/viewspace-1753111/

【DATAGUARD】物理dg的failover切换(六):  
http://blog.itpub.net/26736162/viewspace-1753130/

【DATAGUARD】物理dg在主库丢失归档文件的情状下的死灰复燃(七) : http://blog.itpub.net/26736162/viewspace-1780863/

【DATAGUARD】物理dg配置客户端无缝切换
(八.1)–Data Guard Broker 的部署:http://blog.itpub.net/26736162/viewspace-1811839/

【DATAGUARD】物理dg配置客户端无缝切换
(八.2)–Fast-Start Failover 的布置:http://blog.itpub.net/26736162/viewspace-1811936/

【DATAGUARD】物理dg配置客户端无缝切换
(八.3)–客户端TAF 配置:http://blog.itpub.net/26736162/viewspace-1811944/

【DATAGUARD】物理dg配置客户端无缝切换
(八.4)–ora-16652 和 ora-16603错误
:http://blog.itpub.net/26736162/viewspace-1811947/

 

 

 

 

 

一.3.2.4  启动新创的service

 在主库上推行该存储过程(或者重新开数据库,在开行数据库时见面接触执行dg_taf_proc的触发器),在主库做日志的切换,将扭转采取到备库

 

 

主库执行:

14:59:54
SQL> exec
dg_taf_proc_lhr ;

 

PL/SQL
过程就成做到。

 

现已就此时间: 
00: 00: 00.01

15:06:57
SQL> alter system switch logfile;

 

系统已变更。

 

曾经为此时间: 
00: 00: 01.02

15:20:01
SQL>

[oracle@rhel6_lhr
~]$ lsnrctl status

 

LSNRCTL
for Linux: Version 11.2.0.3.0 – Production on 29-9月 -2015
15:07:32

 

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

 

正在连接到
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.130)(PORT=1521)))

LISTENER
的 STATUS


别名                     
LISTENER

版本                     
TNSLSNR for Linux: Version 11.2.0.3.0 – Production

开行日期                 
29-9月 -2015 12:14:49

健康运行时             
0 天 2 时 52 分 43 秒

跟级别                 
off

安全性                   
ON: Local OS Authentication

SNMP                     
OFF

监听程序参数文件         
/u01/app/grid/11.2.0/network/admin/listener.ora

监听端点概要…

 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.130)(PORT=1521)))

劳动摘要..

服务
“+ASM” 包含 1 个实例。

 
实例 “+ASM”, 状态 READY, 包含此服务之 1 个处理程序…

服务
“PLSExtProc” 包含 1 个实例。

 
实例 “PLSExtProc”, 状态 UNKNOWN, 包含此服务之 1
只处理程序…

服务
“dg_taf_lhr.lhr.com” 包含 1 个实例。

 
实例 “oradg11g”, 状态 READY, 包含此服务的 1 单处理程序…

服务
“ora11g” 包含 1 个实例。

 
实例 “ora11g”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradg11g” 包含 1 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务之 1 只处理程序…

服务
“oradg11g.lhr.com” 包含 2 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

 
实例 “oradg11g”, 状态 READY, 包含此服务的 1 独处理程序…

服务
“oradg11gXDB.lhr.com” 包含 2 个实例。

 
实例 “oradg11g”, 状态 READY, 包含此服务的 1 单处理程序…

 
实例 “oradgphy”, 状态 READY, 包含此服务的 1 个处理程序…

服务
“oradg11g_DGB.lhr.com” 包含 1 个实例。

 
实例 “oradg11g”, 状态 READY, 包含此服务之 1 只处理程序…

服务
“oradg11g_DGMGRL.lhr.com” 包含 1 个实例。

 
实例 “oradg11g”, 状态 UNKNOWN, 包含此服务之 1 单处理程序…

服务
“oradglg” 包含 1 个实例。

 
实例 “oradglg”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradgphy” 包含 1 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务的 1 单处理程序…

服务
“oradgphy.lhr.com” 包含 2 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

 
实例 “oradgphy”, 状态 READY, 包含此服务之 1 只处理程序…

服务
“oradgphy_DGB.lhr.com” 包含 1 个实例。

 
实例 “oradgphy”, 状态 READY, 包含此服务之 1 单处理程序…

服务
“oradgphy_DGMGRL.lhr.com” 包含 1 个实例。

 
实例 “oradgphy”, 状态 UNKNOWN, 包含此服务的 1 独处理程序…

服务
“oradgss” 包含 1 个实例。

 
实例 “oradgss”, 状态 UNKNOWN, 包含此服务的 1 单处理程序…

服务
“orclasm.lhr.com” 包含 1 个实例。

 
实例 “orclasm”, 状态 UNKNOWN, 包含此服务之 1 个处理程序…

一声令下执行成功

 

一.3.2  客户端TAF 配置

一.2.1  导读

诸君技术爱好者,看罢本文后,你可控制如下的技艺,也得如法炮制到片外你所不明白之知识,~O(∩_∩)O~:

① Data
Guard Broker 的配置


Fast-Start Failover 的配置


Oracle
DataGuard 之客户端TAF 配置


使用DGMGRL 来管理数据库

⑤ 物理dg管理及护卫的一部分sql


DataGuard 客户端特级配置

 

注意:本篇BLOG中代码有需要特别关注之地方我都为此黄色背景及红字体来代表,比如下面的事例中,thread
1的极度要命归档日志号为33,thread
2的卓绝可怜归档日志号为43是亟需特地关心的地方。

 
List of Archived Logs in backup set 11

 
Thrd Seq     Low SCN    Low Time            Next SCN   Next
Time

 



 
1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29
11:15:48

 
1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29
11:15:58

 
2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29
11:15:49

 
2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29
11:15:53

 

 

 

 

 

本文如发生误或非完善之地方要大家多指正,ITPUB留言或QQ皆可,您的批评指正是自个儿撰文之极致要命动力。

一.3.3  验证客户端的TAF

先期说说测试过程,首先我们以windows环境下补充加tnsnames,然后cmd中总是到dg环境,执行一个年代久远之询问(select * from (select * from
sys.dba_objects);),此时以dgmgrl中手动shutdown
abort掉主库,那么连的cmd中见面搁浅一会,等待fast-start
failover切换完成后,则继续回结果。

主备切换不影响用户之select操作,但是倘若是dml操作,则有着业务回滚:

 

 

D:\Users\xiaomaimiao>sqlplus
lhr/lhr@dg_taf

 

SQL*Plus:
Release 11.2.0.1.0 Production on Tue Sep 29 15:37:20 2015

 

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

 

 

Connected
to:

Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit
Production

With
the Partitioning, OLAP, Data Mining and Real Application Testing
options

 

SQL>
set line 9999

SQL>
col name format a10

SQL>
col FS_FAILOVER_OBSERVER_HOST format a20

SQL>
col DB_UNIQUE_NAME format a15

SQL>
select dbid,name,
DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status
from v$database;

 

     
DBID NAME       DB_UNIQUE_NAME  RESETLOGS_CHANGE# CURRENT_SCN
PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR
OPEN_MODE            SWITCHOVER_STATUS





1403587593
ORADG11G   oradg11g                 
2575356     2604578 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
PRIMARY 
       
YES READ WRITE           TO STANDBY

 

SQL>
SELECT d.DBID,

 
2         d.DB_UNIQUE_NAME,

 
3         d.FORCE_LOGGING,

 
4         d.FLASHBACK_ON,

 
5         DATAGUARD_BROKER,

 
6         d.FS_FAILOVER_STATUS,

 
7         d.FS_FAILOVER_CURRENT_TARGET,

 
8         d.FS_FAILOVER_THRESHOLD,

 
9         d.FS_FAILOVER_OBSERVER_PRESENT,

10         d.FS_FAILOVER_OBSERVER_HOST

11    FROM v$database d;

 

     
DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       DATAGUAR
FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET    
FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER





1403587593
oradg11g        YES YES                ENABLED  SYNCHRONIZED          
oradgphy                                  30 YES    
rhel6_lhr

 

SQL>

 

可看看连接到的凡主库。

 

外汇 7 

Cmd卡住了,等待observer切换完成后cmd界面继续查询:

15:44:42.75 
2015年9月29日 星期二

正值为数据库
“oradgphy” 启动快速启动故障转移…

当下施行故障转移,
请稍候…

故障转移成功,
新的主数据库为 “oradgphy”

15:44:49.93 
2015年9月29日 星期二

 

 

外汇 8 

SQL>
set line 9999

SQL>
col name format a10

SQL>
col FS_FAILOVER_OBSERVER_HOST format a20

SQL>
col DB_UNIQUE_NAME format a15

SQL>
select dbid,name,
DB_UNIQUE_NAME,RESETLOGS_CHANGE#,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status
from v$database;

 

     
DBID NAME       DB_UNIQUE_NAME  RESETLOGS_CHANGE# CURRENT_SCN
PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    FOR
OPEN_MODE            SWITCHOVER_STATUS





1403587593
ORADG11G   oradgphy  
              
2605058     2605468 MAXIMUM AVAILABILITY RESYNCHRONIZATION  

PRIMARY         
YES READ WRITE           NOT ALLOWED

 

SQL>
SELECT d.DBID,

 
2         d.DB_UNIQUE_NAME,

 
3         d.FORCE_LOGGING,

 
4         d.FLASHBACK_ON,

 
5         DATAGUARD_BROKER,

 
6         d.FS_FAILOVER_STATUS,

 
7         d.FS_FAILOVER_CURRENT_TARGET,

 
8         d.FS_FAILOVER_THRESHOLD,

 
9         d.FS_FAILOVER_OBSERVER_PRESENT,

10         d.FS_FAILOVER_OBSERVER_HOST

11    FROM v$database d;

 

     
DBID DB_UNIQUE_NAME  FOR FLASHBACK_ON       DATAGUAR
FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET    
FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER





1403587593
oradgphy        YES YES                ENABLED  REINSTATE REQUIRED    
oradg11g                                  30 YES    
rhel6_lhr

 

SQL>

 

 

 切换后,在备库上囤积过程启动了TAF的service
,客户端再连时,自动连续至了当前底主库(原备库)。

 

@至此,在DG环境下客户端的TAF配置中心形成。

一.3.2.6  客户端tnsnames 配置

dg_taf
=

   
(DESCRIPTION =

       
(ADDRESS
= (PROTOCOL
=
tcp)(HOST
= 192.168.59.130)(PORT
= 1521**))**

       
(ADDRESS
= (PROTOCOL
=
tcp)(HOST
= 192.168.59.130)(PORT
= 1521**))**

           
(LOAD_BALANCE
= yes)

               
(CONNECT_DATA
=

                   
(SERVER
=
DEDICATED)

                   
(SERVICE_NAME
=
dg_taf_lhr.lhr.com)

               
(FAILOVER_MODE
=

                   
(TYPE = session)

                   
(METHOD
=
basic)

                   
(RETRIES
= 180**)**

                   
(DELAY
= 5**)**

              
)

       
)

)

 

流淌:我的dg环境由于配备在一如既往台机械,所以host一样,生产环境下必将不相同。

 

一.3.1  实验目标

 

成功Oracle
DataGuard 之客户端TAF 配置,并测试无缝切换。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注