Archive

Archive for the ‘Error Solving’ 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
from v$RECOVERY_FILE_DEST;

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';
 or,
 RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE.
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-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;
CREATE SYNONYM s2 for s3
CREATE SYNONYM s3 for s1
or
CREATE SYNONYM s4 for s4

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
union
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

ERROR:

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% 
orcl 

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

ORA-19809: limit exceeded for recovery files

June 28, 2010 2 comments
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:25:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
 

Shutdown the database first

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  1250356 bytes
Variable Size             180358092 bytes
Database Buffers          411041792 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

Problem: The value db_recovery_file_dest_size is not enough for generate archive log

          
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest_size           big integer 2G

Solution: Increase the value of db_recovery_file_dest_size following way.

 
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

System altered.

Now open the database.

SQL> alter database open;

Database altered.

Listener-Broken pipe

December 14, 2009 2 comments

I have successfully install Oracle on Linux, installed and configured my all Java and PHP application for a content Server and a SMS Gateway and everything is working fine. After several months later from an angry user saying that he cannot login to the application and the SMS getaway not working.

The error message indicates a problem connecting to the Oracle database. After some troubleshooting (lsntctl status) I have found the Oracle TNS listener is not running, I attempt to start it and get the following stack trace error:

[oracle@contentserver admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 14-DEC-2009 08:44:43

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /u01/app/oracle/product/9.2.0.1.0/bin/tnslsnr: please wait...

TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 32: Broken pipe

Then I have check with the SYS admin to only find out that nothing has changed with any of the network settings. Every this is ok.

This is one of those troubleshooting issues that can take several hours to track done. But as it turns out, the most common reason for this stack trace when attempting to start the listener is the log file for the listener ($ORACLE_HOME/network/log/listener.log) has reached its 2GB file size limit on Linux. To alleviate the problem, simply archive or delete the file and restart the listener.

How to solve this problem:

Switch to the network log directory

[oracle@contentserver log]$ cd /u01/app/oracle/product/9.2.0.1.0/network/log

See the size of the listener.log, here I have found 2.1 GB

[oracle@contentserver log]$ du -h listener.log
2.1G    listener.log

Copy the log file to the other directory if you need to backup this file for any kind of troubleshooting task.

[oracle@contentserver log]$ cp listener.log /home/oracle/

Then remove the file using OS command, to do this issue the following command.

[oracle@contentserver log]$ rm listener.log

Now start the listener service.

[oracle@contentserver log]$ lsnrctl start