Every thing about RAC and GRID Infra

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Doc ID 810394.1) 

Export issue : UDE-00014: invalid value for parameter, 'attach'

Running expdp with sys as sysdba user:
============================

[oracle@RAC1 ~]$   /opt/oracle/product/11.2.0.3/db_1/bin/expdp \'/ as sysdba \'/ directory=expdp_22Nov14 dumpfile=expdp_22Nov14_LISTING_content_%U logfile=expdp_22Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4

Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:45:54 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
UDE-00014: invalid value for parameter, 'attach'.

Correct Syntax:
==========


[oracle@RAC1 ~]$  /opt/oracle/product/11.2.0.3/db_1/bin/expdp  \'/ as sysdba\' directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY
 logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING' content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4


Export: Release 11.2.0.3.0 - Production on Mon Nov 24 01:56:51 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=EXPDP_21NOV14 dumpfile=expdp_24Nov14_LISTING_content_%U content=METADATA_ONLY logfile=expdp_24Nov14_LISTING_content.log tables='EME.BMLS_LISTING'  content=METADATA_ONLY exclude=constraint,ref_constraint,index,statisstatistics parallel=4
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /orabackup/expdp_21Nov14/expdp_24Nov14_LISTING_content_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 01:57:45


Also you can try:

expdp \"SYS@service AS SYSDBA\"

ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora

[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp
PRCR-1079 : Failed to start resource ora.ractp.db
CRS-5017: The resource action "ora.ractp.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/ractp/spfileractp.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ractp/spfileractp.ora
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 13: Permission denied
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.3/grid_11203/log/rac1/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.ractp.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.ractp.db' on that would satisfy its placement policy

User: oracle

RAC 1  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 01:41 oracle
RAC 2  :  -rwsr-s--x. 1 oracle oinstall 222M Nov 21 02:09 oracle

User Grid:

RAC  1 : -rwxrwxr-x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle
RAC  2 : -rwsr-s--x. 1 grid oinstall 203972171 Nov 19 13:04 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to root and change the permission on RAC1 to 6751 :

chmod 6751 /u01/app/11.2.0.3/grid_11203/bin/oracle

-rwsr-s--x. 1 grid oinstall 203972171 Nov 19 12:55 /u01/app/11.2.0.3/grid_11203/bin/oracle

Switch to oracle user:

[oracle@RAC1 ~]$ srvctl start instance -i ractp1 -d ractp

[oracle@RAC1 ~]$ ps -ef|grep pmon
grid      8860     1  0 22:05 ?        00:00:00 asm_pmon_+ASM1
oracle   10676     1  0 23:00 ?        00:00:00 ora_pmon_ractp1

[oracle@RAC1 ~]$ srvctl status database -d ractp
Instance ractp1 is running on node rac1
Instance ractp2 is running on node rac2

RAC listener Commands

[grid@RAC1 ~]$ ps -ef|grep LISTENER

grid      4008     1  0 00:36 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit
grid      4179     1  0 00:39 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN1 -inherit


oracle Local listener:

lsnrcl status check run in grid home :  check the user who is running listerner from ps -ef output:

[grid@RAC1 ~]$ lsnrctl status LISTENER_SCAN1
[grid@RAC1 ~]$ lsnrctl status LISTENER

[grid@RAC1 admin]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac1
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac2
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac2


[grid@RAC1 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac2

[oracle@RAC1 ~]$ show parameter listener
------------------------------------------
local_listener     string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1521))))
remote_listener     string RAC-SCAN.localdomain:1521

GRID_HOME:
cd $ORACLE_HOME/network/admin/
cd /u01/app/11.2.0.3/grid_11203/network/admin

cat $GRID_HOME/network/admin/listener.oracle
---------------------------------------------

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

Listener Log File         /u01/app/grid/diag/tnslsnr/RAC1/listener/alert/log.xml


cat $GRID_HOME/network/admin/endpoints_listener.ora
----------------------------------------------------
LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.71)(PORT=1521)(IP=FIRST)))) # line added by Agent

Listener Log File: $GRID_HOME/log/diag/tnslsnr/RAC1/listener_scan1/alert/log.xml


[grid@RAC1 admin]$ srvctl config scan
SCAN name: RAC-SCAN.localdomain, Network: 1/192.168.56.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /RAC-SCAN.localdomain/192.168.56.91
SCAN VIP name: scan2, IP: /RAC-SCAN.localdomain/192.168.56.92
SCAN VIP name: scan3, IP: /RAC-SCAN.localdomain/192.168.56.93

[grid@RAC1 admin]$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[grid@RAC1 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2


NODE RAC2:

[grid@RAC2 ~]$ ps -ef|grep LISTENER

[grid@RAC2 admin]$ crsctl status resource -w 'TYPE = ora.scan_vip.type' -t
NAME           TARGET      STATE        SERVER                   STATE_DETAILS    
-------------------------------------------------------------------------------
ora.scan1.vip    1        ONLINE        ONLINE                    rac1                                      
ora.scan2.vip    1 `       ONLINE       ONLINE    rac2
ora.scan3.vip    1        ONLINE        ONLINE                    rac2

[grid@RAC2 ~]$ ps -ef|grep LISTENER

grid      3948     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN3 -inherit
grid      3950     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER_SCAN2 -inherit
grid      4016     1  0 00:35 ?        00:00:00 /u01/app/11.2.0.3/grid_11203/bin/tnslsnr LISTENER -inherit

Compare Scan on both nodes:
----------------------------
[grid@RAC1 admin]$ cluvfy comp scan  

Issue #3: Service not registered with SCAN listener after SCAN listener failover:

After SCAN VIP and SCAN listener failover, instance does not register with the SCAN listener. It might happen for only 1 of the scan listener. Client connection
gets intermittent ORA-12514 TNS:listener does not currently know of service requested in connect descriptor.

Cause:
1. Unpublished Bug 12659561 after scan listener failover, database instance might not register to the scan listener (refer Note 12659561.8), fixed in 11.2.0.3.2,
merge patch 13354057 for 11.2.0.2 available for certain platform.
2. Unpublished Bug 13066936 Instance does not register services when scan fails over (refer Note 13066936.8)

Solution:

1) For both above bugs, the workaround is to unregister and register remote listener on the database instance which does not
register to a SCAN listener with following steps.
show parameter remote_listener
alter system set remote_listener='';
alter system register;
alter system set remote_listener='<scan>:<port>';
alter system register;
2) Other points to check if service is not registered with SCAN listener:
a. remote_listener and local_listener is defined correctly
b. EZCONNECT is defined in sqlnet.ora, eg: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
c. SCAN name is defined in /etc/hosts or DNS and whether there is any mismatch if it is defined in both places
d. nslookup <scan> should display SCAN VIP in round-robin fashion
e. do not set SECURE_REGITER_<listener> in listener.ora if the class of secure transports (COST) is not configured.



11G R2: Oracle Cluster SCAN Listeners: Use easy connect naming method
=========================================================

Just recently I came across another unclear error message while troubleshooting an Oracle Instance registration with SCAN Listener. This post is one of the posts which may save quite a bit of time for people who will hit the same issue.

The errors message I got configuring a database just moved to run under 11GR2 Oracle Cluster control is as following:

1
2
3
4
5
6
7
8
9
10
SQL> alter system set remote_listener='scan.usolygrid-prod.cinram.com:1521';
alter system set remote_listener='scan.your.domain.com:1521'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name
'scan.your.domain.com:1521'

SQL>
I was quite sure that the syntax I used was 100% correct as other instances running on the same host used the same remote_listener parameter value.

It appears that someone adjusted the following string in sqlnet.ora file on the server:

1
2
3
4
from
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
to
NAMES.DIRECTORY_PATH= (TNSNAMES)
The change makes sense to me as it always good to have strict control on where tns names are sourced from on the servers side.

However it appears that Oracle use an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file. Strictly speaking you can use other tns names as the resolution method for the remote_listener parameter but by default it uses the easy connect naming method and you may face the error message shown above if you omit the EZCONNECT keyword.

Top 5 Issues That Cause Troubles with Scan VIP and Listeners (Doc ID 1373350.1)
--------------------------------------------------------------------------------

How to Find Sessions Generating Lots of Redo or Archive logs

To find sessions generating lots of redo, you can use either of the following
methods. Both methods examine the amount of undo generated. When a transaction
generates undo, it will automatically generate redo as well.

The methods are:
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
   how much blocks have been changed by the session. High values indicate a
   session generating lots of redo.

   The query you can use is:
       SQL> SELECT s.sid, s.serial#, s.username, s.program,
           i.block_changes
           FROM v$session s, v$sess_io i
           WHERE s.sid = i.sid
           ORDER BY 5 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
   undo blocks and undo records accessed by the transaction (as found in the
   USED_UBLK and USED_UREC columns).

  The query you can use is:
      SQL> SELECT s.sid, s.serial#, s.username, s.program,
          t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

   Run the query multiple times and examine the delta between each occurrence
   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
   the session.

You use the first query when you need to check for programs generating lots of
redo when these programs activate more than one transaction. The latter query
can be used to find out which particular transactions are generating redo.

How to check if your DB is open using spfile or pfile

If your DB is open using pfile:

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

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 

   FROM sys.v_$parameter WHERE name = 'spfile';

--- PFILE

If your DB is open using SPFILE:

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

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 

   FROM sys.v_$parameter WHERE name = 'spfile';

--SPFILE

sqlplus / as sysdba - ORA-01031 insufficient priv

Check the below:

1. check groups for oracle user. 
2.check password file is proper or not.
3. Try to see this line, SQLNET.AUTHENTICATION_SERVICES = (NTS), in sqlnet.ora file if exists. If yes, comment that and try to connect using / as sysdba.

Using Query level export

[oracle@vm1 ~]$ cat parameterFile.par

tables=MY_SCHEMA.DETAILED_REPORT,MY_SCHEMA.SUMMARY_REPORT
dumpfile=Sumit_expdp_Report.dmp
logfile=expdp_Sumit_Rpt.log
directory=EXPIMP
query=MY_SCHEMA.DETAILED_REPORT:"WHERE to_char(transfer_date,'MM-YY')='05-14'"
query=MY_SCHEMA.SUMMARY_REPORT:"WHERE to_char(transfer_date,'MM-YY')='05-14'"



Now run the export command:

expdp parfile=parameterFile.par ;

Requirement was to export the above mentioned tables with all data for MAY-2014 based on column transfer_date .

Gird and NFS : [FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location.

 Grid is very picky and somewhat uninformative about its NFS support

You need to trace the installer to find out what exactly it doesn’t like about your configuration.

Running the installer normally, the error message is:
[FATAL] [INS-41321] Invalid Oracle Cluster Registry (OCR) location.
CAUSE: The installer detects that the storage type of the location (/cmsstgdb/crs/ocr/ocr1) is not supported for Oracle Cluster Registry.
ACTION: Provide a supported storage location for the Oracle Cluster Registry.

OK, so Oracle says the storage is not supported, but I know that Netapp NFS is support just fine. This means I used the wrong parameters for the NFS mounts. But when I check my fstab and /etc/mount, everything looks A-OK. Can Oracle tell me what exactly bothers it?

It can. If you run the silent install by adding the following flags to the command line:
-J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2

Then you will see the following lines that explain why Oracle does not like your storage:

[main] [ 2011-01-04 23:43:55.184 GMT+00:00 ] [TaskSharedStorageAccess.reportStorageExceptions:754] Adding exception for node [node01]:

[main] [ 2011-01-04 23:43:55.184 GMT+00:00 ] [TaskSharedStorageAccess.reportStorageExceptions:755] Exception message: Mount options did not meet the requirements [Expected = “rw,hard,rsize>=32768,wsize& gt;=32768,proto=tcp |tcp,vers=3|nfsvers=3|nfsv3|v3,timeo>=600, acregmin=0&acregmax=0&acdirmin=0& amp;acdirmax=0|actimeo=0? ; Found = “rw,vers=3,rsize=32768,wsize=32768, acregmin=0,acregmax=0, acdirmin=0,acdirmax=0,hard,proto=tcp,timeo=300,retrans=2,sec=sys, addr=fas01b”]

This way it was much easier to see that I had timeo=300 while Oracle wanted timeo>=600.

 Your NFS configuration is not what you think it is.

If /etc/fstab says “timeo=600? and running “mount” shows that the volume is mounted with “timeo=600?, why does Oracle thinks that the volume is mounted with “timeo=300??

Turns out that the right place to look if you want to know what is your real NFS configuration is in “/proc/mounts”. The man page for “mount” says:

It is possible that files /etc/mtab and /proc/mounts don’t match. The first file is based only on the mount command options, but the content of the second file also depends on the kernel and others settings (e.g. remote NFS server. In particular case the mount command may reports unreliable information about a NFS mount point and the /proc/mounts file usually contains more reliable information.)

Aha! So /proc/mounts shows that timeo=300, which causes installation to fail, and the man page says that this could be caused by remote NFS server settings. Perfect. The problem was packaged and sent to the customer’s sysadmin, and was solved by the next morning.

Archive log Report On Hourly Basis

col day for a10
col thread# format 9999 heading "Thread"
break on thread# skip 2;
set lines 500
set pages 300
set trimspool on

select thread#, to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
count(*) Total
from v$log_history
where first_time > sysdate - &DaysMinus
group by thread#, to_char(first_time,'YYYY-MM-DD') order by 2 ;