In Oracle 10g/11g, LOCAL_LISTENER is the parameter for specifying a listener and used for dynamic instance registration. This listener enables the listener to connect client to the correct instance. According to the Oracle documents, the default value of LOCAL_LISTENER is (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=yourhostname)(PORT=1521))'

The common causes of this error during the database startup is Oracle cannot find your in the hosts file, probably because you have changed the hostname of the server recently or you have set the  LOCAL_LISTENER incorrectly

If the error is caused by changed hostname, then you need to check the hosts file, that is, /etc/hosts in Uinx platform or \System32\drivers\etc\hosts make sure that you have an entry like this:

xxx.xxx.xxx.xxx  yourhostname

For incorrect LOCAL_LISTENER, you should modify the parameter LOCAL_LISTENER in the init.ora file to appropriate one or set it to null or ''.

I have some scripts which monitor the health of the Oracle and its OS( mainly Unix platforms).
Yesterday, I saw one of my scripts that gathers IO of datafile cannot get a correct output in an Oracle 10g RAC. The cluster of the cluster on machine RHEL 5.3 operating system and everything seems quite normal, not error was found in alert log.

When we query the dynamic view v$filestat, we will find the value of of the columns are wrong. The WRITETIM column of all the datafiles is zero. The value of the column is incorrect as we have set timed_statistics to true in our database and the phywrts for the datafile are non-zero, it is not possible the physical write operations takes "zero" seconds to complete.

SQL> select file#,phyrds,phywrts,readtim,writetim from v$filestat;

     FILE#     PHYRDS    PHYWRTS    READTIM   WRITETIM
---------- ---------- ---------- ---------- ----------
         1      20769      12432       2111          0
         2        141      42192          6          0
         3      47766     195797       4704          0
         4        328        238          0          0
         5     120860      56738       8249          0
         6          8          6          0          0
         7     121177      21454       8590          0
         8        902       3285         62          0
         9       8922      26226        587          0
        10    4095646     744810        584          0

10 rows selected.

In fact, this problem caused by the Bug 9622663 and related Bug 7045446. Based on the bug notes from metalink, these two bugs only happen in Oracle 10203 when datafiles are stored in ASM. However it appears Oracle 10204 is also affected. It seems this bug is not fixed until 11gR2 --the latest version of Oracle release.

There is a patch to fix the Bug 9622663, however, the patch is only available in HP Unix and IBM AIX. I am going to raise a patch request for Linux X86-64 platform and hope Oracle development team will hear me.

When running Oracle 10g dataguard, we may find the ORA-00322 error appeared in alert log file from time to time.
This error is caused by the Oracle Bug 5238386 - ORA-322 possible reading standby redo log header and the problem will not hinder the functions of Oracle as long as it does not occur too frequently.

Errors in file /u01/app/oracle/admin/ORCLSB/bdump/orcsb_mrp0_17795.trc:
ORA-00322: log 6 of thread 1 is not current copy
ORA-00312: online log 6 thread 1: '/u03/oradata/ORCLSB/redo01d.log'
ORA-00322: log 6 of thread 1 is not current copy
ORA-00312: online log 6 thread 1: '/u02/oradata/ORCLSB/redo01c.log'
Mon May 14 11:27:15 2009
Errors in file /u01/app/oracle/admin/ORCLSB/bdump/orcsb_mrp0_17795.trc:
ORA-00322: log 6 of thread 1 is not current copy
ORA-00312: online log 6 thread 1: '/u03/oradata/ORCLSB/redo01d.log'
ORA-00322: log 6 of thread 1 is not current copy
ORA-00312: online log 6 thread 1: '/u02/oradata/ORCLSB/redo01c.log'
Mon May 14 11:27:20 2009

However, if the Oracle appears to be unable to apply log in the standby database due to ORA-00322 error. You may consdier to fix the problem
by using one of the following methods

Fix
----
1. Apply the fix for the Bug 5238386.
2. Upgrade the database to the version 10.2.0.4 where the issue has already been fixed.
3. Drop the log group and recreate one
alter database recover managed standby database CANCEL;
alter database drop logfile group 6;
alter database add standby logfile group 6
('/u02/oradata/ORCLSB/redo01c.log',
'/u03/oradata/ORCLSB/redo01d.log') size 100m

References
-----------
Note 294431.1 : ORA-00322 seen in standby alert log when creating a archive log file using SRL.

Scenario
We want to create stored procedure which the table name as parameter and build an empty table.

CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR)
IS
sql_stmt VARCHAR(2000);
BEGIN
sql_stmt := 'create table :t nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;

EXECUTE IMMEDIATE sql_stmt
USING Table_Name;
END;

Problem
The above stored procedure is compiled succesfully. However, when we try to run the stored proc build_table,

SQL> exec build_table('tableA');
we got the following error

ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at “SYSTEM.BUILD_TABLE”, line 14
ORA-06512: at line 1

Solution
After a brief search in google,we found that the problem here is…we cannot use bind variables for table name
And here is an excerpt from Oracle Pl/SQL programming:
“You can bind into your SQL statement only those expressions(literals, variables, complex expressions) that replace placeholders for data values inside the dynamic string. you cannot bind in the names of schema elements(tables, columns, etc) or entrie chunks of the SQL statement. For those parts of your string, you must use concatenation(operator)”

OK then, we change our dynamic statement using concatenation as below and the problem is solved.

CREATE OR REPLACE PROCEDURE Build_Table
(Table_Name IN VARCHAR)
IS
sql_stmt VARCHAR(2000);
BEGIN
sql_stmt := 'create table '
||Table_Name
||' nologging
as select rownum id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from all_objects a where 1=0′;

EXECUTE IMMEDIATE sql_stmt;
END;