tnsnames.ora setup

system@DEMO>
system@DEMO> show parameter name

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string                           DEMO
db_unique_name                       string                           DEMO
global_names                         boolean                          FALSE
instance_name                        string                           DEMO1
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string                           DEMO
system@DEMO>
system@DEMO> show parameter domain

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
db_domain                            string


system@DEMO>  select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
DEMO


sun001@DEMO1:/ora01/oracle/admin/network $ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
ADR_BASE = /oramisc01/oracle
DIAG_ADR_ENABLED=true
SQLNET.SEND_TIMEOUT=10

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

Db_name=DEMO

Service name : DEMO and DEMO_REPORT

step 1

# In database

alter system set service_name= DEMO,  DEMO_REPORT scope=both sid='*';

step 2
check sqlnet.ora and comment parameter names.default_domain 

step 3
lsnrctl reload
lsnrctl status 


step 4

# in tnsnames

DEMO_REPORT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )


DEMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST <SCANNAME>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEMO)
    )
  )

# the end

listener file on 2 nodes cluster 11gR2

cluster with 2 nodes sun001 and sun002

sun001

VIP         sun001-VIP

HOST IP          HOST=x.x.x.xx 

sun001

VIP         sun002-VIP

HOST IP          HOST=x.x.x.yy

PORT = 1521

Directory for trace and logs /oramisc01/oracle

NOTE : location of listener file is <grid_home> not <db_home>

Starting with Oracle Grid Infrastructure 11g Release 2 the node listener run out of the Oracle Grid Infrastructure home and listens on the node-VIP using the specified port (default port is 1521). 

Ever since Oracle Database 11g Release 2, SCAN is an essential part of the Oracle RAC database configuration and therefore the REMOTE_LISTENER parameter is set to the SCAN per default, assuming that the database is created using standard Oracle tools (e.g. the formerly mentioned DBCA). This allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load and a recommendation on how many incoming connections should be directed to the instance.

Single Client Access Name (SCAN) is a feature used in Oracle Real Application Clusters environments that provides a single name for clients to access any Oracle Database running in a cluster.

SCAN resolves to three IP addresses using a round-robin algorithm. 

You can check the SCAN configuration in DNS using “nslookup”

READ MORE http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf

listener.ora and endpoints_listener.ora are in <grid_home>/network/admin directory

$ cat listener.ora on both nodes

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

# custom parameters

ADR_BASE_LISTENER = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN1 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN2 = /oramisc01/oracle

ADR_BASE_LISTENER_SCAN3 = /oramisc01/oracle

$ cat endpoints_listener.ora on node 1 sun001 (notice sun001–vip, ping sun001-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun001-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.xx)(PORT=1521)(IP=FIRST))))                # line added by Agent

$ cat endpoints_listener.ora on node 2 lnx70177 (notice sun002–vip, ping sun002-vip to verify)

LISTENER_sun001=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=sun002-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.yy)(PORT=1521)(IP=FIRST))))              # line added by Agent

# start/stop sequence

srvctl start listener

srvctl start scan

srvctl start scan_listener

stop in the same order as above

# How to check config

srvctl config  listener

srvctl config  scan

srvctl config  scan_listener

# How to check status

srvctl status listener

srvctl status scan

srvctl status scan_listener

# sample 

set environment to grid home

sun001@grid $  srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl config listener

Name: LISTENER

Network: 1, Owner: oracle

Home: <CRS home>

End points: TCP:1521

sun002@grid $ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): sun001, sun002

# Database parameter settings :  local_listener / remote_listener

The LOCAL_LISTENER parameter should be set to the node-VIP.  

The  REMOTE_LISTENER parameter should be set to the SCAN:port
   

IP-SCAN1, IP-SCAN2, IP-SCAN3 are SCAN  IP addresses of SCAN NAME
IP_VIP   are VIP IP address of NODE 1 and NODE 2

Node 1 : 
in local_listener HOST=VIP IP address of node 1

Node 2: 
in local_listener HOST=VIP IP address of node2

remote_listener remans same on both

sys@DEMO> show parameter list

NAME                            VALUE
------------------------------- ------------------------------
listener_networks            
local_listener                   (ADDRESS=(PROTOCOL=TCP)(HOST=<IP-VIP)(PORT=1521))
                                
remote_listener        (ADDRESS_LIST=
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN1>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN2>)(PORT=1521))
                                (ADDRESS=(PROTOCOL=TCPS)(HOST=<IP-SCAN3>)(PORT=1521)))
                             
 # the end                      

Processing XML files and converting to Oracle Relational Format

# SAMPLE XML File content is shown below.

XMLFILE_AA_11.xml
   <?xml version="1.0" encoding="UTF-8"?>
   <EMPDATA TIME_STAMP="2006-08-07T15:00:42"
    TRANSACTION_ID="2006-08-07T15:00:42" VERSION="1.0">
    <HEADER>
     <SENDER>
      <SENDER_NAME>XYZ Corp</SENDER_NAME>
     </SENDER>
     <RECEIVER>
      <RECEIVER_NAME>ABC Bank</RECEIVER_NAME>
     </RECEIVER>
    </HEADER>
    <BODY>
   <EMP>
         <EMPNUM>12345678</EMPNUM>
         <DEPT>07I7</DEPT>
         <LOCATION>22</LOCATION>
         <HIRE_DATE>2006-08-07 00:12:00.0</HIRE_DATE>
         <FIRST_NAME>JOHN</FIRST_NAME>
         <LAST_NAME>SMITH</LAST_NAME>
    <PHONES>
    <PHONE type="Office">5676739</PHONE>
    <PHONE type="Office">9123412432</PHONE>
    </PHONES>
     <AC_NO>12313-123123-1233</AC_NO>
     <AMOUNT>2500.00</AMOUNT>
   </EMP>
   <EMP>
         <EMPNUM>12345679</EMPNUM>
        <DEPT>07I2</DEPT>
        <LOCATION>22</LOCATION>
        <HIRE_DATE>2006-08-17 00:12:00.0</HIRE_DATE>
        <FIRST_NAME>GREG</FIRST_NAME>
        <LAST_NAME>WINTER</LAST_NAME>
     <PHONES>
    <PHONE type="Office">5676733</PHONE>
    <PHONE type="Office">9123434231</PHONE>
     </PHONES>
   <AC_NO>12313-123144_4354</AC_NO>
   <AMOUNT>2800.00</AMOUNT>
       </EMP>
   </BODY>
  </EMPDATA>

step 1
# create directory (location of xml file)

sqlplus / as sysdba
create directory TEST_DIR  as '/ora01/oracle/admin/demo/temp/';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO scott;


step 2
# Check contents of file
SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual;

step 3
# select few columns only - empnum, ac_no and amount. Remaining tags are not selected
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
    SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/

step 4

# insert selected few columns only - empnum, ac_no and amount in table named employee. Remaining tags are not selected

create table employee (
emp_num varchar2(10),
acct_num varchar2(10),
amount  number
)
/

insert into employee (emp_num, acct_num, amount) 
SELECT EXTRACTVALUE (VALUE (ctba), '/EMP/EMPNUM') emp_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AC_NO') acct_num,
          EXTRACTVALUE (VALUE (ctba), '/EMP/AMOUNT') amount
from (
    SELECT xmltype(bfilename('TEST_DIR','XMLFILE_AA_11.xml'), nls_charset_id('WE8ISO8859P1')) xmlcol FROM dual
) t
cross join table(XMLSequence(extract(t.xmlcol,'/EMPDATA/BODY/EMP'))) ctba
/
commit;

GRANTS to USER

-- gen_drop_objects.sql

   set pages 0 feedb off
   accept inuser char prompt 'Enter Userid: '
   -- spool run_drop_objects_&&inuser..out
   spool run_drop_objects_MAXIMO.out
   SELECT 'DROP ' || OBJECT_TYPE || ' ' || owner||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS;'
   FROM   dba_objects
   WHERE OBJECT_TYPE = 'TABLE' and owner = upper('&inuser')
   union all
   select 'drop '||case
            when object_type='PACKAGE BODY' then 'PACKAGE'
            else object_type
          end
     ||' '||owner||'.'||object_name||' ;'
   from dba_objects
   where owner = upper('&inuser')
   /
   prompt purge dba_recyclebin
   prompt /
   
   spool off
   
   
-- gen_rdonly_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_rdonly_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select '
                                  when 'VIEW'  then 'select '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW')
/
spool off
set pages 30 feedback on

--  gen_grants_to_role.sql

set pages 0 lines 120 feedback off verify off
accept ToRole prompt 'Grants to What Role? '
accept ObjOwner prompt 'From What Owner? '
spool gen_grants_to_role.out
select 'set echo on' from dual;
select 'create role &&ToRole;' from dual;
select 'connect &&ObjOwner' from dual;
select 'grant '||case object_type when 'TABLE' then 'select, insert, update, delete '
                                  when 'VIEW'  then 'select '
                                  when 'SEQUENCE' then 'select '
                                  when 'PROCEDURE' then 'execute '
                                  when 'PACKAGE' then 'execute '
                 end
  ||'on '||object_name||' to &&ToRole;'
from dba_objects
where owner=upper('&&ObjOwner')
  and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE')
/
spool off
set pages 30 feedback on


-- gen_new_grants.sql

set feedback off pages 0 lines 100
spool run_new_grants.out
select 'create synonym SCOTT_USER.'||object_name||' for '||owner||'.'||object_name||';'
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('PROCEDURE','PACKAGE','FUNCTION','TABLE')
minus
select synonym_name from dba_synonyms where table_owner='SCOTT_OWN')
/


select case
       when object_type in ('TABLE') then
       'grant select,insert,update,delete on '||owner||'.'||object_name||' to SCOTT_USER;'
       when object_type in ('VIEW','SEQUENCE') then
       'grant select on '||owner||'.'||object_name||' to SCOTT_USER;'
       else
       'grant execute on '||owner||'.'||object_name||' to SCOTT_USER;'
       end
from dba_objects
where owner='SCOTT_OWN' and object_name in (
select object_name from dba_objects where owner='SCOTT_OWN' and object_type in ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
minus
select distinct table_name from dba_tab_privs where owner='SCOTT_OWN' and grantee='CSX_SCOTT_USER_ROLE')
/
spool off
set feedback on pages 24 lines 140

TOP-N Schema List

SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

 SELECT owner, wsize  "Size in MB"
  FROM ( SELECT owner, sum(bytes/(1024*1024)) wsize, DENSE_RANK() OVER (ORDER BY sum(bytes/(1024*1024)) DESC) sal_rank
           FROM dba_segments group by owner)
 WHERE sal_rank <= 5;

User with Read only Access

CREATE USER scott_user IDENTIFIED BY scott_user 
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
-- From Role Privileges
GRANT COMPANY_RDONLY_ROLE TO scott_user;
GRANT COMPANY_CONNECT TOscott_user;

CREATE OR REPLACE TRIGGER scott_user.after_logon_trg
AFTER LOGON ON scott_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCOTT_OWN';
END;
/