Clone Database using RMAN on Windows 10

Cloning Database with RMAN :

A cloning database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks to clone database:

Prerequisites:

You should have 2 servers , both server should have Oracle Software installed on them.

Oracle Version : 11.2.0.4.0
OS : Windows 10
Database Name : DBTEST

set oracle_sid as dbtest and login to sqlplus.

C:\Windows\system32>set oracle_sid=dbtest

C:\Windows\system32>sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 14 18:15:48 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
DBTEST READ WRITE
SQL>

Set db_recovery_file_dest and db_recovery_file_dest_size according to your database.

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
db_recovery_file_dest string D:\oracle\fast_recovery_area
db_recovery_file_dest_size big integer 4152M
SQL>
SQL> alter system set db_recovery_file_dest_size=100G;
System altered.
SQL>

Check supplemental logging at database level :

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

Enable supplemental logging if not enabled .

SQL> alter database add supplemental log data;

Database altered.
SQL>

check force logging at database level

SQL> select force_logging from v$database;

FOR
---
NO

SQL>

Enable force logging , if not enabled.

SQL> alter database force logging;

Database altered.

SQL>

Now connect to RMAN

C:\Windows\system32>rman target sys

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 14 18:19:03 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: DBTEST (DBID=1353774893)

RMAN>

Now set controlfile autobackup on , this will automatically backup the controlfile and spfile when you backup your database.

RMAN> configure controlfile autobackup on;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>

Take full backup of database using backup database command.

RMAN> backup database;

Starting backup at 14-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
input datafile file number=00002 name=D:\ORACLE\ORADATA\DBTEST\SYSAUX01.DBF
input datafile file number=00003 name=D:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
input datafile file number=00004 name=D:\ORACLE\ORADATA\DBTEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 14-DEC-17
channel ORA_DISK_1: finished piece 1 at 14-DEC-17
piece handle=D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\BACKUPSET\2017_12_14\O1_MF_NNND
F_TAG20171214T182203_F34WZN4L_.BKP tag=TAG20171214T182203 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 14-DEC-17

Starting Control File and SPFILE Autobackup at 14-DEC-17
piece handle=D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_9
62734989_F34X1PP4_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-17

Make this backup available to server where we are going to start cloning process.

Restoration Process 

In windows we need to start the service explicitly using oradim command :

C:\Windows\system32>oradim -new -sid dbtest -intpwd oracle -startmode a

Instance created.

Connect to RMAN and startup nomount command. You can see , if oracle wont get proper pfile or spfile to start instance you provided , it will start it using default parameters for you .

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITDBTEST.ORA'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1071333376 bytes

Fixed Size 1410028 bytes
Variable Size 289410068 bytes
Database Buffers 775946240 bytes
Redo Buffers 4567040 bytes

Restore SPFILE to pfile using autobackup .

RMAN> restore spfile to pfile 'd:\oracle\product\11.2.0\dbhome_1\database\initdbtest.ora' 
2> from 'D:\oracle\fast_recovery_area\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_962734989_F34X1PP4_.BKP' ;

Starting restore at 14-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP D:\oracle\fast_recovery_area\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_962734989_F34X1PP4_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-DEC-17

RMAN>

Restart database using pfile we have restored from autobackup .

RMAN> startup force nomount pfile='d:\oracle\product\11.2.0\dbhome_1\database\initdbtest.ora';

Oracle instance started

Total System Global Area 1071333376 bytes

Fixed Size 1406436 bytes
Variable Size 587205148 bytes
Database Buffers 478150656 bytes
Redo Buffers 4571136 bytes

Restore the controlfile

RMAN> restore controlfile from autobackup;

Starting restore at 14-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

recovery area destination: D:\oracle\fast_recovery_area
database name (or database unique name) used for search: DBTEST
channel ORA_DISK_1: AUTOBACKUP D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_962734989_F34X1PP4_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_962734989_F34X1PP4_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=D:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
Finished restore at 14-DEC-17

RMAN>

Mount database using controlfile we have restored.

RMAN>alter database mount;

Now Restore database :

RMAN> restore database;

Starting restore at 14-DEC-17
Starting implicit crosscheck backup at 14-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 14-DEC-17

Starting implicit crosscheck copy at 14-DEC-17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-DEC-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\AUTOBACKUP\2017_12_14\O1_MF_S_962734989_F34X1PP4_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\DBTEST\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\DBTEST\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\BACKUPSET\2017_12_14\O1_MF_NNNDF_TAG20171214T182203_F34WZN4L_.BKP
channel ORA_DISK_1: piece handle=D:\ORACLE\FAST_RECOVERY_AREA\DBTEST\BACKUPSET\2017_12_14\O1_MF_NNNDF_TAG20171214T182203_F34WZN4L_.BKP tag=TAG20171214T182203
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 14-DEC-17

RMAN>

If new archive logs are generated after backup was taken transfer it to clonedb server and apply recover database command:

RMAN> recover database;

Starting recover at 14-DEC-17
using channel ORA_DISK_1

starting media recovery

archived log file name=D:\ARC\DBTEST\ARC0000000044_0960912047.0001 thread=1 sequence=44
archived log file name=D:\ARC\DBTEST\ARC0000000045_0960912047.0001 thread=1 sequence=45
archived log file name=D:\ARC\DBTEST\ARC0000000046_0960912047.0001 thread=1 sequence=46
archived log file name=D:\ARC\DBTEST\ARC0000000047_0960912047.0001 thread=1 sequence=47
unable to find archived log
archived log thread=1 sequence=48
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/14/2017 18:57:41
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 48 and starting SCN of 1788446

Note SCN shown in error and apply following command :

RMAN> recover database until scn 1788446;

Starting recover at 14-DEC-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-DEC-17

RMAN>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Comments

  1. Reply
    Sayan Malakshinov

    It’s much easier to use “rman duplicate”. You can duplicate from backup or active database and change any spparameters you want during this process, for example:

    rman target sys/pass@SRC auxiliary sys/pass@DST
run
{
duplicate target database to DST
   FROM ACTIVE DATABASE
   SPFILE
   set db_unique_name='DST'
   set fal_client=''
   set fal_server=''
   set instance_name='DST'
   set control_files='...CONTROL01.CTL','...CONTROL02.CTL'
   set log_archive_config=''
   set log_archive_dest_1=''
   set log_archive_dest_2=''
   set log_archive_dest_3=''
   set DB_FILE_NAME_CONVERT='...','...'
   set LOG_FILE_NAME_CONVERT='SRC','DST'
   set DB_CREATE_FILE_DEST='...'
   set audit_file_dest='...'
   set dispatchers='(PROTOCOL=TCP) (SERVICE=DSTXDB)'
   set log_archive_config=''
   set local_listener=''
   nofilenamecheck;
}

Leave a Reply

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