Extending Tablespace on ASM

Today after checking the Daily Health Check Report I found that INDX tablespace is running out of space

Megs Megs Megs Megs Megs
Tablespace Max Size Allocated Used Used % Free %

INDX 6,144 5,632 5,543.50 90.23 9.77

Now to resolve the Issue first we need to check where the datafiles are located and what is the uniform size of the datafiles which customer is using.

We can use the following query for the same

select FILE_NAME,BYTES,STATUS,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='INDX';

Now we can extend the Tablespace by using the following query

ALTER TABLESPACE "INDX" ADD DATAFILE '+DATA/prod_sa/datafile/indx.004' SIZE 1946157056 autoextend ON;


Here the datafiles are located on ASM.

Cheers
Aashish

ORA-16014: log 6 sequence# 8614 not archived, no available destinations

Last night one of my team member gave me a call that we have got a space issue on one of our servers. I requested him to delete some old files and then he managed to free up some space.

The next day during the generation of Health Check Report we came across the following error in Alert Log:

ORA-16014: log 6 sequence# 8614 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/opt/oracle/oradata/cdqa/redo06a.log'
ORA-00312: online log 6 thread 1: '/opt/oracle/oradata/cdqa/redo06b.log'

The Archiving processes was automated and we just need to set the log archive dest parameter again so as to point out to the correct location.

We realized that Automated Archiving is not happening properly and to correct the issue did the following :


alter system set log_archive_dest_1="location=/opt/oracle/oradata/cdqa reopen=60";

Here reopen tells the Archiving process to try reopening the failed Archive Destinations after 60 seconds.

Cheers
Aashish

ORA-01102: cannot mount database in exclusive mode

After checking in $ORACLE_HOME/dbs I found that it contains lk file, which is used for locking shared memory.I deleted this file lkdev and tried starting my database again.

But to my surprise it's now giving me following error
ORA-00205:error in identifying control file, check alert log for more info
I checked the Alert Log file which gave me the hint to correct the issue

It means the issue is coming due to one of the following

1. Before I started the database, there were some Oracle processes present in teh system and i need to get rid of them

we can use the following to correct this

$ps -ef| grep ora_

this will give me the list of processes which i can kill using OS command , passing pid as argument

$kill -9 631

or we can use the following command to kill all the oracle processes on the system " but make sure there are no other instances" as it will kill all oracle processes

$ ps -fu oraim | grep ora_ | awk '{print "kill -9 " $2}' | ksh

where oraim is my oracle database owner.

2.Existence od shared Memeory Segments or Semaphores in the Operating System, Although Oracle Instance is already down

we can easily find that out and remove them too usign following comands

$ipcs -b -- This is to find Shared Memory Segments and Semaphores

Remove Shared Memeory Segments

$ipcrm -m

Remove Semaphore

$ipcrm -s Once this is done , I tried to start the database once again and this time it started without any issues.

Cheers
Aashish

EMCA Fails with ORA-01034: ORACLE not available

Hi ,

When upgrading my Database from Oracle 10.2.0.1.0 to 10.2.0.3.0, after running Database Upgrade Assistant, I received the message that Database Control is not configured.


After searching a lot and trying many things which did not worked. I got the solution to this issue.The main reason for this issue is the database has not been cleaned up, need to do some cleaning activity in the database to get it working.

Solution:
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management
objects:

SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Run the following $emca -deconfig dbcontrol db -repos drop
Run the following $emca -config dbcontrol db -repos create

After doing the following tasks it started without any issues.

Reference:
Metalink Note: 560666.1 Dbconsole Creation Fails with an 'ORA-01034: ORACLE not available

Cheers
Aashish

The server encountered an internal error

When we log in into OID DAS if we see some error message like this ( OK is Highlighted in Error Message )


OK

The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, you@your.address and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

I tried to find out what may be the issue and started searching on Metalink and got hold of this note

739676.1
The Server Encountered An Internal Error Or Misconfiguration And Was Unable To Complete Your Request

This note suggest that we should restart opmn processes using the following commands

To implement the solution, please execute the following steps:

1. Stop and restart the mid tier by doing the following:
cd ora_home/opmn/bin
2. Run opmnctl stopall then opmnctl startall

When I gave shutdown command it worked fine

oim$ opmnctl shutdown
opmnctl: shutting down opmn and all managed processes...

but on startup it gave number of errors

oim$ opmnctl startall

opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=oimdb:6200
1 of 4 processes started.

ias-instance id=OAS_Infrastructure.oimdb.sbo.hampton.k12.va.us
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
OC4J/oca/default_island

Error
--> Process (pid=14283)
failed to start a managed process after the maximum retry limit
Log:
/d01/oracle/oasoim/opmn/logs/OC4J~oca~default_island~1

--------------------------------------------------------------------------------
ias-component/process-type/process-set:
OC4J/OC4J_SECURITY/default_island

Error
--> Process (pid=14272)
failed to start a managed process after the maximum retry limit
Log:
/d01/oracle/oasoim/opmn/logs/OC4J~OC4J_SECURITY~default_island~1

--------------------------------------------------------------------------------
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=14256)
failed to start a managed process after the maximum retry limit
Log:
/d01/oracle/oasoim/opmn/logs/HTTP_Server~1

[oimdb:log] oimapp$ opmnctl status

Processes in Instance: OAS_Infrastructure

On checking these log files the what i found was very basic check i should have done earlier

Space issue :(

These log files say that clearly

/d01/oracle/oasoim/opmn/logs/OC4J~oca~default_island~1


09/04/28 05:33:51 Fatal Error: Transaction log file (/d01/oracle/oasoim/j2ee/oca/persistence/oca_default_island_1/transaction.state) did not exist and was not possible to create, the most common reason for this is an invalid path or Orion lacking security to write to that path: No space left on device

/d01/oracle/oasoim/opmn/logs/HTTP_Server~1


/d01/oracle/oasoim/Apache/Apache/bin/apachectl start: execing httpd
Ouch! ap_mm_create(1048576, "/d01/oracle/oasoim/Apache/Apache/logs/mm.14250") failed
Error: MM: mm:core: failed to truncate memory file (No space left on device): OS: No such file or directory

--------
09/04/28 05:33:45 Start process
--------
/d01/oracle/oasoim/Apache/Apache/bin/apachectl start: execing httpd
Ouch! ap_mm_create(1048576, "/d01/oracle/oasoim/Apache/Apache/logs/mm.14256") failed
[oimdb:log] oimapp$



After checking the space on the server I got the confirmation of this as well.

fd 0 0 0 0% /dev/fd
/dev/dsk/c0t0d0s1 8072501 124726 7867050 2% /var
swap 5541816 1461736 4080080 27% /tmp
swap 4080136 56 4080080 1% /var/run
/dev/dsk/c0t0d0s7 99799 1041 88779 2% /state
/dev/dsk/c0t1d0s0 70584033 42845587 27032606 100% /d01
fas-dc-1:/vol/Software
157286400 132940788 24345612 85% /software


My /d01 mount point is 100% full so navigating to the log location on /d01/oracle/oasoim/ldap/odi/log

I removed the log files and Trace files created by Oracle HR Agent and IPlanet Export profile

oimapp$ rm -rf *trc* *aud*

Now again I tried to start opmn and it started successfully and the error was corrected.

Cheers
Aashish

ORA-1654 Error in Alert Log file

Today while running health check report on one of our databases I came across the following error in the alert log file

ORA-1654: unable to extend index PERFSTAT.STATS$SQL_PLAN_USAGE_PK by 2048 in tablespace TOOLS
Tue Jan 27 05:00:09 2009
Errors in file c:\oracle\admin\ssg\udump\ssg_j000_2660.trc:
ORA-12012: error on auto execute of job 161
ORA-01654: unable to extend index PERFSTAT.STATS$SQL_PLAN_USAGE_PK by 2048 in tablespace TOOLS
ORA-06512: at "PERFSTAT.STATSPACK", line 1487
ORA-06512: at "PERFSTAT.STATSPACK", line 2442
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

Looking at this error on can easily guess what went wrong , TOOLS tablespace is full. Now how can we confirm this , here is the query with which you can check if your tablespace is running out of space.

Query to check Tablespace Usage :

set linesize 120
col tablespace_name format a22 word_wrapped heading "Tablespace Name"
col mb_free format a16 heading "Free MB"
col fragments format 99999999 heading "Fragments"
col biggest_bit format a16 heading "Biggest Bit"
col total_mb format a16 heading "Total MB"
SELECT
TSP.TABLESPACE_NAME T_NAME,
TSP.TOTAL_SPACE TOT_SPACE,
FREE.TOTAL_FREE,
ROUND(FREE.TOTAL_FREE /TSP.TOTAL_SPACE*100) PCT_FREE,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE),2) TOT_USED,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE)/TSP.TOTAL_SPACE*100) PCT_USED
FROM
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024/1024 TOTAL_SPACE
FROM
DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) TSP,
(SELECT
TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024,2) TOTAL_FREE,
ROUND(MAX(BYTES)/1024/1024,2) MAX_FREE
FROM
DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT
TABLESPACE_NAME,
ROUND(MAX(NEXT_EXTENT)/1024/1024,2) MAX_NEXT_EXTENT
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) NEXTEXT
WHERE
TSP.TABLESPACE_NAME = FREE.TABLESPACE_NAME (+)
AND TSP.TABLESPACE_NAME = NEXTEXT.TABLESPACE_NAME (+)
AND ((ROUND(FREE.TOTAL_FREE/TSP.TOTAL_SPACE*100)) <> FREE.MAX_FREE);

This will show you the data for all the tablespaces.

Now we need to check which datafile is associated with this tablespace tools

we can run the following query

SQL> select substr(file_name,1,50), tablespace_name from dba_data_files where tablespace_name='TOOLS';


SUBSTR(FILE_NAME,1,50) Tablespace Name
-------------------------------------------------- ----------------------
C:\ORACLE\ORADATA\SSG\TOOLS01.DBF TOOLS

Now we need to extend the datafile associated with this Tablespace.

we can now run the following command to do the same.

alter database datafile 'C:\ORACLE\ORADATA\SSG\TOOLS01.DBF' resize 2600M;

After running this command we can again run the Query to check Tablespace Usage and we should have enough space in Total_Free as shown in image below.


I have altered it to have 600MB more space as 2000 MB TOOLS TS was full so now it's showing those 600MB as free.

Cheers
Aashish

Welcome to my Blog ......

Hi All,

Welcome to my blog ...thanks for taking time out and visiting my blog.

This blog is meant for all those who are beginners and Intermediate Oracle DBA's.

Here we will also checkout how and Oracle DBA follow a path to be an Oracle Applications DBA.

Most important I will post some of the Practical Issues which we face as an Oracle DBA so those who are beginners can learn some practical stuff.

I am working as on Oracle Applications DBA. I get opportunity to work on number of
projects and not only on Oracle Applications but on Oracle Databases,Oracle Middleware and
Oracle Applications.

Cheers
Aashish