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.

Locks in Oracle Database

Locks are held by Oracle Database to maintain the integrity during the concurrent updates. Locks are held until the transaction in a session is committed or rollbacked. Oracle maintains row level lock in the tables that are recently being updated.

In the distributed transaction, the network connection also plays vital role during locking. Once locked transaction in this mode never released if the failure in completion of processing on the remote side. This may also result in the object lock so that complete system may be affected.

So, it is the database administrator job to look after the database as well as to monitor the network passage side by side. Usually Oracle Database administrator use
v$locked_object to monitor the lock objects in the oracle database. One can get the session information and object held in the lock by linking v$locked_object to
v$session and dba_objects. Use folowing query to check the lock held in Oracle Database.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

You may kill the session to release the lock by

alter system kill session 'SID,SERIAL#';

Oracle releases locks during the restart of the database.

How to decrease the size of the datafiles

The dba_data_files view gives information about the physical datafiles in the database whereas dba_free_space view gives information about the free space in the datafiles with their related block id. The query below gives the datafiles free space and total space allocated for the datafiles.

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

With this information one can decrease the size of the datafile by the free space with the command given below:

alter database datafile 'filepath\filename' resize filesize;

Free Space in Percentage Tablespace Wise

select z.TABLESPACE_NAME,sum(z.free_space_in_mb) free_space_in_mb,
sum(z.total_space_allocated_in_mb) total_space_allocated_in_mb,
round(sum(z.free_space_in_mb)/sum(z.total_space_allocated_in_mb)*100,2) free_pct
from (
select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
) z
group by z.tablespace_name
order by z.TABLESPACE_NAME;

Free Space in Percentage At Data File Level

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb, round((free_space/a.BYTES),2)*100 free_pct
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

Export Oracle Dump file from command prompt or from Window Run Mode

The oracle Export Utility is used to export entire database or users or list of tables to the path given by the FILE parameter. The DIRECT path method is much faster than conventional method. The direct path method reads data directly from the disk and places in the buffer and then sent to the export client , writes it to the disk. Whereas conventional path follow SQL SELECT statement to fetch the data thus have to evaluate expression in the memory before transporting the file to the client to write to the disk. The COMPRESS parameter specifies the extent management during import of the table data. If it is set to Y then the table data is consolidated into single extent during import otherwise it uses current storage parameter of the tables during import. It is good practise to verify the log file after the export of the file have been completed to verify the successful export.

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export LOG=log_path_name\logfilename.txt

You can export the selected tables from the file with the TABLES option

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export TABLES=TABLE_NAME LOG=log_path_name\logfilename.txt

Import Oracle Dump file from command prompt or execute from window Run mode

The oracle Import Utility is oracle database tool used to import users, database, tablesapces from database dump file. The utility can be invoked from the command prompt and also could be executed from windows Run mode. You could simply change the parameters and paste the command given below from windows Run mode. The log parameter maintains the log of the import.

IMP username/password FILE=file_path\filename.dmp FROMUSER=user_previously_dumped TOUSER=current_user ROWS=Y INDEXES=Y CONSTRAINTS=Y LOG=log_path_name\logfilename.txt

You can import the selected tables from the file with TABLES option

IMP username/password FILE=file_path\filename.dmp FROMUSER=user_previously_dumped TOUSER=current_user TABLES=BPT_SIGNATURES ROWS=Y INDEXES=Y CONSTRAINTS=Y LOG=log_path_name\logfilename.txt

Managing Oracle tablespace and datafiles

It is the best practise to separate user data from system data in an oracle database. Doing so enhances the performance of the database.
Oracle manages its datafiles in logical space known as Tablespace. A tablespace can have multiple datafiles but a single datafile can belong to one and only one tablespace. Datafiles are the physical files that stores data which are divided again into logical segment and extents. It should be noted that autoextend clause extend
the datafile automatically as and when needed but the auto extension of the size of datafile depend on the maximum file size limit of the operating system and it also degrade performance of the database. Locally managed extents maintain its data state information in its own header(bitmapped). This avoids the throughput in the data dictionary, which increases performance as well.

It is the best approach to separate user datafile and user data index file in separate tablespace. Temporary tablesapce are used for sorting operation required during query executions.

--user data holder tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE01.DBF' SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--add more datafile to a tablespace

ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATAFILE02.DBF' SIZE 100M AUTOEXTEND ON;

--user index tablespace and datafiles
CREATE TABLESPACE TABLESPACE_NAME_INDX
DATAFILE '%ORACLE_BASE%\ORADATA\SID\DATA_INDX01.DBF'
SIZE 100M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

--user temporary tablespace and datafiles
CREATE TEMPORARY TABLESPACE TABLESPACE_TEMP
TEMPFILE '%ORACLE_BASE%\ORADATA\SID\TEMP01.DBF'
SIZE 20M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Simple AJAX

Make your web page optimized by implementing AJAX technology. AJAX stands for Asynchronous JavaScript and XML made popular by Google.

XMLHttpRequest object is the key behind the communication between clients and server. Different browsers use different methods to create the XMLHttpRequest object. Internet Explorer uses an ActiveXObject, while other browsers uses the built-in JavaScript object called XMLHttpRequest.

The onreadystatechange event of the XMLHttpRequest will process the response from a server. By invoking the function on this event we retrieve the information sent by the server.

The readyState property holds the status of the server's response. Each time the readyState changes, the onreadystatechange function will be executed.

Here are the possible values for the readyState property:
State Description
0 Request is not initialized
1 Request has been set up
2 Request has been sent
3 Request is in process
4 Request is complete

Here is the sample code.


start of html tag
start of body tag

start of javascript function

function simpleAjax(){
var xmlHttp;
try {
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e) {
// Internet Explorer
try {
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
try {
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) {
alert("Your browser is very old, does not support AJAX!");
return false;
}
}
}
xmlHttp.onreadystatechange=function() {
if(xmlHttp.readyState==4) {
//get data from the server
document.simpleAjax.fromServer.value=xmlHttp.responseText;
}
}
xmlHttp.open("GET","simpleAjax.php",true);
xmlHttp.send(null);
}

end of javascript function

start of form tag name="simpleAjax"

Name: between angle bracket(input type="text" onkeyup="simpleAjax()" name="user")
Data: between angle bracket(input type="text" name="fromServer")

end of form tag
end of body tag
end of html tag

php file

///output the data to the client
////echo "Data from Server";
//?>

save the form as "simpleAjax.php" in the same directory as html file.

How to run Oracle 9i database in Archive Mode

Steps:
1) Create pfile if the database is started from spfile.
create pfile from spfile;
This will create pfile in the %ORACLE_HOME%/database/pfileSID.ora
or
create pfile='pathname' from spfile;

2) Shutdown the database
shutdown immediate

3) add parameters
log_archive_start=true
log_archive_format='SID%D%T%S.ARC'
log_archive_dest='%ORACLE_BASE%\oradata\archive'

4) Start up the database in mount stage
startup mount;

5) Activate the database to archiving mode
alter database archivelog;

6) Verify the activation
archive log list;

7) Open the database for use
alter database open;

8) Generate the archive log manually
alter system archive log current;

This turns the database in archive log mode.