Cloning Database without RMAN

Cloning Database without RMAN

Clone Oracle Database :- Many time we need to create a clone of Production Database. Below are the situation where we need to create clone of Production Database

1) To setup UAT/Test Environment
2) To refresh the UAT/Test Environment
3) To Prepare DR server
4) To setup Data-Gaurd Environment

Prerequisites :-Below are the prerequisites for creating clone on UAT/Test server.

1) OS and Version of OS should be same as Production server
2) Database software Version should be same as Production server

Below are the steps to create the clone of Production Database on Destination/UAT/Test Server.

On Production Server :-
1) Generate Create Controlfile Statement

SQL>sqlplus / as sysdba
SQL>alter database backup controlfile to trace as '/home/oracle/control.txt';

2) Select "Create Controlfile" statement from "control.txt" file and make below changes

i)  change REUSE to SET
ii) change NORESETLOGS to RESETLOGS
iii) change ARCHIVELOG to NOARCHIVELOG

Old:-

CREATE CONTROLFILE REUSE DATABASE ORA11G NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
   '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_1_f1kyo3nl_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_2_f1kyo6dk_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_3_f1kyo9wf_.log'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_system_f1kyks9t_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_sysaux_f1kyksv5_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_undotbs1_f1kyksx9_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_users_f1kykt0p_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_example_f1kyp8hj_.dbf'
CHARACTER SET WE8MSWIN1252;

New:-

CREATE CONTROLFILE SET DATABASE ORA11G RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_1_f1kyo3nl_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_2_f1kyo6dk_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_3_f1kyo9wf_.log'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_system_f1kyks9t_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_sysaux_f1kyksv5_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_undotbs1_f1kyksx9_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_users_f1kykt0p_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_example_f1kyp8hj_.dbf'
CHARACTER SET WE8MSWIN1252;

3) Shutdown the database

SQL>shutdown immediate; 

On Destination Server :-

1) Create OS Directories for Data Files, Redo Log Files and Control File 

2) Copy Data Files and Redo Log Files from Production server to UAT server

3) Create Parameter File in $ORACLE_HOME/dbs Folder and save it

$vi initORA11G.ora
db_name=ORA11G
control_files=/u01/app/oracle/oradata/ORA11G/controlfile

:wq (For Saving the File)

3) Startup Database in No-mount stage

$sqlplus sys as sysdba
SQL>startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORA11G.ora';
 
4) Create Control File 

SQL>CREATE CONTROLFILE SET DATABASE ORA11G RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_1_f1kyo3nl_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_2_f1kyo6dk_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/app/oracle/oradata/ORA11G/onlinelog/o1_mf_3_f1kyo9wf_.log'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_system_f1kyks9t_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_sysaux_f1kyksv5_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_undotbs1_f1kyksx9_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_users_f1kykt0p_.dbf',
  '/u01/app/oracle/oradata/ORA11G/datafile/o1_mf_example_f1kyp8hj_.dbf'
CHARACTER SET WE8MSWIN1252;

5) Now Open Database in Mount stage

SQL>Alter database mount;

6) Now open the database with resetlogs mode

SQL>alter database open resetlogs;

Now your UAT environment is cloned with production server.

Extras :-To get the Production Environment details 
select name from v$database;
select name from v$datafile;
select name from v$tempfile;
select name from v$controlfile;
select member from v$logfile;
show parameter spfile;

Environment for above activity :- I have used below environment for same

Production  Server:- 
OS - CentOS7
DB - Oracle-11G
IP - 192.168.81.128

UAT Server :-
OS - CentOS7
DB - Oracle-11G
IP - 192.168.81.129