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

RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2

-
RAC Failure Scenario - Testing in our RAC Test DB - Node 1 & Node 2 Testing Various RAC Failure scenario like: 1 - Node Failure 2 - DB Instance Failure 3 - ASM Instance Failure 4 - Listener failure 5 - Public Network Failure 6 - Private Network Failure 7 - OCR & Voting Disk Failure 8 - ASM Disk Failure ============== 1 - Node Failure ============== It can be planned, unplanned or all the nodes it could be any scenario Let us start the workload & shutdown the node The expected result will be: Resources will go offline Instance recovery will be performed Node vip & scan vip fail to surviving node Scan listener will fail over surviving node Client connection are moved to surviving instance Now shutdown Node 1 in OS Level intentionally or due to some reason the node 1 is shutdown #Shutdown –h now Check the node 2 status [oracle@test-rac2 ~]$ crs_stat -t Name           Type           Target    State     Host ----------------------------
Read more

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