Thursday, May 19, 2011

Read-Only Tables in Oracle 11g

Sometime it is necessary to make the particular table read only . Prior to 11g ,a read only table was achieved by using the triggers,constraints and other method to prevent the data from being changed. In many of those cases only INSERT, UPDATE, and DELETE operations were prevented while many DDL operations were not. In oracle 11g ,Tables can be marked as read only, hence preventing the DML operation against. As performance point of view , read-only table performance is quite good because Oracle does not have the additional overhead of maintaining internal consistency, there may be a small, but measurable reduction in resource consumption .                                                                                                                                

When a table is in read-only mode, operations that attempt to modify table data are disallowed. The following operations are not permitted on a read-only table:
  • All DML operations on the table or any of its partitions.
  • TRUNCATE TABLE
  • SELECT FOR UPDATE
  • ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
  • ALTER TABLE SET COLUMN UNUSED
  • ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
  • ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING TABLE DATA for a type with read-only table dependents
  • FLASHBACK TABLE

The following operations are permitted on a read-only table :
  • SELECT
  • CREATE/ALTER/DROP INDEX
  • ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
  • ALTER TABLE for physical property changes
  • ALTER TABLE DROP UNUSED COLUMNS
  • ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME
  • ALTER TABLE MOVE
  • ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK
  • RENAME TABLE and ALTER TABLE RENAME TO
  • DROP TABLE
  • ALTER TABLE DEALLOCATE UNUSED
  • ALTER TABLE ADD/DROP SUPPLEMENTAL LOG
Here is the Demo of the read only table.

SQL> create table test (id number ,name varchar2(12));
Table created.

SQL> insert into test values (1,'joy');
1 row created.

SQL> insert into test values (2,'hope');
1 row created.

SQL> insert into test values (3,'peace');
1 row created.

SQL> insert into test values (4,'happy');
1 row created.

SQL> commit ;
 Commit complete.

SQL> select * from test ;
          ID      NAME
----------      ------------
         1      joy
         2      hope
         3      peace
         4      happy

SQL> select table_name,status,read_only from user_tables where table_name='TEST';
TABLE_NAME                     STATUS          REA
------------------                       -----------         -------
TEST                                     VALID             NO

Now placing the table "test" in read only mode .

SQL> alter table test read only;
Table altered.

SQL> insert into test values (5,'sunny');
insert into test values (5,'sunny')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST"

SQL> delete from test;
delete from test
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST"

SQL> truncate table test;
truncate table test
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST"

Now bringing the table "test"  in read write mode .

SQL> alter table test read write;
Table altered.

SQL> insert into test values (5,'sunny');
1 row created.

SQL> commit ;
Commit complete.

SQL> select * from test;

        ID NAME
---------- ------------
         1 joy
         2 hope
         3 peace
         4 happy
         5 sunny


Enjoy  :-)


Wednesday, May 18, 2011

Automatic Diagnostic Repository (ADR) in Oracle 11g

A special repository, named ADR (Automatic Diagnostic Repository) is automatically maintained by Oracle 11g to hold diagnostic information about critical error events. This repository is maintained in memory which enables database components to capture diagnostic data at its first failure for critical errors.

In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR . ADR is file based repository for diagnostic data like trace file,process dump,data structure dump etc.

The default location of  DIAGNOSTIC_DEST  is  $ORACLE_HOME/log, and if ORACLE_BASE is set in environment  then DIAGNOSTIC_DEST is set to $ORACLE_BASE.  The ADR can be managed via the 11g Enterprise Manager GUI  (Database Control and not Grid Control) or via the ADR command line interpreter adrci .

11g new initialize parameter DIAGNOSTIC_DEST decide location of ADR root.

  


Structure of ADR Directory is designed in such a way that uses consistent diagnostic data formats across products and instances, and a integrated set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances .

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and old style alert file in trace directory . Within ADR base, there can be many ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance. The location of an ADR home for a database is shown in the above pictures . Both the files can be viewed with  EM and ADRCI Utility.

SQL> show parameter diag
NAME               TYPE            VALUE
-------------     -------       -----------------
diagnostic_dest   string           D:\ORACLE

Below table shows us the new location of Diagnostic trace files

   Data                                Old location                        ADR location
-------------------         ------------------------        ---------------------
Core Dump                       CORE_DUMP_DEST                 $ADR_HOME/cdump
Alert log data                    BACKGROUND_DUMP_DEST   $ADR_HOME/trace
Background process trace   BACKGROUND_DUMP_DEST       $ADR_HOME/trace
User process trace             USER_DUMP_DEST                 $ADR_HOME/trace

We can use V$DIAG_INFOview to list some important ADR locations such as ADR Base, ADR Home, Diagnostic Trace, Diagnostic Alert, Default Trace file, etc.

SQL> select * from v$diag_info;
INST_ID          NAME                VALUE
----------    -----------          ---------------------------
1                   Diag Enabled        TRUE
1                  ADR Base             d:\oracle
1                  ADR Home            d:\oracle\diag\rdbms\noida\noida
1                  Diag Trace           d:\oracle\diag\rdbms\noida\noida\trace
1                  Diag Alert             d:\oracle\diag\rdbms\noida\noida\alert
1                  Diag Incident        d:\oracle\diag\rdbms\noida\noida\incident
1                  Diag Cdump          d:\oracle\diag\rdbms\noida\noida\cdump
1                  Health Monitor      d:\oracle\diag\rdbms\noida\noida\hm
1                  Active Problem Count       0
1                  Active Incident Count      0
10 rows selected.

ADRCI ( Automatic Diagnostic Repository Command  Interpreter) :
The ADR Command Interpreter (ADRCI) is a command-line tool that we use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:

  • Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).
  • Viewing Health Monitor reports.
  • Packaging of incident and problem information into a zip file for transmission to Oracle Support.
Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more .


ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

To use ADRCI in interactive mode :
Enter the following command at the operating system command prompt:
C:\>adrci
ADRCI: Release 11.1.0.6.0 - Beta on Wed May 18 12:31:40 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
ADR base = "d:\oracle"

To get list of adrci command type help command as below : 

adrci> help

 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL
 There are other commands intended to be used directly by Oracle, type  "HELP EXTENDED" to see the list

Viewing the Alert Log :  
The alert log is written as both an XML-formatted file and as a text file. we can view either format of the file with any text editor, or we can run an ADRCI command to view the XML-formatted alert log with the XML tags stripped. By default, ADRCI displays the alert log in your default editor

The following are variations on the SHOW ALERT command:

adrci > SHOW ALERT -TAIL
This displays the last portion of the alert log (the last 10 entries) in your terminal session.

adrci> SHOW ALERT -TAIL 50
This displays the last 50 entries in the alert log in your terminal session.

adrci> SHOW ALERT -TAIL -F
This displays the last 10 entries in the alert log, and then waits for more messages to arrive in the alert log. As each message arrives, it is appended to the display. This command enables you to perform "live monitoring" of the alert log. Press CTRL-C to stop waiting and return to the ADRCI prompt.Here are few Example :

adrci> show alert
Choose the alert log from the following homes to view:
1: diag\clients\user_neerajs\host_444208803_11
2: diag\clients\user_system\host_444208803_11
3: diag\clients\user_unknown\host_411310321_11
4: diag\rdbms\delhi\delhi
5: diag\rdbms\noida\noida
6: diag\tnslsnr\ramtech-199\listener
Q: to quit

Please select option:  4
Output the results to file: c:\docume~1\neeraj~1.ram\locals~1\temp\alert_932_4048_delhi_1.ado
'vi' is not recognized as an internal or external command,
operable program or batch file.
Please select option: q

Since we are on window platform so we don't have vi editor.So we have set editor for window say notepad.

adrci> set editor notepad
adrci> SHOW ALERT 
Choose the alert log from the following homes to view:
1: diag\clients\user_neerajs\host_444208803_11
2: diag\clients\user_system\host_444208803_11
3: diag\clients\user_unknown\host_411310321_11
4: diag\rdbms\delhi\delhi
5: diag\rdbms\noida\noida
6: diag\tnslsnr\ramtech-199\listener
Q: to quit

Please select option:
Output the results to file: c:\docume~1\neeraj~1.ram\locals~1\temp\alert_916_956_noida_7.ado
Here it will open the alert log file and check the file as per our need . 

If we want to filter the alert log file then we can filter as below :

adrci> show alert  -P "message_text  LIKE '%ORA-600%'"
This displays only alert log messages that contain the string 'ORA-600'.

Choose the alert log from the following homes to view:
1: diag\clients\user_neerajs\host_444208803_11
2: diag\clients\user_system\host_444208803_11
3: diag\clients\user_unknown\host_411310321_11
4: diag\rdbms\delhi\delhi
5: diag\rdbms\noida\noida
6: diag\tnslsnr\ramtech-199\listener
Q: to quit
Please select option:

Here, there is no ora-600 error in alert log file so it is blank 

Finding Trace Files  : 
ADRCI enables us to view the names of trace files that are currently in the automatic diagnostic repository (ADR). We can view the names of all trace files in the ADR, or we can apply filters to view a subset of names. For example, ADRCI has commands that enable us to:
  • Obtain a list of trace files whose file name matches a search string.
  • Obtain a list of trace files in a particular directory.
  • Obtain a list of trace files that pertain to a particular incident.
The following statement lists the name of every trace file that has the string 'mmon' in its file name. The percent sign (%) is used as a wildcard character, and the search string is case sensitive.

adrci> SHOW TRACEFILE  %pmon%
This statement lists the name of every trace file that is located in the  directory and that has the string 'mmon' in its file name:

adrci> SHOW TRACEFILE -RT
This statement lists the names of all trace files related to incident number 1681:

Viewing Incidents : 
The ADRCI SHOW INCIDENT command displays information about open incidents. For each incident, the incident ID, problem key, and incident creation time are shown. If the ADRCI homepath is set so that there are multiple current ADR homes, the report includes incidents from all of them.

adrci> SHOW INCIDENT

ADR Home = d:\oracle\diag\rdbms\noida\noida:
*******************************************************************
0 rows fetched

Purging Alert Log Content : 
The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.  The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:

adrci > purge -age 10080 -type ALERT

ADR Retention can be controlled with ADRCI :
There is retention policy for ADR that allow to specify how long to keep the data ADR incidents are controlled by two different policies:

The incident metadata retention policy ( default is 1 year )
The incident files and dumps retention policy ( Default is one month)
We can change retention policy using “adrci” MMON  purge data automatically on expired ADR data.

adrci> show control
The above command will show the  shortp_policy and longp_policy and this policy can the changed as below:

adrci> set control (SHORTP_POLICY = 360 )
adrci> set control (LONGP_POLICY = 4380 )

For more detail information on ADR visit below url :


Enjoy   :-) 


Monday, May 16, 2011

Again the hell ORA-00600 Occurs...

This is third time when i have face ORA-00600 . As i have mention last two scenario in my previous post . My production database was working fine .I have created it few days ago .There are about 100 users connected to database . I have scheduled the backup through OEM . On very next day while monitering I find that the backup scheduled through OEM get failed . On diagnosis , i find that the backup failed due to RMAN fatal error . Then i check my alert file and find the ORA-600 .  Even having the ORA-00600 error the production database is going fine.

Error on rman prompt is :

RMAN> run {
                               backup database plus archivelog ;
                              delete noprompt obsolete;
                     }
Starting backup at 11-MAY-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-00571: =======================================================
RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =======================================================
RMAN-00601 : fatal error in recovery manager
RMAN-03004 : fatal error during execution of command

Since i don't have any physical or logical backup of database,so i decided to take logical backup and again got the following error .

C:\>expdp system/xxxx@rosen schemas=CCI_HOAL directory=dpump dumpfile=hoal_11052011.dmp logfile=exp_hoal_11052011log.log
Export: Release 11.2.0.1.0 - Production on Fri May 11 11:27:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_07":  system/********@rosen schemas=CCI_HOAL directory=dpump dumpfile=hoal_11052011.dmp logfile=exp_hoal_11052011log.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.48 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39097: Data Pump job encountered unexpected error -600
ORA-39065: unexpected master process exception in MAIN
ORA-00600: internal error code, arguments: [ksuloget2], [0xFEBDD104], [0xFEBCEE54], [496], [0xFEBDD128], [], [], [], [], [], [], []

and it get's hang here.So i kill the job

Export> kill_job
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4583
ORA-06512: at line 1

So, we have decided to do some workround to get rid of these error. After some troubleshooting and diagnosis we have decided to reduce the size of the sga target . This error occurs when we set the size of sga target more than 1GB. Hence to solve this, I have decided to decrease the size of sga target. Here, in this scenario's i have not exactly solved the issue because it strongly recommended to get support from Oracle whenever we face ORA-00600 .Hope this may help you if the above error occurs.

To know more about ORA-00600. click here .


Enjoy     J J J


Cold Clonning Using controlfile backup in oracle 11g

Cloning is the ability to copy and restore a database image and to set up a database image as a new instance.The new instance can reside on the same system as the original database ,or on a different system.                               
                                                                                                                                                                                 
There are two method of Cloning :
1.) Cold Cloning : Cold cloning doesnot required recovery because the source database is shut-down normally before the image is created ,or on a different system .                                                                             
2.) Hot Cloning : Hot  Cloning  doesnot  include the database . The database is recovered  from the hot backup of the database, backup ,controlfiles and archivelogs

Reason for Cloning :
In every oracle development and production environment there will become the need to transport the entire database from one physical machine to another.   This copy may be used for development, production testing, beta testing, etc, but rest assured that this need will arise and management will ask  us  to perform this task quickly.  Listed below are the most typical uses :
There are various reasons for cloning an Oracle Applications system such as :

  • Creating a copy of the production system for testing updates.
  • Migrating an existing system to new hardware.
  • Creating a stage area to reduce patching downtime.
  • Relocating an Oracle database to another machine.
  • Renaming Oracle database. 
Terms used to describe the method

Production  Database          ===>> "Noida"
Database to be clonned      ===>>  "delhi"
Platform Used                     ===>>   Oracle 11GR1

Here is step by step method of Clonning    
                                                                                                                   
Step 1 : Create directory structure for clone database : 

Create directory structure for oracle database files.  In my case the all datafiles,controlfiles and redologs will be store in "D:\oracle\oradata\delhi" .  So make folder name "delhi" inside oradata folder similary in admin folder make new folder as "delhi" and inside that make new folder as adump,pfile,dpdump respectively.

Step 2 : Create pfile for clone database :

C:\>sqlplus sys/xxxx@noida as sysdba
SQL> create  pfile='C:\initdelhi.ora'  from  spfile ;
File created.

Pfile of the  ”noida”  database is : 
noida.__db_cache_size=109051904
noida.__java_pool_size=12582912
noida.__large_pool_size=4194304
noida.__oracle_base='D:\oracle'#ORACLE_BASE set from environment
noida.__pga_aggregate_target=104857600
noida.__sga_target=322961408
noida.__shared_io_pool_size=0
noida.__shared_pool_size=188743680
noida.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\noida\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\ORACLE\ORADATA\NOIDA\CONTROL01.CTL','D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL','D:\ORACLE\ORADATA\NOIDA\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='noida'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='D:\oracle\flash_recovery_area'
*.diagnostic_dest='D:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=noidaXDB)'
*.log_archive_dest=''
*.log_archive_dest_1='location=D:\archive\'
*.log_archive_format='noida_%s_%t_%r.arc'
*.memory_target=425721856
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'

Replace  the text  “noida”  with  “delhi “  and save it . Hence we have the pfile  for clone database.

delhi.__db_cache_size=109051904
delhi.__java_pool_size=12582912
delhi.__large_pool_size=4194304
delhi.__oracle_base='D:\oracle'#ORACLE_BASE set from environment
delhi.__pga_aggregate_target=104857600
delhi.__sga_target=322961408
delhi.__shared_io_pool_size=0
delhi.__shared_pool_size=188743680
delhi.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\delhi\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\ORACLE\ORADATA\delhi\CONTROL01.CTL','D:\ORACLE\ORADATA\delhi\CONTROL02.CTL','D:\ORACLE\ORADATA\delhi\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='delhi'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='D:\oracle\flash_recovery_area'
*.diagnostic_dest='D:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=delhiXDB)'
*.log_archive_dest=''
*.log_archive_dest_1='location=D:\archive\'
*.log_archive_format='delhi_%s_%t_%r.arc'
*.memory_target=425721856
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'

Step 3 : Configure Listener and Services for Clone Database 
Configure listener by using  netmgr  and  configure  tns  by using  netca .Reload the listener and check it status.
C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 13-MAY-2011 13:14:31
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
STATUS of the LISTENER
---------------------------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
Start Date                13-MAY-2011 11:12:13
Uptime                    0 days 2 hr. 2 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File         d:\oracle\diag\tnslsnr\ramtech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=1521)))
Services Summary...
Service "delhi" has 1 instance(s).
  Instance "delhi", status UNKNOWN, has 1 handler(s) for this service...
Service "noida" has 1 instance(s).
  Instance "noida", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

Check for TNS

C:\> tnsping delhi
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 13-MAY-2011 13:17:06
Copyright (c) 1997, 2007, Oracle.  All rights reserved.
Used parameter files:
D:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = delhi)))
OK (50 msec)

Step 4 : Create  Instance for  Clone database 

C:\>oradim  -new  -sid  delhi  -intpwd  delhi  -startmode  m
Instance created.

Step 5 : Startup the clone database at nomount stage 
C:\>sqlplus sys/delhi@delhi as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri May 13 13:23:11 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='C:\initdelhi.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  426852352 bytes
Fixed Size                  1333648 bytes
Variable Size             310380144 bytes
Database Buffers          109051904 bytes
Redo Buffers                6086656 bytes

Step  6 : Create Script for Controlfile
At the production  database  :
SQL> alter database backup controlfile to trace;
Database altered.

Now check the alert log file and find the name of the .trc file where the backup of controlfile  is. The following information are inside the .trc file.In my case the trace file contains following information.       
     
Trace file d:\oracle\diag\rdbms\noida\noida\trace\noida_ora_3952.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:52M/1015M, Ph+PgF:3270M/5518M, VA:1414M/2047M
Instance name: noida
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 3952, image: ORACLE.EXE (SHAD)
*** 2011-05-13 13:28:03.750
*** SESSION ID:(170.5) 2011-05-13 13:28:03.750
*** CLIENT ID:() 2011-05-13 13:28:03.750
*** SERVICE NAME:() 2011-05-13 13:28:03.750
*** MODULE NAME:(sqlplus.exe) 2011-05-13 13:28:03.750
*** ACTION NAME:() 2011-05-13 13:28:03.750
 Successfully allocated 2 recovery slaves
*** 2011-05-13 13:28:04.078
Using 545 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 11, block 2, scn 1582181
  cache-low rba: logseq 11, block 87608
    on-disk rba: logseq 11, block 89694, scn 1628819
  start recovery at logseq 11, block 87608, scn 0
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 1043Kb in 0.72s => 1.41 Mb/sec
Longest record: 13Kb, moves: 0/1112 (0%)
Change moves: 2/51 (3%), moved: 0Mb
Longest LWN: 404Kb, moves: 0/60 (0%), moved: 0Mb
Last redo scn: 0x0000.0018da92 (1628818)
----------------------------------------------
*** 2011-05-13 13:28:05.593
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 414/413 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 1510/1998 = 0.8
---------------------------------------------
*** 2011-05-13 13:28:05.593
KCRA: start recovery claims for 414 data blocks
*** 2011-05-13 13:28:05.609
KCRA: blocks processed = 414/414, claimed = 414, eliminated = 0
*** 2011-05-13 13:28:07.281
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
*** 2011-05-13 13:28:07.703
Completed redo application
*** 2011-05-13 13:28:08.750
Completed recovery checkpoint
IR RIA: redo_size 1068032 bytes, time_taken 193 ms
*** 2011-05-13 13:28:09.406
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 414/413 = 1.0
Max compares per lookup = 2
Avg compares per lookup = 1739/1923 = 0.9
----------------------------------------------
*** 2011-05-13 13:28:26.921
kwqmnich: current time::  7: 58: 26
kwqmnich: instance no 0 check_only flag 1
*** 2011-05-13 13:28:27.250
kwqmnich: initialized job cache structure
*** MODULE NAME:(Oracle Enterprise Manager.pin EM plsql) 2011-05-13 13:29:07.781
*** ACTION NAME:(start) 2011-05-13 13:29:07.781
 *** 2011-05-13 13:29:07.781
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
-- LOG_ARCHIVE_FORMAT=noida_%s_%t_%r.arc
-- DB_UNIQUE_NAME="noida"
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
-- FAL_CLIENT=''
-- FAL_SERVER=''
-- LOG_ARCHIVE_DEST_1='LOCATION=D:\archive\'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--     Set #1. NORESETLOGS case
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NOIDA" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\NOIDA\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\ORACLE\ORADATA\NOIDA\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\ORACLE\ORADATA\NOIDA\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\USERS01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 2 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''D:\rman_bkp\cf\%F''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','OFF');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''D:\rman_bkp\%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_636026939.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_749730106.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_750184743.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\NOIDA\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--     Set #2. RESETLOGS case
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NOIDA" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\NOIDA\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\ORACLE\ORADATA\NOIDA\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\ORACLE\ORADATA\NOIDA\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\USERS01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 2 DAYS');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''D:\rman_bkp\cf\%F''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','OFF');
-- Configure RMAN configuration record 6
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''D:\rman_bkp\%U''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_636026939.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_749730106.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'D:\ARCHIVE\NOIDA_1_1_750184743.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\NOIDA\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Edit the above the file  and make the following  changes  i.e  replace  “reuse”  with  “set”   and change the database  name from “noida”   to “delhi”  .  After  editing it looks like as :

CREATE CONTROLFILE  SET  DATABASE "DELHI"  RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\NOIDA\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\ORACLE\ORADATA\NOIDA\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\ORACLE\ORADATA\NOIDA\REDO03.LOG'  SIZE 50M
DATAFILE
  'D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\USERS01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF',
  'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF'
CHARACTER SET WE8MSWIN1252;

Save the above editted file as create_control.sql  .

Step 7 : Restore the datafiles :

Shut down the production database i.e,  “noida”   and copy all the datafile from  production to  clone database . In my case , I  have  copy my all datafile from  ‘D:\oracle\oradata\noida\’    to   ‘D:\oracle\oradata\delhi\’   .                                                                                                                           

Step 8 : Execute the control file script : 

Since  clone database i.e, delhi  is in nomount stage so execute the create_control.sql scripts.
SQL> @C:\create_control.sql
Control file created.
Hence  controlfile   is  created, and the database is in mount stage.

Step 9 : Finally open the clone database with resetlogs option : 

SQL> alter database open resetlogs;
Database altered. 

SQL> select name,open_mode from v$database;
NAME                   OPEN_MODE
--------                  ------------------
DELHI                    READ WRITE


Enjoy      J J J