Cloning the Oracle Database using online hot user manged backup

Clone an Oracle database using an online hot user manged backup

Cloning of Oracle Database with using user managed backup to new server.
Note:When a tablespace is put into backup mode, Oracle will used entire blocks to redo/undo segments rather than the usual change data files. For this reason, do not perform a hot backup during periods of heavy database activity – it could lead to a lot of archive logs being created.

Following are the steps for cloning the database

1. Check the current archive log change number of Production database.

Prod> Select max(first_change#) chng from v$archived_log;

2. Check archive status on Prod.

Archive Log list;

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/PROD
Oldest online log sequence 50270
Next log sequence to archive 50272
Current log sequence 50272

3. Take the backup of control file to trace and copy to CLONE server.

alter database backup controlfile to trace as 'Location/create_CLONE.sql';

4. Take backup of spfile to pfile from Production and copy to Clone server.

create pfile='init.ora' from spfile;

5. Create the password file on CLONE Database Server.

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=******

6. Take the database in begin backup mode.

alter database begin backup;

---SCP or copy all the database files from production to Clone server.

alter database end backup;

Note: you can also take table space wise backup mode enable. and transfer only datafiles belong to that tablespace and disable the tablespace backup mode after copy done.

select 'alter tablespace '||NAME||' begin backup;' from v$tablespace
union
select 'alter tablespace '||NAME||' end backup;' from v$tablespace order by 1;
e.g. alter tablespace users01 begin backup;
-- SCP or copy the datafiles belong to only user01 tablespace.
alter tablespace users01 end backup;

then go to another table space similarly.

Note: Verify that all tablespaces is in disable from backup mode.

SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';

7. After taking the data files copy to clone server, Issue this command to production server.

ALTER SYSTEM ARCHIVE LOG CURRENT;

8. Check the current archive and take archives to CLONE Server.

Archive Log list;

SCP or copy all the archive generated from first issued command to this command.

9. Edit the Control file and PFILE if location of data files and control files is different.

Edit the Control file: create_control.sqlSTARTUP NOMOUNT pfile = 'location of pfile'
CREATE CONTROLFILE SET DATABASE "CLON" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 '/u02/oradata/CLON/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/oradata/CLON/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/oradata/CLON/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u02/oradata/CLON/system01.dbf',
'/u02/oradata/CLON/sysaux01.dbf',
'/u02/oradata/CLON/undotbs01.dbf',
'/u02/oradata/CLON/users01.dbf'
CHARACTER SET AL32UTF8 ;

If Location is different from production database. Edit the following parameter in pfile, bdump , cdump , udump , controlfiles , log_archive_dest to new location.

10. Creation of control file script.

Connect the database
sqlplus '/ as sysdba'
SQL> @create_controlfile.sql;

11. Recover and Open the database.

SQL>recover database using backup controlfile until cancel;

Note: When prompted to ‘Specify log’ enter ‘auto’. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:

SQL> alter database open resetlogs;

12. Create Temporary tablespace and tempfiles to database.

13. Create SPFILE from PFILE

Create spfile from pfile;

14. Shutdown the database and startup with spfile.

15. Configure TNS entries and listener
Add entries for new database in the listener.ora and tnsnames.ora as necessary.

16. Change the database ID
If RMAN catalog is going to be used for backup the database, the database ID must be changed. So, Please verify the dbid from v$database view to check both production and test environment has different dbid.

shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up :
shutdown immediate
startup mount
alter database open resetlogs
/

 

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.