1. Identify and copy the database files
With the source database started, identify all of the database’s files. The following query will display all datafiles, tempfiles and redo logs:
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member “name”, l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/
OR
SQL>Select name from v$datafile;
SQL>Select member from v$logfile;
Make sure that the clone databases file-system is large enough and has all necessary directories.
If the source database has a complex file structure, you might want to consider modifying the
above sql to produce a file copy script.
Stop the source database with:
SQL>shutdown immediate
Copy, scp or ftp the files from the source database/machine to the target.
Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
Start the source database up again
SQL> startup
2. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
SQL> create pfile=’init<new database sid>.ora’ from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.
Once created, the new pfile will need to be edited. If the cloned database is to have a new name,
this will need to be changed, as will any paths. Review the contents of the file and make
alterations as necessary.
Also think about adjusting memory parameters. If you are cloning a production database onto
a slower development machine you might want to consider reducing some values.
Now open the parameter file in clone database and change the following parameters with the respective current location.
CONTROL FILES
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
LOG_ARCHIVE_DEST_1
And Place the clone_DB(new DB) pfile on /$ORACLE_HOME/dbs
Note. Pay particular attention to the control locations.
3. In original database generate CREATECONTROLFILE statement by typing the following command.
SQL>alter database backup controlfile to trace;
This will create a trace file containing the “CREATE CONTROLFILE” command to recreate the controlfile in text form.
4. Now, go to the USER_DUMP_DEST directory on the original Database server and open the latest trace file.The trace file will have the form “ora_NNNN.trc with NNNN being a number. This file will contain steps and as well as CREATE CONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste it in a notepad.
Edit the file
FROM: CREATE CONTROLFILE REUSE DATABASE “olddbname” RESETLOGS …
TO: CREATE CONTROLFILE set DATABASE “newdbname” RESETLOGS …
Change the word ‘REUSE’ to ‘set’ and the ‘olddbname’ to ‘newdbname’. Also change the datafiles location parameter to clone database location.
5. Create the necessary directory on the clone database (destination database) server on your desired location.
Example :- mkdir udump adump cdump bdump arch
udump – user dump destination
bdump – background dump destination
adump – audit dump destination
cdump – core dump destination
arch – Archive log destination
6. Now copy the pfile from the original database server to the clone database server and placed it under the $ORACLE_HOME/dbs location. Now open the parameter file in clone database and change the following parameters with the respective current location.
CONTROL FILES
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
LOG_ARCHIVE_DEST_1
7. In clone database SERVER export ORACLE_SID environment variable and start the instance
$export ORACLE_SID= Clone DB name
$sqlplus
Enter User:/ as sysdba
SQL> startup nomount pfile=’initNEWDB_NAME.ora’;
8. Run create controlfile script to create the controlfile
SQL>@createcontrolfile.sql
9. Trouble shoot:
It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
- ORA-01113: file 1 needs media recoveryYou probably forgot to stop the source database before copying the files.
Go back to step 1 and recopy the files.
- ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: ‘/u03/oradata/dg9a/control01.ctl’
ORA-27038: skgfrcre: file exists
Double check the pfile created in step 2. Make sure the control_files setting
is pointing at the correct location. If the control_file setting is ok, make sure that the control
files were not copied with the rest of the database files. If they were, delete or rename them.
10. Open the database
SQL>alter database open;
11. Perform a few checks
If the last step went smoothly, the database should be open.
It is advisable to perform a few checks at this point:
Check that the database has opened with:
SQL> select status from v$instance;
The status should be ‘OPEN’
Make sure that the datafiles are all ok:
SQL> select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.
12. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
SQL> alter database rename global_name to <new database sid>
/
13. Create a spfile
From sqlplus:
SQL> create spfile from pfile;
14. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed.
If RMAN isn’t going to be used, there is no harm in changing the ID anyway – and it’s a good practice to do so.
From sqlplus:
SQL> shutdown immediate
SQL> startup mount
exit
From unix:
$ nid target=/
NID will ask if you want to change the ID. Respond with ‘Y’. Once it has finished, start the database up again in sqlplus:
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs
/
15. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.
16. Finished
That’s it!
Finny's Oracle Blog 