2008/10/26

ORA-12500 on windows platform

Oracle 9.2.5.0, Windows 2000

This is a recent issure I found concerning oracle net service.

If I start OracleServiceSID under a domain user, and start OracleOracleHomeTNSListener under LocalSystem, I cannot connect to oracle through local naming on the same machine, I get ORA-12500. But I can connect to oracle using OS authentication on the same machine, and I can also connect to oracle through local naming from a client machine. But this wired thing will never happen if I start both of them under the same domain user, or start OracleServiceSID under LocalSystem.

Big difference between Linux/Unix and Windows platform regarding Oracle is that windows have domain user, and service for oracle, and dierent services can be started under different users. DBAs should always pay attention to under which user the service is started. Different privs can lead to oracle errors.

Discovering node incorrectly due to listener configuration

Oracle 9.2.0.5, Windows 2000

Normally, oracle will dynamically register service with the name defined in the parameter service_names. So if you add the same entry inside the listener.ora, it will list the same service twice when you execute "lsnrctl status" command. Sometimes, it's not necessary to add this entry into listener.ora. But recently, when I was trying to discover node from OEM, I could not discover the database
on that node or the discovering process generated some error. The OMS, agent, listener are all running. OHS and Listener can be discovered. I finally found that I didn't list any service in my listener.ora file. After I added that service, the discovering process is fine. I'm not sure if this is the reason or some kind of oracle bug for 9i. Worth a shot!

Note:
When the same thing happened, you may want to check out the dbsnmp.log, nmiconf.log, and Oracle%Oracle_Home%Agent.nohup file inside the directory %Oracle_Home%\network\log.
Always try restarting Agent.

Startup OracleServiceSID using spfile

Oracle 9.2.5.0, Windows Server 2000

If ORA_SID_AUTOSTART is set to TRUE in Registry, oracle instance will be started automatically when OracleServiceSID is started.
If ORA_SID_PFILE is set the full path of the initializaiton parameter file, oracle instance will be started using the specified pfile when OracleServiceSID is started. If this entry is not present, then oradim will try to start the database with an SPFILE or PFILE from %ORACLE_HOME%\database.

Could not start Oracle 9i database http service

This error looks like one that frequently appeared when starting the OHS on a DB9iR2 on Windows Platform. If you try to start the OHS by the windows services console and by the command line, a conflict with the lock file (pid file located in %Oracle_Home%\Apache\Apache\logs\) will prevent the OHS from starting, you must delete the file prior to attempt starting the OHS. You cannot start OHS with both, the console
and the command line, you should choose just one of them.

Web Based EM DB Control for Oracle 9i

There is no actually web version EM database control for oracle 9i. But DBAs can launch EM from web browser through JInitiator.
Oracle JInitiator is implemented as a plug-in (Netscape Navigator) or ActiveX Object (IE).
Oracle JInitiator allows you to specify the use of the Oracle certified JVM (Java Virtual Machine) on web clients instead of relying on the default JVM provided by the browser.
There are many docs on OTN website.

Some versions of JInitiator (ex. 1.1.8.19) must be plugged in manually to Firefox. These are the steps:
1) Install JInitiator.
2) C:\Program Files\Oracle\JInitiator 1.1.8.19\bin\NP JinitXXXX.dll XXXX = version
3) Paste it under this directory of Firefox (C:\Program Files\Mozilla Firefox\plugins)
4) Restart Firefox.

RMAN Performance when using UNC Paths

It is not advisable to take RMAN backups through the network on Windows. Taking the backup to a local disk and then transfering the backup pieces on the UNC path is 10 times quicker, this matches the maximum bandwidth of the link. It was a windows issue.

ORA-19504 due to windows user privs using rman backup

If you are using windows and rman backup destination is network device, OracleServiceSID must be started by OS user with permissions to write to this network device. OracleServiceSID running under LocalSystem has no access right to network device. So, you have to set account for OracleServiceSID in service control panel and restart that service.

2008/10/18

Trick to delete job stuck in OEM

Sometimes, deleting job in OEM makes the job status 'deleting' and the job will get stuck. Try to stop the agent on the host where the job is aimed to and start again. The job will be able to be deleted.

Fixit job can not be deleted before you delete it from the event it is associated to.

Steps to cleanly stop OMS

to stop the management server:

$ORACLE_HOME/Apache/Apache/bin/apachectl stop
$ORACLE_HOME/bin/oemctl stop oms sysman/password
shut down database normally
$ORACLE_HOME/bin/lsnrctl stop

to start

$ORACLE_HOME/bin/lsnrctl start
start database normally
$ORACLE_HOME/bin/oemctl start oms sysman/password
$ORACLE_HOME/Apache/Apache/bin/apachectl start

How to Shutdown Oracle Instance on Windows Platform?

Oracle 9.2, Windows 2000

Shutdown oracle instance through SQL*Plus is always the safe way.
If you want to shutdown oracle instance through oracle service on windows platform, make sure the following parameters are set in the Registry.

Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEX\:

ORA_SID_SHUTDOWN = TRUE|FALSE - Set this to TRUE to enable the proper shutdown of the database
ORA_SID_SHUTDOWN_TIMEOUT = Timeout(in seconds) - Sets the maximum time (in seconds) to wait for the shutdown to compplete before the service for a particular SID stops. This timeout should be set high enough to give the database time to complete the shutdown. If this is lower than the time needed, you won't have a clean shutdown. You can check this in the alert file.
ORA_SID_SHUTDOWNTYPE = n|i|a - Where n = SHUTDOWN NORMAL; i = SHUTDOWN IMMEDIATE; a = SHUTDOWN ABORT

Failure to do so, then stopping a service in this manner, will do WORSE than a SHUTDOWN ABORT because Windows will just "clean" the memory. This action is akin to performing KILL -F in Windows or a KILL -9 in Unix. Any cold backups are there likely to be useless.

If you want the oracle instance startup automatically when the system reboot, the following key needs to be set.
ORA_SID_AUTOSTART = TRUE|FALSE

Notice that:
1) OracleServiceSID is running does not guarantee that oracle instance is running.
2) Users cannot connect to oracle unless OracleServiceSID is running.

2008/10/05

VNI-2015: authentication error

VN-2015 error occurs because of mismatch of the required privileges of the user. It all depends on the OS and type of USER.
If the OS is MS-NT, please follow the procedure.

If you are a DOMAIN user, the user will need "Logon as Batch Job" privilege. On a Primary Domain Controller, if the account that has "Logon as Batch Job" privilege is not a member of the Local Administrator group, you will also need to grant Logon Locally.

If you are a local NT user, you will need "Logon as Batch Job" privilege.

Verify the Intelligent Agent's startup account is the default SYSTEM.

If the batch job needs to access network resource, you need to verify the user has the access privilege to the network resource.

ORA-27100

ORA-27100: Shared memory realm already exists

Cause: Tried to start duplicate instances, or tried to restart an instance that had not been properly shutdown

Action: Use a different instance name, or cleanup the failed instance's SGA

To resolve ORA-27100, you should execute the following instructions:
-Move the spfile from its default location or rename the spfile
-Restart the service
-Now the service will start the database using pfile, with old SGA settings. This would start your database successfully.
-Create the new spfile from pfile.

Another cause for this error is trying to start the database using a wrong user on windows platform. This happened to me when I tried to start the database using a domain user on windows 2000. After changing the user to LocalSystem, database was started successfully.