Oracle 11g:Oracle Networking

/ oracle 11g / By

In this post, we will discuss networking concepts in Oracle Database 11g.

The post will cover different types of networking concepts like listener.ora, tnsnames.ora and sqlnet.ora

we also learn about materialized views and different types of materialized views.

Oracle Networking

Oracle Networking facilitates accessing of data existing on a remote server whether it is client or server, the process of installation is same.

The three important files of Oracle networking are

  1. listener.ora

The listener.ora file is a configuration file that defines the Oracle services to which other systems can connect. By default, listener.ora resides in the following location

$ORACLE_HOME/network/admin

  1. tnsnames.ora

The TNSNAMES.ORA file is used by clients and distributed database servers to identify potential destinations, both servers and, optionally, Interchanges.By default, listener.ora resides in the following location

$ORACLE_HOME/network/admin

  1. sqlnet.ora

sqlnet.ora is a plain-text configuration file that contains the information (like tracing options, encryption, route of connections, external naming parameters etc.) on how both Oracle server and Oracle client have to use Oracle Net (SQL*Net) capabilities for networked database access.

 

By default, samples file is available in

$ORACLE_HOME/network/admin/samples directory

By default, Oracle looks for this files in

$ORACLE_HOME/network/admin directory

We can also place this files in some other location but in this scenario, we need to export the environment variable

$export tns_admin=$HOME

we can configure this files either using netca or manually.

listener.ora has to be configured at the server side and tnsnames.ora must be configured at the client side.

Generally, we use sqlnet.ora in case of third party authentications and to restrict / as sysdba

By default, listener service listens to the port 1521. As a DBA, we must need to monitor for the availability of listener. If listener service is down, we cannot establish a connection to the instance

Instance registration :

Instance registrations with the listener are of two types

1. static registrations

2. dynamic registrations

In case of static registration, we are going to hardcode the instance details in the listener.ora

in case of dynamic registration, we are not going to hardcode the instance details in the listener.ora and PMON process dynamically registers the instance with the listener.

In the industry, occasionally we perform some maintenance activities like

listener.log maintenance activity

alert.log maintenance activity

cleaning up the file system

listener.log maintenance activity:

$lsnrctl

lsnrctl> set log_status off

lsnrctl> exit

$mv listener.log listener_11_11_16.log

$touch listener.log

$lsnrctl

lsnrctl> set log_status on

lsnrctl> exit

Note: Before executing the above statements(commands), set logging_listener=off in listener.ora

Note: If you delete the alert log or if we move the alert log to a different location, by default Oracle creates it.

Database links and Materialized views:

Creating a link from one database to another database is called a database link.

Database link enables accessing of data existing in a remote server from a server.

There are two types of database links.

1.private Database link

2.public Database link

In order to create a database link, a user should have create database link privilege

Using  Database link, we can perform all sorts of DML and select operations but not DDL operations

As the volume of the data increases, accessing data through Database link degrades the performance. To overcome this problem, Oracle has introduced materialized views.

In older versions of Oracle, materialized views are called snapshots. In order to create a materialized view, a user should have create materialized view privilege.

a materialized view is a local copy of remote database object and it is a space demanding object.

Normal view is a stored query and it is not space demanding object.

Sometimes we also create materialized views on local database objects to enable query rewrites. At the time of creating a materialized view, we can specify the refresh interval as well as the refresh mode. Refresh interval means how frequently materialized view has to be refreshed.

The different types of refresh modes are

  1. complete
  2. fast
  3. force
  4. none

In case of complete, Oracle truncates the materialized view and refreshes the entire data. complete is not the feasible solution if the volume of data is very high.

If you want to create a materialized view with a fast option, the source object must and should have primary key and at the source database side, we need to create a materialized view log.

Force means, Oracle tries to refresh based on primary key. If primary key is not available, truncates the materialized view and refreshes the entire data.

None means, Oracle will not refresh the materialized view and we need to manually refresh depending on our requirement

In order to refresh the materialized view, we need to set the parameter “job_queue_processes”, From 10g, by default, this parameter is set to a value 10.

CJQ0, CJQ1, CJQ2 . . . CJQ9

that means CJQ Coordinated Jack Queue process gets enabled.

Also read, Oracle 11g configure networking activity

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.