azure 云上 oracle11.2.0.4里dataguard归档日志传输 1034 问题详-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

azure 云上 oracle11.2.0.4里dataguard归档日志传输 1034 问题详

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:50 PM

azureOn the cloud

1 ， dataguard 搭建好后，归档日志传输不过去去查看master库上面的日志 tail –f /data/oracle/diag/rdbms/test_m1/powerdes/trace/alert_powerdes.log，显示信息如下： Sun May 08 00:34:17 2016 Error 1034 received logging on to the standby PING[ARC

1，dataguard搭建好后，归档日志传输不过去

去查看master库上面的日志

tail –f /data/oracle/diag/rdbms/test_m1/powerdes/trace/alert_powerdes.log，显示信息如下：

Sun May 08 00:34:17 2016

Error 1034 received logging on to the standby

PING[ARC2]: Heartbeat failed to connect to standby 'test_m2'. Error is1034.

2，tnsping earch_m2是通的

[oracle@azure_test_dbm1_3_111 admin]$ tnsping test_m3

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 08-MAY-2016 09:13:42

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.112)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test_m2)))

OK (0 msec)

[oracle@azure_test_dbm1_3_111 admin]$

3，去standby备库上，Check检查下service_name，看到service_names确实是test_m2

看起来service_name也没用错，如下所示：

[oracle@azure_test_dbm1_3_112 admin]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:15:27 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

SQL> show parameter name;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cell_offloadgroup_name string

db_file_name_convert string /oracle/app/oracle/oradata/pow

erdes, /oracle/app/oracle/orad

ata/powerdes

db_name string powerdes

db_unique_name string test_m2

global_names boolean FALSE

instance_name string powerdes

lock_name_space string

log_file_name_convert string /data/oracle/oradata/powerdes,

/data/oracle/oradata/pwerdes

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

processor_group_name string

service_names string test_m2

SQL>

文章来源blog地址：http://blog.csdn.net/mchdba/article/details/51344246，谢绝转载

4，在master库、standby库，通过sqlplus登录报错

# 主库登录sqlplus报错

[oracle@azure_test_dbm1_3_111 admin]$ sqlplus sys/testsys@test_m2 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:19:48 2016

ERROR:

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

descriptor

Enter user-name:

# 备库sqlplus登录报错

[oracle@azure_test_dbm1_3_112 admin]$ sqlplus sys/testsys@test_m2 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:18:39 2016

ERROR:

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

descriptor

Enter user-name:

都报错：ORA-12514:TNS:listener does not currently know of service requested in connect descriptor，这个问题一般就涉及到的是tnsnames.ora里面的service_name没有写对了。

5，替换service_name，问题解决

回忆这个备库的搭建过程，我刚建库dbca的时候，设置的service_name是powerdes，我在配置dataguard的时候，修改了参数文件initpowerdes.ora，有在里面新设置*.db_unique_name=test_m2，再次create spfile frompfile;然后以新的参数文件启动数据库后，看到service_names变成了test_m2了，而我就在tnsnames.ora里面设置了新的service_names名字test_m2，这个新的test_m2没有生效结果报错ORA-12514了。

因此，我要将没有生效的test_m2换成原来的powerdes，需要换的地方有2个，一个是listener.ora，一个是tnsnames.ora，主库master库和备库standby库都要修改如下所示：

#主库master库修改1个文件tnsnames.ora

# tnsnames.ora文件

[oracle@azure_test_dbm1_3_111 admin]$ vim tnsnames.ora

test_m2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = powerdes)

)

#备库standby库需要修改2个文件listener.ora、tnsnames.ora

# listener.ora文件

[oracle@azure_test_dbm1_3_112 admin]$ vim listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

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

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = powerdes)

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

)

ADR_BASE_LISTENER = /oracle/app/oracle

# tnsnames.ora文件

[oracle@azure_test_dbm1_3_112 admin]$ vim tnsnames.ora

test_m2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = powerdes)

)

修改完配置后，然后重启lsnrctl监听服务，再在备库使用sqlplus登录test_m2，登录成功：

[oracle@azure_test_dbm1_3_112 admin]$ sqlplus sys/testsys@test_m2 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 8 09:34:51 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

SQL>

再去看主库master库的后台alert日志，就会发现已经有开始传输归档日志的记录了：

……

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

LGWR: Setting 'active' archival fordestination LOG_ARCHIVE_DEST_2

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

LGWR: Standby redo logfile selected toarchive thread 1 sequence 26

LGWR: Standby redo logfile selected forthread 1 sequence 26 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 26 (LGWRswitch)

Current log# 2 seq# 26 mem# 0:/oracle/app/oracle/oradata/powerdes/redo02.log

Sun May 08 01:12:22 2016

Archived Log entry 22 added for thread 1sequence 25 ID 0xcf7feffa dest 1:

ARC0: Standby redo logfile selected forthread 1 sequence 25 for destination LOG_ARCHIVE_DEST_2

Destination LOG_ARCHIVE_DEST_2 isSYNCHRONIZED

……

PS：这里问题比较奇怪，我上次这里service_name需要修改成新的参数文件里面的test_m2才能归档日志传输到备库standby上面，但是这次确需要保持原来的service_name。看来这里面还有别的奥妙所在。需要去探索清楚了。

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles