Archive for the ‘How To’ Category

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 archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.alter database flashback on

There is two possible way to solution.
1. without increasing DB_RECOVERY_FILE_DEST_SIZE.
2. by increasing DB_RECOVERY_FILE_DEST_SIZE.

Without increasing DB_RECOVERY_FILE_DEST_SIZE.
1. Check whether the database is in archive log mode and automatic archiving is enabled.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     29
 Next log sequence to archive   31
 Current log sequence           31

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used

4. if SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.

 $rman target /
 RMAN>delete archivelog until time 'SYSDATE-1';
 RMAN>delete archivelog all;

1. See the path of flash recovery area.

 SQL> show parameter db_recovery_file_dest;

2. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Sytem Altered.

CreateFile error 32 when trying set file time

March 24, 2011 Leave a comment

Today I am going to install Oracle 10g Client and ODAC11gR2 (Oracle Data Provider .Net) in Microsoft Windows XP Professional Service Pack 2. I have got following error message in console.

CreateFile error 32 when trying set file time

After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message

ORA-00439: feature not enabled: Flashback Database

March 13, 2011 1 comment

Some feature is not enabled in oracle database standard edition, as a result we can not use flashback feature in Oracle Database if you are use in standard edition of oracle.

Following Error Message show in Oracle 10g standard edition

SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Version of Oracle

Oracle Database 10g Release - Production
PL/SQL Release - Production
CORE      Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - Production

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
PARAMETER                                                        VALUE                                                           
Objects                                                          TRUE                                                            
Connection multiplexing                                          TRUE                                                            
Connection pooling                                               TRUE                                                            
Database queuing                                                 TRUE                                                            
Incremental backup and recovery                                  TRUE                                                            
Instead-of triggers                                              TRUE                                                            
Parallel load                                                    TRUE                                                            
Proxy authentication/authorization                               TRUE                                                            
Plan Stability                                                   TRUE                                                            
Transparent Application Failover                                 TRUE                                                            
Sample Scan                                                      TRUE                                                            
Java                                                             TRUE                                                            
OLAP Window Functions                                            TRUE

So flashback database is disabled in standard edition or in standard one  edition. To be able to flashback on you have to use enterprise edition.

ORA-01775: looping chain of synonyms

March 12, 2011 1 comment
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself.
Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

For example, the following definitions are circular: CREATE SYNONYM s1 for s2;

Check your synonyms with:
select * from all_synonyms where synonym_name = ‘name’ or table_name = ‘name’;

SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>

select owner, synonym_name, connect_by_iscycle CYCLE
from dba_synonyms
where connect_by_iscycle > 0
connect by nocycle prior table_name = synonym_name
and prior table_owner = owner
select 'PUBLIC', synonym_name, 1
from dba_synonyms
where owner = 'PUBLIC'
and table_name = synonym_name
and (table_name, table_owner) not in (select object_name, owner from dba_objects
where object_type != 'SYNONYM')

Shared Memory Realm does not exist

December 20, 2010 Leave a comment


ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Solution :

check ORACLE_SID has value if not exist then set ORACLE_SID

For Windows Environment

set ORACLE_SID=orcl 
echo %ORACLE_SID% 

For Linux Environment

$ export ORACLE_SID=orcl

Now You need to Manually startup the database

sqlplus "sys/password as sysdba" 
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area  574619648 bytes
Fixed Size                  1250236 bytes
Variable Size             192941124 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL> exit

Basic Concept of Performance Tuning in Oracle

October 25, 2010 Leave a comment

Scope of Performance Tuning

There is four main area for Performance Tuning.

1. SQL Tuning          – Responsibility of the Developer
2. Database Tuning     – Responsibility of the Database Administrator
3. System Tuning       – Responsibility of the System Administrator
4. Network Tuning      – Responsibility of the Network / LAN / WAN Administrator.

SQL Tuning

1. Find the problem to a single SQL

You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.

2. Analyze the SQL to determine the nature of the problem

Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.

3. Fix the problem.

Almost every performance problem has a solution; it’s just that some are more complex than others. In order of increasing complexity and expense, such fixes include:
  • Analyze the underlying table to give Oracle’s Cost Based Optimizer the information it needs to resolve the SQL efficiently.
  • Add one or more hints to the SQL to encourage or discourage certain execution plans.
  • Minor changes to the SQL to encourage or discourage certain execution plans.
  • Restructure a poorly designed SQL that cannot be resolved efficiently.
  • Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
  • Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
  • Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.

Database Tuning

For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.
  • Tuning the cache hit ratio
  • Tuning the library cache
  • Tuning the log buffer
  • Tuning buffer cache hit ratio
  • Tuning sorts
  • Tuning rollback segments
  • Identifying missing indexes
  • Identifying index fragmentation
  • Identifying free list contention
  • Identify significant reparsing of SQL
  • Reducing database fragmentation
  • Rebuilding indexes
  • Reduce thrashing or poor system performance (or how to un-tune oracle?!)

System Tuning(Operating System)

Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:
  • Basic OS Tuning Concepts
  • Solaris Tuning Parameters
  • Linux Tuning Parameters
  • HP-UX Tuning Parameters
  • Windows Tuning Parameters
  • Other Operating System Tuning Information

Network Tuning

Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time 

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:

  • SQL*Net performance
  • SQL*Net tuning



September 20, 2010 Leave a comment

Setting the DB_WRITER_PROCESSES parameter (Background process DBWR) to a value greater than one is supported starting with the Oracle 8.0 release.

Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it.  Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance.  Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

How to change db_writer_processes parameter

SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     1

SQL> alter system set db_writer_processes=2 scope=spfile sid='*';

System altered.

Now restart the database to take the change effect.

SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     2

“The number of DBWR should be two times the number of CPUs (vary 1 or 2 depending on idle state of CPUs during production/test run)”


Default value of DB_WRITER_PROCESSES is 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.