RAC Change SCAN IP

RAC 11G  

[root@rac1 bin]# ./crsctl stat res -t

--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS    
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                                      
               ONLINE  ONLINE       rac2                                      
ora.asm
               ONLINE  ONLINE       rac1                     Started          
               ONLINE  ONLINE       rac2                     Started          
ora.eons
               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                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1                                      
ora.oc4j
      1        OFFLINE OFFLINE                                                
ora.rac.db
      1        OFFLINE OFFLINE                                                
      2        OFFLINE OFFLINE                                                
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                      
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                      
ora.scan1.vip
      1        ONLINE  OFFLINE                                                

[root@rac1 bin]#
[root@rac1 bin]# ./srvctl stop scan_listener

[root@rac1 bin]# ./srvctl stop scan

[root@rac1 bin]# ./srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running

[root@rac1 bin]#
[root@rac1 bin]# ./srvctl config scan
SCAN name: rac-scan.localdomain, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.2.201

[root@rac1 bin]#



[root@rac1 bin]# ./srvctl stop scan_listener

[root@rac1 bin]# ./srvctl stop scan

[root@rac1 bin]# ./srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running

[root@rac1 bin]#
[root@rac1 bin]# ./srvctl config scan
SCAN name: rac-scan.localdomain, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.2.201

[root@rac1 bin]#
[root@rac1 bin]# vi /etc/hosts
[root@rac1 bin]#


[root@rac1 bin]# ./srvctl start scan
PRCR-1079 : Failed to start resource ora.scan1.vip
CRS-5009: The VIP address 192.168.2.201 does not belong to the subnet 192.168.0.0
CRS-2674: Start of 'ora.scan1.vip' on 'rac1' failed
CRS-5009: The VIP address 192.168.2.201 does not belong to the subnet 192.168.0.0
CRS-2674: Start of 'ora.scan1.vip' on 'rac2' failed
CRS-2632: There are no more servers to try to place resource 'ora.scan1.vip' on that would satisfy its placement policy

[root@rac1 bin]#
[root@rac1 bin]# ./srvctl modify scan -n rac-scan.localdomain
[root@rac1 bin]#

[root@rac1 bin]# ./srvctl start scan

[root@rac1 bin]# ./srvctl start scan_listener
[root@rac1 bin]#



-- ---------------------------------------------------------------------------------
-- VERIFICATION
-- ---------------------------------------------------------------------------------

[oracle@rac1 ~]$ oifcfg getif
eth1  192.168.0.0  global  cluster_interconnect
eth0  192.168.0.0  global  public

[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl config nodeapps -a
VIP exists.:rac1
VIP exists.: /rac1-vip/192.168.0.73/255.255.255.0/eth0
VIP exists.:rac2
VIP exists.: /rac2-vip/192.168.0.74/255.255.255.0/eth0

[oracle@rac1 ~]$

[oracle@rac1 ~]$ srvctl config scan
SCAN name: rac-scan.localdomain, Network: 1/192.168.0.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /rac-scan.localdomain/192.168.0.75

[oracle@rac1 ~]$ 

Datafile Dump

SQL> select header_file, header_block from dba_segments where segment_name = 'ABC_I';

 

HEADER_FILE HEADER_BLOCK

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

          5       289161

 

SQL> alter system dump datafile 5 block 289162;

 

System altered.







row#0[8019] flag: ------, lock: 0, len=17

col 0; len 3; (3):  43 42 41

col 1; len 3; (3):  47 46 45

col 2; len 6; (6):  01 44 69 02 00 00

Row Lock Contention

Tuning idea:
------------------

1. I'm thinking to reorganize hot tables as well as their indexes, but by instinct it seems to not give so much value to avoid the huge row lock wait time.

2. I'm also seeing if I can reduce the number of rows per block, by increasing PCTFREE and diminishing PCTUSED, so the contention will spread over many blocks instead of one heavy block.

Question
As SQL stment related to those locked tables are select ... for update, how could I tune this kind of stment?
Does someone have other idea to come up with this row lock contention?

Ans
Increase the initrans value of that object appearing in the select statement.


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

But I have first to know how many initrans are allocated in the targetted table.
To do that, I need to do the following:

1. record one block number

SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),COUNT(*) NUMBER_ROWS_PER_BLOCK FROM  SIMANG_D.INWARD GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) Order by COUNT(*) desc
/

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) NUMBER_ROWS_PER_BLOCK

--------------------------------------------------------------------------------
---------------------
226534 5
226530 5
226504 5
226533 5
226538 5
226535 5
226525 5
226526 5
226532 5
228092 4

2. Dump the contents of a block through the following command

ALTER SYSTEM DUMP DATAFILE 4 BLOCK 226525;

3. see the result

Block header dump: 0x010374dd
Object id on Block? Y
seg/obj: 0xd863 csc: 0x00.cafe8 itc: 169 flg: E typ: 1 – DATA (max itl=169) brn: 1 bdba: 0x10374d1 ver: 0x01 opc: 0
inc: 0 exflg: 0

from this example, the initrans value is 169.

So, I need first to have the data locally then, check the initrans value before change it.
furthermore as I know, the new initrans won't affect current block, but new allocated, so I have to Rebuild the table by changing the initrans value.

Question.
What is the overhead when dumping a contents of a block from the production database as I specified above?

Detect Index Leaf Block Contention

Index block contention can be tricky to diagnose and repair, especially since Oracle does not have many direct tuning knobs for tuning for contention.

The wait “enq: TX – index contention” indicates a wait on an index, and in a RAC environment you might also see “gc” (global cache) waits on indexes:

gc buffer busy waits on Index Branch Blocks
gc buffer busy waits on Index Leaf Blocks
gc current block busy on Remote Undo Headers
gc current split
gcs ast xid
gcs refuse xid


There are many ways to Fixing Oracle index contention (RAC / NON-RAC ENV.)

There are three techniques that are used to relieve this index contention issue:

       1. Reverse key indexes
       2. Sequences with the cache and noorder options
       3. Using hash partitioned global indexes
       4. Adjusting the index block size


select sid, sql_text
from  v$session s, v$sql q
where    sid in (select sid from v$session where state in ('WAITING')
   and   wait_class != 'Idle'
   and   event='enq: TX - index contention'
   and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

WAIT EVENTS

Detection / Contention and Solution:

DB File Scattered Read

DB File Sequential Read

Read By Other Session / Buffer busy wait / Segment buffer busy wait / Block Contention

Free Buffer Wait

Log file sync

Log file parallel write

Log File Switch

Log Buffer Space

Direct Path Writes

Direct Path Reads

Shared pool latch

Library cache latch

Latch Free Waits

Cache Buffer LRU Chain Latch

Cache Buffer Chain Latch

Enqueue Wait

Row cache objects latch

Idle Event

RDBMS IPC Message

Row Lock Contention

Index Leaf Block Contention


RAC : Wait Events

current read block - locally instance read block
cr block - consistent read block

• Block Oriented Wait  

                  gc current block 2-way

                  gc current block 3-way

                  gc cr block 2-way

                  gc cr block 3-way

• Message Oriented Wait 

                  gc current grant 2-way

                  gc current grant 3-way

                  gc cr grant 2-way

                  gc cr grant 3-way (Normally this wait event is not possible, but "_cr_grant_local_role" => turn 3-way CR grants off, make it automatic, or turn it on) 

• Contention Oriented Wait 

                  gc current block busy (cluster cache contention)

                  gc cr block busy

                  gc current buffer busy (local cache contention)

                  gc cr buffer busy (Remote cache contention)

• Load Oriented Wait  

                  gc current block congested

                  gc cr block congested

                  gc current grant congested

                  gc cr grant congested

OTHERS:

gc current block lost : Lost blocks due to Interconnect or CPU. Indicates interconnect issues and contention.

gc cr block lost : Lost blocks due to Interconnect or CPU. Indicates interconnect issues and contention.

gc current multi block request : Full table or index scans.

gc cr multi block request : Full table or index scans.

gc cr request : The time it takes to retrieve the data from the remote cache. Oracle may not pick
                        private interconnect and instead route traffic over slower public network.
                        RAC event similar to buffer busy waits, tune SQL to request less data, tune network
                        latency between RAC nodes, localize data access.

gc current/cr failure/retry : A block is requested and a failure status received or some other
                                            exceptional event has occured.

Reference :

ID 1911398.1 - LMS & CPU issue.

HUGE ARCHIVE LOG GENERATION

Checks Points. In Case face huge archive log generation type of issues.

1. Check Jobs, schedules.
2. Check DB activity like SQL,TABLE,PROCEDURE, TRIGGER.
3. Session wise SQL redo generates.
4. Redo log group full / small redo log file size.
5. DB link distributed work.
6. Session lock.
7. Dead locks.
8. Generate AWR/ASH/ADDM report (see section Top 5 Wait Event,Physical Segemnt/Logical).

Note:- Non Impact (SMON redo generation, fregmentation)



***********************************************************************************


## Archiving generation report:
Per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

***********************************************************************************

## Date & Time wise log generation.

set pages 1000
select to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'DD-MM-YYYY hh24:mm'),thread# order by 1 ;

Note: See the output Date & time when archive log frequently generates. After that create AWR/ASH report and see the TOP wait events.

***********************************************************************************

##  Hour wise archive generation:

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23", count(*) TOT
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

***********************************************************************************

##  Archive generation number with volume:  day/hour wise

select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) GB,count(*)  Archives_Generated from v$archived_log group by trunc(COMPLETION_TIME,'DD'), thread# order by 1;

select to_char(COMPLETION_TIME,'DD-MON-YY HH24') Hour, round(sum(BLOCKS*BLOCK_SIZE)/1048576/1024) gb, count(*)  Archives from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YY HH24') order by 1 ;

***********************************************************************************

##  Currently session wise redo generation:

select b.inst_id,
       lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
       b.username,
       machine,
       b.osuser,
       b.status,
       a.redo_mb
from (select n.inst_id, sid,
             round(value/1024/1024) redo_mb
        from gv$statname n, gv$sesstat s
        where n.inst_id=s.inst_id
              and n.name = 'redo size'
              and s.statistic# = n.statistic#
        order by value desc
     ) a,
     gv$session b
where b.inst_id=a.inst_id
  and a.sid = b.sid
and   rownum <= 30
;

***********************************************************************************

##   Which segments are generating redo logs:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
        dhso.object_name,
        sum(db_block_changes_delta) BLOCK_CHANGED
  FROM dba_hist_seg_stat dhss,
       dba_hist_seg_stat_obj dhso,
       dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhso.obj#
    AND dhss.dataobj# = dhso.dataobj#
    AND begin_interval_time BETWEEN to_date('15-10-31 08:00','YY-MM-DD HH24:MI')
                                AND to_date('15-10-31 10:00','YY-MM-DD HH24:MI')
  GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
           dhso.object_name
  HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;

***********************************************************************************

##  What sql was causing the redo log generation:

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
       dbms_lob.substr(sql_text,4000,1) SQL,
       dhss.instance_number INST_ID,
       dhss.sql_id,
       executions_delta exec_delta,
       rows_processed_delta rows_proc_delta
  FROM dba_hist_sqlstat dhss,
       dba_hist_snapshot dhs,
       dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%USR_RACUNI_MV%'
    AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_number=dhs.instance_number
    AND dhss.sql_id=dhst.sql_id
    AND begin_interval_time BETWEEN to_date('15-10-31 07:00','YY-MM-DD HH24:MI')
                                AND to_date('15-10-31 16:00','YY-MM-DD HH24:MI');


***********************************************************************************

## Session wise programe redo entris genrate


col username for a15
col program for a20
col name for a20

select * from
(select /*+ first_rows */ a.username,a.sid,a.program,b.name,c.value
from v$session a,v$statname b,v$sesstat c
where a.sid=c.sid and b.statistic#=c.statistic# and b.name='redo entries' order by  c.value desc)
where rownum < 11
;


***********************************************************************************

## For RAC Env.
Author: Riyaj Shamsudeen

spool redosize.log
col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT  sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time ,  startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY  startup_time, sysst.instance_number
                ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY    FROM (end_interval_time-begin_interval_time))*24*60*60+
            EXTRACT (HOUR   FROM (end_interval_time-begin_interval_time))*60*60+
            EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
            EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-90
)
select instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value) redo1
from redo_sz
group by  instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/
spool off;

How to truncate Audit table (SYS. AUD$).

Friends, Recently I was trying to Truncate the SYS.AUD$ table, but it won't allow me to do

 the same,because of application is running. (got the error like "resources busy") and found there are lots of DML operation are inserting the record in audit table. 

Use the below command to truncate the audit table.

1) Active and Inactive session.

select status,username,last_call_et from v$session where username is not null;

2) check the long running queries.

col USERNAME for a15
Set lines 2000
set pages 200
col MODULE for a20
col SQL_TEXT for a75
col machine for a20
select distinct s.username,s.sid,s.serial#,round(s.last_call_et/60,2) "MINS_RUNNING",s.module,s.machine,q.sql_text from v$session s
join v$sqlarea q
on s.sql_address(+) = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and s.last_call_et > 1800
and q.sql_text not like '%begin%'
order by sid,serial#;
 

3) Try to Truncate the SYS.AUD$ table.

SQL> Truncate table sys.aud$.

ERROR: ORA-00054: resource busy and acquire with NOWAIT ...

4) Inform Application team for shutdown the application.

5) Shutdown the Database as well.

SQL> shutdown immediate;

6) Restart the database..

SQL> Startup

7) Take the Audit table export backup by using normal EXP command.

$ exp file=EXP_AUD_TABLE_<SID><date>.dmp LOG=EXP_AUD_TABLE_<SID><date>.log Tables=SYS.AUD$ buffer=1000000.

The above command takes some time to export the table data.

8) Truncate audit table to reduce size.

SQL>CONN / as sysdba

SQL>TRUNCATE TABLE sys.aud$;

Inform application team to start the Application.