Ora-01115, Ora-01110, Ora-15081 ORA-15186(alert.log) Error After Connecting To Database with non-oracle Unix User

If you are getting error Ora-01115, Ora-01110, Ora-15081 after you connected to a database with non-oracle user as below.

rep@mydb2:/home/rep/>sqlplus rep_odi/***

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 17:01:10 2011

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

Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options

SQL> select * from dba_indexes;
 select * from dba_indexes
 *
 ERROR at line 1:
 ORA-01115: IO error reading block from file (block # )
 ORA-01110: data file 1: '+DATA/mydb/datafile/system.272.743775337'
 ORA-15081: failed to submit an I/O operation to a disk

Further if you are seeing below lines in alert.log of database:

Mon Nov 21 17:23:23 2011
 Errors in file /u01/app/oracle/diag/rdbms/mydb/MYDB2/trace/MYDB2_ora_13518.trc:
 ORA-15186: ASMLIB error function = [asm_init], error = [18446744073709551611], mesg = [Driver not installed]
 ERROR: error ORA-15186 caught in ASM I/O path

The real cause is you are connecting with OS user which does not belong to OS dba, oper, asmadmin ... etc group.

In order to succesfully do the operation, you must change the ownership of the OS user you are using or conenct to database over tns name, like below:

rep@mydb2:/home/rep/>sqlplus rep_odi/***@REP

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 21 17:01:10 2011

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

Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options

SQL> select * from dba_indexes;

SQL>...

Thanks to brilliant guy Hakan Dömbekçi for routing me to MOS Note.

Ora-01115, Ora-01110, Ora-15081 When Connecting DB with non-dba Group Users [ID 1372571.1]

How to recover from, 64 bit 32 bit ODBC driver could not be loaded due to system error code 998

Problem

ODBC driver could not be loaded due to system error code 998

Cause

The problem arose because application, Oracle client and Windows does not match properly as 32bit or 64 bit.

I have Windows 2003 64 bit, Oracle 10.2 32 bit Client and 64 bit application.

After googleing around,

I found that there is two versions of odbc datasource administrator:

The default one, is 64 bit which we use Start menu for opening it.

In order to open the 32 bit version I used: %windir%\SysWOW64\odbcad32.exe.

With the help of 32 bit version datasource administrator, I could see my 32 bit odbc driver listed.

I extracted this rule of thumb: If your application is 32 bit, then your data source must use Oracle 32 bit client as well.

Last words, beware of 3 pieces:
1- your application (which uses DSN definition),
2- Oracle client
3-data source administrator

All pieces must have same version, in my case it is 64 bit and therefore I installed 64 bit client.

Further reading:
http://support.microsoft.com/kb/942976

Moving Data and Creating Local Index In Huge, Partitioned Tables

In order to get performance while creating a table and moving data with bulk inserts, it is a common way, first creating the table without index. Performance is gained with the help of not dealing with index manipulation for each row inserted, which means “single insert” operation.

You need to create the index after moving the data with insert /*+APPEND*/clause.

"Creating index" is the heart of the matter here... If you go with traditional approach like in small tables it will not work because of the reasons below:

-          new DML operations must wait for the index creation, which could cause space problems, application problems

-          getting ORA-01555 snaphot too old messages while creating the index

So what will I do in order not to bump into above conditions?

Answer: Apply divide and conquer approach, partitions is for this purpose... First create the index as unusable, and then issue rebuild clause to each partition. This could be done as following:

- Make the index unusable:

ALTER INDEX myindex UNUSABLE;

- Rebuild the index in each partition:

ALTER INDEX myindex REBUILD PARTITION mypartition;

- Check the status of index for each partition from:

SELECT PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
WHERE INDEX_NAME = 'MYINDEX';

Also note that if the index is associated with a constraint such as; primary/unique key constraint, you should better disable or drop the constraint, as for not getting error for DML operations take place after index is unusable.

You could get detailed information with regard to unusable state of indexes and index altering operations in the below links respectively:

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes002.htm#CIHJIDJG

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/indexes004.htm#CIHJCEAJ

A photo from Garipce, Istanbul

How to cancel alter database datafile resize command

If you erroneously give alter database resize command, such as giving 33,884,000M  instead of 3,388,400MMB. Which may cause you to end up with disk space.

You can kill the sql from another session, but also you could give true resize value as well.

Here what happens, in alert log file, from real world scenario:

Thu Mar 03 04:28:12 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 33884000M
Thu Mar 03 04:31:59 2011
Immediate Kill Session#: 600, Serial#: 53111
Immediate Kill Session: sess: 7000007726370c8  OS pid: 52691066
Thu Mar 03 04:32:15 2011
Immediate Kill Session#: 866, Serial#: 41187
Immediate Kill Session: sess: 7000007726ae1c8  OS pid: 34406652
Thu Mar 03 04:34:31 2011
NOTE: deferred map free for map id 116809
Error occured while spawning process O004; error = 601
Thu Mar 03 04:36:27 2011
Immediate Kill Session#: 1133, Serial#: 30931
Immediate Kill Session: sess: 7000007687084b0  OS pid: 63242370
Thu Mar 03 04:39:57 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388400M
...
Thu Mar 03 04:40:14 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
ORA-3297 signalled during: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3388500M
...
Thu Mar 03 04:40:31 2011
ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M
Thu Mar 03 04:42:35 2011
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_ora_65994754.trc  (incident=225358):
ORA-00240: control file enqueue held for more than 120 seconds
Incident details in: /u01/app/oracle/diag/rdbms/mydb/mydb1/incident/incdir_225358/mydb1_ora_65994754_i225358.trc
Thu Mar 03 04:42:40 2011
Dumping diagnostic data in directory=[cdmp_20111103044240], requested by (instance=1, osid=65994754), summary=[incident=225358].
Thu Mar 03 04:47:25 2011
minact-scn: useg scan erroring out with error e:12751
Thu Mar 03 04:47:25 2011
Sweep [inc][225358]: completed
Sweep [inc2][225358]: completed
Thu Mar 03 04:49:36 2011
Error occured while spawning process O007; error = 601
Thu Mar 03 04:52:01 2011
Completed: ALTER DATABASE DATAFILE '+MYDG/mydb/datafile/myfile_d_01.dbf'
RESIZE 3389500M

How to increase the speed of RMAN backups

There could be several bottlenecks which makes our backups slower.

Last month we had similar situation and after investigating the issue in the legato side, the number of allocated tape drives seemed us small. Because of that we had increased the number of tape drives devoted for the backup job from 2 to 4.

Interestingly, it does not make any sense.

After than, we increased the number of channels in RMAN script from 8 to 12 and in this way we managed to increase the utilization of tape drives. The real reason behind was the utilization of tape drives are dependent on utilization of disks for reading.

Further, in legato side, while monitoring the backup job, only 2 of allocated 4 tape drives are used. The minimum session limit for each tape drive was 8. That means 12 channels opened only utilizes 2 tape drives. After setting minimum session value per tape drive in legato side to 3, we are happy with the picture we got. 12 channels of backup work is evenly distibuted across the 4 tape drives.

As a result, our full backup job now lasts for 20 hours, which was 50 hours before the optimization.

Find Minimum size of datafiles for resize operations in TOAD

Last minute addition!

If you are not happy with the response times, you may try gathering dictionary statistics, it helped me a lot that response times decreased from minutes to seconds.

exec DBMS_STATS.GATHER_DICTIONARY_STATS;

If you had exhausted of space, and tried to find a quick way of resizing the tablespaces. You may want to use TOAD feature:

From Administer ->Tablespaces-> Select one tablespace and press F4. In the opened window click datafiles tab and double click the datafile which you want to resize. In this window there is a button "Minimum Size?", click it.

After some time of processing, it will find the minimum size of datafile it could be, it could be better than trial-error method for exact sizes.

[caption id="attachment_299" align="aligncenter" width="300" caption="Tablespace Menu Selection"][/caption]

[caption id="" align="aligncenter" width="300" caption="Datafile Definition Window"][/caption]

Spool SQL in TOAD

If you are using TOAD, and want to get the SQL in order automate tasks or simply wonder what happens behind. You may like to check this below:

In TOAD, from main menu select:

Database -> Spool SQL -> Spool SQL to Screen

[caption id="attachment_312" align="aligncenter" width="286" caption="Spool SQL Menu Selection"][/caption]

And you will see every action is spooled in the below window.

[caption id="attachment_313" align="aligncenter" width="300" caption="Spool Output Window"][/caption]

Hope this helps!

TNS Connection Failure Resolution

Many dbas has no single day in a week that not telling  their database  users about how to make TNS connection with the newly created accounts in their hands. I am providing below decision tree, to our users in our company for resolution of their TNS connection failures on their own.

Follow below decision tree for a sample TNS entry in$ORACLE_HOME/network/admin/tnsnames.ora file like below:

MYRAC=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. From cammand line issue: ping mydb.com
   
   
   
   1. If not work, WINS server client configuration problem !
   
   
   2. If works: tnsping MYRAC
   
   
   
   

i.      If works, there must not be any problemJ

ii.      If not work, from command line issue below command:

tnsping  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))

1. If not work:  Oracle client configuration/installation problem ! Or you are editing wrong tnsnames.ora file!


2. If works: Check $ORACLE_HOME/network/admin/sqlnet.ora  file. Note the NAMES.DEFAULT_DOMAIN parameter value if other than null and place it to your $ORACLE_HOME/network/admin/tnsnames.ora:

MYRAC.<names default domain>=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME= MYSERVICE)))

JDBC connect string

While trying to connect to Oracle via Oracle service name provided to you.

Then you could test the connection by:

tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))

And then used this information int the connect string as below:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICE)))

Note that, this is service name using issue, if you have chance to use SID name like INST1, old fashion connection string use will not be a problem, like below:

jdbc:oracle:thin:@mydb.com:1521:INST1

Which view for NLS Parameters

Sometimes it could be confusing, where to look at for NLS parameters.

There are 3 views for NLS parameters which could be used for achieving information about database characterset, language, territory and date parameters.

Characterset from NLS_DATABASE_PARAMETERS view, Language, territory, date from NLS_SESSION_PARAMETERS. I did not use NLS_INSTANCE_PARAMETERS view much, but may help while looking for RAC instance parameters.

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P9
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.




SQL> select * from NLS_INSTANCE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.


SQL>  select * from NLS_SESSION_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  TURKEY
NLS_CURRENCY                   TL
NLS_ISO_CURRENCY               TURKEY
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD-MM-YYYY HH24:MI:SS.FF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RRRR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              YTL
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

Add and drop disks concurrently to a diskgroup in Oracle ASM, as to gain hours by one time rebalance

Adding a disk to a disk group needs rebalancing the data across newly formed diskgroup consisting newer disks. Dropping also needs similar rebalancing across diskgroup as you guess.

If you need to add and drop disks at the same time, then this trick is what you are looking for or bumped into.

As you could understood from below real world example SQL, the dgdata diskgroup is changed by adding 13 disks from new cabinet, and dropping 13 disks from old cabinet. And it worked smothly, thus we gained at least 15 hours from one time rebalancing instead of two times rebalancing. Also it worths noting that no intervention is needed, because you need to execute one SQL instead of 2.

-- Concurrently execute add/drop disk in order gain from rebalance time.

ALTER DISKGROUP DATADG
ADD DISK
    '/dev/ORACLE/ASMDISK/DATA7' NAME DATA_0007,
    '/dev/ORACLE/ASMDISK/DATA8' NAME DATA_0008,
    '/dev/ORACLE/ASMDISK/DATA9' NAME DATA_0009,
    '/dev/ORACLE/ASMDISK/DATA10' NAME DATA_0010,
    '/dev/ORACLE/ASMDISK/DATA11' NAME DATA_0011,
    '/dev/ORACLE/ASMDISK/DATA12' NAME DATA_0012,
    '/dev/ORACLE/ASMDISK/DATA13' NAME DATA_0013,
    '/dev/ORACLE/ASMDISK/DATA14' NAME DATA_0014,
    '/dev/ORACLE/ASMDISK/DATA15' NAME DATA_0015,
    '/dev/ORACLE/ASMDISK/DATA16' NAME DATA_0016,
    '/dev/ORACLE/ASMDISK/DATA17' NAME DATA_0017,
    '/dev/ORACLE/ASMDISK/DATA18' NAME DATA_0018,
    '/dev/ORACLE/ASMDISK/DATA19' NAME DATA_0019
DROP DISK
    DATADG_0000,
    DATADG_0001,
    DATADG_0002,
    DATADG_0003,
    DATADG_0004,
    DATADG_0005,
    DATADG_0006,
    DATADG_0007,
    DATADG_0008,
    DATADG_0010,
    DATADG_0011,
    DATADG_0012
REBALANCE POWER 5;

How to drop diskgroup giving ORA-15027

Problem

While removing all the contents of a database using ASM you may encounter error from acmca or sqlplus:

ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount

Cause

The reason behind is the parameter file residing in the ASM disk:

Solution

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME                           TYPE     TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ------ ---------- ---------- ----------------------- --------------
DATA                           EXTERN    7168000    7167858                       0        7167858
FRA                            EXTERN     512000     511222                       0         511222

SQL>


SQL> column HEADER_STATUS format a20
SQL> set pages 100
SQL> col PATH format a25


SQL> r
1* SELECT name, header_status, path FROM V$ASM_DISK

NAME                           HEADER_STATUS        PATH
------------------------------ -------------------- -------------------------
DATA_0004                      MEMBER               /dev/rdisk/disk38
DATA_0005                      MEMBER               /dev/rdisk/disk39
DATA_0006                      MEMBER               /dev/rdisk/disk40
DATA_0007                      MEMBER               /dev/rdisk/disk41
DATA_0008                      MEMBER               /dev/rdisk/disk42
DATA_0009                      MEMBER               /dev/rdisk/disk43
DATA_0010                      MEMBER               /dev/rdisk/disk44
DATA_0011                      MEMBER               /dev/rdisk/disk45
DATA_0012                      MEMBER               /dev/rdisk/disk46
DATA_0000                      MEMBER               /dev/rdisk/disk60
DATA_0001                      MEMBER               /dev/rdisk/disk61
DATA_0002                      MEMBER               /dev/rdisk/disk62
DATA_0003                      MEMBER               /dev/rdisk/disk63
DATA_0013                      MEMBER               /dev/rdisk/disk64
FRA_0000                       MEMBER               /dev/rdisk/disk65

15 rows selected.






SQL>
SQL>
SQL> DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount

SQL>
SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;
DROP DISKGROUP data FORCE INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15230: diskgroup 'DATA' does not require the FORCE option


SQL> create pfile='/tmp/init.ora' from spfile;

File created.

SQL> shutdown immediate;
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup pfile='/tmp/init.ora';
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size                  2169104 bytes
Variable Size             256595696 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted


SQL>  DROP DISKGROUP data INCLUDING CONTENTS;
DROP DISKGROUP data INCLUDING CONTENTS
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted


SQL> DROP DISKGROUP data FORCE INCLUDING CONTENTS;

Diskgroup dropped.

SQL>

What is the benefit of Service Request escalation?

I do not know the exact meaning of SR escalation and have guesses like:

- Increasing the severity level of a service request as an example from lower severity level 3, to higher severity level 2.

- Making a priority handling of a service request.

Today, one of my SR is escalated and I understood that all of the above guesses missed the truth.

The truth is in below excerpt from MOS reference: How To Escalate a Service Request (SR) with Oracle Support Services [ID 199389.1]

4. What are the benefits of using the Service Request escalation process?

Use the Service Request Escalation process to ensure Oracle Management attention to your issue, and to facilitate the creation of an Action Plan to resolve the issue with your deadline clearly stated. It also allows Oracle management to effectively and promptly assign the required resources to resolve your problem.

Consider that routinely escalating non-critical issues or consistently overstating the criticality of escalated Service Requests may result in a misunderstanding of the importance or critical impact of a future escalation. Prudent use of the Service Request Escalation process enables Oracle to accurately prioritize your critical issue.

How to check PSU version number

After applying 11.2.0.2.2 PSU patch,I want to check to see the version number as 11.2.0.2.2 but, it was not shown in places:

1st the statement while connecting to database or ASM via sqlplus like:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

2nd v$instance

3rd v$version

4th impdp

So what?

In MOS I bumped into a note regarding with the PSU versions after then.
Patch Set Updates for Oracle Products [ID 854428.1] [ID 854428.1]

Patch Set Updates are referenced by their 5-place version number.
The following OPatch commands are a simple way to determine the latest PSU installed in the Oracle Home.

For Database PSUs, enter the following command:
opatch lsinventory -bugs_fixed | grep -i "DATABASE PSU"

For CRS (Cluster Ready Services) PSUs, enter the following command:
opatch lsinventory -bugs_fixed | grep -i "TRACKING BUG" | grep -i PSU

For GI (Grid Infrastructure) PSUs, enter the following command:
opatch lsinventory -bugs_fixed | grep -i "gi psu"

At last I also checked this information from below link, while looking for keyword "5-place version number".
http://forums.oracle.com/forums/thread.jspa?messageID=4250078&tstart=0

Bouncing Oracle db does not give back memory! is this correct?

I changed the memory_max_target and memory_target parameters and bounce the db. And nothing changed.

There maybe two causes: Oracle really doesn't give back the memory allocated. Or some other memory eating processes released what they kept after the restart.

Rebooting the machine helped, total memory utilized decreased. This may be because Oracle kept the memory allocated, or the filecache_max, filecache_min parameters' usage of HP-UX also decreased after reboot. Bouncing the db most probably let OS to take back the memory used by Oracle. After some time, again the OS memory utilization ate up the memory. So, Oracle is innocent, it had already given the memory blocks. OS parameters: filecache_max and filecache_min is the root cause of the problem.

Excerpt from Guy Harrison:

"We might deal with today’s pain but fail to achieve a permanent or scalable solution." matches our case as we need to define  the root cause of the problem, not just descreasing the SGA memory utilization, and sit back.

 

Oracle running on HP-UX 11.31 memory problem

Problem: Still not enough memory, although I decreased memory_max_target and memory_target together with increasing the swap disk size.

Cause: HP-UX kernel parameter tunable filecache_max parameter utilizes the memory hungrily.

Solution: Decreasing filecache_max and filecache_min parameters 5% and 3% respectively solved my problem. The rationale behind this is: "let oracle SGA to utilize and cache the datafiles".

For an example case, you could look at: http://forums11.itrc.hp.com/service/forums/questionanswer.do?admit=109447626+1298195390914+28353475&threadId=1448215

Jobs in 9i does not start one day, 10g scheduler's surprise!

Problem

I had ported the existing jobs in 9i to 10g. It was working properly for 2 or 3 months and one day the jobs did not work. I tried to reexecute them, it says it does not know the procedure in schemas. For instance, EXAMPLESCHEMA.mysampleproc('myparameter'); is not executed. So after some trials, like restarting the database but only omitting the schema name, solves the problem. I left the thoughts of why does it happen to later days.

But, because it is painful change the job definitions of hundreds of jobs, I preferred using job scheduler feature coming in 10g. Surprise is coming now: while defining the jobs in job scheduler, they did not start.

Cause:

No real cause, it can be misleading design bug.

Solution:

The only stupid reason behind was, setting the start time and next start date formula same time. In order to make it execute you must give next date apart from start time, they must not be same!

How to sort processes by RSS (memory utilization) in glance in HPUX

While investigating your Oracle processes memory utilization, it is valuable to see them in order. It is quite hard to find the below shortcut for me, which makes it valuable to note:)

From the main screen do the following:

o then 1 then arrow down to sort key & type "disk" then y

Excerpt from: http://forums13.itrc.hp.com/service/forums/questionanswer.do?admit=109447627+1296503089852+28353475&threadId=403362
Thank you Jeff!

Deleting archive logs matter, no matter whether they are backed up or not

It is sometimes useful to delete the archive logs without backing them up. In my case, I need time and disk space before taking the backup to the tape, which will be accomplished by backup team.

Also it could be done permananently, in order to make your database run in archive log mode but without consuming so much disk space, which will enable you to recover from media failures.

Excerpt from OTN discussion, thank you Arul for the below lines:

To delete all archivelog on disk no matter wether they are backed up or not ...

RMAN > delete archivelog all;

To delete all archivelog on disk no matter wether they are backed up or not and they are one day old ...

RMAN > delete archivelog all completed before 'sysdate -1';

http://forums.oracle.com/forums/thread.jspa?messageID=2033883

In order to continue full backup of archivelogs you must issue below statements:

[oracle@mydb]:/nsr/rman/log> rman target /

RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired archivelog all;

"ORA-14006: invalid partition name" in TOAD

Problem:

After issueing below SQL from the TOAD editor:

alter table myschema.mytable truncate partition p20101002;

It gives below error:
ORA-14006: invalid partition name

Cause:

TOAD's engine sometimes cannot correctly evaulate ";" character at the end.

Solution:
Remove ";" character, or execute it from sqlplus which I did for weeks before finding exact cause:)