Oracle RAC Load Balancer Not Working?
Jul 4, 2013
I'd like to talk about some interesting behaivor with Oracle RAC load balancer.We have Oracle RAC 11.1.0.6 - 2 nodes.
We noticed sometimes the number of sessions { select count(1) from v$session; } was not divided equally between nodes. Also load average was very different between node #1 and node #2.Now we define service explicitly.
srvctl add service -d MYORCL -s MYSERVICE -r MYORCL1,MYORCL2
srvctl config service -d MYORCL -a
MYSERVICE PREF: MYORCL1 MYORCL2 AVAIL: TAF: NONE
After we change connection string of corresponding application(s) the spread will be more equal:
select service_name,inst_id, count(*) the_qty
from gv$session
where service_name = 'MYSERVICE'
group by service_name, inst_id
SERVICE_NAME INST_ID THE_QTY
---------------------------------------------------------------- ---------- ----------
MYSERVICE 1 68
MYSERVICE 2 79
Although it's not round robin algorithm, the distribution looks more equal. It seems explicitly defined service of Oracle RAC has better load balancing approach comparing to the default service (created with db by default).
But there is some downside with more 'equally' distributed load balancing. Consider a client program that is running the following SQL once a 5 minutes:
select ...
from A
inner join B on ...
inner join C on ...
inner join D on ...
where the tables B, C and D data is changing all the time.Once load balancing is distributed more 'equally', the query above will arrive to node #1 and to node #2 more equally. It will cause more interconnect traffic, because Oracle RAC will need to synchronize requested blocks all the time, with much high rate.
View 1 Replies
ADVERTISEMENT
Aug 3, 2011
At the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.
Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.
The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.
View 15 Replies
View Related
May 30, 2013
Output in the sqlldr log:-
------------------------------------------------------------------------------
Path used: Direct
Insert option in effect for this table: APPEND
Trigger DEV."R_TM_BK_BORROWER" was disabled before the load.
DEV."R_TM_BK_BORROWER" was re-enabled.
The following index(es) on table "YO"."TM_BK_BORROWER" were processed:
index DEV.I_NK_TM_BK_BORR_1 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_2 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_3 loaded successfully with 1554238 keys
index DEV.I_NK_TM_BK_BORR_31 loaded successfully with 1554238 keys
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 1554241
Total logical records rejected: 48
Total logical records discarded: 2
Total stream buffers loaded by SQL*Loader main thread: 7695
Total stream buffers loaded by SQL*Loader load thread: 0
------------------------------------------------------------------------------
So, I still see in the sqlldr log that the stream buffers are laoded by main thread and load thread is still not being used. SQL*Loader load thread did not offload the SQL*Loader main thread. If the load thread takes care of the current stream buffers, then it allows the main thread to build the next stream buffer while the load thread loads the current stream on the server. We have a 24 CPU server.
I am using the following parameters set to true in the sqlldr:- parallel=true , multithreading=true , skip_index_maintenance=true in the sqlldr
View 5 Replies
View Related
Dec 11, 2012
We need to simulate load like production environment in test database without actually creating any data volume. Is there any tool which can be used to achieve this, if yes which one is best tool and why?
View 1 Replies
View Related
Jul 3, 2008
How can i convert my ms excel record into oracle records??
View 3 Replies
View Related
Dec 28, 2010
how to load a XL sheet data into oracleDB.
View 7 Replies
View Related
Mar 18, 2011
He has a Windows 7 64 bit laptop and wants to install Oracle Instant Client into it.
Hs has just installed the Oracle Client on his machine and after that he had installed the Oracle Data Access for 64 bits [URL] it still doesn't have the Oracle DataAccess assembly avalaibale. When we look into C:WindowsAssembly we only have the X86 version installed.
He already has Visual Studio 2010 installed on his machine.
Using a tool like Navicap the connection is made possible ...
View 3 Replies
View Related
Apr 1, 2013
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 3 Replies
View Related
Sep 6, 2011
CASE 1:
when i tried to load the data i got the below error,
Error starting at line 2 in command:
INSERT INTO RECON_MATCHED_DETAILS (RECON_MATCHED_DETAIL_OID, RECON_ID, STATEMENT_DATE, EXECUTION_DATE, TRANSACTION_NUMBER, TRANSACTION_DATE, TRADE_ID, TRANSACTION_TYPE, LINK_ID, ITEM_TYPE, ASSET_CODE, ISIN, BUYSELL_INDICATOR, SETTLEMENT_DATE, CURRENCY, QUANTITY, VALUE,
[code]...
CASE 2:
i tried to load the data in oracle 11g but i'm unable to load the data,and for testing i tried with a single row of data.but surprisingly the table filled with (null)s
View 3 Replies
View Related
Jun 18, 2013
I want to load data from oracle table to flat file(csv/text file). Is there SQL query to do this?
View 14 Replies
View Related
Oct 17, 2012
How i can load excel sheet into a table in oracle through pl/sql procedure or a pl/sql block. Excel sheet is saved on my c or d drive on my machine. In xls format.
View 14 Replies
View Related
Mar 7, 2013
I need a tutorial for using utl_file package to read and load in to oracle data.
Oracle is in Linux box.
View 32 Replies
View Related
Aug 13, 2011
how to load data from excel sheet to oracle 10g form from client.
View 1 Replies
View Related
Jun 8, 2011
i want to load jpg into my oracle database 10g throw sqlloader
i did folloing steps
step 1)
Create the table as follows
CREATE TABLE image_table (file_id NUMBER(5),
file_name VARCHAR2(30),file_data BLOB);
step 2 )
Create control file as follows
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
[code].....
step 3)
Then i have run this command
F:oracleproduct10.2.0db_1in>sqlldr control=F:practicecontrol.ctl
Username:system
Password
so i got this error
SQL*Loader: Release 10.2.0.5.0 - Production on Wed Jun 8 13:47:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL*Loader-404: Column FILE_ID present more than once in IMAGE_TABLE's INTO TABL
E block.
View 4 Replies
View Related
Jan 3, 2011
how to load oracle table data into EXCEL Sheet .
View 5 Replies
View Related
Nov 25, 2011
In a flat file 50000 records are there.In that some of the records having special charcters.From that special character record the remaing records are not loading to oracle tables. to load the remaining records,after the record which is having special characters.The data is loading using sql*loader
View 3 Replies
View Related
Jan 31, 2013
best method (easy and efficient) to load tables from sql server database to oracle database.
View 2 Replies
View Related
Sep 28, 2011
Issue: Unable to load a flat file through Oracle Loader
Below is the script that is being used:
drop table dl_fact_fac_data_xtern;
create table dl_fact_fac_data_xtern
(
[Code].....
After rnning this script, it prompts that table has been created; but once I fire the select command on the table I receive the following errors :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "data": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 10 column 11
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
View 2 Replies
View Related
Jun 25, 2013
i have a problem in my ODI 11g with the load into Oralce table of a fixed width file, i configured all the datasource in ODI and when i do view data i see all correct, the end of file is signed like "0D0A" but when i try my load interface i receive the message that my last field is more big than the one declared.
My file have an header of fields and the last field is a data-field of 2000 characters. I controlled and is really fixed the length cause is a COBOL file from a Mainframe. So it looks that ODI don't understand the end of that field and go ahead to the other, i just tryed to enlarge the limit but is always more big like if the file is shifting on the right.
Have i forgot some configuration in some place? The definition of the file present the end of file like Microsoft hexadecimal \u000D\u000A i try all the combination there but no way to avoid this problem.
View 1 Replies
View Related
Aug 22, 2012
After installing a 4 node cluster 11.2.0.3 with 16 CPU's (4 on each node) on IBM 795 with aix 6.1 each server is using 0.5 CPU with no user load on the system. Running SIHA on one server typically uses 0.05 CPU with no user load on the system.
View 5 Replies
View Related
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 Replies
View Related
Jan 20, 2011
I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.
But it throws exception that database instance is unavailable?
View 5 Replies
View Related
Oct 31, 2011
I tried to insert date in date column from another table date column. Below is SP ans execute procedure.
CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from
[code]....
execute test ('27-10-2011 12:01:01');
It gives error as Invalid number.
View 2 Replies
View Related
Mar 30, 2012
following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.
SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,
[Code]....
View 4 Replies
View Related
Jun 16, 2010
I have an oracle package that i am using to search for a string in a blob entry. I compiled the package and the package body in one environment, it had no errors, when i execute, i get my results.I went ahead and created the same package and function in another environment and it fails by giving me the below error
ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.IMPACTUS_PCODE", line 158 for sysadm
I have used this on other environments often and have never had an issue.
View 7 Replies
View Related
Mar 26, 2013
I am trying to setup a one way queue that will be consumed by an inhouse ESB.We currently are able to create a multi-consumer queue using:
CODEBEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue');
END;
but unfortunatly the ESB that we are using uses a jms component which seems to only be able to take mono-consumer queue.So we have created our queue usuing the following
CODEBEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => '"STRMADMIN"."STREAMS_QUEUE_TABLE"',
Queue_payload_type => 'SYS.ANYDATA',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE
[code]...
This therefore permits us to have a queue/queuetable which pushes data to a single consumer.The probleme comes when we try to add a table rule using the following command:
CODEBEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'source.t1',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name => 'strmadmin.streams_queue',
[code]...
We then get the following error:
CODEORA-06512: on line 2 24039. 00000 - "Queue %s not created in queue table for multiple consumers"
*Cause: Either an ADD_SUBSCRIBER, ALTER_SUBSCRIBER, or REMOVE_SUBSCRIBER procedure, or an ENQUEUE with a non-empty recipient list, was issued on a queue that was not created for multiple consumers.
*Action: Create the queue in a queue table that was created for multiple consumers and retry the call.
We are able to create the capture rule without any problem but without the apply rule, nothing seems to end up into the queue table.AQ is not a viable solution since it is troublesome when it comes to deletes and mass updates.
View 1 Replies
View Related
Nov 9, 2012
could not import using impdp. it hangs while processing view.
Oracle version : Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
expdp user/pwd full=y directory=export dumpfile=exp_test.dmp
There is no error in export log.
impdp user/pwd directory=data_pump_dir dumpfile=exp_test.dmp logfile=imp_test.log table_exists_action=replace
*** 2012-11-09 10:23:27.799
A deadlock among DDL and parse locks is detected.This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object MV_LOTS
View 1 Replies
View Related
Dec 29, 2010
send me the procedure for loading the data in an oracle table into an excel file.
View 5 Replies
View Related
Oct 24, 2013
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies
View Related
Oct 26, 2012
I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.
e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date
oracle table bill_temp having the below column:
emp_name,emp_sal,join_date
Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.
Is there any way to skip the loading of particular column data from data file into table?
View 12 Replies
View Related