Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
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.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Addition adding a new database

Deanna Tran_1
Frequent Advisor

Addition adding a new database

I'd like to know the process on how to add on the additional database. The previous database was created using the dbca. Do I have to create a new .profile for this new instance of database, or can I just edit the previous .profile of the existing database?
thankx
13 REPLIES
Nick Wickens
Respected Contributor

Re: Addition adding a new database

Do you mean environment variables for a second database ? On our Informix system we switch between the databases by having an alias in the main profile which points to a second profile containing the environment variables that differ. ie

alias it=". $HOME/.IT.profile;unset INFORMIXSQLHOSTS"
alias dev=". $HOME/.DEV.profile;unset INFORMIXSQLHOSTS"
alias eval=". $HOME/.EVAL.profile;unset INFORMIXSQLHOSTS"

By the way - did'nt you crash the Enterprise ? :@)
Hats ? We don't need no stinkin' hats !!
Kevin Hansen
Advisor

Re: Addition adding a new database

we usually use the same .profile and have a seperate script that sets up oracle specific environment variables for each sid. Oracle ships a script to do this named oraenv. depending on who installed oracle it may be in /usr/local/bin.
Deanna Tran_1
Frequent Advisor

Re: Addition adding a new database

Hi kevin,
Can you give me an example on how to do it?
I found the oraenv script in /usr/local/bin
Can you show me how you set your up?
Deanna Tran_1
Frequent Advisor

Re: Addition adding a new database

Hi kevin,
what are the other scripts that I might need to use to set up an environment?
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

You can use the $ORACLE_HOME/bin/dbassist to create the new database.
YOu have a good GUI kind of creating a database where you can select the options.

Else
you should do as

1.copy the initialization file to the new instance.
$cp $ORACLE_HOME/dbs/init.ora init"".ora
2.Edit the init"instance-name".ora
to change the database_name
and set the values for the Shared_pool_size,DB_BLOCK_BUFFERS,etc..
Also change the location of your control files to the location you want to.

save the file

3.set you ORACLE_SID parameter to the new instance name.

eg.ORACLE_SID=DB001;export ORACLE_SID

check the value of the ORACLE_SID whether it is set properly.

echo $ORACLE_SID

4.$svrmgrl
svrmgr>connect internal

svrmgr>startup nomount pfile=$ORACLE_HOME/dbs/init"newinstance-name".ora

Now your instance will be started

svrmgr>create database "database-name"
>controlfile reuse
>datafile '>logfile group 1 ('oracle_home/dbs/log1NEW_NAME.dbf') size 10 M,
> group 2 ('oracle_home/dbs/log2NEW_NAME.dbf') size 10 M

check for the size for your database.
The datafile created is for the default system tablespace.

now after the database is created,you have to run the catalog.sql and catproc.sql

svrmgr>@$ORACLE_HOME/rdbms/admin/catalog
svrmgrl>@$ORACLE_HOME/rdbms/admin/catproc

After you run the two scripts.

create a seperate tablespace for rollback segment
svrmgr>create tablespace rollback datafile '/home/oracle/rollback.dbf' size 100 M;
svrmgrl>create rollback segment R01 tablespace rollback storage(maxextents unlimited);
svrmgrl>alter rollback segment R01 online;
Now the rollback segment is created .
Now make the entry in the
$ORACLE_HOME/dbs/init"new-instance".ora
In that enter R01 in the place of the
rollback segment in the file.

create the required tablespace ,like if you require user tablespace
like the one for the rollback segment.


Thanks
T G Manikandan
Honored Contributor

Re: Addition adding a new database

sorry,
I clicked twice as the post was not submitted.
So many times....


Sorry

Jeanine Kone
Trusted Contributor

Re: Addition adding a new database

Don't forget to update your /etc/oratab file to add the new database also. If you don't, it will not start and stop on reboot. In addition, some of the env file use it to get the ORACLE_HOME of the database, etc.

I have my env set up to prompt me for the database and then set my env variables appropriately. Here are the lines I added to my .profile to do the prompting and then call the oraenv script:

# Set up the Oracle environment
SIDLIST=`awk -F: '/^[^#]/{printf "%s ",$1}' /etc/oratab`
echo "SIDs on this machine are: $SIDLIST"
ORAENV_ASK=
. /usr/local/bin/oraenv

Hope this helps