EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Clone Database
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Clone Database

By Priya PedamkarPriya Pedamkar

Oracle Clone Database

Introduction to Oracle CLONE Database

Oracle Database Cloning is nothing but a procedure that is used to create an identical database of the existing Oracle database. The cloning technique is used to make a copy of the existing Database to perform various test activities by DBA like backup and recovery. In this topic, we are going to learn about Oracle Clone Database.

  • DBA performs Oracle database cloning to test backup and recovery strategies.
  • This technique uses to export Oracle object that gets dropped somehow from the production database.
  • Cloning can be performed separately or on the same host.

Why Cloning Needed?

  • To perform a backup and recovery strategy.
  • To keep a backup of the production database.
  • That copy of the database can be used for development.
  • That copy of the database can be used for production testing, beta testing, etc.
  • This technique is used to take a copy of the database before renaming that.
  • It is used for relocating an Oracle database to another machine.
  • This technique is very useful for moving the Oracle database to new storage media.
  • Oracle cloning is often the fastest way to make a copy of an existing Oracle database.
  • Oracle cloning procedure mostly used by DBA to provide a full-sized DEV instance to the developers.

Types of Oracle Cloning

There are three methods to perform Oracle Database Cloning. Those are listed below.

  • Cold Cloning
  • Host Cloning
  • RMAN Cloning

We’ll go ahead with a brief explanation of how cloning operations can be performed in all these three methods.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,408 ratings)

1. COLD Cloning

It is a reliable method to perform Oracle Database Cloning. To perform Database Cloning, require database information as given below: Here file name and path is assumed, it can vary user to use depends on the installation.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD/oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Start the source database

export ORACLE_SID=PROD
sqlplus / as sysdba
startup

2. Find the path and names of datafiles, control files, and redo log files.

select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

3. Take the control file backup.

alter database backup controlfile to trace;

4. Parameter file backup.

If ‘PROD’ database is using spfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

If the database is using pfile, use OS command to copy the pfile to a backup location.

5. Shutdown the ‘PROD’ database

shutdown

6. Copy all data files, control files, and redo log files of the ‘PROD’ database to a target database location.

mkdir /u02/PRODCLON/oradata
cp /u01/PROD/oradata/* /u02/PRODCLON/oradata/

7. Create an appropriate directory structure in the clone database for dumps and specify them in the parameter file.

mkdir -p /u02/PRODCLON/{bdump,udump}

8. Edit the clone database parameter file and make necessary changes to the clone database

cd /u02/PRODCLON/
vi initPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/udump
. . .
. . .
:wq!

9. Start the clone database in NOMOUNT stage.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’

10. Create the control file trace for the clone database using the trace control file and specify the paths for redolog and datafiles.

CREATE CONTROLFILE SET DATABASE “PRODCLON” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u02/PRODCLON/oradata/redo01.log’ SIZE 5M,
GROUP 2 ‘/u02/PRODCLON/oradata/redo02.log’ SIZE 5M,
DATAFILE
‘/u02/PRODCLON/oradata/system01.dbf’,
‘/u02/PRODCLON/oradata/undotbs01.dbf’,
‘/u02/PRODCLON/oradata/sysaux01.dbf’,
‘/u02/PRODCLON/oradata/users01.dbf’,
‘/u02/PRODCLON/oradata/example01.dbf’
CHARACTER SET AL32UTF8

11. Create the control file by running from the trace path

@u01/PROD/source/udump/cntrl.sql

12. Once the control file’s successfully created, open the database with resetlogs option.

alter database open resetlogs;

2. HOT Cloning

Hot Cloning method is more suitable for a 24*7*365 running database. To apply this method for database cloning, the Database has to be in archive log mode, and this method, no need to shut down the database.

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD /oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Find the path and names of data files.

select name from v$datafile;

2. Backup the parameter file

If ‘PROD’ database is using spfile create pfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

If the database is using pfile, use OS command to copy the pfile to a backup location.

3. Note down the oldest log sequence number.

alter system switch logfile;
archive log list;

4. Place the database to backup mode

alter database begin backup;

5. Copy all data files of the ‘PROD’ database to a clone location.

mkdir /u02/PRODCLON/oradata
cp /u01/PROD/source/oradata/*.dbf /u02/PRODCLON/oradata/

6. After copying all datafiles, release the database from the backup mode.

alter database end backup;

7. Switch the current log file and note down the oldest log sequence number

alter system switch logfile;
archive log list;

8. Copy all archive log files generated during the first old log sequence no.to the LAST old log sequence no.

9. Take the control file trace backup to the trace path

alter database backup controlfile to trace;

10. Create a directory structure for the clone database and specify the same

cd /u02/PRODCLON
mkdirbdumpudump

11. Edit the clone database parameter file and make necessary changes to the clone database

cd /u02/PRODCLON
vi initPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/udump
. . .
. . .
:wq!

12. Start the clone database in NOMOUNT phase.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’

13. Create the control file for the clone database using the trace control file.

CREATE CONTROLFILE SET DATABASE “PRODCLON” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u02/PRODCLON/oradata/redo01.log’ SIZE 5M,
GROUP 2 ‘/u02/PRODCLON/oradata/redo02.log’ SIZE 5M,
DATAFILE
‘/u02/PRODCLON/oradata/system01.dbf’,
‘/u02/PRODCLON/oradata/undotbs01.dbf’,
‘/u02/PRODCLON/oradata/sysaux01.dbf’,
‘/u02/PRODCLON/oradata/users01.dbf’,
‘/u02/PRODCLON/oradata/example01.dbf’
CHARACTER SET AL32UTF8;

14. Create the control file by running trace file from the trace path

@u01/PROD/source/udump/cntrl.sql

15. Recover the database using a backup controlfile option.

recover database using backup controlfile until cancel;

16. You will be prompted to feed the archive log files henceforth. Specify the absolute path and file name for the archive log files and keep feeding them until you cross the LAST old sequence no. (Refer: Step 8), type CANCEL to end the media recovery.

17. Open the database with resetlogs option.

alter database open resetlogs;

3. RMAN Cloning

There is a DUPLICATE command that is provided by RMAN and that is used for backup of the database to create the clone database. This command restores files to the target database after which an incomplete recovery is performed and the clone database is opened using the RESETLOGS option.

  • Source Database Name: PROD
  • Source Database Physical File Path:/u01/PROD /oradata
  • Clone Database Name: PRODCLON
  • Clone Database Physical File Path:/u01/PRODCLON/oradata

Steps:

1. Parameter file backup.

If ‘PROD’ database is using spfile,

create pfile=’/u02/PRODCLON/initPRODCLON.ora’ from spfile;

If the database is using pfile, use OS command to copy the pfile to a backup location.

2. Create an appropriate directory structure for the clone database

cd /u02/PRODCLON
mkdirbdumpudump

3. Edit the clone database parameter file

cd /u02/PRODCLON
viinitPRODCLON.ora
db_name=PRODCLON
control_files=/u02/PRODCLON/oradata/cntrl01.ctl
db_file_name_convert=(‘/u01/PROD/oradata’,’/u02/PRODCLON/oradata’)

This parameter specifies from where to where the datafiles should be cloned

log_file_name_convert=(‘/u01/PROD/oradata’,’/u02/PRODCLON/oradata’)

This parameter specifies from where to where the redologfiles should be cloned

background_dump_dest=/u02/PRODCLON/bdump
user_dump_dest=/u02/PRODCLON/udump
. . .
. . .
:wq!

Note: db_file_name_convert and log_file_name_convert parameters are required only if the source database directory structure and clone database directory structure differs.

4. Configure the listener using ‘listener.ora’ file and start the listener

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1/)
(SID_NAME =PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = PRODCLON)
(ORACLE_HOME = /u02/oracle/product/10.2.0/db_1/)
(SID_NAME =PRODCLON)
)
)

5. Add the following information to the ‘tnsnames.ora’ file.

con_PRODCLON=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.168.1.22)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODCLON)
)
)

6. Startup the database in NOMOUNT stage and exit.

export ORACLE_SID=PRODCLON
startup nomount pfile=’/u02/PRODCLON/initPRODCLON.ora’
exit

7. Start RMAN, make ‘PROD’ as a target, and ‘PRODCLON’ as auxiliary.

export ORACLE_SID=PROD
rman target / auxiliary sys/sys@con_PRODCLON

8. Issue the RMAN DUPLICATE command to start the cloning process.

RMAN> duplicate target database to ‘PRODCLON’;

Note: The preceding command restores all files from the backup of the target database to the clone database destination using all available archive log files.

Conclusion – Oracle Clone Database

Oracle Cloning is a very useful procedure to take the existing database back up. Administrators do not need to connect to the source database while performing database cloning and that reduces the chances of impact on the production system.

Recommended Articles

This is a guide to Oracle Clone Database. Here we discuss why cloning is needed and 3 types of Oracle Cloning along with the respective steps. You may also have a look at the following articles to learn more –

  1. Oracle COALESCE
  2. Oracle CTE
  3. Oracle REGEXP_REPLACE
  4. PIVOT in Oracle
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more