Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle database creation

SOLVED
Go to solution
Tarek_1
Frequent Advisor

oracle database creation

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.
9 REPLIES
Alan Casey
Trusted Contributor

Re: oracle database creation

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
Honored Contributor
Solution

Re: oracle database creation

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
Honored Contributor

Re: oracle database creation

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
Honored Contributor

Re: oracle database creation

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


Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Tarek_1
Frequent Advisor

Re: oracle database creation

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
Honored Contributor

Re: oracle database creation

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
fiat lux
R. Allan Hicks
Trusted Contributor

Re: oracle database creation

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
"Only he who attempts the absurd is capable of achieving the impossible
Anurag Pathik_2
Occasional Visitor

Re: oracle database creation

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
Occasional Visitor

Re: oracle database creation

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.