Invoke SQL*Plus and connect as a user with SYSDBA privileges.

Issue the following command to create a script that will be used to generate your new control file.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

This command generates a trace file that will be stored in the location specified by the USER_DUMP_DEST initialization parameter. The file will be named ORA_nnnn.trc. You can find the value of USER_DUMP_DEST by issuing the following command:

SHOW PARAMETER USER_DUMP_DEST

Copy this file to a file named ccf<NEW_SID>.sql (or other name of your choice) where NEW_SID is the system identifier (ORACLE_SID) of your copied database.

You must identify the data files and redo log files that you need to copy for your new database. You can view the file names in the trace file you created in step 2. Alternatively, you can perform the following queries to obtain the file names:

SELECT name FROM v$datafile;

SELECT member FROM v$logfile;

Shut down your instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:

SHUTDOWN IMMEDIATE

Make a whole (full or complete) backup of your database files. You should include the data files and redo log files you identified in step 3. In addition you should back up the server parameter file, initialization parameter file and any additional parameter files indicated by the IFILE initialization parameter.

Copy your data files, redo log files, control files, and initialization parameter files to the new location. Be sure to preserve the file ownership and privileges.

Change the name of the initialization parameter file to use your new system identifier (ORACLE_SID).

Edit the initialization parameter file for your copied database. Change the value of the CONTROL_FILES parameter to your new control file names. Change the value of the DB_NAME parameter to the database name of your copied database.

Edit the trace file you created in step 2 to make the following changes:

Remove the comment lines and the STARTUP NOMOUNT statement that precede the CREATE CONTROLFILE statement.

Remove the comment lines and the RECOVER DATABASE and ALTER DATABASE OPEN commands that follow the CREATE CONTROLFILE statement.

Add the SET DATABASE clause and change the database name to your new database name as in the following example.
Change:
CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS
To:
CREATE CONTROLFILE REUSE SET DATABASE "DB2" RESETLOGS

Change the data file and redo log file names to the names for your copied database

Note: If you omit any files from the CREATE CONTROLFILE command, these files cannot be added to the new database at a later date. If any of the file names are not changed from their original names in the CREATE CONTROLFILE command, the files of the original database will become part of the copied database and it will not be possible to restore them to the original database. If this happens, the original database will need to be restored from a backup.

If you are using a password file for authentication, you should create a new password file for your copied database using the ORAPWD utility.

orapwd file=orapw<SID> password=<your password> entries=

At the operating system prompt, change the value of the ORACLE_SID environment variable to your new ORACLE_SID. You can use the following command in the C shell:

%setenv ORACLE_SID <NEW_SID>

Invoke SQL*Plus and connect as a user with SYSDBA privileges.

Start your instance in NOMOUNT mode:

STARTUP NOMOUNT PFILE=<pfile name>

Execute your SQL script to create a new control file:

@ccf<new sid>

Open your database with the RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS;

Make a whole (full or complete) backup of your database using operating system commands or RMAN.