This document gives you a detailed flow of how to backup the database in a 24x7 operation and use it for cloning a Development or Test database. By using the System switching of Log files option, it reduces the amount of archive logs to be applied and reduces the time of recovery (the goal).Most sites running Oracle databases need the latest production databases to be cloned for the development and test teams to work on. These sites need to be refreshed periodically with the production database and Application. Most DBAs use Cold Backup procedures to clone a database. Although cold backups are the most common backup procedures for database cloning, there may be situations where no downtime is available. Hot Backups can be performed when the database is up and in Archive log mode.
Scope
This document explains, in details, about how to perform user managed cloning of a active production database into test/development environment. It does not take into account the process of cloning using the Recovery Manager (RMAN) utility provided by Oracle.
Definitions and Assumptions
Source Database: The active production instance/database to be cloned.
Target Database: The cloned database, typically a test/development database.
Cloning steps
Switching Logs
We need to make sure that all the present online logs are switched and archived, so that the time to do the media recovery to the cloned database is as small as possible.
Query the number of log groups that exists in the database and switch as many times as there are log groups.
Code:
Select group#,archived,status from v$log;
GROUP# ARCHIVED STATUS
1 YES INACTIVE
2 NO CURRENT
We can see from the example above, we need to switch logs to archive the online logs.
Code:
alter system switch logfile;
System altered.
Make sure that no log group shows a “STALE” status.
Destination Directory structure creation
Create the destination directory structures as you would like to move the data files and redo log files. Also create the dump directory to hold the target database dumps (udmp, bdump cdump, adump etc).
Let’s note down the last archive log file from the archive log destination directory or from the alert log and then do another
Alter system switch logfile;
This would give us the current log that got archived by switching and also a
checkpoint occurs. Remember that switching leads to check pointing and check pointing not necessarily need lead to a log switch. So the data files all get consistent since the current scn’s are stamped. We do the switch to the number of log groups we have. That means the first archive log file that was switched might be your full file size and the subsequent ones would have lesser size depending on the data.
Copy source database files to the target
The next step is to copy all the data files of the source database to the destination database directories.
Before starting to copy the files of a particular tablespace, we need to put that tablespace into hot backup mode.
By putting a tablespace into hot backup mode, we will ensure two things.
1) The first time a block is changed in a data file that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) are written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the data file and DBWR are working on the same block simultaneously.
2) The data file headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.
To limit the effect of this additional logging, you should ensure you only place one tablespace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.
List out all the tablespaces present in the system and their corresponding data files.
select tablespace_name,status,contents from dba_tablespaces
TABLESPACE_NAME STATUS CONTENTS
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
FLIRT ONLINE PERMANENT
6 rows selected.
select tablespace_name,file_name from dba_data_files order by tablespace_name;
TABLESPACE_NAME FILE_NAME
FLIRT C:\FLIRT\DBFILES\FLIRT01.DBF
SYSAUX C:\FLIRT\DBFILES\SYSAUX01.DBF
SYSTEM C:\FLIRT\DBFILES\SYSTEM01.DBF
UNDOTBS1 C:\FLIRT\DBFILES\UNDOTBS01.DBF
USERS C:\FLIRT\DBFILES\USERS01.DBF
5 rows selected
Now put each tablespace into hot backup mode, copy it’s corresponding data files to the destination directory, and then bring out the tablespace from the hot backup mode, one tablespace at a time.
Alter tablespace FLIRT begin backup;
!copy C:\FLIRT\DBFILES\FLIRT01.DBF C:\VALEN\DBFILES\FLIRT01.DBF
Alter tablespace FLIRT end backup;
Switch logs again
Once again, once the copy is complete, switch log groups the required number of times. In this way we need to copy over only those archive files starting with the ones that we noted down when we started the log switch to the ones we have when we did the log switch after the backup. These would be very few and hence the recovery would also be faster when we
apply these archive files. We would also have a set of archive files that we are sure of and consistency can thus be assured.
Backup the Control File.
Now we need a control file creation script for the target database. The best way to achieve this is to backup the source control file to trace with the following command.
Code:
alter database backup controlf ile to trace;
Database altered.
Copy the appropriate archive logs and online redo logs to the target location.
Prepare Initialization Parameter File
Make appropriate changes to the initialization parameter file of the target db to reflect the new file locations and new database. Create appropriate target dump locations (adump, bdump, udump etc).
Creating Service and control files in target
Set the sid to the target database.
If the target host is windows, then you will need to create database service control with the oradim command. Use the following syntax to create the service.
Oradim –new –sid
start a sqlplus session as sysdba and start the instance in nomount mode with target init file.
Open the control file trace backup. Towards the end, you’ll find controlfile creation statement with resetlogs option .Following is an example:
Quote:
CREATE CONTROLFILE reuse DATABASE "FLIRT" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 'C:\FLIRT\REDO1\REDO01.LOG' SIZE 5M,
GROUP 2 'C:\FLIRT\REDO2\REDO02.LOG' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'C:\FLIRT\DBFILES\SYSTEM01.DBF',
'C:\FLIRT\DBFILES\UNDOTBS01.DBF',
'C:\FLIRT\DBFILES\SYSAUX01.DBF',
'C:\FLIRT\DBFILES\USERS01.DBF',
'C:\FLIRT\DBFILES\FLIRT01.DBF',
'C:\FLIRT\DBFILES\DGOD01.DBF'
CHARACTER SET WE8MSWIN1252
;
Change the first line of the statement to look like this :
Quote:
CREATE CONTROLFILE set DATABASE "VALEN" RESETLOGS ARCHIVELOG ……..
Also change the file locations of the database mentioned in the statement to reflect the target file location. After all the changes to the statement, it should look like following :
Quote:
CREATE CONTROLFILE set DATABASE "VALEN" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 'C:\valen\REDO1\REDO01.LOG' SIZE 5M,
GROUP 2 'C:\valen\REDO2\REDO02.LOG' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'C:\valen\DBFILES\SYSTEM01.DBF',
'C:\valen\DBFILES\UNDOTBS01.DBF',
'C:\valen\DBFILES\SYSAUX01.DBF',
'C:\valen\DBFILES\USERS01.DBF',
'C:\valen\DBFILES\flirt01.DBF',
'C:\valen\DBFILES\DGOD01.DBF'
CHARACTER SET WE8MSWIN1252
;
Go the sqlplus session and execute the above (changed) controlfile creation statement to create the controlfiles. Controlfile should be created without any errors.
Apply Media Recovery
Now you need to apply media recovery to make the database consistent. Execute the following statement.
Quote:
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
You may need to apply the online redo logs to make the media recovery complete.
After media recovery is complete open the database in restelogs mode.
Code:
Alter database open resetlogs;
Add tempfiles to the temporary tablespaces.
Code:
ALTER TABLESPACE TEMP ADD TEMPFILE
'C:\valen\DBFILES\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND OFF;
Cloning is complete!.
No comments:
Post a Comment