Oracle Database Administration/Network

This lesson introduces the Oracle database network environment.

Objectives and Skills

Objectives and skills for the network environment portion of Oracle Database Administration I certification include:

• Configuring the Oracle Network Environment
  • Configure and Manage the Oracle Network
  • Using the Oracle Shared Server architecture

Readings

1. Oracle: Database 2-Day DBA Configuring the Network Environment

Multimedia

1. YouTube: Understanding the tnsnames.ora and listener.ora files

Activities

Network

1. Configure the network environment.
   1. Complete the tutorial Oracle: Configuring the Network Environment.

Listener

1. Stop and start the listener.
   1. Review Oracle: Configuring and Administering Oracle Net Listener.
   2. Use Enterprise Manager to stop and start the listener.
   3. Use the following commands to view status, stop, and start the Enterprise Manager console service:
      lsnrctl status
lsnrctl stop
lsnrctl start
   4. Use the Windows Services management console to view status, stop, and start the OracleOraDb11g_<network>TNSListener service.
2. Test local and remote connections.
   1. Review Oracle: Net Services.
   2. Use SQL*PLUS or SQL Developer to connect to the database from the server and from a network client to verify functionality. Close the client application.
   3. Use Enterprise Manager, lsnrctl, Services, or the net command to stop the listener service.
   4. Use SQL*PLUS or SQL Developer to connect to the database from the server and from a network client to verify functionality. The local server connection should still work, while the network client connection will fail.
   5. Use Enterprise Manager, lsnrctl, Services, or the net command to start the listener service.
   6. Use SQL*PLUS or SQL Developer to connect to the database from a network client to verify functionality. Do not close the client application.
   7. Use Enterprise Manager, lsnrctl, Services, or the net command to stop the listener service.
   8. Use the following query to select instance information:
      SELECT * FROM V$INSTANCE;
      Stopping the listener prevents new network connections but does not interrupt existing connections.
   9. Use Enterprise Manager, lsnrctl, Services, or the net command to start the listener service.
3. View listener settings.
   1. Review Oracle: Managing Oracle Net Services.
   2. Use Enterprise Manager to view listener settings.
   3. Use a text editor to open the %ORACLE_HOME%\network\admin\listener.ora file and view listener settings.
   4. Use Net Manager to view listener settings.
   5. Use Network Configuration Assistant to view / reconfigure listener settings. Cancel the assistant without saving changes.

TNSNAMES

1. View and edit tnsnames.ora settings.
   1. Review Oracle: Local Naming Parameters (tnsnames.ora).
   2. Use a text editor to open the %ORACLE_HOME%\network\admin\tnsnames.ora file.
   3. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@orcl
      The connection should work, using the orcl connection information found in tnsnames.ora.
   4. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@test
      The connection should fail, because there is no test service entry in the tnsnames.ora file.
   5. In tnsnames.ora, copy the existing orcl service entry and paste it after the existing entry. Rename the copied service entry as TEST. Leave all other information as is. Save the updated tnsnames.ora file.
   6. Use the following command to connect to the local server using this tnsnames.ora file:
      sqlplus system/<password>@test
      The connection should now work, because there is a test service entry in the tnsnames.ora file.

SQLNET

1. View sqlnet.ora settings.
   1. Review Wikipedia: Sqlnet.ora and Oracle: Profile Parameters (sqlnet.ora).
   2. Use a text editor to open the %ORACLE_HOME%\network\admin\sqlnet.ora file.
   3. Examine each entry in the file to determine the result of that setting.

Shared Server

1. Review Oracle: Configuring Oracle Database for Shared Server.
2. View the current server environment.
   1. Open two command prompts, one for viewing listener status and one for SQL*PLUS queries. Connect to Oracle using:
      SQLPLUS / AS SYSDBA
   2. Use the following query to display current processes:
      SELECT PROGRAM FROM V$PROCESS ORDER BY PROGRAM;
   3. Use the following command to view listener status:
      lsnrctl status
3. Configure the shared server environment.
   1. Use SQL*PLUS to enable shared server processes with the following query:
      ALTER SYSTEM SET SHARED_SERVERS = 20;
   2. Configure dispatcher processes with the following query:
      ALTER SYSTEM SET DISPATCHERS='(PROT=tcp)(DISP=2)';
   3. Register the configuration with the following query:
      ALTER SYSTEM REGISTER;
   4. Use the following query to display current processes:
      SELECT PROGRAM FROM V$PROCESS ORDER BY PROGRAM;
      Note the addition of the D0?? and S0?? processes.
   5. Use the following query to display shared server information:
      SELECT * FROM V$SHARED_SERVER;
   6. Use the following query to display dispatcher information:
      SELECT * FROM V$DISPATCHER;
   7. Use the following command to view the new listener status:
      lsnrctl status
      Note the additional dispatcher listener configuration.
4. Reset the shared server environment.
   1. Use the following query sequence to reset the shared server environment:
      ALTER SYSTEM SET DISPATCHERS='';
ALTER SYSTEM SET SHARED_SERVERS=0;
ALTER SYSTEM REGISTER;
   2. Query V$PROCESS, V$SHARED_SERVER, and V$DISPATCHER and view listener status to confirm the configuration.

References

← Instances

Oracle Database Administration

Architecture →