Failover-Connections-for-Data-Guard-Error-with-ORA-1033-[ID-461874.1]
Oracle数据库连接的failover配置
Failover的连接配置刘伟以下内容参考了官方文档。
这里的failover，是指应用发起一个数据库连接以后，如果工作过程中该连接所连到的实例发生了故障，连接可以自动切换到正常节点，从而最小化对业务的影响。
根据Oracle的介绍，我们有两种连接方式可以实现数据库连接的failover: TAF和FCF 1. TAFTAF的全称是Transparent Application Failover，即透明应用故障切换。
按照官方文档的描述，TAF让Oracle Net将一个失效的连接从故障点转移到另一个监听上，用户能使用这个新的连接来继续未完成的工作。
TAF可以配置为使用client端的(Transparent Network Substrate)TNS连接字符串来连接，或者使用server端的服务。
如果两种方式同时使用，则使用server端的服务配置。
TAF可以工作在两种模式下：session failover和select failover。
前者在failover时会重建失败的连接，后者则能够继续进程中未完成的查询(如果failover前一个session正在从一个游标中获取数据，则新的session将在相同的snapshot下重新运行select语句，并返回余下的行)。
如果failover时，session执行了DML操作且未提交，则failover后，若不执行rollback 回滚而执行新的操作，将会收到一条错误信息ORA-25402: transaction must roll back TAF在dataguard中使用，可以自动进行failover一个典型的使用了TAF的TNS连接串如下：NEWSDB =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = dyora)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))2. FCFFCF全称是Fast Connect Failover，快速连接故障切换。
Cisco-ASA-Failover防火墙冗余
F a i l o v e rFailover是思科防火墙一种高可用技术，能在防火墙发生故障时数秒内转移配置到另一台设备，使网络保持畅通，达到设备级冗余的目的。
原理前提需要两台设备型号一样（型号、内存、接口等），通过一条链路连接到对端（这个连接也叫心跳线）。
该技术用到的两台设备分为Active设备（Primary）和Stanby设备（Secondary）,这种冗余也可以叫AS模式。
活跃机器处于在线工作状态，备用处于待机状态实时监控活跃设备是否正常。
当主用设备发生故障后（接口down，设备断电），备用设备可及时替换，替换为Avtive的角色。
Failover启用后，Primary设备会同步配置文件文件到Secondary设备，这个时候也不能在Scondary添加配置，配置必须在Active进行。
远程管理Failover设备时，登录的始终是active设备这一点一定要注意，可以通过命令（show failover）查看当时所登录的物理机器。
目前启用failover技术不是所有状态化信息都可以同步，比如NAT转换需要再次建立。
配置Active设备：interface Ethernet0nameif outsidesecurity-level 0interface Ethernet1nameif insidesecurity-level 100ASA1(config)# failover lan unit primary //指定设备的角色主ASA1(config)# failover lan interface failover Ethernet2 //Failover接口名，可自定义ASA1(config)# failover link Fover Ethernet2//状态信息同步接口ASA1(config)# f //配置Failover IP地址,该网段可使用私网地址ASA1(config)# failover lan key xxxx //配置Failover 认证对端ASA1(config)# failover //启用Failover；注意，此命令一定要先在Active上输入，否则会引起配置拷错；将一个接口指定为failover 接口后，再show inter 的时候，该接口就显示为：interface Ethernet3description LAN Failover Interface //确保接口up配置standby设备：ASA2(config)# inte Ethernet3ASA2(configif)# no shutdownASA2(configif)# exitASA2(config)# failover lan unit secondaryASA2(config)# failover lan interface failover Ethernet03ASA1(config)# failover link Fover Ethernet2ASA2(config)#byASA2(config)# failover key xxxxASA2(config)# failover //先在Active设备运行此命令两台设备同步信息后，配置只能在Active进行，备份设备hostname会和主设备相同。
MySQL错误码及解决方案汇总
MySQL错误码及解决方案汇总引言：MySQL是一种常用的开源数据库管理系统，被广泛应用于各种Web应用程序和其他应用场景中。
然而，在使用MySQL时，我们可能会遇到各种错误和异常情况。
本文将总结常见的MySQL错误码，并为每个错误码提供相应的解决方案。
1. 错误码1 - Can't create/write to file这个错误通常表示MySQL服务器没有权限创建或写入指定的文件。
解决方案可以是确保MySQL对文件或目录具有适当的写入权限，或者更改需要读写的文件或目录的权限。
2. 错误码2 - No such file or directory如果你在MySQL执行命令时收到这个错误码，可能是因为指定的文件或目录不存在。
解决方案包括检查路径和文件名的拼写，确保指定的文件或目录存在。
3. 错误码1045 - Access denied for user这个错误通常表示MySQL拒绝了连接请求，因为提供的用户名或密码不正确。
解决方案可以是确保提供的用户名和密码是正确的，并且用户具有与MySQL服务器建立连接所需的适当权限。
4. 错误码1062 - Duplicate entry当插入或更新表中的数据时，如果遇到这个错误，意味着有重复的唯一键值。
解决方案可以是使用UPDATE语句而不是INSERT语句来更新数据，或者更改需要唯一的键值。
5. 错误码1146 - Table doesn't exist如果查询一个不存在的表时收到此错误码，表示你在操作一个不存在的表。
解决方案可以是确认该表是否存在，或者修正查询语句中的表名。
6. 错误码1215 - Cannot add foreign key constraint这个错误通常表示在创建外键约束时出现问题。
解决方案包括确保参照主键的表已存在，确保参照主键字段类型和长度与外键字段类型和长度一致，并且参照主键上不存在重复值。
7. 错误码1364 - Field 'xxx' doesn't have a default value当插入数据时遇到这个错误，表示插入的数据中有一个或多个字段没有指定默认值，并且该字段不允许为空。
利用MySQL工具进行故障切换第二部分：mysqlfailover
利用MySQL工具进行故障切换——第二部分：mysqlfailover 在本系列的前面的文章我们看到在MySQL 5.6中，当GTID启用时如何使用mysqlrpladmin 执行手动故障转移/切换。
现在我们将回顾MySQL实用程序中的另一个工具mysqlfailover(1.4.3版本)，它可以用于故障的自动转移。
Summary●如果MySQL 5.6的GTID复制启用的话，mysqlfailover可以执行故障自动转移。
●所有的从机都必须使用——主信息资料库列表。
（All slaves must use --master-info-repository=TABLE.）●监控节点是一个单故障点：别忘了监视它。
（The monitoring node is a single point of failure: don’t forget to monitor it! ）●错误事项检测效果很好，但是你必须使用保守的选项来确保故障转移将永远不会发生错误。
（Detection of errant transactions works well, but you have to use the -- pedanticoption to make sure failover will never happen if there is an errant transaction. ）●有一些局限性，比如不能只失败一次或过度的CPU利用率，但这对大多数设置影响不是很大。
（There are a few limitations such as the inability to only fail over once, or excessive CPU utilization, but they are probably not showstoppers for most setups. ）Setup我们将使用与上次相同的设置：一个主机和两个从机，并且都使用了GTID复制。
PostgreSQL高可用之Pgpool-II的故障转移和故障恢复参数详解
PostgreSQL⾼可⽤之Pgpool-II的故障转移和故障恢复参数详解Pgpool-II故障转移（failover）是指 Pgpool-II会将⽆法访问的PostgreSQL后端节点从集群中分离出去，如果故障的是主机节点，则会提升⼀个备机节点作为主机继续提供服务。
Pgpool-II通过使⽤健康检查来确认PostgreSQL后端节点的不可访问性：【健康检查】定期进⾏健康检查()，健康检查过程尝试从Pgpool-II连接到PostgreSQL节点以确认其健康状况。
如果连接失败，则可能是Pgpool-II 和PostgreSQL之间的⽹络连接出现问题或PostgreSQL⽆法正常⼯作。
Pgpool-II不会区分每种情况，只是确定如果运⾏状况检查失败，则认为特定的PostgreSQL节点不可⽤。
【故障恢复参数详解】failover_on_backend_error若failover_on_backend_error为off，则Pgpool-II仅报告错误，并在出现此类错误时断开会话（但是请注意，当Pgpool-II检测到PostgreSQL后端服务器的管理性关闭时，它仍会触发故障转移）。
如果客户端已经连接到Pgpool-II 并且PostgreSQL已关闭（请注意，如果根本没有客户端连接到Pgpool-II，关闭PostgreSQL不会触发故障转移）若failover_on_backend_error为on，则触发故障转移failover_commandfailover_command通常是⼀个故障转移脚本，官⽅提供的是failover.sh.sample，参数说明如下：failover_command（字符串）指定当PostgreSQL后端节点分离时要运⾏的⽤户命令。
Pgpool-II将以下特殊字符替换为后端特定信息。
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'follow_primary_commandfollow_primary_command命令⽤于通过调⽤pcp_recovery_node命令从新的主数据库恢复备⽤数据库。
redisson connections initialized for 卡住
redisson connections initialized
for 卡住
在初始化Redisson连接时，出现了卡住的情况。
具体原因可能各不相同，以下是一些排查和解决方法：
- 网络问题：Redis是通过TCP协议进行网络通信的，如果客户端和服务器在不同的机器上，需要检查网络是否连通，以及Redis服务器是否已经开启。
- 配置文件问题：Redis是根据配置文件启动的，如果配置文件出现问题，可能会导致Redis无法启动，进而引起连接不上的问题。
在这种情况下，可以检查配置文件是否正确，或者尝试重新配置Redis的配置文件。
- 内存问题：Redis是一个基于内存的存储系统，如果服务器没有足够的内存来存储所需的数据，可能会出现连接不上的问题。
在这种情况下，可以尝试扩大服务器内存，或者使用Redis集群的方式来解决问题。
如果以上方法无法解决问题，建议提供更多的信息，以便我能更好地帮助你解决问题。
MySQL中的连接超时与重连处理技巧
MySQL中的连接超时与重连处理技巧在使用MySQL进行数据库操作时，经常会遇到连接超时的情况。
连接超时意味着客户端与服务器之间的连接已经断开，导致无法继续与数据库进行交互。
为了解决连接超时问题，以及优化数据库的稳定性和可靠性，我们可以采取一些连接超时与重连的处理技巧。
一、连接超时的原因分析连接超时的原因有很多种，可能是网络不稳定、数据库服务器负载过高、SQL 查询耗时过长等。
下面列举一些常见的连接超时原因及解决方法：1. 网络不稳定网络不稳定是造成连接超时最常见的原因之一。
在网络不稳定的情况下，客户端与服务器之间的连接可能会断开，导致连接超时。
针对网络不稳定的问题，我们可以采取以下处理方法：- 修改MySQL配置参数：可以通过修改MySQL的超时设置来增加连接的稳定性，例如将参数wait_timeout和interactive_timeout调整为较大的值，以延长连接的空闲时间。
- 检查网络环境：可以通过ping命令或者网络监测工具来检查网络是否存在延迟或丢包的情况，如果网络存在问题，可以与网络管理员协调解决。
2. 数据库服务器负载过高当数据库服务器的负载过高时，可能会导致连接超时。
负载过高可能是由于并发连接数过多、SQL查询耗时过长等原因引起的。
针对数据库服务器负载过高的问题，我们可以采取以下处理方法：- 优化SQL查询：可以通过对数据库表添加索引、优化SQL语句等方式来减少查询耗时，从而降低数据库服务器的负载。
- 增加数据库服务器资源：可以通过增加数据库服务器的内存、CPU等硬件资源，以提高数据库服务器的处理能力。
3. SQL查询耗时过长在进行数据库查询时，如果查询语句的执行时间过长，可能会导致连接超时。
针对SQL查询耗时过长的问题，我们可以采取以下处理方法：- 优化SQL查询：可以通过对查询语句进行优化，使用索引、避免全表扫描等方式来提高查询效率。
- 拆分复杂查询：对于复杂的查询语句，可以将其拆分为多个简单的查询语句来分散查询压力，从而减少查询耗时。
mysqlrouter使用配置
mysqlrouter使⽤配置mysql router使⽤配置参考资料：⼀、架构图介绍：MySQL Router是处于应⽤client和dbserver之间的轻量级代理程序，它能检测，分析和转发查询到后端数据库实例，并把结果返回给client。
是mysql-proxy的⼀个替代品。
Router实现读写分离，程序不是直接连接数据库IP，⽽是固定连接到mysql router。
MySQL Router对前端应⽤是透明的。
应⽤程序把MySQL Router当作是普通的mysql实例，把查询发给MySQL Router,⽽MySQL Router会把查询结果返回给前端的应⽤程序。
从数据库服务器故障，业务可以正常运⾏。
由MySQL Router来进⾏⾃动下线不可⽤服务器。
程序配置不需要任何修改。
主数据库故障，由MySQL Router来决定主从⾃动切换，业务可以正常访问。
程序配置不需要做任何修改。
⼆、读写分离原理MySQL Router接受前端应⽤程序请求后，根据不同的端⼝来区分读写，把连接读写端⼝的所有查询发往主库，把连接只读端⼝的select查询以轮询⽅式发往多个从库，从⽽实现读写分离的⽬的。
读写返回的结果会交给MySQL Router,由MySQL Router返回给客户端的应⽤程序。
三、实验环境编号主机名IP⾓⾊1db_shenji10.10.81.134mysql router2dba_test_00110.10.50.60主库3dba_test_00210.10.117.231从库四、MySQL-Router安装配置1、主从搭建，此处省略2、MySQL-Router配置2.1、下载[root@db_shenji ~]# cd /data/[root@db_shenji data]# wget https:///get/Downloads/MySQL-Router/mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz[root@db_shenji data]# tar -Jxvf mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz[root@db_shenji data]# ln -s /data/mysql-router-8.0.18-linux-glibc2.12-x86_64 /usr/local/mysql-router2.2、配置[root@db_shenji data]# mkdir /etc/mysql-route/[root@db_shenji data]# cp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.conf修改后完整的内容[DEFAULT]# ⽇志存放⽬录logging_folder = /data/log/mysql-route# 插件存放⽬录plugin_folder = /usr/local/mysql-router/lib/mysqlrouter# 配置⽂件存放⽬录config_folder = /etc/mysql-route# 运⾏⽬录runtime_folder = /var/run[logger]# ⽇志运⾏级别level = debug# 主节点故障转移配置[routing:basic_failover]# 写节点地址bind_address=10.10.81.134# 写节点端⼝bind_port = 7001# 模式，读写mode = read-write# 主节点地址：默认情况下第⼀台主数据库为写主库，当第⼀台主数据库DOWN机后，第⼆台数据库被提升为主库destinations = 10.10.50.60:3306,10.10.117.231:3306# 从节点负载均衡配置[routing:balancing]# 绑定的IP地址bind_address=10.10.81.134# 监听的端⼝bind_port = 7002# 连接超时时间connect_timeout = 3# 最⼤连接数max_connections = 1024# 后端服务器地址destinations = 10.10.117.231:3306# 模式:读还是写mode = read-only[keepalive]interval = 60创建⽬录mkdir -p /data/log/mysql-routechown root:root /data/log/mysql-route/启动⽅式/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &查看⽇志[root@db_shenji ~]# tail -f /data/log/mysql-route/mysqlrouter.log2019-12-0614:05:40 main DEBUG [7f48cf782720] Starting all plugins.2019-12-0614:05:40 main DEBUG [7f48cc30e700] plugin 'keepalive:' starting2019-12-0614:05:40 main DEBUG [7f48cf782720] plugin 'logger:' doesn't implement start()2019-12-0614:05:40 keepalive INFO [7f48cc30e700] keepalive started with interval 602019-12-0614:05:40 keepalive INFO [7f48cc30e700] keepalive2019-12-0614:05:40 main DEBUG [7f48cb90d700] plugin 'routing:balancing' starting2019-12-0614:05:40 main DEBUG [7f48caf0c700] plugin 'routing:basic_failover' starting2019-12-0614:05:40 main DEBUG [7f48cf782720] Running.2019-12-0614:05:40 routing INFO [7f48cb90d700] [routing:balancing] started: listening on 10.10.81.134:7002, routing strategy = round-robin 2019-12-0614:05:40 routing INFO [7f48caf0c700] [routing:basic_failover] started: listening on 10.10.81.134:7001, routing strategy = first-available 登录验证[root@db_shenji ~]# mysql -h 10.10.81.134 -uw -p'w' -P7001Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 130622Server version: 5.6.41-84.1-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql> show slave status\G;Empty set (0.00 sec)ERROR:No query specifiedmysql>[root@db_shenji ~]# mysql -h 10.10.81.134 -uw -p'w' -P7002Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.6.41-84.1-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.10.50.60Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000021Read_Master_Log_Pos: 3512Relay_Log_File: relay-bin.000053Relay_Log_Pos: 354Relay_Master_Log_File: mysql-bin.000021Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 3512Relay_Log_Space: 521Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1574144089Master_UUID: 0501f340-0a94-11ea-ad2b-5254007dcbb3Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:10-5440876Executed_Gtid_Set: 0501f340-0a94-11ea-ad2b-5254007dcbb3:1-5440876,137347eb-0a94-11ea-ad2b-525400dd43f8:1-531071Auto_Position: 01 row in set (0.00 sec)ERROR:No query specifiedmysql>。
OracleRACfailover测试（连接时故障转移）
OracleRACfailover测试(连接时故障转移)Oracle RAC 集群最突出的表现就是高可用性，这些内容主要包括load balance以及failover，通过这些技术使得单点故障不影响客户端端应用程序对数据库的正常访问，以及通过创建service实现节点间负载均衡。
本文主要描述Oracle 10g rac环境下的Oracle failover测试。
下面是一些关于这方面的基础参考或相关链接：有关负监听配置，载均衡(load balance)以及Oracle service请参考ORACLE RAC 监听配置 (listener.ora tnsnames.ora)ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)Oracle RAC 客户端连接负载均衡(Load Balance)Oracle RAC 服务器端连接负载均衡(Load Balance)Oracle RAC 负载均衡测试(结合服务器端与客户端)Oracle RAC failover 测试(TAF方式)Oracle RAC failover 测试(Server TAF方式)1、Oracle failover的几种方式Oracle failover也叫故障转移，从Oracle 10g开始，分为3种方式：a. Client-Side Connect time Failover客户端连接failover模式，此方式较为简单，只要安装了rac集群，缺省情况下即被启用。
b. TAF透明故障转移，此方式同样基于客户端完成，需要配置客户端tnsnames.ora，连接故障发生时，无须重新连接c. Service-Side TAF服务器端透明故障转移，通过配置service来实现，客户端无须任何配置。
本文主要演示第一种情形，即客户端在发起连接请求时如何实现故障转移注意事项：不能在listener.ora 文件中设置GLOBAL_NAME该参数会禁用Connect-time Failover 和 Transparent Application Failover2、Client-Side Connect time Failover下面关于Client-Side Connect time Failover来自Oracle 的官方描述 ID 453293.1The connect-time failover enables clients to connect to another listener if the initial connection to the first listener fails. The number of listener protocol addresses determines how many listeners are tried. Withoutconnect-time failover, Oracle Net attempts a connection with only one listener. The default is on.Tnsnames Parameter: FAILOVER(failover=on) is default for ADDRESS_LISTs, DESCRIPTION_LISTs, and a set ofDESCRIPTIONs., therefore, you do not have to specify it explicitly.基于客户端的failover比较好理解。
mysql常见问题解决
mysql常见问题解决⽇常使⽤mysql数据库遇到的⼀些问题，做下记录，会持续更新。
⼀、MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决⽅法环境：linux，mysql5.6错误：Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'原因： 同⼀个ip在短时间内产⽣太多（超过mysql数据库max_connect_errors的最⼤值）中断的数据库连接⽽导致的阻塞；默认max_connect_errors是10解决⽅法：1、提⾼允许的max_connection_errors数量（治标不治本）： ①进⼊Mysql数据库查看max_connect_errors： show variables like '%max_connection_errors%'; ②修改max_connection_errors的数量为1000： set global max_connect_errors = 1000; ③查看是否修改成功：show variables like '%max_connection_errors%';2、使⽤mysqladmin flush-hosts 命令清理⼀下hosts⽂件（不知道mysqladmin在哪个⽬录下可以使⽤命令查找：whereis mysqladmin）； ①在查找到的⽬录下使⽤命令修改：/usr/bin/mysqladmin flush-hosts -h192.168.1.1 -P3306 -uroot -prootpwd; 注意： 其中端⼝号，⽤户名，密码都可以根据需要来添加和修改； 配置有master/slave主从数据库的要把主库和从库都修改⼀遍的； 第⼆步也可以在数据库中进⾏，命令如下：flush hosts;mysql错误⽇志：cat /var/log/mysqld.log15042214:10:01 [Warning] IP address '172.17.1.69' could not be resolved: Name or service not known15042214:10:01 [Warning] IP address '192.168.120.1' could not be resolved: Name or service not known15042214:10:02 [Warning] IP address '192.168.80.1' could not be resolved: Name or service not known错误⽇志有⼤量的连接错误，可能是这些连接错误堆积起来达到了100,如果程序连接中都是⽤的ip的话(就是连接mysql时⽤的是：mysql -h 123.123.123.123 ⽽不是 mysql -h hostname)，可以考虑skip-name-resolve设置为ON，不再进⾏反解析，加快数据库连接反应时间mysql> set global skip_name_resolve=ON;mysql> show variables like '%skip%';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| skip_external_locking | ON || skip_name_resolve | ON || skip_networking | OFF || skip_show_database | OFF || slave_skip_errors | OFF || sql_slave_skip_counter | 0 |+------------------------+-------+6 rows in set (0.00 sec)设置好变量值后，flush hosts;⽣效mysql>flush hosts;⼆、sql_mode=only_full_group_by问题，this is incompatible with sql_mode=only_full_group_by错误错误：在使⽤MySQL命令⾏进⾏分组时报错：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependenton columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by原因：看⼀下group by的语法：select 选取分组中的列+聚合函数 from 表名称 group by 分组的列从语法格式来看，是先有分组，再确定检索的列，检索的列只能在参加分组的列中选。
连接MySql超时断开报错问题
连接MySql超时断开报错问题使用Mysql数据库时，最让人头疼的一个问题就是不定时会出现连接报错异常Exception，类似的Exception如下（Hibernate为例）：.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfu lly to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/ or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J conne ction 'autoReconnect=true' to avoid this problem..hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch update.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state..hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/er ror:8.9.** BEGIN NESTED EXCEPTION **municationsException大多数人遇到这个问题都会很费解，我也是遇到这个问题，细细研究后才发现了本质原因。
MySQL连接超时问题排查和解决
MySQL连接超时问题排查和解决1. 引言MySQL是一种广泛使用的开源关系型数据库管理系统，它广泛应用于Web 应用程序的后端数据存储和查询。
然而，由于网络环境变化或数据库服务器负载增加，可能会出现MySQL连接超时的问题。
本文将介绍如何有效地排查和解决MySQL连接超时问题。
2. 确定连接超时原因首先，我们需要确定连接超时的具体原因。
连接超时通常有以下几个可能的原因：- 网络延迟：网络不稳定或连接质量差，导致连接超时。
- MySQL服务器负载：当MySQL服务器负载过高时，可能无法及时响应连接请求，导致连接超时。
- 防火墙或网络代理：防火墙或网络代理可能会干扰连接，导致连接超时。
- MySQL连接配置：MySQL连接参数的配置问题可能导致连接超时。
3. 网络延迟排查如果怀疑网络延迟是连接超时的原因，我们可以使用常用的网络测试工具，如ping和traceroute，来检测网络连接的质量和延迟。
如果网络连接不稳定，建议联系网络管理员来解决。
4. MySQL服务器负载排查如果MySQL服务器负载过高，导致连接超时，我们可以通过以下几种方法来排查问题：- 使用SHOW PROCESSLIST命令查看当前运行的MySQL连接和查询，以及等待状态的连接。
通过查看连接的状态和执行时间可以确定是否存在性能瓶颈。
- 使用EXPLAIN命令分析查询语句的执行计划，确定是否存在慢查询。
慢查询可能导致连接超时，通过优化查询语句可以提高数据库性能。
- 调整MySQL服务器的配置参数，如max_connections和thread_cache_size，以增加服务器的并发连接能力。
5. 防火墙或网络代理排查防火墙或网络代理可能会拦截和干扰MySQL连接，导致连接超时。
我们可以通过以下几种方法来排查问题：- 检查防火墙配置，确保MySQL服务端口是开放的，并且允许外部连接。
- 检查网络代理设置，确保MySQL连接的通信端口没有被代理拦截或重定向。
如何解决MySQL中的连接超时和负载问题
如何解决MySQL中的连接超时和负载问题在现代互联网应用程序中，数据库是非常关键的一环。
作为常用的数据库管理系统之一，MySQL广泛应用于各类系统中。
然而，随着用户和数据量的增长，MySQL面临着连接超时和负载问题。
本文将介绍如何解决MySQL中的连接超时和负载问题，并提供一些优化建议。
一、什么是连接超时和负载问题在MySQL中，连接超时和负载问题是指由于连接数过多或查询负载过重导致数据库响应变慢或连接失败的情况。
连接超时是指客户端在与MySQL数据库建立连接后，由于执行时间过长或查询结果过多，导致连接在给定的时间内未返回结果，从而被数据库系统主动关闭的情况。
负载问题是指数据库系统在处理大量查询请求时，由于硬件资源限制或配置不合理，导致系统响应变慢。
二、解决连接超时问题的方法1. 调整连接超时参数默认情况下，MySQL的连接超时时间是8小时（28800秒）。
可以通过修改f配置文件中的wait_timeout参数来调整连接超时时间。
将该参数调整为适当的值，可以避免因长时间查询导致的连接超时问题。
2. 优化查询连接超时问题通常是由于查询语句执行时间过长导致的。
优化查询可以通过以下方法解决：- 索引优化：为频繁查询的列添加索引，可以加快查询速度。
- 查询重构：合理设计查询语句，避免全表扫描和大数据量排序等操作，采用覆盖索引代替全表扫描，可以有效提高查询性能。
- 数据分片：将大表分拆为多个小表，将查询请求均匀分布到各个分片上，从而提高查询效率。
三、解决负载问题的方法1. 垂直拆分数据库当单一数据库无法承受高负载时，可以考虑将数据库按照功能或业务拆分为多个独立的数据库，从而减轻单一数据库的负载压力。
例如，将订单、用户等相关数据分散到不同的数据库实例中，可以提高整体的并发处理能力。
2. 水平拆分数据表当某个数据表的数据量过大，无法满足并发查询需求时，可以考虑将该表按照某个字段（如用户ID、时间戳等）进行拆分。
dbforge studio 2020 for mysql backup error -回复
dbforge studio 2020 for mysql backup error -回复DBForge Studio 2020 is a powerful tool that offers a wide range of features for managing and administrating MySQL databases. One of its key functions is the ability to create backups of databases, allowing users to safeguard their data and restore it in case of any issues. However, like any complex software, DBForge Studio 2020 may encounter errors while performing backups. In this article, we will explore the common backup errors encountered in DBForge Studio 2020 for MySQL and provide step-by-step troubleshooting solutions to overcome them.1. Insufficient Privileges:One possible cause for backup errors is insufficient privileges. Ensure that the account you are using to connect to the MySQL server has the necessary permissions to create backups. Verify that the account has the "SELECT" privilege for all the tables in the database you are trying to back up. To do this, follow the steps below:a. Log in to the MySQL server as a privileged user.b. Run the following SQL command to grant the required privileges for the account:GRANT SELECT ON database_name.* TO 'username''localhost';Replace "database_name" with the name of your database and "username" with the account you are using in DBForge Studio.c. After granting the required privileges, try creating the backup again.2. Insufficient Disk Space:Another common cause for backup errors is insufficient disk space on the storage location where backups are being saved. To resolve this issue, check the available disk space on the destination location and ensure it has enough capacity to store the backup file.a. Open Windows File Explorer and locate the destination folder where backups are being saved.b. Right-click on the folder and select "Properties."c. In the "General" tab, check the available disk space. If the available space is low, consider freeing up space or choosing a different destination location with sufficient space.d. Once you have ensured sufficient disk space, attempt the backup operation again.3. Connection Issues:DBForge Studio connects to the MySQL server to perform backup operations. Any connection issues between DBForge Studio and the MySQL server can cause backup errors. To troubleshoot connection issues, follow these steps:a. Ensure that the MySQL server is running and accessible from the machine where DBForge Studio is installed.b. Check the connection settings in DBForge Studio. Open the "Database" menu and select "Manage Connections." Verify that the connection details, such as the server name, port, username, and password, are correctly configured.c. Test the connection by clicking the "Test Connection" button. If the test fails, double-check the connection details and ensure that the MySQL server is accessible from the machine.d. If the connection is successful, attempt the backup operation again.4. Database Corruption:In some cases, backup errors may occur due to database corruption. If the database contains corrupt structures, the backup process may encounter errors. To overcome this issue, you can try repairing the database using the DBForge Studio built-in repair functionalities. Follow these steps:a. In DBForge Studio, right-click on the database you want to repair in the Object Explorer pane.b. Select "Check Database" from the context menu. DBForge Studio will scan the database for any errors or corruption.c. If any issues are detected, right-click on the database again and select "Repair Database." DBForge Studio will attempt to repair the database structures.d. After the repair operation completes, try creating the backup again.5. Software Compatibility:Incompatibility between DBForge Studio and the MySQL server version can also lead to backup errors. Ensure that you are using a compatible version of DBForge Studio with the MySQL server. Check the compatibility matrix provided by the software vendor to verify whether your DBForge Studio version is compatible with your MySQL server version.If the backup errors persist even after following these troubleshooting steps, it is advisable to reach out to the software vendor's support team for further assistance. They will be able to provide specific guidance tailored to your situation.In conclusion, backup errors encountered in DBForge Studio 2020for MySQL can be resolved through careful troubleshooting. By addressing issues related to privileges, disk space, connections, database corruption, and software compatibility, users can ensure a smooth backup process and safeguard their data effectively.。
sqlrecoverrableexception connect reset -回复
sqlrecoverrableexception connect reset -回复SQLRecoverableException Connect Reset1500-2000 words articleIntroduction:SQLRecoverableException Connect Reset is an error commonly encountered by developers when working with databases in Java applications. This error message indicates that the connection between the application and the database server has been lost or forcibly closed. In this article, we will explore the causes of this exception and discuss possible solutions to resolve it step by step.Section 1: Understanding SQLRecoverableException1.1. What is SQLRecoverableException? SQLRecoverableException is a subclass of SQLException in Java, which represents a database access error or other errors during SQL operations. This exception is specifically used when a previously established connection to the database server is no longer valid or the connection has been reset unexpectedly.1.2. Why does SQLRecoverableException occur? SQLRecoverableException occurs due to various reasons, includingnetwork failures, database server issues, or configuration problems. It can happen when there is a sudden drop in network connectivity, when the database server is restarted or undergoes maintenance, or when the connection pool settings are incorrect.Section 2: Causes of SQLRecoverableException2.1. Network failureOne common cause of SQLRecoverableException is a network failure. This could happen due to fluctuating network connectivity, firewall settings, or improper configuration of the network infrastructure. When the application loses connection with the database server, a reset command is sent to close the connection, leading to the SQLRecoverableException.2.2. Database server issuesSQLRecoverableException can also occur when there are problems on the database server side. The server might undergo maintenance, restart, or experience an error that forces it to reset the connections. In such cases, the client application receives a reset command, resulting in the SQLRecoverableException.2.3. Connection pool configurationImproper connection pool configuration can contribute to SQLRecoverableException. If the connection pool settings have a low timeout value or a high maximum connection limit, it can lead to frequent connection resets. This can occur when the application is unable to acquire an available connection from the pool within the specified timeout, resulting in the exception.Section 3: Resolving SQLRecoverableException3.1. Check network connectivityThe first step in resolving SQLRecoverableException is to verify the network connectivity between the application server and the database server. Check for any network interruptions or firewall settings that may be blocking the connection. Ensure that the network infrastructure is properly configured and stable.3.2. Verify database server statusNext, check the status of the database server. Ensure that it is running and accessible. If the server has undergone maintenance or restart, wait for it to become available again. If there are any errors on the server side, resolve them accordingly before attempting to reconnect.3.3. Adjust connection pool settingsIf the SQLRecoverableException persists, examine the connection pool settings. Increase the timeout value to allow the application more time to acquire a connection before it times out. Additionally, ensure that the maximum connection limit is set appropriately based on the application's requirements and the database server's capabilities.3.4. Implement connection recovery mechanismsTo handle unexpected connection resets, it is recommended to implement connection recovery mechanisms in the application. This can include implementing connection retry logic, where the application attempts to reconnect to the database server after a connection reset. Additionally, connection pooling libraries often provide built-in mechanisms for handling connection resets, such as gracefully recovering or reestablishing connections.Section 4: Best Practices for Handling SQLRecoverableException 4.1. Use connection poolingConnection pooling is a best practice to efficiently manage database connections in Java applications. It helps in mitigating SQLRecoverableException by reusing existing connections,avoiding the overhead of establishing new connections for each database operation.4.2. Logging and monitoringImplement comprehensive logging and monitoring in the application to detect SQLRecoverableException occurrences. Logging can provide valuable insights into the root cause of the exception, helping in diagnosing and resolving the issue. Monitoring can alert the development team about sudden spikes in connection resets, enabling them to take proactive measures.4.3. Graceful error handlingImplement robust error handling mechanisms in the application to handle SQLRecoverableException gracefully. This can involve providing informative error messages to the user, retrying failed operations, or redirecting to an error page. Graceful error handling enhances the user experience and assists in faster issue resolution.Conclusion:SQLRecoverableException Connect Reset is an error encountered by developers when working with databases in Java applications. Itoccurs when the connection between the application and the database server is lost or forcibly closed. This article provided an understanding of SQLRecoverableException, its causes, and steps to resolve the issue. By following best practices, such as using connection pooling, monitoring, and implementing graceful error handling, developers can effectively mitigate SQLRecoverableException and ensure robust database connectivity in their applications.。
关于Abortedconnection告警日志的分析
关于Abortedconnection告警日志的分析前言：有时候，连接MySQL的会话经常会异常退出，错误日志里会看到"Got an error reading communication packets"类型的告警。
本篇文章我们一起来讨论下该错误可能的原因以及如何来规避。
1.状态变量Aborted_clients和Aborted_connects首先我们来了解下Aborted_clients和Aborted_connects这两个状态变量的含义，当出现会话异常退出时，这两个状态值会有变化。
根据官方文档描述，总结如下：造成Aborted_connects状态变量增加的可能原因：1.客户端试图访问数据库，但没有数据库的权限。
2.客户端使用了错误的密码。
3.连接包不包含正确的信息。
4.获取一个连接包需要的时间超过connect_timeout秒。
造成Aborted_clients状态变量增加的可能原因：1.程序退出前，客户机程序没有调用mysql_close()。
2.客户端睡眠时间超过了wait_timeout或interactive_timeout 参数的秒数。
3.客户端程序在数据传输过程中突然终止。
简单来说即：数据库会话未能正常连接到数据库，会造成Aborted_connects变量增加。
数据库会话已正常连接到数据库但未能正常退出，会造成Aborted_clients变量增加。
2.Got an error reading communication packets原因分析哪种情况会导致error log中出现“Aborted connection xxxx to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)”类似告警呢？下面我们根据上面可能的原因来做下具体测试。
mysql重连，连接丢失：Thelastpacketsuccessfullyreceive。。。
mysql重连，连接丢失：Thelastpacketsuccessfullyreceive。
1.1 错误信息：Caused by: municationsException: The last packet successfully received from the server was 20,820,001 milliseconds ago. The last packet sent successfully to the server was 20,820,002 milliseconds ago at sun.reflect.GeneratedConstructorAccessor29.newInstance(Unknown Source) ~[na:na]at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_51]at ng.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_51]at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3988) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2598) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828) ~[mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5372) ~[mysql-connector-java-5.1.29.jar:na]at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881) ~[c3p0-0.9.1.1.jar:0.9.1.1]at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit(AttributeRestoringConnectionInvocationHandler.java:98) ~[quartz-2.2.1.jar:na]1.2 解决⽅法- 如果使⽤的是JDBC，在JDBC URL上添加autoReconnect=true，如：jdbc:mysql://10.10.10.10:3306/mydb?autoReconnect=true- 如果是在Spring中使⽤DBCP连接池，在定义datasource增加属性validationQuery和testOnBorrow，如：<bean id="vrsRankDataSource" class="mons.dbcp.BasicDataSource" destroy-method="close"><property name="driverClassName" value="${jdbc.driverClassName}" /><property name="url" value="${countNew.jdbc.url}" /><property name="username" value="${er}" /><property name="password" value="${countNew.jdbc.pwd}" /><property name="validationQuery" value="SELECT 1" /><property name="testOnBorrow" value="true"/></bean>- 如果是在Spring中使⽤c3p0连接池，则在定义datasource的时候，添加属性testConnectionOnCheckin和testConnectionOnCheckout，如：<bean name="cacheCloudDB" class="boPooledDataSource"><property name="driverClass" value="${jdbc.driver}"/><property name="jdbcUrl" value="${cache.url}"/><property name="user" value="${er}"/><property name="password" value="${cache.password}"/><property name="initialPoolSize" value="10"/><property name="maxPoolSize" value="${cache.maxPoolSize}"/><property name="testConnectionOnCheckin" value="false"/><property name="testConnectionOnCheckout" value="true"/><property name="preferredTestQuery" value="SELECT 1"/></bean>参考附录分析When a c3p0-proxied Connection throws an SQLException, c3p0 examinesthe Exception and the Connection to make a judgement about whetherthe problem implies that the Connection should no longer be includedin the pool. c3p0 tests the Connection, and if the test fails, theConnection will be excluded from the pool.What c3p0 is telling you here is that a Connection that previouslysignalled an error and then failed a Connection test is still in use,and has signalled another error. From c3p0's perspective, this is anon-issue, it just means c3p0 doesn't have to do any kind of checksor notifications, the Connection is already gone as far as the poolis concerned. But c3p0 wonders why you'd still be using such aConnection, and warns you about it.Usually, if a client continues to use a Connection that c3p0 hascorrectly identified as broken, all further uses will provoke such anexception, and the fix is to close the Connection and start over whenan application's Connection turns out to be dead. But, by the erroryou're getting, it looks like your Connection is still live and okay-- it's clearly communicating with the database. So, the issue is,why did c3p0 deem the Connection dead if it is not?If you turn on DEBUG level logging (relevant loggers would becom.mchange.v2.c3p0.impl.NewPooledConnection,com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool, andcom.mchange.v2.c3p0.impl.DefaultConnectionTester, unless you'vedefined your own ConnectionTester), you can trace the testing andinvalidation of Connections, and try to understand why Connectionsthat seem okay are testing as broken. That will give you betterinformation about what's going on.That said, the only cost of this behavior is disconcerting warningmessages and somewhat faster churn of Connections through the pool.c3p0 is erring on the side of caution -- it has reason to believe aConnection is bad, so it's been excluded from the pool. It'd be niceto know why apparently good Connections are failing Connection tests,but if it is an infrequent occurrence, it's very little to worryabout. (If it's happening a lot, you should track it down.)原⽂地址：/p/c3p0/mailman/message/18310863/。
MySQL出现toomanyconnections（1040）错误解决方法
MySQL出现 toomanyconnections（ 1040）错误解决方法
其实MySQL默认的最大连接数为100，可能在大访问量的时候造成了连接不上数据库。解决的办法： 1、如果你的MySQL数据库使用f文件配置 找到文件 /etc/f 编辑器打开，修改max_connections的值为10000。实际MySQL服务器允许的最大连接数16384。 set-variable = max_connections=10000 2、如果你的MySQL数据库使用my.ini文件配置 找到文件/bin/my.ini（不一定在这里，根据你的数据库安装路径情况而定） 在 mysqld 里面增加 max_connections=10000 如果原来就有，那么可以修改max_connections的值。 3、重启mysql数据库。 这样就可以解决MySQL出现too many connections（1040）
Navicat远程连接mysql时，报错误“CantconnecttoMySQLserve。。。
Navicat远程连接mysql时，报错
误“CantconnecttoMySQLserve。
问题：
安装完mysql，添加完远程⽤户并赋予权限后，使⽤Navicat⼯具远程连接时报错误提⽰
解决⽅法：
1、检查端⼝53309是否填写错误，要与f中的端⼝保持⼀致----发现保持⼀致
2、检查登录⽤户（'root'@'%'）的密码是否填写错误-----发现填写正确
3、检查mysql服务是否已经启动-----发现已经启动
4、检查防⽕墙是否开放了端⼝53309：# firewall-cmd --list-ports-----发现未开放，找到连接失败原因：
添加防⽕墙开放端⼝：# firewall-cmd --zone=public --add-port=53309/tcp --permanent
重启防⽕墙⽣效：# firewall-cmd --reload
再次查看是否开放了端⼝53309：# firewall-cmd --list-ports-----开放成功
Navicat重新连接测试------测试成功，问题解决
---------结束------------。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Moreinformation on Dataguard setup can be found in following articles:Note 316740.1How to configure to let TAF work after Data Guard Switchover or Failover
CauseError ORA-1033 is expected for connections when they attempt to connect to standby instance, because it is mounted and not open.
When LOAD_BALANCE is set,ween the addresses in the net service name.
Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]
分类: 默认分类
日期: 2010-12-17 12:26
This method ensures the service failover is only available on the primary node.The net service name DGtest works through the addresses in the address_list section, in order. If NodeA is the standby, then Node B will have the service failover and the connection will attempt to connect.
Modified 28-SEP-2010Type PROBLEMStatus PUBLISHED
In this DocumentSymptomsCauseSolution
Applies to: Oracle Net Services - Version: 9.2.0.1.0 to 10.2.0.1.0 - Release: 9.2 to 10.2Information in this document applies to any platform.
DGtest= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=NodeA)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=NodeB)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=failover) (SERVER=DEDICATED) (FAILOVER_MODE= (TYPE=session) (METHOD=BASIC) (RETRIES=180) (DELAY=5) ) ) ) )
2. Create and startservice
SQL>execDBMS_SERVICE.CREATE_SERVICE(service_name=>'failover',network_name=>'failover',aq_ha_notifications=>true,failover_method=>'BASIC',failover_type=>'SELECT',failover_retries=>180,failover_delay=>1); PL/SQLproceduresuccessfullycompleted.SQL>exec dbms_service.start_service('failover');PL/SQL procedure successfully completed.
Service"failover"has1instance(s). Instance"tom1",statusREADY,has1handler(s)forthisservice... Handler(s): "DEDICATED"established:0refused:0state:ready LOCALSERVER
When there are two description sections in a net service name, Oracle Net will load balancebetweenthem.
SolutionExample is for service called failover 1. Setup the net service name, ensuring LOAD_BALANCE is not used and one has one description section.
SymptomsConnections indata guard environment fail withORA-01033: ORACLE initialization or shutdown in progress.
Oracle Net client trace shows after successful connection handshake
4. Ensure the change is made on the standby with command.
SQL>Alter system archive log current ;
5. Listener service command lsnrctl services on primary will then show
[27-SEP-200711:34:18:104]nsprecv:0000394F52412D30|..9ORA-0| [27-SEP-200711:34:18:104]nsprecv:313033333A204F52|1033:.OR| [27-SEP-200711:34:18:104]nsprecv:41434C4520696E69|ACLE.ini| [27-SEP-200711:34:18:104]nsprecv:7469616C697A6174|tializat| [27-SEP-200711:34:18:104]nsprecv:696F6E206F722073|ion.or.s| [27-SEP-200711:34:18:104]nsprecv:687574646F776E20|hutdown.| [27-SEP-200711:34:18:104]nsprecv:696E2070726F6772|in.progr| [27-SEP-200711:34:18:104]nsprecv:6573730A|ess.|
相关视频：/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
/technology/deploy/availability/htdocs/maa.htm/technology/deploy/availability/pdf/MAA_WP_10gR2_ClientFailoverBestPractices.pdf
Or has two description sections
(DESCRIPTION_LIST= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(Host=NodeA)(Port=1521)) (CONNECT_DATA= (SERVICE_NAME=ORCL1) ) ) (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(Host=NodeB)(Port=1521)) (CONNECT_DATA= (SERVICE_NAME=ORCL2)
原文地址: /tianlesoftware/article/details/6081898 分类： Oracle Troubleshooting 2010-12-17 12:26 490人阅读 评论(0) 收藏 举报 Failover Connections for Data Guard Error with ORA-1033 [ID 461874.1]
------------------------------------------------------------------------------
Blog： /tianlesoftware
网上资源：
3.Create a database trigger to ensure the service name is always the same, no matter which node's instance is the primary.
sqlplus / as sysdbaSQL>CREATEORREPLACETRIGGERmanage_OCIservice afterstartupondatabase DECLARE roleVARCHAR(30); BEGIN SELECTDATABASE_ROLEINTOroleFROMV$DATABASE; IFrole='PRIMARY'THEN DBMS_SERVICE.START_SERVICE('failover'); ELSE DBMS_SERVICE.STOP_SERVICE('failover'); ENDIF; END; PL/SQLproceduresuccessfullycompleted.