Add another database on an existing server

Log in to the existing database and created pfile using existing spfile or pfile, then edit this pfile with the new instance name

Then create passwd file using ORAPWD Utility.

$ orapwd file=/opt/oracle/app/11.2.0.2/dbs/orapwRCDEV  password=oracle

Password file resides in $ORACLE_HOME/dbs location

Important thing is to backup the controlfile to trace, then a trace file will be created in the tracefile location

SQL >alter database backup controlfile to trace;

Copied the trace file to new location and edit as shown below and i saved it as control.ctl file

CREATE CONTROLFILE REUSE DATABASE "OLDDB" NORESETLOGS

NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 240

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',

'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,

GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',

'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M

DATAFILE

'/u01/oradata/oldlsq/system01.dbf',

'/u01/oradata/oldlsq/mydatabase.dbf'

;

edit as ...........

CREATE CONTROLFILE SET DATABASE "NEWDB" RESETLOGS

NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 240

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 ('/u03/oradata/newdb/log1a.dbf',

'/u03/oradata/newdb/log1b.dbf') SIZE 30M,

GROUP 2 ('/u04/oradata/newdb/log2a.dbf',

'/u04/oradata/newdb/log2b.dbf') SIZE 30M

DATAFILE

'/u01/oradata/newdb/system01.dbf',

'/u01/oradata/newdb/mydatabase.dbf'

;

replace old sid with new sid

create all directories specified in the pfile

Shut down the old database and copy the datafiles to new SID datafiles location. Here I don’t have ASM, if we have ASM configured then check the location and copy

NOTE :     check for the tablespaces

Export ORACLE_SID=newinstance

Check with echo $ORACLE_SID

Sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 13 14:21:34 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > 

SQL > startup pfile=’/../../initsid.ora’

SQL> @control.ctl                    (this script created using control file trace file)

SQL >  Alter database open resetlogs;

SQL > shut immediate

SQL > startup pfile=’/../../initsid.ora’

So your database is create. Now just run the catalog.sql and catproc.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

Then the oracle Dictionaries and packages will be created

***** Must BOUNCE the database

After the instance has been created, verify

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 13 14:21:34 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > select dbid, name, instance_name, host_name, created, log_mode, open_mode from v$database, v$instance;

Add listener and TNS entries:

Go to $ORACLE_HOME/network/admin, edit listener and tnsnames.ora

Start the new listener using

SQL>  lsnrctl start new_listener

Check using $ ps –fu oracle |grep tns      -->   to see all running listeners