Services and backups

Services and backups
I’ve been a proponent of using services both to help categorize database connections and to use them to help classify workload. For applications where “you can’t touch the code” this can be the only way to come up with categories to measure application workload. Recently I’ve started using a “backup” service to identify the cost backups contribute to overall system workload. The problem with services however is that there are only limited ways to associate a service to a connection. For “internal” connections a service can be associated with a scheduler job through a job class, and can also be associated with parallel execution or parallel DML, or with advanced queuing (although I’ve only used scheduler jobs with services). As for client connections, the service is assigned with the “SERVICE_NAME” parameter on the connect string. This implies a connection through the listener since this is typically part of Oracle net services. This can pose a problem for backups since this creates, in my opinion, an unnecessary dependence on the listener being up in order to take advantage of running the backup process through a service.
Typically a backup program, and we’ll talk about RMAN here but it could be any product really, will connect to the database through a “bequeath” connection by setting the ORACLE_SID environment variable as part of the environment setup. This type of connection doesn’t require that a listener be available, and also doesn’t need a tnsnames.ora entry. Unfortunately there is also no way to associate the connection with a service that I’ve found. However, I have stumbled upon a solution. It turns out that it is possible to define a “bequeath” connection in the tnsnames.ora file and subsequently use the SERVICE_NAME parameter as part of the connect string. This type of connection, even though part of the tnsnames.ora file, doesn’t require a listener to work. I stumbled upon this in Metalink note 132764.1, and it seems that it has always existed, at least since Oracle7 and SQL Net V2, based on some of the other notes available on Metalink.
The following will show an example of setting up the TNS entry and then setting up the backup service and running RMAN connected to the new backup service.
The first task is to create the service in the database. The following example is for a single instance database, in RAC you should use srvctl.
SQL> show parameters service

NAME TYPE VALUE
---------------------------- ----------- -------------------------------------------
service_names string orcl.appsdba.com
SQL> alter system set service_names = "orcl.appsdba.com,backup.appsdba.com" scope=both;

System altered.

SQL> show parameters service

NAME TYPE VALUE
---------------------------- ----------- -------------------------------------------
service_names string orcl.appsdba.com,backup.appsdba.com
SQL>
Verify the listener service registration:
$ lsnrctl services

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:12:00

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "backup.appsdba.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.appsdba.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
$
Let’s show the service registration in the database:
SQL> select * from v$services where service_id = 10;

SERVICE_ID NAME NAME_HASH
---------- -------------------------- ----------
NETWORK_NAME
------------------------------------------------
CREATION_ CREATION_DATE_HASH
--------- ------------------
10 backup.appsdba.com 833319543
backup.appsdba.com
25-JUN-08 638794112

SQL>
Now, we’ll show the tnsnames.ora entry. Note that the protocol is “beq” for bequeath and that we’ve used the SERVICE_NAME for our backup service. Also note that as part of the definition we’ve concatenated the “oracle” executable name and our oracle SID name for the ARGV0 value.
ORCL_BACKUP =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = BEQ)
(PROGRAM = oracle)
(ARGV0 = oracleorcl)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
)
(CONNECT_DATA =
(SERVICE_NAME = backup.appsdba.com)
)
)
Now let’s test the database connection:
$ sqlplus system@orcl_backup

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:19:06 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select USERNAME, SERVER, PROGRAM, STATUS, SERVICE_NAME
from v$session where username = 'SYSTEM';

USERNAME SERVER PROGRAM STATUS
------------------------------ --------- ---------------------------------- --------
SERVICE_NAME
----------------------------------------------------------------
SYSTEM DEDICATED sqlplus@appsdba.com (TNS V1-V3) ACTIVE
backup.appsdba.com

SQL>
So, we’ve connected to the database and verified through the v$session view that we’ve connected to our backup service.
Now let’s connect with RMAN:
$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN>
In another session we’ll query v$session again:
SQL> l
1 select username, status, program, service_name from v$session
2* where username = 'SYS'
SQL> /

USERNAME STATUS PROGRAM
------------------------------ -------- --------------------------------
SERVICE_NAME
----------------------------------------------------------------
SYS ACTIVE sqlplus@appsdba.com (TNS V1-V3)
SYS$USERS

SYS INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SYS INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SQL>
Now let’s verify that we can still connect with no listener:
/u01/app/oracle/local/bkup$ lsnrctl stop

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:33:09

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
The command completed successfully
/u01/app/oracle/local/bkup$
Now let’s see if we can connect with our normal TNS alias:
/u01/app/oracle/local/bkup$ sqlplus system@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:33:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener

Enter user-name:
And no, we can’t because the listener is down. Now let’s see if our backup connection will work:
/u01/app/oracle/local/bkup$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN> exit

Recovery Manager complete.
/u01/app/oracle/local/bkup$
And indeed it does. We now have a backup service and it is independent of the Oracle network listener.