ORA-00354 corrupt redo log block header
I ran in to an issue recently where a database was reporting ORA-00257 archiver error, connect internal only, until freed. However when looking at the system I could not find evidence to support the ORA-00257.
Numerous m000 trace files in which KEWROCISTMTEXEC encountered ORA-12899
While clearing up some space in diag/trace directory, I noticed numerous trace files were being generated by the Memory Monitor slave process (m000). These trace files were being generated at regular intervals. Read more »
ORA-04020 When Dropping Materialized View with a Spatial Index
While upgrading our GIS development database from 11gR1 to 11gR2 we found that we were getting deadlocks when dropping materialized views that had spatial indexes. The deadlock always occurred on the first attempt to drop the view and any further attempt to drop the view would report success. However; an attempt to recreate the materialized view would result in an ORA-00955.
Read more »
ORA-20200: Database/Instance does not exist in STATS$DATABASE_INSTANCE
When attempting to run a Statspack instance report using spreport.sql the following error is generated.
Read more »
08S01: Communication link failure. System call recv() failed with OS error 104 ECONNRESET
When attempting to establish a connection to a remote Oracle TimesTen database using ttIsqlCS on Linux you receive the following error.
Read more »
java.sql.SQLException: Problems with loading native library/missing methods: no ttJdbc1121 in java.library.path
When trying to execute a Java program that connects to remote Oracle TimesTen database you receive the following stack trace.
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders. Many times the source of confusion lies in the similarity of this error number to ORA-12154.
ORA-12543: TNS:destination host unreachable
Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders.
This aim of this document is to show a method for diagnosis and resolution to a scenario in which an ORA-12543 is raised during the connection to a database.
Read more »
ORA-12545: Connect failed because target host or object does not exist
Connectivity errors are common questions on Oracle related forums and in many cases they are highly misunderstood by the poster of the questions and some responders.
This aim of this document is to show a method for diagnosis and resolution to a scenario in which an ORA-12545 is raised during the connection to a database.
Read more »
NI cryptographic checksum mismatch error: 12599
In testing of Oracle Enterprise Manager 11g R1 Grid Control we noticed frequent ORA-12599 errors in the alert logs of the monitored databases including the repository database. Upon further investigation we found that the ORA-12599 error message, such as the one below, was generated every time a connection was made to the target database as SYSDBA through Oracle Enterprise Manager 11g R1 Grid Control.
Read more »
ORA-12154: TNS:could not resolve the connect identifier specified
Questions on this error seem to be a regular occurrence on Oracle related forums so I decided to add a post about it here. This post will demonstrate the error, how to look up the error and finally how to resolve the error.
Read more »
Setting up Oracle Net Services Tracing on the Client and Server
Tracing the Oracle Net Services provides detailed information to aid in diagnosing connectivity issues. This document will detail the parameters used in tracing Oracle Net Services along with providing an example of tracing a connection to a database.
Read more »
Using the OERR Utility to Aid in Error Investigation
Many Oracle products on UNIX have a utility called OERR that can be used to aid error investigation. The OERR utility is not documented so many new to Oracle on UNIX may not be aware of its existence.
Read more »
Data Guard Standby Archive Destination Full
Problem:
You notice the following error in the alert.log of the Data Guard Standby database. Your Standby is configured to write its archive logs to the Fast Recovery Area.
Errors in file /u01/app/oracle/diag/rdbms/standby/standby/trace/standby_arc0_8253.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remaining bytes available. ************************************************************************ You have following choices to free up space from recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************ Errors in file /u01/app/oracle/diag/rdbms/standby/standby/trace/standby_arc0_8253.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 1292288 bytes disk space from 11646 limit ARC0: Error 19809 Creating archive log file to '/u01/app/oracle/oradata/standby/arc/STANDBY/archivelog/2010_06_01/o1_mf_1_134_%u_.arc'
Solution:
You have several of options in resolving this issue as suggested in the error message. Below we will detail the options available along with providing recommendations.
Changing the RMAN RETENTION POLICY or Backup using BACKUP RECOVERY AREA
The RMAN RETENTION POLICY is used to determine how long to keep backups on the file system and records of the backup. If this value is changed to lower number backups can be marked as available for space reclamation. I would recommend that you do not make this change lightly. Changing the retention policy should be done with input of the owner of the data as it could adversely impact the recovery time.
A better option would be to backup the files in Fast Recovery Area using the BACKUP RECOVERY AREA command. When files are backed up using this method they are marked as available for space reclamation.
Recommendation- Changing the RETENTION POLICY should be a discussion with the owner of the data and not used as a short term fix for a full Fast Recovery Area. Backing up the Fast Recovery Area is the better of these two options but may not resolve the issue at hand if the Fast Recovery Area is subject to regular backups.
Adding space to DB_RECOVERY_FILE_DEST that can be reflected in DB_RECOVERY_FILE_DEST_SIZE
If possible you should consider adding more space to the location pointed to by DB_RECOVERY_FILE_DEST and reflect the new space in DB_RECOVERY_FILE_DEST_SIZE. Adding space to the Fast Recovery Area may not be possible for either a short term solution such as this or as long term solution. The size of the Fast Recovery Area is the topic of another good discussion to have the owner of the data. It is very probable that size originally planed for the Fast Recovery Area is now inadequate and a new size needs to be agreed upon
Recommendation- If you have the space available this could be a quick short term fix to get archive logs flowing again. However it is not the optimal solution.
Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY
Removing archive logs that are no longer needed is a quick and easy way to free up space. Using RMAN is the recommended method and removes a lot of the guess work out of which logs to remove.
In RMAN you can use the delete archivelog all completed after|before|between ‘date’; command to remove archive logs that are no longer needed. Below is an example.
[oracle@dreco ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 2 09:56:29 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: PRODDB (DBID=459961910, not open)
RMAN> delete archivelog all completed before 'sysdate';
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
List of Archived Log Copies for database with db_unique_name STANDBY
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
140 1 139 A 01-JUN-10
Name: /u01/app/oracle/oradata/standby/arch/1_139_719914169.dbf
144 1 140 A 01-JUN-10
Name: /u01/app/oracle/oradata/standby/arch/1_140_719914169.dbf
142 1 141 A 02-JUN-10
Name: /u01/app/oracle/oradata/standby/arch/1_141_719914169.dbf
146 1 142 A 02-JUN-10
Name: /u01/app/oracle/oradata/standby/arch/1_142_719914169.dbf
<<DELETED for clarity>>
Do you really want to delete the above objects (enter YES or NO)?
If you want to avoid being prompted to enter YES or NO in regards to deleting the archive logs you can add noprompt to end of the command.
You can automate the deletion of archive logs that have been applied to the standby in the case of space of pressure on the Fast Recovery Area by setting the RMAN parameter ARCHIVE DELETION POLICY TO APPLIED ON STANDBY. After setting the parameter you will noticed in the alert log when logs are deleted.
Wed Jun 02 08:48:06 2010 Media Recovery Log /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_182_60dqhzvy_.arc Wed Jun 02 08:48:13 2010 Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_177_60dqh5h1_.arc Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_172_60dqp6s3_.arc Wed Jun 02 08:48:14 2010
Recommendation- Setting the ARCHIVE DELETION POLICTY TO APPLIED ON STANDBY is the best solution for ongoing archive log maintenance on the Data Guard Standby. If you are currently experiencing space pressure now the removal of archive logs already applied using the RMAN command delete archivelog all completed after|before|between ‘date’; should get the logs flowing once again.
Note: Even after setting the archive deletion policy you still might encounter space pressure in Fast Recovery Area due to archive logs. This could occur if there is a sharp increase in log traffic and low available space. Sound Fast Recovery Area management practices should limit the occurrence of such issues.
Linux Error 111: Connection refused
When attempting to start the listener you get an error like the one below.
SET AUTOTRACE fails with error SP2-0618
Problem:
You attempt to set autotrace and receive the following error message.
SQL> set autotrace traceonly; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report SQL> connect / as sysdba
Solution:
The user executing AUTOTRACE needs to have the PLUSTRACE role. By default the PLUSTRACE role does not exist and can be created by the SYS user with ORACLE_HOME/sqlplus/admin/plustrace.sql.
The plustrace.sql creates the PLUSTRACE role and grants SELECT on V_$SESSTAT, V_$STATNME and V_$MYSTAT. PLUSTRACE is granted to the DBA role with ADMIN OPTION.
For databases earlier than 10g you might also need to create the plan table. Execute the script ORACLE_HOME/rdbms/admin/utlxplan.sql to create the PLAN_TABLE. The PLAN_TABLE already exists on database version 10g and higher.
I just got/found an ORA-00600 after doing < X >. What should I do?
This is a common question on many Oracle related forums. Unfortunately, no one monitoring the forums can provide the answer to that question.