Tom,

Didn't quite get it yet...
Is there any way to edit the spfile when the database is down? Or to create an init.ora from the spfile when the database is down?

Thanks, jan

Thanks a lot Tom, that looks easy enough :-)

jan

I followd the standerd notations and got this error. Couldn't work with spfile.

SQL> connect sys/a as sysdba
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters 
LRM-00110: syntax error at '' 


SQL> show parameter spfile;
ORA-01034: ORACLE not available 


SQL> spool off

 

Tom,

When you shutdown the database, how does oracle maintain the user session.i.e.of sysdba.

Thanks tom for your wonderful answer..

Tom

Think this site is the best....

If you are using an spfile and have the one line entry spfile=?/dbs/spfileORA9.ora in your init.ora file. In the case of a typo, could you not overwrite it in the init.ora file below the spfile entry? Thereby correcting the error, then once the database is up re-enter the invalid entry into the spfile and remove the extra line from the init.ora.

OK Tom

I was offering the following way of working around having to re-create the init.ora file starting the database, then re-creating the spfile.
As in this example, we overwrite the db_block_buffers parameter as oppossed to overwriting a corrupt entry.

For arguments sake lets say we are working in /u01/9i/dbs and the "spfileSID.ora" has db_block_buffers=200 entry in it.

mv spfileSID.ora spfileSID.foo
create initSID.ora with the following :-
spfile=/u01/9i/dbs/spfileSID.foo
db_block_buffers=10000

Startup the database, whalla 10000 block buffers.
alter sysetm set db_block_buffers=10000 scope=spfile;

Remove initSID.ora file and mv spfileSID.foo spfileSID.ora

Hi Tom,

First of all thanx to you and Kevin for a wonderful solution.

Coming to your example, wherein you set the parameter value for processes to a wrong value and then when you tried to start the DB got the error.

But assuming that I have changed a few parameter or say i have changed a parameter long ago and now i restart the DB, how will i know the error is related to which parameter.

I know that you can look the ALERT log file. But then as I said above it could have been one of the parameters set long ago or a few of them at the same day....

Does'nt the error (while starting the DB) tell you which parameter value is set wrong???

I dont have Oracle 9i DB. Therefore can'nt try out for myself.

Would appreciate your answer and thanx for the good work.

Regards,
Sagi

I found this very interesting as I had difficulty with a bad parameter in the spfile a few months ago.

Out of curiosity, even the pfile format in 9i has this new *. before each line. What's the reason for it?

Tom,

I see so many dedicated server process running in my server, for my instance oracleTSTETMCT there 6 dedicated server processes running.

is there anything wrong happening over here, please let me know

HYD280R:oracle:TSTEENWS: ps -ef|grep oracle
oracle 20167 1 0 May 08 ? 0:16 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20173 1 0 May 08 ? 0:17 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20165 1 0 May 08 ? 0:16 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20986 20975 0 00:12:28 pts/6 0:00 -sh
oracle 380 1 0 Apr 21 ? 0:01 /u01/app/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit
oracle 406 1 0 Apr 21 ? 0:01 ora_pmon_TSTEENWS
oracle 408 1 0 Apr 21 ? 0:02 ora_dbw0_TSTEENWS
oracle 410 1 0 Apr 21 ? 0:02 ora_lgwr_TSTEENWS
oracle 412 1 0 Apr 21 ? 1:44 ora_ckpt_TSTEENWS
oracle 414 1 0 Apr 21 ? 0:00 ora_smon_TSTEENWS
oracle 416 1 0 Apr 21 ? 0:00 ora_reco_TSTEENWS
oracle 432 1 0 Apr 21 ? 0:01 ora_pmon_DEVETMCT
oracle 434 1 0 Apr 21 ? 0:06 ora_dbw0_DEVETMCT
oracle 436 1 0 Apr 21 ? 0:07 ora_lgwr_DEVETMCT
oracle 438 1 0 Apr 21 ? 1:39 ora_ckpt_DEVETMCT
oracle 440 1 0 Apr 21 ? 0:02 ora_smon_DEVETMCT
oracle 442 1 0 Apr 21 ? 0:00 ora_reco_DEVETMCT
oracle 450 1 0 Apr 21 ? 0:01 ora_pmon_TSTESCLC
oracle 452 1 0 Apr 21 ? 0:01 ora_dbw0_TSTESCLC
oracle 454 1 0 Apr 21 ? 0:01 ora_lgwr_TSTESCLC
oracle 456 1 0 Apr 21 ? 1:29 ora_ckpt_TSTESCLC
oracle 458 1 0 Apr 21 ? 0:01 ora_smon_TSTESCLC
oracle 460 1 0 Apr 21 ? 0:00 ora_reco_TSTESCLC
oracle 20892 1 0 09:39:31 ? 0:01 oracleDEVETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20169 1 0 May 08 ? 0:17 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20171 1 0 May 08 ? 0:16 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 24516 1 0 Apr 27 ? 0:20 ora_dbw0_TSTETMCT
oracle 24518 1 0 Apr 27 ? 0:19 ora_lgwr_TSTETMCT
oracle 24514 1 0 Apr 27 ? 0:01 ora_pmon_TSTETMCT
oracle 20894 1 0 09:43:07 ? 0:00 oracleDEVETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 24520 1 0 Apr 27 ? 1:03 ora_ckpt_TSTETMCT
oracle 24522 1 0 Apr 27 ? 0:04 ora_smon_TSTETMCT
oracle 24524 1 0 Apr 27 ? 0:00 ora_reco_TSTETMCT
oracle 20175 1 0 May 08 ? 0:11 oracleTSTETMCT (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
oracle 20975 20973 0 00:12:04 pts/6 0:00 -sh

Thanks for your time!

Tom - is there anyway to remove a parameter from the spfile without copying to an init version, editing and re-createing?

If I issued,

alter system set undo_tablespace = undotbs2;

without specifying SCOPE, even after I bounced the instance the change persisted as seen below. Does it mean that without the scope clause, the change is affected for the current instance as well as it updated the spfile. If so, why do we need SCOPE=BOTH clause? Or it depends on which parameter we change using alter system? Thanks.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

 

Tom, please see below. Why can't I rest the parameter open_cursors to the default value? Thanks.

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Nov 8 17:39:58 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
SQL> alter system set open_cursors = 500 scope=spfile;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
SQL> alter system set open_cursors = 500;

System altered.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      %ORACLE_HOME%\DATABASE\SPFILE%
                                                 ORACLE_SID%.ORA
SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
SQL> alter system reset open_cursors scope=both sid='practice';
alter system reset open_cursors scope=both sid='practice'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE 

in single instance oracle it would be:

 
  1* alter system reset open_cursors scope=spfile sid='*'
ops$tkyte@ORA920> /
 
System altered.
 

It works for me. Wonderful contribution by Tom. He is a jolly good fellow! Cheers everybody.

I opened the PFILE in wordpad to look at it. Then being a newbie to Oracle and not following the directions I made a change to it and saved it (OOPS!!!).

Anyway, now I can't connect with SQLPlus, or even get into the Enterprise Manager in Oracle 10g.

Is there a way to replace this file with a "default" one? I am really lost and need some help fast! I think I will read the directions a little more carefully next time.

I am running the 10g suite of tools and made a change to the file because I was trying to install the repository. The file still has the parameters in it. But it looks like something was messed up since I changed it with Wordpad.

Any help or if you can point me in the right direction would be greatly appreciated

Hi Tom,

While the SPFILE does provide a neat way to manage init.ora parameters by providing the scope='BOTH' option in the alter system command, it doesn't provide help for parameters that are not sys modifiable (can't be altered via the 'alter system' command). An example is the 'compatible' parameter. Such parameters still need to be modified in an init.ora file, and the spfile must then be recreated from the modified init.ora file. Does that sound about right? Thanks.

Kashif

ops$tkyte@ORA9IR2> alter system set compatible = '9.2' ;
alter system set compatible = '9.2'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
                                                                                                            
                                                                                                            
ops$tkyte@ORA9IR2> a  scope=spfile;
  1* alter system set compatible = '9.2'  scope=spfile
ops$tkyte@ORA9IR2> /
                                                                                                            
System altered.



<b>scope=spfile, takes effect on next restart</b> 

Can i change db_cache_size and pga_aggregated_target on a fly?
Thanks

Even i did set max_sga size?
if yes then please tell me how? my life much easier.

thanks

Even i did't setup max_sga size?
if yes then please tell me how? my life much easier.

thanks

Can you tell me something about SpfileSID.ora?
Having any differents between spfile.ora and spfileSID.ora ?
Why when we starting an instance,Oracle read spfileSID.ora before spfile.ora?
Thanks and Best Regards,
LanHoang

hi Tom,
I have a problem in Oracle 10G. I have database with over 10 million total rows. when I try to select rows from diff table this gives me result after 1 hour or some time more then that. I have created one user and difine diffrent table space to that user and for temp I have created one more table space.

How do I tune my database.

One more thing I have deleted some 5 million rows from database. Now I found that the datafile size could not be reduse. I try to reorganised with reorganised tablespace from dbconsole. I am new in this. I have worked on Oracle 7 and 8 I don't have exp on 9i and 10G.

Can you help me I think you can.

Pl. reply me ASAP. you can mail me on my Mail address(gkp12@yahoo.com)

Thanks

Gautam

hi Tom,
I have a problem in Oracle 10G. I have database with over 10 million total
rows. when I try to select rows from diff table this gives me result after 1
hour or some time more then that. I have created one user and difine diffrent
table space to that user and for temp I have created one more table space.

How do I tune my database.

One more thing I have deleted some 5 million rows from database. Now I found
that the datafile size could not be reduse. I try to reorganised with
reorganised tablespace from dbconsole. I am new in this. I have worked on Oracle
7 and 8 I don't have exp on 9i and 10G.

Can you help me I think you can.

BELOW IS THE TABLE STRUCTURE
----------------------------
CUSTOMER_RESPONSE_MASTER MORE THEN 3 MILLION RECORDS
LOG_ID,
SHOP_ID,
DATERESPONSE,
FORM_ID,
EMPLOYEE_ID,
UNIQUEID

CUSTOMER_RESPONSE_DETAIL MORE THEN 10 MILLION RECORDS
LOG_ID,
QUESTION_ID,
ANS_VAL,
UNIQUEID,
ANS_NAME

select avg(dtl1.ans_val)as AVRAGE1, avg(dtl2.ans_val)as AVRAGE2
from customer_response_master mst,
customer_response_detail dtl1,
customer_response_detail dtl2
where mst.log_id=dtl1.log_id and
mst.log_id=dtl2.log_id and
dtl1.log_id=dtl2.log_id and
mst.shop_id = 1 and
mst.form_id = 2 and
to_date(mst.dateresponse) >= to_date('1-1- 2004','dd-mm-yyyy') and
to_date(mst.dateresponse) <= to_date('31-12-2004','dd-mm-yyyy') and
dtl1.question_id=4 and
dtl2.question_id=7 and
dtl1.ans_val is not null and
dtl2.ans_val is not null

We use above AVRAGE1 and AVRAGE2 here in below query.
Query 2 :
select
sum((dtl1.ans_val-AVRAGE1)*(dtl2.ans_val-AVRAGE2))/sqrt(sum(power((dtl1.ans_val-AVRAGE1),2))*sum(power((dtl2.ans_val-AVRAGE2),2)))
from
customer_response_master mst,
customer_response_detail dtl1,
customer_response_detail dtl2
where
mst.log_id=dtl1.log_id and
mst.log_id=dtl2.log_id and
dtl1.log_id=dtl2.log_id and
mst.shop_id = 1 and
mst.form_id = 2 and
to_date(mst.dateresponse) >= to_date('1-1-2004','dd-mm-yyyy') and
to_date(mst.dateresponse) <= to_date('31-12-2004','dd-mm-yyyy') and
dtl1.question_id=4 and
dtl2.question_id=7 and
dtl1.ans_val is not null and
dtl2.ans_val is not null

Thanks

Gautam

Hi Tom,
I am working Oracle10g on asianux(O/s).
I have problem in Oracle 10g. I have chenge so many Intial parameters of oracle and due to that my database could not be mount. So I have decided to drop that entire database and create new database.

When I created that database It was running fine but now this database gives me problem. while I try to connect with that database. it always give me error like database is intialised or shutdown in process and now it gives me listner could not resolve SID given.
I chack and try to start database but it we could not mount new database.

Please reply ASAP.

Gautam

Hi Tom,
I have a problem in 9.2.I can't connect with my local database. TNSListner and all the services are already started. when I try to connect it gives me message end of communication channel. so I can't connect with local. I can connect with all other database like 8.0.5 , 9.0.1 and 10g.

Gautam

Tom,

Oracle 9i documentation says this:


DB_DOMAIN Parameter
Starting with release 8.1, if the DB_DOMAIN initialization parameter is not set, then it is set to NULL by default. In prior releases of Oracle, the default setting was the following:

WORLD


A NULL setting for DB_DOMAIN may cause database connection problems in some environments. If you are upgrading from release 8.0.6 or earlier, then make sure the DB_DOMAIN initialization parameter in your initialization parameter file is set to one of the following:

WORLD
a valid domain setting for your environment

If DB_DOMAIN is not set in your current database, then set it to WORLD before you upgrade.

If DB_DOMAIN is set to a valid domain for your environment in your current database, then retain the setting in your initialization parameter file when you upgrade

-----

Currently we are on 8.1.7., DB_DOMAIN is not set. Could you tell what kind of connection problems the document was referring to? Also, should it be changed to "WORLD" before the upgrade to 9i?

Hi Tom, hope you're well.

I've got a weird spfile in 10g, this is on a w2k SP2 box.

I was missing the spfile so I created one. It has the following structure:

*.compatible='10.1.0.2.0'
*.control_files='C:\oracle\product\10.1.0\oradata\orcl\control01.ctl','C:\oracle\product\10.1.0\oradata\orcl\control02.ctl','C:\oracle\product\10.1.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.1.0\admin\orcl\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.1.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=104857600
*.sort_area_size=65536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.1.0\admin\orcl\udump'
*.undo_tablespace='UNDOTBS1'

although I have a load of non printable (I think chars at the top and bottom)

My question is that I need to add AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES to this file: Do I just add them at the bottom or do I need to add them as *.AQ_TM_PROCESSES?

Many thanks in advance,

Phil

Greetings Tom,

I have a 9.2.0.6 database that has been upgraded gradually from 8.0.5 or older .. the compatability parameter is set at 9.0 .. and my question is, is there somthing that becomes obsolete if I change compatability to 9.2 ?

cheers

Is there any way that we can know from which file (.ora) the database was started ? I am using 9i on Linux.

Hi Tom,

I used 'alter system set ... scope=spfile' to enlarge both sga_max_size and sga_target.
Before the change, they were both set to 146.800.640, which I changed to 220.200.960.
After shutdown/startup, the Total SGA was reported as 222.298.112, which is a little bit above the sga_max_size.
Furthermore, I noticed that the various SGA-parts were recalculated:
The Database Buffers were enlarged from 58.720.256 to 146.800.640, but at the same time the Variable Size diminished from 86.497.672 to 73.914.304.
Does this mean, that Oracle has to redo its 'auto-tuning' of the various SGA-parts? I would have expected, that the extra amount of SGA would be 'fairly' distributed.

Thanks Tom,
the granule size indeed explains the little difference.
Regarding the re-tuning, would it be possible to speed-up this process without losing the automatic tuning feature, f.i. by setting the shared_pool_size to a somewhat higher value?

Great!
Thanks very much, Tom, for your prompt answers.
Amazing how you can find the time for that.
I have downloaded the chapter on Memory Management, and will go reading it right now. Thanks again.

I need help Tom. I work in a government facility and we do information security for many hosts. We have an Oracle host which is using Oracle 9.2i ad has had a recourring issue when we scan theat server for vunlerabilities. we are trying to recreate the issue. When trying to purchase a 9.2i it cannot be done. We purchased a copy of 10g and are now trying to downgrade to 9.2g. What is the way to sucessfully downgrade after a 10g installation to 9.2g?

Hi Tom,

When I created a pfile from spfile the pfile has lines like the following....

*.background_dump_dest='?\admin\

I have a few doubts regarding that.

1> Why *. is there before each line.

2> Why the locations of the dumps as well as the control files have ?\ prefixed.

3> Even when i use sho parameter control_file it shows the ?


Please help me in understanding the above.

Hi Tom,

Thanks for your ever valuable help. I am facing an issue with the upgradation from 9.2.0.7 to 10.2.0.1 (W2K). My spfile is having "?" symbol embedded into it for Oracle_Hoem location. But while upgrading through DBUA I am getting the error " Control file can not be identified...." Once I prepare an init<SID>.ora from spfile and edit the ? to Oracle_home everything is working fine.

Want to know was it necessary to follow the previous step to do the upgrade?

Please help..

I understand what an spfile is, where it is and how it is used. I know I can use the the 'strings' command to view it in the OS.

My question is simple (and I think I know the answer). Do you know of any way to use 'show parameter' or similar to view the spfile (specifically parameters that have been set but have not taken effect, ie audit_trail)?

I don't know how but my spfile ended having both db_block_buffers and db_cache_size... you guessed it folks, problems.
The way I fixed it was to edit the spfile in binary mode(actually hexa) and change the first d of db_block_buffers into #, so now I had #b_block_buffers, a nice comment ora can live with.

d*a*n*g*e*r*o*u*s

and not at all necessary.

I did what you had:


SQL> startup
ORA-00824: cannot set sga_target due to existing internal settings
SQL> !strings spfileora10g.ora | grep db_
ora10g.__db_cache_size=784334848
*.db_4k_cache_size=16777216
*.db_block_buffers=1024
*.db_block_size=8192
*.db_create_file_dest='/home/ora10g/oradata/ora10g'
*.db_domain='localdomain'
*.db_name='ora10g'
 
SQL> create pfile from spfile;
File created.

SQL> exit

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options


<b>
Then edit initora10g.ora and fix it....</b>

 


[ora10g@desktop dbs]$ sysdba
 
SQL*Plus: Release 10.1.0.4.0 - Production on Fri Nov 25 10:36:22 2005
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> create spfile from pfile;
File created.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 1073741824 bytes
Fixed Size                   782648 bytes
Variable Size             263458504 bytes
Database Buffers          801112064 bytes
Redo Buffers                8388608 bytes
Database mounted.
Database opened.
SQL>
 

You're right, it may be dangerous if the person doesn't know what he's doing, and of course you're suggestion is much better.
Please consider removing what I wrote, I'd feel better knowing nobody does something foolish because of me.

Regards,
Marius

I don't use Oracle widely, mostly SQL Server, so when I screwed up my new DB install with a processes parameter that was too low for me to connect, this answer got me back up and running.
Many thanks!

I did the following
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora
SQL> 


I searched whole of my hard drive but couldnt find
a file named spfile@.ora

 

SQL> alter system set java_pool_size ='151M' scope=both 
* 
ERROR at line 1: 
ORA-02095: specified initialization parameter cannot be modified 


SQL> alter system set java_pool_size='151M' scope=spfile; 

System altered. 

SQL> alter system set java_pool_size='152M' scope=memory; 
alter system set java_pool_size='152M' scope=memory 
* 
ERROR at line 1: 
ORA-02095: specified initialization parameter cannot be modified 


SQL> show parameter sga_max_size 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
sga_max_size big integer 320308936 
SQL> alter system set sga_max_size='1024M' scope=spfile; 

System altered. 

SQL> alter system set sga_max_size='1050' scope=both; 
alter system set sga_max_size='1050' scope=both 
* 
ERROR at line 1: 
ORA-02095: specified initialization parameter cannot be modified 

Seems like there are some parameters which cant accept scope=both, I really cant get why is this so? Why do I have to bounce my DB for these parameter to be active with new values. 

 

query v$parameter, read the documentation on the parameter - each one says whether it is

session
system
deferred

settable or not.  "why", just because some (fewer and fewer with each release)


ops$tkyte%ORA10GR2> alter system set java_pool_size = 96m;

System altered.

are not changeable without a restart - and some don't make sense to change (eg: control files)  

In 10g, if the value column of "show parameter spfile" shows a spfile name, can I safely assume that the database instance was started using that spfile?

SQL> show parameter spfile

<code>SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\
                                                 DATABASE\SPFILEORCL.ORA

idle> startup force pfile=initora10gr2.ora
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1263200 bytes
Variable Size             327158176 bytes
Database Buffers          272629760 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
idle> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/ora10gr2/dbs/spfileora10
                                                 gr2.ora

On Oracle 10g/Windows, the behavior seems to be a little bit different though.

SQL> connect / as sysdba
Connected.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\
                                                 DATABASE\SPFILEORCL.ORA

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=initORCL.ora
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1290892 bytes
Variable Size             285216116 bytes
Database Buffers          318767104 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> select * From v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production

Hi Tom,

I have several question about the paramters.

1) when I run the following query:
SQL> show parameter SGA_target_size

no rows are returned, does this mean that "SGA_target_size" will use default value?

2) SQL> show parameter optimizer_index_caching

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
optimizer_index_caching              integer
0

Oracle using default value 0. It means Oracle will use the real "0" for this parameter or it will select a proper one at the run time?

3) SQL> show parameter db_cache_size

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size                        big integer
0

The same question as above. My colleague said when set db_cache_size as the default value "0", Oracle will set the proper value for this parameter at run time. Is this true?


Thank you very much for your answer!

ops$tkyte%ORA10GR2> show parameter SGA_target_size
ops$tkyte%ORA10GR2> show parameter SGA_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 512M

helloo, plz give me the suitable values for these parameters
I want to build a database with these requirements:

widows/oracle 9i R2/library database/small rows/db_file=100
process=9/high performance/10 H.disk(200G etch one)

with those info, how can I select the best values for these parameters:
db_block=?
compatible=?
minymum SGA size=?
DB_CACHE_SIZE=?
LOG_BUFFER=?
SHARED_POOL_SIZE=?
LARGE_POOL_SIZE=?
JAVA_POOL_SIZE=?
and
number of datafile/redo log file/controle file??? and their locations.
plz Justify all your answer plz plz.


thanx for all

sorry I foorget to tell u
SGA_MAX_SIZE = 500 M.
add this condition to prev Question

thankxxxxxxx

Explain briefly how you ensure the availability of your database in case of:
1-Loosing a redo log member.
2-Loosing one control file.
3-Loosing one datafile.

hello Tom

how can I enable oassword encryption in my database?

answer me q plz

thnk you

Hi Tom,
Is there any pfile/spfile parameter which always forces an index scan for indexed tables?
Thanks for your time.

Tom, apologies if this is a new question.. it is at least related..

My database (10.2.0.2) is started with an spfile. The application vendor has asked me to remove a parameter.

The only way I can see to do this is to create the pfile, remove the spfile, edit the pfile, start from pfile, create a new spfile..

is there a way to do this directly?

Thanks -

Hi Tom,

I'm adding a lot of database service names to the instance, eg. "alter system set service_names = app1.domain, app2.domain, app3.domain, app4.domain,...".

I got an ORA-0600 when the service_names string is about 200 characters.

Question: Is there a maximum limit for the length of such a parameter list?

hi,

i just want to know what is actual meanign of
alter system .....scope=both
alter system ....scope=spfile

can any body help me out ???

Hi Tom,

Thanks a ton for your support.
Could you please tell me what are the possible reason for Spfile corruption?
I would like to know whether spfile may corrupt due to parameter change.

Regards
Suresh.R

You shall use the database ORCL installed during the setup of Oracle 11g database. If you are
requested to use the Enterprise Manager, all
screen shots of the steps have to be delivered.
If SQL PLUS is requested to be used then use the SPOOL command to save the instructions
and results as follows:
To save the SQL queries and statements in SQL PLUS tool you can use the following
statement:
SQL
>
SPOOL
C:
\
Users
\
studentx
\
Desktop
\
result.txt
You can use any folder with any path you want in your local hard disk to save all the
executed statements in the active session.
To finish the spooling or saving the statements to the mentioned destination file
you have to
write:
SQL>
SPOOL off
Example:
SQL>
SPOOL
C:
\
Users
\
studentx
\
Desktop
\
result.txt
SQL> select * from emp;
SQL >
SPOOL
off
Please consider that:
a
-
If result.txt is already available then it just override the file, so be careful always
try to create
a new file.
b
-
You can choose the extension of the file result.txt as you want, but you want to
save. We recommend to go for.doc )

بكالوريوس تقانات
المعلومات
–
وظيفة
BIT
-
Unit Assignment
-
4
-
REQUESTS
1.
By reference to the database startup stages, you are requested to:

Shut down the database ORCL.

Restart the database
step by step.

List the initialization parameters in each stage of database startup.
1..1.
Use the SQL PLUS and deliver the spool file.
1..2.
Use the Enterprise Manager and deliver the screen shots.
2.
How to flush all the information in the shared pool? Write the
related SQL statements
using SQL PLUS and deliver the spool file. Explain why DBA use this action?
3.
List all the background processes in the database by writing the related SQL statement in
SQL Plus, and deliver the spool file.
4.
How many database writer proc
esses in the ORCL database you are using:
4..1.
Use the SQL PLUS and deliver the spool file.
4..2.
Use the Enterprise Manager and deliver the screen shots.
5.
List all the initialization parameters that can be modified on session level and those that
can be modified on s
ystem level too:
5..1.
Use the SQL PLUS and deliver the spool file.
5..2.
Use the Enterprise Manager and deliver the screen shots.
6.
List all the tablespaces in the ORCL database by displaying the available space, used
space, status and type. Also permanent and temporar
y files related to those tablespaces
in a separate query:
6..1.
Use the SQL PLUS and deliver the spool file.
6..2.
Use the Enterprise Manager and deliver the screen shots.
7.
Create a user named “SUCCESS_USER” and give him the required privileges to access the
database a
nd create sessions, with the following quotas on the tablespaces: Users: 20
MB, system: 5 MB. And with the authentication method: external:
7..1.
Use the SQL PLUS and deliver the spool file.
7..2.
Use the Enterprise Manager and deliver the screen shots