oracle database creation

[Tarek_1]

Hi,
i want to do two things:
1) create a new database from command line
2) create a new database by importing data from an existing one
So what's the approach to follow in the two cases?
What i have to do? how and when create controlfile, redologs, instance....
The database that i want to import is exported from 8.1.6 and has to be imported in 9.2.

[Alan Casey]

Hi Tarek

I think you should be posting this question on the Oracle website:


http://metalink.oracle.com/


Can you get access to this?

[John Palmer · Accepted solution]

Normal steps to create a database...

1. Create appropriate initSID.ora
2. Set your environment for the new SID, ORACLE_SID etc.
3. Run:
sqlplus '/ as sysdba'
startup nomount
create database...
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
create tablespace...

create rollback segments...

The controlfiles are specified in your init file and will be created by 'create database'

The redologs are specified in your 'create database'.

You need to read the Oracle documentation provided on the documentation CD provided with the software to decide what you want to specify in 'create database' etc.

Once you've created the database, you can run import to populate it with data from the 8.1.6 export. There are many ways of running import, you pays your money and takes your choice...

The above is an outline of the tasks involved, if you don't have access to a DBA then you've got a lot of reading to do!

Regards,
John

[T G Manikandan]

it is a big process

check this doc for creating the database

http://www.vi.unizh.ch/oracle8i_816/onlinedocu/doc/server.816/a76956/create.htm#1656

[Andreas D. Skjervold]

Hi

The easy way out is this:
Run the dbca assistant. This is a Java wizard that lets you set up the database whitout createing the scripts yourself.
To run:
export DISPLAY=yourworkstation:0.0
cd $ORACLE_HOME/bin/
./dbca

Fill in all the windows and the dbca can create the database for you.

BUT
what you should do is to select "Create script" in the last window.
This saves the create scripts for the database in a given location.

You can the edit and run these scripts one by one to have full controll over your database (as you should)

When the database is created and up and running you can create the user that holds the schema you'll import from the old database.
Remeber to create this with the same rights.

Then export the old schema from the 8.1.6 database using owner= parameter and import into the 9i database.
Import is then done to the same user. If you want it into another user use the fromuser touser parameters.

Andreas

[Tarek_1]

A few doubts.. when i issue CREATE DATABASE statement, i have to create the SYSTEM tablespace or i can create it later?
If i want to create it in the CREATE DATABASE statement what is the correct syntax?
CREATE DATABASE blaba
LOGFILE ....
DEFAULT TABLESPACE system
DATAFILE'.../system.dbf' SIZE..
UNDO TABLESPACE undotbs
DATAFILE '...'

So in the statement i DON'T have to specify CONTROLFILE clause because it's created automatically by reading init$SID.ora??

[Jean-Luc Oudart]

control files defintion are in the init.ora

I aatched an example script to run as user SYS
(this is for an oracle8i instance)
change the string with your ORACLE_SID,
adapt for file sizes.

Jean-Luc

[R. Allan Hicks]

From your 11/13 post....

do I have to create a system tablespace?

Yes, the system tablespace contains the data dictionary for all the data objects.

The control files contain information about the level of change in each of the tablespaces are are used in the recovery process to validate the database and they are created when the database is created.

As another respondent has already pointed out....

Use the dbca and look at the scripts created. Building a database from a command line is not an easy task without going through a series of scripts anyway to set up the various packages and data definitions in the system tablespace.

-Good Luck

[Anurag Pathik_2]

The follwoing is the script U can use. Just change the respective path in your environment.

CREATE DATABASE X
DATAFILE '/x01/oradata/arch/system.dbf' SIZE 700M
character set "WE8ISO8859P1"
maxdatafiles 200
LOGFILE GROUP 1 ('/x01/oradata/arch/redoBAAN01.log') size 10M,
GROUP 2 ('/x01/oradata/arch/redoBAAN02.log') size 10M,
GROUP 3 ('/x01/oradata/arch/redoBAAN03.log') SIZE 10M;


create tablespace rbs datafile '/x01/oradata/BAAN/rbs01.dbf' size 5000M;
create tablespace temp datafile '/x01/oradata/BAAN/temp01.dbf' size 3000M;
create tablespace users datafile '/x01/oradata/BAAN/users01.dbf' size 200M;

create rollback segment r01 tablespace rbs storage (initial 500M next 500M maxex
tents unlimited);
create rollback segment r02 tablespace rbs storage (initial 500M next 500M maxex
tents unlimited);
create rollback segment r03 tablespace rbs storage (initial 500M next 500M maxex
tents unlimited);
create rollback segment r04 tablespace rbs storage (initial 500M next 500M maxex
tents unlimited);
create rollback segment r05 tablespace rbs storage (initial 500M next 500M maxex
tents unlimited);

@/usr/db/app/oracle/product/8.1.6/rdbms/admin/catalog
@/usr/db/app/oracle/product/8.1.6/rdbms/admin/catproc

[Anurag Pathik_2]

Also to import and export. Can U tell me if the old and new database is on the same machine? Is the OS unix? Do you have an issue with downtime during import/export process?, How much down time U can aford and the size of the database.