ORA-09817: Write to audit file failed. Linux Error: 28: No space left on device

Today while try to logging with our development box got below error message

-bash-3.00$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 12 15:19:38 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba

ERROR:

ORA-09817: Write to audit file failed.

Linux Error: 28: No space left on device

ORA-01075: you are currently logged on

SQL> --reason as mentioned above "Linux Error : 28 : No Space left on device"...

SQL> --solution: make some free space on file system.

SQL> exit

-bash-3.00$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

                      145G  138G     0 100% /

/dev/sda1              99M   13M   82M  14% /boot

none                 1010M     0 1010M   0% /dev/shm

DB console cannot connect to the database due to ORA-12516 errors

Oracle 11gr2 on win 32 bit, after upgrading from oracle 11gr1 to 11gr2 ORACLE ENTERPRISE MANAGER was not getting started and failed with ORA-12516 error.

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found

Cause: processes parameter was set on default value i.e.: 150

Solution: Increase processes parameter value to appropriate digit.

How to increase processes parameter value?

1. connect to database with SYSDBA user

2. execute the following sql statement

alter system set processes = [appropriate value] scope=spfile;

3. shutdown database

shutdown immediate;

4. startup database

startup

5. check processes parameter value

show parameter processes

Now try to restart ORACLE ENTERPRISE MANAGER SERVICES

cmd> set oracle_sid =[sid]

cmd> emctl start dbconsole

Oracle 11gr2 + ORA-12514: TNS:listener does not currently know of service requested in connect

Today i received email from one user regarding the below email.

He installed oracle 11gr2 on windows machine after that from client machine when he is trying to connect then getting following message

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

below is the output of LISTENER.ORA and LSNRCTL status/services

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = C:\app\Administrator

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-AUG-2011 15:19

:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))

STATUS of the LISTENER

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

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ

ction

Start Date                25-AUG-2011 15:12:27

Uptime                    0 days 0 hr. 6 min. 50 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:\app\Administrator\product\11.2.0\dbhome_1\network\a

dmin\listener.ora

Listener Log File         c:\app\administrator\diag\tnslsnr\test\listener

\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1

521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...

Service "test" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

Service "testXDB" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

The command completed successfully

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-AUG-2011 15:22

:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

  Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

Service "test" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:21 refused:0 state:ready

         LOCAL SERVER

Service "testXDB" has 1 instance(s).

  Instance "test", status READY, has 1 handler(s) for this service...

    Handler(s):

      "D000" established:0 refused:0 current:0 max:1022 state:ready

         DISPATCHER {machine: TEST-SERVER, pid: 5532}

         (ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=2514))

The command completed successfully

Solution

Rename the current listener.ora file with listener.ora.old

and create new listener file and paste the following contents.

NOTE: replace HOST and SERVICE_NAME as per your environment.

 # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER=

        (SID_LIST =

                     

                (SID_DESC =

                        (GLOBAL_DBNAME=test)

                        (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

                        (SID_NAME = test)

                )

                )

                )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)                                                                             )

    )

  )

Adpatch Fails While Executing afcmgr.odf. Error : The table is missing the index FND_CONCURRENT_QUEUES_N2 ORA-00054: resource busy and acquire with NOWAIT specified

Dear All,

I received one email from one reader mentioning below error message

Adpatch Fails While Executing afcmgr.odf. Error : The table is missing the index FND_CONCURRENT_QUEUES_N2 ORA-00054: resource busy and acquire with NOWAIT specified

When i checked metalink then found below note:

[ID 295992.1]

I can't paste content of metalink over here but you can also use the following workaround:

afcmgr.odf is failed because concurrent manager was running and locks on concurrent manager tables.

Workaround: stop concurrent manager when applying this patch "Patch 3262159 (11i.FND.H) fails while executing afcmgr.odf.:.

You can check the concurrent manager status with following query:

SQL> select object_name from all_objects
where object_id in (select object_id from v$locked_object)
and upper(object_name) like 'FND%';

- If any locks found, release the locks on those objects. ( Shutting down the Database should release the locks on the object. Either do "shutdown immediate or shutdown normal" )

- Manually execute the adodfcmp command to load afcmgr.odf details.
e.g. Command to be used for executing ODF (for tables) :
adodfcmp userid=applsys/apps mode=tables   
odffile=/pd01/oracle/lprdappl/fnd/11.5.0/patch/115/odf/afcmgr.odf
touser=apps/ priv_schema=system/ changedb=yes           
logfile="log_table.log"
- Check logfile log_table.log.
This would execute the afcmgr.odf successfully.

adapcctl.sh: exiting with status 150

After resolve adoafmctl.sh: exiting with status 204 when i try to start application tier services then getting below error message:

adapcctl.sh: exiting with status 150

Cause:

There is a states file called .opmndat at /oracle/apps/VIS/inst/apps//ora/10.1.3/opmn/logs/states which gets created when services are started.
Whenever services are started this file gets created or updated.
Unfortunately for some reason this file is not updated when services are started.

Solution:

1. shutdown all application services on application tier (applmgr) user

2. nevigate to $INST_TOP/ora/10.1.3/opmn/logs/states directory

3. delete this file .opmndat ( this is hidden file)

#rm .opmndat 

4. restart all application services

5. re-test issue

write me email in case of any inquires at askdbataj

adoafmctl.sh: exiting with status 204

One of our site i need to change SERVER IP address in Oracle application r12 on linux 5.

I changed IP address on both server ( we have two server 1. db server 2. application server)

after changing the server IP when we are trying to start application service, application service ended with below error message:

adoafmctl.sh: exiting with status 204

Cause: Server IP address change

Solution: 

Step:

1. logon to application server with applmgr user

2. go to $INST_TOP

3.  stop application services ($ cd $INST_TOP/admin/scripts )

4. delete following files

rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

4. execute autoconfig on application tier 

$cd $INST_TOP/admin/scripts

$. ./autoconfig {hit enter}

5. start all application services

6. re-test the issue

write me email in case of any inquries @ askdbataj

Config: "Java.Lang.Exception: Exception In Sending Request :: Null"

dbconsole configuration ended with the following error


and while opening oracle enterpirse manager console
java.lang.Exception: Exception in sending Request :: null


in the /db/app/oracle/Db_1/spiora.sterling.local_orcl/sysman/log/emoms.trc

2011-07-19 14:15:06,030 [EMUI_14_15_06_/console/database/instance/sitemap] ERROR perf.sitemap initializeGeneral.400 - Failed to initialize object: ORA-20206:
ORA-06512: at "SYSMAN.EMD_DATABASE", line 1165
ORA-01403: no data found
ORA-06512: at line 1


solution:
+ Stop the dbconsole
+ Relink the agent component manually using this procedure :

1. Make a copy of $ORACLE_HOME/sysman/lib/env_sysman.mk file.

2. Edit the script $ORACLE_HOME/sysman/lib/env_sysman.mk :

- search for the section having "MK_EMDW_LIBNMEMSO_SHLIB"

- replace the line

$(LIBNMEM) $(LIBNMER) \

with

$(LIBNMEM) $(LIBNMER) $(LIBNMO) \

3. Perform the following relink:


cd $ORACLE_HOME/sysman/lib
make -f ins_sysman.mk agent
This will not affect the other database components as only the agent is being relinked.

How to resize Redolog File Size

Today I resized one of our production database redolog size from 10m to 25m.

I want to share with all.

Procedure (Tested on PROD)

1. Check the logfile member , group and current size

SQL> column member format a50

SQL> select a.group#,a.member,b.bytes/1024/1024 "size"

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 10 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 10 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 10 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 10 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 10

2. Check the logfile status of the existing redolog file

SQL> select group#,status from v$log; GROUP# STATUS
--------- ----------------
1 INACTIVE

2 CURRENT

3 INACTIVE

4 INACTIVE

5 INACTIVE

3. Switch the logfile upto last group

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

Again check the logfile status

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 INACTIVE

2 ACTIVE

3 ACTIVE

4 ACTIVE

5 CURRENT

4. Issue the below statement to make the Inactive status of active status and avoid the 'ORA-01624'.

SQL> alter system checkpoint global;

5. Once logfile status show "INACTIVE" then issue the following statement.

SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.

6. At least minimum 2 group must be exist in the database at a time Or if we trying to drop group 4 then we receive the following error.

SQL> alter database drop logfile group 4;alter database drop logfile group 4

*

ERROR at line 1:ORA-01567: dropping log 4 would leave less than 2 log files for instance orcl (thread 1)

ORA-00312: online log 4 thread 1: '/db/app/oracle/Db_1/oradata/orcl/redo04.log'

To avoid above error please add the group first then drop group 4,5 respectivily.

SQL> alter database add logfile group 1 2 ('/db/app/oracle/Db_1/oradata/orcl/redo01.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 2 2 ('/db/app/oracle/Db_1/oradata/orcl/redo02.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 3 2 ('/db/app/oracle/Db_1/oradata/orcl/redo03.log') size 25m reuse;
Database altered.

Note drop the group 4,5 and add the group 4,5 with above statement.

Check the group status and size...

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

4 UNUSED

5 UNUSED

SQL> select a.group#,a.member,b.bytes/1024/1024 "size"

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size

--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 25 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 25 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 25 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 25 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 25

You can receive the following error message during the above procedure ...

1. ORA-01624

To avoid this error issue the "alter system checkpoint global;"

2. ORA-01567

To avoid this error we need to keep at least two redolog group in the database.

3. ORA-01623

To avoid this error issue the "alter system switch logfile;"

I use the below references:

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml

Control Files Problem: Ora-202, Ora-27037, Vms Error 2, Ora-205 During Startup

If your getting "Control Files Problem: Ora-202, Ora-27037, Vms Error 2, Ora-205 During Startup" error during startup the database.

Error Description from alert.log file

ORA-00202: control file: '/db/app/oracle/Db_1/oradata/orcl/control01.ctl*.core_dump_dest='ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directory

Reason: Check metalink NOTE:300064.1

Solution: "Shutdown" the database and open new sqlplus session and startup.

ORA-04030/ORA-12500 In 32 Bit Windows Environment

One of our production db we are getting frequently the following error and our end user not able to connect database server.

ORA-04030: out of process memory when trying to allocate 2097196 bytes (joxp heap,f:OldSpace)

When Search on metalink I found the following note

Doc ID:
Note:373602.1

I didn't follow the above metalink note suggest solution. but for the workaround I bounce the db and increase the Processes parameter. after that error is gone.

EXP-00056 + ORA-00932

The following error message i am getting during export operation in 10gr1 on windows platform after apply the cpu october 2008 patch.

EXP-00056: ORACLE error 932 encountered

ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

When i Search on metalink i found below metalink note: 339938.1

The above problem can be fixed to run the following SQL script as SYSDBA user.

SQL> connect / as sysdba

SQL> @?/rdbms/admin/catmetx.sql

SQL> @?/rdbms/admin/utlrp.sql

ORA-01422 + ORA-06512 + "SYSMAN.MGMT_TIME_SYNC"

Platform: Windows2003

During Patchset applying process from 10gr2(10.2.0.1.0) to 10gr2(10.2.0.4.0) "catupgrd.sql" ended with below error.

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 193

ORA-06512: at line 2

All oracle component was successfully upgraded except OEM becuase of above error.

Clause: After searching on metalink and google didn't find proper reason why it is happened.

Solution: Re-Run catupgrd.sql script

ORA-01991: invalid password file

Oracle Version: 10gr1

OS: Windows 2003 Server

In development db i was getting below error message during database startup in mount stage.

SQL> startup

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

ORA-01991: invalid password file

'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDspipr.ORA'

above error occured after i refreshed development db from production db.

Clause: I was restore production db hot backup (user mananged) to development db and re-create oracle services and forget to create password file. so everything is new but the password file is old one. that is why i was getting ABOVE error during startup database.

Solution: Re-create the password file.

1. Stop oracle services

D:\oracle\product\10.1.0\Db_1\BIN>net stop oracleserviceSPIPR

The OracleServiceSPIPR service is stopping.......

The OracleServiceSPIPR service was stopped successfully.

2. Re-create password file through ORAPWD utility

D:\oracle\product\10.1.0\Db_1\BIN>orapwd file=D:\oracle\product\10.1.0\Db_1\database\pwdSPIPR.ORA password=oracle entries=5 force=Y

NOTE: FORCE=Y will re-create password file and replace any existing passwordfile.

3. start oracle database services

D:\oracle\product\10.1.0\Db_1\BIN>net stART oracleserviceSPIPR

The OracleServiceSPIPR service is starting......

The OracleServiceSPIPR service was started successfully.

D:\oracle\product\10.1.0\Db_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 18 19:11:19 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
[output cut]

Database mounted.

Database opened.

Oracle 9i Can't allocate log, archival required

When your getting below error message means you enable the archivelog mode for Oracle 9i database but still didn't "start" archive.

For example:

In oracle 9i we need to enable archivelog mode as well as we need to start.

1. LOG_ARCHIVE_START parameter we need to also set for start archivelog mode.

see below:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Disabled

Archive destination C:\Ora9ihome\RDBMS

Oldest online log sequence 4

Next log sequence to archive 4

Current log sequence 6

In above Database is archive log mode but archiving is not start means following parameter is not TRUE.

SQL> show parameter log_archive_start
NAME TYPE VALUE

------------------------------------ ----------- -----------log_archive_start boolean FALSE

LOG_ARCHIVE_START is static parameter so we need to restart Oracle database, ( startup followed by shutdown)

SQL> alter system set log_archive_start=TRUE scope=spfile;
System altered.

SQL> shutdown immediate;

SQL> startup

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Ora9ihome\RDBMS
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

ORA-07445 [SIGBUS] [Invalid address alignment]

One of my friend getting below error message with his database

In alertlog file

ORA-07445: exception encountered: core dump

[0000000101849ECC] [SIGBUS] [Invalid address alignment]

[0x7AF07180C000073] [] []

ORA-07445: exception encountered: core dump

[0000000101849ECC] [SIGBUS] [Invalid address alignment]

[0x7AF07180C000073] [] []

It is due to lack of SWAP space. Increase swap space will solve the issue.

for more info: Metalink Note: 268451.1

ORA-12516

Last couple of days i faced strange problem with our development database.

We are getting following error when trying to connect to database with TNS_ENTRY

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

When i check ALERTSID.LOG file i am getting following entry

Mon Jun 16 09:36:01 2008

Process q000 started up but failed with error = 20

Mon Jun 16 09:57:55 2008

Process q000 started up but failed with error = 20

Mon Jun 16 10:02:01 2008

Timed out trying to start process J000.

Mon Jun 16 10:02:02 2008

kkjcre1p: unable to spawn jobq slave process

Mon Jun 16 10:02:02 2008

Errors in file d:\oracle\product\10.1.0\admin\bdump\oramfe_cjq0_3328.trc:

Timed out trying to start process J000.

according error message i search on metalink and found to increase

job_queue_processes parameter from default value 10 to 20 but after that the problem is not solved.

after that i checked without TNS_ENTRY and found below error message

SQL> conn scott/tiger

ERROR:ORA-00020: maximum number of processes (%s) exceeded

then finally I increase PROCESSES parameter & change Listener Registration from DYNAMIC to STATIC then after problem solved.

How to change listener registration from DYNAMIC to STATIC see below link

http://dbataj.blogspot.com/2007/02/ora-12514.html

SQL> alter system set processes = 400 scope=spfile;

ORA-03113

Today when I creating tablespace in our Development database that time I getting below error message

SQL> create tablespace TEST 2 datafile 'c:\ora10ghome\oradata\orcl10g\test01.dbf' size 9g;

datafile 'c:\ora10ghome\oradata\orcl10g\test01.dbf' size 9g *

ERROR at line 2:

ORA-03113: end-of-file on communication channel

Check what Oracle Documentation say about this error message

ORA-03113: end-of-file on communication channel

Cause: The connection between Client and Server process was broken.

Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.

According above action: first we need to check network problem?

But in my case there was not network related problem.

Second we need to check alert[sid].log file for more information

in my case i found below entries in alertsid.log file

create tablespace TESTdatafile 'c:\ora10ghome\oradata\orcl10g\test01.dbf' size 9g

Wed May 14 11:01:36 2008

Starting control autobackup

Wed May 14 11:01:39 2008

Errors in file c:\ora10ghome\admin\orcl10g\udump\orcl10g_ora_1360.trc:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x77C47B79] [] [] [] []

So the actuall reason for ORA-03113 error is ORA-07445 error and as we know ORA-07445 / ORA-00600 is database bug.

And whenever we encounter any bug then please visit METALINK site.

1. Login to http://metalink.oracle.com/ site for this you need VALID CSI (customer support identifier) number.

2. Use ORA-00600/07445 lookup tools click here

3. Also check metalink note : Note:211909.1 for ORA-07445

NOTE: ORA-03113 mean BUG most of time.

so whenever your getting this error first check alertlog and use metalink for proper solution.

Client IP Address From Server.

How to find Client IP address from Oracle Server?

We can use PLSQL package UTL_INADDR.

There is two procedure 1. GET_HOST_NAME or 2. GET_HOST_ADDRESS for find out local or remote HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)

from v$session

where type = 'USER' and username is not null

order by sid;

If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

Modified code

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;

ORA-01996 & ORA-01994

ORA-01996 & ORA-01994

This error occurred when password file “entries” value is reached to maximum size.

First understand what “ENTRIES” parameter is.
Entries parameter is use for specify numeric value multiple of four.
We can assign any value but it is always use multiple of four.
Like 4,8,12 so on

Password files use for SYSDBA password. And ENTRIES clause use for HOW many database users can connect with SYSDBA privilege or how many times we can grant SYSDBA privilege to any database user.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDorcl.ORA' is full

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=shared is set.

Solution:
1. Recreate PASSWORD FILE with MAXIMUM ENTRIES=N VALUE.
2. Restart Oracle Service After recreate PWD file.

ORA-02449

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


Whenever get ORA-02449 error during drop tablespace then just use CASCADE CONSTRAINTS cluase with DROP TABLESPACE statement.

SQL> drop tablespace users including contents and datafiles cascade constraints;


Tablespace dropped.