iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 26

"You can tell whether a man is clever by his answers. You can tell whether a man is wise by his questions." -Naguib, Mahfouz

 

Client Side Configuration

The following are the various naming methods supported by Oracle Net.

1- Host Naming Method

2- Local Naming Method

3- Directory Naming Method

4- Oracle Names Method

Host Naming Method

When you using the Host Naming method, a client must specify a username, password, and a connect string. The connect string either should be the hostname or its IP address. The database listener on the server must listen to the port number 1521. This will not work if you change the port number.

 

Now, the SQL NET on the client machine looks for the host in the network, and will attempt to connect to the serverís listener by assuming that there is a listener on port 1521.

Now the listener hears the request, and pass the request to either a dispatcher or dedicated server depending on its configuration.

 

Notice that your global database name must be the same as your machine name (hostname) or alias name. This is an easiest Oracle Network Configuration that you can have. The problem you have is that you cannot have more than one database in your machine, you canít change your machine name or your instance name, and your port must be 1521.

 

The following is an example of how you can use naming resolution in your machine by using /etc/hosts file on the Unix platform:

#IP address of server host name alias

#----------------------- ------------ ---------

256.32.156.72 supper-pc mycomputer

 

The following is an example of parameters that you should set in your listener configuration file (listener.ora).

LISTENER4MYDBS=

     (DESCRIPTION=

          (ADDRESS_LIST=

               (ADDRESS=(PROTOCOL=tcp) (HOST=mycomupter) (PORT=1521))

               (ADDRESS=(PROTOCOL=ipc) (KEY=extproc))

          )

     )

SID_LIST_LISTENER4MYDBS=

     (SID_LIST=

          (SID_DESC=

               (GLOBAL_DBNAME=mydbs.company)

               (ORACLE_HOME=/u01/app/oracle/product/9.2.0)

               (PROGRAM=extproc)

               SID_NAME=mydbs)

          )

     )

 

Local Naming Method

When you using the Local Naming method, the same as the Host Naming method, a client must specify a username, password, and a connect string. The connect string must be your Service Name. The Service Name is an entry that was defined in the TNSNAMES.ORA file. The TNS stand for Transparent Network Substrate. In contrast to the Host Naming Method, the Local Naming Method can use any port number as long as it has not been used. The Oracle default port number is 1521 or 1526.

 

In the Service Name entry in the TNSNAMES.ORA file, you should define the hostname or the IP address of the database server. You use the hostname if the name was define in DNS directory or the /etc/hosts file.

 

For each address (ADDRESS=) you may use a different protocol type such as TCP, NMP, IPC, SPX and etc. If you use TCP, you should specify the host name (or an IP address) and the port number to be used. If you use NMP (Named Pipes) for a network using Novel, you should specify your Machine Name and the Pipe Name. If you use IPC (Interprocess Communication), you should specific the library name that will be accessed on the same node. The IPC protocol will be used only when client and server run on the same machine or node.

 

If you use SPX, the IPX/SPX Microsoft protocol, you should specify a service name. For other protocol, you may have to specify the relevant information for establishing the Oracle connectivity.

Now, the SQL NET on the client machine by using the service name, it looks for the host in the network, and will attempt to connect to the serverís listener by using a specified port number in the listener.

 

Now the listener hears the request, and pass the request to either a dispatcher or dedicated server depending on its configuration.

 

In the Service Name entry, you must define your database instance name (SID) with its Home Oracle ($ORACLE_HOME) address path.

 

This method not only uses the tnsnames.ora file but also the SQLNET.ORA file. You may use the SQLNET.ORA file to identify client preferences such as the database domain, directory path, security, and firewall. These two files are located in the $ORACLE_HOME/network/admin directory.

 

The following shows what the TNSNAMES file typically looks like:

 

mydbs=

     (DESCRIPTION =

          (ADDRESS_LIST=

               (ADDRESS=(PROTOCOL=tcp)(HOST=106.36.197.51)(PORT=1526)

          )

     )

     (CONNECT_DATA=(SID=mydbs)

     (ORACLE_HOME=/u01/app/oracle/product/9.2.0)(SERVER=DEDICATED))

 

----------------OR------------------

 

mydbs=

     (DESCRIPTION =

          (ADDRESS_LIST=

               (ADDRESS=(PROTOCOL=tcp)(HOST=106.36.197.51)(PORT=1526)

          )

     )

     (CONNECT_DATA=

     (SERVICE_NAME=mydbs))

 

The following shows what the SQLNET.ORA file typically looks like:

AUTOMATIC_IPC = OFF

TRACE_LEVEL_CLIENT = OFF

NAMES.DIRECTORY_PATH = (TNSNAMES)

NAMES.DEFAULT_DOMAIN = world

NAME.DEFAULT_ZONE = world

NAMES.DEFAULT_DOMAIN = world

 

Later in this book, you will learn how to create, delete, and maintain the TNSNAMES.ORA and SQLNET.ORA file using the NET Manager utility.

 

To test it, just go to operating system and run the tnsping utility.

Example: (tnsping service_name)

# tnsping mydbs

 

If your connection was successful, you will get a message showing the XXXms (ex: 30ms) number of milli-second took that the client established a connection to the database.

 

Oracle Names Method

When you using the Oracle Names method, the same as the Local Naming method, a client must specify a username, password, and a connect string. The connect string must be your Service Name. In contrast to the local Naming Method, the Oracle Names Method will use the IP address or host name where the Oracle Name Server was installed and is listening. In the Oracle Name Server, a user process will be directed to an assigned destination on the server. In contrast to the local naming Method, the service name doesnít contain the hostname or its IP address.

 

The following are the steps to configure Oracle Names Server.

Components required for setup:

Oracle8/81/9i,

Net8/8i Net Server,

Net8 Client,

Oracle Protocol Adapters,

Oracle Name Server,

Net8 Assistant/Net Manager,

Java Runtime Environment (JRE),

Assistant Common Files.

 

1. Create the required tables for the ONames repository by connecting to the database as onames/onames and run the following script:

MS-DOS> ORACLE_HOME.sql (Windows)

MS-DOS> ORACLE_HOME.sql (Windows)

# $ORACLE_HOME/network/admin/namesini.sql (Unix)

# $ORACLE_HOME/network/admin/namesupg.sql (Unix)

 

2. Use Net8 Assistant and create a Name Server (mynameserver) with the following options:

Protocol = TCP

Host = myserver

Port = 1575

a) Use a Region Database.

b) Enter the listener information:

Protocol = TCP

Host = myserver

Port = 1521

c) Enter the SID name for your repository.

d) Enter the username/password of the Oracle user who ran the NAMESINI.SQL script (onames/onames).

c) Select Yes for NameServer is in the root region.

d) Click Finish

e) Save the Network Configuration.

3. Click on the Ď+í sign next to LOCAL and expand it and then highlight the PROFILE item. In the Naming Methods box remove TNSNAMES and HOSTNAME and then add ONAMES naming Methods. You can also, make the ONAMES naming first and the rest be TNSNAMES and HOSTNAME. Go to Preferred Oracle NameServer and from the drop down menu, enter the NameServer name. Save the Network Configuration.

 

4. Start the NameServer

For Oracle8 (8.0) on Windows:

C:> namesctl80 start

or

C:> namesctl start

 

For Unix

$ namesctl start

 

5. Add service name information to the repository

In the Net8 Assistant (or Netmgr for Oracle9i) click on NameServer and from the drop down menu select Manage Data. Then click Add and then enter the details for your service name, e.g.:

 

Service Name = mynameserver

Protocol = TCP

Host = myserver

Port = 1521

SID = mydbs

 

Then click Execute. Repeat this process for all service names you wish to have. Save Network Configuration and test a connect from SQL*Plus.

If this works your NameServer is set up and working correctly and you can then distribute the sqlnet.ora created to the client PC's or Unix workstations.

 

Setting up Checkpointing for Fail Over

Take the following scenario: If you shutdown your NameServer and repository database, and the database fails to start, you can start your NameServer but no clients will be able to connect. This could lead to a production down situation.

 

This problem can be avoided with the use of checkpoint files. Checkpointing allows all information from the NameServer cache to be written to files. So, when you start the NameServer it will read these files and put everything back in the cache. This obviously gets you around the earlier scenario as users will still be able to connect while you fix your database.

 

Checkpointing of local region data is performed automatically so if you decide to have multiple regions you need to set checkpointing up as follows:

 

1. Load the Net8 Assistant (or Netmgr for Oracle9i).

2. Click on NameServer.

3. Select Manage Server from the drop down menu.

4. Go to the Tuning tab and change the Cache Checkpoint Interval to a frequency you wish the cache to be written to file.

5. Click Apply.

 

Creation of a Second NameServer

Even though checkpointing provides some protection against fail-over it is not 100% full proof.

 

For example, let's say the node on which your NameServer resides goes down. In this situation, no one can connect at all. You can workaround this problem by running another NameServer on a different node. The clients can forward their requests to this secondary server - other all problems will be invisible to the clients.

 

1. Load Net8 Assistant (or Netmgr on Oracle9i).

2. Click on NameServer and click the '+' button.

3. The Names Wizard appears. Enter the NameServer name e.g. backup

4. Select:

Protocol = TCP

Host = server2

Port = 1575

 

5. Use a Region Database.

6. Enter the listener information where the first NameServer resides:

Protocol = TCP

Host = server1

Port = 1521

 

7. Enter the SID name where the repository is.

8. Enter the username and password of the Oracle user who ran the NAMESINI.SQL file e.g. onames/onames.

9. Select NameServer is NOT in the root region.

10. Leave the domain name blank

11. Enter the address information of the first NameServer, e.g.:

Protocol = TCP

Host = server1

Port = 1575

 

12. Click Finish.

13. In profile, have the second NameServer as your first preferred NameServer and have the original server as your second preferred NameServer. If you don't have it in this order your second NameServer won't start.

14. The Next step is to start the 2nd NameServer:

 

For Oracle8 (8.0) on Windows:

C:> namesctl80 start

 

For Unix

$ namesctl start

Again, you can set-up checkpointing as described earlier in this note.

 

SAMPLE CONFIGURATION

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

Server 1: sun1.oracle.com

~~~~~~~~~~~~~~~~~~~~~~~~~

# NAMES.ORA Configuration File:/install/app/oracle/product/8.0.5/network/admin/names.ora

# Generated by Oracle Net8 Assistant

NAMES.SERVER_NAME=sun1_onames.oracle.com

NAMES.ADDRESSES=

(ADDRESS=(PROTOCOL=TCP)(HOST=sun1.oracle.com)(PORT=1575))

NAMES.ADMIN_REGION=

(REGION=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=sun1.oracle.com)(PORT=1521))

(CONNECT_DATA=(SID=V805)(Server=Dedicated))

)

(USERID=names)

(PASSWORD=names)

(NAME=LOCAL_REGION)

(REFRESH=86400)

(RETRY=60)

(EXPIRE=600)

(VERSION=134230016)

)

 

# SQLNET.ORA Configuration File:/install/app/oracle/product/8.0.5/network/admin/sqlnet.ora

# Generated by Oracle Net8 Assistant

NAMES.PREFERRED_SERVERS =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL=TCP)(HOST=sun1.oracle.com)(PORT=1575))

)

NAMES.DEFAULT_DOMAIN = oracle.com

SQLNET.EXPIRE_TIME = 0

NAMES.DIRECTORY_PATH= (ONAMES)

 

Server 2: hp1.oracle.com

~~~~~~~~~~~~~~~~~~~~~~~~

# NAMES.ORA Configuration File:/apps/app/oracle/product/8.0.5/network/admin/names.ora

# Generated by Oracle Net8 Assistant

NAMES.SERVER_NAME = hp1_onames.oracle.com

NAMES.ADDRESSES =

(ADDRESS=(PROTOCOL=TCP)(HOST=hp1.oracle.com)(PORT=1575))

NAMES.ADMIN_REGION =

(REGION =

(DESCRIPTION =

(ADDRESS=(PROTOCOL=TCP)(HOST=sun1.oracle.com)(PORT=1521))

(CONNECT_DATA=(SID=V805)(Server=Dedicated))

)

(USERID = names)

(PASSWORD = names)

(NAME = LOCAL_REGION)

(REFRESH = 86400)

(RETRY = 60)

(EXPIRE = 600)

(VERSION = 134230016)

)

 

# SQLNET.ORA Configuration File:/apps/app/oracle/product/8.0.5/network/admin/sqlnet.ora

# Generated by Oracle Net8 Assistant

NAMES.PREFERRED_SERVERS =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL=TCP)(HOST=hp1.oracle.com)(PORT=1575))

)

NAMES.DEFAULT_DOMAIN = oracle.com

SQLNET.EXPIRE_TIME = 0

NAMES.DIRECTORY_PATH= (ONAMES)

 

Client Configuration

~~~~~~~~~~~~~~~~~~~~

# SQLNET.ORA Configuration File:/install/app/oracle/product/8.0.5/network/admin/sqlnet.ora

# Generated by Oracle Net8 Assistant

NAMES.PREFERRED_SERVERS =

(ADDRESS_LIST =

(ADDRESS=(PROTOCOL = TCP)(HOST=sun1.oracle.com)(PORT=1575))

(ADDRESS=(PROTOCOL = TCP)(HOST=hp1.oracle.com)(PORT=1575))

)

 

NAMES.DEFAULT_DOMAIN = oracle.com

SQLNET.EXPIRE_TIME = 0

NAMES.DIRECTORY_PATH= (ONAMES)

 

"I suggest that the only books that influence us are those for which we are ready, and which have gone a little further down our particular path than we have gone ourselves." -E. M. Forster

 

Questions:

Q: What are the various naming method supported by Oracle Net?

Q: Describe the Host Naming Method.

Q: Describe the Local Naming Method?

Q: Describe the Oracle Names Method?