Monday, March 21, 2016

ORA-00119 & ORA-00132

Scenario: Bouncing of database failed with following errors 

 [admind@tnc63 goldengate]$ . oraenv
ORACLE_SID = [admin] ? admin
The Oracle base remains unchanged with value /u01/app/admind
[admind@tnc63 goldengate]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 21 21:33:01 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'tnc63.sfdc.sbc.com:1521'
SQL> !

In order to start Database & to fix this issue 
1) commented out local_listener parameter in pfile i.e ORACLE_HOME/dbs location
2) rename server parameter file (spfile) 
3) start database using pfile from step 1

 [admind@tnc63 dbs]$ vi initadmin.ora 
[admind@tnc63 dbs]$ cat initadmin.ora 
admin.__data_transfer_cache_size=0
admin.__db_cache_size=134217728
admin.__java_pool_size=16777216
admin.__large_pool_size=33554432
admin.__oracle_base='/u01/app/admind'#ORACLE_BASE set from environment
admin.__pga_aggregate_target=117440512
admin.__sga_target=989855744
admin.__shared_io_pool_size=0
admin.__shared_pool_size=251658240
admin.__streams_pool_size=0
*.audit_file_dest='/u01/app/admind/admin/admin/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/admind/oradata/ADMIN/controlfile/o1_mf_ccso8s0y_.ctl','/u01/app/admind/fast_recovery_area/ADMIN/controlfile/o1_mf_ccso8s4h_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/admind/oradata'
*.db_domain=''
*.db_name='admin'
*.db_recovery_file_dest='/u01/app/admind/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/admind'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=adminXDB)'
*.enable_pluggable_database=true
*.inmemory_size=524288000
###*.local_listener='tnc63.sfdc.sbc.com:1521'
*.memory_target=1048m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[admind@tnc63 dbs]$ 

listener file & tnsnames entry as follows

[admind@tnc63 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADMIN =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tnc63.sfdc.sbc.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_ADMIN =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/admind/product/12.1.0/dbhome_1)
      (SID_NAME = admin)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/admind/product/12.1.0/dbhome_1)
      (SID_NAME = nspdba)
    )
  )

 tnsnames.ora 
ADMIN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tnc63.sfdc.sbc.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = admin)
    )

  )

[admind@tnc63 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-MAR-2016 22:37:37
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-MAR-2016 21:30:07
Uptime                    0 days 1 hr. 7 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/admind/diag/tnslsnr/tnc63/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tnc63.sfdc.sbc.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tnc63.sfdc.sbc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/admind/admin/admin/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "admin" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "adminXDB" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "nspdba" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
The command completed successfully

 #######

SQL> startup;
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size     2923488 bytes
Variable Size   503317536 bytes
Database Buffers   50331648 bytes
Redo Buffers   13852672 bytes
In-Memory Area   536870912 bytes
Database mounted.
Database opened.


SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string
SQL> alter system set local_listener='ADMIN' scope=both;

SQL> create pfile from spfile;
File created.

SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string ADMIN

SQL> show parameter pfile;
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile     string /u01/app/admind/product/12.1.0
/dbhome_1/dbs/spfileadmin.ora
SQL> 
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

 setting local_listener parameter completed for CDB$ROOT database :) 

SQL> startup;
ORACLE instance started.
Total System Global Area 1107296256 bytes
Fixed Size     2923488 bytes
Variable Size   503317536 bytes
Database Buffers   50331648 bytes
Redo Buffers   13852672 bytes
In-Memory Area   536870912 bytes
Database mounted.
Database opened.
SQL> show parameter local_listener
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string ADMIN


[admind@tnc63 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-MAR-2016 22:58:51
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-MAR-2016 21:30:07
Uptime                    0 days 1 hr. 28 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/admind/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/admind/diag/tnslsnr/tnc63/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tnc63.sfdc.sbc.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tnc63.sfdc.sbc.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/admind/admin/admin/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "admin" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "adminXDB" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
Service "nspdba" has 1 instance(s).
  Instance "admin", status READY, has 1 handler(s) for this service...
The command completed successfully

 --Nikhil Tatineni--
--Database Error--

By -

Querys to monitor RAC

following few  Query's will help to find out culprits-  Query to check long running transaction from last 8 hours  Col Sid Fo...

  • Skip transaction GoldenGate replicat 12c
    syntax for skipping transaction # ggsci> start replicat skiptransaction  GGSCI (red.gg.ora.com) 14> info all Program     St...
  • ORA-24324, ORA-24323, ORA-01090
    scenario # trying to startup / shutdown database encountered with following error # ORA-24324: service handle not initialized ORA-243...
  • ERROR OGG-01454
    Today GoldenGate Extract abended on Servers During my initial investigation,  goldengate error report says as follows  2016-02...