Friday, June 8, 2012

Script to Find any String in any Table in a Schema

Tonight, I had to go looking for a string to help solve a VMWare corruption issue.  Problem was,  how the heck do you find a string in hundreds of tables?


I may have been long winded creating this,  but it did the trick for me:



- ----------------------------------------------------------
--  Tested on Oracle 11gR2
--  by : Linda Smith
-- Created on : 08-Jun-2012
-- Usage : Search a string in all tables and provides the output in a Owner:Table:Column:Counter
--    format.
-- ----------------------------------------------------------
-- Remarks:
-- You will be prompted for database schema and string to search
-- Added dbms_application_info, so progress can be viewed in v$session

set serveroutput on size 100000;
set verify off;

accept i_schema prompt "Enter Database Schema to search in: "
accept i_string prompt "Enter Text string to search for: "

-- Multiple rows fetch
-- Get all tables with CHAR-like columns
declare

vg_stime pls_integer default dbms_utility.get_time;

-- Check if column is numeric
function fg_is_number (i_string varchar2) return boolean
is
vl_dummy pls_integer;
begin
vl_dummy := to_number(i_string);
return true;
exception
when others then
return false;
end fg_is_number;

procedure pg_search_table( i_schema in varchar2
, i_table in varchar2
, i_column in varchar2
, i_string in varchar2
)
as
type cursor_type is ref cursor;
rl_cnt cursor_type;
vl_cnt pls_integer;
begin
open rl_cnt for 'select count(*) from "'||i_schema||'"."'||i_table||'" where lower("'||i_column||'") like :b3'
using i_string;
fetch rl_cnt into vl_cnt;
case
when (vl_cnt > 0) then dbms_output.put_line (i_schema||'.'||i_table||'.'||i_column|| ': ' || vl_cnt);
else null;
end case;
close rl_cnt;
end pg_search_table;

procedure pg_search_schema( i_schema in varchar2, i_string in varchar2)
is

vl_dtp varchar2(16) := 'VARCHAR2_TABLE';

cursor cl_tab( i_schema in varchar2, i_data_type in varchar2)
is
select tcs.table_name
, tcs.column_name
from dba_tab_columns tcs
where data_type like i_data_type
and data_type != vl_dtp
and owner = i_schema
and not exists ( select null
from dba_views vws
where vws.view_name = tcs.table_name
and vws.owner = tcs.owner
)
order by table_name
, column_name
;

type tl_tabnm is table of all_tab_columns.table_name%type;
type tl_colnm is table of all_tab_columns.column_name%type;
vl_tables tl_tabnm;
vl_columns tl_colnm;
vl_data_type varchar2(20);

begin

-- If the search-string is a numeric value search in NUMBER and VARCHAR2 columns
-- Else search in all CHAR-like columns
if fg_is_number(i_string)
then
vl_data_type := 'NUMBER';
dbms_output.put_line('searching for number '||i_string||' in database schema '||i_schema||chr(10));
else
vl_data_type := '%CHAR%';
dbms_output.put_line('searching for string "'||i_string||'" in schema '||i_schema||chr(10));
end if;

open cl_tab(i_schema, vl_data_type);
fetch cl_tab
bulk collect into vl_tables, vl_columns;
for rl_tab in 1..cl_tab%rowcount loop
dbms_application_info.set_action( 'searching ' || vl_tables(rl_tab) || '.' || vl_columns(rl_tab) );
pg_search_table(i_schema, vl_tables(rl_tab), vl_columns(rl_tab), i_string );
end loop;
close cl_tab;
end pg_search_schema;

begin

-- Tell to uncle v$session what you are doing
dbms_application_info.set_module( 'search.sql','searching for "&&i_string"' );
-- dbms_lock.sleep(5);
pg_search_schema(upper('&&i_schema'), lower('&&i_string'));

-- Reset session information
dbms_application_info.set_module( null, null );

dbms_output.put_line('Search time: '||round((dbms_utility.get_time-vg_stime)/100, 2) ||' Seconds...' );
end;
/

undefine i_schema;
undefine i_string;


Tuesday, June 5, 2012

How to Change the Domain Name on an Oracle 11gR2 RAC environment


In this example, we are going to modify the current old-domain.com domain to a new domain called new-domain.com. This will be completed on a two node Oracle 11gR2 RAC database running on RedHat Linux 5.4 64-bit.


First, identify the DNS Server changes have been completed.
Update the /etc/resolv.conf file to reflect the new DNS Server settings (if needed)

As root:

$ cat /etc/resolv.conf
nameserver 10.10.15.221
nameserver 10.20.66.252


Copy the /etc/resolv.conf file to all other servers in the cluster as root.


# scp prod-server1:/etc/resolv.conf /etc/resolv.conf


resolv.conf 100% 50 0.1KB/s 00:00




Edit the /etc/hosts file and change all old domain entries to the new domain entries on each server in the cluster.


#vi /etc/hosts

$ cat /etc/hosts.old


# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 prod-server1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
# Production IPs
192.68.57.190 prod-server1.old-domain.com prod-server1 ps1
192.68.57.191 prod-server2. old-domain.com prod-server2 ps2
192.68.57.170 prod-server1-vip. old-domain.com prod-server1-vip ps1-vip
192.68.57.171 prod-server2-vip. old-domain.com prod-server2-vip ps2-vip



$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 prod-server1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
# Production IPs
192.68.57.190 prod-server1.new-domain.com prod-server1 ps1
192.68.57.191 prod-server2.new-domain.com prod-server2 ps2
192.68.57.170 prod-server1-vip.new-domain.com prod-server1-vip ps1-vip
192.68.57.171 prod-server2-vip.new-domain.com prod-server2-vip ps2-vip



Test that the SCAN listener address with the new domain is established with the DNS Server.



#nslookup prod_cluster-scan.new-domain.com
Server: 10.10.15.221
Address: 10.10.15.221#53

Name: xnzxpdb205206x-scan.new-domain.com
Address: 192.68.57.182
Name: xnzxpdb205206x-scan.new-domain.com
Address: 192.68.57.180
Name: xnzxpdb205206x-scan.new-domain.com
Address: 192.68.57.181

Log on as oracle and modify the tnsnames.ora entries to reflect the new domain:


#su - oracle
oracle@prod-server2:proddb2:/home/oracle >
$ cd $ORACLE_HOME/network/admin
oracle@prod-server2:proddb2:/proddb/app/oracle/product/11.2.0.3/network/admin >
$ vi tnsnames.ora.old


proddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server1-vip.old-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.old-domain.com)
)
)


proddb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server1-vip.old-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.old-domain.com)
(INSTANCE_NAME = proddb1)
)
)


proddb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server2-vip.old-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.old-domain.com)
(INSTANCE_NAME = proddb2)
)
)


$ cat tnsnames.ora
proddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server1-vip.new-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.new-domain.com)
)
)



proddb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server1-vip.new-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.new-domain.com)
(INSTANCE_NAME = proddb1)
)
)




proddb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod-server2-vip.new-domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb.new-domain.com)
(INSTANCE_NAME = proddb2)
)
)


Log on to SQL*PLUS and modify the domain name:



oracle@prod-server2:proddb2:/proddb/app/oracle/product/11.2.0.3/network/admin >


$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 17:03:32 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string old-domain.com

SQL> alter system set db_domain='new-domain.com' scope=spfile sid='*';

System altered.


NOTE: This will require us to bounce the database for the change to take effect.




Modify the global_name

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
PRODDB.OLD-DOMAIN.COM



SQL> update global_name set global_name='PRODDB.NEW-DOMAIN.COM' where global_name='PRODDB.OLD-DOMAIN.COM';

1 row updated.



Or


SQL> alter database rename global_name to PRODDB.NEW-DOMAIN.COM;


Database altered.


SQL> select * from global_name;


GLOBAL_NAME
--------------------------------------------------------------------------------
PRODDB.NEW-DOMAIN.COM


SQL>



Modify the remote_listener to reflect the use of the new SCAN listener address.


SQL> alter system set remote_listener='prod-cluster-scan.new-domain.com:1521' scope=both sid='*';


System altered.


Modify the database services with SRVCTL to use the new domain name.


oracle@prod-server2:proddb2:/home/oracle >


$ srvctl modify database -d proddb -m new-domain.com -o /proddb/app/oracle/product/11.2.0.3




Confirm the results:

oracle@prod-server2:proddb2:/home/oracle >
$ srvctl config database -d proddb

Database unique name: proddb
Database name: proddb
Oracle home: /proddb/app/oracle/product/11.2.0.3
Oracle user: oracle
Spfile: +PROD_DATA/proddb/spfileproddb.ora
Domain: new-domain.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: proddb
Database instances: proddb1,proddb2
Disk Groups: PROD_DATA,FLASH,REDOA,REDOB,PROD_CTRL1,PROD_CTRL2
Mount point paths:
Services:
Type: RAC
Database is administrator managed
oracle@prod-server2:proddb2:/home/oracle >


Bounce the database:
oracle@prod-server2:proddb2:/home/oracle >


$ srvctl stop database -d proddb


oracle@prod-server2:proddb2:/home/oracle >
$ srvctl start database -d proddb




Test the remote connection:


oracle@prod-server2:proddb2:/proddb/app/oracle/diag/rdbms/proddb/proddb2 ;


$ sqlplus a/b@proddb


SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 17:12:30 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied




Confirm the services name and domain name are updated in the database:


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 17:13:21 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options



SQL> show parameter domain




NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string new-domain.com


SQL> show parameter service



NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string proddb.new-domain.com

Monday, June 4, 2012

Oracle 11gR2 RAC: How to Configure a Second Listener on a Separate Network




This example will be for a two node cluster.

During the Grid Infrastructure installation, the (default) node VIP listener is always created referencing the public network on Oracle 11gR2. 

However, some systems may be attached to more than one public network, so there may be a need to create a second listener for this second public network. 

The purpose of this note is to explain how to add/create/configure a second listener on a second network in an 11.2 Grid Infrastructure environment.
This note does not apply to the binding of two public network interfaces for failover purposes, like IPMP or bonding etc.

Solution:
Assume the following names are defined for two nodes cluster (DNS or /etc/hosts):
# public host name and associated VIP on eth1
prodserver1             192.18.20.195
prodserver1-vip         192.18.20.175
prodserver2             192.18.20.175
prodserver2-vip         192.18.20.176


# scan name
prod_cluster-scan                               192.18.20.186
                                                192.18.20.187
                                                192.18.20.188

# host name for the 2nd public network and associated VIP on eth0
prodserver1-m              192.168.10.36
prodserver1-m-vip          192.168.10.205
prodserver2-m              192.168.10.37
prodserver2-m-vip          192.168.10.206


A listener has already been created on 192.18.20.x network, we are going to create a 2nd listener on 192.168.10.x network.

1. Ensure a VIP address is defined for the 2nd public network

In this example it's prodserver1-m-vip and prodserver2-m-vip (see above).

2. Create the CRS resource for the 2nd network

A new network and new vip resources for the 2nd network are created by using the '-k' switch:
as root user:
# srvctl add vip -n  -k  -A //[if1[|if2...]] [-v]

eg:
# srvctl add vip -n prodserver1 -k 2 -A 192.168.10.205/255.255.255.0/eth0
# srvctl add vip -n prodserver2 -k 2 -A 192.168.10.206/255.255.255.0/eth0
This command will implicitly create the dependent network resource.

From 11.2.0.2+, network resource can be created explicitly:
as root user:
# srvctl add network [-k ] -S //[if1[|if2...]] [-w ] [-v]

eg:
# srvctl add network -k 2 -S 192.168.10.0/255.255.255.0/eth0

Then add vip resource for the 2nd network:
# srvctl add vip -n prodserver1 -k 2 -A 192.168.10.205/255.255.255.0/eth0
# srvctl add vip -n prodserver2 -k 2 -A 192.168.10.206/255.255.255.0/eth0

The CRS resource 'ora.net2.network' and 2nd network vip should now have been created, they are both required for the 2nd listener, run the following command to verify:
# crsctl stat res -t |grep -E 'net|vip'
ora.net1.network
ora.net2.network
ora.scan1.vip
ora.scan2.vip
ora.scan3.vip
ora.prodserver1-m-vip.vip
ora.prodserver1.vip
ora.prodserver2-m-vip.vip
ora.prodserver2.vip





3. Create a new RAC listener using 'netca'
As the grid user invoke "netca" from the 11.2 GRID_HOME, the select "Cluster configuration" -> "Listener configuration" -> "Add", enter Listener name as required.  For example: "LISTENER_MON". 
On the next page, "Select Subnet", the 2nd subnet will appear in the drop down list. For example:
2 192.168.10.0/255.255.255.0
1 192.18.20.0/255.255.255.128

grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >
$ ./netca

Oracle Net Services Configuration:
Oracle Net Configuration Assistant is launched from Grid Infrastructure home. Network configuration will be clusterwide.
Configuring Listener:LISTENER_MON
prodserver1...
prodserver2...
Listener configuration complete.
Oracle Net Listener Startup:
    Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >

Select subnet 2 , select the protocol and define the Listener port as desired to complete the listener creation.
Note: When adding 2nd listener with srvctl on the same port, it may fail with:

$ ./srvctl add listener -l LISTENER_MON -p 1523 -k 2
PRCN-2061 : Failed to add listener ora.LISTENER_MON.lsnr
PRCN-2065 : Port(s) 1523 are not available on the nodes given
PRCN-2067 : Port 1523 is not available across node(s) "prodserver1
"

The workaround is to use "-s" option:

$ ./srvctl add listener -l LISTENER_MON -s -p 1523 -k 2

4. Verify the new RAC listener
The new listener will be started at the end of netca. This listener will listen on the IP address 192.168.10.205 (or 192.168.10.206 for node 2) associated with the 2nd network.  To check:
$ lsnrctl status LISTENER_MON

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-MAY-2012 22:54:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MON)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MON
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                30-MAY-2012 22:52:49
Uptime                    0 days 0 hr. 1 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/app/grid/product/11.2.0.3/network/admin/listener.ora
Listener Log File         /grid/app/gridbase/diag/tnslsnr/prodserver1/listener_mon/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MON)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.205)(PORT=1523)))
The listener supports no services
The command completed successfully
grid@prodserver1:+ASM1:/grid/app/grid/product/11.2.0.3/bin >

Please note the output for 11.2.0.2+ only shows two ADDRESS lists:


(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MON)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.205)(PORT=1523)))

5. Prepare the database instance for the new listener

To ensure that connections to the remote listener are only redirected to the local listener on the same network, LISTENER_NETWORKS parameter needs to be set in the pfile or spfile for the database instance.

As the Oracle user, assuming the RDBMS $ORACLE_HOME/network/admin/tnsnames.ora (or the $TNS_ADMIN/tnsnames.ora) has the following local and remote listener defined,  prepare similar content for each node using node specific VIP name (or VIP IP adddress). For example, on node1:
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
  )


proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1523)))
   )

On node 2:
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
  )

proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523)))
   )

Make certain your remote_listener is set to the SCAN address:
alter system set remote_listener=’prod_cluster-scan.domain.com:1521’ scope=both sid=’*’;

Update your local listener for each database to point to the Description address as outlined in the Setting up SCAN notes from Oracle:

alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.18.20.175)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.205)(PORT=1523)))) scope=both sid=’proddb1’;


alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.18.20.176)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.206)(PORT=1523)))) scope=both sid=’proddb1’;

At this point,   I added the following to my tnsnames.ora file as the user oracle:
proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

The database parameter LISTENER_NETWORKS needs to be set to:
alter system set LISTENER_NETWORKS='((NAME=network1)(LOCAL_LISTENER=proddb_LOCAL)(REMOTE_LISTENER=prod_cluster-scan.domain.com))','((NAME=network2)(LOCAL_LISTENER=proddb_mon_LOCAL)(REMOTE_LISTENER=LISTENER_MON_PRODDB))';
Without using sid="', above change will be applied to all instances.
Please note:
 
1. Listeners specified by the LISTENER_NETWORKS parameter should not be used in the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks.

2. Due to unpublished bug 8678541 INCORRECT ENDPOINTS OF LISTENER ON THE SECOND NETWORK, the 2nd listener by default will also listen on the IP address associated with the host name of the node (in this example 192.18.20.195). Although it is invalid, it should not cause any issue. This has been fixed in 11.2.0.2.

3. Only 1 SCAN name is defined for a RAC cluster, the 2nd network can not use the same SCAN name.


Appendix:   Examples of the setup tnsnames, listener, and settings


AS ORACLE USER SQLPLUS ON DATABASE
Database Parameters:

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      proddb, proddb.domain.com
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string      ((NAME=network1)(LOCAL_LISTENE
                                                 R=proddb_LOCAL)(REMOTE_LISTENER
                                                 =prod_cluster-scan.domain
                                                 .com)), ((NAME=network2)(LOCAL
                                                 _LISTENER=proddb_mon_LOCAL)(REM
                                                 OTE_LISTENER=LISTENER_MON_OP1V
                                                 C))
local_listener                       string      (DESCRIPTION=
                                                 (ADDRESS_LIST=(ADDRESS=(PROTOC
                                                 OL=TCP)(HOST=192.18.20.175)(PO
                                                 RT=1521))

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.10.205)(PORT=1523))))
remote_listener                      string      prod_cluster-scan.domain.
                                                 int:1521
SQL>




AS ORACLE USER

Node 1:

$ cat tnsnames.ora
proddb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb1)
     )
    )

proddb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb2)
     )
    )
#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
  )


proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1523)))
   )



Node 2:
$ cat tnsnames.ora
proddb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb_mon =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
    )
  )

proddb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb1)
     )
    )

proddb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb.domain.com)
      (INSTANCE_NAME = proddb2)
     )
    )

#
# Local Listeners
#
proddb_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521))
  )

proddb_mon_LOCAL =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523))
   )

# Remote Listener
LISTENER_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-vip.domain.com)(PORT = 1521)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-vip.domain.com)(PORT = 1521)))
   )

LISTENER_MON_PRODDB =
   (DESCRIPTION_LIST =
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1-m-vip.domain.com)(PORT = 1523)))
     (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2-m-vip.domain.com)(PORT = 1523)))
   )




GRID USER


$ cat listener.ora
LISTENER_PRODDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_PRODDB))))               # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRODDB=ON                # line added by Agent
oracle@prodserver1:proddb1:/proddb/app/oracle/product/11.2.0.3/network/admin >


AS GRID USER:
cat listener.ora

# listener.ora.prodserver1 Network Configuration File: /grid/app/grid/product/11.2.0.3/network/admin/listener.ora.prodserver1
# Generated by Oracle configuration tools.
LISTENER_MON=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MON))))            # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON

LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

LISTENER_SCAN3 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))
    )
  )

LISTENER_SCAN2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
    )
  )

ADR_BASE_LISTENER_SCAN3 = /grid/app/grid

LISTENER_SCAN1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
    )
  )

ADR_BASE_LISTENER_SCAN2 = /grid/app/grid

ADR_BASE_LISTENER_SCAN1 = /grid/app/grid

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_MON=ON          # line added by Agent



AS GRID USER:

cat sqlnet.ora

# sqlnet.ora.prodserver1 Network Configuration File: /grid/app/grid/product/11.2.0/network/admin/sqlnet.ora.prodserver1
# Generated by Oracle configuration tools.



NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /grid/app/gridbase