Quick Database Cloning

One of the common tasks of the DBAs is to clone a database. Very often there is a need to create a test or dev database which is a replica of Production database. In such cases Database cloning can be of great help. This process is quick and has simple steps.
Database cloning procedure can be used to clone/copy a database from one UNIX server to another server. This is one of the fastest ways to copy a database to another server.
Before you clone the database it is important to know that this procedure is Operating System dependent. You cannot clone a database from HP-UX to Windows server or Red Hat Linux server. The simple reason being the difference in Endianness. So before starting the cloning process, please make sure that the target and source OS are same or atleast follow same Endianness.

1. Create the script from Source database

Connect to the source database using SQL*PLUS (as SYSDBA) and execute the following command:

SQL> alter database backup controlfile to trace;

This command will generate a script containing a create database syntax in the trace file directory identified by USER_DUMP_DEST parameter in init.ora file.
The file looks like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 20
MAXLOGMEMBERS 3
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 115
LOGFILE
GROUP 1 (‘/app1/oradata/src_db/log1a.dbf’,
‘/app1/oradata/src_db/log1b.dbf’) SIZE 50M,
GROUP 2 (‘/app2/oradata/src_db/log2a.dbf’,
‘/app2/oradata/src_db/log2b.dbf’) SIZE 50M
DATAFILE
‘/appdata/oradata/src_db/system01.dbf’,
‘/appdata/oradata/src_db/srcdatabase.dbf’
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

2. Shutdown the source database

SQL> Shutdown immediate

3. Copy datafiles to destination server

Copy all the datafiles on the destination server. You may change the filenames if you wish, in this case you will also have to edit the controlfile to reflect the new datafile names of the destination server.

rcp /app1/oradata/src_db/*    newhost:/app1/oradata/dest_db
rcp /app2/oradata/src_db/*    newhost:/app2/oradata/dest_db
rcp /appdata/oradata/src_db/* newhost:/appdata/oradata/dest_db

4. Edit the Create database command

Change the Create controlfile clause in the Create database command generated in STEP1:

Old:

CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “DEST_DB” RESETLOGS

5. Remove extra clauses

Remove the “recover database” and “alter database open” clause from the script generated in STEP1:

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
#RECOVER DATABASE
# Database can now be opened normally.
#ALTER DATABASE OPEN;

6. Change the path of datafiles

Change the path of the datafiles in the script generate in STPE1 to reflect the path on the destination server

Old:

DATAFILE
‘/appdata/oradata/src_db/system01.dbf’,
‘/appdata/oradata/src_db/mydatabase.dbf’

New:

DATAFILE
‘/appdata/oradata/dest_db/system01.dbf’,
‘/appdata/oradata/dest_db/mydatabase.dbf’

7. Create the dump directories

Create the dump directories: bdump, cdump, udump

>cd /app/admin
>mkdir bdump cdump udump pfile

8. Copy the Initialization parameter file

Copy the old init.ora file to the destination server

rcp /app/admin/src_db/pfile/*.ora newhost:/app/oracle/admin/dest_db/pfile

9. Create the new database

SQL> @db_create_controlfile.sql

 

Finally, your cloned database is ready to use.

Expensive Merge Join Cartesian

Merge Join Cartesian is very expensive to Oracle. It is never useful except for the case when two tables have many to many join relation and they have very few rows. In general, Merge Join Cartesian tells us that there is something wrong with the query. Below given are some tips to troubleshoot and remove the deadly Merge Join Cartesian:

1. Check Join Conditions

Start with the table joins. Check if there are any join conditions missing between the given tables in the query. As I said, Cartesian product is only possible when two tables have Many to Many join relation. In normal scenarios, the joins should be of the type: (a.primary_key = b.foreign_key)
In most of the cases, adding proper join conditions will remove the Merge Join Cartesian from the plan.

2. Stale Statistics

The optimizer selects the best execution plan on the basis of the object statistics. If the statistics are stale, the plan might not be the optimal plan. Gather the statistics using DBMS_STATS package.

3. Optimizer Bug (_optimizer_transitivity_retain)

The issue with Optimizer regarding (_optimizer_transitivity_retain) is explained best by Oracle ACE Syed Jaffer: 

http://url-zip.com/sub 

Conclusion:

If the database design is correct and if proper join conditions are used, then there are very little chances that the Optimizer will use Merge Join Cartesian in the for the execution.