Создать service name oracle
This article describes how to create database services in single instance databases using the DBMS_SERVICE package. This package was introduced in Oracle 10g, and has been extended with later releases.
In a multitenant database services can be created in a CDB or a PDB. In these examples we will just create them in the CDB, so it looks similar to a non-CDB instance.
Thursday, January 7, 2016
Exceptions
The following table lists the exceptions raised by DBMS_SERVICE package.
Table 95-4 DBMS_SERVICE Exceptions
The service name argument was found to be NULL
The network name argument was found to be NULL
This service name was already in existence
The specified service was not in existence
The specified service was running
The service name was too long
The network name, excluding the domain, was too long
The services layer was not yet initialized
There was an unknown failure
The maximum number of services has been reached
The specified service was not running
The database was closed
The instance name argument was not valid
The network name was already in existence
All attributes specified were NULL
Invalid argument supplied
The database is open read-only
The total length of all running service network names exceeded the maximum allowable length
15 years 3 months + of experience in database administration, performance engineering and software cost optimization. Expert in architecture of large scale product, service features in product developments and several POCs executions. Specialty in Database administration, Database Performance, SQL, PLSQL, Shell, Simple solutions for Designing Performance engineering solutions, actively working on Software Cost Optimization in CLOUD. Always ready for new challenges with simple solutions
************************************************************
Create a Service
We create a new service using the CREATE_SERVICE procedure. There are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly. The only mandatory parameters are the the SERVICE_NAME and the NETWORK_NAME , which represent the internal name of the service in the data dictionary and the name of the service presented by the listener respectively.
We can display information about existing services using the
************************************************************
SAMPLE :
BEGIN
DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',
network_name=>'QPDEV.WORLD'); (or) network_name=>'QPDEV');
END;
/
SQL> begin
dbms_service.create_service(' ORCLTEST ',' ORCLTEST ');
end;
/
PL/SQL procedure successfully completed.
SQL> select SERVICE_ID,name from V$SERVICES;
SQL> begin
DBMS_SERVICE.START_SERVICE('ORCL');
end;
/ 2 3 4
PL/SQL procedure successfully completed.
Examples
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('FAILOVER_TYPE') :='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT') :=86400;
params('FAILOVER_DELAY') :=10;
params('FAILOVER_RETRIES') :=30;
params('commit_outcome') :='true';
params('aq_ha_notifications') :='true';
DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
END;
LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:
Modify a Service
The MODIFY_SERVICE procedure allows us to alter parameters of an existing service. Like the CREATE_SERVICE procedure, there are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly.
dbms_service.create_service :
We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name.
oracle@LINUX201:[~] $ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select SERVICE_ID,name from V$SERVICES;
You can also use more advanced features like failover parameters like below
Stop a Service
The STOP_SERVICE procedure stops an existing service, so it is no longer available for connections via the listener.
The service is still present, but it is no longer active.
************************************************************
Using DBMS_SERVICE
This section contains topics which relate to using the DBMS_SERVICE package.
************************************************************
SQL> select SERVICE_ID,name from V$SERVICES;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples shrept.lst tnsnames.ora
Security Model
The client using this package should have the ALTER SYSTEM execution privilege and the V$SESSION table read privilege.
This package should be installed under SYS schema.
The EXECUTE privilege of the package is granted to the DBA role only.
Overview
DBMS_SERVICE supports the management of services in the RDBMS for the purposes of workload measurement, management, prioritization, and XA/and distributed transaction management.
Oracle Real Application Clusters (RAC) has a functionality to manage service names across instances. This package allows the creation, deletion, starting and stopping of services in both RAC and a single instance. Additionally it provides the ability to disconnect all sessions which connect to the instance with a service name when RAC removes that service name from the instance.
Sites
************************************************************
************************************************************
Tuesday, April 5, 2016
CREATE ORACLE DATABASE SERVICES/SERVICE_NAME
************************************************************
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Start a Service
The START_SERVICE procedure starts an existing service, making it available for connections via the listener.
We can see the service is now active.
CHECK IF THE SERVICE IS CREATED AND STARTED
************************************************************
Disconnect Sessions
The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.
The installation process creates a tnsnames.ora file on each node.
The tnsnames.ora file acts as a repository of net service names. Each net service name is associated with a connect identifier. A connect identifier is an identifier that maps a user-defined name to a connect descriptor. A connect descriptor contains the following information:
The network route to the service, including the location of the listener through a protocol address
The SERVICE_NAME parameter, with the value set to the name of a database service
The SERVICE_NAME parameter you use in the tnsnames.ora file is singular, because you can specify only one service name. The SERVICE_NAME parameter is different from the service_names database initialization parameter. The service_names database parameter defaults to the global database name, a name comprising the db_name and db_domain parameters in the initialization parameter file. When you add service names using SRVCTL or Oracle Enterprise Manager Cloud Control, it lists additional cluster-managed services for the database.
The tnsnames.ora file is located in both the Grid_home \network\admin and Oracle_home \network\admin directories. By default, the tnsnames.ora file is read from the Grid home when Oracle Grid Infrastructure is installed.
With Oracle Clusterware 11 g Release 2 and later, the listener association no longer requires tnsnames.ora file entries. The listener associations are configured as follows:
Oracle Database Configuration Assistant (DBCA) no longer sets the LOCAL_LISTENER parameter. The Oracle Clusterware agent that starts the database sets the LOCAL_LISTENER parameter dynamically, and it sets it to the actual value, not an alias. So listener_ alias entries are no longer needed in the tnsnames.ora file.
The REMOTE_LISTENER parameter is configured by DBCA to reference the SCAN and SCAN port, without any need for a tnsnames.ora entry. Oracle Clusterware uses the Easy Connect naming method with scanname : scanport , so no listener associations for the REMOTE_LISTENER parameter are needed in the tnsnames.ora file.
Example 6-2 Adding a Second Listener to an Oracle RAC Database
If you created a database named orcl1 , to add a second listener, listening on port 2012, use a command similar to the following command to have the database register with both listeners on startup:
Oracle Database Net Services Administrator's Guide for more information about the tnsnames.ora file
The DBMS_SERVICE package lets you create, delete, activate and deactivate services for a single instance.
The chapter contains the following topics:
Delete a Service
The DELETE_SERVICE procedure removes an existing service.
We can see it's not longer listed as an available service.
LEST CREATE A NEW DB SERVICE
How to create service name in oracle database or Add service name to single instance database
Service name can be different from database name or instance name (also know as SID). Default service name is created with same as database name. Service name is like an alias that allow us to connect the db, it can be recorded in tnsnames.ora file or may not be recorded in tnsnames.ora file, it depends what connection method we use to connect.
we can create more than one service name for single database. The way we create service name in single instance db is different from RAC. the method below shown is for single instance db.
Let me explain why did we required to do the below. Most people may wonder why we do have more than 8 character size service name or instance name. Oracle may not recommends but does not stop from creating instance_name with more than 8 character length string. We have a db with more than 8 character length and users want to connect db with 8 character length string with no db downtime, hence we did the below.. and it works.
In real production systems, we use service names as it allows to manage resources, workloads more effectively.
*real strings are changed*
We can also use dbms_service package .
It works as it has entry in tnsnames.ora file
TNS-03505: Failed to resolve name
--- It did not work as we have no entry for this in our tnsnames.ora file.
We still conntect db with new service name
The method we used to connect db is known as EZCONNECT or JDBC connection . EZCONNECT is very easy connect naming method and does not require any edits in tnsnames.ora file.
I will be concentrating mostly on 12c database on this site. you can visit my other blog for knowledge on previous versions of oracle database.
LETS TEST IT :
Constants
The DBMS_SERVICE package uses the constants shown in following tables
Constants used in calling arguments are described in Table 95-1, "Constants used in Calling Arguments"
Constants used in connection balancing goal arguments are described inTable 95-2, "Constants used in Connection Balancing Goal Arguments"
Constants used TAF failover attribute arguments are described inTable 95-3, "Constants used in TAF Failover Attribute Arguments"
Table 95-1 Constants used in Calling Arguments
Disables Load Balancing Advisory
Load Balancing Advisory is based on elapsed time for work done in the service plus available bandwidth to the service
Load Balancing Advisory is based on the rate that work is completed in the service plus available bandwidth to the service
Table 95-2 Constants used in Connection Balancing Goal Arguments
Connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled (either goal_service_time or goal_throughput ). When GOAL = NONE (no load balancing advisory), connection load balancing uses an abridged advice based on CPU utilization.
Balances the number of connections per instance using session count per service. This setting is recommended for applications with long connections such as forms. This setting can be used with Load Balancing Advisory when the connection pool is sized to accommodate gravitation within the pool itself (without adding or removing connections). The latter is the most efficient design.
Table 95-3 Constants used in TAF Failover Attribute Arguments
Server side TAF is not enabled for this service
Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)
Server side TAF type is NONE
Server side TAF failover type is SESSION . At failure time, if the failover type is SESSION , TAF will re-connect to a surviving node and re-establish a vanilla database session. Customizations (for example, ALTER SESSION ) must be re-executed in a failover callback.
Server side TAF failover type is SELECT
Number of retries to use during a failover. Specifies the number of times that TAF should attempt the re-connect and re-authenticate pair. The value must be integral and greater than 0. The maximum value is UB4MAXVAL
Number of seconds delay before trying to failover. Specifies the delay (in seconds) that TAF will incur if the re-connect / re-authentication fails. The value must be integral and greater than 0. The maximum value is UB4MAXVAL .
If a TAF callback has been registered, then the failover retries and failover delay are ignored. If an error occurs, TAF will continue to re-attempt the connect and authentication as long as the callback returns a value of OCI_FO_RETRY . Any delay should be coded into the callback logic
Server side TAF settings override client-side counterparts that might be configured in TNS connect descriptors. If TAF is not configured on the client side, then at a minimum, the failover type must be set to enable TAF. If the failover type is set on the server side, then the failover method will default to BASIC . Delay and retries are optional and may be specified independently.
Читайте также: