Re: How to reduce memory consumption?
Date: Sun, 23 Mar 2008 07:12:05 -0700 (PDT)
Message-ID: <dea82d40-65ab-4b30-9710-40d6b3073b66@e6g2000prf.googlegroups.com>
On Mar 22, 10:58 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
> On Mar 22, 10:52 pm, gnewsgroup <gnewsgr..._at_gmail.com> wrote:
> > On Mar 21, 8:26 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > If the above commands show values, and you are using a spfile, you can
> > > adjust the parameters like this:
> > > ALTER SYSTEM SET MEMORY_TARGET=250M SCOPE=SPFILE;
> > > ALTER SYSTEM SET MEMORY_MAX_TARGET=300M SCOPE=SPFILE;
>
> > > Note in the above that SCOPE is set to SPFILE, so you will need to
> > > bounce the database. If you do not specify the SCOPE, you will see
> > > the following error when attempting to modify MEMORY_MAX_TARGET:
> > > ORA-02095: specified initialization parameter cannot be modified
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
>
> > Hi, Charles,
>
> > Thank you. I tried the 2 new commands you offered and they gave me a
> > big problem. I issued those two commands and then shutdown the
> > instance and tried to restart it as follows:
>
> > SQL> startup
> > ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be
> > at least 252M
> > SQL>
>
> > See the problem? How do I alter the MEMORY_TARGET value again now
> > that no Oracle instance is running? Thank you.
>
> OK, I tried this:
>
> SQL> startup pfile=D:\myoracle11g_home\admin\oracle11g\pfile\init.ora.
> 2212008193447
>
That's great that you found a work-around for the problem. I should have mentioned this to you - always keep an updated copy of the pfile (init.ora) handy. Name the pfile as initSID.ora (where SID is the database name) and place it in the same folder (directory) as the spfile. If you make a bad parameter choice when updating the spfile, all that you need to do is rename the spfile, and on the next startup command, Oracle will automatically use the pfile. You can then create a new spfile from the pfile with a simple command. The web interface in Oracle 10.2 makes it all too easy to select a bad parameter value that will prevent the database from opening, so I had a bit of practice with this approach.
So, what can you do if you do not have an up to date pfile? Open the
spfile using Wordpad, but do not make any changes using Wordpad.
Create a new file in Notepad listing each parameter that is found in
the spfile, on a separate line in the Notepad document, for example:
compatible='11.1.0.0.0'
control_files=("C:\Oracle\oradata\VMTEST\vmtest\control01.ctl", "D:
\Oracle\oradata\VMTEST\vmtest\control02.ctl")
db_block_size=8192
db_domain=''
...
There may be a couple stray characters here and there that need to be
removed. You can then save the file in the location expected by
Oracle, with the expected init.ora file name, and Oracle will open the
database using that file. If you have to use this method to recover
the parameters, you might encounter errors such as this when Oracle
tries to mount the database:
ORA-01078: failure in processing system parameters LRM-00101: unknown parameter name 'oracle_base' ---------------------------------------------------Just comment out the parameter identified by Oracle and try again.
For a fun experiment, I put my 11g test database into the same
condition as your database:
SQL>SHUTDOWN IMMEDIATE;
SQL>EXIT
C:\>SET ORACLE_SID=VMTEST
C:\>SQLPLUS /NOLOG
SQL>CONNECT / AS SYSDBA
SQL>STARTUP NOMOUNT;
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET=300M SCOPE=SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET=250M SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>STARTUP NOMOUNT;
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be
at least 800M
After renaming the SPFILEVMTEST.ORA spfile found in: C:\Oracle\Product\11.1.0\db_1\database
SQL>STARTUP
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT
\11.1.0\DB_1\DATABASE\INITVMTEST.ORA'
That's a nice improvement over Oracle 10.2, it even tells me where the
expected init.ora file should be located and the expected name of the
file. So, I create a new pfile by manually extracting the values from
the spfile as I have no backup pfile (just for this experiment).
SQL>STARTUP
ORA-01078: failure in processing system parameters
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account, see
alert log for more information
Checking the new pfile, I see that the values still specified for SGA_TARGET and PGA_AGGREGATE_TARGET are too large for the value specified for MEMORY_MAX_TARGET, so I comment out those values by placing # in front of the parameters.
SQL>STARTUP
ORA-01078: failure in processing system parameters
ORA-00600: internal error code, arguments: [kmgsb_dependency_2],
[0x8822174], [0
], [], [], [], [], []
Well, that is a rather unhelpful error. Let's see if HPUXRAC's comment: "IMHO, running with an sga and a pga limited to a toal size of 250M is going to lead to problems 'real soon now'.", is correct. Let's comment out the MEMORY_MAX value, but leave MEMORY_MAX_TARGET set to 300M.
SQL>STARTUP Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes Variable Size 255854948 bytes Database Buffers 50331648 bytes Redo Buffers 6340608 bytes SQL>SHOW PARAMETER MEMORY_MAX_TARGET NAME TYPE VALUE -------------------- ----------- -----
memory_max_target big integer 300M
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE -------------------- ----------- ----- memory_target big integer 0
Now that the Oracle database is mounted, I can create a new spfile
from the current pfile using the following command:
SQL>CREATE SPFILE FROM PFILE;
File created.
To continue the experiment:
SQL> ALTER SYSTEM SET MEMORY_TARGET=250M;
System altered.
SQL>SHOW PARAMETER MEMORY_TARGET
NAME TYPE VALUE -------------------- ----------- ----- memory_target big integer 250M
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes Variable Size 255854948 bytes Database Buffers 50331648 bytes Redo Buffers 6340608 bytes SQL>SHOW PARAMETER MEMORY_TARGET NAME TYPE VALUE -------------------- ----------- ----- memory_target big integer 0
Because I had previously started the database using the pfile, the parameter change did not survive the bouncing of the database. If I had wanted it to survive, that would have been one of the advantages of using the spfile rather than the pfile. SQL> ALTER SYSTEM SET MEMORY_TARGET=250M; System altered.
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes Variable Size 226494820 bytes Database Buffers 79691776 bytes Redo Buffers 6340608 bytes SQL>SHOW PARAMETER MEMORY_TARGET NAME TYPE VALUE -------------------- ----------- ----- memory_target big integer 252M
So, it looks like HPUXRAC's comment is correct that 250M causes problems, but 252M will work for test databases, or even very small production databases.
It is important to experiment when trying to learn about Oracle.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Mar 23 2008 - 09:12:05 CDT