ORA-27054 on NFS file system on SUSE 10 SP2 for archives to be create directly on standby server.

On SUSE 10 SP 2 which is the supported version to be used with LOCAL,SAN,NAS devices and database 10gR2 although you create nfs filesystems archiver process reports the following error :

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

This is happening because on Oracle 10gR2 Oracle checks the options with which a NFS mount is mounted on the filesystem.
Correct option for SUSE datafiles,archives and backup are :
cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600

If you mount your NFS filesystems and you got on this error this is due to bug 5146667

If available apply patch. Else use the following work around:

1) set the Event 10298 in the init file event="10298 trace name context forever, level 32"

If you are using the spfile then :


SQL> alter system set event='10298 trace name context forever, level 32'scope= spfile sid='*';

Once you set the above parameter bounce the instance to effect parameter.

Check as follows

SQL> select name, value from v$parameter where name = 'event';
NAME VALUE
---------- ------------------------------------------------------------
Event 10298 trace name context forever, level 32

Then try by switching logfile again.

sid='*' is for RAC. For single instance do not use it.

[Oracle Net Service]WARNING: inbound connection timed out (ORA-3136)

This problem can occur on any platform, When you use Oracle Net services.

Because; The Oracle Net 10G parameter "SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 in 10.1.
To address Denial of Service (DOS) issues, the parameter were set to have a default of 60 (seconds) in 10.2

The following may be seen in the alert log: WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause:
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution:
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT

After setup this parameter in listener reload the listener by issuing command reload on o/s level.Below is the example

$lsnrctl reload

Note:Further tuning of these parameters may be needed is the problem persists.

IMPORT ZIP DUMP WITHOUT UNZIP

Script to import zip dump

Here I'm creating script on o/s level

$ vi imp_compress_dump.sh

#/bin/bash

# Example Script to Import an Object from a Compressed Export File
# without compressing it, by using FIFO (named_pipes)
# Use when uncompressing an export is not possible
# due to space and/or time constraints

# Declare the name of the named pipe

NAMED_PIPE=mypipe.pipe;

# Make the named pipe (FIFO)

mknod $NAMED_PIPE p;

# Use zcat to read compressed file and direct the standard out to the named pipe created earlier

zcat expfull_test_16-nov-09_test_export.dmp.gz > $NAMED_PIPE &

# A sample imp using the named pipe

imp file=$NAMED_PIPE userid=test/test commit=y compile=n buffer=2000000000;

# Remove the named pipe

rm $NAMED_PIPE;

# Exit the script

exit;

:wq (save and exit)

--------example------------

$ sqlplus "/ as sysdba"

SQL> create user test identified by test;

User created.

SQL> grant connect, dba to test;

Grant succeeded.

SQL> connect test/test
Connected.
SQL> create table test_export (id number not null);

Table created.

SQL> insert into test_export values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> ! exp userid=test/test file=expfull_test_16-nov-09_test_export.dmp log=exfutest_16-nov-09.log buffer=2000000000 statistics=none

About to export specified tables via Conventional Path ...
. . exporting table TEST_EXPORT 1 rows
exported
Export terminated successfully without warnings.

SQL> ! gzip expfull_test_16-nov-09_test_export.dmp

SQL> ! ls -ltr *.gz
-rw-r--r-- 1 oracle oinstall 459 Nov 16 09:22 expfull_test_16-nov-09_test_export.dmp.gz

SQL> exit

$ ./imp_compress_dump.sh

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing TEST's objects into TEST
. . importing table "TEST_EXPORT" 1 rows
imported
Import terminated successfully without warnings.

$ sqlplus test/test


SQL> select * from test_export;

ID
----------
1

Difference between DBMS & RDBMS

DBMS (Database Management System):

1)In DBMS no relationship concept.
2)It supports single user only.
3)It treats data as files internally.
4)It supports 3 rule of EF CODD out of 12 rules.
5)It requires low software and hardware requirements.
6)FOXPRO,IMS are example.

RDBMS (Relational Database Management System):

1)It is used to establish the relationship concept between two database objects.i.e,tables etc.
2)It supports multiple users.
3)It treats data as tables internally.
4)It supports minimum of 6 rules of EF CODD.
5)It requires high software and hardware.
6)SQL-server,Oracle are examples.

12 RULES OF EF CODD:

If DB support >=6 then the DB is known as RDBMS rule.

1)Information rule:DB is should contain in the form of table with R&C for storing.
2)Rule of Guarantee access:For ex:if emp,dept if i want to access emp it should give the data from emp not from other table.
3)Systematic treatment of Null value:Null value:Absence of info.
4)Comprehensive Sub language:
5)View update rule:
ex: emp emp1
10 columns 3 columns
6)Insert and Update rule:
DMS command insert,update,delete.
7)Physical data independent:
HD1 HD2 HD3
Access the data from any HD
8)Logical data independent:
C D E F
Access the data from others.
9)Data description rule:
See the structure of table
>desc emp;
10)Data distribution rule:
DB ADMIN
emp,dept LAN
Distribute the table which can access from db.
11)Integrity rule:
Constraints
12)No subversion rule:
New version should support old version.

How to enable listener logging and tracing

Enable listener logging and tracing helps you to identify the network problems and troubleshooting connection problems in oracle.It also help to identify when a client is connected to oracle database.

Enabling Logging and Tracing in listener.ora:

1)Putting entry in listener.ora
go to location for ex:$cd $ORACLE_HOME/network/admin
vi listener.ora
2)Put an entry of LOGGING_LISTENER for logging and TRACE_LEVEL_LISTENER for tracing.

TRACE_FILE_LISTENER=listenertrace.trc (The destination file for the trace file)
TRACE_DIRECTORY_LISTENER=/home/oracle (The destination directory for the trace file)
LOGGING_LISTENER=on
TRACE_LEVEL_LISTENER=support

TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.

i))off (equivalent to 0) provides no tracing.

ii))user (equivalent to 4) traces to identify user-induced error conditions.

iii)admin (equivalent to 6) traces to identify installation-specific problems.

iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.

c)Reload The Listener:
exp:$lsnrctl reload

TROUBLESHOOTING:

Format of the Listener Log Audit Trail:
---------------------------------------------
The audit trail formats text into the following fields:

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code


Properties of the audit trail are as follows:

-Each field is delimited by an asterisk (*).
-Protocol address information and service name or SID information appear only when a connection is attempted.
-A successful connection or command returns a code of zero.
-A failure produces a code that maps to an error message.

With the return code you can be able to see which type of error and when it occurs.

Audit Trail information can be used to view trends and user activity by first storing it in a table and then collating it into a report format.

Using trcasst Assistant to examine trace files

With trcasst examine the trace file, for ex:

trcasst trace_file_name_here.
For example to see statistics we can use,

trcasst -s /home/oracle/mytracefile.trc