This article will walk you through manual methods for cloning databases and tablespaces. If you’re already familiar with these techniques, then feel free to move on to the next chapters in this book, which illustrate how to employ the RMAN duplication process.
Knowledge of these methods will help you understand when it’s appropriate to use a technique and its advantages and disadvantages. This information will help you better understand the other chapters in this book that contrast these techniques with the RMAN DUPLICATE functionality. First up is cloning a database using a cold backup.
If you worked with Oracle twenty or so years ago, you probably used a cold backup to move a database from one server to another. Even though this is an old technique, I still find myself occasionally using this method for cloning a database. For example, recently my supervisor asked me to copy a database (about 20 gig in size) from one server to another. In this scenario, the destination server directory structure was different from the source server directory structure, and the destination database needed to have a different name that the source database. In this situation I used a cold backup to move the database for the following reasons:
- Source database wasn’t in archive log mode
- There weren’t any RMAN backups of the source database
- Source database was fairly small, and it wouldn’t take long to copy the data files
- The requirement was to replicate the entire database
For this example to work you need the same version of Oracle installed on both the source and destination servers. The scenario is depicted in Figure 1.
Figure 1. Cloning Oracle with a cold backup
Next are the detailed descriptions of each of the steps shown in Figure 1.
1. On the source database, determine the locations of the data files:
SQL> select name from v$datafile;
Here’s some output for the database used in this example:
NAME
--------------------------------
/u01/dbfile/TRG/repdata.dbf
/u01/dbfile/TRG/repidx.dbf
/u01/dbfile/TRG/sysaux01.dbf
/u01/dbfile/TRG/system01.dbf
/u01/dbfile/TRG/undotbs01.dbf
/u01/dbfile/TRG/users01.dbf
2. On the source database create a trace file that contains a CREATE CONTROLFILE command in it:
SQL> alter database backup controlfile to trace as '/tmp/dk.sql' resetlogs;
3. Copy the trace file from the source server to the destination server. This example uses the Linux/UNIX scp command (initiated from the source server):
$ scp /tmp/dk.sql oracle@shrek2:/tmp
4. Shut down the source database using immediate (and not abort):
$ sqlplus / as sysdba
SQL> shutdown immediate;
5. Create directory structures on destination server:
$ mkdir /u01/dbfile/DUP
$ mkdir /u01/oraredo/DUP
6. While the source database is shut down, copy the source data files from the source server to the destination server. This example uses the Linux/UNIX scp command (initiated from the destination server):
$ scp oracle@shrek:/u01/dbfile/TRG/*.dbf /u01/dbfile/DUP
Notice there’s no need to copy the control files or the online redo logs in this scenario. Since the destination directory structure and destination database name will be different from the source name, the control files and online redo logs will need to be recreated. If the directory structure and the database name were the same on both the source and the destination, the procedure would be as simple as shutting down the source database, copying all control files, data files, online redo logs, and initialization file to the destination server, and then starting the database.
Note
7. Copy the source init.ora file to the destination server. If your source database uses an SPFILE, then you can create a text-based init.ora file from SQL*Plus, as follows:
SQL> create pfile from spfile;
This command will place a text-based initialization file with the name of init<SID>.ora in the ORACLE_HOME/dbs directory. If you don’t want the text-based file to be placed in that directory you can override the default behavior as follows:
SQL> create pfile='/tmp/initTRG.ora' from spfile;
This example uses the Linux/UNIX scp command (initiated from the destination server). Modify this appropriately for your environment. Assuming the file is in the default location of ORACLE_HOME/dbs:
$ scp oracle@shrek:$ORACLE_HOME/dbs/initTRG.ora $ORACLE_HOME/dbs/initDUP.ora
8. Modify the destination init.ora file, change the DB_NAME parameter to reflect the new name of the database, and also modify any directories to reflect the directory structure of the destination environment:
$ vi $ORACLE_HOME/dbs/initDUP.ora
Here is the content of the initDUP.ora file after the modifications:
db_name='DUP'
control_files='/u01/dbfile/DUP/control01.ctl','/u01/dbfile/DUP/control02.ctl'
db_block_size=8192
fast_start_mttr_target=500
job_queue_processes=10
memory_max_target=500M
memory_target=500M
open_cursors=100
os_authent_prefix=''
processes=100
remote_login_passwordfile='EXCLUSIVE'
resource_limit=true
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
workarea_size_policy='AUTO'
9. Modify the script to recreate the destination database control file:
$ vi /tmp/dk.sql
Change the first line to include the SET keyword and change the database name and directory structures to reflect the destination environment. Here are the contents of dk.sql after the modifications:
CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 876
LOGFILE
GROUP 1 '/u01/oraredo/DUP/redo01a.rdo' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/oraredo/DUP/redo02a.rdo' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/dbfile/DUP/system01.dbf',
'/u01/dbfile/DUP/sysaux01.dbf',
'/u01/dbfile/DUP/undotbs01.dbf',
'/u01/dbfile/DUP/users01.dbf',
'/u01/dbfile/DUP/repdata.dbf',
'/u01/dbfile/DUP/repidx.dbf'
CHARACTER SET AL32UTF8;
10. Set the ORACLE_SID variable to reflect the destination database name:
$ export ORACLE_SID=DUP
11. Start up the destination database in nomount mode:
$ sqlplus / as sysdba
SQL> startup nomount;
12. Execute the script to recreate the control file:
SQL> @/tmp/dk.sql
You should see this message if successful:
Control file created.
At this point you have new control files and the database is in mount mode.
13. Alter the destination database open with the OPEN RESETLOGS clause:
SQL> alter database open resetlogs;
Database altered.
14. Lastly, add the temporary tablespace temp file:
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/dbfile/DUP/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND OFF;
Also keep in mind that other steps may be required for your environment depending on your standards. For example, you might want to ensure the database SID is listed in the oratab file, or you might require the use of an SPFILE, enabling a password file, changing passwords, enabling archiving, taking a backup, adding entries into Oracle Net files, and so on.
The advantages of the cold backup approach to cloning a database are:
- It’s fairly simple and not much can go wrong (which simplifies troubleshooting any issues). There aren’t many moving parts to this technique.
- It uses a combination of SQL and operating system commands, so you don’t need to be familiar with any other tools to accomplish this task. A savvy manager, system administrator, or developer could easily use this approach to replicate a database.
The downside to this approach is that it requires you to shut down the source Oracle 12c database while it is being copied. Thus, if you work in an environment that can’t afford any downtime with the source database, then this approach isn’t appropriate.
