Create and Manage Multiple Network Configuration Files

1. Documentation in Tahiti -> Masters Book List -> Administrator’s Guide -> Net Services Administrator’s Guide

Documentation Tahiti -> Masters Book List -> Administrator’s Guide -> Net Services Reference

2. In this order asking us to create and properly handle the following network configuration files “listener.ora”, “sqlnet.ora” and “tnsnames.ora”. By default, its location is $ ORACLE_HOME / admin / network but can be located in any directory as long as we set the session variable $ TNS_ADMIN.

3. We begin with the listener.ora configuration file. In the LISTENER settings (obvious, right?) Is defined.

Documentation Tahiti -> Masters Book List -> Administrator’s Guide -> Net Services Administrator’s Guide -> 9 Configuring and Administering Oracle Net Listener

# Check if you have defined the variable $ TNS_ADMIN in the CMO machine (user Oracle)
echo $ TNS_ADMIN
# Do not we have defined it, then do not have to show anything, so we go to the $ ORACLE_HOME / network / admin
cd $ ORACLE_HOME / network / admin
# Check if we have the listener.ora configuration file
listener.ora ls -l
# We have not created any yet, so we did not have any file should appear
# If we had one, always remember to back up whenever you modify, it is good practice
# For example => cp -p listener.ora listener.ora.20121227

# We can start a LISTENER without the listener.ora file
# This is the default setting (implicit) when there is no file listener.ora => (ADDRESS = (PROTOCOL = tcp) (HOST = host_name) (PORT = 1521))
lsnrctl start
# Check the status of LISTENER
# If you have started the BD, the PMON process attempt to register the BD own services in the LISTENER every 60 seconds
lsnrctl status # If we have waited long enough OCMXDB see the CMO and services (the latter is used for XML DB)
# You can also view the status of services in detail
lsnrctl services
# I remind you that we are not specifying the name of LISTENER, because it has the default name
# This statement is equivalent to previous
lsnrctl services LISTENER

# Now let's do a manual configuration file "listener.ora"
# The listener.ora file contains configuration entries of one or more LISTENER
# Stopped the LISTENER
lsnrctl stop
# We can use the example that comes in the documentation as a guide "http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#CCHCIJFE"
# Add these lines to the file $ ORACLE_HOME / network / admin / listener.ora and modify variables "HOST", "GLOBAL_DBNAME", "SID_NAME" and "ORACLE_HOME"
LISTENER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp) (HOST = ocm.dbajunior.com) (PORT = 1521))
      (ADDRESS = (PROTOCOL = ipc) (KEY = extproc))))
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CMO)
      (ORACLE_HOME = / u01 / app / oracle / product / 11.2.0 / dbhome_1)
      (SID_NAME = COM))
    (SID_DESC =
      (SID_NAME = plsextproc)
      (ORACLE_HOME = / u01 / app / oracle / product / 11.2.0 / dbhome_1)
      (PROGRAM = extproc)))
# We raise the LISTENER
lsnrctl start

# Consider the status of the services of the LISTENER
lsnrctl status
# It is worth noting the following line => "Instance" OCM ", status UNKNOWN, you have one handler (s) for esta service ..."
# This means that the OCM OCM instance attending the service has been registered statically (through input SID_LIST_LISTENER "listener.ora" file)
# The other entries with STATUS = READY are dynamically registered by the PMON process

4. Now it is the turn of “tnsnames.ora”. This file aliases are introduced to connect descriptors to a LISTENER a DB.

# Create the file “tnsnames.ora” manually (then an assistant will practice com)
# We use the example of documentation “http://docs.oracle.com/cd/E11882_01/network.112/e10836/naming.htm#BABIDHJB”
# Add these lines to the file $ ORACLE_HOME / network / admin / tnsnames.ora
CMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp) (HOST = ocm.dbajunior.com) (PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = COM)))
# Check that you can connect to the service using OCM input file tnsnames.ora
tnsping ocm
# We try to enter with SQL * Plus to verify that both file (tnsnames.ora and listener.ora) are correct
sqlplus system @ CMO
# We can review the log of the connection with the LISTENER in the file /u01/app/oracle/diag/tnslsnr/ocm/listener/trace/listener.log
# We will see a similar entry => 28-DEC-2012 9:52:29 * (CONNECT_DATA = (SERVICE_NAME = CMO) (CID = (PROGRAM = sqlplus) … * Establish * COM * 0

5. There remains the sqlnet.ora file. In this file various settings SQL Net (both client and server) configuration. In principle it is not necessary since the default values ​​are correct.

# Add the following lines to the file $ ORACLE_HOME / network / admin / sqlnet.ora
NAMES.DIRECTORY_PATH = (tnsnames, ldap, EZConnect)
ADR_BASE = / u01 / app / oracle
# We tested a connection to Easy Connect (EZConnect)
sqlplus / nolog
# Now we run the following statement
# - CONNECT system / ****** @ ocm.dbajunior.com:1521/OCM

# Let's see what happens if we remove the option EZConnect
# Edit the file $ ORACLE_HOME / network / admin / sqlnet.ora and modify the following line
NAMES.DIRECTORY_PATH = (tnsnames, ldap, EZConnect)
# For this other
NAMES.DIRECTORY_PATH = (tnsnames, ldap)
# We again try to connect in the same way as in the previous year
sqlplus / nolog
# Now we run the following statement and see what gives us the error ORA-12154
# - CONNECT system / ****** @ ocm.dbajunior.com:1521/OCM
# We modify the sqlnet.ora file again leaving it was like with the "NAMES.DIRECTORY_PATH = (tnsnames, ldap, EZConnect)" parameter

6. We will now recreate the three files but with the assistant NETCA. We started creating the LISTENER

# LISTENER and we stopped the backup that we created
lsnrctl stop
mv listener.ora listener.ora.20121228
mv tnsnames.ora tnsnames.ora.20121228
mv sqlnet.ora sqlnet.ora.20121218
# Run the wizard NETCA
netca

https://i2.wp.com/www.dbajunior.com/wp-content/uploads/2013/01/NETCAWelcomePage.png

Select “Listener configuration” ->
-> Click “Next” ->
-> Select “Add” ->
-> Click “Next” ->
-> Leave the default name “LISTENER” ->
-> Click “Next” ->
-> Highlight your IPC and add it to the “Selected Protocols” list ->
-> Click “Next” ->
-> We leave default port 1521 “Use the standard port number of 1521” ->
-> Click “Next” ->
-> IPC Key value = “extproc” ->
-> Click “Next” ->
-> Select “No” ->
-> Click “Next” ->
-> Click “Next”

# We have recorded in $ ORACLE_HOME / network / configuration admin / listener.ora
# Also has raised the LISTENER, we checked
cat $ ORACLE_HOME / network / admin / listener.ora
lsnrctl status LISTENER

7. Using the same wizard will create the tnsnames.ora file

In the initial screen of the wizard ->
-> Select “Local Net Service Name configuration” ->
-> Click “Next” ->
-> Click “Add” ->
-> “Click” Next “->
-> Service Name = “OCM” ->
-> Click “Next” ->
-> Click “TCP” ->
-> Click “Next” ->
-> Host name = “ocm.dbajunior.com” ->
-> Click “Next” ->
-> Select “Yes, perform a test ->
-> Click “Next” ->
-> We will give an error (ORA-01017) Click “Change Login” ->
-> Username = “system” and Password = ->
-> Click “OK” ->
-> This time must leave “Connecting … Test succesful.” ->
-> Click “Next” ->
-> Net Service Name = “OCM” ->
-> Click “Next” ->
-> Click “No” ->
-> Click “Next” ->
-> Click “Next”

8. Now we generate the sqlnet.ora file, also with assistant NETCA

In the initial screen of the wizard ->
-> Select “Naming Methods configuration” ->
-> Click “Next” ->
-> Move methods “Local Naming”, “Directory Naming” and “Easy Connect Naming” ->
-> Click “Next” ->
-> Click “Next” ->
-> Click “Finish”

9. When using a different port to 1521 LOCAL_LISTENER have to define the variable. Here’s an example:

# Add the following lines to the file $ ORACLE_HOME / rdbms / admin / listener.ora
LISTENER_TEST =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 50000))
     )
   )
# Add the following entries to the file $ ORACLE_HOME / rdbms / admin / tnsnames.ora
LISTENER_TEST =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 50000))
     )
   )
# We raise the LISTENER LISTENER_TEST
lsnrctl start LISTENER_TEST

Finally, we have to modify the parameter of the DB LOCAL_LISTENER

- We go to the database as SYS and execute the instruction siguietne
ALTER SYSTEM SET LOCAL_LISTENER = 'LISTENER_TEST' SCOPE = BOTH;

We can see, in less than a minute, and services are recorded BD

# Check the services registered in the listener LISTENER_TEST
lsnrctl status LISTENER_TEST

Remove the last LISTENER (LISTENER_TEST), and we will stay with the usual (over port 1521)

# Stopped the listener LISTENER_TEST
lsnrctl stop LISTENER_TEST
# We remove the entry into the listener.ora file on LISTENER_TEST
# We remove the entry in the tnsnames.ora file on LISTENER_TEST

Finally, we return to modify the parameter LOCAL_LISTENER

- We went with SQL * Plus and resetamos parameter (thus we do not restart the instance)
ALTER SYSTEM RESET LOCAL_LISTENER SCOPE = SPFILE;
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 1521))' SCOPE = MEMORY;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s