Create a Clone DB from Backup on the Same Server (or remote)

Hi,

I wanted to create a post series about cloning/duplicating database on the same or remote servers. There are so many options and combinations about this like rman duplicate database with/without  target/source db connection, rman restore using backups, ASM to OS or OS to ASM, same server or remote server, OMF or regular file naming etc…

What I am about to demonstrate is creating a TEST db from PROD db on the same server with “no ASM” only OS file system using PROD DB backups (not rman duplicate database command). of course, you can use same method to create a TEST db on “remote server”  just by copying backups and archivelogs (maybe a copy of controlfile too) to same location on a remote server. So, Lets get started:

source database (let’s say this is your PROD db which is source db) SID                : orcl
duplicate/target database (let’s say this is the TEST db which you want to create ) SID : dup
method : using backups only (no duplicate database command)
server    : same or remote (you can copy your backups to a remote server under same location with prod server and follow the same steps)
file naming : OMF (Oracle Managed Files)
OS : Linux / Windows (Windows steps are also very similar. I will add some notes for windows).
Database version: 19c (19.3)

while doing this, I will try to explain why we do that step and important files of database.

1- Parameter File

Parameter file is the first file to read while starting a database instance. it defines vital and mandatory parameters to create an instance, like database name. So, to create a new database, first we need parameter file. it could be either SPFILE (binary version) or PFILE (text version). Since we can not create a binary file easily, I will create text parameter file and then create binary version of it. there thousands of parameters in Oracle but just 3 of them is enough to create an instance. under ORACLE_HOME/dbs directory, create an empty file with name initdup.ora and put those into it:

Windows OS Note: for Windows OS, directory is ORACLE_HOME/database and file name is INITDUP.ora by default. Also you need to create a Windows Service for Oracle Database instance using “oradim” tool first: “oradim -NEW -SID dup -STARTMODE auto” is enough to achieve this.

what are these 3 parameters?

1. db_name: name of the database which is “orcl” for now. wait what? don’t we create a new db called “dup” why is this orcl ? because we will use orcl database backups to create our database and I don’t want to show all backup files to rman. so We will act as if we are creating an db called “orcl” but we will change it while doing this. we will use orcl is just for finding backups and restore/recover datafiles.
2. db_unique_name: this parameter is set to null by default and if it is null, it’s value is db_name. so you can think it as if Oracle uses it “NVL(db_unique_name, db_name)”. as you see this parameter is set to “dup” which is name of new database. while doing restore/recover operations, if rman does not know that this is a different database then it will restore /recover source database (orcl). with this parameter we are identifying our unique database.
3. db_create_file_dest: this is the parameter that makes your database as OMF (oracle managed files) which means you don’t need to give any file name. just say “add a datafile to this tablespace”; Oracle will add new datafile and give a name to it. we don’t need to know what is the name of a datafile. this parameter also, of course, tells that where the files will be created. by saying files not just datafiles. also redo logs and control files too. this is the “main directory”  for a database. so path pattern is like this:
   /u01/app/oracle/oradata/<instance_name>/datafile => for datafiles
   /u01/app/oracle/oradata/<instance_name>/controlfile => for controlfiles
   /u01/app/oracle/oradata/<instance_name>/onlinelog => for redo logs.
   in our example, instance name will be “dup”.

Now, we have a parameter file so we can start our database instance. Do not forget that our source database is also on the same server so we must be careful not to mess with source db. So, I will set ORACLE_SID environment variable first:

Windows OS Note: in Windows you can set your environment variable ORALCE_HOME by using “set” command not export. “set ORACLE_SID=dup”.

as you can see we started an instance already. of course we don’t even have a datafile yet but still this is an instance. we didn’t specify any memory parameter for example in pfile but they have all default values so it will work but of course if you have a defined configuration you can add them into pfile (initdup.ora) so db instance will be using them.

I created SPFILE before starting the instance so spfiledup.ora is created under ORACLE_HOME/dbs (ORACLE_HOME/database for windows os ) directory and instance is started by this spfile.

so we have a basic parameter file and our instance is up. let’s continue with step 2.

2- Control File

control file is the next step while starting a database. it stores very critical information like where are datafiles, backups, redo logs. Also many database limits are defined in controlfile like maximum number of data files which can be created in db (MAXDATAFFILES). to restore control file, first I will backup as copy control file from source db (which is orcl, our prod database). so, from “orcl”, first we create control file copy.

so I created controlORCL.ora file under /tmp which is copy of current control file of orcl db. now let’s turn to dup db and restore it.

command is “restore controlfile from ‘/tmp/controlORCL.ora’;” and control file restored under: /u01/app/oracle/oradata/DUP/controlfile/ directory. as you see it is under DUP directory because of db_unique_name parameter.

We restored our control file but this is not entirely correct because this control file is belong to orcl db and it knows this database as “orcl” but we will deal with that later. Now, this control file knows where backups are, so we can restore datafiles but first we must take our database to mount mode.

Now, restore database BUT as I mentioned, this control file belongs to “orcl” db so if I run “restore database” it will restore datafiles to original “orcl” db datafile locations.  You must be careful here, you don’t want to shutdown your source db. to restore those datafiles to a different location (dup db datafile location)  we will use “set newname” command:

since I use OMF, ” set newname for database to new;” means, restore datafiles to wherever db_file_create_dest parameter points. by default, control file knows where those datafiles are so if I restore them, it will be under ORCL db location. “set newname for database to new” cause re-read parameter file and datafile location information and restore datafiles to there. as you see in the output, datafiles are restore under DUP location (ex: channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_system_%u_.dbf) .

perfect. now we have datafiles too but we can not open database right away. first, even if we are able to open database, control file is still old and will look for datafiles under ORCL directory not DUP directory. we must tell controlfile to use those recently restored datafiles as primary datafiles. so we must “switch database to copy”.

let’s recover database now:

this will apply all changes which are made after the backup. it will read archivelog files and apply them into datafiles.

Good!, can we open database now? well, no 🙂 why? because as I said earlier, this controlfile is still a stranger! it knows this database as “orcl” but it is not anymore! Since we get everything we want (controlfile, datafiles, recover operation) now we can change our controlfile. to achieve that we must re-create control file and tell “this is dup db anymore”.  of course before do that, we must change “db_name” parameter anymore. it is still “orcl”. we can set it as “dup” now but this parameter can not be changed without restart. Problem about that, you can not change db_name with “alter system” if you are using spfile. so stesp are these:

1. I create pfile
2. shutdown database
3. modify pfile and change db_name parameter to “dup”, remove db_unique_name parameter
4. create spfile again. ===> This is very important. if you forgot this step, when you start db, it will be using old values.
5. start db in mount mode.
6. create controlfile create script using “alter database backup controlfile to trace” command.

finally we need to re create our controlfile to make it completely a dup db controlfile. I created controlfile script under the tmp directory. you can open it any text editor. you will find 2 “create controlfile ” command. one with NORESETLOGS and the other RESETLOGS. on a new database, to create redo logs, we must use RESETLOGS clause. so I will just write here RESETLOGS version in control_file.txt file.

as you can see, controlfile thinks that this is ORCL database and all redo logs are also under ORCL directory (‘/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_j6xpd19n_.log’,). This is also an important step. if you don’t change those ORCLs to DUP, you might cause damage to your source database which is orcl in my example. Since I use OMF, and I always use OMF just for these reasons, all I need to do is remove those path.

Also, since we want to change database name, we must add SET clause after “create controlfile reuse” part. Please note that, just changing ORCLs to DUP in this script won’t work because datafiles also has database name information in their header file. so we must use SET which means, “set database name”. this will also update datafile headers. to create controlfile again, you must be in nomount mode (in mount mode, cotnrolfiles are already in use, you can not change them.)

finally, we have a real controlfile for dup db. after now, we can  open it with resetlogs option. creating controlfile will also take our database into “mount” mode so no need another “alter database mount” command.

viola! we have a new db with a new name which is also created from another database backup. you can use this on the same or remote servers. as said before, only difference is copying backups to remote server.

I hope this helps. I will try to write for other methods too like duplicate database etc.

please make a comment, so I can believe that not daily 30 visitors on my blog are bots 🙂

wish you healthy days.

Important Edit: you might ask why we don’t create controlfile before all those operations. it has 2 reasons. creating control file will cause loosing the location of backups and more importantly, create controlfile command validates the datafiles if they are in the correct locations. so we can not create controlfile without having datafiles.