Cold clone Oracle 12c Database steps on Linux

Environment detailed

OS:- Linux7.2 / 64 bits.
Oracle Database:- 12.1.0

                                        Cold clone PROD to UAT environment


What is Database Cloning?
Cloning is a process that can be used to create a copy of the existing Oracle database. Sometimes DBA’s need to clone databases to test backup, development environments and Relocating an database to another machine, and  recovery strategies or export a table that was dropped from the production database and import it back into the production database.
Cold backup Database Cloning is simplest method to clone the database . The disadvantage with this approach is that database will be down during cold backup.
The high level steps for How to clone the database using Cold backup Database Cloning
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora ,then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online


PROD_SID= Airtel (Source) Host_name:-sujeet
UAT_SID= Idea (Target) Host_name:- Sonu
Steps:-1
Login with Source Instance(PROD).
[oracle@sujeet ~]$ sqlplus / as sysdba
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;
INSTANCE_NAME    STATUS
---------------- ------------
Airtel            OPEN

SQL> create pfile from spfile;
File created.

SQL> select name, value From v$parameter Where name='user_dump_dest';
NAME                                                                                    VALUE
--------------------------------------------------------------------------------
user_dump_dest                             /Airtel/product/12.1.0/dbhome_1/rdbms/log

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_system_cmrsn65x_.dbf
/Airtel/oradata/airtel/Airtel/datafile/tfqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_sysaux_cmrslrt4_.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_undotbs1_cmrsopm4_.dbf
/Airteloradata/airtel/Airtel/datafile/rcqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_users_cmrsomgs_.dbf
/Airtel/oradata/airtel/Airtel/datafile/icdata01.dbf
/Airtel/oradata/airtel/Airtel/datafile/icindx01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_3_cmrspswy_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_3_cmrspt1y_.log
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_2_cmrsprnj_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_2_cmrsprs3_.log
/Airtel/oradata/acsqa/Airtel/onlinelog/o1_mf_1_cmrspqcg_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_1_cmrspqjb_.log

6 rows selected.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /Airtel/oradata/airtel/Airtel                                                                                                            /controlfile/o1_mf_cmrspnsv.ctl

Create the script that will re-create the control file:

SQL> alter database backup controlfile to trace;

Database altered.


Steps:-2
Shut down PROD server.
SQL> shutdown immediate
SQL> !

Steps:-3

Copy source PFILE to Target instance.

Go to Pfile location on PROD:-
[oracle@sujeet dbs]$ cd /Airtel/product/12.1.0/dbhome_1/dbs
[oracle@sujeet dbs]$ cp initacsqa.ora /Idea/product/12.1.0/dbhome_1/dbs/

Rename PFILE on Target instance
[oracle@sonu dbs]$ mv initAirtel.ora initIdea.ora
[oracle@sonu dbs]$ ll
-rw-r--r-- 1 oracle oinstall 1002 Dec 29 01:50 initIdea.ora

[oracle@sonu dbs]$ chmod 777 initIdea.ora

Edit Pfile on Target server:-


Idea.__data_transfer_cache_size=0
Idea.__db_cache_size=8388608
Idea.__java_pool_size=16777216
Idea.__large_pool_size=33554432
Idea.__oracle_base='Idea'#ORACLE_BASE set from environment
Idea.__pga_aggregate_target=603979776
Idea.__sga_target=989855744
Idea.__shared_io_pool_size=0
Idea.__shared_pool_size=230686720
Idea.__streams_pool_size=0
*.audit_file_dest='/Idea/admin/idea/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/Idea/oradata/idea/IDEA/controlfile/o1_mf_cmrspnsv_.ctl','/Idea/fast_recovery_area/IDEA/controlfile/o1_mf_cmrspnxf_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/Idea/oradata/idea'
*.db_domain='sonu.net'
*.db_name='Idea'
*.db_recovery_file_dest='/Idea/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/Idea'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=IdeaXDB)'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'


Steps:-4
copy new trace file from PROD to UAT server.
[oracle@sujeet ~]$ cd  /Airtel/product/12.1.0/dbhome_1/rdbms/log
[oracle@sujeet log]$ cp Airtel_ora_4547.trc /Idea/product/12.1.0/dbhome_1/rdbms/log

Rename trace file with .sql format.
[oracle@sonu ]$ cd /Idea/product/12.1.0/dbhome_1/rdbms/log
[oracle@sonu log]$ mv Airtel_ora_4547.trc  Idea_c1.sql

Edit control file created scripts.

[oracle@sonu log]$ vi Idea_c1.sql
CREATE CONTROLFILE SET DATABASE "IDEA" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_1_cmrspqcg_.log' SIZE 50M,
GROUP 2'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_2_cmrsprnj_.log' SIZE 50M,
GROUP 3'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_3_cmrspswy_.log' SIZE 50M,
GROUP 4'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_1_cmrspqjb_.log' SIZE 50M,
GROUP 5'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_2_cmrsprs3_.log' SIZE 50M,
GROUP 6'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_3_cmrspt1y_.log' SIZE 50M
DATAFILE
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_system_cmrsn65x_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/tfqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_sysaux_cmrslrt4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_undotbs1_cmrsopm4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/rcqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_users_cmrsomgs_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icdata01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icindx01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;

:wq <SAVE this file as is it>

Steps:-5
Copy Datafile, Logfile & ORACLE_HOME PROD to UAT Server.
Copy Datafile:-
[oracle@sujeet datafile]$cd /Airtel/oradata/airtel/Airtel/datafile
 [oracle@sujeet datafile]$ cp * /Idea/oradata/idea/IDEA/datafile

 Copy Logfile:-
[oracle@sujeet  onlinelog]$ cd /Airtel/fast_recovery_area/AIRTEL/onlinelog
 [oracle@sujeet onlinelog]$ cp * /Idea/fast_recovery_area/IDEA/onlinelog
[oracle@sujeet ~]$ cd /Airtel/oradata/airtel/AIRTEL/onlinelog
 [oracle@sujeet onlinelog]$ cp * /Idea/oradata/idea/IDEA/onlinelog

Copy ORACLE_HOME:-
[oracle@sujeet ~]$ cd $ORACLE_HOME
[oracle@sujeet dbhome_1 ~]$ cp -r * /Idea/product/12.1.0/dbhome_1

Steps:-6
Set .Bash_Profile on UAT Server.

Steps:-7
[oracle@sonu ~]$ . .bash_profile
[oracle@sonu ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/Idea/product/12.1.0/dbhome_1/dbs/initIdea.ora';
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2923096 bytes
Variable Size             192939432 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes

Steps:-8
Run the re-created control file script on IDEA clone database:
SQL> @Idea_c1.sql
Control file created.
Database altered.

Steps:-9
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;

INSTANCE_NAME    STATUS
---------------- ------------
Idea          OPEN

SQL> create spfile from pfile;

File created.

Check New clone UAT server files location.

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files;

*************************************END******************************************

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...