clone database in oracle

cloning or refreshing or renaming oracle database
A database clone is a complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.
The cloning refers to the replication of the server in order to have a backup, to upgrade the environment.

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue:
SQL>alter database backup controlfile to trace;

This will put the create database syntax in the trace file directory(UDUMP FOLDER, last created trace file in user trace files in udump folder). The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u01/oradata/oldlsq/log1a.dbf',
'/u01/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u01/oradata/oldlsq/log2a.dbf',
'/u01/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.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;

note:you have to change the yellow marked words thats why i marked those words in yellow.

STEP 2: Shutdown the old database

SQL>shut immediate;

STEP 3: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

open the dbclone_controlfile_creation.sql in a notepad or text editor and change the below yellow lines to green lines

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 4: Remove the “recover database” and “alter database open” syntax

# 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;

STEP 5: Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.
change the yellow marked lines to green marked lines in dbclone_controlfile_creation.sql
Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

otherwise create newfolder for clone database and copy and paste all datafiles and redolog files from primary database folder to the clone database destination folder.

/u01/oradata/oldlsq folder to /u01/oradata/newlsq folder.

STEP 7: Create the bdump, udump and cdump directories

cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile


STEP 8: Copy-over the old init.ora file

open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations
cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

STEP 9: Start the new database

SQL>startup nomount pfile=' /u01/admin/newlsq/pfile/newlsq.ora ' ;

SQL> @dbclone_controlfile_creation.sql

SQL>alter database open resetlogs;

SQL>create spfile from pfile;

STEP 10: Place the new database in archivelog mode
for enabling archivelog mode refer the following link:
http://rajeshkumar-oracledba.blogspot.com/2008_10_01_archive.html

thanks for reading this documentation and visiting this blog

references:
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://sabdarsyed.blogspot.com/2008/08/cloning-and-refreshing-oracle-database.html
http://www.sap-img.com/oracle-database/cloning-database-from-hot-backup.htm
http://www.dba-oracle.com/oracle_news/2004_12_14_webb.htm
http://www.club-oracle.com/forums/steps-to-clone-oracle-applications-database-from-prod-to-test-t10/s-to-clone-oracle-applications-database-from-prod-to-test-t10/https://students.kiv.zcu.cz/doc/oracle/em.102/b16227/oui7_cloning.htm
http://www.builderau.com.au/strategy/soa/Oracle-Tip-Use-DBCA-to-clone-a-database/0,2000064882,339130378,00.htm-Use-DBCA-to-clone-a-database/0,2000064882,339130378,00.htm
http://www.pythian.com/news/1041/oracle-silent-mode-part-3-cloning-software-and-databases
http://beyondoracle.wordpress.com/2009/01/07/move-clone-copy-database-with-rman/
http://download-west.oracle.com/docs/cd/B19306_01/em.102/b16227/oui7_cloning.htm
http://www.my-whiteboard.com/oracle-dba/use-rman-duplicate-to-restore-oracle-database-to-another-server.html
http://www.shutdownabort.com/quickguides/clone_hot.php

oracle architecture

oracle9i server: an overview
The oracle server consists of two major components---the database and the instance. Database is a confusing term that is often used to represent different things on different platforms; the only commonality is that is something to do with data. In oracle, the term database represents the physical files that store data. An instance comprises the memory structures and background processes used to access(from the physical database files). Each database should have at least one instance associated with it. It is possible for multiple instances to access a single database; this is known as the Real Application Cluster configuration.
You use the oracle database,which is a collection of data,to store and retrieve information. The database consists of logical structures and physical structures. Logical structures represent the components that you can in the oracle database( such as tables,indexes, and so on), and physical structures represent the method of storage that oracle uses internally ( the physical files), so that the logical structures can be defined identically across different hardware and operating system platforms.
Logical storage structures
oracle logically divides the database into smaller units to manage,store, and retrieve data effeciently. the following paragraphs give you an overview of the logical structures;
Tablespaces The database is logically divided into smaller units at the highest level called tablespaces. A tablespace commonly groups related logical structures together. For example, you might group data specific to an application or a function together in one or more tablespaces. This logical division helps to administer a portion of the database without affecting rest of it. Each database should have one or more tablespaces. When you create a database, oracle creates system tablespace as a minimum requirements.
Blocks A block is the smallest unit of storage in oracle. A block is usually a multiple of the operating system block size. A data block corresponds to a specific number of bytes of storage space. The block size is based on the parameter DB_BLOCK_SIZE and is determined when the database is created.
Extents An extent is the next level of logical grouping, it is a grouping of contiguous blocks, allocated in one chunk.
Segments A segment is a set of extents allocated for logical structures such as tables,indexes, clusters and so on. Whenever you create a logical structure, oracle allocates a segment, which contains at least one extent, which in turn has at least one block. A segment can be associated to only one tablespace.
There are four types of segments:
Data segments Store the table(or cluster) data. Every table created will have a segment allocated.
Index segments Store the index data. Every index created will have an index segment allocated.
Temporary segments Are created when oracle needs a temporary work area, such as sorting, during a query, and to complete execution of SQL statement. These segments are freed when the execution completes.
Undo segments Store undo information.When you roll back the changes made to the database, undo records in the undo tablespace are used to undo the changes.
A schema is a logical structures that groups the database objects. A schema is not directly related to a tablepace or any other logical structure. The objects that belong to a schema can reside in different tablespaces, and a tablespace can have objects that belong to multiple schemas. Schema objects include structures such as tables, indexes, synonyms, procedures, triggers, database links and so on.
Oracle physical storage structures
the physical database structure consists of three types of physical files:
#datafiles
#redo log files
#control files
Datafiles contain all the database data. every oracle database should have one or more datafiles. each data file is associated with one and only one tablespace. A tablespace can consist of one or more datafiles.
Redo log files record all changes made to data. Every oracle database should have two or more redolog files, because oracle writes to the redo log files in a circular fashion. If a failure prevents a database change from being written to a datafile, you can obtain the changes from the redo log files; therefore changes are never lost. Redo logs are critical for database operation and recovery from a failure. Oracle allows you to have multiple copies of the redo log files(preferably on different disks). This feature is known as multiplexing of redologs, a process in which oracle treats the redolog and its copies as a group identified with an integer, known as redo log group.
Control files Every oracle database has at lieast one control file. It maintains information about the physical structure of the database. The control file can be multiplexed, so that oracle maintains multiple copies. It is critical to the database. The control file contains the database name and timestamp of database creation as well as the name and location of every datafile and redolog file.
Oracle Memory Structures
The memory structures are used to cache application data, data dictionary information ( metadata--information about the objects, logical structures, schemas, privileges, and so on, Structured Query Language(SQL) commands, PL/SQL and Java program units, transaction information, data required for exection and individual database requests, and other control information. Memory structures are allocated to the oracle instance when the instance is started. The major memory stuctures are known as the System Global Area( also called the Shared Global Area) and the Program Global Area ( also called Private Global Area or the Process Global Area).
reference: OCA/OCP Oracle 9i DBA Fundamentals I study guide by Biju Thomas and Bob Bryla, BPB Publications.

logminer

logminor is an oracle utility. using logminor one can query the contents of online redolog files and archived log files . it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.
steps for configuring logminer:
step 1:
To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled
SQL> SELECT LOG_MODE FROM V$DATABASE;
OR
SQL> ARCHIVE LOG LIST;
FOR LOGMINER archivelog must be in archive log mode,
for enabling the archive logmode and automatic archiving using following steps:
http://rajeshkumar-oracledba.blogspot.com/2009/04/archivelog-mode-enable-and-disable.html
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archivedestination D:\oracle\ora92\RDBMS
Oldest online log sequence 46
Next log sequence to archive 48
Current log sequence 48
Database Supplemental Logging:
Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the REDO operations associated with DML changes. It ensures that LogMiner (and any products building on LogMiner technology) have sufficient information to support chained rows and various storage arrangements such as cluster tables. In most situations, you should at least enable minimal supplemental logging.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP SUP
--- --- ---
NO NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
To enable identification key logging, execute the following statement:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP SUP
--- --- ---
YES YES YES
note: more information about supplemental log
http://web.deu.edu.tr/oracle/B10501_01/server.920/a96521/logminer.htm#25840
step:2
add the parameter utl_file_dir in pfile or spfile for creating directory for logminer dictionary.
SQL> alter system set utl_file_dir='d:\oracle\oradata\jay' scope=spfile;
System altered.
you can create logminer dictionary in any destination as your wish.
important:after this , shutdown and startup the database.
SQL> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string d:\oracle\oradata\jay
step:3
creating logminer dictionary.
SQL> exec dbms_logmnr_d.build('dictionary.ora','d:\oracle\oradata\jay');
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select thread#,sequence#,completion_time from v$archived_log
2 order by sequence# desc;
THREAD# SEQUENCE# COMPLETION_TIME
------------ -------------- -------------------------
1 47 2009-04-20 17:01:07
1 46 2009-04-20 16:20:51
1 45 2009-04-20 16:03:50
1 44 2009-04-20 15:21:27
.......
.......
47 rows selected.
step:4
SQL> exec dbms_logmnr.add_logfile('D:\oracle\ora92\rdbms\ARC00047.001',dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
step:5
Now from the below view, make sure you have all the registered logs available for mining.
SQL> SELECT LOG_ID,FILENAME FROM V$LOGMNR_LOGS;
LOG_ID FILENAME
------ ----------------------------------------
46 D:\oracle\ora92\rdbms\ARC00046.001
FIND THE SCN'S:
Using the below view's find the first scn and high scn to mine from the registered logs.
From the above out gather the details and add it to the below logminer session :
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select low_time, high_time, low_scn, next_scn from v$logmnr_logs;
LOW_TIME HIGH_TIME LOW_SCN NEXT_SCN
------------------- ------------------- ---------- ----------
2009-04-20 16:03:48 2009-04-20 16:20:42 522290 543854
SQL> exec dbms_logmnr.start_logmnr(dictfilename => 'd:\oracle\oradata\jay\dictionary.ora', starttime => to_date('2009-04-20 16:03:48','yyyy-mm-dd hh24:mi:ss'),endtime => to_date('2009-04-20 16:20:42','yyyy-mm-dd hh24:mi:ss'));
PL/SQL procedure successfully completed.
SQL> create table logmnr_table as select * from v$logmnr_contents;
Table created.
Example: Using LogMiner to Calculate Table Access Statistics
SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SCOTT' AND SEG_NAME = 'EMPLOYEES';
SQL> SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits
FROM V$LOGMNR_CONTENTS
WHERE SEG_NAME NOT LIKE '%$'
GROUP BY SEG_OWNER, SEG_NAME;
for more information and reference:
http://web.deu.edu.tr/oracle/B10501_01/server.920/a96521/logminer.htm