Move LOB objects and LOB indexes to different Tablespace

I received a question from my junior DBA to fix a block crorruption issue for LOB indexes in a tablespace which I want to drop. But he unable to find the same in DBA_LOBS table.

Here are some views to find LOB objects and LOB indexes:

-- To find LOB objects from a tablespace:
e.g.,
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = 'PAYROLL'
   AND SEGMENT_TYPE LIKE 'LOB%'
 ORDER BY 1;

Sample out:

SEGMENT_NAME                SEGMENT_TYPE       OWNER TABLESPACE_NAME
------------------------------ ------------------ ---------- -------
SYS_IL0000077613C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000077671C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000078212C00001$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000078458C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_LOB0000077613C00006$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000077671C00006$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000078212C00001$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000078458C00006$$      LOBSEGMENT         PAYROLL    PAYROLL

8 rows selected

Note: Segments prefixed with 'SYS_IL....' is log index and segements prefixed with 'SYS_LOB.....' is table.

-- To find Table Name for Log segment
e.g.,
SQL> select owner,table_name,column_name,tablespace_name
  2  from dba_lobs
  3  where segment_name='SYS_LOB0000077613C00006$$';

OWNER      TABLE_NAME                     COLUMN_NAME        TABLESPACE_NAME
---------- ------------------------------ ----------------------------------
PAYROLL    PAY_LOAN                       INSTALLMENT        PAYROLL
SQL>

-- To find Table name for LB Index:
e.g.,
SQL> select owner, table_name, column_name, segment_name, index_name
  from dba_lobs
 where index_name = 'SYS_IL0000077613C00006$$';

OWNER      TABLE_NAME COLUMN_NAME     SEGMENT_NAME             INDEX_NAME
---------- ---------- ---------------  -------------------------
PAYROLL    PAY_LOAN   INSTALLMENT     SYS_LOB0000077613C00006$$      SYS_IL0000077613C00006$$
SQL>

A LOB is made up of a LOB data segment storing LOB data and a LOB index segment used to access LOB data. SYS_LOBxxx is the LOB data segment and SYS_ILxxx is the LOB index segment. As a consequence it is meaningless to try to drop only the LOB index segment (and I don't think it is possible to do so): you can only drop the LOB column to drop data and index segment or maybe try to move the column to be stored in row only if LOB size allows.

How to move lobsegment and  lobindex to a different Tablespace?

when I am executing the following statement in order to move the LOBINDEX, I am getting the errors listed below:

SQL> ALTER INDEX SYS_IL0000265968C00002$$ REBUILD TABLESPACE TEST;
ALTER INDEX SYS_IL0000265968C00002$$ REBUILD TABLESPACE TEST
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB


See my workaround:

-- create a table in USERS tablespace

CREATE TABLE TEST_TBL
(
TEST_ID NUMBER NOT NULL,
TEST_NAME CLOB,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
) tablespace USERS;

-- see the tables's tablespace name

SQL> col owner format a5;
SQL> column segment_name format a10;
SQL> col segment_type format a10;
SQL> col tablespace_name format a10;
SQL> select owner,segment_name,segment_type,tablespace_name
  2   from dba_segments where segment_name='TEST_TBL';

OWNER SEGMENT_NA SEGMENT_TY TABLESPACE
----- ---------- ---------- ----------
SYS   TEST_TBL   TABLE      USERS

SQL>

-- See the LOB index

SQL> SELECT index_name, tablespace_name
  2  FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
SYS_IL0000265968C00002$$       USERS
PK_TEST                        USERS

SQL>

-- Move table to different tablespace;

SQL> ALTER TABLE TEST_TBL MOVE TABLESPACE TEST;

Table altered.

SQL> select owner,segment_name,segment_type,tablespace_name
  2  from dba_segments where segment_name='TEST_TBL';

OWNER SEGMENT_NA SEGMENT_TY TABLESPACE
----- ---------- ---------- ----------
SYS   TEST_TBL   TABLE      TEST

SQL>

SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
SYS_IL0000265968C00002$$       USERS
PK_TEST                        USERS

SQL>

See the above results, table is moved from 'USERS' tablespace to 'TEST' tablespace but index remain in same USERS tablespace.  This is because LOB data is stored outside of the table.

The below example, TEST_NAME is the CLOB column which we want to move to new tablespace and EXAMPLE is target tablespace. Above command will successfully move LOB segments to the new tablespace. We can verify it by issuing same sql again.


SQL> ALTER TABLE TEST_TBL MOVE LOB(TEST_NAME) STORE AS (TABLESPACE TEST);

Table altered.

SQL> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
PK_TEST                        USERS
SYS_IL0000265968C00002$$       TEST

Note : "small" LOBs stored inline (ie in the row itself) are not in a seperate LOBSEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.

Fix block corruption via RMAN - Pains & Gains of NOLOGGING mode in Oracle database

Fix block corruptions via RMAN using Oracle 11gR2
Pains & Gains of NOLOGGING mode in Oracle database

a) About NOLOGGING
b) FORCE LOGGING Options
c) Gains of NO LOGGING
d) Detection of Nologging Operations On the Primary and Standby Databases
e) An workaround to fix block corruption via RMAN
f) Best practice to avoid block corruptions
------------------------------------------------------------------------


a) About NoLOGGING Operation:

The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs. Force logging is the new feature added to the family of logging attributes.

Before the existence of FORCE LOGGING, Oracle provided logging and nologging options. These two options have higher precedence at the schema object level than the tablespace level; therefore, it was possible to override the logging settings at the tablespace level with nologging setting at schema object level.

The FORCE LOGGING option can be set at the database level or the tablespace level. The precedence is from database to tablespace. If a tablespace is created or altered to have FORCE LOGGING enabled, any change in that tablespace will go into the redo log and be usable for recovery.

Similarly, if a database is created or altered to have the FORCE LOGGING enabled, any change across the database, with exception of temporary segments and temporary tablespace, will be available in redo logs for recovery. The FORCE LOGGING option can be set at database creation time or later using the alter database command.

To enable FORCE LOGGING after the database is created, use the following command:

ALTER DATABASE FORCE LOGGING;

b) FORCE LOGGING Options:

1) Database level:
  -- Enable
SQL> ALTER DATABASE FORCE LOGGING;
  -- Disable
SQL> ALTER DATABASE NO FORCE LOGGING;


b) Tablespace Level:
-- Disable
SQL> ALTER TABLESPACE <tablespace_name> NO FORCE LOGGING;
-- Enable
SQL> ALTER TABLESPACE <tablespace name> FORCE LOGGING;

c) Table level:
-- Disable
SQL> ALTER TABLE <table_name> NOLOGGING;
-- Enable
SQL> ALTER TABLE <table_name> NOLOGGING;
Note:

Temporary tablespaces and temporary segments have no effect during FORCE LOGGING mode because these objects do not generate any redo. Undo tablespaces are in FORCE LOGGING mode by default, so they cannot be put into FORCE LOGGING mode. Oracle will generate an error if an attempt is made to put a temporary tablespace or undo tablespace into FORCE LOGGING mode.

The FORCE_LOGGING column of v$database view can be queried to verify that the database is in FORCE LOGGING mode. Similarly, the FORCE_LOGGING column of dba_tablespaces view provides the same logging information for each tablespace.

select force_logging from v$database;
select force_logging from dba_tablespaces;


Warning !!!

Putting a database in FORCE LOGGING mode will have some performance impact.


c) Gains of NO LOGGING:

Nologging operations indicate that the database operation is not logged in the online redo log file. Even though a small invalidation redo record is still written to the online redo log file, nologging operations skip the redo generation of the corresponding DML data.  Nologging can be extremely beneficial for the following reasons:

• data written to the redo is minimized dramatically
• time to insert into a large table or index or LOB can be reduced dramatically
• performance improves for parallel creation of large tables or indices

However, NOLOGGING is intended for configurations in which media recovery or the recovery of the corresponding object is not important. Thus, if the disk or tape or storage media fails, you will not be able to recover your changes from the redo because the changes were never logged. 

d) Detection of Nologging Operations On the Primary and Standby Databases

On the primary database, you can monitor for the most recent nologging operation that occurred in the database by issuing the following query:

         SELECT NAME, UNRECOVERABLE_CHANGE#,               
         TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
         FROM V$DATAFILE;

The above primary database's query dictates when the most recent nologging operation occurred and when the invalidation redo was written to the redo.  Once Redo Apply (or Media Recovery) processes the invalidation redo, it marks all the corresponding data blocks corrupt.  You will detect encounter corrupted blocks on the physical standby database when you query any data that references these data blocks.   You will receive the following errors: 

                ORA-01578: ORACLE data block corrupted (file # 3, block # 514)
                ORA-01110: data file 3: '/oracle/oradata/PROD/users.dbf'
                ORA-26040: Data block was loaded using the NOLOGGING option

You can proactively catch some of these corrupted blocks on Redo Apply (or media recovery) instance by running DBVERIFY on the data files.

Example :
           
$ cd /oracle/oradata/PROD
$ dbv file=users01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Aug 25 16:41:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/oradata/PROD/users01.dbf

DBV-00201: Block, DBA 17162242, marked corrupt for invalid redo application
DBV-00201: Block, DBA 17162244, marked corrupt for invalid redo application
...
...
DBV-00201: Block, DBA 17162493, marked corrupt for invalid redo application


DBVERIFY - Verification complete

Total Pages Examined         : 407680
Total Pages Processed (Data) : 221830
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1257
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 175354
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 9239
Total Pages Marked Corrupt   : 111
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2355429844 (29.2355429844)
 
$ SQL apply ignores the invalidation redo since it cannot convert it to any reasonable SQL; so, the logical standby will not receive any immediate errors.   If future transactions reference the missing data, then apply slave will receive an ORA-01403 in the alert.log.  

For example, the following UPDATE statement failed on the logical standby because it was referencing  'nologged' rows that do not exist on the logical standby database.

-- Repair of Nologged Changes on the Physical and Logical Standby Databases:

After a nologged operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future.  However there are additional steps required if you have an existing physical or logical standby database.    This is crucial if you want to preserve the data integrity of your standby databases.

For a physical standby database, Redo Apply will process the invalidation redo and mark the corresponding data blocks corrupt.

For a physical standby database, follow these steps to reinstantiate the relevant data files .

    1. stop Redo Apply (recover managed standby database cancel)
    2. offline corresponding datafile(s) (alter database datafile <NAME> offline drop;)
    3. start Redo Apply (recover managed standby database disconnect)
    4. copy the appropriate backup datafiles over from the primary database (e.g. use RMAN to backup datafiles and copy them)
    5. stop Redo Apply (recover managed standby database cancel)
    6. online corresponding data files (alter database datafile <NAME> online;)
    7. start Redo Apply (recover managed standby database disconnect)

======================
Solution with an workaround:
======================

As per above section, we can use this procedure if RMAN backup has not failed while reading the corrupt block. To determine the same, run a backup on the datafile having the corrupt block:

RMAN> backup check logical datafile 7 format '/u03/backup/%U' tag 'CORRUPT_BLK_FILE_BKP';

Starting backup at 24-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/oradata/PROD/demo01.dbf
channel ORA_DISK_1: starting piece 1 at 24-AUG-15
channel ORA_DISK_1: finished piece 1 at 24-AUG-15
piece handle=/u03/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-AUG-15

Ensure that the location where backup-piece will be created has sufficient space. You can change this destination using the FORMAT clause. Also, even though the backup seems successful, crosscheck using
below query that the corrupt block is not contained in the backup:

SQL> select BP.HANDLE, BP.COMPLETION_TIME, BC.FILE#, BC.BLOCK#, BC.BLOCKS, BC.MARKED_CORRUPT, BC.CORRUPTION_TYPE
    from V$BACKUP_PIECE BP, V$BACKUP_CORRUPTION BC
    where BP.SET_COUNT = BC.SET_COUNT and
          BP.SET_STAMP = BC.SET_STAMP and
          BP.TAG = 'CORRUPT_BLK_FILE_BKP';

no rows selected.

If the above query returns rows showing the corrupt block, we cannot use this procedure. In above case, since it has not returned rows, we can be sure that RMAN has skipped the corrupt block due to unused block optimization algorithm described above. Now, if the datafile is restored from this backup, RMAN will format and restore a empty copy of the corrupt block which can then be used for rman block recovery as below.

1. Restore the datafile to alternate location:

RMAN> run {
2> set newname for datafile 7 to '/oracle/oradata/PROD/demo01_RESTORED.dbf';
3> restore datafile 7 from tag 'CORRUPT_BLK_FILE_BKP';
4> }

executing command: SET NEWNAME

Starting restore at 24-AUG-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /oracle/oradata/PROD/demo01_RESTORED.dbf
channel ORA_DISK_1: reading from backup piece /u03/backup/1jnbhl5c_1_1
channel ORA_DISK_1: piece handle=/u03/backup/1jnbhl5c_1_1 tag=CORRUPT_BLK_FILE_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 24-AUG-15

2. Run DBV on the restored datafile to verify it is free from corruption:

$ dbv file=/oracle/oradata/PROD/demo01_RESTORED.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Aug 25 17:01:13 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/oradata/PROD/demo01_RESTORED.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 12799
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 775154 (0.775154)

3. Run BLOCKRECOVER command as below to repair the corrupt block.

The corrupt block will be replaced by the empty, formatted block from the restored datafile:

RMAN> blockrecover datafile 7 block 150 FROM DATAFILECOPY;

Starting recover at 24-AUG-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring block(s) from datafile copy /oracle/oradata/PROD/demo01_RESTORED.dbf

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 24-AUG-154. 

Run DBV on the original file to confirm that it is now free from corruption:


$ dbv file=/oracle/oradata/PROD/demo01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.4.0 - Production on Tue Aug 25 17:11:33 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/oradata/PROD/demo01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 356
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 152
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12292
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 775154 (0.775154)

5. Additionally, you can run VALIDATE in RMAN to verify further:

RMAN> backup validate check logical datafile 7;

Starting backup at 24-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/oradata/PROD/demo01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              12292        12801           775154
  File Name: /oracle/oradata/PROD/demo01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              356
  Index      0              0
  Other      0              152

Finished backup at 24-AUG-15

f) Best practices to avoid block corruptions:

Regular and proactive checking of the database for corruptions:

1. Run the DBVerify utility against the datafile at a periodic intervals, to check for any physical corruption.

e.g.,
dbv file=system01.dbf blocksize=8192

2.  Run object level analyze command at a periodic intervals. This will check for logical inconsistencies and even detect physical corruptions. It is important to note that this command locks the object, which is  being  analyzed so need to be performed at off-peak hours. The online option available with analyze helps
in removing this restriction to some extent.( IN OFF PAEK HOURS only)

e.g.,
SQL> Analyze table <user>.<table_name> validate structure cascade [online];
SQL> Analyze index <user>.<index_name/cluster_name> validate structure;
     
   For partition table analyze, the utlvalid.sql script must be run to create invalid_rows table If not run you need to run the below analyze command instead.

SQL> Analyze table <user>.<table_name> partition <partition_name> validate structure cascade into invalid_rows;

3. Always take full database export backup ( use EXPDP/ EXP)

e.g.,
expdp directory=dir_name dumpfile=dump_name.dmp logfile=log_name.log full=y

Note : As per requirement you can add many more parameters. Use expdp -help to see details.

Click here to read more about expdp

4. Ensuring the data free of corruptions when doing a bulk load. After the dataload, perform normal
   validations(select/analyze/export) to detect corruptions, if introduced while loading.

5. Use RMAN to check the existence of physical and logical corruption.

e.g.to validate the complete database

RMAN> BACKUP CHECK LOGICAL VALIDATE DATABASE;

Note: See the above document to know more about RMAN validation.

6. Use DBMS_REPAIR package to verify corruption in an object and can use this package to mark the block as soft corrupt.

DBMS_REPAIR.CHECK_OBJECT :CHECK_OBJECT procedure checks the specified object and populates the repair
table with information about corruption and repair directive(s). Validation consists of block checking all blocks in the object. All blocks previously marked corrupt will be skipped.

Click here to read more...

7. Run Memory/Hardware diagnostics periodically.

8. Set block checking parameters.

DB_BLOCK_CHECKING = TRUE (FULL from 10.2 onwards)
DB_BLOCK_CHECKSUM = TRUE (FULL from 10.2 onwards)
_DB_BLOCK_CHECK_FOR_DEBUG = TRUE

Note : Enable these parameter has performance impact. Don't go above parameters unless untill load test clarification. See more documents in Oracle site.

11g specific:

* DB_ULTRA_SAFE = { OFF | DATA_ONLY | DATA_AND_INDEX }

Click here to read "Best Practices for Avoiding and Detecting Corruption" (Doc ID 428570.1)

Oracle RAC service - create | manage | TAF | PRCR-1013 : Failed to start resource - a fix

Create and Manage custom Service in Oracle 11gR2 RAC database database:

We will discuss below points how we will configure and manage custom RAC services to connect database for various purposes:

• create service
• start service
• stop service
• config service
• modify service

Note: I assume that you have all basic ideas about RAC services. Let us see what is the output when we have a default database service. Assume I have a database with name PROD. i.e., It has two RAC instances ( for two node RAC)

Along with we will discuss a workaround on below issues at last:

Fix : PRCR-1013 : Failed to start resource - while start service in RAC database

Out put for configuration for default service for my SID :

$ srvctl config database -d PROD

Database unique name: PROD

Database name: PROD

Oracle home: /oracle/oracle_base/product/dbhome_1

Oracle user: oracle

Spfile: +DATA/PROD/spfilePROD.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: PROD

Database instances: PROD1,PROD2

Disk Groups: DATA,FRA

Mount point paths:

Services:

Type: RAC

Database is administrator managed

Deatils of rac setup:

$ crsctl stat res -t

--------------------------------------------------------------------------------

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.FRA.dg

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.LISTENER.lsnr

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.LISTENER_PROD.lsnr

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.QUORUM.dg

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.asm

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.gsd

               OFFLINE OFFLINE      rac1                                       

               OFFLINE OFFLINE      rac2                                       

ora.net1.network

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.ons

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

ora.registry.acfs

               ONLINE  ONLINE       rac1                                       

               ONLINE  ONLINE       rac2                                       

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       rac2                                       

ora.PROD.db

      1        ONLINE  ONLINE       rac1                    Open               

      2        ONLINE  ONLINE       rac2                    Open               

ora.cvu

      1        ONLINE  ONLINE       rac1                                       

ora.rac1.vip

      1        ONLINE  ONLINE       rac1                                       

ora.rac2.vip

      1        ONLINE  ONLINE       rac2                                       

ora.oc4j

      1        ONLINE  ONLINE       rac1                                       

ora.scan1.vip

      1        ONLINE  ONLINE       rac2                                       

$

1) Create policy managed services:

syntax:

Adds services to a database and assigns them to instances. If you have multiple instances of a cluster database on the same node, then always use only one instance on that node for all of the services that node manages.

Note:

The srvctl add service command does not accept placement options for Oracle RAC One Node databases.

Syntax and Options

Use the srvctl add service command to create a service, using the first of the following syntax models, or to update an existing service, using the second syntax model:

srvctl add service -d db_unique_name -s service_name {-r "preferred_list"

   [-a "available_list"] [-P {BASIC | NONE | PRECONNECT}] | -g server_pool

   [-c {UNIFORM | SINGLETON]} [-k network_number]

   [-l [PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY]

   [-y {AUTOMATIC | MANUAL}] [-q {TRUE | FALSE}] [-x {TRUE | FALSE}]

   [-j {SHORT | LONG}][-B {NONE | SERVICE_TIME | THROUGHPUT}]

   [-e {NONE | SESSION | SELECT}] [-m {NONE | BASIC}] [-z failover_retries]

   [-w failover_delay]

srvctl add service -d db_unique_name -s service_name

   -u {-r preferred_list | -a available_list} [-f]

Service Management Policy:

When you use Oracle Clusterware to manage your database, you can configure startup options for each individual database service when you add the service using the srvctl add service command with the -y option. If you set the management policy for a service to AUTOMATIC (the default), then the service starts automatically when you start the database with SRVCTL. If you set the management policy to MANUAL, then the service does not automatically start, and you must manually start it with SRVCTL. A MANUAL setting does not prevent Oracle Clusterware from monitoring the service when it is running and restarting it if a failure occurs. Prior to Oracle RAC 11g release 2 (11.2), all services worked as though they were defined with a MANUAL management policy.

Note:

When you use automatic services in an administrator-managed database, during planned database startup, services may start on the first instances to start rather than their preferred instances.

Example from Oracle Doc:

Use this example syntax to add the gl.example.com service to the my_rac database with AQ HA notifications enabled, a failover method of BASIC, a Connection Load Balancing Goal of LONG, a failover type of SELECT, and 180 failover retries with a delay of 5:

$ srvctl add service -d my_rac -s gl.example.com -m BASIC -e SELECT -z 180 -w 5 -j LONG

Use this example syntax to add a named service to a database with preferred instances in list one and available instances in list two, using preconnect failover for the available instances:

srvctl add service -d crm -s sales -r crm01,crm02 -a crm03 -P PRECONNECT

Example:

$ srvctl add service -d db_unique_name -s service_name -r preferred_list [-a available_list] [-P TAF_policy]

$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -a PROD2 -m BASIC -e SELECT -z 180 -w 5 -j LONG

Some issues I faced during service creation. See my errors also:

TRY-1:

$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -a PROD2 -m BASIC -e SELECT -z 180 -w 5 -j LONG

PRKO-2102 : Failed to create server pool PROD_hrprod: PRCS-1009 : Failed to create server pool ora.PROD_hrprod

PRCR-1071 : Failed to register or update server pool ora.PROD_hrprod

CRS-2714: 'rac2' is specified multiple times in 'SERVER_NAMES' of 'ora.PROD_hrprod'

Try-2:

srvctl add service -d PROD -s hrprod -m BASIC -e SELECT -z 180 -w 5 -j LONG

$ srvctl add service -d PROD -s hrprod -m BASIC -e SELECT -z 180 -w 5 -j LONG

PRKO-3116 : '-g' or '-r' option should be provided

Correct One:

$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -m BASIC -e SELECT -z 180 -w 5 -j LONG

outpu: 
$  crsctl stat res -t

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       rac2                                       

ora.PROD.hrprod.svc

      1        OFFLINE OFFLINE                                                  

      2        OFFLINE OFFLINE                                                  

ora.PROD.db

      1        ONLINE  ONLINE       rac1                    Open               

      2        ONLINE  ONLINE       rac2                    Open  

2) Start the services:

$ srvctl start service

Starts a service or multiple services on the specified instance. The srvctl start service command will fail if you attempt to start a service on an instance if that service is already running on its maximum number of instances, that is, its number of preferred instances. You may move a service or change the status of a service on an instance with the srvctl modify service and srvctl relocate service commands described later in this appendix.

Note:All manual service startup must specify the name of the service to be started by the user.

Syntax and Options:

Use the srvctl start ser
vice command with the following syntax:

srvctl start service -d db_unique_name

            [-s "service_name_list" [-n node_name | -i instance_name]]

            [-o start_options]

Example:

a) Start service in one node:

$ srvctl start service -d PROD -s hrprod -n rac1 -o open

when service started in one node, then see the output of 'crsctl stat res -t':

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       rac2                                       

ora.PROD.hrprod.svc

      1        ONLINE  ONLINE       rac1                                       

      2        OFFLINE OFFLINE                                                  

ora.PROD.db

      1        ONLINE  ONLINE       rac1                    Open               

      2        ONLINE  ONLINE       rac2                    Open

Now try to start service in other node:

$ srvctl start service -d PROD -s hrprod -n rac2 -o open

b) Start service in all nodes at a time:

$ srvctl start service -d PROD -s hrprod -o open

see the output of 'crsctl stat res -t':

--------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       rac2                                       

ora.PROD.hrprod.svc

      1        ONLINE  ONLINE       rac1                                       

      2        ONLINE  ONLINE       rac2                                       

ora.PROD.db

      1        ONLINE  ONLINE       rac1                    Open               

      2        ONLINE  ONLINE       rac2                    Open  

3) Stop service:

srvctl stop service

Stops one or more services globally across the cluster database, or on the specified instance.

Syntax and Options

Use the srvctl stop service command with the following syntax:

srvctl stop service -d db_unique_name [-s "service_name_list"

     [-n node_name | -i instance_name] [-f]

a) Stop service in one node/ instance:

$ srvctl stop service -d PROD -s hrprod -n rac1

$ srvctl stop service -d PROD -s hrprod -n rac2

b) Stop service in all nodes/ instances:

$ srvctl stop service -d PROD -s hrprod

4) Config service:

$ srvctl config service -d PROD -s hrprod

$ srvctl config service -d PROD -s hrprod

Service name: hrprod

Service is enabled

Server pool: PROD_hrprod

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: SELECT

Failover method: BASIC

TAF failover retries: 180

TAF failover delay: 5

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Preferred instances: PROD1,PROD2

Available instances:

$

When I created in one my other test database:

$ srvctl config service -d proddb -s finprod

Service name: finprod

Service is enabled

Server pool: proddb_finprod

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: SHORT

Runtime Load Balancing Goal: SERVICE_TIME

TAF policy specification: NONE

Edition:

Preferred instances: proddb1,proddb2

Available instances:

Best practice: As per oracle suggestion, if no standby, then below configuration is good. As per one of production setting, I used below commands to create service:

$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -B SERVICE_TIME -j SHORT
$ srvctl start service -d PROD -s hrprod

$ srvctl config service -d PROD -s hrprod
Service name: hrprod
Service is enabled
Server pool: PROD_hrprod
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: SHORT
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: NONE
Edition:
Preferred instances: PROD1,PROD2
Available instances:

5) Remove service:

srvctl remove service  --Removes the configuration for a service.

Syntax and Options

Use the srvctl remove service command as follows:

srvctl remove service -d db_unique_name -s service_name [-i instance_name] [-f]

-f    :  Removes the service resource even though the service is running   

Best practice: First stop the service, then drop it.

$ srvctl remove service -d crm -s sales

The following example removes the services from specific instances:

$ srvctl remove service -d crm -s sales -i crm01,crm02

Example:

$ srvctl remove service -d PROD -s hrprod -f

6) Re-create the services with different TAF policy:

a) We have created earlier like below:

$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -m BASIC -e SELECT -z 180 -w 5 -j LONG

b) In below example we are taking care entire SESSION failover instead of SELECT sessions.

srvctl add service -d PROD -s hrprod -r PROD1,PROD1 -e SESSION -m BASIC -w 10 -z 150

c) If culster is primary cluster and you have standby cluster also, then follow below guide lines:

1. On the primary and standby hosts create the service (oltpworkload) that the application will use to connect to the database. The service should be created such that it is associated with and runs on the database when it is in the ‘PRIMARY’ database role:

Primary cluster:

srvctl add service -d Austin -s oltpworkload -r ssa1,ssa2,ssa3,ssa4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150

Standby cluster:

srvctl add service -d Houston -s oltpworkload -r ssb1,ssb2,ssb3,ssb4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150

2. If the standby is also going to support read-only reporting applications, then create a service specific for this workload (reports) that will start when the database is in PHYSICAL_STANDBY role.

Primary cluster:

srvctl add service -d Austin -s reports -r ssa1,ssa2,ssa3,ssa4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150

Standby cluster: 

srvctl add service -d Houston -s reports -r ssb1,ssb2,ssb3,ssb4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150

In addition to creating the database service “reports” on both clusters, the following SQL statement must also be run on the primary database so that the service definition is transmitted via the redo stream and applied to the physical standby database:

SQL run at the Primary database:

EXECUTE DBMS_SERVICE.CREATE_SERVICE('reports', 'reports', NULL, NULL,TRUE, 'BASIC', 'SESSION', 150, 10, NULL);

7) Configure an Oracle Net : primary & standby cluster

Configure an Oracle Net alias that the OCI application will use to connect to the database. The Oracle Net alias should specify both the primary and standby SCAN hostnames. For best performance while creating new connections the Oracle Net alias should have LOAD_BALANCE=OFF for the DESCRIPTION_LIST so that DESCRIPTIONs are tried in an ordered list, top to bottom. With this configuration the second DESCRIPTION is only attempted if all connection attempts to the first DESCRIPTION have failed.

SALES=

(DESCRIPTION_LIST=

(LOAD_BALANCE=off)

(FAILOVER=on)

(DESCRIPTION=

(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)

  (ADDRESS_LIST=

    (LOAD_BALANCE=on)

    (ADDRESS=(PROTOCOL=TCP)(HOST=Austin-scan)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=oltpworkload)))

(DESCRIPTION=

(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)

  (ADDRESS_LIST=

    (LOAD_BALANCE=on)

    (ADDRESS=(PROTOCOL=TCP)(HOST= Houston-scan)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=oltpworkload))))

When a new connection is made using the above Oracle Net alias the following logic is used: 

a) Oracle Net contacts DNS and resolves Austin-scan to a total of 3 IP addresses.

b) Oracle Net randomly picks one of the 3 IP address and attempts to make a connection. If the connection attempt to the IP address does not respond in 3 seconds (TRANSPORT_CONNECT_TIMEOUT) the next IP address is attempted. All 3 IP addresses will be tried a total of four times (initial attempt plus RETRY_COUNT in the above example).

c) If the connection to primary site is unsuccessful, it then contacts DNS and resolves Houston-scan to 3 addresses.

d) The same sequence is performed for the standby Houston-scan as it was for the Austin-scan.

When a new connection is made using the above Oracle Net alias the following logic is used: 

a) Oracle Net contacts DNS and resolves Austin-scan to a total of 3 IP addresses.

b) Oracle Net randomly picks one of the 3 IP address and attempts to make a connection. If the connection attempt to the IP address does not respond in 3 seconds (TRANSPORT_CONNECT_TIMEOUT) the next IP address is attempted. All 3 IP addresses will be tried a total of four times (initial attempt plus RETRY_COUNT in the above example).

c) If the connection to primary site is unsuccessful, it then contacts DNS and resolves Houston-scan to 3 addresses.

d) The same sequence is performed for the standby Houston-scan as it was for the Austin-scan.

Additional information on the Oracle Net parameters used in the above alias:

LOAD_BALANCE is ON by default for DESCRIPTION_LIST only. This parameter by default is OFF for an address list within a DESCRIPTION. Setting this ON for a SCAN-based address implies that new connections will be randomly assigned to one of the 3 SCAN-based IP addresses resolved by DNS.

In certain situations, round-robin address assignment by DNS may not be possible - see the Oracle Database 11.2.0.2 Readme. The best practice to ensure connect-time client load balancing across the 3 SCAN IP addresses is to explicitly specify LOAD_BALANCE=on. Note that this behavior is independent of server-side load balancing which will occur subsequently, after the initial SCAN listener receives the connection request.

The default value for the FAILOVER parameter is ON for an address list within a DESCRIPTION. This impacts the 3 SCAN IP addresses the same way as if those 3 IP addresses were listed explicitly in the connect descriptor. This means that if the initial connection requests to the first randomly-assigned SCAN IP address fails, the connection will failover to another SCAN IP address, and will continue to do so, till it iterates the complete address list. Note that this parameter is relevant only to new connections. Failover of existing connections is handled by TAF, which is controlled by the separate FAILOVER_MODE parameter.

The CONNECT_TIMEOUT parameter is the time to connect to the database instance providing the requested service, and includes the time to establish a TCP connection to the listener. The TCP duration is controlled by TRANSPORT_CONNECT_TIMEOUT, which has a default value of 60 seconds. If both timeouts are specified, it is recommended that CONNECT_TIMEOUT be set to a value slightly greater than TRANSPORT_CONNECT_TIMEOUT. The timeout interval is applicable for each ADDRESS in an ADDRESS_LIST, and each IP address to which a host name is mapped. Set the CONNECT_TIMEOUT parameter to the maximum amount of time (in seconds) to wait for a response from an address before skipping to the next address. A setting of three seconds is recommended and is acceptable in most cases. Do not set this parameter to fewer than three seconds.

The equivalent global parameter in sqlnet.ora is SQLNET.OUTBOUND_CONNECT_TIMEOUT. If the same timeout value is sufficient for all connect strings, it would be simpler to set the global parameter. Otherwise, a separate setting can be done for each connect string.

The equivalent global parameter for TRANSPORT_CONNECT_TIMEOUT is TCP.CONNECT_TIMEOUT. Both these parameters are applicable only when the protocol is TCP.

The RETRY_COUNT parameter specifies the number of times an address list is traversed before the new connection attempt is terminated. The default value is 0. With respect to SCAN, with FAILOVER = on, setting this RETRY_COUNT parameter to a value of 2 (for example), means the 3 SCAN IP addresses are traversed thrice (i.e. 3*3=9 connect attempts), before the connection is terminated:

    > When the connection request initially comes in, the first randomly assigned IP address tries to service that request, followed by the two remaining IP addresses (this behavior is controlled by the FAILOVER parameter);

    > The retries then kick in and the list of 3 IP addresses is tried two more times. RETRY_COUNT is only supported at DESCRIPTION level in connect string, but not at global (i.e. sqlnet.ora) level.

Incident/ Issues to start a service:- Test case scenario

When unable to start a service:

Fix : PRCR-1013 : Failed to start resource - while start service in RAC database

Suddenly I found one of the service which is created for load-balancing TAF purpose, is down. When I tried to start it, then below error came.

$ srvctl start service -d proddb -s finprod -n racdb2 -o open

PRCR-1013 : Failed to start resource ora.proddb.finprod.svc

PRCR-1064 : Failed to start resource ora.proddb.finprod.svc on node racdb2

CRS-5017: The resource action "ora.proddb.finprod.svc start" encountered the following error:

ORA-06550: line 1, column 34:

PLS-00553: character set name is not recognized

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

. For details refer to "(:CLSN00107:)" in "/u01/app/grid4/log/racdb2/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.proddb.finprod.svc' on 'racdb2' failed

CRS-5017: The resource action "ora.proddb.finprod.svc clean" encountered the following error:

ORA-06550: line 1, column 33:

PLS-00553: character set name is not recognized

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

. For details refer to "(:CLSN00106:)" in "/u01/app/grid4/log/racdb2/agent/crsd/oraagent_oracle/oraagent_oracle.log".

Now read the error written in oraagent_oracle.log file. Best way is raise SR and follow it immediately. 

Collect details:

script /tmp/out.1

echo "Took on server `hostname` at `date` as `id`"

crsctl stat res -t

srvctl stop service -d proddb -s finprod

crsctl stat res -t

### make sure the service is stopped at this point

export SRVM_TRACE=true

srvctl start service -d proddb -s finprod

date

crsctl stat res -t

date

#### service should be still down since its not working this way.

srvctl start service -d proddb -s finprod -i proddb2

crsctl stat res -t

date

exit

Workaround:

I did below activities and resolved the issues:

Before any activity, output of "crsctl stat res -t":

--------------------------------------------------------------------------------

NAME           TARGET  STATE        SERVER              

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.FRA.dg

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.LISTENER.lsnr

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.asm

               ONLINE  ONLINE       racdb1                    Started            

               ONLINE  ONLINE       racdb2                    Started            

ora.gsd

               OFFLINE OFFLINE      racdb1                                       

               OFFLINE OFFLINE      racdb2                                       

ora.net1.network

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.ons

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.registry.acfs

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       racdb2                                       

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       racdb1                                       

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       racdb1                                       

ora.cvu

      1        ONLINE  ONLINE       racdb1                                       

ora.racdb1.vip

      1        ONLINE  ONLINE       racdb1                                       

ora.racdb2.vip

      1        ONLINE  ONLINE       racdb2                                       

ora.proddb.db

      1        ONLINE  ONLINE       racdb1                    Open               

      2        ONLINE  ONLINE       racdb2                    Open               

ora.proddb.finprod.svc

      1        ONLINE  ONLINE       racdb1                                       

      2        ONLINE  OFFLINE                                                  

ora.oc4j

      1        ONLINE  ONLINE       racdb1                                       

ora.scan1.vip

      1        ONLINE  ONLINE       racdb2                                       

ora.scan2.vip

      1        ONLINE  ONLINE       racdb1                                       

ora.scan3.vip

      1        ONLINE  ONLINE       racdb1  

Here service is not running in node2. In very first step stop entire service in all nodes and start it again. If same issue is comming you can follow below practice.

1. Ensure that the database is properly started up, that is, mounted and opened.

2. Find PID and kill the process:

cd $GRID_HOME/log/nodename/agent/crsd/oraagent_oracle

cat oraagent_oracle.pid

You should see a pid number in the file.

ps -ef |grep <pid> -----> verify if this pid is the oraagent.bin process.

if it is the oraagent.bin process kill it.

kill -9 <pid>

3. If not ps -ef|grep d.bin. You should find two oraagent.bin processes kill these.

Killing the process should not have any effect and they should respawn again automatically.

Confirm that the processes have started.

4. Then restart the service using srvctl.

srvctl start service -d <database_name> -s <service_name> -i <instance_name>

Activity done in my test env.:

cd /u01/app/grid4/log/racdb2/agent/crsd/oraagent_oracle

$ cat oraagent_oracle.pid

7798818

$ ps -ef|grep 7798818

$ ps -ef|grep 7798818

  oracle  7798818        1   2   Jul 22      - 43:22 /u01/app/grid4/bin/oraagent.bin

  oracle 16974300 36307328   0 17:35:08  pts/4  0:00 grep 7798818

kill -9 7798818

 ps -ef|grep d.bin

  oracle  5767238        1   0   Jul 22      -  7:16 /u01/app/grid4/bin/evmd.bin

  oracle  5832844        1   0   Jul 22      -  1:45 /u01/app/grid4/bin/gpnpd.bin

    root  5963902        1   0   Jul 22      - 15:07 /u01/app/grid4/bin/ohasd.bin reboot

  oracle  8192028        1   0   Jul 22      -  0:05 /u01/app/grid4/bin/mdnsd.bin

  oracle  6291842  8585614   3   Jul 22      - 70:27 /u01/app/grid4/bin/ocssd.bin

    root  7668170        1   0   Jul 22      -  7:40 /u01/app/grid4/bin/octssd.bin reboot

    root 26411340        1   3   Jul 23      - 41:36 /u01/app/grid4/bin/osysmond.bin

  oracle 28049762 36307328   0 17:36:52  pts/4  0:00 grep d.bin

    root 45875706        1   0   Jul 22      - 18:22 /u01/app/grid4/bin/crsd.bin reboot

  oracle 51052946        1   0   Jul 22      - 10:53 /u01/app/grid4/bin/gipcd.bin

Note : There is no pid value as we killed.

Now start the service again:

$ srvctl start service -d proddb -s finprod -n racdb2 -o open

Yah. It is started. Now see the "crsctl stat res -t" output:

$ crsctl stat res -t

--------------------------------------------------------------------

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

--------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.FRA.dg

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.LISTENER.lsnr

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.asm

               ONLINE  ONLINE       racdb1                    Started            

               ONLINE  ONLINE       racdb2                    Started            

ora.gsd

               OFFLINE OFFLINE      racdb1                                       

               OFFLINE OFFLINE      racdb2                                       

ora.net1.network

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.ons

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

ora.registry.acfs

               ONLINE  ONLINE       racdb1                                       

               ONLINE  ONLINE       racdb2                                       

--------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       racdb2                                       

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       racdb1                                       

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       racdb1                                       

ora.cvu

      1        ONLINE  ONLINE       racdb1                                       

ora.racdb1.vip

      1        ONLINE  ONLINE       racdb1                                       

ora.racdb2.vip

      1        ONLINE  ONLINE       racdb2                                       

ora.proddb.db

      1        ONLINE  ONLINE       racdb1                    Open               

      2        ONLINE  ONLINE       racdb2                    Open               

ora.proddb.finprod.svc

      1        ONLINE  ONLINE       racdb1                                       

      2        ONLINE  ONLINE       racdb2                                       

ora.oc4j

      1        ONLINE  ONLINE       racdb1                                       

ora.scan1.vip

      1        ONLINE  ONLINE       racdb2                                       

ora.scan2.vip

      1        ONLINE  ONLINE       racdb1                                       

ora.scan3.vip

      1        ONLINE  ONLINE       racdb1

Service Name Usage more points:


If you have a RAC database, you may have introduced a few features to support some of the advanced functionalities of RAC, e.g., load balancing across nodes and making sessions fail over to a surviving node when a node dies. These things will occur only if users are employing the proper connect string, utilizing SERVICE_NAME and not SID. Here is one way to use connect strings, utilizing service name "finprod" connecting to the RAC database running on three nodes – rac1, rac2 and rac3.


PROD =
 (DESCRIPTION =
 (LOAD_BALANCE = on)
 (FAILOVER = on)
 (enable = broken)
 (ADDRESS = (PROTOCOL = TCP)(HOST = racdb1)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = racdb2)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = racdb3)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = finprod)
 (FAILOVER_MODE =
 (TYPE = SELECT)
 (METHOD = BASIC)
 (RETRIES = 120)
 (DELAY = 2)
 )
 )
 ) 

Key Note on database service:

Service manage in Oracle RAC database- Command List
(Keys and description)
-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-v Verbose
-p policy {AUTOMATIC | MANUAL}
-t TAF
-f  Force stop or relocate service operations

-- Check status of database and list services:
$ srvctl status database -d db_unique_name -v
Or using View :
SQL> select * from gv$active_services

-- View configuration of services
$ srvctl config service -d DB2 –a

-- Add service
$ srvctl add service -d PROD -s OLTP_SVC -r PROD1,PROD2 -a PROD3
$ srvctl add service -d PROD -s BATCH_SVC -r PROD3 -a PROD1,PROD2

-- Modify service
$ srvctl modify service -s service_name -d db_unique_name -n -i pref_inst_list [ -a avail_inst_list] [-f]
e.g., $ srvctl modify service -s OLTP_SVC -d PROD -n -i PROD3 -a PROD1,PROD2

-- Moving a service member from one instance to another (move and change configuration):
$ srvctl modify service -s PROD_UK_SEARCH  -d PROD -i PROD1 -t PROD2

-- Relocate service (not changing configuration):
$ srvctl relocate service -d PROD -s PROD_OPERATIONS_SERVICE -i PROD1 -t PROD3

-- Enable|disable|start|stop service
$ srvctl enable|disable|start|stop service  -s service_name -d db_unique_name

-- Remove Service:
$ srvctl remove service -d PROD -s PROD_UK_SEARCH -i PROD5

-- Create Service and TAF:
$ srvctl add service –d PROD –s crmprd  -r  node1, node2  -B SERVICE TIME –j  SHORT
$ srvctl start service –d PROD –s crmprd  [- w – failover delay, -z -> failover retries
$ srvctl add service -d PROD -s hrprod -r PROD1,PROD2 -m BASIC -e SELECT -z 180 -w 5 -j LONG

Reference :
http://docs.oracle.com/cd/E11882_01/rac.112/e41960/srvctladmin.htm