11gR2 RAC -Find configured private and public interfaces on the cluster

Clusterware uses the interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster. The volume and traffic patterns of this type of data, shared between nodes can vary greatly depending on the applications.

Protocol
Unix and Linux

cluster interconnect IPC version:Oracle UDP/IP (generic)

Windows:

cluster interconnect IPC version:Oracle 9i Winsock2 TCP/IP IPC

in 10gR1 x$ tables x$ksxpia and x$skgxpia provide the same information.


>srvctl config nodeapps -a

The value of the private interconnect for an instance can be identified using

1. The views V$CLUSTER_INTERCONNECTS and V$CONFIGURED_INTERCONNECTS
   
   V$CLUSTER_INTERCONNECTS displays one or more interconnects that are being used for cluster communication.
   
   V$CONFIGURED_INTERCONNECTS displays all the interconnects that Oracle is aware of. This view aims to answer the question on where Oracle found the information about a specific interconnect.
   
   These views are not available in Oracle 10g 10.1.
   
   
2. The alert.log
   
   • CLUSTER_INTERCONNECTS parameter is set
   • Value read from Cluster Registry

olsnodes -n -p can be used to identify the private node name.

You may also check the private node name in the ocrdump output.

ocssd.log has a line with clssnmClusterListener

References:
NOTE:283684.1 - How to Change Interconnect/Public Interface IP or Subnet in Oracle Clusterware
NOTE:341788.1 - Recommendation for the Real Application Cluster Interconnect and Jumbo Frames
NOTE:368464.1 - How to Setup IPMP as Cluster Interconnect

How to use adcri in 11g

unxqrdb02cdp(DBNAME2) /unx01/u0002/SR
>adrci

ADRCI: Release 11.2.0.1.0 - Production on Tue Mar 27 09:26:27 2012

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

ADR base = "/unx01/u0001/app"
adrci> show home
ADR Homes:
diag/rdbms/unxqc/unxQC2
diag/rdbms/unxqa/unxQA2
diag/rdbms/DBNAME/DBNAME2
adrci> set homepath diag/tnslsnr/unxqrdb02cdp/listener_DBNAME
adrci> show home
ADR Homes:
diag/tnslsnr/unxqrdb02cdp/listener_DBNAME
adrci> set homepath diag/rdbms/DBNAME/DBNAME2
adrci> show home
ADR Homes:
diag/rdbms/DBNAME/DBNAME2
adrci> show problem



adrci> IPS pack problem 4 in /unx01/u0002/SR
Generated package 2 in file /unx01/u0002/SR/ORA7445st_20120327092843_COM_1.zip, mode complete

adrci> IPS pack problem 5 in /unx01/u0002/SR
Generated package 3 in file /unx01/u0002/SR/ORA600135_20120327093137_COM_1.zip, mode complete
adrci>

ORA-00280: change 12613586372514 for thread 2 is in sequence #1170

Recovering a datafile from the archivelogs:

SQL> select name from v$recover_file;
'+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'




SQL> alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online;
alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39: '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'


Start Manual recovery:


SQL> recover datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'
ORA-00279: change 12613586221711 generated at 02/27/2012 22:27:23 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021
ORA-00280: change 12613586221711 for thread 2 is in sequence #1167


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782


** I don't have this log in the FRA ********
ORA-00308: cannot open archived log '+DBNAME_FRA_01'
ORA-17503: ksfdopn:2 Failed to open file +DBNAME_FRA_01
ORA-15045: ASM file name '+DBNAME_FRA_01' is not in reference form

Restore the log from the backup:

RMAN> connect target /

connected to target database: DBNAME (DBID=1279696963)

RMAN> restore archivelog sequence 782 thread 1;

Starting restore at 28-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7898 instance=DBNAME1 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=782
channel ORA_DISK_1: reading from backup piece /ora_backup/u0002/DBNAME1/archivelog/ARC_DBNAME1_20120227_222702_s1048_p1_0on4dcav
channel ORA_DISK_1: piece handle=/ora_backup/u0002/DBNAME1/archivelog/ARC_DBNAME1_20120227_222702_s1048_p1_0on4dcav tag=TAG20120227T222741
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 28-FEB-12


Now start the recovery again.

SQL> recover datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101'
ORA-00279: change 12613586221711 generated at 02/27/2012 22:27:23 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021
ORA-00280: change 12613586221711 for thread 2 is in sequence #1167


Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021'
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782




Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1167.882.776418021'
ORA-00279: change 12613586221711 generated at 02/26/2012 12:00:46 needed for
thread 1
ORA-00289: suggestion : +DBNAME_FRA_01
ORA-00280: change 12613586221711 for thread 1 is in sequence #782


Specify log: {=suggested | filename | AUTO | CANCEL}
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_782.866.776423291'
ORA-00279: change 12613586221719 generated at 02/27/2012 22:27:24 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_783.348.776418019
ORA-00280: change 12613586221719 for thread 1 is in sequence #783


Specify log: {=suggested | filename | AUTO | CANCEL}
auto *********Now all the archive logs are in the FRA so I am starting the auto recovery***
ORA-00279: change 12613586349995 generated at 02/28/2012 07:40:17 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_784.374.776418019
ORA-00280: change 12613586349995 for thread 1 is in sequence #784


ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_784.374.776418019' no
longer needed for this recovery


ORA-00279: change 12613586350043 generated at 02/28/2012 07:40:20 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1168.489.776418183
ORA-00280: change 12613586350043 for thread 2 is in sequence #1168


ORA-00279: change 12613586350420 generated at 02/28/2012 07:40:54 needed for
thread 1
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_1_seq_785.1231.776418929
ORA-00280: change 12613586350420 for thread 1 is in sequence #785


ORA-00279: change 12613586371543 generated at 02/28/2012 07:43:02 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1169.431.776418183
ORA-00280: change 12613586371543 for thread 2 is in sequence #1169


ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file
'+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1169.431.776418183' no
longer needed for this recovery


ORA-00279: change 12613586372514 generated at 02/28/2012 07:47:37 needed for
thread 2
ORA-00289: suggestion :
+DBNAME_FRA_01/dbname/archivelog/2012_02_28/thread_2_seq_1170.335.776418927
ORA-00280: change 12613586372514 for thread 2 is in sequence #1170


Log applied.
Media recovery complete.
SQL> alter database datafile '+DBNAME_DATA_01/dbname/datafile/undotbs2.449.755961101' online;

Database altered.

ORA-25152: TEMPFILE cannot be dropped at this time

While trying to drop the tempfile, I was getting below error.


SQL> create temporary tablespace temp1;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.


SQL>select username,temporary_tablespace from dba_users where temporary_tablespace='TEMP3'
no rows selected




SQL> alter database tempfile '+ICMQA_DATA_01/icmqa/tempfile/temp3.473.774200997' drop including datafiles;
alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

find out which sessions are still using the old temp tablespace.

SELECT a.INST_ID,b.TABLESPACE
, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;



Now kill all those sessions
SQL> alter system kill session '7618,4566' IMMEDIATE;

System altered.

or if there are more sessions like in the screenshot create the script to kill all the sessions as below:


SELECT 'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||''' IMMEDIATE;'
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size'
AND a.saddr = b.session_addr
AND a.paddr = c.addr
and b.tablespace='TEMP3' <-- change the tablespace name
and a.inst_id=2 <-- change the instance name
ORDER BY b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;




SQL> alter database tempfile '+DBNAME_DATA_01/dbname/tempfile/temp3.473.774200997' drop including datafiles;

Database altered.

SQL>

drop bad disk from the ASM

1>Please check with your system/Storage administrator on this disk level issue ,make sure not a single block get overwritten.

2>If you have enough space on other disks to hold data of this effected disk,drop that disk ,seems there is some OS level corruption at this disk level ,so it is not able to read that block.

sql> alter diskgroup PROD_DATA_01 drop disk PROD_DATA_01_0045 rebalance power 5;

Wait for rebalance to complete .

from ASM sqlplus till below query returns "no rows selected"

sql> select * from v$asm_operation ;

If you does not have enough space then add a new disk to this diskgroup and the drop this disk ,

Remember ,both separate operation will cause rebalance operation to kick-off,so you have to allow both of them complete.


3> Then start the database on mount stage,

sql> startup mount;

Then check any datafile needs recovery or not,

select * from v$recover_file;

4> If required recovery then ,do recovery of those specific datafiles.

5> Then open the database .


Note:: If after dropping you face any issue related to spfile ,then please recreate the spfile (As it is in same effected diskgroup).
Then refer to below document ,

Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM (Doc ID 554120.1)