OEM dbconsole Start Failed

Problem The dbconsole was working fine before the timezone setting (DST) was changed, but after the timezone was changed it stopped working.

solution When OEM dbconsole is started the agent timezone setting is recorded in the emd.properties. Everytime you start the dbconsole it will check the current timezone setting with the value in the emd.properties. When the two values mismatch the dbconsole will not start. We have to update the setting for the agent timezone.

To update follow the steps given below.

Check the timezone setting in /etc/environment.
The setting for TZ must be one of the supported timezone setting listed in the ORACLE_HOME/sysman/admin/supportedtzs.lst file


$ export ORACLE_SID=gcprod1
$ emctl config agent updateTZ
The output could be a different setting from the TZ in /etc/environment.

$ emctl stop dbconsole

$ emctl config agent updateTZ

$ emctl start dbconsole

Thanks
Vinod D

ORA-01652 (RAC)

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

In an RAC environment this error occurs in the following situation

Out of space in temporary tablespace.

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name = '';

If the query returns 0 then increase the size of the tablespace.

Error occurs even though space is available in the tablespace

This situation occurs when we are requesting the sort segments from another instance. This should be considered as a warning to the DBA that there is instance contention for temporary tablespace.

Use the following query to monitor the instance wise temp space usage.

SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Unlike non RAC environment, where the statement fails when this error occurs, the sort operations in RAC is successful.

Thanks

ORA-01652 (Non RAC)

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

The space in the temporary tablespace is not enough to allocate for the temp segment.

A temp segment, in the temporary tablespace, is not always used for sorting. It is sometimes used for creating objects such as index, CTAS etc. At the end of the creation these temp segments are converted in to the real objects. Hence rebuilding/creating a large index requires large temp segments thereby resulting in ORA-01652.

Solution: Increase the size of the temp tablespace by using the statement

SQL> Alter tablespace add tempfile ‘path’ size ‘size’;

Thanks

ORA - 01555 Snapshot Too Old

ORA - 01555 Snapshot Too Old Error

In case of long running queries, if data is changed while the query is running, undo segments created for that change are needed to make a read consistent image for the long running query. When these undo segments are reused while these were still need by the query, this error occurs, because oracle cannot provide the read consistent image of the data.

To avoid this error you have to postpone the reuse of the undo segments by

i) Increase UNDO_RETENTION value.

ii) Increase the size of Undo Tablespace.

Note: Committing less often would be other solution. As until transaction is committed undo segment can not be reused thus reducing the chances of a snapshot too old error.

Thanks

ORA-25153: Temporary Tablespace is Empty

When you recreate a control file or restore a controlfile from backup, you must recreate the tempfile in the temporary tablespace.

Alter temporary_tablespace add tempfile size xM reuse;

otherwise you will encounter the following error,

ORA-25153: Temporary Tablespace is Empty

Thanks

Page Space Error

Last week we created a second database in our test server. Suddenly we encountered the following errors in the alert file of both the database.

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

Solution

Check for the page size.

$ lsps -a

It was 97% used. We increased the page space and the problem is solved.

Thanks.

FRA Errors

When the FRA is full, Oracle issues the following error message,

ORA-19815: Warning: db_recovery_file_dest_size of XXXXXX bytes is 100% used, and has 0 remaining bytes available.

ORA-19809: Limit set by the db_recovery_dest_file_size is exceeded.

ORA-19804: Oracle is unable to reclaim a specified amount of bytes from the limit set by the db_recovery_file_dest_size.

ORA-01031 Insufficient Privilege

While using the flashback feature, i encountered the following error message, when i tried to query dba_tables which is owned by sys user.

Select * from dba_tables as of timestamp sysdate-30/1440;
ORA-01031 Insufficient Privilege

The above error message is resulted due to the setting of the init parameter O7_DICTIONARY_ACCESSIBILITY to FALSE.

O7_DICTIONARY_ACCESSIBILITY

Possible Values - False (default) and True

When set to False it restricts the system privilege to access the objects owned by the sys user.
When set to true it allows the access to the objects owned by the SYS user.

In order to access the objects owned by sys user despite setting the parameter to false, you must explicitly grant the following roles,

SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE

Setting the Parameter to true is not recommended for security reasons. For eg., setting the parameter to true will ignore the parameter remote_login_passwordfile parameter. Hence you can connect to the database remotely and hack the database.

0403-031 - Fork Function Failed. There is not enough Memory available

Last week our Production Server was hanged. When I issued a command it took a lot of time and finally displayed the following error message.

0403-031 - Fork Function Failed. There is not enough Memory available.

The reason for this is there is no enough Page space.

To monitor the page space issue the following command

$ lsps -a
Page Space Physical Volume Volume Group Size %Used
----------- ---------------- -------------- ---- ------
Paging00 hdisk0 rootvg 16GB 25%
hd6 hdisk1 rootvg 8GB 95%

You can use the following command to find out top 3 processes utilizing most of the page space.

$ svmon -gP -t 3

To add Paging space

1. Identify the physical volume where you want to add page space (For eg. hdisk1).
2. Identify the PPsize (say 128MB) and Free PPs (500) available by using the command lspv hdisk1.
3. Determine how much space you want to add (say 8192MB).
4. Divide 8192MB/PP Size(128) = 64. Hence you want to increase 64 logical partitions.
5. Use the chps command to increase the paging space.

$ chps -s64 hd6

Alternatively you can use SMIT tool to increase the page space.

Exp-00091 Exporting questionable statistics.

Set the statistics=none to avoid this error.
The default value is Estimate.

The error is caused by one of the following

1. Your table rows have fatel error while exporting.
2. You have used query parameter
3. client and server characterset mismatch.

After importing the data you have to gather fresh statistics to use it. So it is always advisable to use statistics=none.

ORA-03113 End of File on Communication Channel

Last week we had a problem with the annoying error message - ORA-03113 end of file on communication channel. Usually the users will run reports that will take 3 to 4 hours. Since last week they were not able to run the reports, it terminated with the error ORA-03113.

There was no error reported in the alert log file and no trace file generated. I even tried to alter the value of SQLNET.ORA parameter sqlnet.expire_time, but nothing worked.

The network was fine and there is no DBLINK in the database.

After much diagnosis and discussion with the networking people, i found the error was due to the timeout connection setting in the firewall. We changed the value from 1 hr to 5 hr and now we are not facing the error.

How to solve ORA-03113 Error

It occurs because the connection between client and server process was broken. We need to find out why the connection got broken.There are many reasons for that. Use the following steps to find out the possible reason.

Step 1: Look for the Alert log file for any error message. If the ORA-03113 error is reported in the alert log it will be accompanied by any othererror message. For example Oracle internal error, or server down etc etc etc.. try to solve that issue. If there is no error in the alert log or no trace file generated then it may not be due to a database error.

Step 2: Look for listener.log file size. If the file size is too large, try to stop the listener, create a new listener.log and restart the listener.

$ LSNRCTL STOP
$ mv listener.log listener_old.log
$ LSNRCTL START ( This will create a new listener.log file).

Step 3: Check for network issues. Ping the server. Check for any protocol error. Get the help of networking people in resolving these errors.

Step 4: Firewall error. There is a setting in the firewall namely, connection timeout, look for the value in this setting. Change to higher valueand check. One way to find whether the error is due to firewall setting is open a telnet session simultaneously and leave it idle till your report runs or returns error. Then try to issue any command in the telnet if the session hangs and you are not able to type the command then the error is due to the firewall setting.

Step 5: If none of the above step works. Contact Oracle Support.