need to create a second instance

[hrishy]

Hi

i am in urgent need of craeting one more test instance on my test machine.i already have a instance name test1 and a databse by the same name.could anybody expalin me how to create one more instance
named test2 which can mount a database name test2.........what envirinment variables should i change etc step by step info would be great.

databaseracle 8.1.6
o/s:sun solaris

regards
hrishy

[tomate]

call the dbaasistent then you can make with an application.

Or as script which must you modify.
Here is the script

#! /bin/sh

if [ $# != 1 ]; then
echo "usage: create_db_instance.sh "
exit
fi

DBNAME=$1
ORA_HOME=/opt/Oracle8i/OraHome
ADMINDIR=/opt/Oracle8i/admin/$DBNAME
DATADIR=/opt/Oracle8i/oradata/oradata/$DBNAME

ORACLE_SID=$DBNAME
export ORACLE_SID


#
# create needed directories
#

# ... for data
mkdir $DATADIR

# .. for configs
mkdir $ADMINDIR
mkdir $ADMINDIR/adhoc
mkdir $ADMINDIR/arch
mkdir $ADMINDIR/bdump
mkdir $ADMINDIR/cdump
mkdir $ADMINDIR/create
mkdir $ADMINDIR/exp
mkdir $ADMINDIR/pfile
mkdir $ADMINDIR/udump


#
# now copy/edit some files
#
cd $ORA_HOME/dbs
cp init.ora $ADMINDIR/pfile/init$DBNAME.ora
ln -s ../admin/$DBNAME/pfile/init$DBNAME.ora

#
# user SYS anlegen mit password "change_on_install"
#
orapwd file=orapw$DBNAME password=change_on_install



#
# edit $ADMINDIR/pfile/init$DBNAME.ora
#
# change db_name
# change db_domain
# change contrl_file to something like
# "/opt/database/oradata/$DBNAME/control01.ctl",
# "/opt/database/oradata/$DBNAME/control02.ctl",
# "/opt/database/oradata/$DBNAME/control03.ctl"
echo "please edit /opt/oracle/OraHome1/dbs/init$DBNAME.ora and then enter return"
read tmp


##################################################################################


$ORA_HOME/bin/svrmgrl << EOF
spool $ADMINDIR/create/crdb1.log
connect internal
startup nomount pfile = "$ADMINDIR/pfile/init$DBNAME.ora"
CREATE DATABASE "$DBNAME"
maxdatafiles 254
maxinstances 8
maxlogfiles 32
character set US7ASCII
national character set US7ASCII
DATAFILE '$DATADIR/system01.dbf' SIZE 54M AUTOEXTEND ON NEXT 640K
logfile '$DATADIR/redo01.log' SIZE 500K,
'$DATADIR/redo02.log' SIZE 500K,
'$DATADIR/redo03.log' SIZE 500K;
disconnect
spool off
exit

EOF

##################################################################################

#echo "db created, please press return"
#read tmp

##################################################################################


$ORA_HOME/bin/svrmgrl << EOF
spool $ADMINDIR/create/crdb2.log
connect internal
@$ORA_HOME/rdbms/admin/catalog.sql;

REM ********** ALTER SYSTEM TABLESPACE *********
ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM
MINIMUM EXTENT 64K;

REM ********** TABLESPACE FOR Tool **********
CREATE TABLESPACE TOOLS DATAFILE '$DATADIR/tools01.dbf' SIZE 8M REUSE
AUTOEXTEND ON NEXT 320K
MINIMUM EXTENT 32K
DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

REM ********** TABLESPACE FOR ROLLBACK **********
CREATE TABLESPACE RBS DATAFILE '$DATADIR/rbs01.dbf' SIZE 1028M REUSE
AUTOEXTEND ON NEXT 1024K
MINIMUM EXTENT 1024K,
'$DATADIR/rbs02.dbf' SIZE 1028M REUSE
AUTOEXTEND ON NEXT 1024K
MINIMUM EXTENT 1024K
DEFAULT STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 4 MAXEXTENTS 4096);

REM ********** TABLESPACE FOR TEMPORARY **********
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '$DATADIR/temp01.dbf'
SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;

REM ********** TABLESPACE FOR USER **********
CREATE TABLESPACE USERS DATAFILE '$DATADIR/users01.dbf' SIZE 104M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

REM ********** TABLESPACE FOR INDEX **********
CREATE TABLESPACE INDX DATAFILE '$DATADIR/indx01.dbf' SIZE 54M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);

REM **** Creating four rollback segments ****************
CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS1 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS2 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS3 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS4 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS5 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS6 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS7 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS8 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS9 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS10 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS11 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS12 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS13 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS14 TABLESPACE RBS
STORAGE ( OPTIMAL 4096K );
ALTER ROLLBACK SEGMENT "RBS0" ONLINE;
ALTER ROLLBACK SEGMENT "RBS1" ONLINE;
ALTER ROLLBACK SEGMENT "RBS2" ONLINE;
ALTER ROLLBACK SEGMENT "RBS3" ONLINE;
ALTER ROLLBACK SEGMENT "RBS4" ONLINE;
ALTER ROLLBACK SEGMENT "RBS5" ONLINE;
ALTER ROLLBACK SEGMENT "RBS6" ONLINE;
ALTER ROLLBACK SEGMENT "RBS7" ONLINE;
ALTER ROLLBACK SEGMENT "RBS8" ONLINE;
ALTER ROLLBACK SEGMENT "RBS9" ONLINE;
ALTER ROLLBACK SEGMENT "RBS10" ONLINE;
ALTER ROLLBACK SEGMENT "RBS11" ONLINE;
ALTER ROLLBACK SEGMENT "RBS12" ONLINE;
ALTER ROLLBACK SEGMENT "RBS13" ONLINE;
ALTER ROLLBACK SEGMENT "RBS14" ONLINE;


REM **** SYS and SYSTEM users ****************
alter user sys temporary tablespace TEMP;
alter user system temporary tablespace TEMP;
disconnect
spool off
exit

EOF


##################################################################################

#echo "db tablespaces created, please press return"
#read tmp

##################################################################################

$ORA_HOME/bin/svrmgrl << EOF
spool $ADMINDIR/create/crdb3.log
connect internal
@$ORA_HOME/rdbms/admin/catproc.sql
@$ORA_HOME/rdbms/admin/caths.sql
@$ORA_HOME/rdbms/admin/otrcsvr.sql
connect system/manager
@$ORA_HOME/sqlplus/admin/pupbld.sql

disconnect
spool off
exit

EOF


##################################################################################

#echo "db catalogs created, please press return"
#read tmp

##################################################################################

$ORA_HOME/bin/svrmgrl << EOF
connect internal/oracle
alter user system default tablespace TOOLS;
alter user system temporary tablespace TEMP;
exit

EOF

##################################################################################

#echo "db tablespaces altered, please press return"
#read tmp

##################################################################################

[pipo]

I guess you want to install another instance in the same version (8.1.6), so ORACLE_HOME env variable won't change.
here are the steps to do this :

1) export ORACLE_SID=TEST2
2) svrmgrl
3) create and personnalize an initTEST2.ora file, and place it in $ORACLE_HOME/dbs (don't forget to change the instance name)
4) run following script, after having personnalized it of course (check the paths at the end for I'm not sure of them)

------------------------------------------
spool CRDBXXXX.log

connect internal

startup nomount

set stoponerror on

create database TEST2
character set (WE8ISO8859P1 or US7ASCII)
controlfile reuse
logfile 'PATH' size 2M reuse,
'PATH' size 2M reuse,
'PATH' size 2M reuse,
'PATH' size 2M reuse
datafile 'PATH' size 60M reuse;

set stoponerror off

create rollback segment rb0
tablespace SYSTEM
storage (initial 16K next 16K);

alter rollback segment rb0 online;

create tablespace RBS
datafile 'PATH' size 100M reuse
default storage (initial 512K next 512K pctincrease 0);

create tablespace TEMP
datafile 'PATH' size 60M reuse
default storage (initial 2M next 2M pctincrease 0);

alter tablespace TEMP
temporary;

alter user SYS identified by manager;
alter user system temporary tablespace TEMP;

create rollback segment rb1
tablespace RBS
storage (minextents 8 optimal 4M);

create rollback segment rb2
tablespace RBS
storage (minextents 8 optimal 4M);

alter rollback segment rb1 online;
alter rollback segment rb2 online;

@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc
@$ORACLE_HOME/sqlplus/plustrce
connect system/manager
@$ORACLE_HOME/dbs/pupbld

-- If ARCHIVELOG NEEDED :
-- connect internal
-- shutdown immediate
-- startup mount
-- alter database archivelog;
-- alter database open;
-- alter system archive log current;

spool off
exit
------------------------------------------

5) here you are

to switch between the instances for SQL*Plus or svrmgrl, the only thing you need to change is the environment variable ORACLE_SID.
Here you are, post if you have a problem

[hrishy]

Thanks to you both......

Pipo i have question..............regarding environmental variables.......................cuurently i have a file called .oracle816 which stores the env settings to connect to test 1................now when i create test2 ......i want something like a menu which should ask me which database to connect to.......i guess i ahve to write a shell script for that..............but i am very very poor at writing shell scripts could anybody provide me such a shell script..

b)do i have to create a separate .oracle816 file which contains the sid of the new instance.....or i can add it to to the existing .oracle816


regards
hrishy

[sudip]

hi!
Why don't you clean shut yr database. back it up
copy all datafiles, logfiles int.ora files to a new location.
Create a new control file specifying a different database name.
Incorporate that name in the inti.ora file
Startup the database.
I wused the same procedure to create a similar test instanace from a production instance.

[hrishy]

Hi

i am looking for a menu which i should get when i log in and ask me which instance should i connect to

b)i am also wundering wheather i should craete one more file like .oracle816 which is currently used to set my env variables of test1 instance.

regards
hrishy

[pipo]

well, depending on your shell, you have in your home, a .profile, .login or .cshrc file.
this file should contain your environment variables.
in your case, the only variable you need to change is ORACLE_SID, so your *menu* will only change that !!

you can use a function like :

---------------------------
function mysid
{
export ORACLE_SID=$1
echo "ORACLE SID = $ORACLE_SID"
}
typeset -fx mysid
---------------------------

you put that in your .profile, and you function will be available, that is to say you can then type :
mysid TEST1
and you know you'll be on TEST1