简单的说，如果你的数据库运行在11g版本上，如果用数据泵导出的话，导出次数超过65535次之后，你的数据库就会出现这个问题（数据库逻辑导出期间，出现ORA-12514，结束之后问题消失）。这个是bug，可以通过重启实例暂时解决、可以应用补丁、升级数据库到12c。
症状，适用于oracle数据库11gThere are cases when existing service names are removed from the instance and the listener during the execution of a DataPump job in RAC configuration.
Under such a situation, the following error may occur when a new connection with service name is initialized:
ORA-12514 "TNS:listener does not currently know of service requested in connect descriptor"
The error is hit under the following circumstances:
- RAC environment (instance has been running for a long time)
- ORA-12514 error occurs for a new connection
- DataPump job is running while the ORA-12514 error was raised
- ORA-12514 is no longer seen after the completion of DataPump job.
起因
This is due to Bug 14320415 - VALUE OF SERVICE NAME IS SOMETIMES REMOVED DURING EXPDP, which is caused by an internal limit in the number of service names that is kept in an Oracle instance.
Bug 14320415 has been superseded by unpublished Bug 22036236 - Instance terminated by PMON due to ORA-600[KGHALO4] during datapump export / import if fix 14320415 present in 11.2.
The problem only surfaces in RAC configuration, because each DataPump session generates a new service name identified by timestamp for its own queue operation, as one can be seen from alert.log:
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20120711000049.MYORA.WORLD','hr','market' SCOPE=MEMORY SID='orcl1';
...
ALTER SYSTEM SET service_names='hr','market' SCOPE=MEMORY SID='orcl1'
After 65536 different groups of service name are registered, and we're trying to set a new group of service name, then the first statement will silently fail,
but service_names parameter is unset until the second statement is executed, so a new session tries to connect using service name 'hr' or 'market' fails with ORA-12514.
The second statement succeeds because Oracle is able to find matching group of service name('hr' and 'market') in the list of 65536 service names.
如何重现问题
This problem could be reproduced by running the following steps :
1. Using alter system to register 33000 groups of service name.
declare wk_cnt char(10000); v_sql varchar2(200); begin for wk_cnt in 0..33000 loop v_sql := 'alter system set service_names=''test'',''' || wk_cnt || ''',''' || wk_cnt || '_A'' scope=memory sid=''orcl1'''; execute immediate v_sql; end loop; end; /2. Using oradebug to dump tracefile.
SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug tracefile_name SQL> oradebug dump errorstack 3
3. Find the current number of service name groups that is registered in the instance using keyword 'kmmsga'.
kmmsg kmmsga_ [06003E120, 06003E4A8) = 00000000 00000000 00000000 00000000 ... <----- in this case, the begin address of kmmsga is 06003E120 Dump of memory from 0x06003E130 to 0x06003E4A8 06003E130 00000000 00000000 8514B7C0 00000000 [................] 06003E140 9E158750 00000000 85355A00 00000000 [P........Z5.....] 06003E150 9E157E18 00000000 00000000 00000000 [.~..............] 06003E160 92869B68 00000000 92AF1C08 00000000 [h...............] 06003E170 00000000 00000000 7FFFFFFF 00000000 [................] 06003E180 00000000 000004C6 7FFFFFFF 00000000 [................] 06003E190 00000000 00000000 00000000 00000000 [................] 06003E1A0 00000001 000080EB FFFFFFFF 7FFFFFFF [................] 06003E1B0 00000000 00000000 000080EC 00010000 [................]4. Add 0x98(fixed value) to the begin address of kmmsga, in this case the result is 6003E1B8.
The value in 6003E1B8 is the current number of service name groups that is registered in the instance. Change hexadecimal 000080EC to decimal 33004.5. Using alter system to register another 33000 groups of service name.
declare wk_cnt char(10000); v_sql varchar2(200); begin for wk_cnt in 0..33000 loop v_sql := 'alter system set service_names=''test'',''' || wk_cnt || ''',''' || wk_cnt || '_B'' scope=memory sid=''orcl1'''; execute immediate v_sql; end loop; end; /6. Using oradebug to dump tracefile and find the current number of service name groups.
The value in 6003E1B8 now is increased to 00010000. Change hexadecimal 00010000 to decimal 65536. kmmsg kmmsga_ [06003E120, 06003E4A8) = 00000000 00000000 00000000 00000000 ... Dump of memory from 0x06003E130 to 0x06003E4A8 06003E130 00000000 00000000 8514B7C0 00000000 [................] 06003E140 9E158750 00000000 8505DC00 00000000 [P...............] 06003E150 9E157E18 00000000 00000000 00000000 [.~..............] 06003E160 92869B68 00000000 92AF1C08 00000000 [h...............] 06003E170 00000000 00000000 7FFFFFFF 00000000 [................] 06003E180 00000000 000004C6 7FFFFFFF 00000000 [................] 06003E190 00000000 00000000 00000000 00000000 [................] 06003E1A0 00000001 FFFFFFFF FFFFFFFF 7FFFFFFF [................] 06003E1B0 00000000 00000000 00010000 00010000 [................]7. New connection using service name 'test' fails with ORA-12514.
sqlplus xxx/xxx@xxx:1521/test ERROR: ORA-12514: "TNS:listener does not currently know of service requested in connect descriptor"
如何解决这个问题
To solve the issue, use any of below alternatives:
1. Upgrade to 12.1.0.1.
- OR -
2. Restart the instance as a workaround.
The number kept in kmmsga will be reset to 0 after the instance restart.
- OR -
3. Apply superseding interim Patch 22036236, if available for your platform and Oracle version.
To check for conflicting patches, please use the MOS Patch Planner Tool
Please refer to
Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
If no patch exists for your version, please contact Oracle Support for a backport request.
Reference
Connection With Net Service Name Sometimes Fails With ORA-12514 During DataPump Export/Import In RAC (Doc ID 2302731.1)
