mattypenny

Well, it seems odd to me anyway….

If you set up a synonym pointing across a database link….

SQL> conn elvis@old_db
SQL> create synonym sale for sale@new_db
  2  /
Synonym created.

Other users cannot see it….

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale
  2  /
select count(*) from elvis.sale
                                *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from new_db

Unless you proceed it with the username (in bold below)

SQL> conn elvis/@old_db
Connected.
SQL> drop synonym sale;
Synonym dropped.
SQL> create synonym sale for <strong>elvis</strong>.sale@new_db
  2  /
Synonym created.

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;

  COUNT(*)
----------
   3906455

SQL> 

Well, it seems odd to me anyway….

If you set up a synonym pointing across a database link….

SQL> conn elvis@old_db
SQL> create synonym sale for sale@new_db
  2  /
Synonym created.

Other users cannot see it….

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale
  2  /
select count(*) from elvis.sale
                                *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from new_db

Unless you proceed it with the username (in bold below)

SQL> conn elvis/@old_db
Connected.
SQL> drop synonym sale;
Synonym dropped.
SQL> create synonym sale for <strong>elvis</strong>.sale@new_db
  2  /
Synonym created.

SQL> conn scott@old_db
Connected.
SQL> select count(*) from elvis.sale;

  COUNT(*)
----------
   3906455

SQL> 

BJS is the scheduling tool we use on our Windows servers. Its produced by Camellia Software:

• Camellia website
  
  ….and it’s very good.
  
  I had a problem this morning though.
  
  

BJS jobs would go into ‘Awaiting Exec’ state for up to 45 minutes before running.

The Log file would show:


* BATCH JOB SERVER
* A Camellia Software Product - www.CamelliaSoftware.com
* Copyright 1994-1998
* Version 2.1 - A Build 529
* ***** J O B L O G

JOB NO. 0737


Job Name: BIG Backup PWHC Open
Username: xxx
Submitted by:

Server: bigserver
Source File: \bigserverD$BJSbig_backup_pwhc_open.bat
Output Directory: D:BJSBJSWORK
*
***

>
> 11/04/05 08:00:29 PM Job scheduled for execution
>
> 11/04/05 08:45:00 PM Job started execution
>

The reason for this was/is that there is a setting under Server-Administer-Change Service Parameters called ‘Maximum Batch Job Processes’. This limits the number of jobs you can run at any one time. It should be set to twice the number of jobs you might want to have running conccurrently.

There is also a setting against Job Class which does something similar - under Server-Administer-Job Class Maintenance

A case of RTFM….

BJS is the scheduling tool we use on our Windows servers. Its produced by Camellia Software:

• Camellia website
  
  ….and it’s very good.
  
  I had a problem this morning though.
  
  

BJS jobs would go into ‘Awaiting Exec’ state for up to 45 minutes before running.

The Log file would show:


* BATCH JOB SERVER
* A Camellia Software Product - www.CamelliaSoftware.com
* Copyright 1994-1998
* Version 2.1 - A Build 529
* ***** J O B L O G

JOB NO. 0737


Job Name: BIG Backup PWHC Open
Username: xxx
Submitted by:

Server: bigserver
Source File: \bigserverD$BJSbig_backup_pwhc_open.bat
Output Directory: D:BJSBJSWORK
*
***

>
> 11/04/05 08:00:29 PM Job scheduled for execution
>
> 11/04/05 08:45:00 PM Job started execution
>

The reason for this was/is that there is a setting under Server-Administer-Change Service Parameters called ‘Maximum Batch Job Processes’. This limits the number of jobs you can run at any one time. It should be set to twice the number of jobs you might want to have running conccurrently.

There is also a setting against Job Class which does something similar - under Server-Administer-Job Class Maintenance

A case of RTFM….

Going to try this out: http://download.microsoft.com/download/5/f/2/5f26ad97-ace1-4454-975c-bca19dbd61d1/sfu35intro.doc#_Toc59604538

SFU 3.5 includes Interix, a complete POSIX development environment tightly integrated with the Windows kernel. Both Korn and C shells are included, and the Bash shell is available as a free download. With more than 350 UNIX utilities and a single rooted file system, SFU provides a familiar environment for UNIX developers, users, and administrators.

Looking at Oracle’s External Tables feature, which ‘uses the ‘sql-loader’ engine.

Links include:

• http://www.quest-pipelines.com/newsletter-v6/0105_B.htm

• http://www.oracle.com/oramag/oracle/01-sep/o51o9i.html

Ran the following:

create or replace directory bjs_history as 'D:BJS_HISTORY';

drop table external_bjs_history;

CREATE TABLE external_bjs_history (bjs_sched_time varchar2(12),
bjs_job_no varchar2(8),
bjs_job_name VARCHAR2(40),
record_type varchar2(5),
job_started varchar2(17),
job_finished varchar2(17)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY bjs_history
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
badfile 'history.bad'
)
LOCATION ('history.dat')
)
REJECT LIMIT UNLIMITED;

select count(*) from external_bjs_history;

and got:

SQL> @c:bjs_owner

Directory created.
Table dropped.
Table created.

select count(*) from external_bjs_history
*
ERROR at line 1:
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 "badfile": expecting one of: "exit, (, reject"
KUP-01007: at line 4 column 9
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I took out the badfile clause and all OK:

Probably a rtfm type thing….

Or something…..

Blocking locks are easy enough to spot, through utllockt or v$session_waits or the goo-ey of your choice. The situation we had this morning seems to be that locks were being held which prevented a batch load from running. However, if you looked for them in a blocking locks type query you wouldn’t see diddley, ‘cos the batch load was bombing out straightaway. At least not unless you caught it at the right time

So, need to look at V$lock - in particular the ctime value. Found these:

0E3E08F0 0E3E09BC 20 TX 196617 877445 6 0 175785 0 1D95BCA4 1D95BCB8 20 TM 81940 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B9D4 1D95B9E8 20 TM 81942 0 2 0 175785 0 1D95BC2C 1D95BC40 20 TM 81933 0 3 0 175785 0 1D95B4AC 1D95B4C0 20 TM 81938 0 2 0 175785 0 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST ——– ——– ———- – ———- ———- ———- ———- CTIME BLOCK

1D95B254 1D95B268 20 TM 81960 0 3 0 175785 0 1D95B614 1D95B628 20 TM 81952 0 3 0 175785 0 1D95B7F4 1D95B808 20 TM 81935 0 3 0 175785 0 Bounced the associated app, and all now ok

Note, 15 years on: I have have a clear idea of I was on about here, and I don’t do Oracle any more. I think I linked he ctime value to a ‘clock time’ and then tracked addr back to an application. I’m leaving it as published anyway a) just in case anybody else can decipher what I’m on about, and b) in case it becomes clear when I look at it next

This is a problem we had a month or so back. I’m going to cut and paste from my notes at the time…

We are trying to upgrade our warehouse from 8.1.7 to 9.2.0.4.

In testing the pre-production database, we are getting the error below from our systems integration tools (Crossworlds). This transfers data via jdbc to the warehouse. This works fine against the old 8i production database, but not against the 9i database.

ThreadPool Worker (#6775863)] [Type: Error] [MsgID: 90012] [Mesg: 
ParseException java.text.ParseException: Unparseable date: ""]

Used Metalink note Note 232357.1 to get the following versioning information:

Database Product Name is ... Oracle
Database Product Version is Oracle9i Enterprise Edition Release 9.2.0.5.0 - 

Pro duction
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 8.1.7.2.0
JDBC Driver Major Version is 8
JDBC Driver Minor Version is 1
=============

Following advice from Oracle, I copied cl* and nls* out of the 9i database $ORACLE_HOME/jdbc/lib to the same directory on the client.

This seemed to sort it out.

However there was an an associated problem which I’ll put into the next post….

In the course of trying to sort out the problem outlined in the previous post, I hit a problem trying to get the jdbc to talk to the 9i database at all.

This started occurring once I’d re-built the 9i database. [Re-building the 9i database seems like, and on the face of it was, an over-reaction to the problem - its a longish story but at the time we had a fairly short but imminent window of opportunity for doing re-builds, we were waiting for Oracle to get back to us, the NLS_CHARACTERSET setting was wrong, it was Friday afternoon, yada yada..]

Anyway, running with a test script derived from Metalink Note Note 232357.1, I got

C:temp>javac doselect.java

C:temp>java doselect
Jstring is ... jdbc:oracle:thin:@::
Exception in thread "main" java.sql.SQLException: Io exception: The Network Adap
ter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at doselect.main(doselect.java:22)

Most of the literature, both Metalink and otherwise, quite sensibly directs you towards:

• correcting your jdbc connection string
• correcting your sqlnet set up
• I checked and re-checked but no issues with either. I could connect using sqlplus with no problem.

The Oracle support guy set up a web conference so he could see what was going on.

I showed him the listener.ora.

He spots a problem in the entry for the database I’m trying to connect to.

‘Aha!’ he says, there’s your problem.

‘Aha!’ I say, no its not, I just changed that [from what the Oracle chap was recommending] half an hour ago to try it out.

Oracle support guy insists I change it and re-try. In truth a little bit irritated, I change it back, from my point of view in order to demonstrate that its not relevant to the issue.

So I re-start the listener and…..it works.

So I eat several helpings of humble pie, profusely thank the Oracle support guy and go and lie down in a darkened room.

I then, for some reason try the connection again and…..it fails.

Nobody has changed anything in the meantime.

On investigation, after a listener restart, connectivity is fine for maybe half a minute or so then starts failing.

The culprit seems to be the dynamic service registration feature. As it happens, I’ve picked up a slight mistrust of the feature - I can’t particularly remember why or from where.

Anyway, given that:

• it takes maybe half a minute or so for the service to dynamically register with the listener, and
• it takes maybe half a minute or so for connectivity to start failing

The first thing I try is diasabling the dynamic service registration, as follows:

ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=tcp)(host=hostname)(port=7))" SCOPE=SPFILE;

Turning the feature off is described here:

• Note 140571.1

And…it works.

I leave the database and listener up all weekend and all is still OK.

My best guess is that this is a bug. The likelihood is that it only effects our very specific (and largely unsupported) set of circumstances. I should possibly pursue it with Oracle Support, but the support guy I’m talking to is reluctant to do so - I could escalate it but its probably not worth it. I’ve typed all the above up a) so I can keep it for my records b) in the hope that anyone Googling “Exception in thread “main” java.sql.SQLException: Io exception: The Network Adapter could not establish the connection” may find it. We wouldn’t have hit hit if I hadn’t rebuilt the database, but then we would have hit it if somebody had inadvertently turned on dynamic service registration in the future. It was a learning experience.

In the course of trying to sort out the problem outlined in the previous post, I hit a problem trying to get the jdbc to talk to the 9i database at all.

This started occurring once I’d re-built the 9i database. [Re-building the 9i database seems like, and on the face of it was, an over-reaction to the problem - its a longish story but at the time we had a fairly short but imminent window of opportunity for doing re-builds, we were waiting for Oracle to get back to us, the NLS_CHARACTERSET setting was wrong, it was Friday afternoon, yada yada..]

Anyway, running with a test script derived from Metalink Note Note 232357.1, I got

C:temp>javac doselect.java

C:temp>java doselect
Jstring is ... jdbc:oracle:thin:@::
Exception in thread "main" java.sql.SQLException: Io exception: The Network Adap
ter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at doselect.main(doselect.java:22)

Most of the literature, both Metalink and otherwise, quite sensibly directs you towards:

• correcting your jdbc connection string
• correcting your sqlnet set up
• I checked and re-checked but no issues with either. I could connect using sqlplus with no problem.

The Oracle support guy set up a web conference so he could see what was going on.

I showed him the listener.ora.

He spots a problem in the entry for the database I’m trying to connect to.

‘Aha!’ he says, there’s your problem.

‘Aha!’ I say, no its not, I just changed that [from what the Oracle chap was recommending] half an hour ago to try it out.

Oracle support guy insists I change it and re-try. In truth a little bit irritated, I change it back, from my point of view in order to demonstrate that its not relevant to the issue.

So I re-start the listener and…..it works.

So I eat several helpings of humble pie, profusely thank the Oracle support guy and go and lie down in a darkened room.

I then, for some reason try the connection again and…..it fails.

Nobody has changed anything in the meantime.

On investigation, after a listener restart, connectivity is fine for maybe half a minute or so then starts failing.

The culprit seems to be the dynamic service registration feature. As it happens, I’ve picked up a slight mistrust of the feature - I can’t particularly remember why or from where.

Anyway, given that:

• it takes maybe half a minute or so for the service to dynamically register with the listener, and
• it takes maybe half a minute or so for connectivity to start failing

The first thing I try is diasabling the dynamic service registration, as follows:

ALTER SYSTEM SET LOCAL_LISTENER="(ADDRESS=(PROTOCOL=tcp)(host=hostname)(port=7))" SCOPE=SPFILE;

Turning the feature off is described here:

• Note 140571.1

And…it works.

I leave the database and listener up all weekend and all is still OK.

My best guess is that this is a bug. The likelihood is that it only effects our very specific (and largely unsupported) set of circumstances. I should possibly pursue it with Oracle Support, but the support guy I’m talking to is reluctant to do so - I could escalate it but its probably not worth it. I’ve typed all the above up a) so I can keep it for my records b) in the hope that anyone Googling “Exception in thread “main” java.sql.SQLException: Io exception: The Network Adapter could not establish the connection” may find it. We wouldn’t have hit hit if I hadn’t rebuilt the database, but then we would have hit it if somebody had inadvertently turned on dynamic service registration in the future. It was a learning experience.