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:)