Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RAC Database cloning with HOT Backup

Oracle Tips by Vijaya Chander
July 11,  2008


Tips from the trenches by Vijaya Chander
 
Most shops that are running 24/7 Oracle databases need production data, which needs to be periodically refreshed into development/testing/acceptance/pre-production environments for a variety of reasons such as to re-produce functional bugs, performance issues and to conduct performance testing.
 
This article describes step by step approach to clone Oracle RAC databases using an RMAN hot backup.
                                                      
Steps to be conducted on Source DB
 
Step 1: Identify number of online redo log groups available with the database using:
 
SQL> SELECT * FROM GV$LOGFILE;
 
This step is required to switch logs those many times before starting hot backup (Step 3). This will make sure that all the online redo logs are archived. For example, if you have 4 online redo log groups, switch logs 4 times. Online redo logs can be switched using:
                       
SQL> ALTER SYSTEM SWITCH LOGFILE;
                       
Step 2: Note the latest archive log file name from archive destination directory.
           
Step 3: Take hot backup tablespace by tablespace, copying datafiles to desired location. Execute following script to get a spool file (hotbackup.sql), which in turn can be used to take hot backup.
                       
#############################################################################
spool hotbackup.sql
DECLARE
                          
            CURSOR dfiles
            IS
            SELECT tablespace_name,file_name
            FROM dba_data_files
            ORDER BY bytes DESC;
                           
BEGIN
 
            DBMS_OUTPUT.ENABLE(1000000);
                                      
            FOR i IN dfiles LOOP
            DBMS_OUTPUT.PUT_LINE ('alter tablespace '|| i.tablespace_name ||' begin backup;');
            DBMS_OUTPUT.PUT_LINE ('!cp ' || i.file_name || '/u99/oradata/clone');
            DBMS_OUTPUT.PUT_LINE ('alter tablespace ' || i.tablespace_name || ' end backup;');
            END LOOP;
                         
END;
spool off
#############################################################################
Note: Change hot backup location "/u99/oradata/clone" used in the script as required.
                       
Take hot backup by executing the spool file.
                       
SQL> @hotbackup.sql
                       
Step 4: Once hot backup is complete, again switch logfiles as many number of times as those online redo log groups (same as Step 1).
                       
Step 5: Again, note the latest archive log file name from archive destination directory. Move all archive log files created during hot backup to destination DB "db_recovery_file_dest" parameter location.
                       
Step 6: Take backup of control file to trace and move it to destination DB location. This trace file contents will be used to generate new control files at destination.
                       
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 
 

Steps to be conducted on Destination DB

 
Step 7: Edit control file by removing all the statements except "CREATE CONTROL FILE ... RESETLOGS" statement.
 
  • Change "REUSE DATABASE" to "SET DATABASE"
     
  • Change DBNAME (Source database name to appropriate Destination DB name)
     
  • Retain entries related to online redo log groups for the first instance and remove all other redo log groups.
     
  • Change Datafile/Redo log locations and copy those files from hot backup to the location specified in controlfile.
After making above changes, the control file should look something like this.
#############################################################################
CREATE CONTROLFILE SET DATABASE "PREPROD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 9344
LOGFILE
  GROUP 1 (
    '/u99/oradata/clone/redo1_01a.rdo',
    '/u99/oradata/clone/redo1_01b.rdo'
  ) SIZE 400M,
  GROUP 2 (
    '/u99/oradata/clone/redo1_02a.rdo',
    '/u99/oradata/clone/redo1_02b.rdo'
  ) SIZE 400M,
  GROUP 3 (
    '/u99/oradata/clone/redo1_03a.rdo',
    '/u99/oradata/clone/redo1_03b.rdo'
  ) SIZE 400M,
  GROUP 4 (
    '/u99/oradata/clone/redo1_4a.rdo',
    '/u99/oradata/clone/redo1_4b.rdo'
  ) SIZE 400M
DATAFILE
  '/u99/oradata/clone/system01.dbf',
  '/u99/oradata/clone/undotbs01.dbf'
  '/u99/oradata/clone/sysaux01.dbf',
  '/u99/oradata/clone/undotbs02.dbf',
  '/u99/oradata/clone/undotbs03.dbf',
  '/u99/oradata/clone/undotbs04.dbf',
  '/u99/oradata/clone/audit_data.dbf',
  '/u99/oradata/clone/audit_index.dbf',
  '/u99/oradata/clone/cust_data.dbf',
  '/u99/oradata/clone/cust_index.dbf',
  '/u99/oradata/clone/app_data.dbf',
  '/u99/oradata/clone/app_index.dbf',
  '/u99/oradata/clone/app_hist_data.dbf',
  '/u99/oradata/clone/app_hist_index.dbf',
  '/u99/oradata/clone/blob_data32_amh.dbf',
  '/u99/oradata/clone/blob_data32_och.dbf',
  '/u99/oradata/clone/note_data.dbf',
  '/u99/oradata/clone/note_index.dbf',
  '/u99/oradata/clone/lookup_data.dbf',
  '/u99/oradata/clone/lookup_index.dbf',
  '/u99/oradata/clone/rule_data.dbf',
  '/u99/oradata/clone/rule_index.dbf',
  '/u99/oradata/clone/queue_data.dbf',
  '/u99/oradata/clone/queue_index.dbf'
CHARACTER SET WE8ISO8859P1;
#############################################################################
 
Step 8: Get existing pfile from destination DB by using:
 
SQL> CREATE PFILE='/u99/pfile_dbname.ora' FROM SPFILE;
 
Edit this parameter file by changing CLUSTER_DATABASE parameter to FALSE. CREATE CONTROLFILE command needs the database to be mounted exclusively and by setting CLUSTER_DATABASE=FALSE, database can be mounted exclusively.
 
Step 9: Shutdown destination database (all nodes) and take a cold backup of datafiles, redo logfiles, controlfiles, etc., if required. Shutdown database by shutting each instance by using following command.
 
SQL> SHUTDOWN IMMEDIATE;
 
Otherwise, use following command from OS
 
srvctl stop database ?d <dbname>
 
Step 10: Start the database in nomount stage using pfile created in step 8 using:
                       
SQL> STARTUP NOMOUNT PFILE='/u99/pfile_dbname.ora';
                       
Step 11: In nomount stage, create controlfile using edited backup controlfile (from Step 7). This step creates control files as mentioned in pfile "control_files" parameter.                     
 
Step 12: Now, recover database using:
                         
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
 
Start feeding archive logs one by one using:
                
SQL> SELECT * FROM GV$ARCHIVED_LOG
WHERE <CHANGE_NUMBER> BETWEEN FIRST_CHANGE# AND NEXT_CHANGE# AND THREAD#=<THREAD_NUMBER>
 
Step 13: Once recovery is complete, open the database using resetlogs to start with new redologs.
 
SQL> ALTER DATABASE OPEN RESETLOGS;

More information on OPEN RESETLOGS is available HERE.

Now the database should be up and running and ready to use.
                         
Step 14: Create as many temporary tablespaces as they are in source DB with destination DB. Those temporary tablespace metadata entries would be present in destination DB, but temp files wouldn?t be present under those temporary tablespaces. For example, tempfiles can be created using:
 
SQL> ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ?/u99/oradata/clone/temporary01.dbf? size 20G
 
Step 15: While creating control files (in Step 7), online redo log groups and members were created only for the first instance in the database. Online redo logs has to be created for other instances also. Create as many online redo log files as they are on source DB in destination DB. For example:
 
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('/u99/oradata/clone/redo2_5a.rdo', '/u99/oradata/clone/redo2_5b.rdo') SIZE 400M;
 
Step 16: Shutdown the instance (only one node is up as of now), edit pfile with cluster_database=true parameter and start the database (all nodes).
 
Step 17: Shutdown the database and take a cold backup if required.
 
 
About the Author:
 
Vijaya Chander V.S is an experienced Senior Technical Architect (Databases) employed by Infosys Technologies Limited, Bangalore, India. Vijay can be reached at vijaya dot chander at gmail dot com.

 
   
Oracle Grid and Real Application Clusters

See working examples of Oracle Grid and RAC in the book Oracle Grid and Real Application Clusters.

Order directly from Rampant and save 30%. 
 



 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.