Solved

Oracle 9i Database creation issue: Control file wrong block size

Posted on 2003-11-04
12
879 Views
Last Modified: 2007-12-19
Help clarify something for me please, I haven't done an install since 7.x.  Using dbca, I attempted to create a database, where the initial block size (db_block_size) was set to 8k.  When DBCA attempted to create the database, it threw an error stating that the control files needed to be 4k (the default).  I was not able to get DBCA to create the database until i reset the db_block_size parameter back to 4k.  

Is this a known problem with dbca, or a misunderstanding on my part?  

I'm also a bit confused by the pfile.  When I let Oracle create a database, the only initialization file I could find appears to be a compiled form of the init<sid>.ora files I'm familiar with.  I was able to see some typical text parameters in the file, but it is a binary file (not a text file).

Is there a way that one creates a pfile from an init<sid>.ora file?

0
Comment
Question by:gizmola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 25 total points
ID: 9684044
you can create pfile from spfile in the sqlplus :

SQL> CREATE PFILE[='PFILE_NAME'] FROM SPFILE[='SPFILE_NAME'];
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9684098
I have never heard such a problem with dbca though, maybe you can post the exact Oracle error number and message?
0
 
LVL 2

Expert Comment

by:Kong
ID: 9684124
You've probably partially completed an install then resumed it trying to change the db_block_size...

Once the database is created (controlfiles really) you can't change db_block_size.

G'day Seazodiac, seen you around a lot recently :-)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:seazodiac
ID: 9684140
Likewise. Nice to hang out here with you guys...cool...
0
 
LVL 4

Author Comment

by:gizmola
ID: 9684739
Seazodiac,
  I didn't record the error numbers unfortunately.

In regards to creating the pfile and spfile, is an spfile a "source" pfile?  This is something different from an sfile?  These are all 8.x enhancements I gather?

Kong that is possible, I suppose.  What I did was create a database and then dropped it. Is it possible that Oracle would re-use the control files that existed for the dropped database because I did not delete them from the file system?  I did use the same SID name each time.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9684768
You do not need to use DBCA when you install Oracle. If this is a fresh installation  then the best way is to deinstall everything and to begin the installation from scratch. If you would like to create additional DB then delete the created (or failed to be created DB if any) and begin from the very begining. Do not try to edit SPFILE. It seems yoy tried to edit it - there are visible characters. Use
ALTER SYSTEM SET par_name=value SCOPE=BOTH
to set parameters.
0
 
LVL 2

Assisted Solution

by:Kong
Kong earned 50 total points
ID: 9686062
gizmola,

To create your 8K database do the following:

- Find the spfile, init.ora, control files and remove them (ie search for *<sid>*.*)
- Delete the following folder: %ORACLE_HOME%\oradata\<sid>

Run DBCA & choose pfile rather than the new 9i spfile - which is a binary file. However, it is easy to revert between the two as per seazodiac's instructions.

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9686232
spfile and pfile are totally different:

spfile is a oracle8i new feature, enhancement, and it's a binary file. It helps oracle8i server to keep a consistent repository of all parameters and you can store multiple instance parameter without mixing them up.

pfile is a conventional method of configuring SGA and database parameters, it's instance specific and a ASCII file.

0
 
LVL 2

Assisted Solution

by:racher
racher earned 25 total points
ID: 9686531
For certain features of 9i you must use the SPFILE.
For example if you want to use Dataguard (it was just called Oracle Standby) you have to use SPFILEs.

"A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine where the Oracle database server executes. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent. It also provides a basis for self tuning by the Oracle database server."
0
 
LVL 4

Author Comment

by:gizmola
ID: 9690176
Ok folks, I'm gonna close this out and award points.  Thanks to everyone who weighed in.  It's a good bit clearer for me now.
0
 
LVL 4

Author Comment

by:gizmola
ID: 9690250
Also, I felt Kong provided me the best answer to my problem, although I guess I checked the wrong reply when I awarded points.  I believe the problem is a combination of

-Having the default init.ora in the /dbs directory (this was a linux install btw).
-Not deleting the control files on the file system prior to trying to use dbca again.

I will be following Kong's advice and doing a fresh install later today.  

I also appreciate the clarification on the pfile and spfile.  I was somehow confused in thinking there was a 3rd type of file, but I see now that there is only 2... the pfile which is the standard init<sid>.ora file (text format) and the spfile, which is an added convenience which allows you to write out the current database parameters to a persistent file that will be read on subsequent startups.  I can see how this is a nice feature as oppossed to the pre 8.x days when you had to remember to hand edit the init<sid>.ora file whenever you made a change.

If my understanding is correct, when the instance loads it will

1.Read the pfile.
2.Read the spfile.
0
 
LVL 2

Expert Comment

by:Kong
ID: 9690662
Glad to have helped.

If you have problems with the fresh install today, update this thread.

For clarification the 9i Startup procedure is:

If you issue "startup", it will read the following files (in the listed order) from $ORACLE_HOME/dbs:

- spfile<sid>.ora
- spfile.ora
- init<sid>.ora

The first valid file it finds will be used. The command "show parameter spfile" will identify which was used.

Executing: startup pfile=..... will obviously direct it to use the pfile specified.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join a table with user_tab_columns in oracle 3 82
join actual table rows based on the column 25 44
Error in creating a view. 8 52
Automate an Oracle update in Excel 7 70
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question