Oracle Database Administration/Network
This lesson introduces the Oracle database network environment.
Objectives and Skills
editObjectives and skills for the network environment portion of Oracle Database Administration I certification include:[1]
- Configuring the Oracle Network Environment
- Configure and Manage the Oracle Network
- Using the Oracle Shared Server architecture
Readings
editMultimedia
editActivities
editNetwork
edit- Configure the network environment.
- Complete the tutorial Oracle: Configuring the Network Environment.
Listener
edit- Stop and start the listener.
- Review Oracle: Configuring and Administering Oracle Net Listener.
- Use Enterprise Manager to stop and start the listener.
- Use the following commands to view status, stop, and start the Enterprise Manager console service:
lsnrctl status
lsnrctl stop
lsnrctl start
- Use the Windows Services management console to view status, stop, and start the OracleOraDb11g_<network>TNSListener service.
- Test local and remote connections.
- Review Oracle: Net Services.
- 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.
- Use Enterprise Manager,
lsnrctl
, Services, or thenet
command to stop the listener service. - 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.
- Use Enterprise Manager,
lsnrctl
, Services, or thenet
command to start the listener service. - Use SQL*PLUS or SQL Developer to connect to the database from a network client to verify functionality. Do not close the client application.
- Use Enterprise Manager,
lsnrctl
, Services, or thenet
command to stop the listener service. - 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. - Use Enterprise Manager,
lsnrctl
, Services, or thenet
command to start the listener service.
- View listener settings.
- Review Oracle: Managing Oracle Net Services.
- Use Enterprise Manager to view listener settings.
- Use a text editor to open the %ORACLE_HOME%\network\admin\listener.ora file and view listener settings.
- Use Net Manager to view listener settings.
- Use Network Configuration Assistant to view / reconfigure listener settings. Cancel the assistant without saving changes.
TNSNAMES
edit- View and edit tnsnames.ora settings.
- Review Oracle: Local Naming Parameters (tnsnames.ora).
- Use a text editor to open the %ORACLE_HOME%\network\admin\tnsnames.ora file.
- 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. - 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. - 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.
- 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
edit- View sqlnet.ora settings.
- Review Wikipedia: Sqlnet.ora and Oracle: Profile Parameters (sqlnet.ora).
- Use a text editor to open the %ORACLE_HOME%\network\admin\sqlnet.ora file.
- Examine each entry in the file to determine the result of that setting.
Shared Server
edit- Review Oracle: Configuring Oracle Database for Shared Server.
- View the current server environment.
- Open two command prompts, one for viewing listener status and one for SQL*PLUS queries. Connect to Oracle using:
SQLPLUS / AS SYSDBA
- Use the following query to display current processes:
SELECT PROGRAM FROM V$PROCESS ORDER BY PROGRAM;
- Use the following command to view listener status:
lsnrctl status
- Open two command prompts, one for viewing listener status and one for SQL*PLUS queries. Connect to Oracle using:
- Configure the shared server environment.
- Use SQL*PLUS to enable shared server processes with the following query:
ALTER SYSTEM SET SHARED_SERVERS = 20;
- Configure dispatcher processes with the following query:
ALTER SYSTEM SET DISPATCHERS='(PROT=tcp)(DISP=2)';
- Register the configuration with the following query:
ALTER SYSTEM REGISTER;
- 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. - Use the following query to display shared server information:
SELECT * FROM V$SHARED_SERVER;
- Use the following query to display dispatcher information:
SELECT * FROM V$DISPATCHER;
- Use the following command to view the new listener status:
lsnrctl status
Note the additional dispatcher listener configuration.
- Use SQL*PLUS to enable shared server processes with the following query:
- Reset the shared server environment.
- Use the following query sequence to reset the shared server environment:
ALTER SYSTEM SET DISPATCHERS='';
ALTER SYSTEM SET SHARED_SERVERS=0;
ALTER SYSTEM REGISTER;
- Query V$PROCESS, V$SHARED_SERVER, and V$DISPATCHER and view listener status to confirm the configuration.
- Use the following query sequence to reset the shared server environment:
See Also
editReferences
edit