Re: Rebuilding database to change block size

Forgive me listers....

/plug

Oracle SQL & PL/SQL Annotated Archives from Oracle Press, authors Kevin Loney and Rachel Carmichael

a collection of scripts (some we wrote, some we found in various archives) that are useful to a DBA... the scripts are listed, explained, and the output is also explained. Thus the "annotated". We include a CD with the scripts from the book.

/end of plug

Rachel

>From: "Don Dealy II" <dondealy_at_teleport.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Rebuilding database to change block size
>Date: Wed, 07 Jun 2000 07:29:05 -0800
>
>Where are the Annotated Archives located that you make reference to?
>
>
>---------- Begin Original Message ----------
>
>On 6/6/00 at 10:39 AM Rachel Carmichael wrote:
>
>:>Don't skip step 4.. you get errors if the controlfiles exist when you
>:>rebuild
>:>
>:>7 -- I have a script to generate the create database commands, as well as
>:>the init.ora from an existing database. It's in the Annotated Archives,
>if
>:>you have it. Then edit to change what you want.
>:>
>:>precreate only the tablespaces you want to change. Oracle will create the
>:>others as part of the import.
>:>
>:>Oh, and save off any grants that sys has made that are not part of the
>:>Oracle-supplied scripts.... or you lose them
>:>
>:>Rachel
>:>
>:>
>:>>From: "Miller, Jay" <JayMiller_at_TDWaterhouse.com>
>:>>Reply-To: ORACLE-L_at_fatcity.com
>:>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>:>>Subject: Rebuilding database to change block size
>:>>Date: Tue, 06 Jun 2000 08:50:35 -0800
>:>>
>:>>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).
>:>
>:>________________________________________________________________________
>:>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>:>
>:>--
>:>Author: Rachel Carmichael
>:> INET: carmichr_at_hotmail.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).
>
>---------- End Original Message ----------
>
>--
>Author: Don Dealy II
> INET: dondealy_at_teleport.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).