Re: ORA-03113 on startup

In our last gripping episode lbudney_at_pobox.com wrote:
> In article <5QoS5.219256$g6.101703319_at_news2.rdc2.tx.home.com>,
> "Michael O'Neill" <mjoneill_at_email.com> wrote:
> > When was your last backup? Last time I had that error it was
> > a symptom of a larger problem, namely a sever corruption in the
> > data dictionary caused by a hardware failure.
>
> Well, that's a problem. This is a personal setup while I prepare for
> the Oracle DBA tests. I had a working database, but tried regenerating
> a control file to rename it. I didn't have proper backups at the time,
> and the DB wouldn't start, complaining that the files were
 inconsistent.
> So I can't easily avoid the charge that I "just plain screwed it all
> up."
>
> However it seems strange to me that the DB would suddenly start this
> new problem, and I thought I could address it separately. Apparently
> not?
>
> BTW I did borrow copies of the control files, redo logs, etc., from a
> different database which was working. When I put those in place and
> tried starting up, I still got the same error ORA-03113. Those were
> properly copied from an OS-level cold backup, so they should be fine
> by themselves. Any suggestion where to look? Is there a common file I
> probably forgot to modify? (The borrowed DB was basically identical to
> the one I screwed up.)
>
> As a last resort, where might I look for instructions in producing a
> clean, empty instance? I.e., to create generic control files,
 intialize
> brand-new tablespaces and redo logs, etc., so as to get up and running
> again? The data which I lost was completely expendable. (I'd just
> reinstall, but the Java-based installer doesn't work under RedHat 7.)
>
> Thanks for your reply; at least you're helping me realize the vital
> importance of backups!
>
> Len.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

To create a clean, empty instance several steps need to be executed:

Create or reuse the existing parameter file (init<SID>.ora). This can be renamed or copied to a different name if the database name is to change. If the dbname will change make the db_name parameter in the init<SID>.ora file the same as the new name.

Create the new database with a command similar to that listed below:

CREATE DATABASE newtest

CONTROLFILE REUSE
LOGFILE
   GROUP 1 ('diskb:log1.log', 'diskc:log1.log') SIZE 50K,    GROUP 2 ('diskb:log2.log', 'diskc:log2.log') SIZE 50K MAXLOGFILES 5
MAXLOGHISTORY 100
DATAFILE 'diska:dbone.dat' SIZE 200M
MAXDATAFILES 10
ARCHIVELOG
CHARACTER SET US7ASCII; This example reuses the existing control files, creates two redo log groups, sets the character set, sets archivelog mode and sets the maximum number of datafiles.

The next step is to load the various
packages/functions/procedures/tables/views necessary for proper database operation (such as imp and exp):

@?/rdbms/admin/catalog

This should set up the basic functionality within ORACLE.

Next is to create the tablespaces; you can reuse the datafiles you created earlier:

...
create tablespace ... datafile '.....' size ... reuse; ...

Repeat this syntax for each tablespace, except for SYSTEM (which has already been created with the CREATE DATABASE command) you need for your instance. Of course, you can also create new datafiles for this instance but it might be a bit faster to reuse the existing files.

Next, create your rollback segments and bring them online so that the SYSTEM rollback segment can be taken offline:

create rollback segment R01 ... tablespace RBS; ...

alter rollback segment R01 online;
...

alter rollback segment system offline;

Create the user accounts:

create user ... identified by ... default tablespace ... temporary tablespace TEMP quota ... on ...;

grant connect, create session to ...;

You will want a DBA account outside of SYS and SYSTEM:

create user mydba identified by mypass default tablespace ... temporary tablespace temp quota unlimited on ...;

grant connect, resource, dba to mydba;

[RESOURCE is soon to be de-supported; usually granting DBA to a user account provides sufficient privilege to perform administration functions]. Run the catdbsyn.sql script after connecting as the new DBA: connect mydba/mypass

@?/rdbms/admin/catdbsyn

This creates the synonyms the DBA will need.

This is, by no stretch of the imagination, a comprehensive treatise on creating a database. This should get you started creating a new instance to work from.

The instructions above have been written with UNIX/Linux in mind. All commands are executed through Server Manager (svrmgrl).

If you have installed Oracle on NT/2000 you can use oradim or the Oracle Database Manager (found from the Start menu: Start->Programs-
>Oracle->Database Administration->Database Manager, I believe) to
create a new instance. Start the manager and follow the GUI; you should be able to get by with the default settings.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.