Dynamic registration in Oracle

-
·      *   In a server if there is a listener running, then an Oracle database can dynamically register it in the listener to provide its service.
·       * This dynamic registration is automatic if the listener is running in the Oracle default listening port 1521
·       *  Incase if you want to allow dynamic database registration only to certain databases in the server. You need to run the listener in non-default port. (For example: 1522 or 1530 etc)
·       *  To allow dynamic registration of database services in the listener with non-default port we need to modify few parameters in database level. No changes needed in listener.ora file. Below are the parameters
o   In case if the listener and database are in same server change the parameter LOCAL_LISTENER
For example:
Alter system set local_listener=”orclnew” scope=both;

Where listener2 should be resolvable by tnsping command as shown below

Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
OK (0 msec)

Or

You can directly give the address in the local_listener parameter itself as shown below

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1522))';

System altered.

SQL>
·         After the database startup check the lsnrctl status to check whether the database is registered in the listener
o   Lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 24-MAR-2015 14:39:19

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                23-MAR-2015 16:06:20
Uptime                    0 days 22 hr. 32 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Preetha\product\11.2.0\dbhome_2\network\admin\listener.ora
Listener Log File         d:\app\preetha\diag\tnslsnr\Preetha-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
The listener supports no services
The command completed successfully

The above highlighted portion says that no services are registered yet.

o   In this case you can manually register your database by the command
Alter system register;
·         Whenever a database is starting up , the background process LREG tries to register to the listener in the server.
·         In case if the database is not up or it is not yet dynamically registered with the listener running in the server, then you may encounter ORA-12514.
o   If you face this error, then you can wait a while and try again to connect to the database.
o   Check the listener status whether the database is registered.
o   If not registered then manually registered, try registering the database manually.
o   If you are not able to access the database and face ORA-12514 then check the parameter LOCAL_LISTENER is correct or not.

o   If you still face problem check the listener log file, and database alert log file for clues.

Sample below.

See the value of the parameter local_listener in the database

SQL> show parameter local_listener

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

SQL>

Currently it is NULL, this means the database will try to register with the default port 1521.

Check the services in the listener.

Check listener status.
SQL> host lsnrctl services

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 26-MAR-2015 19:19:20

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Preetha-PC)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

SQL>

It is obvious that the database is not registered.

Now change the parameter local_listener to point the listener with non-default port.

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1522))' SCOPE=BOTH;

System altered.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=L
                                                 OCALHOST)(PORT=1522))
SQL>

You can either wait 60 seconds for the instance to register the services with the listener or you can issue the alter system command register the database manually.

SQL> alter system register;

System altered.

SQL> host lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.1.0 - Production on 26-MAR-2015 19:35:13

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Preetha-PC)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.1.0 - Production
Start Date                26-MAR-2015 19:19:08
Uptime                    0 days 0 hr. 16 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\oracle\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\oracle\product\12.1.0\dbhome_1\log\diag\tnslsnr\Preetha-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Preetha-PC)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl12c" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
Service "orcl12cXDB" has 1 instance(s).
  Instance "orcl12c", status READY, has 1 handler(s) for this service...
The command completed successfully

As you can see the service is register with the listener.


Comments

Post a Comment

Popular posts from this blog

Current Variables in T24

-
CURRENT VARIABLE Introduction In addition to the system common variables that can be used in ENQUIRY and VERSION there is a feature where the user can populate a variable of their own and use it later. There are now a series of new system variables and a much wider option for users to create and use their own which are: CURRENT.CUSTOMER CURRENT.ARRANGEMENT CURRENT.EXTERNAL.USER These may be set on login by an external user and cleared when they logout. This means that ENQUIRY & VERSION can be tailored to accept the content of the variable instead of forcing a user to enter their own customer number or arrangement etc. Similarly in VERSION you can now default a field content by using the !CURRENT.CUSTOMER to populate a customer field with the current content of the variable. So the variable could be set in one Enquiry and used in either another Enquiry linked to it; or via VERSION to populate fields in an application triggered from the Enquiry. Note: ...
Read more

All accounting entries of an T24 account

-
There is a core routine that will return all the accounting entries for an account it is EB.ACCT.ENTRY.LIST Syntax: EB.ACCT.ENTRY.LIST(ACCOUNT.NUMBER,FROM.DATE,END.DATE,YID.LIST,OPENING.BAL,ER) Passed Parameters. ACCOUNT.NUMBER :- Account for which balance & entries is to be returned. FROM.DATE :- Start date for opening balance and entries. END.DATE :- The last date to be considered. Outgoing : YID.LIST :- List of statement entry ids. OPENING.BAL :- Opening balance on the startt date. ER :- Any errors found
Read more

SQL queries in JBase

-
The query Language of Jbase is really restrictive. Data analysis and comparison between two table is not possible without the use of spreadsheet software. I recently came to know that Jbase is providing a command called SQLSELECT through which you can actually issue SQL query command directly on Jbase tables. A sample would be like SQLSELECT * FROM FBNK.ACCOUNT WHERE SHORT_NAME LIKE '%PETER%' AND CATEGORY = 6601 Limitations: The command though have serious limitations such as  The table you query should not have dot charecter for example you cannot query FBNK.CUSTOMER.ACCOUNT with this command. But there is a workaround for the above drawback. Make a copy of the VOC record FBNK.CUSTOMER.ACCOUNT into a new one without ID in its ID. For Example: COPY FROM VOC FBNK.CUSTOMER.ACCOUNT,FBNK.CUSACC SQLSELECT * FROM FBNK.CUSACC; will work. The field name should not have dot character. You can overcome this by changing the dot character to underscore. For...
Read more