Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Admin’ Category

« Previous Entries

How to resolve ORA-00490 error?

Posted by Pavan DBA on October 17, 2011

Yesterday in one post (http://pavandba.com/2011/10/16/which-process-starts-first-when-instance-is-started/) I explained about PSP0 process which is introduced in 10g

Sometimes all of a sudden our Instance will be crashed and when looked at alert log file we found below error message

ORA-00490: PSP process terminated with error

While PSP process itself terminated due to any error the whole instance is crashed with ORA-00490 error message.

On further check you may found one more error also in the alert log file which describes as

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

When operating system is encountering with some unknown error like insufficient space in temp Area or swap Area or insufficient system resources then Oracle throws above errors

Same time PMON is terminating instance with following error with process id of PMON process. Because Oracle processes are being unmanageable of Oracle database instance.

PMON: terminating instance due to error 490
Instance terminated by PMON, pid = 20094

Root Cause:

This error will occur when there is no free space available in swap area of System for spawning new process of Oracle. Due to this reason Process SPwaner process PSP0 (with ORA-00490 error code of Oracle) terminated because it doesn’t able to manage or create Oracle processes. Result is Oracle instance crashed by PMON process with errorstack 490 (which is pointing out ORA-00490). If lack of system resource found then also same situation can be occurring.

Solution :

There are 2 solutions for this problem which are mentioned below

1. Check your swap space and increase swap area in system. Because due to lack of space in swap are Oracle unable to create new process and PSP0 Process SPwaner is unable to manage Oracle process.

2. Check “ulimit” setting for Oracle. “ulimit” is for user shell limitation. If maximum shell limit is reached then also PSP0 process becomes unstable to manage other Oracle processes.Increase the “ulimit” setting for Oracle user.

Note : Hope this helps and Please share this post to your friends in case you feel its informative………..

Posted in Admin | Tagged: , , , , , , , , , , , , | 10 Comments »

which process starts first when instance is started?

Posted by Pavan DBA on October 16, 2011

Long time back, I asked this question in my POLL and many of the DBA enthus choosed the option of SMON

But unfortunately, this is not the right answer (when considering 10g)

The first process that will be started when we start instance is PSP process. This is called PROCESS SPAWNER.  This process is introduced in 10g and is responsible for creating and managing other oracle backgroung processes.

As the name specifies, this process can spawn so that you will see the process name as PSP0 in alert log file.

More about PSP0 will be followed in my next article…..

Note : Please put your comments and also share this article by clicking share button below so as to spread the DBA knowledge

Posted in Admin | Tagged: , , | 3 Comments »

materialized view refresh is slow…what to do?

Posted by Pavan DBA on September 8, 2010

Many a times i had seen my DBA friends asking this question. There could be lot of reasons whenever something is slow….but in case of materialized view, we can use below checks to get confirmed where is the problem

Troubleshoot the problem in following steps

1) check the network connectivity using ping command. you should able to see no time gap between packets transfer

2) check if tnsping command is taking time

3) check the size of MV log. it should be truncated after every refresh

Note: It may be a surprise for many people if i say MV log will get truncated after every refresh. But this is the actual thing that happens. how oracle will manage refresh in such case…lets discuss in another post

4) check the size of original table. if MV log size is more than original table, then its clear that problem is with MV log

The following is the reason for increment in size of MV log than table

1) Additional MV’s are created on the same tables, but had stopped refreshing now.
2) If the snapshot becomes invalid or lost and was not dropped formally.

Solution:
The below would be quite good solutions to apply
1) Drop the MV which are not using from long time.

2) drop the materialized view log and re-create (but this will require a complete refresh and the table will not be accessible during this refresh to the users)

again, can we drop MV log alone without dropping MV? answer is YES and below is sample command for the same

DROP MATERIALIZED VIEW LOG ON VLRAPP.CL_ACC_HOLD_TRANS;

You can create MV log again using below command 

CREATE MATERIALIZED VIEW LOG ON “VLRAPP”.”CL_ACC_HOLD_TRANS”
 PCTFREE 60 PCTUSED 30 INITRANS 1 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE “DATA”
  WITH PRIMARY KEY EXCLUDING NEW VALUES;

For commands, please use DBMS_METADATA.GET_DDL package (for syntax, just google it)

as said, there could be some other reasons tooo….but this post may give a glance on what to verify initially

Posted in Admin | Tagged: , , | 1 Comment »

Resolving ora-01031 while configuring EM 10g

Posted by Pavan DBA on August 24, 2010

EMCTL is one service which we never know whether it will run fine or not…. :-)

Just kidding, here is one such problem with Enterprise Manager configuration and its solution

Problem:

When trying to configure EM for 10g or trying to drop repository or trying to recreate repository you may get below error
[oracle@issgascop218 ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Aug 24, 2010 7:51:44 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: PASQ
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Aug 24, 2010 7:51:54 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log.
Aug 24, 2010 7:51:55 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig stopDBMSJobs
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
WARNING: Unable to remove DBMS jobs.
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) …
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /opt/oracle/product/10.2.0/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issgascop218)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PASQ.issgascop218.global.iss.biz))) -repos_user SYSMAN -action drop -verbose -output_file /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_repos_drop_2010-08-24_07-51-57-AM.log
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error dropping the repository
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_repos_drop_<date>.log for more details.
Aug 24, 2010 7:51:57 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error dropping the repository
Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log for more details.
Could not complete the configuration. Refer to the log file at /opt/oracle/product/10.2.0/cfgtoollogs/emca/PASQ/emca_2010-08-24_07-51-44-AM.log for more details.
When i opened log file, i found below information…

[24-08-2010 07:51:57] Enter SYS user’s password :
[24-08-2010 07:51:57]
[24-08-2010 07:51:57] Enter repository user password :
[24-08-2010 07:51:57]
[24-08-2010 07:51:57] Getting temporary tablespace from database…
[24-08-2010 07:51:57] Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issgascop218)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PASQ.issgascop218.global.iss.biz))): ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin)

One of possible reasons for ORA-01031 is OS user is not added to either oinstall or dba group. But in my case, its there.

After a search, i found that the reason is lack of PASSWORD FILE. Then i created password file which solved the issue

[oracle@issgascop218 ~]$ cd $ORACLE_HOME/dbs
[oracle@issgascop218 dbs]$ orapwd file=orapw$ORACLE_SID password=oracle entries=1 force=y
[oracle@issgascop218 dbs]$ ls -ltr
total 16096
-rw-r—–  1 oracle dba    8385 Sep 11  1998 init.ora
-rw-r—–  1 oracle dba   12920 May  3  2001 initdw.ora
-rw-rw—-  1 oracle dba      24 Mar 22  2007 lkPAS
-rw-rw—-  1 oracle dba    1552 Mar 22  2007 hc_PAS.dat
-rw-rw—-  1 oracle dba    1552 May  9  2007 hc_PASQ.dat
-rw-rw—-  1 oracle dba      24 May  9  2007 lkPASQ
-rw-r—–  1 oracle dba    2430 May  9  2007 initPASQ.ora
-rw-r—–  1 oracle dba    2560 Dec 28  2007 spfilePAS.oraold
-rw-r—–  1 oracle dba    2393 Dec 28  2007 initPAS.ora
-rw-r—–  1 oracle dba    1536 Apr 30 11:54 orapwPAS
-rw-r—–  1 oracle dba    2560 Jul  1 22:00 spfilePAS.ora
-rw-r—–  1 oracle dba    3584 Jul 12 22:00 spfilePASQ.ora
-rw-r—–  1 oracle dba 7716864 Aug 23 18:30 snapcf_PASQ.f
-rw-r—–  1 oracle dba 8601600 Aug 23 18:57 snapcf_PAS.f
-rw-r—–  1 oracle dba    1536 Aug 24 07:54 orapwPASQ

[oracle@issgascop218 dbs]$ emca -config dbcontrol db -repos recreate

Last few lines of above command

INFO: Repository successfully created
Aug 24, 2010 8:01:56 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Aug 24, 2010 8:03:32 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 24, 2010 8:03:33 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://issgascop218:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
So, whenever you get ora-01031 while performing some action on EM, plz do check if this solution works out

Posted in Enterprise Manager | Tagged: , | 2 Comments »

All about DUAL table

Posted by Pavan DBA on August 19, 2010

Friends, many of us know about DUAL and its usage. Lets learn few things about it…

1) can we drop a dual table?

Ans: Yes. but it will have serious impact on the database functionality. so you should never do that

2) Can we create DUAL table if dropped?

Ans: Yes. use the following steps for the same…

SQL> DROP TABLE SYS.DUAL ;
 
Table dropped.
 
SQL> CREATE TABLE SYS.DUAL
  2  (
  3    DUMMY  VARCHAR2(1 BYTE)
  4  )
  5  TABLESPACE SYSTEM;
 
Table created.
 
SQL> CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
 
Synonym created.
 
SQL> GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;
 
Grant succeeded.
 
SQL> INSERT INTO dual VALUES (‘X’);
 
1 row created.
 
SQL> SELECT * FROM dual;
 
D                                                                              
-                                                                              
X        

3) Can i create my own DUAL table which is having same functionality?

Ans: Yes. use below script which will create MYDUAL as another DUAL table.

CREATE OR REPLACE PROCEDURE replace_mydual (
 table_name_in IN VARCHAR2
)
IS
BEGIN
 BEGIN
    EXECUTE IMMEDIATE ‘DROP TABLE ‘ || table_name_in;
 EXCEPTION
    WHEN OTHERS THEN NULL;
 END;

 EXECUTE IMMEDIATE ‘CREATE TABLE ‘
                   || table_name_in
                   || ‘ (dummy VARCHAR2(1))’;

 EXECUTE IMMEDIATE
    ‘CREATE OR REPLACE TRIGGER mydual_’ || table_name_in ||
    ‘  BEFORE INSERT
       ON ‘ || table_name_in || ‘
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
       l_count PLS_INTEGER;
    BEGIN
       SELECT COUNT (*)
       INTO   l_count
       FROM   ‘ || table_name_in || ‘;

       IF l_count = 1
       THEN
          raise_application_error
          ( -20000
          , ”The ‘ || table_name_in || ‘ table can only have one row.” );
       END IF;
    END;’;

 EXECUTE IMMEDIATE ‘BEGIN INSERT INTO ‘
                   || table_name_in
                   || ‘ VALUES (”X”); COMMIT; END;’;

 EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘
                   || table_name_in
                   || ‘ TO PUBLIC’;

 EXECUTE IMMEDIATE ‘CREATE PUBLIC SYNONYM ‘
                   || table_name_in
                   || ‘ FOR ‘
                   || table_name_in;

 EXECUTE IMMEDIATE
    ‘CREATE OR REPLACE FUNCTION next_pky (seq_in IN VARCHAR2)
        RETURN PLS_INTEGER AUTHID CURRENT_USER
     IS
        retval PLS_INTEGER;
     BEGIN
        EXECUTE IMMEDIATE ”SELECT ” || seq_in
                      || ”.NEXTVAL FROM ‘ || table_name_in ||
                      ‘|| ”INTO retval;
        RETURN retval;
     END next_pky;’;

END replace_mydual;

TOM KYTE explained about this in one of his articles and as always its best…

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

some more interesting part, why DUAL?
http://radiofreetooting.blogspot.com/2006/12/why-dual.html

All above ask your friend GOOGLE about this, lot of results will hit your door

HAPPY LEARNING…

Posted in Admin | Tagged: , , | Leave a Comment »

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.

Join 1,272 other followers