RE: Rebuilding database to change block size

From: Bruce Page <bpage_at_kimball.com>
Date: Tue, 6 Jun 2000 12:56:08 -0500
Message-Id: <10520.107933@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFCFE0.954BD6A4
Content-Type: text/plain;

charset="iso-8859-1"

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-----
From: Mike Lanteigne [mailto:mikel_at_shec.com] Sent: Tuesday, June 06, 2000 1:00 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Rebuilding database to change block size

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).

------_=_NextPart_001_01BFCFE0.954BD6A4
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Rebuilding database to change block size</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Also check your I/O and see if you have any bottle =
necks.&nbsp; You could use this as a chance to move tablespaces easily =
to other file systems.&nbsp; You could move tables to different =
tablespaces.&nbsp; You can change your free list parameter.</FONT></P>

<P><FONT SIZE=3D2>You are at a point where you can take a step back and =
say &quot;What can I change now that I cannot easily change =
later?&quot;</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Mike Lanteigne [<A =
HREF=3D"mailto:mikel_at_shec.com">mailto:mikel_at_shec.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, June 06, 2000 1:00 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Rebuilding database to change block =
size</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Hi Jay,</FONT>
</P>

<P><FONT SIZE=3D2>I'd take this opportunity to do a little more. What =
I've done in the past is</FONT>
<BR><FONT SIZE=3D2>to pre-create a script to create the tablespaces, =
database objects,&nbsp; users,</FONT>
<BR><FONT SIZE=3D2>etc.&nbsp; That way when you're done, you'll have a =
script to create the database</FONT>
<BR><FONT SIZE=3D2>anytime you want, and you'll have a better feel for =
your database.</FONT>
</P>

<P><FONT SIZE=3D2>Mike</FONT>
</P>
<BR>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>----- Original Message -----</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, June 06, 2000 11:50 AM</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>&gt; Okay, we finally got enough storage space to =
hold an export of our</FONT>
<BR><FONT SIZE=3D2>&gt; datawarehouse so I'm going to change the block =
size from 4 to 16.</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Obviously I'm testing this on a small play =
database first, but I've never</FONT>
<BR><FONT SIZE=3D2>&gt; done this before so I have several questions =
and would welcome any</FONT>
<BR><FONT SIZE=3D2>&gt; advice/comments/criticisms.&nbsp; Here's what I =
was thinking of:</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; 1.&nbsp; Do a full export (consistent=3Dy) as =
sys.&nbsp; Backup control file to trace</FONT>
<BR><FONT SIZE=3D2>&gt; (just as an extra precaution).</FONT>
<BR><FONT SIZE=3D2>&gt; 2.&nbsp; Shutdown database.</FONT>
<BR><FONT SIZE=3D2>&gt; 3.&nbsp; Cold backup of all files.</FONT>
<BR><FONT SIZE=3D2>&gt; 4.&nbsp; Drop all datafiles,control files, redo =
logs.&nbsp; Any other cleanup</FONT>
<BR><FONT SIZE=3D2>&gt; necessary?&nbsp; Or since the CREATE DATABASE =
command will erase data in</FONT>
<BR><FONT SIZE=3D2>existing</FONT>
<BR><FONT SIZE=3D2>&gt; datafiles, can I just skip this step =
entirely?</FONT>
<BR><FONT SIZE=3D2>&gt; 5.&nbsp; Change db_block_size in init.ora =
file</FONT>
<BR><FONT SIZE=3D2>&gt; 6.&nbsp; Start database in NOMOUNT mode.</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Here's where I'm on somewhat thinner =
ice:</FONT>
<BR><FONT SIZE=3D2>&gt; 7.&nbsp; Issue create database command.&nbsp; =
Is there a way to generate this</FONT>
<BR><FONT SIZE=3D2>&gt; automatically from the existing database?&nbsp; =
It's easy enough to write from</FONT>
<BR><FONT SIZE=3D2>&gt; scratch but I'd rather not take chances with =
typos if I don't have to.</FONT>
<BR><FONT SIZE=3D2>&gt; 8.&nbsp; Change sys and system =
passwords.</FONT>
<BR><FONT SIZE=3D2>&gt; 9.&nbsp; I definitely want to precreate a few =
tablespaces where I want to</FONT>
<BR><FONT SIZE=3D2>change</FONT>
<BR><FONT SIZE=3D2>&gt; the initial extent parameter.&nbsp; Is it =
necessary to precreate all</FONT>
<BR><FONT SIZE=3D2>tablespaces</FONT>
<BR><FONT SIZE=3D2>&gt; and users (I can generate that script very =
easily)?</FONT>
<BR><FONT SIZE=3D2>&gt; 10.&nbsp; Import database as sys.</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Okay, what am I missing?</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Thanks to all,</FONT>
<BR><FONT SIZE=3D2>&gt; Jay</FONT>
<BR><FONT SIZE=3D2>&gt; --</FONT>
<BR><FONT SIZE=3D2>&gt; Author: Miller, Jay</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp; INET: =
JayMiller_at_TDWaterhouse.com</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services&nbsp;&nbsp;&nbsp; -- =
(858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt; =
--------------------------------------------------------------------</FO=
NT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information (like subscribing).</FONT>
</P>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Mike Lanteigne</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: mikel_at_shec.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =

Received on Tue Jun 06 2000 - 12:56:08 CDT