NFS Mount options for use with GoldenGate

NFS Client OS    Mount options for Oracle GoldenGate Files         

SUN Solaris          rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,
                             noac,forcedirectio,vers=3,suid

AIX (5L)              rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,
                             vers=3,timeo=600

HPUX11.23        rw,bg,vers=3,proto=tcp,noac,forcedirectio,hard,nointr,
                            timeo=600,rsize=32768, wsize=32768, suid
      
Linux                   rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,
(x86-32/64/         vers=3,timeo=600
Itanium)  
     


NFS Server Operating System          Additional Mount option on 
                                                                 NFS server local disk 

SUN Solaris                                              forecedirectio
HPUX 11.23                                             no_fs_async
Linux (x86-32/64/Itanium)                  sync
NetApp (Data OnTap)                             Optional FlexCache system must be
                                                                disabled

Find user's ip address.

Below query find user's ip adderss  to use UTL_INADDR package.

select sid, machine,UTL_INADDR.get_host_address
(substr(machine,instr(machine,'\')+1))ip from v$session 
where type='USER' and username is not null order by sid;

Identify files with imbalanced allocation in an ASM diskgroup

Below SQL script to identify files with imbalanced extents across the disks in an ASM diskgroup.

Set ORACLE_SID is point to ASM instance.


select number_kffxp file#, max(count1) max,min(count1) min from
(select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and disk_kffxp != 65534
group by number_kffxp,disk_kffxp
order by number_kffxp,disk_kffxp)
group by number_kffxp;


 FILE#        MAX        MIN
---------- ---------- ----------
      3242      2523       3414
      3243      3893       6824
      3244      7853      10581
      3924     10583     10581
      4098      6828       6827


 Max and min columns should be close to each other for balanced files



From the output above, files with imbalanced extents are file# 3242, 3243 and 3244.

File# 3924 and 4098 have extents almost equally balanced. Small imbalances are expected.

Number of extents per disk can be drilled down for certain files by a query like:

select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and number_kffxp=<number_kffxp>





RAID type for usage Oracle Database

**************************************************************************
RAID    Type of RAID        Control         Database          Redo Log         Archive
                                               File                File                     File            log File
**************************************************************************
   0          Striping                   Avoid*           OK*                 Avoid*          Avoid* 
--------------------------------------------------------------------------------------
   1        Shadowing                 OK               OK                   Reco              Reco
--------------------------------------------------------------------------------------
0+1    Striping +Shadowing     OK            Reco $               OK               Avoid    
--------------------------------------------------------------------------------------
    3     Striping with                 OK            Avoid &           Avoid             Avoid
           Static Parity                                                                                         
--------------------------------------------------------------------------------------
    5     Striping with                OK           Avoid &           Avoid              Avoid
         Rotating Parity                     
--------------------------------------------------------------------------------------


*  -  RAID 0 does not provide any protection against failures. It requires a strong backup strategy.

$ -   RAID 0+1 is recommended for database files because this avoids hot spots and gives  the best possible        performance during a disk failure.  The disadvantage of RAID 0+1 is that it is a costly configuration.

& - When heavy write operation involves this datafile


Reco -  Recommended

Using the Temporary Tablespace

Oracle try to use sort and hash operation in memory(PGA). Suppose if operation is too  long, it  use temporary tablespace. Even single  transaction try to use full temporary tablespace.  Once temp tablespace is full other session ‘s SQL also try to use  temp tablespace.

So we can get ORA error ORA-01652: unable  to extend temp  segment error.

     1. New Sessions may not be able to connect.

     2. Queries can sometimes hang.

     3. Users may  not able to issue new query.

If temp tablespace is full, transaction won’t   complete. We can find error into alert log file also.

Use below query to fine out which SQL statment is using Temporay Tablespace.

 select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name  order by s.sid;

Below query find out which session are using space in the memory.

select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser, p.spid,

s.module,s.program,sum (o.blocks) * t.block_size / 1024 / 1024 mb_used, o.tablespace,count(*) sorts from v$sort_usage o, v$session s, dba_tablespaces t, v$process p where o.session_addr = s.saddr

and s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,s.program, t.block_size, o.tablespace  order by sid_serial;

Find Blocks in the Buffer cache

Use below  below query to find how many blocks  for each segment are currently in the buffer Cache.

RAC Database:


select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);

Non-RAC Database:


select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);

Excluding Tablespace from RMAN Backup

You have a tablespace with test data that you don't need to backup. You can exclude such tablespaces from a whole backup of the database.

First find out any tablespace are already configured to be excluded from backups:

RMAN> show exclude;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters

Use following command to exclude tablespace from whole database.

RMAN> configure exclude  for tablespace users;

Tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

To make confirm tablespace exclude in your RMAN backup

RMAN>  show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';

Now you want to include a previously excluded tablespace in your backup by using the following command.

RMAN> configure exclude  for tablespace users clear;


Tablespace USERS will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters



You want include all tablespace even you configured exclude tablespace. You use the 'noexclude' option as part of a backup database command.

RMAN>backup database noexclude;

Find Bind value in Oracle

Here are below  query that help you to find the value of bind variable.

1. From V$sql_bind_capture:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING, WAS_CAPTURED, LAST_CAPTURED
FROM v$sql_bind_capture WHERE sql_id='8v176wjqww15y';

2. From RAC:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING,WAS_CAPTURED, LAST_CAPTURED
FROM gv$sql_bind_capture WHERE sql_id='8v176wjqww15y' and inst_id=1;

3. From DBA_HIST_SQLBIND:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING,WAS_CAPTURED, LAST_CAPTURED
FROM DBA_HIST_SQLBIND WHERE SQL_ID='8v176wjqww15y' and SNAP_ID='12345';

Relationship Between Init.ora Parameters and Unix, Linux Kernel Parameters

The following table documents Unix and Linux kernel parameters that should be monitored and possibly increased after changes are made to the related init.ora parameter. Please check with your Operating System documentation for specific details on the parameter changes.

                                    Init.ora Parameter                        Kernel Parameter

db_block_buffers                            shmmax, shmall

db_files(maxdatafiles)                    nfile, maxfiles

large_pool_size                                shmmax, shmall

log_buffer                                        shmmax, shmall

processes                                        nproc, semmsl, semmns

shared_pool_size                            shmmax, shmall

GGS ERROR 500 Oracle GoldenGate Capture for Oracle, ext1.prm: Could not find archived log for sequence

Oracle GoldenGate - Version: 10.4.0.19 and later   [Release: 10.4.0 and later ]
Information in this document applies to any platform.

Symptoms

ORACLE RAC Extract fails with the error
GGS Error 500 Could Not Find Archived Log For Sequence
The error message describes an archive log for a thread is not available when it is on disk.
A diagnostic is to run the query that OGG is running to see if you return more than one row which is a symptom of the a known bug.
Here is the query that will help you diagnose the issue
SELECT sequence#, DECODE(archived, 'YES', 1, 0) FROM v$log WHERE
sequence# = (select max(sequence#) from v$log where first_time <
TO_DATE('timeinyourerrormessage','YYYY-MM-DD HH24:MI:SS') AND thread# = <thread # in error message> );

here is an example error and the pieces that were used to complete the query :-
GGS ERROR 500 Oracle GoldenGate Capture for Oracle, e1.prm: Could not find archived log for sequence 13861 thread 7 under default destinations SQL <SELECT name FROM gv$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'N>, error retrieving redo file name for sequence 13861, archived = 1, use_alternate = 0 Not able to establish initial position for begin time 2010-07-22 03:02:40
Here is the completed query ready to be excuted against the DB :-
SELECT sequence#, DECODE(archived, 'YES', 1, 0) FROM v$log WHERE
sequence# = (select max(sequence#) from v$log where first_time <
TO_DATE('2010-07-22 03:02:40','YYYY-MM-DD HH24:MI:SS') AND thread# = 7 );

When run returned the following output
SEQUENCE# DECODE(ARCHIVED,'YES',1,0)
13881 0
13881 1

Cause

The query executed in OGG to obtain the correct archive file for the thread returned more than one row. The query didn't take having multiple threads into account with log sequence # that were close in value, this is a known bug 9846123

Solution

Upgrade to 10.4.0.77 or higher, and restart your extract,
or do the following
1. switch log file
2. position extract to current sequence number minus 1.

 

error while loading shared libraries: libnnz11.so: wrong ELF class: ELFCLASS32

Golden Gate  issue.

Cause:
When you run ./ggsci from your host, you can see below error

./ggsci: error while loading shared libraries: libnnz11.so: wrong ELF class: ELFCLASS32

Solutions:
    You need setup  LD_LIBRARY_PATH in your host
for example
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib

ORA-39000 ORA-31640 And ORA-27054 Erorrs On Invoking DataPump Import

When you use Data pump to import data in your database , you can see below error.

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "<Dump file path>" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3

Here is Solution

Here are the mount options that need to be used for NFS volumes on Linux are:
rsize=32k, wsize=32k, hard, actimeo=0

Along with the error there are warning messages printed in the alert log with the mount options to be used.Sometimes you may need to do the following to allow datapump to work with NFS mounted files:
Set the event in the init.ora, to disable the mount point parameter checking:

event="10298 trace name context forever, level 32"
          
Note that the event 10298 skips all mount checks.  Setting this event has to be done with care. Verify settings with your System Administrator to ensure no datafiles are corrupted

How to Install ASMLIB and Configure

First find out Kernal version
> uname -r
2.6.18-164.11.1.el5> cat /proc/version
Linux version 2.6.18-164.11.1.el5(brewbuilder@ls20-bc2-13.build.redhat.com) (gcc version 4.1.2 20071124 (Red Hat 4.1.2-41)) #1 SMP Tue Apr 29 13:16:15 EDT 2008

> rpm -q kernel
kernel-2.6.18-164.11.1.el5 
Then you need download RPM packpage from oracle Website
http://www.oracle.com/technetwork/topics/linux/index-101839.html  

# rpm -Uvh oracleasm-support-2.1.7-1.el5.x86_64.rpm oracleasmlib-2.0.4-1.el5.x86_64.rpm
oracleasm-2.6.18-164.11.1.el5.x86_64.rpm
warning: oracleasm-support-2.1.7-1.el5.x86_64.rpm:
Header V3 DSA signature: NOKEY, key ID 4a7y5433
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.18-164.11.1.el5########################################### [ 67%]
3:oracleasmlib ########################################### [100%]
[root@]# /etc/init.d/oracleasm configure Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting <ENTER> without typing an answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
oracle Default group to own the driver interface []: dba or oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
　
[root@etc/init.d]# ./oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

How to findout ASMLIB available or not

[root@etc/init.d]# rpm -qa | grep oracleasm
oracleasm-support-2.1.7-1.el5
oracleasmlib-2.0.4-1.el5
oracleasm-2.6.18-164.11.1.el5
　
　
　
　
Make sure that Oracleasm configure command ran properly
　
Log on root
　
Execute the lsmod command (as root) to show the loaded oracleasm module.
The oracleasm module should be listed with a ‘Used by’ column setting of 1
[root@etc/init.d]# lsmod
Module Size Used by

oracleasm 84136 1
ipv6 420481 332
xfrm_nalgo 43845 1 ipv6
crypto_api 42177 1 xfrm_nalgo
autofs4 57289 0
hidp 83521 2
l2cap 89281 5 hidp

Execute the command cat /proc/filesystems and make sure that an entry named oracleasmfs exists in this filesystem listing
　
[root@etc/init.d]# cat /proc/filesystems
nodev sysfs
nodev rootfs
nodev bdev
nodev proc
nodev cpuset
nodev binfmt_misc
nodev debugfs
nodev securityfs
nodev sockfs
nodev usbfs
nodev pipefs
nodev futexfs
nodev tmpfs
nodev inotifyfs
nodev eventpollfs
nodev devpts
ext2
nodev ramfs
nodev hugetlbfs
iso9660
nodev mqueue
ext3
nodev rpc_pipefs
nodev nfs
nodev nfs4
nodev autofs
nodev asmdisk
nodev oracleasmfs

Execute the command df –ha. This should show you a that oracleasmfs is mounted on /dev/oracleasm
　
　
[root@etc/init.d]# df -ha
Filesystem Size Used Avail Use% Mounted on
/dev/sda7 5.9G 607M 5.0G 11% /
proc 0 0 0 - /proc
sysfs 0 0 0 - /sys
devpts 0 0 0 - /dev/pts
/dev/sda8 88G 18G 65G 22% /home
/dev/sda5 9.7G 217M 9.0G 3% /var
/dev/sda3 9.7G 152M 9.1G 2% /opt
/dev/sda2 9.7G 1.6G 7.7G 17% /usr
/dev/sda1 99M 12M 83M 12% /boot
tmpfs 16G 5.6G 11G 36% /dev/shm
none 0 0 0 - /proc/sys/fs/binfmt_misc
sunrpc 0 0 0 - /var/lib/nfs/rpc_pipefs
oracleasmfs 0 0 0 - /dev/oracleasm

The Network Adapter Could Not Establish Connection

Issue 1:
  Connecting to database with jdbc thin driver the following error is reported:
 

java.sql.SQLException: Io exception: The Network Adapter could not establish the connection at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111) at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145) at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254) at
oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:386) at
oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:413) at
oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:164)

The error can be caused by :

• A firewall between client and database server
• Database host does not exist in the etc/hosts file

Solution 1:  
   

1. Ensure that the database server host name exists in the etc/hosts file
2. Open the port used by java in the firewall

Issue 2:

      You implemented dataguard and your JDBC THIN client connections from your application server began failing shortly after. The class used to implement the JDBC driver is:
    oracle.jdbc.driver.OracleDriver

   Error message received:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

As of JDBC 11g the oracle.jdbc.OracleConnection interface has been enhanced. For better visibility and clarity, all connection properties are defined as constants in this interface. The package oracle.jdbc.driver, which was deprecated in Oracle Database release 9.0.1, is desupported in this release. Code having references to this package will not compile and run. You can use package oracle.jdbc instead of this package.

Solution 2:

       To implement the solution, please execute the following steps:

      Change the JDBC connection driver class in your application server
     from:
          oracle.jdbc.driver.OracleDriver
    to
         oracle.jdbc.OracleDriver

Issue 3:

An application using the JDBC Thin Driver 10.2.0.1.0 cannot connect to the database server. At connection time, it fails with the following error:

java.sql.SQLException: Io exception: The Network Adapter could not establish the connection Error Code: 17002

If the NIC card of the server is configured to support IPv4 and IPv6, some services may start with IPv6. Any client application that tries to connect using IPv4 to the service that is running with IPv6 (or viceversa) will receive a connection refused error. If the java application is trying to connect to the database using JDBC then the client application may just hang or fail with an Io exception error.
The JDK used version is :  1.4.2.

Solution 3:

  

The java.net.preferIPv4Stack property allows Java applications to connect , and accept connections from, both IPv4 and IPv6 hosts. Setting this property to true indicates that JVM will use IPv4 sockets.

Here are two solutions :

1. Indicate to the java machine to use ip protocol used is V4.
   For this, launch the java machine where the jdbc application is running with the following parameter -Djava.net.preferIPv4Stack=true
   As example :
         java -Djava.net.preferIPv4Stack=true jdbcTest
   
2. Use the ORACLE OCI  JDBC driver.

 

ORA-12516 TNS:listener could not find available handler with matching protocol

When ORA-12516 raise , you can see  below error in you application log

Caused by: org.jboss.util.NestedSQLException: Could not create connection; -
nested throwable: (java.sql.SQLException: Listener refused the connection with
the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol
stack
.......
......
       at
org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSour
ce.java:106)
        at
net.sf.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasour
ceConnectionProvider.java:59)
        at
net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:292)
        ... 78 more

Background of the error:

One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.

By way of instance registration, PMON is responsible for updating the listener with information about a particular instance such as load and dispatcher information. Maximum load for dedicated connections is determined by the PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE information varies according to the workload of the instance. The maximum interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Only when PMON updates the listener via SERVICE_UPDATE is the listener informed of current load. Since this can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.

When the listener believes the current number of connections has reached maximum load, it may set the state of the service handler for an instance to "blocked" and begin refusing incoming client connections with either of the following errors: ora-12519 or ora-12516

Solution

Increase the processes and sessions value to allow the listener to spawn more server process as required in your environment.

DBCA Creates a RAC Database But at the End Shows PRPP-1001 and CRS-0215

When you try create database DBCA show up PRPP-1001 and CRS-0215

1) There are separate homes for the ASM instance and the database instances.
2) The Oracle NET listener runs from a separate home than the home used for the database.
3) Even though the TNS_ADMIN for the session that launched DBCA was set and pointed to
    the proper home and directory were the Oracle NET configuration files reside (e.g., tnsnames.ora, etc.),
    this was not passed to the srvctl commands kicked off by DBCA

You can fix issue  here

Update the TNS_ADMIN environment setting for each of these database and instance resources registered in the OCR, to point to the proper directory.
Example (do this only from one of the RAC nodes, any node),
1) If the node's Oracle NET listener runs from home '/home/oracle/oracle/product/11.1/asm_1', then
2) If DBCA is launched from home '/home/oracle/oracle/product/11.1/db_1' (which will be the home for the new database), then
3) From any node and as the Oracle software owner
srvctl setenv db -d <db_name> -t TNS_ADMIN=/home/oracle/oracle/product/11.1/asm_1/network/admin
srvctl setenv inst -d <db_name> -i <instance_name_1> -t TNS_ADMIN=/home/oracle/oracle/product/11.1/asm_1/network/admin
srvctl setenv inst -d <db_name> -i <instance_name_2> -t TNS_ADMIN=/home/oracle/oracle/product/11.1/asm_1/network/admin
4) Now, the RAC database and instance should not fail to startup via srvctl:
 srvctl start db -d <db_name>

Encountered file error when copying listeners from home

When try to create new databse using dbca, you can get below error
'Encountered file error when copying listeners from home=/u01/app/oracle/prodouct/

Problem1 :

The problem does not occur

If the $TNS_ADMIN is set.
If the ORACLE_HOME and the ASM_HOME are owned by the same user.

Issue:
In this case the ORACLE_HOME & ASM_HOME were owned by different users.
From the dbca log file residing in the $ORACLE_HOME/cfgtoollogs/dbca directory

file `/home/oracle/oracle/product/11.0/asm/network/admin/listener.ora': Permission denied 

Solution:

Ensure that you have write permission to the network/admin directory under the asm home.
chmod 775 /home/oracle/oracle/product/11.0/asm/network/admin/
Rerun dbca.


Problem 2:

Issue:

Database Configuration Assistant (DBCA) fails to create database at 0% of the "Copying database files" stage.

Pop-up shows:

Encountered file error when copying listeners from home=/home/oracle/oracle/product/11.1/asm_1
                                               [OK]
After clicking on the OK button, DBCA goes back to the last page before it attempted to create the database (Database Configuration Assistant, Step 16 of 16 : Creation Options).

11g by default creates DBCA trace. The DBCA trace file ought to be under the directory $ORACLE_BASE/cfgtoollogs/dbca/<database_name>.

[Thread-240] [17:51:28:709] [NativeSystem.rununixcmd:929] NativeSystem.rununixcmd: RetString 0|scp: /home/oracle/oracle/product/11.1/db_1/network/admin/listener.ora: No such file or directory :failed
[Thread-240] [17:51:28:709] [CopyCommand.execute:79] CopyCommand.execute: native copyFile returns `0|scp: /home/oracle/oracle/product/11.1/db_1/network/admin/listener.ora: No such file or directory :failed'
[Thread-240] [17:51:28:710] [NativeResult.<init>:88] NativeResult: The String obtained is0|scp: /home/oracle/oracle/product/11.1/db_1/network/admin/listener.ora: No such file or directory :failed
[Thread-240] [17:51:28:710] [NativeResult.<init>:96] The status string is: 0

Solution:

The problem was caused by a missing soft link used to link listener.ora in the $ORACLE_HOME/network/admin directory used by the DBCA session to the $ORACLE_HOME/network/admin/listener.ora used by the node's Oracle listener.

Example, On node1
(where DBCA was launched):

[oracle@test1] /home/oracle/oracle/product/11.1/db_1/network/admin> ls -ltr 
lrwxrwxrwx 1 root root 64 Jan 12 17:37 listener.ora -> /home/oracle/oracle/product/11.1/asm_1/network/admin/listener.ora 

Example, On node2 (same $ORACLE_HOME used by DBCA on node1 but that is on node2) :

[oracle@test2] /home/oracle/oracle/product/db_1/network/admin> ls -ltr 
<no listing for a soft link>

Do not use soft links to link Oracle NET configuration files (listener.ora, etc). Instead, configure TNS_ADMIN to point the DBCA session to the proper directory where these configuration files reside:

Example:

1) If your home where you will launch DBCA is '/home/oracle/oracle/product/11.1/db_1', then
2) If the server's listener run from home '/home/oracle/oracle/product/11.1/asm_1', then
3) From the session that will launch DBCA, set its TNS_ADMIN variable to point to the home where the listener runs of:
   $ export TNS_ADMIN=/home/oracle/oracle/product/11.1/asm_1/network/admin
4) Start up DBCA and database creation should now successfuly complete.

CRS output readable format

When you run crs_stat -t command, you can see  at below format output

Name              Type           Target    State     Host
------------------------------------------------------------
ora.test.db       application    ONLINE    ONLINE    server01
ora....e1.inst    application    ONLINE    ONLINE    server01
ora....e2.inst    application    ONLINE    ONLINE    server02
ora....SM1.asm    application    ONLINE    ONLINE    server01
ora....02.lsnr    application    ONLINE    ONLINE    server01
ora.server01.gsd  application    ONLINE    ONLINE    server01
ora.server01.ons  application    ONLINE    ONLINE    server01
ora.server01.vip  application    ONLINE    ONLINE    server01
ora....SM2.asm    application    ONLINE    ONLINE    server02
ora....02.lsnr    application    ONLINE    ONLINE    server02
ora.server02.gsd  application    ONLINE    ONLINE    server02
ora.server02.ons  application    ONLINE    ONLINE    server02
ora.server02.vip  application    ONLINE    ONLINE    server02

Here is readable format output.

Run below code  in shell script

#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment
RSC_KEY=$1
export ORA_CRS_HOME=/home/oracle/oracle/product/10.1/crs
QSTAT=-u
AWK=/usr/bin/awk    # if not available use /usr/bin/awk
# Table header:echo ""
$AWK \
  'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
          printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'
# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; state=3;}
  state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

Here is output

HA Resource                                                     Target          State
-----------                                                            ------            -----
ora.test.db                                                          ONLINE     ONLINE on server01
ora.neptune.test1.inst                                        ONLINE     ONLINE on server01
ora.neptune.test2.inst                                        ONLINE     ONLINE on server02
ora.server01.ASM1.asm                                   ONLINE     ONLINE on server01
ora.server01.LISTENER_server01.lsnr           ONLINE     ONLINE on server01
ora.server01.gsd                                                ONLINE     ONLINE on server01
ora.server01.ons                                                ONLINE     ONLINE on server01
ora.server01.vip                                                 ONLINE     ONLINE on server01
ora.server02.ASM2.asm                                    ONLINE     ONLINE on server02
ora.server02.LISTENER_server02.lsnr           ONLINE     ONLINE on server02
ora.server02.gsd                                                 ONLINE     ONLINE on server02
ora.server02.ons                                                 ONLINE     ONLINE on server02
ora.server02.vip                                                 ONLINE     ONLINE on server02

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

When you do export or import into oracle database, you can see below error

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "<Dump file path>" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3 

And also you can see below error message in alert.log file

Wed May 13 10:23:27 2009

DM00 started with pid=77, OS id=21928, job <username>.SYS_IMPORT_FULL_01

WARNING:NFS file system /ora/odmp mounted with incorrect options

WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,noac/actimeo=0

Wed May 13 10:23:27 2009

Cause

The problem is due to a configuration issue trying to write the dumpfile using a directory on a NFS mount point.

Solution

Here are the mount options that need to be used for NFS volumes on Linux are:

rsize=32k, wsize=32k, hard, actimeo=0 

Along with the error there are warning messages printed in the alert log with the mount options to be used.Sometimes you may need to do the following to allow datapump to work with NFS mounted files:

Set the event in the init.ora, to disable the mount point parameter checking:

event="10298 trace name context forever, level 32"
          
Note that the event 10298 skips all mount checks.  Setting this event has to be done with care. Verify settings with your System Administrator to ensure no datafiles are corrupted

ORA-01994: GRANT failed: password file missing or disabled.

You try to grant sysdba privilege to a user. You can see below error

ORA-01994: GRANT failed: password file missing or disabled.

User doesn’t have sysdba privilege

Solutions

1.      Log  on to the server

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=10

2.      Grant Sysdba privilege to the user 

      grant sysdba to <user>

3.      Check v$pwfile_users to verify the prvilege

4.       Check owner,group  of orapw$ORACLE_SID

5.      If it is not   to oracle and  dba , then change the ownership

chown oracle:dba orapw$ORACLE_SID

ORA-27303: additional information: requested interface eth1 mixed MTU configured 9000 1500

When you try install RAC. You can see error when ASM instance started

ASM1> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 24 21:57:34 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:if MTU failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcini2
ORA-27303: additional information: requested interface eth1 mixed MTU configured 9000 1500
. Check output from ifconfig command
SQL>

It looks like 2 network devices (eth1 & eth2) are configured as cluster interconnect.

>/home/oracle/oracle/product/11.1.0/crs/bin> ./oifcfg getif
eth0 10.*.*.* global public
eth2 10.*.*.* global cluster_interconnect
eth1 10.*.*.* global cluster_interconnect

2. eth1 and eth2 are configured with different MTU sizes.
eth1 MTU=1500
eth2 MTU=9000
ACTION PLAN
===========
1. If your goal is to use both eth1 and eth2 for cluster interconnect, please set them
up with identical MTU sizes.

2. If only 1 interface is needed for cluster interconnect, please delete one of them.
$ cd <crs home>/bin
$ ./oifcfg delif -global <interface to be deleted>

Verify changes:
$ ./oifcfg getif

Connection To RMAN Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

Symptoms

Connecting to an auxiliary instance via RMAN fails with ORA-01031 errors. The same is seen when properly testing a connection via SQL*Plus to the auxiliary instance.

RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-01031: INSUFFICIENT PRIVILEGES

Changes

You may have tried to add the connecting user to the password file but that has failed:

SQL> select * from v$pwfile_users;
No rows
SQL>  grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01109: database not open

Because the auxiliary is nomounted, the addition of a user to the password file for the auxiliary is not allowed, nor does it resolve this problem.

You have verified the same problem happens in SQL*Plus as does RMAN.  For example, if in RMAN you are issuing:
 

RMAN>  connect auxiliary <user/password>@<auxiliary_db>

then for testing SQL*Plus you'd have to issue this from the operating system prompt:
 

% sqlplus "<user/password>@<auxiliary_db> as sysdba"

also shows the ORA-1031 error.

(connecting in two steps to the auxiliary is not a valid test).

Cause

There may be a mismatch in the "case" of characters between these items on the host where the auxiliary database resides:

1)  The entry in the listener.ora file for service_name for the auxiliary instance.
2)  The entry for db_name in the init<sid>.ora file for the auxiliary instance.
3)  The naming of the password file.
4)  The $ORACLE_SID for the auxiliary instance.

If all four items have matching case characters, you may find the errors connecting remotely via the password file are resolved.

Solution

Set the reference to service_name in the listener.ora, db_name in the init<sid>.ora, the password file name itself, and the $ORACLE_SID to all uppercase.

Listener example:

   (DESCRIPTION=
   (ADDRESS=...)
   (ADDRESS=...)
   (CONNECT_DATA=
   (SERVICE_NAME=SALES.US.ACME.COM)))

The init<sid>.ora file:

  db_name=SALES

The password file:

  orapwSALES.ora  or orapwSALES

The $ORACLE_SID:

  $ ORACLE_SID=SALES; export ORACLE_SID