| |
 |
|
RAC Database cloning with HOT Backup
Oracle Tips by Vijaya Chander
July 11, 2015
|
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.
|
|
|
