Suppose you are in an environment where you have many logons and look for a
solution to tune the logon time for the users. This can be in an OLTP system.
An option we are going to explore here is creating multiple listeners.
You first have to create new listener, which let's say will run on port 1523;
on top of the default one you already have running on 1521.
You can simply edit your listener.ora or create new listener with Net Configuration
Assistant.
You will also have to add new entry in your tnsnames.ora, either by editing the file or
with Net Manager.
A sample of the existing files will be:
listener.ora
ADR_BASE_LISTENER_1523 = D:\oci112
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\oci112
LISTENER_1523 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
)
)
tnsnames.ora
HOBER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hober)
)
)
HOBER1523 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hober)
)
)
Now let's check the status of the listeners:
LSNRCTL> status LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 30-JAN-2012 23:37:39
Uptime 0 days 0 hr. 7 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 1 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 30-JAN-2012 23:02:35
Uptime 0 days 0 hr. 43 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hober" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
The new listener has the UNKNOWN status as is defined statically, still, this will not prevent us to establish a new connection.
The one with status READY is the one which is registered automatically -
when DB is started, PMON automatically registers the DB to the listener.
It is doing this in the very first minute since db startup.
That is the reason you have to keep the correct order, first you start the listener and then the database.
Now, let's attempt to connect:
----------1
C:\>sqlplus sys@hober as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 23:41:50 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 300
SQL> set pagesize 200
SQL> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
HOBER hober
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as "date" from dual;
date
-------------------
30-01-2012 23:44:05
SQL>
------------2
C:\>sqlplus sys@hober1523 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 23:43:34 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
--------- ------------------------------
HOBER hober
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as "date" from dual;
date
-------------------
30-01-2012 23:44:16
SQL>
The connection is succesfully established.
Now we want the instance to automatically register with listener.
We are doing this by altering the parameter LOCAL_LISTENER as it follows:
SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
2 (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))" scope=BOTH;
System altered.
SQL> show parameter listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS_LIST=(ADDRESS=(PROTOC
OL=TCP)(HOST=localhost)(PORT=1
521))
(ADDRESS=(PROTOCOL=TCP)(HOST
=localhost)(PORT=1523)))
remote_listener string
We restart the listeners and check their status:
---stopping stage
LSNRCTL> stop LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
LSNRCTL> stop LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
The command completed successfully
---startup stage
LSNRCTL> start LISTENER
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:31
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> start LISTENER_1523
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:44
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 1 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> services LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
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
And now the stats:
LSNRCTL> stat LISTENER
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:31
Uptime 0 days 0 hr. 3 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "hober" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stat LISTENER_1523
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER_1523
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 31-JAN-2012 00:00:44
Uptime 0 days 0 hr. 3 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\oci112\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oci112\diag\tnslsnr\emy-85f0b520004\listener_1523\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1523)))
Services Summary...
Service "HOBER" has 2 instance(s).
Instance "HOBER", status UNKNOWN, has 1 handler(s) for this service...
Instance "hober", status READY, has 1 handler(s) for this service...
Service "hoberXDB" has 1 instance(s).
Instance "hober", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
We see that the instance appears READY in both the listener configurations, meaning it has
succesfully registered with the listener.
Now you can redirect your application to connect either to service name hober or hober1523, which in the end will connect to the same db instance.
Tuesday, January 31, 2012
Monday, January 30, 2012
History of CPU/PSU applied
When patching, you have two entities in your environment
- Oracle Database Software
- Oracle Database
You can check the patches installed using opatch utility which you can find in $ORACLE_HOME/OPatch:
./opatch lsinv -bugs_fixed | grep -i 'database cpu'
Following patching your database with a PSU/CPU, you want to keep a history of the patches applied.
In 11g (actually this started since 10.2.0.4), there is a new post patch installation script which does the job.
It is catbundle.sql, and is located in $ORACLE_HOME/rdbms/admin. (In previous releases this was done by catcpu.sql)
This shall be invoked with 2 parameters, patch type (PSU/CPU) and action (apply or rollback).
The purpose of the script is to update the entry in registry$history.
SQL> @catbundle.sql CPU APPLY
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_GENERATE_2012Jan30_11_07_27.log
Apply script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_APPLY.sql
Rollback script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/' || 'catbundle_CPU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/PROD/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_APPLY_2012Jan30_11_07_32.log
The control script you can use is the following - this will list which CPU is implemented into your database:
select substr(action_time,1,30) action_time,
substr(id,1,8) id,
substr(action,1,10) action,
substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) BUNDLE_SERIES,
substr(comments,1,20) comments
from registry$history;
References:
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]
Is it required to run PSU/CPU patch post installation step after creating the database using DBCA in already patched Oracle Home ? [ID 1388555.1]
Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch [ID 865255.1]
- Oracle Database Software
- Oracle Database
You can check the patches installed using opatch utility which you can find in $ORACLE_HOME/OPatch:
./opatch lsinv -bugs_fixed | grep -i 'database cpu'
Following patching your database with a PSU/CPU, you want to keep a history of the patches applied.
In 11g (actually this started since 10.2.0.4), there is a new post patch installation script which does the job.
It is catbundle.sql, and is located in $ORACLE_HOME/rdbms/admin. (In previous releases this was done by catcpu.sql)
This shall be invoked with 2 parameters, patch type (PSU/CPU) and action (apply or rollback).
The purpose of the script is to update the entry in registry$history.
SQL> @catbundle.sql CPU APPLY
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_GENERATE_2012Jan30_11_07_27.log
Apply script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_APPLY.sql
Rollback script: /u01/xxxx/apps/proddb/11.2.0/rdbms/admin/catbundle_CPU_xxxx_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/xxxx/apps/proddb/11.2.0/cfgtoollogs/catbundle/' || 'catbundle_CPU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/PROD/apps/proddb/11.2.0/cfgtoollogs/catbundle/catbundle_CPU_xxxx_APPLY_2012Jan30_11_07_32.log
The control script you can use is the following - this will list which CPU is implemented into your database:
select substr(action_time,1,30) action_time,
substr(id,1,8) id,
substr(action,1,10) action,
substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) BUNDLE_SERIES,
substr(comments,1,20) comments
from registry$history;
References:
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]
Is it required to run PSU/CPU patch post installation step after creating the database using DBCA in already patched Oracle Home ? [ID 1388555.1]
Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch [ID 865255.1]
Subscribe to:
Posts (Atom)