Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuilding database to change block size - PROBLEM

RE: Rebuilding database to change block size - PROBLEM

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Tue, 6 Jun 2000 17:15:13 -0400
Message-Id: <10520.107972@fatcity.com>


Oh, I should mention that this is a small test database so I'm not concerned but I'm not doing it for real until I get this to work.

-----Original Message-----
From: Miller, Jay
Sent: Tuesday, June 06, 2000 5:14 PM
To: 'ORACLE-L_at_fatcity.com'
Subject: 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 bytes
CREATE DATABASE jay804
*
ORA-01501: CREATE DATABASE failed
ORA-02084: database name is missing a component

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 500K
    DATAFILE '/dataprod/jay804/system_jay804.dbf' SIZE 193404K     maxdatafiles 300
    NOARCHIVELOG
    exclusive;

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-L
Received on Tue Jun 06 2000 - 16:15:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US