Connection With Net Service Name Sometimes Fails With ORA-12514 During DataPump Export/Import In RAC (文档 ID 2302731.1)

In this Document

Symptoms

Cause

Solution

References
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Oracle Database - Standard Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.Symptoms
There 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.Cause
  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”
   Solution
   To solve the issue, use any of below alternatives:
8. 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.
   References
   NOTE:14320415.8 - Bug 14320415 - The value of service name is sometimes removed during expdp/impdp in RAC - superseded