Control File Error while starting Database

While starting one of database, i faced the below error:

ORA-00202: control file: ‘/u09/oradata/MYTEST/TEST1/control02.ctl’
ORA-27086: unable to lock file – already in use
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 8
Mon Dec 19 10:29:03 2011
ORA-205 signalled during: ALTER DATABASE MOUNT…

My first thought is that some oracle process is still using the controlfile even after the successful shutdown immediate. Since, only one database was running on this server, so i simply checked for processes running from OS user owining database. In this case OS user “oracle” owns the database.

/home/oracle => ps -efgrep oracle

No process comes from above command which is related to database and could have locks on control file.

So, next step is to involve AIX administrator. Since, /u09 is NFS mounted file system, i asked AIX Admin to check for NFS service and Bingooo…..

To resolve this error, one should clear the NFS Demon process from OS, if the mount points having controlfile are NFS mounted.

New 10g Background Process

MMAN:

It acts as the coordinator for sizing the SGA memory components. It keeps track
of the size of components and any resizing needs.

CTWR:

It record the changing blocks of database so that at time of incremental backup
full datafile need not to be scanned for changed blocks.

RBAL:

It checks the rebalacing of disks controlled by ASM.


ARBALx:

This process does the actual reblalacing of ASM disks and ASM_POWER_LIMIT parameter
tells how many slave process will be used for rebalacing.


ASMB:

This is used to communicate with cluster synchronization service used by ASM.


CJQn:

This is job queue monitoring process which is started by parameter JOB_QUEUE_PROCESSES.


MMON:

This process for Automatic Workload Repository.

ORA-02800 when use_current_session=False in DBMS_SCHEDULER.RUN_JOB

You have written following block to schedule a job in 10g

BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'ins_test',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN insert into test values(to_char(sysdate,''hh24:mi:ss'')); commit; END;',
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');
END;

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_job_definition',
program_name => 'ins_test',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byminute=1',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined by existing program and inline schedule.');
END;

When you try to run the job :

BEGIN
-- Run job asynchronously.
DBMS_SCHEDULER.run_job (job_name => 'test_prog_job_definition',
use_current_session => TRUE);
END;

You got the error:

ORA-6512: at line 1
ERROR at line 1: ORA-2800: Requests timed out
ORA-6512: at "SYS.DBMS_ISCHED", line 150
ORA-6512: at "SYS.DBMS_SCHEDULER", line 441

This is because the timezone patches are not installed on database server.
Please verify that you have installed the correct patched.

ORA-28575: unable to open RPC connection to external procedure agent

When you are getting "ORA-28575: unable to open RPC connection to externa
procedure agent" error when using external procedure call in your PL/SQL code,
please check that you have following entries (in bold & italics) in your listener.ora
file and tnsnames.ora file:

Listener.ora

LISTENER_L1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCL1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = 172.10.1.1)(PORT = 1526))
)
)
)
SID_LIST_LISTENER_L1 =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /software/ora9)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /software/ora9/)
(SID_NAME = testdb)
)
)

Tnsnames.ora

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCL1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORA-03136: Inbound Connection Time Out (10g only)

When you got ORA-03136: Inbound Connection Time Out (10g only) and listener is already running then shutdown the listener and set the following parameters in listener.ora

INBOUND_CONNECT_TIMEOUT_=0
SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF

Start the listener again.

Convert Number (Digits) To Word (Strings)

There are many times when we need to spell out the amount in number to words. E.g. In banking/billing you need to convert amount 450 to "Four Hundred and Fifty" for display. This can be very lengthy task if you write a procedure to achieve this functionality. Here is a simple query (for ORACLE only) that will simplyfy your task. Suppose you want to convert 873 to words for displaying in your report.

SQL> select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

CONVERTED_FORM
---------------------------
EIGHT HUNDRED SEVENTY-THREE

J represents the julian format. When the to_char function is applied to the result of to_date, it spells (SP) the word for the number passwd to to_date.

To convert the decimal number simply follow the trick:

SQL> SELECT TO_CHAR(TO_DATE(trunc(100.50),'J'),'JSP') ' Point ' TO_CHAR(TO _DATE( to_number(substr('100.50', instr('100.50','.') + 1)),'J'),'JSP') from dual;