Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuilding database to change block size - PROBLEM
Okay, I rm'ed all the files and issued the create database command. It
creates the control files, redo logs and system datafile.
The create database command then bombs out as follows:
Connected.
ORACLE instance started.
Total System Global Area 72679408 bytes Fixed Size 69616 bytes Variable Size 71700480 bytes Database Buffers 819200 bytes Redo Buffers 90112 bytesCREATE DATABASE jay804
The command I'm using is:
CREATE DATABASE jay804
LOGFILE
GROUP 1 ('/dataprod/jay804/jay804_redo1.dbf' ) SIZE 500K, GROUP 2 ('/dataprod/jay804/jay804_redo2.dbf' ) SIZE 500K, GROUP 3 ('/dataprod/jay804/jay804_redo3.dbf' ) SIZE 500KDATAFILE '/dataprod/jay804/system_jay804.dbf' SIZE 193404K maxdatafiles 300
I've also tried it after deleting the $ORACLE_HOME/dbs/lkJAY804 file. And I've tried it both with the new 16k block size and the original 4k block size.
If I try issuing an ALTER DATABASE OPEN; command I get the error:
when attempting to open the database:
01531, 00000, "a database already open by the instance"
// *Cause: During ALTER DATABASE, an attempt was made to open // a database on an instance for which there is already // an open database. // *Action: If you wish to open a new database on the instance, first // shutdown the instance and then startup the instance and // retry the operation.
But if I try selecting from a dba_ table it tells me the database isn't open and I can only select from fixed tables (which I can with no problems).
The ORACLE_SID is set correctly.
Any ideas before I restore from backup tomorrow morning and start over? Solaris 2.6, Oracle 8.1.6
-----Original Message-----
From: Miller, Jay [mailto:JayMiller_at_tdwaterhouse.com]
Sent: Tuesday, June 06, 2000 4:22 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rebuilding database to change block size
Being on 8.1.6 I can change the free list parameter whenever I like :). But your point is well taken and it reminded me that I was intending to seperate my partitioned tables into one ts/partition when I got the chance.
-----Original Message-----
Sent: Tuesday, June 06, 2000 3:10 PM
To: Multiple recipients of list ORACLE-L
Also check your I/O and see if you have any bottle necks. You could use this as a chance to move tablespaces easily to other file systems. You could move tables to different tablespaces. You can change your free list parameter.
You are at a point where you can take a step back and say "What can I change now that I cannot easily change later?"
-----Original Message-----
Sent: Tuesday, June 06, 2000 1:00 PM
To: Multiple recipients of list ORACLE-L
Hi Jay,
I'd take this opportunity to do a little more. What I've done in the past is
to pre-create a script to create the tablespaces, database objects, users, etc. That way when you're done, you'll have a script to create the database
anytime you want, and you'll have a better feel for your database.
Mike
> Okay, we finally got enough storage space to hold an export of our
> datawarehouse so I'm going to change the block size from 4 to 16.
>
> Obviously I'm testing this on a small play database first, but I've never
> done this before so I have several questions and would welcome any
> advice/comments/criticisms. Here's what I was thinking of:
>
> 1. Do a full export (consistent=y) as sys. Backup control file to trace
> (just as an extra precaution).
> 2. Shutdown database.
> 3. Cold backup of all files.
> 4. Drop all datafiles,control files, redo logs. Any other cleanup
> necessary? Or since the CREATE DATABASE command will erase data in
existing
> datafiles, can I just skip this step entirely?
> 5. Change db_block_size in init.ora file
> 6. Start database in NOMOUNT mode.
>
> Here's where I'm on somewhat thinner ice:
> 7. Issue create database command. Is there a way to generate this
> automatically from the existing database? It's easy enough to write from
> scratch but I'd rather not take chances with typos if I don't have to.
> 8. Change sys and system passwords.
> 9. I definitely want to precreate a few tablespaces where I want to
change
> the initial extent parameter. Is it necessary to precreate all
tablespaces
> and users (I can generate that script very easily)?
> 10. Import database as sys.
>
>
> Okay, what am I missing?
>
> Thanks to all,
> Jay
> --
> Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Author: Mike Lanteigne INET: mikel_at_shec.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Author: Miller, Jay INET: JayMiller_at_TDWaterhouse.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Jun 06 2000 - 16:14:23 CDT
![]() |
![]() |