Oracle Class notes-day 18th

Oracle NET

1. The client obtains the specific connection information of the server through the @ora10g name to the Tnsname.ora file.

2. The client sends a link request to the server via the description in the Tnsname.ora server side

3. After the server's listener receives the connection request, verifies the validity of the requested service

4. Server-side generates a service process and client processes to establish a connection

To view the session setup process:

$ NETSTAT-TLNP | grep 1521

$ sqlplus sys/[email protected] as Sysdba

$ NETSTAT-TNP | grep sqlplus

$ kill-9 1234 kills the process of maintaining sqlplus

configuration of the listener

Configuration file:

$ VI $ORACLE _home/network/admin/listener.ora

Add a new listener service via NETCA Listener15210, port using 15210

$ VI Listener.ora

$ netstat-tln|grep 1521

$ LSNRCTL Status listener15210

Configuring advanced options with Netmgr

Start/stop/view/Reload Listener/service via LSNRCTL command

Lsnrctl Start|stop|status|reload|service

Specify the name of the listener:

$ lsnrctl status listener15210 monitoring Information

Network environment changes, need to check Listener.ora and/etc/hosts files

NETCA Delete Listener15210

configuration of the instance (database)

Static Registration and dynamic registration

What is static registration

Is that the listener's configuration file describes which instance to listen to and configure the Sid_desc field

You can locate instances by using Sid_name or service_name to locate

What is dynamic registration

Is that the listener's configuration file does not indicate which instance to listen to

To tell a specific instance of the listener to listen through Pmon

Pmon is to tell the listener that the process is to register server_name.

The default one-minute Pmon registration means that the boot listener is not registered when it is not connected

Add 3 ways, delete it later, overwrite it with the document

Differentiate between static and dynamic registrations

Lsnrctl status

Is ready is the dynamic

Yes, Unknow is static.

Static registration Listener.ora File information:

$ VI Listener.ora

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Oracle_home =/u01/app/oracle/product/11.2.0/db_1)

(Sid_name =ora11g)

)

)

$ lsnrctl Reload

.....

Services Summary ...

Service "ora11g" has 1 instance (s).

Instance "ora11g", status UNKNOWN, have 1 handler (s) for the This service ...

The status is always displayed unknown, and when requested, the listener confirms that the data exists

Dynamic registration

Oracle9i The instance uses dynamic service registration to notify the listener about its database service.

Service registration relies on the Pmon process to register instance information with listeners registration interval of about 1 minutes

Manual registration command alter SYSTEM register;

No need to set any information in the Listener.ora file this file can not exist

3 Ways to register:

Local default port listener (Listener and database are not requiredto be configured on one host) 1521

Local non-default port listener (Listener and database in a host, need to be configured, know where the port is, port is not 1521)

Remote monitoring (not on a single host)

Local non-default port:

NETCA creating listener15210, using 15210 ports

$ NETSTAT-TLNP | grep 15210

Write the Listener alias:

$ cd $ORACLE _home/network/admin

$ VI Tnsnames.ora

listener15210 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 15210))

)

Modify Parameters:

sql> ALTER SYSTEM SET local_listener=listener15210;

sql> ALTER SYSTEM Register; Register Now (optional)

The service information has been added to the new listener:

$ LSNRCTL Status listener15210

The client specifies a new port connection:

$ sqlplus sys/[email PROTECTED]:1521/ORCL as SYSDBA error

$ sqlplus sys/[email PROTECTED]:15210/ORCL as Sysdba

To delete a configuration:

sql> ALTER SYSTEM SET local_listener= ";

sql> ALTER SYSTEM Register;

$ vi Tnsnames.ora Delete listener15210 alias

NETCA Delete 15210-port monitoring

Client Configuration

Easy connection:

$ sqlplus sys/[email PROTECTED]:1521/ORCL as Sysdba

Suitable for temporary connections

Local naming:

To view an existing host connection string

$ cd $ORACLE _home/network/admin

$ VI tnsnames.ora parsing

$ sqlplus sys/[email protected] as Sysdba

Add a new host connection string using NETCA orcl192

$ sqlplus sys/[email protected] as Sysdba

Sql> select name from V$database;

Tnsping test, no user name and password required:

$ tnsping 192.168.0.1:1521/ORCL

$ tnsping orcl192

Order of parsing methods:

$ VI Sqlnet.ora

NETCA can be modified

Restore default settings:

Sql> alter system set local_listener= ";

sql> Alter SYTEM Register;

NETCA deleting unwanted listening and connection configurations

Shared Server Mode

Proprietary services and Shared services models

Proprietary mode

Connect between each user process and service process through a listener

Process information is stored in the PGA, that is, how many of the user processes have the PGA generated

Only process connections are not sufficient to operate the database, but also to generate session information

Session information is stored in UGA, and UGA exists in the PGA in proprietary mode

Session information is also relatively independent because processes and processes are isolated from each other

This causes the service process to only know that the session request information for the current user process can only be serviced for the current user process

Sharing mode

A user process request is received by the listener, and the listener does not delegate the server process, but instead returns the scheduler information to the client

The scheduler places requests for user processes into the request queue

A service process in multiple service processes takes a request from a queue for a user process and processes requests from that user process

After the service process finishes processing the processing results into the response queue, each scheduler has its own response queue

Response queue feedback to the corresponding scheduler

The scheduler then returns the results of service process processing to the user process

The session information for a user process in shared mode is visible to each server process.

Because the UGA information for shared mode exists in the SGA, a request for a user process can be done by multiple service processes at this time.

The configuration of the shared server is configured by initializing the parameter dispatchers

Can be modified by DBCA.

$ LSNRCTL Service

Sql> Show Parameter disp

Sql> Show Parameter Shared_server

Proprietary and shared modes are compatible with each other.

At this point, we see how users choose

In Tnsnames.ora

ora10g =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = dba.up.com) (PORT = 1521))

(Connect_data =

(server = dedicated) # Do not write this value is matched by server mode

# shared Specifies the connection using shared mode

# dedicated Specifies the use of proprietary mode connections

(service_name = raw10g)

)

)

Test three modes

Orcl_default = Default

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(Connect_data =

(service_name = ORCL)

)

)

orcl_dedicated = Administrator

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = ORCL)

)

)

orcl_shared = Shared

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(Connect_data =

(SERVER = SHARED)

(service_name = ORCL)

)

)

Test:

$ sqlplus sys/[email Protected]_default as Sysdba

$ sqlplus sys/[email protected]_dedicated as Sysdba

$ sqlplus sys/[email protected]_shared as Sysdba

Sql> Select SID, SERVER, program from v$session where Username= ' SYS ';

Sql> select distinct SID from V$mystat;

Management maintenance, large data import, backup and recovery work is not suitable for sharing methods:

sql> shutdown immediate Shared connection cannot publish administrative commands

Restore the original settings:

DBCA modified to dedicated mode

db Link

Database A accesses the table above remote database B:

In database A, use NETCA to create a host description string Orcl_dblink point to Database B

$ netca

$ tnsping Orcl_dblink

To create a database link in database A:

Sql> CREATE DATABASE link orcl_dblink_hr connect to HR identified by HR using ' orcl_dblink ';

Using the host descriptor string Orcl_dblink

Both the user and the password are on database b

The name of the DB link does not require the same as the host description string

To access tables in database B:

Sql> Select COUNT (*) from [email protected]_dblink_hr;

Sql> Select COUNT (*) from hr.employees, [email protected]_dblink_hr;

Oracle Class notes-day 18th