Easy Clone Oracle Database

Description:-

In this article we are going to see Fast cloning of your Oracle database.

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

This 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 Steps will support all the version and Same Server or Different server

OLDDB NAME:-TEST12C

NEWDB NAME:-TEST12CD

Step:- 1 Take controlfile trace

SQL> alter database backup controlfile to trace as ‘/home/oracle/cont.sql’;
Database altered.

SQL> select name from v$database;

NAME
———
TEST12C

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/oracle/archivelogs/TEST12C
Oldest online log sequence 28782
Next log sequence to archive 28784
Current log sequence 28784
SQL> select name from v$datafile;

NAME
——————————————————————————–
/home1/oracle/oradata/TEST12C/system01.dbf
/home1/oracle/oradata/TEST12C/sysaux01.dbf
/home1/oracle/oradata/TEST12C/undotbs01.dbf
/home1/oracle/oradata/TEST12C/idx01.dbf
/home1/oracle/oradata/TEST12C/users01.dbf

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
/home1/oracle/oradata/TEST12C/redo03.log
/home1/oracle/oradata/TEST12C/redo02.log
/home1/oracle/oradata/TEST12C/redo01.log

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/home1/oracle/oradata/TEST12C/control01.ctl
/home1/oracle/oradata/TEST12C/control02.ctl

SQL> select sum(bytes)/1024/1024/1024 size_in_mb from dba_data_files;

SIZE_IN_MB
———-
45.0646973

STEP 2: Shutdown the old database

[oracle@testv12db ~]$ . oraenv
ORACLE_SID = [TEST12C] ? TEST12C
The Oracle base remains unchanged with value /home/oracle/app/oracle
[oracle@testv12db ~]$ sqlplus / as sysdba

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

NEW DATABASE :- TEST12CD

Step:-3 Create the directories

[oracle@testv12db ~]$ mkdir -p /home1/oracle/oradata/TEST12CD
[oracle@testv12db ~]$ mkdir -p /backup/oracle/archivelogs/TEST12CD
[oracle@testv12db ~]$ mkdir -p /home/oracle/app/oracle/admin/TEST12CD/adump

STEP 4: Copy all data files into the new directories on the 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 server.

[oracle@testv12db ~]$ cd /home1/oracle/oradata/
[oracle@testv12db oradata]$ ls -lrt
total 28
drwxrwxr-x 2 oracle oracle 4096 Aug 14 2018 TEST12C
drwxrwxr-x 2 oracle oracle 4096 Aug 29 13:10 TEST12CDB
[oracle@testv12db oradata]$ cd TEST12C
[oracle@testv12db TEST12C]$ ll
total 80311096
-rw-rw—- 1 oracle oracle 13549568 Aug 29 13:09 control01.ctl
-rw-rw—- 1 oracle oracle 13549568 Aug 29 13:09 control02.ctl
-rw-rw—- 1 oracle oracle 5242888192 Aug 29 13:09 idx01.dbf
-rw-rw—- 1 oracle oracle 104858112 Aug 29 13:00 redo01.log
-rw-rw—- 1 oracle oracle 104858112 Aug 29 13:09 redo02.log
-rw-rw—- 1 oracle oracle 104858112 Aug 29 05:40 redo03.log
-rw-rw—- 1 oracle oracle 2212503552 Aug 29 13:09 sysaux01.dbf
-rw-rw—- 1 oracle oracle 4057997312 Aug 29 13:09 system01.dbf
-rw-rw—- 1 oracle oracle 34358697984 Aug 29 08:01 temp01.dbf
-rw-rw—- 1 oracle oracle 12907978752 Aug 29 13:09 undotbs01.dbf
-rw-rw—- 1 oracle oracle 23966523392 Aug 29 13:09 users01.dbf

[oracle@testv12db TEST12C]$ cp -rp *.dbf* /home1/oracle/oradata/TEST12CD

Step:-5 Add oratab entries

Before

[oracle@testv12db dbs]$ cat /etc/oratab
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
# Multiple entries with the same $ORACLE_SID are not allowed.
TEST12C:/home/oracle/app/oracle/product/12.1.0.2/oracle:Y

After

[oracle@testv12db dbs]$ vi /etc/oratab

Step:-6 Copy old init.ora file and Edit the file for New database

cp $ORALCE_HOME/dbs/initTEST12C.ora initTEST12CD.ora

[oracle@testv12db dbs]$ cat initTEST12CD.ora

*._fix_control=’17376322:OFF’
*.audit_file_dest=’/home/oracle/app/oracle/admin/TEST12CD/adump‘
*.audit_trail=’db’
*.compatible=’12.1.0.2.0′
*.control_file_record_keep_time=30
*.control_files=’/home1/oracle/oradata/TEST12CD/control01.ctl’,’/home1/oracle/oradata/TEST12CD/control02.ctl’
*.control_management_pack_access=’diagnostic+tuning’
*.db_block_size=8192
*.db_domain=”
*.db_name=’TEST12CD‘
*.db_recovery_file_dest=’/home/oracle/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest=’/home/oracle/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TEST12CXDB)’
*.log_archive_dest_1=’LOCATION=/backup/oracle/archivelogs/TEST12CD‘
*.open_cursors=700
*.pga_aggregate_target=1600m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.sga_target=4800m
*.undo_tablespace=’UNDOTBS1′

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

SQL> alter database backup controlfile to trace as ‘/home/oracle/cont.sql’;

Open cont.sql edit as below

Old:

CREATE CONTROLFILE REUSE DATABASE “TEST12C” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “TEST12CD” RESETLOGS

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE “TEST12CD” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2336
LOGFILE
GROUP 1 ‘/home1/oracle/oradata/TEST12CD/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/home1/oracle/oradata/TEST12CD/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/home1/oracle/oradata/TEST12CD/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/home1/oracle/oradata/TEST12CD/system01.dbf’,
‘/home1/oracle/oradata/TEST12CD/sysaux01.dbf’,
‘/home1/oracle/oradata/TEST12CD/undotbs01.dbf’,
‘/home1/oracle/oradata/TEST12CD/idx01.dbf’,
‘/home1/oracle/oradata/TEST12CD/users01.dbf’
CHARACTER SET WE8MSWIN1252;

Step:-8 Start the new database

[oracle@testv12db ~]$ . oraenv
ORACLE_SID = [TEST12CD] ? TEST12CD
The Oracle base remains unchanged with value /home/oracle/app/oracle
[oracle@testv12db ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 29 14:02:43 2020

Connected to an idle instance.

SQL> STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE “TEST12CD” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2336
LOGFILE
GROUP 1 ‘/home1/oracle/oradata/TEST12CD/redo01.log’ SIZE 100M BLOCKSIZE 512,
GROUP 2 ‘/home1/oracle/oradata/TEST12CD/redo02.log’ SIZE 100M BLOCKSIZE 512,
GROUP 3 ‘/home1/oracle/oradata/TEST12CD/redo03.log’ SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/home1/oracle/oradata/TEST12CD/system01.dbf’,
‘/home1/oracle/oradata/TEST12CD/sysaux01.dbf’,
‘/home1/oracle/oradata/TEST12CD/undotbs01.dbf’,
‘/home1/oracle/oradata/TEST12CD/idx01.dbf’,
‘/home1/oracle/oradata/TEST12CD/users01.dbf’
CHARACTER SET WE8MSWIN1252
;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 5033164800 bytes
Fixed Size 2934744 bytes
Variable Size 1040189480 bytes
Database Buffers 3976200192 bytes
Redo Buffers 13840384 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Control file created.

Step:- 9 Open the database

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> alter database open RESETLOGS;

Database altered.

SQL> select status,instance_name from v$instance;

STATUS INSTANCE_NAME
———— —————-
OPEN TEST12CD

Step:10 add the database name in Tnsnames.ora file and listener

Connect with me:-

Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr

Recent Posts

Get updated

Easy Clone Oracle Database

Previous Post

On-Premise Oracle 19c Database Cloning Using RMAN From Oci Object Storage

View Post

Next Post

Apply Patching On Oracle 19c Database Release Update 19.8.0.0.200714

View Post

Recent Posts

Get updated