Oracle 11g:Oracle Networking
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
- 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
- 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
- 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
- complete
- fast
- force
- 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.