Existing Instance and Database status:
Environment Variables:
[root@dev ~]# env | grep ORA
ORACLE_OEM_JAVARUNTIME=/opt/java/jre
ORACLE_OWNER=oracle
ORACLE_SID=ins02
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/8.1.6
[root@dev ~]#
Oracle Initialization Files:
[root@dev /]# ls -l $ORACLE_HOME/dbs
total 32
lrwxrwxrwx 1 oracle dba 40 Dec 5 2008 configins02.ora -> /opt/dbfiles/admin/ins02/pfile/configins02.ora
-rw-r–r– 1 oracle dba 9219 Oct 22 1999 initdw.ora
-rw-r–r– 1 oracle dba 8385 Oct 22 1999 init.ora
lrwxrwxrwx 1 oracle dba 38 Dec 5 2008 initins02.ora -> /opt/dbfiles/admin/ins02/pfile/initins02.ora
-rw-rw—- 1 oracle dba 24 Apr 9 14:32 lkins02
-rwSr—– 1 oracle dba 2560 Dec 5 2008 orapwins02
[root@dev /]#
Instance Name and Status:
SQL> select instance_name, status, database_status from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
—————- ——- —————–
ins02 OPEN ACTIVE
SQL>
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
—————– ——— ——- — ———- ——- ———–
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE
———- — —————– ——————
1 ins02
dev.test.lab
8.1.6.4.0 11-APR-14 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCESQL>
DB Service Name:
SQL> Show parameter service_name
NAME TYPE VALUE
———————————— ——- ——————————
service_names string ins02
SQL>
Database Name:
SQL> select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS
———- ——— ——— —————– ———
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE CHECKPOINT_CHANGE#
———————– ——— ———— ——————
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
————— ——- ——— ——————— ——————-
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
——— ———– ——— ———-
2337018765 ins02 11-AUG-06 1 11-AUG-06
0 NOARCHIVELOG 5342178
5320190 CURRENT 11-AUG-06 33353 5342178
11-APR-14 NOT ALLOWED 11-AUG-06 READ WRITESQL>
REMOTE_LOGIN_PASSWORDFILE Mode:
SQL> show parameter password
NAME TYPE VALUE
———————————— ——- ——————————
remote_login_passwordfile string EXCLUSIVE
SQL>
DataFiles being used by Database and their locations:
SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/opt/dbfiles/oradata/ins02/system01ins02.dbf
/opt/dbfiles/oradata/ins02/rbs01ins02.dbf
/opt/dbfiles/oradata/ins02/temp01ins02.dbf
/opt/dbfiles/oradata/ins02/gappins02.dbf
/opt/dbfiles/oradata/ins02/gsysins02.dbf
/opt/dbfiles/oradata/ins02/gblobins02.dbf
/opt/dbfiles/oradata/ins02/gsys_ixins02.dbf
/opt/dbfiles/oradata/ins02/gapp_ixins02.dbf8 rows selected.
SQL>
LogFiles being used by Database and their locations:
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/opt/dbfiles/oradata/ins02/log01ins02.dbf
/opt/dbfiles/oradata/ins02/log02ins02.dbf
/opt/dbfiles/oradata/ins02/log03ins02.dbfSQL>
Detaching Existing DB:
Just initiate the shutdown of DB to stop the existing DB.
SQL> SHUTDOWN NORMAL
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
to attach the files of the different DB, you should be specifying the PFILE parameter with the config details of the new DB that you would like to have started up.
Change REMOTE_LOGIN_PASSWORDFILE Mode to SHARED: This is an important step for bringing up another DB in existing DB instance. By setting this mode to Shared, you are instructing Oracle that the existing PASSWORD file will be used for more than one/all DBs in this instance instead of being exclusively used by only one DB.
Refer to below article for exact steps to do this.
How to Set/Update/Modify Oracle DataBase Instance property: REMOTE_LOGIN_PASSWORDFILE
Once set, you can see it’s value:
SQL> show parameter password
NAME TYPE VALUE
———————————— ——- ——————————
remote_login_passwordfile string SHARED
SQL>
Attaching the New DB and Starting up:
Whenever you startup the Oracle DB it just uses the default/last initialization (PFILE) file specified and picks the respective DB details like name, data/log file locations from the file and starts up the DB.
To bring up other DB, you’ll be simply specifying it’s PFILE and start up the DB.
SQL> STARTUP PFILE=/opt/oracle/product/8.1.5/dbs/initins03.ora
ORACLE inst
ance started.Total System Global Area 87859360 bytes
Fixed Size 73888 bytes
Variable Size 56893440 bytes
Database Buffers 30720000 bytes
Redo Buffers 172032 bytes
Database mounted.
Database opened.
SQL>
Verifications after attaching the DB:
Environment Variables:
[root@dev ~]# env | grep ORA
ORACLE_OEM_JAVARUNTIME=/opt/java/jre
ORACLE_OWNER=oracle
ORACLE_SID=ins02
ORACLE_BASE=/opt/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/opt/oracle/product/8.1.6
[root@dev ~]#
Check Instance Name and Status:
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
—————– ——— ——- — ———- ——- ———–
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE
———- — —————– ——————
1 ins02
dev.test.lab
8.1.7.4.0 11-APR-14 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCESQL>
Check DB Service Name:
SQL> Show parameter service_name
NAME TYPE VALUE
———————————— ——- ——————————
service_names string ProdDB01
SQL>
Database Name:
SQL> select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS
———- ——— ——— —————– ———
PRIOR_RESETLOGS_CHANGE# PRIOR_RES LOG_MODE CHECKPOINT_CHANGE#
———————– ——— ———— ——————
ARCHIVE_CHANGE# CONTROL CONTROLFI CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE#
————— ——- ——— ——————— ——————-
CONTROLFI OPEN_RESETL VERSION_T OPEN_MODE
——— ———– ——— ———-
2337018765 ProdDB01 11-AUG-06 1 11-AUG-06
0 NOARCHIVELOG 5342178
5320190 CURRENT 11-AUG-06 33353 5342178
11-APR-14 NOT ALLOWED 11-AUG-06 READ WRITESQL>
Check if the correct datafiles and the logfiles are being loaded by the Oracle:
SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/opt/dbfiles/oradata/ProdDB01/system01ProdDB01.dbf
/opt/dbfiles/oradata/ProdDB01/rbs01ProdDB01.dbf
/opt/dbfiles/oradata/ProdDB01/temp01ProdDB01.dbf
/opt/dbfiles/oradata/ProdDB01/gapp01.dbf
/opt/dbfiles/oradata/ProdDB01/gapp_ix01.dbf
/opt/dbfiles/oradata/ProdDB01/gblob01.dbf
/opt/dbfiles/oradata/ProdDB01/gsys01.dbf
/opt/dbfiles/oradata/ProdDB01/gsys_ix01.dbf
/opt/dbfiles/oradata/ProdDB01/gapp02ProdDB01.dbf9 rows selected.
SQL>
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/opt/dbfiles/oradata/ProdDB01/log01ProdDB01.dbf
/opt/dbfiles/oradata/ProdDB01/log02ProdDB01.dbf
/opt/dbfiles/oradata/ProdDB01/log03ProdDB01.dbfSQL>
You are now all done with Detaching an in-use DB and attaching a new DB in existing oracle instance at server side.
Now, you need to verify the connectivity to the newly attached database from any of the client devices:
On the server you have essentially:
removed an existing DB (ins02) and Service (ins02) and have
started up the new DB (ProdDB01) and service (ProdDB01) under the same instance ins02.
In order for your clients be able to connect to your new DB, they need to have new entry in TNSNAMES.ORA files:
# TNSNAMES.ORA Network Configuration File: C:oracleora81networkadmintnsnames.ora
# Generated by Oracle configuration tools.ProdDB01.TESTDRIVE.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev.test.lab)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ProdDB01)
)
)
The clients should be able to resolve and connect to the Host specified “dev.test.lab”. If there are issues resolving/connecting to the host, make an hard coded entry in Hosts file.
