RMAN cloning in archive log mode

i have cloned the database SUNU in RAC2 machine to RAC1 Machine in the name DBDEMO.


****************************************************************
RAC2 MACHINE
****************************************************************
ORACLE_SID = [test] ? sunu
[oracle@rac2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 15 19:27:41 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? sunu
[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 15 19:34:50 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: SUNU (DBID=555109830)

RMAN> backup full database format '/home/u01/app/backup/%d_%U.bkp' plus archivelog format '/home/u01/app/backup/%d_%U.bkp';


[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ ls -ltr
total 516356
-rw-r----- 1 oracle oinstall 2124800 May 15 19:37 SUNU_01mcdp74_1_1.bkp
-rw-r----- 1 oracle oinstall 518946816 May 15 19:37 SUNU_02mcdp76_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 15 19:37 SUNU_03mcdp8j_1_1.bkp
-rw-r----- 1 oracle oinstall 2560 May 15 19:37 SUNU_04mcdp8l_1_1.bkp
[oracle@rac2 backup]$



step 2:-
copy the initsunu.ora file to rac1 machine and also create a password file in rac1 machine for dbdemo database
add an entry in oratab file in rac1 machine
also move the backup files to rac1 machine to exact folder as it is in rac2(source) machine

note: make sure the folder is available in RAC1 machine
here we are taking backup in /home/u01/app/backup so now i am going to create a folder in RAC1 machine and also moving the backup from RAC2 to RAC1.
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/backup/


or you can create a symbolic link for the backup location

example, in machine RAC1(the machine/host/server i dont have exact folder or exact mount point as it is in RAC2 machine(target)
do like this,

[oracle@rac2 dbs]$ cd /home/u01/app/backup/
[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/test_bk/

and then create a symbolic link:
[oracle@rac1 app]$ ln -s /home/u01/app/test_bk /home/u01/app/backup

[oracle@rac1 app]$ ls
backup oracle test_bk
[oracle@rac1 app]$ cd backup/
[oracle@rac1 backup]$ ls -ltr
total 671932
-rw-r----- 1 oracle oinstall 7029760 May 22 12:35 SUNU_09mcoc44_1_1.bkp
-rw-r----- 1 oracle oinstall 97460224 May 22 12:36 SUNU_0amcoc48_1_1.bkp
-rw-r----- 1 oracle oinstall 6144 May 22 12:36 SUNU_0cmcoc7a_1_1.bkp
-rw-r----- 1 oracle oinstall 1097728 May 22 12:36 SUNU_0bmcoc78_1_1.bkp
-rw-r----- 1 oracle oinstall 39838720 May 22 12:36 SUNU_0dmcvels_1_1.bkp
-rw-r----- 1 oracle oinstall 534740992 May 22 12:38 SUNU_0emcvem5_1_1.bkp
-rw-r----- 1 oracle oinstall 27648 May 22 12:38 SUNU_0gmcvepg_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 22 12:38 SUNU_0fmcvepe_1_1.bkp
[oracle@rac1 backup]$

now we are going to copy the init files from RAC2 to RAC1 machine using scp , as shown below.
you can mention hostname or ipaddress in scp command

[oracle@rac2 dbs]$ scp initsunu.ora oracle@192.168.1.1:$ORACLE_HOME/dbs/
initsunu.ora 100% 1070 1.0KB/s 00:00
***********************************************************************
IN RAC1 MACHINE
***********************************************************************

i copied the initsunu.ora file to the RAC1 machine and now i am going to replace
the database name sunu by dbdemo
also,
adding the parameter log_file_name_convert and db_file_name_convert parameters to clone database with different directory structure.

dbdemo.__db_cache_size=541065216
dbdemo.__java_pool_size=4194304
dbdemo.__large_pool_size=4194304
dbdemo.__shared_pool_size=192937984
dbdemo.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/dbdemo/adump'
*.background_dump_dest='/home/u01/app/oracle/admin/dbdemo/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/u01/app/oracle/oradata/dbdemo/control01.ctl','/home/u01/app/oracle/oradata/dbdemo/control02.ctl','/home/u01/app/oracle/oradata/dbdemo/control03.ctl'
*.core_dump_dest='/home/u01/app/oracle/admin/dbdemo/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbdemo'
*.db_recovery_file_dest='/home/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbdemoXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=248512512
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/u01/app/oracle/admin/dbdemo/udump'
db_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
log_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
*.sga_target=745537536


Now i am going to add an entry in the /etc/oratab file for dbdemo database

dbdemo:/home/u01/app/oracle/product/10.2.0/db_1:N


going to create required folders for the dbdemo database files

[oracle@rac1 dbdemo]$ pwd
/home/u01/app/oracle/admin/dbdemo

[oracle@rac1 dbdemo]$ ls
adump bdump cdump dpdump hdump pfile udump
[oracle@rac1 dbdemo]$

[oracle@rac1 oradata]$ pwd
/home/u01/app/oracle/oradata
[oracle@rac1 oradata]$ ls
dbdemo
[oracle@rac1 oradata]$


creating a pwd file for dbdemo database

[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
bash: orapwd: command not found
[oracle@rac1 oradata]$ . oraenv
ORACLE_SID = [orcl] ? dbdemo
[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
[oracle@rac1 oradata]$

now i am going to start dbdemo database in nomount mode
and then do cloning

before that we should have add the tns entry of sunu database in RAC1 machine


[oracle@rac1 backup]$ cd /home/u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ ls -ltr

add/check this entries available in both machines else add this entry in tnsnames.ora file of both machines.

SUNU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sunu)
)
)

DBDEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdemo)
)
)
[oracle@rac1 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:21

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (0 msec)
[oracle@rac1 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac1 admin]$

[oracle@rac2 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:09

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (10 msec)
[oracle@rac2 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:15

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac2 admin]$

we are checking connectivity b/n the two machines using tnsping command.

now all the pre cloning steps finished.

now we can start the cloning in RAC1 machine.


startup nomount the DATABASE dbdemo in RAC1 machine


making/creating flash recovery area in RAC1 machine

[oracle@rac1 oracle]$ mkdir -p /home/u01/app/oracle/flash_recovery_area
[oracle@rac1 oracle]$ ls
admin flash_recovery_area oradata oraInventory product
[oracle@rac1 oracle]$ cd flash_recovery_area/
[oracle@rac1 flash_recovery_area]$ ls
[oracle@rac1 flash_recovery_area]$ mkdir dbdemo
[oracle@rac1 flash_recovery_area]$ ls
dbdemo


[oracle@rac1 dbs]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:40:50 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 201329660 bytes
Database Buffers 541065216 bytes
Redo Buffers 2969600 bytes
SQL>

[oracle@rac1 flash_recovery_area]$ . oraenv
ORACLE_SID = [ORCL1] ? dbdemo
[oracle@rac1 flash_recovery_area]$ rman target sys/oracle@SUNU auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 19 20:42:06 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: SUNU (DBID=555109830)
connected to auxiliary database: DBDEMO (not mounted)

RMAN> duplicate target database to dbdemo;

[oracle@rac1 dbdemo]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbdemo]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 21:20:23 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
DBDEMO

changing database dbid

SQL> startup mount
ORACLE instance started.

Total System Global Area 481267712 bytes
Fixed Size 1300716 bytes
Variable Size 226494228 bytes
Database Buffers 247463936 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [demo2] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac1 ~]$ nid target = /

DBNEWID: Release 11.1.0.6.0 - Production on Thu Dec 24 20:05:44 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database DEMO2 (DBID=3682169720)

Connected to server version 11.1.0

Control Files in database:
/u01/app/oracle/oradata/demo2/control01.ctl
/u01/app/oracle/oradata/demo2/control02.ctl
/u01/app/oracle/oradata/demo2/control03.ctl

Change database ID of database DEMO2? (Y/[N]) => y

Proceeding with operation
Changing database ID from 3682169720 to 3682222232
Control File /u01/app/oracle/oradata/demo2/control01.ctl - modified
Control File /u01/app/oracle/oradata/demo2/control02.ctl - modified
Control File /u01/app/oracle/oradata/demo2/control03.ctl - modified
Datafile /u01/app/oracle/oradata/demo2/system01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/sysaux01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/undotbs01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/users01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/demo2/temp01.dbf - dbid changed
Control File /u01/app/oracle/oradata/demo2/control01.ctl - dbid changed
Control File /u01/app/oracle/oradata/demo2/control02.ctl - dbid changed
Control File /u01/app/oracle/oradata/demo2/control03.ctl - dbid changed
Instance shut down

Database ID for database DEMO2 changed to 3682222232.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@rac1 ~]$
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

DBID
----------
3682222232

regards,
rajeshkumar g

clone database using Hot backup

Clone an Oracle database using an online/hot backup

This procedure will clone a database using a online copy of the source database files.

PART I
You have to follow steps 1 to step 8 is a prerequisite steps for creating clone database.

* 1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:

SQL> select max(first_change#) chng
from v$archived_log
/

* 2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

SQL> spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
SQL> spool off

SQL>spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
SQL>spool off

* 3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:

SQL> @begin_backup

This will put all of the databases tablespaces into backup mode.

* 4. Copy the files to the new location
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.

* 5. Take the source database out of backup mode
Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:

SQL>@end_backup

* 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:

SQL> 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 step 1.

SQL> select name
from v$archived_log
where first_change# >= &change_no
order by name
/

Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

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

From sqlplus:

SQL> create pfile='/u01/admin/clone/pfile/init.ora' from spfile;

This will create a new pfile in the /u01/admin/clone/pfile 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.

Ensure that the archive log destination is pointing to the directory created in step 6.

* 8. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

SQL> alter database backup controlfile to trace;

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

o Remove all lines from the top of the file including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

o Remove any lines that start with --

o Remove any lines that start with a #

o Remove any blank lines in the 'CREATE CONTROLFILE' section.

o Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

o Remove the line 'ALTER DATABASE OPEN RESETLOGS;'

o Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.

o Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

o If the file paths are being changed, alter the file to reflect the changes.

Here is an example of how the file would look for a small database called CLONE which isn't in archivelog mode:

CREATE CONTROLFILE set DATABASE "clone" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/product/10.2.0/oradata/clone/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/product/10.2.0/oradata/clone/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/product/10.2.0/oradata/clone/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/product/10.2.0/oradata/clone/system01.dbf',
'/u01/app/oracle/product/10.2.0/oradata/clone/undotbs01.dbf',
'/u01/app/oracle/product/10.2.0/oradata/clone/sysaux01.dbf',
'/u01/app/oracle/product/10.2.0/oradata/clone/users01.dbf',
'/u01/app/oracle/product/10.2.0/oradata/clone/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
IMPORTANT: SAVE THIS FILE IN YOUR HOME DIRECTORY AS dbclone.sql

PART II
This are the steps to launch clone database
* 9. Add a new entry to oratab and source the environment
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

If this doesn't output the new database sid go back and investigate.

* 10. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):

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:
open new terminal and follow the steps:
[oracle@localhost ~]$ ORACLE_SID=clone
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 12 18:54:56 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/u01/admin/clone/pfile/init.ora

SQL> @/home/oracle/dbclone.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:

SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 542839 generated at 05/12/2009 02:38:29 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/CLONE/archivelog/2008_11_
25/o1_mf_1_3_%u_.arc
ORA-00280: change 542839 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
IMPORTANT:You need to give the archived log name here and then say "Cancel" and open the database

/u01/app/oracle/oradata/CLONE/o1_mf_1_3_4lqgzpsg_.arc
ORA-00279: change 543072 generated at 11/25/2008 02:48:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/CLONE/archivelog/2008_11_
25/o1_mf_1_4_%u_.arc
ORA-00280: change 543072 for thread 1 is in sequence #4
ORA-00278: log file '/u01/app/oracle/oradata/CLONE/o1_mf_1_3_4lqgzpsg_.arc' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/CLONE/o1_mf_1_4_4lqgzsbq_.arc
ORA-00279: change 543074 generated at 11/25/2008 02:48:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/CLONE/archivelog/2008_11_
25/o1_mf_1_5_%u_.arc
ORA-00280: change 543074 for thread 1 is in sequence #5
ORA-00278: log file '/u01/app/oracle/oradata/CLONE/o1_mf_1_4_4lqgzsbq_.arc' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

AND THEN MAKE YOUR DATABASE TO ARCHIVELOG MODE

* 13. Create temp files
Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

* 14. Perform a few checks
If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

o Check that the database has opened with:

SQL>select status from v$instance;

The status should be 'OPEN'

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

It should return only ONLINE and SYSTEM.

o Take a quick look at the alert log too.

* 15. 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
/

Note. no quotes!

* 16. Create a spfile
From sqlplus:

SQL>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 - and it's a good practice to do so.

From sqlplus:

SQL>shutdown immediate
SQL>startup mount
SQL>exit

From unix and linux:

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
/

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

* 19. Finished
That's it!

Original documentation prepard by shutdownabort.com, Andrew Barry, I have made some changes in that documentation for easy understanding with the help of ASKDBA ORACLE FEED forum.
for further reference and more information:
http://www.shutdownabort.com/quickguides/clone_hot.php
http://askdba.org/forum/topic.php?id=18

clone database in oracle

cloning or refreshing or renaming oracle database
A database clone is a complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.
The cloning refers to the replication of the server in order to have a backup, to upgrade the environment.

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue:
SQL>alter database backup controlfile to trace;

This will put the create database syntax in the trace file directory(UDUMP FOLDER, last created trace file in user trace files in udump folder). The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u01/oradata/oldlsq/log1a.dbf',
'/u01/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u01/oradata/oldlsq/log2a.dbf',
'/u01/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

note:you have to change the yellow marked words thats why i marked those words in yellow.

STEP 2: Shutdown the old database

SQL>shut immediate;

STEP 3: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

open the dbclone_controlfile_creation.sql in a notepad or text editor and change the below yellow lines to green lines

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 4: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 5: Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.
change the yellow marked lines to green marked lines in dbclone_controlfile_creation.sql
Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

otherwise create newfolder for clone database and copy and paste all datafiles and redolog files from primary database folder to the clone database destination folder.

/u01/oradata/oldlsq folder to /u01/oradata/newlsq folder.

STEP 7: Create the bdump, udump and cdump directories

cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile


STEP 8: Copy-over the old init.ora file

open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations
cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

STEP 9: Start the new database

SQL>startup nomount pfile=' /u01/admin/newlsq/pfile/newlsq.ora ' ;

SQL> @dbclone_controlfile_creation.sql

SQL>alter database open resetlogs;

SQL>create spfile from pfile;

STEP 10: Place the new database in archivelog mode
for enabling archivelog mode refer the following link:
http://rajeshkumar-oracledba.blogspot.com/2008_10_01_archive.html

thanks for reading this documentation and visiting this blog

references:
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://sabdarsyed.blogspot.com/2008/08/cloning-and-refreshing-oracle-database.html
http://www.sap-img.com/oracle-database/cloning-database-from-hot-backup.htm
http://www.dba-oracle.com/oracle_news/2004_12_14_webb.htm
http://www.club-oracle.com/forums/steps-to-clone-oracle-applications-database-from-prod-to-test-t10/s-to-clone-oracle-applications-database-from-prod-to-test-t10/https://students.kiv.zcu.cz/doc/oracle/em.102/b16227/oui7_cloning.htm
http://www.builderau.com.au/strategy/soa/Oracle-Tip-Use-DBCA-to-clone-a-database/0,2000064882,339130378,00.htm-Use-DBCA-to-clone-a-database/0,2000064882,339130378,00.htm
http://www.pythian.com/news/1041/oracle-silent-mode-part-3-cloning-software-and-databases
http://beyondoracle.wordpress.com/2009/01/07/move-clone-copy-database-with-rman/
http://download-west.oracle.com/docs/cd/B19306_01/em.102/b16227/oui7_cloning.htm
http://www.my-whiteboard.com/oracle-dba/use-rman-duplicate-to-restore-oracle-database-to-another-server.html
http://www.shutdownabort.com/quickguides/clone_hot.php