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  |
 |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 11:36:10 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
