Database Cloning Steps

Cloning Steps:Case 1

Database cloning is a procedure by which you can create an exact copy of an Oracle database without doing a full export/import. This method is used frequently by DBAs to update test or development environments from production.

As a rule, testing and development should not be done on your production database. That much is common sense. Depending on the resources available to companies, some have separate test and development databases and many roll testing and development into a single database. Now, the developers need up-to-date data for testing purposes, and it is the DBA who provides this to them. There are a few ways to do this. The first one that comes to mind is, of course, export and import. But this is cumbersome if you have to do the data refresh frequently and import takes a long time if your database is of good size.   You can of course, resort to exporting and importing tablespaces if they are self contained and you are running version 8i or greater, enterprise edition. A faster alternative is to clone the database.

Clone means an exact copy. That is, you will copy the very datafiles from your production database and use it to create your test database. So your test database will be a mirror image of the production in all respects, except of course, for the database name. We will be changing that.   The method used here is actually a backup/recovery of the production database on the test server. We just recreate the controlfile on the test machine using a new database name and do a recovery. The datafiles from the production database can be from a hot backup, a cold backup or an RMAN backup. If the database was open during backup (hot or RMAN), you will need all the archivelogs since the time the backup started to recover the database to its present state (that is, do a complete recovery). Throughout the article, I will refer to environment variables by their UNIX notation (example $ORACLE_HOME). Replace this with the Windows equivalent if you are using NT/2000 (example $ORACLE_HOME becomes %ORACLE_HOME%).

Reason for Cloning:

In every oracle development and production environment there will become the need to transport the entire database from one physical machine to another.   This copy may be used for development, production testing, beta testing, etc, but rest assured that this need will arise and management will ask you to perform this task quickly.   Listed below are the most typical uses:

1.  Relocating an Oracle database to another machine.

2.  Moving Oracle database to new Storage media.

3.  Renaming Oracle database.

Steps:

Clone an Oracle Database using Cold Physical Backup

Database Name: troy

Source Database side: (Troy database)

Cold Backup Steps:

1. Get the file path information using below query

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

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Taken the control file backup
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

4.Shutdown immediate

5.Copy all the data files/log files using os command & placed in backup path.

6.Startup the database.

Clone Database side: (Clone database)

Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc…

3.Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.

5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change ‘REUSE’ needs to be changed to ‘SET’.

CREATE CONTROLFILE SET DATABASE “clone” RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 ‘/U01/oradata/clone/redo01.log’ SIZE 200M,
GROUP 2 ‘/U01/oradata/clone/redo02.log’ SIZE 200M,
GROUP 3 ‘/U01/oradata/clone/redo03.log’ SIZE 200M
DATAFILE
‘/U01/oradata/clone/system01.dbf’,
‘/U01/oradata/clone/undotbs01.dbf’,
‘/U01/oradata/clone/users01.dbf’,
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created.

6.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles & instance status

***************************************************

Clone an Oracle Database using Hot Physical Backup

Database Name: troy

Database must be in Archive log mode.

Source Database side: (Troy database)

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path. (Refer below example)
4.Once copied the datafile, release the tablespace from begin backup mode to end backup
5.Repeat the steps 1-3 for all your tablespaces.
6.Taken the controlfile backup
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

7.Backup all your archive log files between the previous backup and the new backup as well.

Example:
steps:

2.Alter tablespace system begin backup;

To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;

Clone Database side: (Clone database)

Database Name: Clone

Clone Database Steps:

1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc…

3.Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4.Startup the Database in NOMOUNT stage.

5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change ‘REUSE’ needs to be changed to ‘SET’.

CREATE CONTROLFILE SET DATABASE “clone” RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 ‘/U01/oradata/clone/redo01.log’ SIZE 200M,
GROUP 2 ‘/U01/oradata/clone/redo02.log’ SIZE 200M,
GROUP 3 ‘/U01/oradata/clone/redo03.log’ SIZE 200M
DATAFILE
‘/U01/oradata/clone/system01.dbf’,
‘/U01/oradata/clone/undotbs01.dbf’,
‘/U01/oradata/clone/users01.dbf’,
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created.

6.Recover the database using controlfile.

Recover database using backup controlfile until cancel;

7.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles status.

CASE 2

Refresh Preparatory Tasks

  • Check the target database space is sufficient or not.
  • If the source is development/test database, confirm the downtime available for the source database.
  • If the Source is                                                                                                                                                                                                                                                                                                                                                                                    -Hotbackup-Production Database

-Restored Hotbackup set

-Restored Colbackup

-Coldbackup-Production Database

-Export Backup (Production Database)

Data Source :Hotbackup-Production Database

At target : Development DB

Pre-Refresh Tasks

  • Edit oratab file and put database in DONTMONITOR mode.                                                                                                                                                      – On SunOS : /var/opt/oracle/oratab                                                                                                                                                               – On HP-UX : /etc/oratab
  • set oracle environment variables                                                                                                                                                                   – ORACLE_SID                                                                                                                                                                                                                                                             – ORACLE_HOME  or                                                                                                                                                                                                         – Use (. oraenv) environment setup script
  • Use env | grep ORA to verify the oracle setup
  • Login as oracle on target database server and run the script /oracle/admin/dbrefresh/<SID>/pre_refresh.sh <SID> and validate
  • Please note the Database is in Archive log or No Archive log mode
  • Please note the TEMP tablespace size of the target Database
  • Shutdown immediate the database on target database server
  • Clean up all the datafiles,control files and log files of the target

At Source: Production DB

  • Issue the command Alter database backup controlfile to trace on the source database, modify it so as to create a database (set the database name, change noresetlogs to resetlogs &  change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. Copy create_db.sql  to /oracle/admin/dbrefresh/<SID> on Target Server.
  • Issue ‘Alter system switch log file’
  • Alter all the tablespaces to be put into the begin backup mode. Copy all the datafiles to the target server. End the begin backup mode of all the tablespaces.
  • Take the list of Archive log files generated during(hot) backup and copy to target server for the recover process.
  • If the  init<SID>.ora file has to be copied then  make sure apart from other changes the following mandatory parameters have to be changed. DBNAME, BDUMP,CDUMP, DUMP & CONTROL_FILE. Do it only when required

.

At Target:

  • Start the instance in NOMOUNT mode. Use PFILE option if required
  • Run /oracle/admin/dbrefresh/<SID>/create_db.sql
  • Recover the database by applying log files
  • Create the TEMP files
  • Keep the Database in Archive log or No Archive log mode
  • Drop database links pointing to production
  • Re-create the database links on target database

Go to Post-refresh Tasks

  • Check database and listener are UP and Running
  • Change the entry in oratab to MONITOR

Data Source :Restored Hotbackup set

At Target :

Pre-Refresh Tasks

  • Edit oratab file and put database in DONTMONITOR mode.                                                                                                                                                      – On SunOS : /var/opt/oracle/oratab                                                                                                                                                               – On HP-UX : /etc/oratab
  • set oracle environment variables                                                                                                                                                                   – ORACLE_SID                                                                                                                                                                                                                                                             – ORACLE_HOME  or                                                                                                                                                                                                         – Use (. oraenv) environment setup script
  • Use env | grep ORA to verify the oracle setup
  • Login as oracle on target database server and run the script /oracle/admin/dbrefresh/<SID>/pre_refresh.sh <SID>  and validate
  • Please note the Database is in Archive log or No Archive log mode
  • Please note the Size of the TEMP tablespace
  • Shutdown immediate the database on target database server
  • Clean up all the datafiles,control files and log files of the target
  • To restore the hotbackup set or copy the following files to destination server.                                                                                      – datafile                                                                                                                                                                                                                                 – init<SID>.ora                                                                                                                                                                                              – archivelog files                                                                                                                                                                                              – control_trace_file
  • Edit controltrace<SID>.<DATE>.trc file so as to create a database (set the database name, change noresetlogs to resetlogs &  change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. Copy create_db.sql  to /oracle/admin/dbrefresh/<SID> on Target Server.

Pre-Refresh Tasks

  • Start the instance in NOMOUNT mode. Use PFILE option if required
  • Run /oracle/admin/dbrefresh/<SID>/create_db.sql
  • Recover the database by applying log files
  • Create the TEMP files.
  • Keep the Database in Archive log or No archive log mode backup Policy.
  • Drop database links pointing to production

Go to Post-refresh Tasks

  • Check database and listener are UP and Running
  • Change the entry in oratab to MONITOR

Source : Restored Coldbackup set

  • To restore the coldbackup set or copy the following files to destination server.                                                                                    -datafile                                                                                                                                                                                                                 -init<SID>.ora                                                                                                                                                                                                                                                                                                                       -redolog files                                                                                                                                                                                                              -controlfile<SID>.<DATE>.trc
  • Edit controltrace<SID>.<DATE>.trc file so as to create a database (set the database name, change noresetlogs to resetlogs &  change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. Copy create_db.sql  to /oracle/admin/dbrefresh/<SID> on Target Server.
  • Login as oracle on target database server and run the script /oracle/admin/dbrefresh/<SID>/pre_refresh.sh <SID> .
  • Edit oratab file and put database in DONTMONITOR mode.                                                                                                                                                      – On SunOS : /var/opt/oracle/oratab                                                                                                                                                               – On HP-UX : /etc/oratab
  • set oracle environment variables                                                                                                                                                                   – ORACLE_SID                                                                                                                                                                                                                                                             – ORACLE_HOME  or                                                                                                                                                                                                         – Use (. oraenv) environment setup script
  • Use env | grep ORA to verify the oracle setup
  • Please note the Database is in Archive log or No Archive log mode
  • Please note the Size of the TEMP tablespace
  • Shutdown immediate the database on target database server
  • Clean up all the datafiles,control files and log files of the target
  • Start the instance in NOMOUNT mode. Use PFILE option if required
  • Run /oracle/admin/dbrefresh/<SID>/create_db.sql
  • Recover the database using backup controlfile, no need to apply archive log files, but online redolog files may be applied in some occasionand create temp files
  • Create the TEMP files
  • Keep the Database in Archive log or No archive log mode as per the backup Policy.
  • Drop database links pointing to production

Go to Post-refresh Tasks

  • Check database and listener are UP and Running
  • Change the entry in oratab to MONITOR

Source : Coldbackup-Production Database

At Target:

  • Edit oratab file and put database in DONTMONITOR mode.                                                                                                                                                      – On SunOS : /var/opt/oracle/oratab                                                                                                                                                               – On HP-UX : /etc/oratab
  • set oracle environment variables                                                                                                                                                                   – ORACLE_SID                                                                                                                                                                                                                                                             – ORACLE_HOME  or                                                                                                                                                                                                         – Use (. oraenv) environment setup script
  • Use env | grep ORA to verify the oracle setup
  • Login as oracle on target database server and run the script /oracle/admin/dbrefresh/<SID>/pre_refresh.sh <SID> .
  • Please note the Database is in Archive log or No Archive log mode
  • Please note the Size of the TEMP tablespace
  • Shutdown immediate the database on target database server
  • Clean up all the datafiles,control files and log files of the target

At Source:

  • Edit oratab file and put database in DONTMONITOR mode Shutdown the database on source database server.
  • Issue the command Alter database backup controlfile to trace on the source database, modify it so as to create a database (set the database name, change noresetlogs to resetlogs &  change ARCHIVELOG to NOARCHIVELOG) and name as create_db.sql. Copy create_db.sql  to /oracle/admin/dbrefresh/<SID> on Target Server. Create dd script if required
  • Do ‘Alter system switch logfile’ n+1 number of times, where n is number of archive groups.
  • Shutdown immediate the database
  • Copy the following files to Targer server.                                                                                                                                                        – datafiles                                                                                                                                                                                                                – redolog files                                                                                                                                                                                                                  – create_db.sql                                                                                                                                                                                                                                            – init<SID>.ora
  • starup the source database
  • Edit oratab file and put database back in MONITOR.

At Target:

  • Start the instance in NOMOUNT mode. Use PFILE option if required
  • Run /oracle/admin/dbrefresh/<SID>/create_db.sql
  • Recover the database using backup controlfile, no need to apply archive log files, but online redolog files may be applied in some occasion.
  • Create TEMP files.
  • Keep the Database in Archive log or No archive log mode as per the backup Policy.
  • Drop database links pointing to production

Go to Post-refresh Tasks

  • Check database and listener are UP and Running
  • Change the entry in oratab to MONITOR

Source : Export Backup(Production Database)

At Target:

  • Take an export backup of target if required
  • Drop the required user (Target schema) using                                                                                                                                                                               – Drop user <required schema name> cascade;
  • Re-create the required user in the target (development) database using                                                                                                                        – Create < username > identified by < password >
  • default tablespace < user’s default tablespace name >                                                                                                                                                                                                                                                              temporary tablespace < temporary tablespace name >
  • Grant requisite privileges to the user.

At Source:

  • Export the required schema using                                                                                                                                                                          exp <dba username>/<password> owner=<required schema name> file=<dump file >. Validate the export log
  • Copy the export dump file to Targer server.

At Target:

  • Import the dump file of source (production) database into target  (development) database using                                                                                                                          -imp <dba username>/<password> fromuser=<production schema name>touser=<development schema name> file=<dump file>
  • Validate the import log.

Note:  The target database init.ora file can be reused, provided the SGA size and other parameter  needs to be retained with same values.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: