Separating RAC TNS Services for connecting Application to Database with a specific Service Name

I want to explain about separating of service name in Oracle RAC .Today I had a request for separating Financial client service because of when I down a service we shouldn’t interrupt on Financial system.

In this solution we have Orale Rac 11g with 2 nodes. I decide make a new service with srvctl utility. The service on the two nodes can provide services of course you can configure for example This service provide just on node 1 or node 2.

Following the example earlier in this section, one services will be created, Fin_svc . To partition the applications using these services, FIN_svc will run on the first instance, orcl1. The service accntg_svc will run on the second instance, orcl2.

[oracle@css-db-test-1 ~]$  srvctl add service -d orcl -s FIN_Srv -r orcl1,orcl2

[oracle@css-db-test-1 ~]$ srvctl status service -d orcl

Service FIN_Srv is not running.

[oracle@css-db-test-1 ~]$ srvctl start service -d orcl -s FIN_Srv

[oracle@css-db-test-1 ~]$ srvctl status service -d orcl

Service FIN_Srv is running on instance(s) orcl1,orcl2

[grid@css-db-test-2 ~]$ crsctl stat res -t

ora.orcl.fin_srv.svc

      1        ONLINE  ONLINE       css-db-test-1

      2        ONLINE  ONLINE       css-db-test-2

You can see this service in the configuration of listener:

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                28-NOV-2015 16:56:24

Uptime                    0 days 0 hr. 25 min. 11 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /grid/product/11.2.0/grid_1/network/admin/listener.ora

Listener Log File         /gridbase/diag/tnslsnr/css-db-test-1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.72.24)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.8.72.27)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "FIN_Srv" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl1", status READY, has 1 handler(s) for this service...

The command completed successfully

Add following scripts to Tnsnames.ora on 2 nodes:

Test_Backup_CSS_FINNNN =

  (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = css-db-test-scan.ssd.net)(PORT = 1521))   

    (CONNECT_DATA =

  (SERVER = DEDICATED)

      (SERVICE_NAME = FIN_Srv)

    )

  )

SQL> select NAME from dba_services;

The Finance application would make a connection to the FIN_Srv alias in the tnsnames.ora configuration file.

Flashback Archive data for keep history of all changes (FDA)

Flashback Data Archive provides the automated ability to track and store all transactional changes to a table over its lifetime without having to build this intelligence into your application.Flashback Data Archive uses a background process fbda to capture data asynchronously.

Creating a Flashback Data Archive (lets call it FBDA)

·  Create a new tablespace or use existing tablespace – tablespace needs to be ASSM

·  Specify the FBDA as the default (optional)

·  Assign a quota for the FBDA (optional)

·  Assign a retention period for the FBDA

·  Retention period integer denoting days,months or years

·  Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.    

SQL> create tablespace historical_data_01;

Tablespace created.

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

SQL>  create flashback archive fba1 tablespace historical_data_01 retention 2 DAY;

Flashback archive created.

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

SQL> select flashback_archive_name,to_char(create_time,'dd-mon-yyyy') created,retention_in_days,status from dba_flashback_archive;

                FLASHBACK_ARCHIVE_NAME            CREATED                       RETENTION_IN_DAYS  STATUS

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

                          FBA1                                      28-nov-2015                                                                2          

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

SQL> grant flashback archive on fba1 to sys;

SQL> create table vahid(id number,name varchar2(50));

SQL>  insert into vahid(id,name) values(1,'Alex');

1 row created.

SQL> commit;

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

SQL> alter table vahid flashback archive fba1;

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

SQL> select table_name ,flashback_archive_name,archive_table_name,status from user_flashback_archive_tables;

            TABLE_NAME FLASHBACK_ARCHIVE_NAME          ARCHIVE_TABLE_NAME          STATUS

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

      VAHID                FBA1                      SYS_FBA_HIST_221804      ENABLED

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

 SQL>insert into vahid(id,name) values(2,'Anjel');

  commit;

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

SQL>  select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') date_system from dual;

            DATE_SYSTEM

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

            28-11-2015 14:45:51

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

 SQL>update vahid set name='vahid' where ID=2;

  commit;

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

  SQL> select * from vahid;

            ID         NAME

             1            Alex

             2           vahid

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

SQL> select * from vahid as of timestamp to_timestamp('28-11-2015 14:45:00','dd-mm-yyyy hh24:mi:ss');

            ID         NAME

             1          Alex

              2        Anjel

Now you can see history of changing record with ID=2.

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

For dropping flashback archive  :

SQL> drop flashback archive fba1;

SQL> drop tablespace historical_data_01;

What Is the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin.
It can be recovered at a later time. This feature is called Flashback Drop.

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space.

Enabling and Disabling the Recycle Bin

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

For example:

1-

create user vahid identified by vahid account unlock;

grant connect , resource to vahid;

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

2-

create table vahid.TBL_UNCOMIITED_TRN2(id number,name varchar2(30));

insert into vahid.TBL_UNCOMIITED_TRN2 values(1,'test1');

commit;

create index IDX_ID_PK on vahid.TBL_UNCOMIITED_TRN2(ID);

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

3-

select * from vahid.TBL_UNCOMIITED_TRN2;

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

4-

drop table vahid.TBL_UNCOMIITED_TRN2;

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

5-

select object_name,original_name,type,can_undrop as "undo",can_purge as "PUR",droptime 

       from dba_recyclebin where original_name like 'TBL_UNCOMIITED_TRN2%' order by droptime;

  OBJECT_NAME                     ORIGINAL_NAME    TYPE undo  PUR   DROPTIME

BIN$JZgAyoU+u1DgUxQVCApelQ==$0 TBL_UNCOMIITED_TRN2  TABLE YES YES   2015-11-28:13:39:27

select * from vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0";

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

6-

flashback table vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0" to before drop;

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

7-

select * from vahid.TBL_UNCOMIITED_TRN2;

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

8-

--Restoring Dependent objects

select object_name,original_name,type from recyclebin;

select index_name from dba_indexes where table_name='TBL_UNCOMIITED_TRN2';

    INDEX_NAME

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

 BIN$JZgAyoU9u1DgUxQVCApelQ==$0

--Restore the original name of the index as follow: 

alter index "BIN$JZgAyoU9u1DgUxQVCApelQ==$0" rename to IDX_ID_PK;

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

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space.

PURGE TABLE JZgAyoU9u1DgUxQVCApelQ==$0;

Users can purge the recycle bin of their own objects, and release space for objects

PURGE RECYCLEBIN;

enq: TM - contention

The waits on the enq: TM (Table modification) – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table’s foreign key constraint that references a parent table is missing an index on the associated key. Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren’t restricted to a row but to the entire table.

You can find unindex foreign key constraints with blew script:

SELECT tbl.*,
       'CREATE INDEX IDXFK_' || substr(tbl.column_name, 1, 23) || ' ON ' ||
       tbl.table_name || ' ( ' || tbl.column_name ||
       ' ) tablespace main_index ; '

  FROM (SELECT c.owner,
               c.table_name,
               cc.column_name,
               cc.position column_position
      
          FROM DBA_constraints c, DBA_cons_columns cc
         WHERE c.constraint_name = cc.constraint_name
           and c.OWNER = 'Yuor Username'
           AND c.constraint_type = 'R'
           and c.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
          FROM DBA_indexes i, DBA_ind_columns ic
         WHERE i.index_name = ic.index_name) tbl
 ORDER BY table_name;

enq: TX - row lock contention

enq: TX - row lock contention:

This is indicative of a session waiting for a row lock held by another session.

There are several situations of TX enqueue:

In mode 6:

Waits for TX in mode 6 occur when a session is waiting for a row level lock that is already held by another session. This occurs when one application is updating or deleting a row that another session is also trying to update or delete. This will generate a wait event "enq: TX - row lock contention". To solve this particular instance, the session holding the lock must perform a COMMIT or ROLLBACK.

In mode 4:we have 3 reasons:

  1.     Unique key contention:

     In mode 4, a TX wait can occur if there is a potential duplicate in a unique index. When two sessions try to insert the same key value the second session must wait to see if an ORA-001 should be raised. This can cause the "enq: TX - row lock contention" wait event. This wait event can be handled by having the session holding the lock perform a COMMIT or ROLLBACK.

  2.     Foreign Key contention:

If referential constraints have been defined between tables and the foreign key columns in the child table are not indexed, then modifications to the parent table's key cause locks on the child table, and if the foreign key columns are not indexed this can cause contention.

  3.     Bitmap index contention:

The wait event “enq: TX – row lock contention” can also occur in mode 4 when a session is waiting on a shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same index fragment, then the second session must wait for the first session to perform either a COMMIT or a ROLLBACK by waiting for the TX lock in mode 4, otherwise it will generate the “enq: TX – row lock contention” wait event.  This index is suitable for data warehouse environments.

enq: HW - contention                  Configuration

enq: SQ - contention                   Configuration

enq: SS - contention                    Configuration

enq: ST - contention                   Configuration

enq: TM - contention                  Application

enq: TW - contention                  Administrative

enq: TX - allocate ITL entry       Configuration

enq: TX - index contention         Concurrency

enq: TX - row lock contention      Application

enq: TX – contention                    Application

select
           event, 
           p1,
           mod(p1,16)  as "mode"
    from v$active_session_history
    where event like 'enq:%‘;

select        count(*) cnt, 
                 session_id sid,
                 substr(event,1,30) event, 
                 mod(p1,16)  as lm,
                 sql_id,
                 CURRENT_OBJ# || ' ' || object_name obj
               , o.object_type type
               , CURRENT_FILE# file#
               , CURRENT_BLOCK#  block#
               , blocking_session bsid
    from v$active_session_history ash,
         all_objects o
    where
            event  like 'enq: T%'
      and o.object_id (+)= ash.current_obj#
   group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
   order by  count(*);

  select    substr(event,1,30) event, sql_id,
             CURRENT_OBJ# || ' ' || object_name obj
           , o.object_type type
           , CURRENT_FILE# file#
           , CURRENT_BLOCK#  block#
    from v$active_session_history ash,
             ( select a.object_name, 
                      a.object_id,
                      decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
               from all_objects a, all_indexes i where 
               a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
    where
            event  like 'enq: TX%'
      and o.object_id (+)= ash.current_obj#
    order by sample_time;

set password life time to unlimited in Oracle

Error code: ORA-28032

Description: Your password has expired and the database is set to read-only

Cause:
Your password expired and needs to be changed before your login request can be processed. Because the database is currently open in read-only mode, the password change operation cannot be performed on this database.

How to modify password life time in oracle for resolving above error. The following statement modifies the profile:

Action:
Log in to the primary database to change your password, then retry your login request at the standby database.

SQL> ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

The most important Data Guard Background processes

• Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence. 

• Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database. the Remote File Server (RFS) process receives the redo data and writes it to a Standby Redo Log (SRL). RFS sends an acknowledgement back to the production database that the redo has been received and written to disk. When the production database receives this acknowledgement, it acknowledges the commit to the client application and processes the next transaction. 

• Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).

• Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.

• Logical Standby Process (LSP) - The LSP applies the redo records from archived redo logs to the logical standby database. The Oracle database log miner engine is used by the logical standby process for the SQL apply operations. Using the log miner engine, the LSP process recreates the SQL statements from redo logs that have been executed on the primary database. These statements are then applied to the standby database to keep it current with the primary database.

Change Data guard Protection Mode

Change data guard protection mode from Maximum Performance to Maximum Availability.

The default is Maximum Performance.

SYNC | ASYNC = Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM = Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT = Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN = Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability. 
so here LGWR is directly connected with RFS,henc there will be no LNS process. 

Check Primary database:

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;



 PROTECTION_MODE      PROTECTION_LEVEL

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

 MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

---On Primary Database

 SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;


  STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
  ------ ------------- ------------- --------------------

  OPEN    orcl          PRIMARY       MAXIMUM PERFORMANCE

 SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTBY LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstby' scope=both;

 SQL> alter database set standby database to maximize availability;

---On Standby Database

 SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 SQL> alter database recover managed standby database cancel;

 SQL>  alter system set log_archive_dest_2='SERVICE=ORCL LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;

 SQL> alter database recover managed standby database using current logfile disconnect from session;

 stby->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

 PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


 prod-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

 PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------

 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY