Coloning Oracle Database

This action will carbon a database application a online archetype of the antecedent database files. Before alpha though, there are a few things that are account acquainted about online/hot backups:

When a tablespace is put into advancement mode, Oracle will address absolute blocks to accommodate rather than the accepted change vectors. For this reason, do not accomplish a hot advancement during periods of abundant database action - it could advance to a lot of annal logs actuality created.

This action will put all tablespaces into advancement approach at the aforementioned time. If the antecedent database is absolutely ample and you anticipate that it ability booty a continued time to copy, accede artful the tablespaces one at a time, or in groups.
While the advancement is in progress, it will not be accessible to booty the tablespaces offline commonly or shut bottomward the instance

1. Make a note of the current archive log change number
Select max(first_change#) SCN from v$archived_log ;

2. Prepare the begin/end backup scripts

SET lines 999
SET pages 999
SET verify off
SET feedback off
SET heading off

spool e:\oracle\beginbackup.sql

SELECT 'alter tablespace ' tablespace_name ' begin backup;' tsbb
FROM dba_tablespaces WHERE contents != 'TEMPORARY'
ORDER BY tablespace_name

spool off

spool e:\oracle\endbackup.sql

SELECT 'alter tablespace ' tablespace_name ' end backup;' desc
FROM dba_tablespaces WHERE contents != 'TEMPORARY'
ORDER BY tablespace_name;

spool off

3. Put the source database into backup mode using beginbackup.sql file. This will put all of the databases tablespaces into backup mode.

SQL> @beginbackup.sql

4. Copy the files to the new location copy, scp or ftp the files from the source database/machine to the target.

5. Take the source database out of backup modeOnce the file copy has been completed, take the source database out of backup mode.

SQL> @endbackup.sql

6. Copy archive logs
It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

ALTER SYSTEM ARCHIVE LOG CURRENT;

Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in

select name from v$archived_logwhere first_change# >= &change_noorder by name

7. 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.

create pfile='init.ora' from spfile;

8. Create the clone controlfile
To do this, connect to the source database and request a dump of the current control file.

alter database backup controlfile to trace as '/home/oracle/createdb.sql'

9. Add a new entry to oratab and source the environment (Only Linux)
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.Source the new environment with '. oraenv' and verify that it has worked by issuing the following
command:

echo $ORACLE_SID

10. Create the a password file
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

11. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba" @/home/oracle/createdb.sql

If all goes to plan you will see the instance start and then the message 'Control file created'.

12. Recover and open the database
The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus: recover database using backup controlfile until cancel
When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:
alter database open resetlogs;

13. Create temp files
create the temp files. Make sure the paths to the file(s) are correct.

14. Perform a few checks

Check that the database has opened with: The status should be 'OPEN'
select status from v$instance;

Make sure that the datafiles are all ok:
select distinct status from v$datafile;

It should return only ONLINE and SYSTEM. Take a quick look at the alert log too.

15. Set the databases global
alter database rename global_name to

16. Create a spfile
create spfile from pfile;

17. 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

shutdown immediate
startup mount

From comand prompt type: 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:

shutdown immediate
startup mount
alter database open resetlogs

18. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora as necessary.