2.10 Cloning a Database with CloneDB

«« Previous
Next »»

CloneDB enables you to clone a database multiple times without copying the data files into several different locations. Instead, CloneDB uses copy-on-write technology, so that only the blocks that are modified require additional storage on disk.
  • About Cloning a Database with CloneDB
It is often necessary to clone a production database for testing purposes or other purposes.
Common reasons to clone a production database include the following:
  1. Deployment of a new application, or an update of an existing application, that uses the database
  2. A planned operating system upgrade on the system that runs the database
  3. New storage for the database installation
  4. Reporting
  5. Analysis of older data
Before deploying a new application, performing an operating system upgrade, or using new storage, thorough testing is required to ensure that the database works properly under the new conditions. Cloning can be achieved by making copies of the production data files in one or more test environments, but these copies typically require large amounts of storage space to be allocated and managed.

With CloneDB, you can clone a database multiple times without copying the data files into several different locations. Instead, Oracle Database creates the files in the CloneDB database using copy-on-write technology, so that only the blocks that are modified in the CloneDB database require additional storage on disk.

Cloning a database in this way provides the following advantages:

1. It reduces the amount of storage required for testing purposes.
2. It enables the rapid creation of multiple database clones for various purposes.

The CloneDB databases use the data files of a database backup. Using the backup data files ensures that the production data files are not accessed by the CloneDB instances and that the CloneDB instances do not compete for the production database's resources, such as CPU and I/O resources.

The instructions in this chapter describe cloning a non-CDB. You can also clone a pluggable database (PDB) in a CDB using the CREATE PLUGGABLE DATABASE statement. If your underlying file system supports storage snapshots, then you can use the SNAPSHOT COPY clause of the CREATE PLUGGABLE DATABASE statement to clone a PDB using a storage snapshot.

Note:
The CloneDB feature is not intended for performance testing.
  • Cloning a Database with CloneDB
You can clone a database with CloneDB.

Before cloning a database, the following prerequisites must be met:

1. Each CloneDB database must use Direct NFS Client, and the backup of the production database must be located on an NFS volume.

Direct NFS Client enables an Oracle database to access network attached storage (NAS) devices directly, rather than using the operating system kernel NFS client. This CloneDB database feature is available on platforms that support Direct NFS Client.

2. At least 2 MB of additional System Global Area (SGA) memory is required to track the modified blocks in a CloneDB database.

3. Storage for the database backup and for the changed blocks in each CloneDB database is required.

The storage required for the database backup depends on the method used to perform the backup. A single full RMAN backup requires the most storage. Storage snapshots carried out using the features of a storage appliance adhere to the requirements of the storage appliance. A single backup can support multiple CloneDB databases.

The amount of storage required for each CloneDB database depends on the write activity in that database. Every block that is modified requires an available block of storage. Therefore, the total storage requirement depends on the number of blocks modified in the CloneDB database over time.

This section describes the steps required to create one CloneDB database and uses these sample databases and directories:
  1. The Oracle home for the production database PROD1 is /u01/prod1/oracle.
  2. The files for the database backup are in /u02/oracle/backup/prod1.
  3. The Oracle home for CloneDB database CLONE1 is /u03/clone1/oracle.
To clone a database with CloneDB:

1. Create a backup of your production database. You have the following backup options:
  • An online backup
If you perform an online backup, then ensure that your production database is in ARCHIVELOG mode and that all of the necessary archived redo log files are saved and accessible to the CloneDB database environment.
  • A full offline backup
If you perform a full offline backup, then ensure that the backup files are accessible to the CloneDB database environment.
  • A backup that copies the database files
If you specify BACKUP AS COPY in RMAN, then RMAN copies each file as an image copy, which is a bit-for-bit copy of a database file created on disk. Image copies are identical to copies created with operating system commands such as cp on Linux or COPY on Windows, but are recorded in the RMAN repository and so are usable by RMAN. You can use RMAN to make image copies while the database is open. Ensure that the copied database files are accessible to the CloneDB database environment.

2. Create a text initialization parameter file (PFILE) if one does not exist.

If you are using a server parameter file (SPFILE), then run the following statement on the production database to create a PFILE:

CREATE PFILE FROM SPFILE;

3. Create SQL scripts for cloning the production database.

You will use one or more SQL scripts to create a CloneDB database in a later step. To create the SQL scripts, you can either use an Oracle-supplied Perl script called clonedb.pl, or you can create a SQL script manually.

To use the clonedb.pl Perl script, complete the following steps:

A. Set the following environment variables at an operating system prompt:

MASTER_COPY_DIR - Specify the directory that contains the backup created in Step 1. Ensure that this directory contains only the backup of the data files of the production database.

CLONE_FILE_CREATE_DEST - Specify the directory where CloneDB database files will be created, including data files, log files, control files.

CLONEDB_NAME - Specify the name of the CloneDB database.

S7000_TARGET - If the NFS host providing the file system for the backup and the CloneDB database is a Sun Storage 7000, then specify the name of the host. Otherwise, do not set this environment variable. Set this environment variable only if cloning must be done using storage snapshots. You can use S7000 storage arrays for Direct NFS Client without setting this variable.

B. Run the clonedb.pl Perl script.

The script is in the $ORACLE_HOME/rdbms/install directory and has the following syntax:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/install/clonedb.pl 
prod_db_pfile [sql_script1] [sql_script2]
Specify the following options:

prod_db_pfile - Specify the full path of the production database's PFILE.

sql_script1 - Specify a name for the first SQL script generated by clonedb.pl. The default is crtdb.sql.

sql_script2 - Specify a name for the second SQL script generated by clonedb.pl. The default is dbren.sql.

The clonedb.pl script copies the production database's PFILE to the CloneDB database's directory. It also creates two SQL scripts that you will use to create the CloneDB database.

C. Check the two SQL scripts that were generated by the clonedb.pl Perl script, and make changes if necessary.

D. Modify the initialization parameters for the CloneDB database environment, and save the file.

Change any initialization parameter that is specific to the CloneDB database environment, such as parameters that control SGA size, PGA target, the number of CPUs, and so on. The CLONEDB parameter must be set to TRUE, and the initialization parameter file includes this parameter. See Oracle Database Reference for information about initialization parameters.

E. In SQL*Plus, connect to the CloneDB database with SYSDBA administrative privilege.

F. Run the SQL scripts generated by the clonedb.pl Perl script.

For example, if the scripts use the default names, then run the following scripts at the SQL prompt:

crtdb.sql
dbren.sql

To create a SQL script manually, complete the following steps:

A. Connect to the database with SYSDBA or SYSBACKUP administrative privilege.

B. Generate a backup control file script from your production database by completing the following steps:

Run the following SQL statement:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This statement generates a trace file that contains the SQL statements that create the control file. The trace file containing the CREATE CONTROLFILE statement is stored in a directory determined by the DIAGNOSTIC_DEST initialization parameter. Check the database alert log for the name and location of this trace file.

C. Open the trace file generated in Step 3.b, and copy the STARTUP NOMOUNT and CREATE CONTROLFILE statements in the trace file to a new SQL script.

D. Edit the new SQL script you created in Step 3.c in the following ways:

Change the name of the database to the name of the CloneDB database you are creating. For example, change PROD1 to CLONE1.

Change the locations of the log files to a directory in the CloneDB database environment. For example, change/u01/prod1/oracle/dbs/t_log1.f to /u03/clone1/oracle/dbs/t_log1.f.

Change the locations of the data files to the backup location. For example, change /u01/prod1/oracle/dbs/t_db1.f to /u02/oracle/backup/prod1/t_db1.f.

The following is an example of the original statements generated by the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD1" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/prod1/oracle/dbs/t_log1.f'  SIZE 25M BLOCKSIZE 512,
  GROUP 2 '/u01/prod1/oracle/dbs/t_log2.f'  SIZE 25M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/prod1/oracle/dbs/t_db1.f',
  '/u01/prod1/oracle/dbs/t_ax1.f',
  '/u01/prod1/oracle/dbs/t_undo1.f',
  '/u01/prod1/oracle/dbs/t_xdb1.f',
  '/u01/prod1/oracle/dbs/undots.dbf'
CHARACTER SET WE8ISO8859P1
;

The following is an example of the modified statements in the new SQL script:

STARTUP NOMOUNT PFILE=/u03/clone1/oracle/dbs/clone1.ora
CREATE CONTROLFILE REUSE DATABASE "CLONE1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u03/clone1/oracle/dbs/t_log1.f'  SIZE 25M BLOCKSIZE 512,
  GROUP 2 '/u03/clone1/oracle/dbs/t_log2.f'  SIZE 25M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oracle/backup/prod1/t_db1.f',
  '/u02/oracle/backup/prod1/t_ax1.f',
  '/u02/oracle/backup/prod1/t_undo1.f',
  '/u02/oracle/backup/prod1/t_xdb1.f',
  '/u02/oracle/backup/prod1/undots.dbf'
CHARACTER SET WE8ISO8859P1
;

If you have a storage level snapshot taken on a data file, then you can replace the RMAN backup file names with the storage snapshot names.

E. After you edit the SQL script, save it to a location that is accessible to the CloneDB database environment.

Make a note of the name and location of the new SQL script. You will run the script in a subsequent step. In this example, assume the name of the script is create_clonedb1.sql

F. Copy the text initialization parameter file (PFILE) from the production database environment to the CloneDB database environment.

For example, copy the text initialization parameter file from /u01/prod1/oracle/dbs to /u03/clone1/oracle/dbs. The name and location of the file must match the name and location specified in the STARTUP NOMOUNT command in the modified SQL script. In the example in Step 3.d, the file is /u03/clone1/oracle/dbs/clone1.ora.

G. Modify the initialization parameters for the CloneDB database environment, and save the file.

Add the CLONEDB parameter, and ensure that this parameter is set to TRUE. Change any other initialization parameter that is specific to the CloneDB database environment, such as parameters that control SGA size, PGA target, the number of CPUs, and so on. See Oracle Database Reference for information about initialization parameters.

H. In SQL*Plus, connect to the CloneDB database with SYSDBA administrative privilege.

I. Run the SQL script you saved in Step 3.e.

J. For example, enter the following in SQL*Plus:

@create_clonedb1.sql
For each data file in the backup location, run the CLONEDB_RENAMEFILE procedure in the DBMS_DNFS package and specify the appropriate location in the CloneDB database environment.

For example, run the following procedure if the backup data file is /u02/oracle/backup/prod1/t_db1.f and the CloneDB database data file is /u03/clone1/oracle/dbs/t_db1.f:

BEGIN
  DBMS_DNFS.CLONEDB_RENAMEFILE(
    srcfile  => '/u02/oracle/backup/prod1/t_db1.f',
    destfile => '/u03/clone1/oracle/dbs/t_db1.f');
END;
/

4. If you created your CloneDB database from an online backup, then recover the CloneDB database. This step is not required if you performed a full offline backup or a BACKUP AS COPY backup.

For example, run the following SQL statement on the CloneDB database:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

This statement prompts for the archived redo log files for the period when the backup was performed.

5. Open the database by running the following SQL statement:

ALTER DATABASE OPEN RESETLOGS;

The CloneDB database is ready for use.

To create additional CloneDB databases of the production database, repeat Steps 3 - 5 for each CloneDB database.
  • After Cloning a Database with CloneDB
After a CloneDB database is created, you can use it in almost any way you use your production database. Initially, a CloneDB database uses a minimal amount of storage for each data file. Changes to rows in a CloneDB database cause storage space to be allocated on demand.

You can use the same backup files to create multiple CloneDB databases. This backup can be taken either by RMAN or by storage level snapshots. If you have a storage level snapshot taken on a data file, then you can replace the RMAN backup file names with the storage snapshot names.

You can use the V$CLONEDFILE view to show information about each data file in the CloneDB database. This information includes the data file name in the backup, the corresponding data file name in the CloneDB database, the number of blocks read from the backup file, and the number of requests issued against the backup file.

Because CloneDB databases use the backup files as their backend storage, the backup files must be available to each CloneDB database for it to run. If the backup files become unavailable, then the CloneDB databases return errors.

When your use of a CloneDB database is complete, you can destroy the CloneDB database environment. You can delete all of the files in the CloneDB database environment without affecting the production database environment or the backup environment.

Dropping a Database

Dropping a database involves removing its data files, online redo logs, control files, and initialization parameter files

To drop a database:
  • Submit the following statement:
DROP DATABASE;

The DROP DATABASE statement first deletes all control files and all other database files listed in the control file. It then shuts down the database instance.

To use the DROP DATABASE statement successfully, the database must be mounted in exclusive and restricted mode.

The DROP DATABASE statement has no effect on archived redo log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files.

If you used the Database Configuration Assistant to create your database, you can use that tool to delete (drop) your database and remove the files.

Database Data Dictionary Views

You can query data dictionary views for information about your database content and structure.

You can view information about your database content and structure using the following views:

View Description
DATABASE_PROPERTIES Displays permanent database properties
GLOBAL_NAME Displays the global database name
V$DATABASE Contains database information from the control file

«« Previous
Next »»