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: Janardhana Babu <jbdonga_at_ucdavis.edu>
Date: Wed, 07 Jun 2000 09:51:25 -0700
Message-Id: <10521.108067@fatcity.com>

--=====================_336694831==_.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed

shutdown the database even if it is not open. If shutdown won't work, use shutdown abort, and then startup nomount pfile=..., then issue create database command.

> > 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-----
> > Sent: Tuesday, June 06, 2000 4:22 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > 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
> >
> >
> >
> >
> >
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, June 06, 2000 11:50 AM
> >
> >
> > > 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
> > (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
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
>__________________________________________________
>Do You Yahoo!?
>Send online invitations with Yahoo! Invites.
>http://invites.yahoo.com
>--
>Author: A. Bardeen
>   INET: abardeen1_at_yahoo.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
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).

--=====================_336694831==_.ALT
Content-Type: text/html; charset="us-ascii"

<html>
shutdown the database even if it is not open. If shutdown won't work, use shutdown abort, and then startup nomount pfile=..., then issue create database command.<br>
<br>
-- janardhana Babu<br>

<br>
<br>
<br>
<blockquote type=cite cite>&gt; If I try issuing an ALTER DATABASE OPEN;
command I<br>
&gt; get the error:<br>
&gt; when attempting to open the database:<br>
&gt; 01531, 00000, &quot;a database already open by the<br>
&gt; instance&quot;<br>
&gt; // *Cause:&nbsp; During ALTER DATABASE, an attempt was<br>
&gt; made to open<br>
&gt; //&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a database on an
instance for which there<br>
&gt; is already<br>
&gt; //&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; an open database.<br>
&gt; // *Action:&nbsp; If you wish to open a new database on<br>
&gt; the instance, first<br>
&gt; //&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; shutdown
the instance and then startup<br>
&gt; the instance and<br>
&gt; //&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; retry the operation.<br>
&gt; <br>
&gt; <br>
&gt; But if I try selecting from a dba_ table it tells me<br>
&gt; the database isn't open<br>
&gt; and I can only select from fixed tables (which I can<br>
&gt; with no problems).<br>
&gt; <br>
&gt; The ORACLE_SID is set correctly.<br>
&gt; <br>
&gt; Any ideas before I restore from backup tomorrow<br>
&gt; morning and start over?<br>
&gt; Solaris 2.6, Oracle 8.1.6<br>
&gt; <br>
&gt; <br>
&gt; -----Original Message-----<br>
&gt; Sent: Tuesday, June 06, 2000 4:22 PM<br>
&gt; To: Multiple recipients of list ORACLE-L<br>
&gt; <br>
&gt; <br>
&gt; Being on 8.1.6 I can change the free list parameter<br>
&gt; whenever I like :).<br>
&gt; But your point is well taken and it reminded me that<br>
&gt; I was intending to<br>
&gt; seperate my partitioned tables into one ts/partition<br>
&gt; when I got the chance.<br>
&gt; <br>
&gt; -----Original Message-----<br>
&gt; Sent: Tuesday, June 06, 2000 3:10 PM<br>
&gt; To: Multiple recipients of list ORACLE-L<br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; Also check your I/O and see if you have any bottle<br>
&gt; necks.&nbsp; You could use<br>
&gt; this as a chance to move tablespaces easily to other<br>
&gt; file systems.&nbsp; You<br>
&gt; could move tables to different tablespaces.&nbsp; You can<br>
&gt; change your free list<br>
&gt; parameter.<br>
&gt; <br>
&gt; You are at a point where you can take a step back<br>
&gt; and say &quot;What can I change<br>
&gt; now that I cannot easily change later?&quot; <br>
&gt; <br>
&gt; <br>
&gt; -----Original Message----- <br>
&gt; Sent: Tuesday, June 06, 2000 1:00 PM <br>
&gt; To: Multiple recipients of list ORACLE-L <br>
&gt; <br>
&gt; <br>
&gt; Hi Jay, <br>
&gt; <br>
&gt; I'd take this opportunity to do a little more. What<br>
&gt; I've done in the past is<br>
&gt; <br>
&gt; to pre-create a script to create the tablespaces,<br>
&gt; database objects,&nbsp; users, <br>
&gt; etc.&nbsp; That way when you're done, you'll have a<br>
&gt; script to create the database<br>
&gt; <br>
&gt; anytime you want, and you'll have a better feel for<br>
&gt; your database. <br>
&gt; <br>
&gt; Mike <br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; ----- Original Message ----- <br>
&gt; To: Multiple recipients of list ORACLE-L<br>
&gt; &lt;ORACLE-L_at_fatcity.com&gt; <br>
&gt; Sent: Tuesday, June 06, 2000 11:50 AM <br>
&gt; <br>
&gt; <br>
&gt; &gt; Okay, we finally got enough storage space to hold<br>
&gt; an export of our <br>
&gt; &gt; datawarehouse so I'm going to change the block<br>
&gt; size from 4 to 16. <br>
&gt; &gt; <br>
&gt; &gt; Obviously I'm testing this on a small play<br>
&gt; database first, but I've never <br>
&gt; &gt; done this before so I have several questions and<br>
&gt; would welcome any <br>
&gt; &gt; advice/comments/criticisms.&nbsp; Here's what I was<br>
&gt; thinking of: <br>
&gt; &gt; <br>
&gt; &gt; 1.&nbsp; Do a full export (consistent=y) as sys. <br>
&gt; Backup control file to trace <br>
&gt; &gt; (just as an extra precaution). <br>
&gt; &gt; 2.&nbsp; Shutdown database. <br>
&gt; &gt; 3.&nbsp; Cold backup of all files. <br>
&gt; &gt; 4.&nbsp; Drop all datafiles,control files, redo logs. <br>
&gt; Any other cleanup <br>
&gt; &gt; necessary?&nbsp; Or since the CREATE DATABASE command<br>
&gt; will erase data in <br>
&gt; existing <br>
&gt; &gt; datafiles, can I just skip this step entirely? <br>
&gt; &gt; 5.&nbsp; Change db_block_size in init.ora file <br>
&gt; &gt; 6.&nbsp; Start database in NOMOUNT mode. <br>
&gt; &gt; <br>
&gt; &gt; Here's where I'm on somewhat thinner ice: <br>
&gt; &gt; 7.&nbsp; Issue create database command.&nbsp; Is there a
way<br>
&gt; to generate this <br>
&gt; &gt; automatically from the existing database?&nbsp; It's<br>
&gt; easy enough to write from <br>
&gt; &gt; scratch but I'd rather not take chances with typos<br>
&gt; if I don't have to. <br>
&gt; &gt; 8.&nbsp; Change sys and system passwords. <br>
&gt; &gt; 9.&nbsp; I definitely want to precreate a few<br>
&gt; tablespaces where I want to <br>
&gt; change <br>
&gt; &gt; the initial extent parameter.&nbsp; Is it necessary to<br>
&gt; precreate all <br>
&gt; tablespaces <br>
&gt; &gt; and users (I can generate that script very<br>
&gt; easily)? <br>
&gt; &gt; 10.&nbsp; Import database as sys. <br>
&gt; &gt; <br>
&gt; &gt; <br>
&gt; &gt; Okay, what am I missing? <br>
&gt; &gt; <br>
&gt; &gt; Thanks to all, <br>
&gt; &gt; Jay <br>
&gt; &gt; -- <br>
&gt; &gt; Author: Miller, Jay <br>
&gt; &gt;&nbsp;&nbsp; INET: JayMiller_at_TDWaterhouse.com <br>
&gt; &gt; <br>
&gt; &gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051
<br>
&gt; FAX: (858) 538-5051 <br>
&gt; &gt; San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet<br>
&gt; access / Mailing Lists <br>
&gt; &gt;<br>
&gt;<br>
--------------------------------------------------------------------<br>
&gt; <br>
&gt; &gt; To REMOVE yourself from this mailing list, send an<br>
&gt; E-Mail message <br>
&gt; &gt; to: ListGuru_at_fatcity.com (note EXACT spelling of<br>
&gt; 'ListGuru') and in <br>
&gt; &gt; the message BODY, include a line containing: UNSUB<br>
&gt; ORACLE-L <br>
&gt; &gt; (or the name of mailing list you want to be<br>
&gt; removed from).&nbsp; You may <br>
&gt; &gt; also send the HELP command for other information<br>
&gt; (like subscribing). <br>
&gt; <br>
&gt; -- <br>
&gt; Author: Mike Lanteigne <br>
&gt;&nbsp;&nbsp; INET: mikel_at_shec.com <br>
&gt; <br>
&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX:<br>
&gt; (858) 538-5051 <br>
&gt; San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet<br>
&gt; access / Mailing Lists <br>
&gt;<br>
--------------------------------------------------------------------<br>
&gt; <br>
&gt; To REMOVE yourself from this mailing list, send an<br>
&gt; E-Mail message <br>
&gt; to: ListGuru_at_fatcity.com (note EXACT spelling of<br>
&gt; 'ListGuru') and in <br>
&gt; the message BODY, include a line containing: UNSUB<br>
&gt; ORACLE-L <br>
&gt; (or the name of mailing list you want to be removed<br>
&gt; from).&nbsp; You may <br>
&gt; also send the HELP command for other information<br>
&gt; (like subscribing). <br>
&gt; <br>
&gt; -- <br>
&gt; Author: Miller, Jay<br>
&gt;&nbsp;&nbsp; INET: JayMiller_at_TDWaterhouse.com<br>
&gt; <br>
&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX:<br>
&gt; (858) 538-5051<br>
&gt; San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet<br>
&gt; access / Mailing Lists<br>
&gt;<br>
--------------------------------------------------------------------<br>
&gt; To REMOVE yourself from this mailing list, send an<br>
&gt; E-Mail message<br>
&gt; to: ListGuru_at_fatcity.com (note EXACT spelling of<br>
&gt; 'ListGuru') and in<br>
&gt; the message BODY, include a line containing: UNSUB<br>
&gt; ORACLE-L<br>
&gt; (or the name of mailing list you want to be removed<br>
&gt; from).&nbsp; You may<br>
&gt; also send the HELP command for other information<br>
&gt; (like subscribing).<br>
&gt; -- <br>
&gt; Author: Miller, Jay<br>
&gt;&nbsp;&nbsp; INET: JayMiller_at_TDWaterhouse.com<br>
&gt; <br>
&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX:<br>
&gt; (858) 538-5051<br>
&gt; San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet<br>
&gt; access / Mailing Lists<br>
&gt;<br>
--------------------------------------------------------------------<br>
&gt; To REMOVE yourself from this mailing list, send an<br>
&gt; E-Mail message<br>
&gt; to: ListGuru_at_fatcity.com (note EXACT spelling of<br>
&gt; 'ListGuru') and in<br>
&gt; the message BODY, include a line containing: UNSUB<br>
&gt; ORACLE-L<br>
&gt; (or the name of mailing list you want to be removed<br>
&gt; from).&nbsp; You may<br>

&gt; also send the HELP command for other information<br> &gt; (like subscribing).<br>
<br>
<br>
__________________________________________________<br>
Do You Yahoo!?<br>
Send online invitations with Yahoo! Invites.<br> <a href="http://invites.yahoo.com/" eudora="autourl">http://invites.yahoo.com</a><br> -- <br>
Author: A. Bardeen<br>
&nbsp; INET: abardeen1_at_yahoo.com<br>
<br>
Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) 538-5051<br>
San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet access / Mailing Lists<br>
--------------------------------------------------------------------<br>
To REMOVE yourself from this mailing list, send an E-Mail message<br> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<br> the message BODY, include a line containing: UNSUB ORACLE-L<br> (or the name of mailing list you want to be removed from).&nbsp; You may<br>
also send the HELP command for other information (like subscribing).<br>
-- <br>
Author: Miller, Jay<br>
&nbsp; INET: JayMiller_at_TDWaterhouse.com<br> <br>
Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) 538-5051<br>
San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Received on Wed Jun 07 2000 - 11:51:25 CDT

Original text of this message

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