How to set Local_Listener ?

oracle local_listener
Set Local_Listener
what is the advantage of Local_Listener ?
What are the advantages of local_listener ?

http://anuj-singh.blogspot.com/2014/05/ora-12514-tnslistener-does-not.html

local_listener
_______________


The LOCAL_LISTENER parameter is a component of the cross instance registration technology for Oracle RAC (Real Application Clusters). Cross instance registration needs the local listener to help manage connections across all database instances for load balancing and failover. The local listener is particularly meaningful for the RAC configuration featured by multiple listeners on cluster nodes. If your Oracle configuration is not RAC enabled, you may as well remove the LOCAL_LISTENER parameter from your spfile in case for default port 1521 .




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

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.2.210)(PORT=1521))';
System altered.

SQL> alter system register;
System altered.

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL=TCP)(HOST
=192.168.2.210)(PORT=1521))


To dynamically update the LOCAL_LISTENER parameter, use the SQL statement ALTER SYSTEM SET:

ALTER SYSTEM SET LOCAL_LISTENER='listener_alias'

If you set the parameter to null with the statement that follows, then the default local address of TCP/IP, port 1521 is assumed.

ALTER SYSTEM SET LOCAL_LISTENER=''



example


in local host tnsnames.ora file


LISTENER_PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521)))





-bash-3.2$ tnsping LISTENER_PRIM

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-APR-2012 13:24:10

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521)))
OK (250 msec)




*.local_listener=LISTENER_PRIM




SQL> ALTER SYSTEM SET LOCAL_LISTENER='LISTENER_PRIM' scope=both sid='*' ;

System altered.




SQL> show parameter LOCAL_LISTENER

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

In case multiple entries are needed then:

==============

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.106.245.x5)(PORT=1521))))' scope=both SID='ugaryd1';

SQL>

System altered.

SQL> SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.106.245.x7)(PORT=1521))))' scope=both SID='ugaryd2';

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD

                                                 DRESS=(PROTOCOL=TCP)(HOST=xx.1

                                                 06.245.x5)(PORT=1521))))

===

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=192.168.1.106))" scope=both ;

alter system register ;

===

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node1>)) scope=both sid='INSTANCE_NAME1';

*  same for the 2nd instance 

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=<your_vip_node2>)) scope=both sid='INSTANCE_NAME2';

======

 srvctl config scan

 srvctl config scan_listener |grep -i port

 srvctl config vip -n nodename

  SHOW PARAMETER remote_listener;

REMOTE_LISTENER = (DESCRIPTION= (ADDRESS_LIST=

  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=xxx.0.0.1))

  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=xxx.0.0.2))

  (ADDRESS= (PROTOCOL=TCP) (PORT=1521) (HOST=xxx.0.0.3))

 ))

 

 alter system set remote_listener='REMOTE_LISTENER' scope=both sid='*';

 

 with ip

alter system set remote_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XX.16.1.1)(PORT=1521))

                                                (ADDRESS=(PROTOCOL=TCP)(HOST=XX.16.1.2)(PORT=1521))

                                                (ADDRESS=(PROTOCOL=TCP)(HOST=XX.16.1.3)(PORT=1521)))'

                                                 ;

 

 

set linesize 300 pagesize 300

 col value for a100

 col type for a20

  select type, value from v$listener_network where type='REMOTE LISTENER';

TYPE                 VALUE

-------------------- ------------------------------------------------------------------------------

REMOTE LISTENER      (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=xx.106.245.xx)))

 

 

 or 

 

 alter system set remote_listener='prod-scan:1521' scope=both sid='*';

or

 alter system set remote_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=xx.168.xxx.105)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=xx.168.xxx.106)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=xx.168.xxx.107)(PORT=1521)))'  scope=both sid='*';

 alter system register;

====

How to setup LOCAL_LISTENER for RAC One Node Database (Doc ID 1497619.1)

Health Check Alert: Set REMOTE_LISTENER and LOCAL_LISTENER initialization parameters (Doc ID 957536.1)

Oracle granule

SGA memory is allocated in unit of contiguous memory chunks called granule. The SGA memory components(shared pool,buffer cache, redo log buffer,java pool,streams pool,large pool) are sized as multiples of granules.

The granule size is determined based on the amount of memory requested at the instance startup. It is based on the SGA_MAX_SIZE. If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET for the purpose of sizing the granule. Once set, the granule size does not change for the life of the instance.
However granule maximum size = 16MB on 32-bit platforms.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 800M

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

RDBMS	SGA_MAX_SIZE (or memory_max_target)	GRANULE SIZE
9.2	<= 128MB	4MB
	> 128MB	16MB
10.2	<= 1GB	4MB
	> 1GB	16MB
11gR1	<= 1GB	4MB
	1Gb - 4GB	16MB
	4Gb - 16GB	64MB
	16Gb - 64GB	256MB
	> 64GB	512MB
11gR2 (and 11gR1 with patch 8813366 applied *)	< 1Gb	4Mb
	1Gb - 8Gb	16Mb
	8Gb - 16Gb	32Mb
	16Gb - 32Gb	64Mb
	32Gb - 64Gb	128Mb
	64Gb - 128Gb	256Mb
	> 128Gb	512Mb

How to test from above table. If I need granule size 16Mb, I have to Memory 1Gb - 4GB for SGA???

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> !free -m
total used free shared buffers cached
Mem: 888 849 38 0 2 740

I have only 900Mb for Physical Memory. Find Idea to test then created /tmp/pfile file for modified MEMORY_MAX_TARGET parameter.

SQL> create pfile='/tmp/pfile' from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

then faked /dev/shm size (this's just test for granule size to fake memory_max_target parameter)

Filesystem Size Used Avail Use% Mounted on
tmpfs 4.0G 0 4.0G 0% /dev/shm

then modified /tmp/pfile (MEMORY_MAX_TARGET=4G) - If MEMORY_MAX_TARGET is specified, then SGA_MAX_SIZE defaults to MEMORY_MAX_TARGET.

SQL> startup pfile='/tmp/pfile';
ORACLE instance started.

Total System Global Area 2058981376 bytes
Fixed Size 1300968 bytes
Variable Size 1644168728 bytes
Database Buffers 402653184 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.

SQL> show parameter memory_max_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 4G

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

Used granule size 16M.
If we set a value in the spfile that is not a multiple of the granule size, the actual size allocated will be rounded up to the nearest granule.

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
4

SQL> alter system set java_pool_size=4M;

System altered.

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 4M

SQL> alter system set MEMORY_MAX_TARGET=4G scope=spfile;

System altered.

SQL> shutdown

SQL> startup

SQL> select bytes/1024/1024 bytes_Mb from v$sgainfo where name like 'Granule Size';

BYTES_MB
----------
16

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 16M

The java_pool_size 4M in the spfile, after increased granule size, then the actual allocation for the java_pool_size will be rounded up to 16M.

Oracle 11g Grid Control rpm required on Red Hat 5 / centos 5 / oracle Linux 5 64bit

Oracle 11g Grid Control rpm required for Red Hat 5 / centos 5 / oracle Linux 5 64bit
Red Hat Enterprise Linux (RHEL) 5x86_64 platform.
oracle 11g 64bit rpm


■■ binutils-2.17.50.0.6 (x86_64)
■■ compat-libstdc++-33-3.2.3 (x86_64)
■■ compat-libstdc++-33-3.2.3 (i386)
■■ elfutils-libelf-0.125 (x86_64)
■■ elfutils-libelf-devel-0.125 (x86_64)
■■ gcc-4.1.2 (x86_64)
■■ gcc-c++-4.1.2 (x86_64)
■■ glibc-2.5-24 (x86_64)
■■ glibc-2.5-24 (i386)
■■ glibc-common-2.5 (x86_64)
■■ glibc-devel-2.5 (x86_64)
■■ glibc-devel-2.5 (i386)
■■ glibc-headers-2.5 (x86_64)
■■ ksh-20060214 (x86_64)
■■ libaio-0.3.106 (x86_64)
■■ libaio-0.3.106 (i386)
■■ libaio-devel-0.3.106 (x86_64)
■■ libaio-devel-0.3.106 (i386)
■■ libgcc-4.1.2 (x86_64)
■■ libgcc-4.1.2 (i386)
■■ libstdc++-4.1.2 (x86_64)
■■ libstdc++-4.1.2 (i386)
■■ libstdc++-devel 4.1.2 (x86_64)
■■ make-3.81 (x86_64)
■■ numactl-devel-0.9.8.x86_64 (x86_64)
■■ sysstat-7.0.2 (x86_64)
■■ unixODBC-2.2.11 (x86_64)
■■ unixODBC-2.2.11 (i386)
■■ unixODBC-devel-2.2.11 (x86_64)
■■ unixODBC-devel-2.2.11 (i386)

Oracle 11g process

ora_pmon_ : The Process Monitor is responsible for monitoring processes. In this case pmon will perform recovery for user processes that fail, will rollback uncommitted transactions, and clean up the buffer cache and free the resources being consumed.

ora_smon_ : The System Monitor process is responsible for instance recovery –cleaning up temporary segments that are not required and coalescing contiguous free extents in dictionary managed tablespaces.

ora_dbw0_ : The Database Writer process(s) handle the writing of dirty/modified buffers in the database buffer cache to datafiles.Depending on the activity of the database, there may be multiple database writers to keep up with the changes occurring.


ora_lgwr_ : The Log Writer process is responsible for writing data from the redo log buffers to the redo log files on disk.

ora_ckpt_ : The Checkpoint Process is responsible for signaling the synchronization of all database files with checkpoint information ensuring data consistency and faster database recovery in case of a crash.

ora_vktm_ : The Virtual Keeper of Time process is responsible for keeping track of the wall-clock time and used as a reference-time counter.

ora_diag_ : The Diagnosability Process handles hang detection and deadlock resolution.

ora_dbrm_ : The Database Resource Manager process takes care of setting up resource manager related tasks and resource plans.

ora_psp0_ : The Process Spawner will spawn Oracle processes as needed.


ora_vktm_ : The Virtual Keeper of Time process is responsible for keeping track of the wall-clock time and used as a reference-time counter.

ora_dbrm_ : The Database Resource Manager process takes care of setting up resource manager related tasks and resource plans.


ora_mman_ : The mman process is used for internal database tasks.


ora_reco_ : The Recoverer process is responsible for recovering all in-doubt transactions in a distributed database setup – connecting to remote databases and resolving pending transactions.

ora_mmon_ : The mmon process will perform manageability-related background tasks such as issuing alerts, taking snapshots, and capturing statistics values for SQL objects.

ora_mmnl_ : The mmnl process will perform other but light-weight manageability-related tasks such as capturing session history and metrics computation.

ora_d000_ : The Dispatcher processes will listen to and receive requests from connected sessions – placing them in the request queue on
the next available server for further processing. Dispatcher processes will also pick up outgoing responses from a results
queue and transmit those results back to the clients.

ora_s000_ : The Shared Server process will pick up requests from a call request queue, make the calls to the database to resolve the request, and then return the results to a result queue.

ora_smco_ : The Space Management Coordinator process handles the coordination and execution of space management tasks – spawning wnnn processes to carry out the task.

ora_fbda_ : The Flashback Data Archiver process archives historical data for tracked tables into the flashback data archives – storing a pre-image of the rows into the flashback archive and keeping the metadata for those rows.

ora_cjq0_ : The Job Queue process is used for batch processing – running user jobs within a scheduler service. Able to run a large number of concurrent jobs, the coordinator process (cjq0) will spawn job queue slaves (jnnn) to actually run the jobs.

===

SET LINESIZE 500

col sid format 999999

col username format a20

col osuser format a15

col name for a20

col ERROR for 999999999999999

col TRACEFILE for a80

SELECT b.spid,a.sid, a.serial#,a.username, a.osuser,BACKGROUND,name ,bg.DESCRIPTION,ERROR,a.sql_id,TRACEFILE

FROM v$session a, v$process b,V$BGPROCESS BG

WHERE a.paddr= b.addr

and BACKGROUND=1

and bg.paddr= b.addr

ORDER BY b.spid;

Oracle trace file compress

# Gzips Oracle Archive logs that are older than 5 days
#

cd /opt/oracle/admin/cccdb/arch
/usr/bin/find . -name '*.arc' -mtime +5 -exec /usr/bin/gzip -9 {} \;

Oracle Shell script for wait capture

#!/bin/bash

ORACLE_HOME=/opt/oracle/product/10.2; export ORACLE_HOME
ORACLE_SID=cccdb; export ORACLE_SID

# export PATH=$ORACLE_HOME/bin:.:$PATH

sqlplus -s /nolog <connect / as sysdba

set serveroutput on
set pagesize 100 lines 130
set feed on heading off
spool orapinx.alert

-- execute immediate dbms_output.put_line('see oracle dump');

declare

x number(7);

begin

select sysdate from dual;
select count(*) into x from v\$session_wait where event like '\%wait on X\%';

-- dbms_output.put_line('see oracle dump');

if x>0 then

execute immediate 'ALTER SESSION SET max_dump_file_size = UNLIMITED' ;
execute immediate 'ALTER SESSION SET EVENTS ''10998 trace name context forever,level 1''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';

dbms_output.put_line('see oracle dump');
else
null;
end if;
end;

spool off
exit
EOF

# /opt/oracle/admin/cccdb/scripts

touch /opt/oracle/admin/cccdb/orapinx1.tmp

if [ `cat orapinx.alert|wc -l` -gt 0 ]

then
# echo "test"
cat /opt/oracle/admin/cccdb/scripts/orapinx.alert > /opt/oracle/admin/cccdb/scripts/orapinx.tmp

# to remove blank space
# cat tablespace.tmp | awk '$0!~/^$/ {print $0}' > xyz.tmp
# ^L= ctrl+L <<<<<<<<<--------


cat /opt/oracle/admin/cccdb/scripts/orapinx.tmp | sed 's/^L//g' > /opt/oracle/admin/cccdb/scripts/orapinx1.tmp

mv /opt/oracle/admin/cccdb/orapinx1.tmp /opt/oracle/admin/cccdb/orapinx.tmp



# mailx -s " Ora-error wait PIN X for APTUS (Novagenesis) Database" -r oracle@joshua anuj.singh@XXXX.co.uk < orapinx.tmp

fi

ORA-00600: internal error code, arguments: [kzsviver:1], [], [], [], [], [],

in Oracle 11g

ORA-00600: internal error code, arguments: [kzsviver:1], [], [], [], [], [],

alter user xyz identified by values is null in pl sql block <<<<<<<<

example .....

my_text :=
'Alter user '
|| pwd.username
|| ' identified by values '
|| ''''
|| l_old_password
|| '''';
-- dbms_output.put_line(my_text);
EXECUTE IMMEDIATE (my_text);