Cloning (or) Renaming the database

Unix.localhost.com$ echo $ORACLE_SID

ABC

Unix.localhost.com$ echo $ORACLE_HOME

/opt/oracle/u01/app/oracle/product/10.2.0/db_1

Unix.localhost.com$ sqlplus sys/rainbow7 as sysdba

SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='/opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/initABC.ora' nomount;
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2039888 bytes
Variable Size 113254320 bytes
Database Buffers 146800640 bytes
Redo Buffers 6340608 bytes

SQL> alter database mount;
Database altered.

SQL> alter database open;
Database altered.

SQL>alter database backup controlfile to trace as '/opt/oracle/u02/oradata/traceABC.sql';
Database altered.

SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ cd /opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/

$ cp initABC.ora initPROD07.ora

/opt/oracle/u01/app/oracle/admin

$ mv ABC PROD07

$ cd /opt/oracle/u05/

$ mv ABC PROD07

$ cd /opt/oracle/u06/

$ mv ABC PROD07

remove all control files from /u04 ,/u07 ,/u06 folders

From the control file trace /opt/oracle/u02/oradata/traceABC.sql

remove all unwanted space and comments

rename database from aBC to PROD07

REUSE ---> SET

ARCHIVELOG --> NOARCHIVELOG

NORESETLOGS--> RESETLOGS

remove STARTUP NOMOUNT

-----
$ export ORACLE_SID=PROD07

$ export ORACLE_HOME=/opt/oracle/u01/app/oracle/product/10.2.0/db_1

$ echo $ORACLE_SID
PROD07

$ echo $ORACLE_HOME
/opt/oracle/u01/app/oracle/product/10.2.0/db_1

$

$ pwd

/opt/oracle/u01/app/oracle/product/10.2.0/db_1/bin

$ orapwd file=/opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/orapwPROD07 password=rainbow7 entries=10 force=y

$ sqlplus sys/rainbow7 as sysdba

----P0217X sys/manager as sysdba----
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jun 26 09:48:43 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL>startup pfile='/opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD07.ora' nomount;

SQL> select status from v$instance;
STATUS
------------
STARTED

SQL> @/opt/oracle/u02/oradata/traceABC.sql
Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/u07/PROD07/temp01.dbf' SIZE 2050M REUSE AUTOEXTEND OFF;
Tablespace altered.

SQL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>exit

$ sqlplus sys/rainbow7 as sysdba

SQL>startup pfile='/opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD07.ora';
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2039888 bytes
Variable Size 113254320 bytes
Database Buffers 146800640 bytes
Redo Buffers 6340608 bytes
Database mounted.
Database opened.

SQL>create spfile from pfile='/opt/oracle/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD07.ora';
File created.

SQL> select instance_name,host_name from v$instance;
INSTANCE_NAME     HOST_NAME

----------------------     ---------------------------------------------------------
PROD07                       hsun62

SQL>select OWNER, OBJECT_TYPE, count(OBJECT_NAME) from dba_objects group by OWNER, OBJECT_TYPE;

SQL>conn system/sys7tem2
Connected.

SQL>conn apacs/apacs
Connected.

SQL>sys/rainbow7 as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 2039888 bytes
Variable Size 167780272 bytes
Database Buffers 92274688 bytes
Redo Buffers 6340608 bytes
Database mounted.
Database opened.

SQL>select job,what,last_date,last_sec,next_date, next_sec from dba_jobs;