huge ons.log size in Oracle 10g AS Infra

Introduction:
We had an issue of huge size of ons.log file under Oracle 10g AS R2 infra tier, and it was making the 1.4 GB ons.log files and in days it was filling more then 20GB file system space.

Troubleshooting:
When we checked the ons.log file, it was having the following errors, and these errors were keep coming in the ons.log files.

--------------------
09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor09/06/08 12:56:48 [4] Local connection 0,127.0.0.1,6101 missing form factor
--------------------

In this test machien we had the following Oracle products installed.

1- Oracle 10g R2 Database
2- Oracle 10g AS R2 (infra and middle tier)
3- Oracle 10g AS R3 (SOA)

We raised a SR with Oracle support, and they came up that there is port conflict between one of the ons of above Oracle Products.

Resolution:

We checed the opmn/conf files from all the Oracle products, and found the port conflict between Oracle 10g R2 Database ons and Oracle 10g AS R2 infra ons.

There were following options to be applied.

1- The TNS listener should be configured so it would not let ons subscribe to it. (this option is for Oracle 10g R2 Database listener.) Following settings were recommended by Oracle support.

Following needs to be added in the listener.ora file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

2- rename the ons.config file for the Oracle 10g R2 Database, so that ons should not be started and try to be subscribed to listener.

3- the last but not the least option would be to change the conflicted ports in the ons.config file of Oracle 10g R2 Database.

we changed the ports in ons.config file, but first we made sure that new ports should not be used in the system, we did this by checking netstat -rn command on Solaris box. After resetting the ports we stopped the ons, listener and restarted the ons and listener and every thing was fine.

Database crashed in manual hotbackup mode...

Introduction:

One of our test database crashed, while it was in manual hotbackup mode. And when we tried to open the database, it didnt comeup as expected, becuase all the datafiles were in backup mode, and needed recovery. We were doing the manual hotbackup becuase of some limitations of the environment, where we were not able to use RMAN.

Troubleshooting and Resolution:

Before doing a blind recovery, we checked the alert log of the database, and searched theh v$backup view, to see which files were in backup mode. And then we initiated the recovery.

1- We checked the alert log file and found the following entries.

Thu Mar 16 04:02:57 2009
ARC0: Evaluating archive log 3 thread 1 sequence 52
ARC0: Beginning to archive log 3 thread 1 sequence 52
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s52.arc'
ARC0: Completed archiving log 3 thread 1 sequence 52
Thu Mar 16 04:03:46 2009
Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 17937
Thu Mar 16 04:04:04 2009

2- When database was started again, it gave the following messages.

Completed: ALTER DATABASE MOUNT
Thu Mar 16 04:04:49 2009
alter database open
Thu Mar 16 04:04:49 2009
ORA-1113 signalled during: alter database open...
Thu Apr 16 04:05:24 2009


3- When we checked the v$backup view, 7 files were in backup mode, so we had to recover these 7 datafiles.

SQL> l
1 select * from v$backup
2* where status != 'NOT ACTIVE'
SQL>
SQL> /

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
18 ACTIVE 7.2622E+12 15-MAR-09
19 ACTIVE 7.2622E+12 15-MAR-09
37 ACTIVE 7.2622E+12 15-MAR-09
49 ACTIVE 7.2622E+12 15-MAR-09
51 ACTIVE 7.2622E+12 15-MAR-09
60 ACTIVE 7.2622E+12 15-MAR-09
75 ACTIVE 7.2622E+12 15-MAR-09

7 rows selected.


4- Then we started database recovery, having put database in autorecovery mode. Following is the log what we did, we recovered all the datafiles, which was in backup mode, and we got that information from v$backup view.

ALTER DATABASE RECOVER datafile 18
Thu Mar 16 04:05:24 2009
Media Recovery Datafile: 16
Media Recovery Start
Starting datafile 16 recovery in thread 1 sequence 45
Datafile 16: '/u02/oradata/TEST/test_idx_01.dbf'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 16 ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s45.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s46.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s47.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s48.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s49.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s50.arc
Recovery of Online Redo Log: Thread 1 Group 2 Seq 51 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Media Recovery Complete
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Thu Mar 16 04:05:49 2009
alter database open
Thu Mar 16 04:05:49 2009
Beginning crash recovery of 1 threads
Thu Mar 16 04:05:49 2009
Started first pass scan
Thu Mar 16 04:05:49 2009
Completed first pass scan
136 redo blocks read, 20 data blocks need recovery
Thu Mar 16 04:05:49 2009
Started recovery at
Thread 1: logseq 53, block 2, scn 1690.3567247729
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Thu Mar 16 04:05:49 2009
Ended recovery at
Thread 1: logseq 53, block 138, scn 1690.3567267893
20 data blocks read, 20 data blocks written, 136 redo blocks read
Crash recovery completed successfully
Thu Mar 16 04:05:49 2009
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 54
Thread 1 opened at log sequence 54
Current log# 2 seq# 54 mem# 0: /u02/oradata/TEST/redo02.log
Successful open of redo thread 1.
Thu Mar 16 04:05:49 2009
ARC0: Evaluating archive log 1 thread 1 sequence 53
ARC0: Beginning to archive log 1 thread 1 sequence 53
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s53.arc'
Thu Mar 16 04:05:49 2009
SMON: enabling cache recovery
Thu Mar 16 04:05:49 2009
ARC0: Completed archiving log 1 thread 1 sequence 53
Thu Mar 16 04:05:50 2009
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Mar 16 04:05:50 2009
SMON: enabling tx recovery
Thu Mar 16 04:05:50 2009
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

How to insert & as a column value in a table

Introduction:



This post is very simple, but usefull, as when I tried to find this thing on google, I couldnt get any page, may be I didnt search properly, but I wanted to share how we can insert & as a column value. This post is very useful if you are inserting thousands of records and all have & in one of the column values. As if you will try to run in SQL*PLUS, it would be asking you the value for a variable preceeding &.



Reolution:



We did differnet testings, and finally was able to insert the & as a column value, following are the testings and results.



1- created an empty table with one field as text varchar2(60).



test-sql>create table testtable (text varchar2(60));
Table created.



2- different tries to insert the &, last one is successfull.



a- test-sql>insert into testtable values(' this is & record');
Enter value for record: &
old 1: insert into testtable values(' this is & record')
new 1: insert into testtable values(' this is &')

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &



b- test-sql>insert into testtable values(' this is '&' record');
insert into testtable values(' this is '&' record')
*
ERROR at line 1:
ORA-00917: missing comma



c- test-sql>insert into testtable values(' this is "&" record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record



d- test-sql>insert into testtable values(' this is '''&''' record');
insert into testtable values(' this is '''&''' record')
*
ERROR at line 1:
ORA-00917: missing comma



e- test-sql> insert into testtable values (' this is ' '&' ' record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record
this is & record

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

Introduction:

We had issue with Listener connectivity from client. Even the Listener was running, but it was throwing the following error message.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connectdescriptor

Troubleshooting:

1- We checked with the tnsping utility from client side, and server side, and it was all working, it was displaying the following message.

TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 20-APR-20
09 10:02:32

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
D:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TESTSERVER)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_N
AME = TESTDB)))
OK (20 msec)

2- When we checked through sql*plus it was showing the following message.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB
Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

3- We checked the local_listener parameter in the database and it was not set to any of the value.

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

4- Here are the listener.ora and tnsnames.ora files. The important point to note here is the port number was 1528

LISTNER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u02/oracle/products/9.2.0)
(SID_NAME = TESTDB)
)
)

TNSNAMES.ORA

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)
Resolution:

1- We set the LOCAL_LISTENER parameter to service name "TESTDB" and checked and it was all working.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB_ora
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>

2- Following was the value of LOCAL_LISTENER parameter

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string TESTDB

Inactive - No Concurrent Manager after running autoconfig

Introduction:

Due to IP change in one of our APPS testing environment, we ran autoconfig on all the application tier and database tier, but after the successful running of autoconfig, when we checked the concurrent manager by submitting "Active Users" request, it was showing "Inactive - No Concurrent Manager" error.





Troubleshooting:

Following are the steps which we did.



We shutdown all the application tier services web , forms , concurrent manager and again restarted, but it was showing the same error, "Inactive No Manager" for submission of "Active Users" request. see the screen shot.

We checked the log files for concurrent manager, and we found following error messages in ICM log files.

---------------------------------------------------------------------------Application Object Library: Concurrent Processing version 11.5
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
Internal Concurrent Manager started : 04-MAR-2009 09:44:58
---------------------------------------------------------------------------Spawned Process 8573Could not contact Service Manager FNDSM_TESTSERVER_uat. The TNS alias could not be located, the listener process on TESTSERVER could not be contacted, or the listener failed to spawn the Service Manager process.Spawned Process 8574
Process monitor session started : 04-MAR-2009 09:44:59---------------------

Resolution:

We searched on metalink site, and found following note 392246.1helpful.

Requests Stuck with Inactive No Manager - Routine AFPESA Cannot Construct the Name of an Executable File

When we checked the env files in $APPL_TOP/admin, we found that the Custom Top were not defined in that file. We updated the env file with the custom top and restarted the Concurrent manager and it was working fine.

Concurrent Manager Request error

Introduction:

We got Concurrent Manager Request error for one of our List Generation request. When we checked the log file (by clicking on view log button),

we got the following message

Troubleshooting and Resolution:

We logged in with the "sysadmin" user and searched the required List Generation request.

And then clicked Diagnostics button, which showed the following message.

When we checked the executable, it was not on the path mentioned. It was a symbolic link to the directoy which was of produciton, as it was the cloned system. We remove the symbolic link, and recreated with the correct path. And this List Generation request worked fine.

ORA-27211: Failed to load Media Management Library

Introduction:
We had interesting but painfull issue for Oracle backups which are done with Veritas Netbackup. There was activity of patching on Netbackup. And after the patching there was issues with Oracle Backups. All the Oracle backups were failing with following error messages....

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Troubleshooting:

We tried different steps to resolve the error. Following are the steps which we did.

1- Checked with the "sbttest" command, to see whether Netbackup is doing successfull test backups. and we got the following errors.

$>sbttest test

The sbt function pointers are loaded from oracle.static library.libobk.so could not be loaded. Check that it is installed.

2- We tested with simple rman commands to allocate a tape channel and the release. and following was the error messages.

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:40:43
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

3- The above error messages were showing that MML is not able to load, and that libobk.so was being used as static from Oracle binaries.

We explicitly used the Netbackup /usr/openv/netbackup/bin/libobk.so64.1, inside the RMAN script, but we got the same error as of step 2. Following were the error messages

RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1';
3> release channel ch1;
4> }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 02/25/2009 13:41:11
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

The above all steps showed that there was strange kind of problem with Netbackup. As we were not able to allocate one channel with "sbt_tape" type, and all the times MML was not able to be loaded.

We also check in the udump of the Oracle Database server, and following were the error messages in udump trace file.

*** SESSION ID:(47.58746) 2009-02-25 13:40:43.872
SKGFQ OSD: Error in function sbtinit on line 2384
SKGFQ OSD: Look for SBT Trace messages in file /usr/oracle/home/oracle/admin/ORA/u
dump1/sbtio.log
SBT Initialize failed for oracle.static
*** 2009-02-25 13:41:11.006
Failed to load SBT library /usr/openv/netbackup/bin/libobk.so64.1

The above error messages of udump trace file, was clearly showing that the libobk.so64.1 was not able to load. And also we found valid symbolic link from $ORALCE_HOME/lib/libobk.so to /usr/openv/netbackup/bin/libobk.so64.1

Resolution:

After trying every way, finally our system admin did one thing and got resolved the issue. What he did, he renamed the /usr/openv/netbackup directory and again pushed the netbackup client from Netbackup Server, and we again tested the Oracle Backups and it was all working. Following is the simple allocation of channel of 'sbt_tape'



RMAN> run {
2> allocate channel ch1 type 'SBT_TAPE';
3> release channel ch1;
4> }

using target database controlfile instead of recovery catalog
allocated channel: ch1
channel ch1: sid=35 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 6.0 (2005090703)

released channel: ch1

RMAN>