Cloning Oracle Database

CPU intensive reports should be generated to another database than the production database. For such type of cases we need to create another db for processing purpose. As the effects the performance negatively in the production server.

1.set ORACLE_SID='dbname'

2.sqlplus / as sysdba

3.SQL> Select name from v$database;
>> dbname
4.Select log_mode from v$database;

5. in case of archive log we will get >> ARCHIVELOG

6.SQL> archive log list

7. For the cloning purpose, we need to create the DB folder in the corresponding location as per the instance. Let assume we want to clone the database in the same server with the name dbname2, For this reason, we will create a folder with this name in the oradata folder , inside this directory we will create the ARCHIVED_LOGS directory also.

8. Then in the oracle_home location ..\db_1\database make a file names initdbname2.ora following the another init file. The content of the file should be db_name=dbname2 [as per our example]

9. Now we need to make a instance for this newly created database.
oradim -new -sid dbname2
net start OracleServicedbname2
[For windows environment; not for Unix or Linux Enviroment]

10. Now again after going back to my main databse,
SQL> Selct * from v$backup;
SQL> select tablespace_name from dba_tablespaces;

11. To make the main db to backupmode:
SQL> alter database begin backup;

12. now after executing the below query:
      SQL> Select * from v$backup;
      we get the status of the backup in the active mode

13. Now going to the file location of the database oradata\dbname folder
      cp *dbf /usr/home/usrname/filelocation

14. now again through sqlplus we need to stop the back mode of the main database:
     SQL> alter database end backup;
     after that for checking we can use Select * from v$backup; which will indicate ‘Not Active’

15. SQL> alter system archived log current

16. SQL> alter system backup controfile to trace as ‘/usr/home/usrname/filelocation/trace.sql’ reuse;

17. Now in the trace.sql file, we need to change the dbname by dbname2.
    and CREATE CONTROLFILE REUSE DATABASE dbname NORESETLOGS should be        
    changed as REATE CONTROLFILE SET DATABASE dbname RESETLOGS

18. Now we can copy the already copied database to dbname2 file location
      cp /usr/home/usrname/filelocation/* to .
19. Now we will access dname2 by sqlplus
SQL> set ORALCE_SID=dbname2
SQL> sqlplus / as sysdba
SQL>@/usr/home/usrname/filelocation/trace.sql;

database will be started successfully. No we can get all the database of dbname in dbname2
SQL> Select name from v$database;
SQL> alter system archive log current;

now we need to take the archive log of the dbname for dbname2 for recover the database.
SQL> set log source ‘arhive log direcotry of dbname’
SQL> recover database using backup control file until cancel;
SQL> alter database open reset logs;
SQL> Select Open_Mode from v$database;