Turn Off Archive log mode

Once the Oracle database in archivelog mode can be turned off with the following steps:

Shutdown the database
shutdown immediate;

Change the parameter log_archive_start=false in pfile. This will stop the automatic archival of the database and the database administrator have to manually archive the
redo file for backup issuing alter system archive log all in archivelog mode;

Start the database in mount mode
startup mount;

Alter the database in noarchivelog mode. This will change the database mode to noarchive log mode such that redo log files can be overwritten before they are archived.
alter database noarchivelog;

Open the database
alter database open;

Verify the database status
archive log list;

Command to Remove Window Service including Oracle Starter Database Service

Oracle installation create window starter service for Oracle database. It may sometimes be necessary to drop the unnecessary services. It can be removed in two ways in case of oracle starter service:

Using oracle utililty named oradim -
used to create, delete, edit the System Identification Number for the database instance.

Add new SID
oradim -new -sid sidname -intpwd password -maxusers no_of _users -startmode a
Edit SID
oradim -edit -sid sidname -intpwd password -maxusers no_of _users -startmode a
Delete SID
oradim -delete -sid sidname

Using windows OS Commnad -
can delete any other Services including service for Oracle Database

Delete Service
sc delete ServiceName

Performance and Database Connections

Various performances related issue is typically caused by large number of database connection and we have to consider following major areas that can be checked and adjusted for the number of oracle database connections in oracle 9i.

If we are using the different server names for the same oracle database (TNS names) that will create additional, unnecessary connections. Therefore server name and TNS names both must match and case sensitive.


Oracle database doesn’t do the sufficient locking to prevent conflicts between connections on the same server. The conflicts can show up under very high database usage and timing, and only from a few application plus major factor about the timer initiated disconnection and dead connection detention feature that allows SQL NET to identify connection that have been left hanging by the abnormal termination of a client and unfortunately this feature is available only after the release of 2.1 and later. And only if the SQL NET can identify dead connections due to the client process or machine being unreachable, the connection will be closed when an error is generated by the send operation and server process will exit and a small probe packet will be sent from the server to client at user defined interval and that will automatically forces a database rollback of uncommitted transactions and locks held by the user of the broken connection and allow further regular continuous transactions. Thus, this feature minimizes the waste of resources by connections that are no longer valid plus we can specify the expire time in SQLNET.ORA of database server side (file usually in $ORACLE_HOME/network/admin) which will direct SQLNET to send a probe to the client through the network and if the client doesn’t respond, it will be killed.


MAX CONNECTION parameter: We have to consider the total number of db connections for dbusers per data source. Additional database connection requests beyond this value will be queued for the next available connection. If this value is exceeded, the user request will fail generating errors in the log file.

minConnection=5
maxConnection=50
initialConnection=5
poolGrowth=5

Run a script on the database server, each node with incoming connections grouped by
a. db user
b. program
c. machine name

Script:

select username, count(*) from v$session
group by username;
select machine count(*)
from v$session
group by machine;

select program, count(*)
from v$session
group by program;

Note: Outcome of this can be send to the developers and they will have to take corrective steps if necessary.