Friday, July 13, 2012

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

0 comments:

Post a Comment

Auto Scroll Stop Scroll