ORA-03135: connection lost contact.

 This error appears when you are trying to connect to an oracle database and the connection receives time out. This ORA-03135 can be cause by the firewall.

In order to solve/avoid this kind of situation you have to modify the sqlnet.ora file. This file is located on $ORACLE_HOME/network/admin/sqlnet.ora.

One solution for the ORA-03135 error is to increase the value of thesqlnet.ora:expire_time parameter in the sqlnet.ora file :

SQLNET.INBOUND_CONNECT_TIMEOUT = 300
SQLNET.SEND_TIMEOUT = 300
SQLNET.RECV_TIMEOUT = 300

To check a solution for the ORA-03135 error, start by checking to see if the OS PID still exists, using the:

ps –ef|grep syntax

Thank you for reading this article.

Difference between a "rebuild index" and the "rebuild index online"

Question:  What is the difference between a "rebuild index" and the "rebuild index online" operation.  When do I consider adding the "online" argument to a rebuild index statement?
Answer:  The fundamental difference between an "alter index rebuild" and an "alter index rebuild online" is the access methods for the index data.  When doing an "alter index rebuild", Oracle reads the existing index to create the new index.  With the "alter index rebuild online" option, Oracle instead performs a full-table scan on the target table, an operation that might cause performance degradation during high DML periods.

• Alter index rebuild online:  During a online index rebuild, Oracle will make a snapshot log on the target table to hold DML activity, read the table in a full-table scan (read consistent), build the new index and then apply the changes from the snapshot log after the index has been rebuilt.  Because the "alter index rebuild online" performs a full-table scan, it can be perform using parallel query (a parallel hint), and the online rebuild can also be performed in NOLOGGING mode.
• Alter index rebuild:  During a "regular" index rebuild, locking occurs as the existing index is read.  Hence, this command is designed for scheduled downtime periods where there is no DML activity.

ORA-00257: archiver error.

error:ORA-00257: archiver error.

Question: I am running Oracle Apps and getting a ORA-00257 error:

ORA-00257: archiver error. Connect internal only, until freed.

Answer:  The oerr utility says this about the ORA-00257 error:

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameterarchive_log_dest is set up properly for archiving.

The Oracle ARCH background process is responsible for taking the redo logs from the online redo log file system and writing them to the flat file

ORA-00257 is a common error in Oracle.  You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery area (FRA), or db_recovery_file_dest_size . 
First, make sure your automatic archiving is enabled.  To check the archive lo made, try:

SQL> archive log list;

Now, note thatyou can find archive destinations if you are using a destination ofUSE_DB_RECOVERY_FILE_DEST by:

SQL> show parameter db_recovery_file_dest;

The next step in resolving ORA-00257 is to find out what value is being used fordb_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

You next need to archive the log files by,

SQL> alter system archive log all;

It is important to note that within step five of the ORA-00257 resolution,  you may also encounter ORA-16020 in theLOG_ARCHIVE_MIN_SUCCEED_DEST, and you should use the proper archivelog path and use (keeping in mind that you may need to take extra measures if you are using Flash Recovery Area as you will receive more errors if you attempt to use LOG_ARCHIVE_DEST):

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

The last step in resolving ORA-00257 is to change the logs for verification using:

SQL> alter system switch logfile;

ORA-28001: the password has expired

In order to re-enable the schema owner you can run the following SQL:

ALTER USER <username> IDENTIFIED BY <new_password>;

Another way to re-enable the schema owner can be done with brconnect:

brconnect -u system/<password> -f chpass -o <username> -p <password>

ORACLE-DBA Docs 11g

Scripts Every DBA Should Have

I. Display the Current Archivelog Status :

ARCHIVE LOG LIST;

II. Creating a Control File Trace File

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

III. Tablespace Free Extents and Free Space

column Tablespace_Name format A20

column Pct_Free format 999.99

select Tablespace_Name,

Max_Blocks,

Count_Blocks,

Sum_Free_Blocks,

100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free

from

(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks

from DBA_DATA_FILES

group by Tablespace_Name),

(select Tablespace_Name FS_TS_NAME,

MAX(Blocks) AS Max_Blocks,

COUNT(Blocks) AS Count_Blocks,

SUM(Blocks) AS Sum_Free_Blocks

from DBA_FREE_SPACE

group by Tablespace_Name)

where Tablespace_Name = FS_TS_NAME;

IV. Display Allocated Space & Quota by User

select * from DBA_TS_QUOTAS

order by Tablespace_Name, Username;

V. Show Allocated Storage for All Objects

column Segment_Name format A40

select Segment_Name, Extents, Blocks

from DBA_SEGMENTS

where Segment_Type = ‘&segment_type’

order by Segment_Name;

VI. Map a Tablespace’s Used and Free Space

select Segment_Name, File_Id, Block_Id, Blocks

from DBA_EXTENTS where Tablespace_Name = ‘&&tablespacename’

UNION

select ‘Free Space’, File_Id, Block_Id, Blocks

from DBA_FREE_SPACE where Tablespace_Name = ‘&&tablespacename’

order by 2,3;

VII. Blocks Used by Data in a Table

select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))

Blocks_Used from &table;

VIII. Reset a User’s Password

select ‘ALTER USER &&1 IDENTIFIED BY VALUES

‘||””||Password||””||';’

from DBA_USERS where Username = UPPER(‘&&1′);

IX. Query V$PARAMETER

column Name format A50

column Value format A28

select Name, Value from V$PARAMETER;

X. Show Statement Execution Path

select LPAD(‘ ‘,2*LEVEL)||Operation||’ ‘||Options

||’ ‘||Object_Name Q_PLAN

from PLAN_TABLE where Statement_ID = ‘TEST’