Oracle Database Discussions

Oracle 10g on Windows Server 2003
One Listener Service with Two instances

I understand you, the reader, may have some experience working with Oracle Servers and I am hoping you may be able to shed some light on my current issue.

I have Oracle 10g Version 10.1.0.2.0 Standard running on Windows Server 2003 SP2. There are multiple database instances; as you know each instance has a SID, its own service “OracleService<SID>”, memory space, files, configurations, etc.

Each database runs though the same listener with an individual listener service.

There are now six databases on the system (mastsch1, indosch1, bathsch1, bathsch2, bathsch3, and bathsch4)

The most recent database, bathsch4, is a clone/copy of bathsch3.

I chose to use the Database Administrator Console to Clone an existing database (bathsch3) to create a new database (bathsch4).

What I ended up with was a single listener service “bathsch4” reporting two instances “bathsch4” READY and “bathsch3” BLOCKED.

I’ve checked lsnrctl services and status to gather this information, validated the PATH, and registry settings, and tested resolution of TNSPING which all report the same issue.

Issue 1:
I need assistance in understanding how exactly to alter “bathsch3” so it runs again in its own listener service “bathsch3” and is no longer BLOCKED.

Thankfully, all of the other databases (mastsch1, indosch1, bathsch1, bathsch2, and bathsch4) are running correctly.

I walked through many scripts and diagnostics to get were I thought stopping and starting the service would bring me back to completion, but ended up after a system cycle back to the same problem, so I’ve clearly missed something.

In the process of researching this issue, I found that my listener.log had grown exponentially and was at 700 MB already. The majority of this log was the windows service “OracleServicebathsch3” attempting to connect to the database.
I shutdown the database and stopped the service which drastically reduced the rate of growth.

Issue 2:
I need assistance in understanding how exactly to set up the listener.log to either rollover or truncate after 20 MB.

If you believe you are capable of provide me with any helpful guidance? Please do so, I am happy to answer any question and provide every bit of information you feel would be of assistance.

Thank you,

James