Home » RDBMS Server » Networking and Gateways » Resource leak problem (Oracle Database 12.1.0.1.0 standart edition one, x64)
Resource leak problem [message #634689] Fri, 13 March 2015 05:11 Go to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
Hi

Oracle Database 12.1.0.1.0 "standart edition one" installed on windows 2008 Server R2 SP1 (64 bit).

I configure access to remote Databases MS SQL Server 2008. The access configured via "heterogeneous services".:

1) Created ODBC source 1 : type "ODBC SQL Server 6.01.7601.17514 Microsoft Corparation", name "AOF_Statistic_140"
2) Created ODBC source 2 : type "ODBC SQL Server 6.01.7601.17514 Microsoft Corparation", name "AOF_Statistic"

3)created file "initAOF_Raport.ora" in "с:\app\oracle_db\product\12.1.0\dbhome_1\hs\admin"
Сontent of the file "initAOF_Raport.ora" :
HS_FDS_CONNECT_INFO = AOF_Raport_140
HS_FDS_TRACE_LEVEL=0
HS_LANGUAGE=RUSSIAN_RUSSIA.CL8MSWIN1251
HS_IDLE_TIMEOUT=5
HS_TRANSACTION_MODEL=READ_ONLY


4)created file "initAOF_Statistic.ora" in "с:\app\oracle_db\product\12.1.0\dbhome_1\hs\admin"
Сontent of the file "initAOF_Statistic.ora" :
HS_FDS_CONNECT_INFO = AOF_Statistic_140
HS_FDS_TRACE_LEVEL=0
HS_LANGUAGE=RUSSIAN_RUSSIA.CL8MSWIN1251
HS_IDLE_TIMEOUT=5
HS_TRANSACTION_MODEL=READ_ONLY


5) Сontent of the file "с:\app\oracle_db\product\12.1.0\dbhome_1\network\admin\listener.ora"
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = с:\app\oracle_db\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:с:\app\oracle_db\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC=
         (SID_NAME= AOF_Statistic)
         (ORACLE_HOME=с:\app\oracle_db\product\12.1.0\dbhome_1)
         (PROGRAM=dg4odbc)
    )
    (SID_DESC=
         (SID_NAME= AOF_Raport)
         (ORACLE_HOME=с:\app\oracle_db\product\12.1.0\dbhome_1)
         (PROGRAM=dg4odbc)
    )	
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Indas-debug)(PORT = 1521))
    )
  )


6) Сontent of the file "с:\app\oracle_db\product\12.1.0\dbhome_1\network\admin\tnsnames.ora"
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Indas-debug)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

aof_Raport  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST= localhost)(PORT=1521))
    (CONNECT_DATA=(SID = AOF_Raport))
    (HS=OK)
  )
aof_Statistic  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST= localhost)(PORT=1521))
    (CONNECT_DATA=(SID = AOF_Statistic))
    (HS=OK)
  )


7) Created public links:
CREATE PUBLIC DATABASE LINK aof_Statistic CONNECT TO "ascub" IDENTIFIED BY "111**" using 'aof_Statistic';
CREATE PUBLIC DATABASE LINK aof_Raport CONNECT TO "ascub" IDENTIFIED BY "111**" using 'aof_Raport';

8)created pl sql package "test_sql_hs" with following code:

procedure raport_rep_L337_3 is
  l_dt date;
 begin

  ---- table L337 from AOF_RAPORT
  for cur1 in (select
  to_date(TO_CHAR("DateAndTime", 'DD.mm.YYYY HH24:MI:SS'),'DD.mm.YYYY HH24:MI:SS') as DateAndTime,
  "D3_Q" as d3_q,
  "D5_Q" as d5_q,
  "D7_Q" as d7_q,
  "D9_Q" as d9_q,
  to_number("D11_Q") as d11_q,
  to_number("D13_Q") as d13_q,
  to_number("Work_Time") as work_time
  from L337_3@AOF_RAPORT
  where "DateAndTime"> (l_dt-1)
  order by "DateAndTime" asc ) loop
    begin
     insert into TB$A$L337 (dateandtime,d3_q,d5_q,d7_q,d9_q ,d11_q,d13_q,work_time,UPDATE_TIME)
        values(cur1.dateandtime,cur1.d3_q,cur1.d5_q,cur1.d7_q,cur1.d9_q ,cur1.d11_q,cur1.d13_q,cur1.work_time,sysdate);
    exception  when dup_val_on_index then null;
    update TB$A$L337 t set t.update_time=sysdate where t.dateandtime=cur1.dateandtime;
    end;
 end loop;
 commit;
exception when others then
   rollback;
   sys_log(p_oid => 'raport_rep_L337',  content => sqlerrm);
   raise;
end;
procedure static_rep_L337_3 is
  l_dt date;
 begin

  ---- table L337 from AOF_RAPORT
  for cur1 in (select
  to_date(TO_CHAR("DateAndTime", 'DD.mm.YYYY HH24:MI:SS'),'DD.mm.YYYY HH24:MI:SS') as DateAndTime,
  "D3_Q" as d3_q,
  "D5_Q" as d5_q,
  "D7_Q" as d7_q,
  "D9_Q" as d9_q,
  to_number("D11_Q") as d11_q,
  to_number("D13_Q") as d13_q,
  to_number("Work_Time") as work_time
  from L337_3@AOF_STATISTIC
  where "DateAndTime"> (l_dt-1)
  order by "DateAndTime" asc ) loop
    begin
     insert into TB$A$L337 (dateandtime,d3_q,d5_q,d7_q,d9_q ,d11_q,d13_q,work_time,UPDATE_TIME)
        values(cur1.dateandtime,cur1.d3_q,cur1.d5_q,cur1.d7_q,cur1.d9_q ,cur1.d11_q,cur1.d13_q,cur1.work_time,sysdate);
    exception  when dup_val_on_index then null;
    update TB$A$L337 t set t.update_time=sysdate where t.dateandtime=cur1.dateandtime;
    end;
 end loop;
 commit;
exception when others then
   rollback;
   sys_log(p_oid => 'raport_rep_L337',  content => sqlerrm);
   raise;
end;

procedure rep_MSSQL_140 is
begin
 raport_rep_L337_3;
 static_rep_L337_3
 exception when others then
  rollback;
end;

9) Created JOB which runs every 30 seconds function: test_sql_hs.rep_MSSQL_140

begin
  sys.dbms_job.submit(job => :job,
                      what => 'begin
test_sql_hs.rep_mssql_140;
exception when others then
 rollback;
end;',
                      next_date => sysdate,
                      interval => 'sysdate+30/(24*60*60)');
  commit;
end;



PROBLEM IS:

Initially JOB runs normally, but after 12 hours of working errors occure:

1)code test_sql_hs.rep_mssql_140 returns error: "ORA-28511: lost RPC connection to heterogeneous remote agent using ..." permanently.
2) At the same time I can not login to OS using any windows account, I get error "Access denied". I have "lack of resources" message in Windows system log.

The problem disappears only when I reboot Windows service "OracleOraDB12Home1TNSListener".

[Updated on: Fri, 13 March 2015 06:40]

Report message to a moderator

Re: Resource leak problem [message #634708 is a reply to message #634689] Fri, 13 March 2015 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I should say: this is Windows, this is Microsoft... reboot is "normal".
I don't see what you expect from us but we tell you to raise a SR on MOS.
First check this combination of Oracle version/edition is certified for this version/edition of Windows.

Re: Resource leak problem [message #634712 is a reply to message #634708] Fri, 13 March 2015 11:57 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
In document:
Quick Installation Guide 12c Release 1 (12.1) for Microsoft Windows x64 (64-Bit) (E56576-01)

I see list support window OS:

Oracle Database for Windows x64 is supported on the following operating systems:
Windows Server 2008 x64 and Windows Server 2008 R2 x64 - Standard, Enterprise, Datacenter, Web, and Foundation editions.


=> Oracle Standard Edition One is supported on Windows Server 2008 R2 x64.

I think that the problem is in the process: TNSLSNR.exe (Windows service "OracleOraDB12Home1TNSListener")
I think that : reboot process TNSLSNR.exe - is not "normal" !!!

Re: Resource leak problem [message #634713 is a reply to message #634712] Fri, 13 March 2015 11:59 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
TNSLSNR.exe - is oracle software
Re: Resource leak problem [message #634714 is a reply to message #634713] Fri, 13 March 2015 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes but the problem appears ONLY on Windows server, and this since this process exists, 20 years ago.

Re: Resource leak problem [message #634715 is a reply to message #634714] Fri, 13 March 2015 12:12 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
We will bought : windows server 2008 R2
We will bought: database Standart Editon One
what to do ?
I not access on MOS Sad

[Updated on: Fri, 13 March 2015 12:16]

Report message to a moderator

Re: Resource leak problem [message #634716 is a reply to message #634715] Fri, 13 March 2015 12:19 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
I also tested it on windows 7 64 bit - it has the same problem
Re: Resource leak problem [message #634717 is a reply to message #634716] Fri, 13 March 2015 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, I told you, this problem exists in all Windows versions since the listener was created.
If you have no access to MOS then the only thing you can do is to restart the listener from time to time before the problem appears. It's just a job in Windows scheduler.

Re: Resource leak problem [message #634718 is a reply to message #634717] Fri, 13 March 2015 12:36 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
Did the oracle-developers for so many years could not solve the problem ( Sad)

[Updated on: Fri, 13 March 2015 12:37]

Report message to a moderator

Re: Resource leak problem [message #634720 is a reply to message #634718] Fri, 13 March 2015 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe because the problem is in Windows.
As I said it does not occur in any of the many other OS it is implemented, not in AIX, not in Linux (any flavour), not in HP-UX, not in SunOS, not in Solaris, not in GCOS...

Re: Resource leak problem [message #634722 is a reply to message #634715] Fri, 13 March 2015 15:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
bna2015 wrote on Fri, 13 March 2015 17:12
We will bought : windows server 2008 R2
We will bought: database Standart Editon One
what to do ?
I not access on MOS Sad
When did you buy the SE1 licence? It s not possible to buy it without a year of support. So unless you bought it very soon after it became available, you should still be covered and be able to raise a TAR on MOS.
Re: Resource leak problem [message #634775 is a reply to message #634722] Sun, 15 March 2015 22:06 Go to previous messageGo to next message
bna2015
Messages: 8
Registered: March 2015
Junior Member
We have bought license without tech support because we was distributor's partner. We are not distributor's partner now. Our license is less then year.

I have set up Enterprise Edition v12.1.0.2 for testing on the same server. The problem is not detected.

Unfortunately, Standard Edition is available only v12.1.0.1.
Re: Resource leak problem [message #634784 is a reply to message #634775] Mon, 16 March 2015 02:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What you describe does not sound legal to me. I work for a partner (there is no such thing as a "distributor") and we cannot sell licences without support. Not even to ourselves.

[Updated on: Mon, 16 March 2015 02:11]

Report message to a moderator

Re: Resource leak problem [message #634842 is a reply to message #634784] Mon, 16 March 2015 12:10 Go to previous message
bna2015
Messages: 8
Registered: March 2015
Junior Member
I'm Sorry. I'm probably confused, because I don't understand very well in licensing.
Perhaps I misunderstood our administrators

[Updated on: Mon, 16 March 2015 12:14]

Report message to a moderator

Previous Topic: Intermittent Oracle connection issue
Next Topic: Not able to login
Goto Forum:
  


Current Time: Fri Mar 29 05:49:56 CDT 2024