Managing Oracle Networking Using - Net Manager
Introduction
ORACLE NET facilitates the sharing of data between, even if those Databases do not reside on the same server, but on different Servers running different Operating Systems and Communications Protocols. Each Database Server in the distributed database environment cooperates to maintain the consistency of the global database.
Configuring listener.ora and tnsnames.ora:
If you are connected to a database DEMO on a machine named 'LOCAL_HOST' and would like to get the information from ORCL database on a machine named 'REMOTE_HOST' having the user SYSTEM/MANAGER, then the steps to be taken for establishing a connection through SQL*NET are as follows:
At machine remote host(server side machine)
$cd $ORACLE_HOME/network/admin
$vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (KEY = ORCL)
(ADDRESS = (PROTOCOL=TCP) (HOST = 192.168.0.15) (PORT = 1521)
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /oraeng/app/oracle/product/10.2.0)
)
)
/* Save the file and start the listener */
/* Issue the command to start the listener */
$lsnrctl start listener
At machine local host(client side machine)
$cd $ORACLE_HOME/network/admin
$vi tnsnames.ora
MYALIAS=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) ( HOST = 192.168.0.15) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
/* At local host */
$sqlplus system/manager@myalias
Database Links:
Database links are used to access schema objects at remote database from the local database.
SQL>CREATE DATABASE LINK <linkname> CONNECT TO <username> IDENTIFIED BY <password> USING '<aliasname>';
To create database link at the local database which connects to the remote database user issue
SQL>GRANT CREATE DATABASE LINK TO scott;
SQL>CONNECT scott/tiger
SQL>CREATE DATABASE LINK lnk1 CONNECT TO system IDENTIFIED BY manager USING 'my_alias';
Gives the Table information of user system.
SQL>SELECT * FROM tab@lnk1;
Database Links do not support DDL (Create, Alter, Drop, etc.,) operations. You can use DML operations such as Insert, Update and Delete using database links.
To drop a database link issue the command,
SQL>DROP DATABASE LINK <link name>;
Global Names:
If database participates in a global network with more number of databases, and there are two or more databases with same names, then it becomes difficult to address each of the database differently. Oracle offers a solution to this problem, by enabling the GLOBAL NAMING concept. The Global Name for a database in Oracle follows the pattern "DB_NAME.DB_DOMAIN", where the domain_name for machine is always unique. Also Oracle imposes you to create database links pointing to remote databases in the similar pattern "DB_NAME.DB_DOMAIN".
Set the following parameters in the initialization file (init<ORACLE_SID>.ora) to enable GLOBAL NAMES:
-DB_NAME=ORCL
-DB_DOMAIN=wilshiresoft.com
-GLOBAL_NAMES=TRUE
Start the database and rename the global_name for the database by issuing the following statement:
SQL>ALTER DATABASE RENAME global_name TO orcl.wilshiresoft.com;
Materialized Views:
MATERIALIZED VIEWs can be thought of as a table that holds the results of a query, usually on one or more tables, called master tables, in a remote database. When MATERIALIZED VIEWs are used, a refresh interval is established to schedule refreshes of replicated data. Local updates can be prevented, and transaction based refreshes can be used, available for some type of MATERIALIZED VIEWs, send from the master database only those rows that have changed for the MATERIALIZED VIEW. You need CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE VIEW and CREATE INDEX privileges. The queries that form the basis of MATERIALIZED VIEWs are grouped into two categories.
Simple and Complex MATERIALIZED VIEWs:
Simple MATERIALIZED VIEW's queries should not have a GROUP BY, CONNECT BY clause, a subquery, join conditions or set operations. If a MATERIALIZED VIEW's query has any of these clauses or operations, it is referred to as a Complex MATERIALIZED VIEW. When a MATERIALIZED VIEW is created several internal objects are created in schema of the MATERIALIZED VIEW. These objects should not be altered. To create a MATERIALIZED VIEW the steps are as follows:
Syntax:
SQL>CREATE MATERIALIZED VIEW <materialized view name>
REFRESH [COMPLETE | FAST]
WITH [PRIMARY KEY | ROWID]
START WITH SYSDATE
NEXT SYSDATE+1/(24*60*60) # (For every second)
AS SELECT * FROM <username.table_name@linkname>;
If you create a MATERIALIZED VIEW with refresh fast option, then you need to create a MATERIALIZED VIEW log on the table at the Remote site (i.e., at the server side).
SQL>CREATE MATERIALIZED VIEW LOG ON <table_name>;
COMPLETE:
Entire data is regenerated every time the MATERIALIZED VIEW is refreshed.
FAST:
Only the rows that are modified are regenerated every time the MATERIALIZED VIEW is refreshed using the MATERIALIZED VIEW log. Changed information is stored in the MATERIALIZED VIEW log. MATERIALIZED VIEW log is a table in the master database that is associated with the master table. Oracle uses a MATERIALIZED VIEW log to track the rows that have been updated on the master table.
Eg: If LNK1 has order table, on which I want to create the MATERIALIZED VIEW then,
SQL>CREATE MATERIALIZED VIEW snap1
REFRESH COMPLETE
WITH ROWID
START WITH SYSDATE
NEXT SYSDATE + 1 / (24*60*60)
AS SELECT * FROM jones.order@link1;
If you want to make your snap1 to refresh fast then,
SQL>ALTER MATERIALIZED VIEW snap1 REFRESH FAST;
If you want to drop the MATERIALIZED VIEW then,
SQL>DROP MATERIALIZED VIEW snap1;
However without seeing the following parameters in the init<ORACLE_SID>.ora, which starts the necessary background processes (snp0), the MATERIALIZED VIEWs will not get refreshed automatically.
JOB_QUEUE_PROCESSES=3
Two Phase Commit:
ORACLE automatically controls and monitors the commit or rollback of a distributed transaction and maintains the integrity of the global database using a mechanism called two phase commit. This mechanism guarantees that the nodes participating in a distributed transaction either commit or rollback the transaction, there by maintaining the integrity. It has two phases:
Prepare Phase:
The initiating node asks all the participants to prepare (either to commit or to rollback, even if there is failure).
Commit Phase:
If all participants respond to the initiating node that they are prepared, the initiating node asks all nodes to commit the transaction, if all participants cannot prepare, it asks to rollback the transaction. If there is failure of transaction due to any reason, the status of transaction is recorded in commit point site. Commit point decides the commit point strength at the beginning. All transactions are automatically resolved by RECO and automatically removed from the pending transaction table.
Note:
The MATERIALIZED VIEW can be manually refreshed using the DBMS package,
SQL>EXECUTE DBMS_MVIEW.REFRESH ('materialized viewname','refresh_option');
The Option parameter is to indicate whether MATERIALIZED VIEW has been created with COMPLETE or FAST option.
Networking with Easy connect naming Method:
Clients can connect to Oracle Database using easy connect naming if the following conditions are met:
-Oracle Net Services software installed on the clinet.
-Oracle TCP/IP protocol support on both the client and database server
-No features requiring a more advanced connect descriptor are required
WITH NON-DEFAULT LISTENER:
Server - side configuration (dba11g)
[oracle11g@dba11g ~]$vi listener.ora
demolist =
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dba11g)(PORT=1600))
)
)
)
SID_LIST_demolist=
(SID_LIST=
(SID_DESC=
(SID_NAME=demo)
(ORACLE_HOME=/oraeng/app/oracle/product/11.1.0)
)
)
[oracle11g@dba11g ~]$lsnrctl start demolist
Client-side configuration : testdba
[oracle11g@testdb ~]$sqlplus system/manager@dba11g:1600/demo
SYSTEM>>select username,machine,terminal from v$session;
With Default Listener Configuration:
Server-side configuration (dba11g)
[oracle11g@dba11g ~]$vi listener.ora
LISTENER =
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=dba11g)(PORT=1521))
)
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=demo)
(ORACLE_HOME=/oraeng/app/oracle/product/11.1.0)
)
)
[oracle11g@dba11g ~]$lsnrctl start
Client-side configuration:
[oracle11g@testdba ~]$sqlplus system/manager@dba11g/demo
SYSTEM>>select username, machine, terminal from v$session;
Configuring Listener.ora and Tnsnames.ora
ORACLE NET facilitates the sharing of data between, even if those
Databases do not reside on the same server, but on different Servers
running different Operating Systems and Communications Protocols. Each
Database Server in the distributed database environment cooperates to
maintain the consistency of the global database.
Configuring listener.ora and tnsnames.ora:
If you
are connected to a database DEMO on a machine named 'LOCAL_HOST' and
would like to get the information from ORCL database on a machine named
'REMOTE_HOST' having the user SYSTEM/MANAGER, then the steps to be taken
for establishing a connection through SQL*NET are as follows:
At machine remote host(server side machine)
$cd $ORACLE_HOME/network/admin
$vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (KEY = ORCL)
(ADDRESS = (PROTOCOL=TCP) (HOST = 192.168.0.15) (PORT = 1521)
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = /oraeng/app/oracle/product/10.
)
)
/* Save the file and start the listener */
/* Issue the command to start the listener */
$lsnrctl start listener
At machine local host(client side machine)
$cd $ORACLE_HOME/network/admin
$vi tnsnames.ora
MYALIAS=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) ( HOST = 192.168.0.15) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
/* At local host */
$sqlplus system/manager@myalias
No comments:
Post a Comment