Configure the Multiple Listener Port in Oracle DB & Tested in our Env

-

Configure the Multiple Listener Port in Oracle DB & Tested in our Envirnoment

 Configure Port 1521 – Default port

 Configure Port 1522   - Port Added by request from client

 Vi listener.ora
LISTENER1 =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS =
           (PROTOCOL = TCP)
           (HOST = *****)
           (PORT = 1522)
         )
       )
     )
   )
SID_LIST_LISTENER1 =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = prim)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = prim)
     )
  )
LISTENER2 =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS =
           (PROTOCOL = TCP)
           (HOST = *****)
           (PORT = 1521)
         )
       )
     )
   )
SID_LIST_LISTENER2 =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = prim)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME = prim)
     )
   )
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
vi tnsnames.ora
prim =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = *****)
         (PORT = 1522)
       )
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SID = prim)
     )
   )
prim
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = *****)
         (PORT = 1521)
       )
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = prim)
       (UR = A)
     )
   )
After this set the local listener into the database by using the below command

 alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=
(PROTOCOL=TCP)(HOST=*****)(PORT=1521))
      (ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1522)))" scope=BOTH;

 System altered.

 Verify the Above Set parameter:

 SQL> show parameter LOCAL_LISTENER

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=(ADDRESS=(PROTOC
                                                 OL=TCP)(HOST=*****)(PORT
                                                 =1521)) (ADDRESS=(PROTOCOL=TCP
                                                 )(HOST=*****)(PORT=1522)
                                                 ))
Verify Listener1 with port 1522

 LSNRCTL> status listener1

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-JUN-2016 05:21:51
Uptime                    196 days 22 hr. 58 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***** )(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "prim" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
Service "primXDB" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL> service listener1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1522)))
Services Summary...
Service "prim" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:428 refused:0 state:ready
         LOCAL SERVER
Service "primXDB" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: localhost.localdomain, pid: 4389>
         (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=47710))

 The command completed successfully

 Verify Listener1 with Port 1521

 LSNRCTL> status listener2

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                07-JAN-2017 14:07:12
Uptime                    1 days 21 hr. 44 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "prim" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
Service "primXDB" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL> service listener2
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1521)))
Services Summary...
Service "prim" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:10 refused:0 state:ready
         LOCAL SERVER
Service "primXDB" has 1 instance(s).
  Instance "prim", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: localhost.localdomain, pid: 4389>
         (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=47710))

 The command completed successfully

 Hence Tested the Above scenario & Verify in our Env

Comments

Post a Comment

Popular posts from this blog

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

-
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' Error occur while restoring the database from the backup of RMAN RMAN> alter database open resetlogs; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/03/2017 16:05:14 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' To resolve this error fallow the below procedure – Hopefully you will succeed If you’re primary database is flashback & it has the standby – Fallow below procedure RMAN> run { 2> recover database; 3> } ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter datab
Read more

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

-
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All
Read more

Solutions: error while starting of Oracle Single instance ASM Database

-
1 st – Starting ASM [root@asmdb ~]#su - grid [grid@asmdb ~]$ asmcmd Connected to an idle instance ASMCMD> ls ASMCMD-08102: no connection to ASM; command requires ASM to run ASMCMD> [grid@asmdb ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:44:30 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Automatic Storage Management option SQL> conn / as sysasm Connected to an idle instance SQL> startup ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service SQL> exit Disconnected 2nd : Starting DB [oracle@asmdb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:15:15 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Gl
Read more