A note on Statspack report

The TOP - 5 wait event section.
A very important point to keep in mind is, irrespective of whether
the performance is good or bad, we would see some or the other
wait events in this list, when there is a problem we check this
list and see which were the top 5 waits which just gives an idea
what the database was doing for the given snap interval, most often
we will have to drill down further to find what is causing the
sessions to wait for such a long time, or why suddenly there were
too many waits for a wait event, and try to correlate this with the
problem symptoms and operations running on the database during the
snapshot interval.

So to say, just looking at the statspack will not always reveal a
performance problem, and this is the reason we always would see more than
one report to compare and see the trend, once you know the problem we would
make the snapshot interval smaller to see how the pattern is and identify
the cause.

Every column in the top-5 event is important, what matters is how has
the trend changed since the time the performance was good to now when the
performance is bad.

for example if you see lots of waits for "enqueue", first identify if there
was a time when the events were never seen or the number of waits has increased,
When number of waits increase it could indicate that many sessions are requesting
the same enqueue, in general we know the concurrency(no. of users) has increased.

There is really no document available which will help you to analyze
a statspack report because most part of it is self explanatory.

You can still read these articles which provide a great insight into the usage of the tool
http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/

>> With respect to the Top SQLs, the sections are ordered by GETS, CPU
PARSE, DISK READS etc, again this should be used based on understanding
the problem and also the rest of the data in the report, once you know
for sure there is a lot of logical reads / buffer gets happening, you may
want to see the Top SQLs under Gets section and again you should establish
a pattern, like with Top 5 events, any SQL having High reads compared to others
in the given interval will be listed here(please note reading 2 blocks is greater
than reading 1 block however is really not a problem compared to 1000 Vs 10).

How to Create and Run a Oracle Java Stored Procedure

How to Create and Run a Oracle Java Stored Procedure

Setting up the JavaVM

1. Make sure the database is up and running.
2. Connect as user SYS or SYSTEM.
3. Perform the following queries to make sure that your JavaVM is installed and correctly initialized.

SQL> DESC DBMS_JAVA

SQL> SELECT table_name
FROM user_tables
WHERE table_name LIKE '%JAVA%';

SQL> SELECT count(*)
FROM dba_objects
WHERE object_type LIKE '%JAVA%';

Note : If any of these queries return no data the JavaVM is not installed correctly in the oracle database.

Check the installed JDK in the ORACLE_HOME.

1. Check that you have JDK installed and that it is in the path environment variable by executing following commands:
   
   > which java
> which javac

   
   These commands will return something similar to:
   
   > /bin/java
> /bin/javac

Creating Java stored procedures.

Create a Java stored procedures in the database
There are two ways to create a java stored procedure. The first one is to create a so called source class (*.java) outside the database, compile the class and load this class into the database. The second option is to create both the source and the compiled class within the database.

• Create a source code file outside the database.
  In our situation this class already exists as Hello.java (). As we only want to load the executable file (class-file) we first need to compile the source outside the database in a normal JDK environment. To prevent any errors from happening make sure to use the JDK supplied with the database.
1. Create the executable file of our example Hello.java.

> cd $ORACLE_HOME/javavm/examples/jsp/Hello.java
> javac Hello.java

Above steps will create a file called Hello.class.

2. In order to use the "class"-file we need to load this file into the database.

> $ORACLE_HOME/bin/loadjava -r -f -o -user scott/tiger Hello.class

• Create a source code file inside the database.
  When connected as user SCOTT execute following source code command. The database will create the executable file (class-file) automatically.

CREATE JAVA SOURCE NAMED "Hello"
AS
public class Hello {
public static String world() {
return "Hello World ";
}
};
/

Create a wrapper function.
A java stored procedure cannot be called directly from SQL or PL/SQL. In order to do so a so called wrapper function must be created. This wrapper function is an interface to access the java stored procedure. For our java example the wrapper function looks like:

CREATE OR REPLACE FUNCTION Hello RETURN VARCHAR2
as LANGUAGE JAVA NAME 'Hello.world() return String';
/

Test the wrapper function.
To check whether the java stored procedure and the wrapper function are installed correctly, execute following query:

SQL> select Hello from dual;

HELLO
--------------------------------------------------------------------------------
Hello world

Code for Hello.java

// Hello.java

public class Hello
{
public static String world()
{
return "Hello world";
}
}

Migrating to ASM Using RMAN

The following method shows how a primary database can be migrated to ASM from a disk based backup:

• Disable change tracking (only available in Enterprise Edition) if it is currently being used.
  

  SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

• Shutdown the database.
  

  SQL> SHUTDOWN IMMEDIATE

• Modify the parameter file of the target database as follows:
  
  • Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
  • Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
  
  
• Start the database in nomount mode.
  

  RMAN> STARTUP NOMOUNT

• Restore the controlfile into the new location from the old location.
  

  RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';

• Mount the database.
  

  RMAN> ALTER DATABASE MOUNT;

• Copy the database into the ASM disk group.
  

  RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';

• Switch all datafile to the new ASM location.
  

  RMAN> SWITCH DATABASE TO COPY;

• Open the database.
  

  RMAN> ALTER DATABASE OPEN;

• Create new redo logs in ASM and delete the old ones.
  
  
• Enable change tracking if it was being used.
  

  SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Form-based File Upload with Java Servlet or JSP

To upload files using a web form displayed in the browser to the target website, we use a special type of form called the Multipart form, it is the web server's capability
to handle multipart data, when using PHP you have built-in options, however when using Java based web server like Tomcat as in this illustration, you have to depend on 3rd party libraries, there are several providers, some charge a fee and some are free, in this article we will make use of libraries provided by Apache themselves.

"Apache Jakarta Commons FileUpload package "

It is open-source and can be downloaded free of charge over the Internet. We will demonstrate how to use the Apache Jakarta Commons FileUpload package to extract uploaded files submitted from a form. The techniques are the same for HTML and XHTML. If you are not familiar with JSP or Java Servlet we advise a primer course first, It is assumed the reader is comfortable using JSP and has adequate knowledge of servlets and knows how to deploy them atleast on apache tomcat.

In this article we'll use Commons FileUpload 1.1.1, later versions may have some changes.

Step 1 : Get the Libraries
To download the Apache Jakarta Commons FileUpload library, go to the home page of the "Apache Jakarta Commons FileUpload Project"
(http://jakarta.apache.org/commons/fileupload/), You will also need
"Commons IO library" (http://jakarta.apache.org/commons/io/)

Step 2 : Install the Apache Jakarta Commons FileUpload and the Commons IO libraries.

Installation is simply to copy the JAR files "commons-fileupload-1.1.1.jar" and "commons-io-1.2.jar" to the /WEB-INF/lib/ directory in the document root of your web application.

Note that JAR libraries stored in /WEB-INF/lib/ will be available to the containing web application only. If you want to share the libraries among all web applications installed in Tomcat (suppose you are using Tomcat 5 or Tomcat 4), the JAR files should be copied to the $CATALINA_HOME/shared/lib/ directory, where $CATALINA_HOME is the root of your Tomcat installation.

Step 3 : Create a HTML page which has a multipart form

form.html
~~~~~~~~
<form action="upload.jsp" method="post" enctype="multipart/form-data">

<input name="file" type="file">

<input type="submit">

</form>




Step 4 : Write the code for upload.jsp

<%@

page
import="org.apache.commons.fileupload.*,org.apache.commons.fileupload.servlet.ServletFileUpload,
org.apache.commons.fileupload.disk.DiskFileItemFactory,
org.apache.commons.io.FilenameUtils, java.util.*, java.io.File,
java.lang.Exception"

%>

<%

response.setContentType("application/vnd.wap.xhtml+xml");

%>

<html>

<head>
<title>File Upload
Example</title>
</head>


<body>
<h1>Data Received at the
Server</h1>
<hr/>

<p>

<%
if(ServletFileUpload.isMultipartContent(request)){

ServletFileUpload servletFileUpload = new ServletFileUpload(new

DiskFileItemFactory());

List fileItemsList = servletFileUpload.parseRequest(request);


String optionalFileName = "";

FileItem fileItem = null;


Iterator it = fileItemsList.iterator();

while(it.hasNext()){

FileItem fileItemTemp =(FileItem)it.next();

if(fileItemTemp.isFormField()){

%>

<b>Name-value
Pair Info:</b><br/>
Field
name:<%=fileItemTemp.getFieldName() %><br/>
Field
value: <%=fileItemTemp.getString()%><br/><br/>

<%if(fileItemTemp.getFieldName().equals("filename")){

optionalFileName= fileItemTemp.getString();

}

else{

fileItem=fileItemTemp;

}

if(fileItem!=null){

String fileName = fileItem.getName();

%>

<b>Uploaded
File Info:</b><br/>
Content
type:<%=fileItem.getContentType() %><br/>
Field
name: <%=fileItem.getFieldName() %><br/>
File
name: <%=fileName %><br/>
File
size: <%=fileItem.getSize()%><br/><br/>

<%

/*Save the uploaded file if its size is greater than 0. */

if(fileItem.getSize()> 0){

if(optionalFileName.trim().equals("")){

fileName=FilenameUtils.getName(fileName);

}

else{

fileName=optionalFileName;

}

String dirName ="/file_uploads/";

File saveTo = new File(dirName + fileName);

try {

fileItem.write(saveTo);

%>

<b>The
uploaded file has been saved successfully.</b>

<%

}catch(Exception
e){%>

<b>An
error occurred when we tried to save the uploaded
file.</b>

<%

}

}

}

}
%>


</p>
</body>
</html>



Step 5 : Test the application.

Start tomcat and invoke the form.html

http://localhost:8080/testing/form.html


Once you select a file and click submit
You should see a result page as follows




For more information you may visit this website
http://www.developershome.com/

How to Load File Content to a BLOB Field, and Unload BLOB Content to a File on the OS

Load contents of Binary File to a blob column of a table

Create these 2 tables

SQL> CREATE TABLE MYLOB ( ID NUMBER, PHOTO BLOB ) ;
SQL> CREATE TABLE TEMP_BFILE( B_FILE BFILE) ;

Insert bfile locator for the binary file into temp_file
SQL> insert into temp_bfile values ( bfilename('MYDIR','pic1.jpg'));

MYDIR is a directory object created with the CREATE DIRECTORY command.

Inserting lob into file
SQL> declare
tmp_blob blob default EMPTY_BLOB();
tmp_bfile bfile:=null;
dest_offset integer:=1;
src_offset integer:=1;
begin
select b_file into tmp_bfile from temp_bfile;
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
dbms_lob.createtemporary(tmp_blob, TRUE);
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
insert into mylob values(1,tmp_blob);
DBMS_LOB.CLOSE(tmp_bfile);
commit;
end;

Unload contents of a blob column of a table to a file on the filesystem

Offload the blob to a file
SQL> declare
tmp_blob blob default empty_blob();
begin
dbms_lob.createtemporary(tmp_blob, true);
select photo into tmp_blob from mylob;
sys.retrieve_lob_to_file (tmp_blob, 'MYDIR','mypic1.jpg');
end;
/

Note : MYDIR is a directory object created with the CREATE DIRECTORY command

Source for procedure retrieve_lob_to_file

CREATE OR REPLACE PROCEDURE retrieve_lob_to_file(temp_blob in BLOB, file_path in varchar2, file_name in varchar2) IS
data_buffer RAW (32767);
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
blob_length INTEGER;
chunk_size BINARY_INTEGER := 32767;
BEGIN
blob_length := dbms_lob.getlength(temp_blob);

filehandle := utl_file.fopen(file_path, file_name,'wb', 1024);

WHILE position < blob_length LOOP

dbms_lob.read (temp_blob, chunk_size, position, data_buffer);

utl_file.put_raw (filehandle, data_buffer);

position := position + chunk_size;

data_buffer := null;

END LOOP;

utl_file.fclose(filehandle);


EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;
/

Please note the plsql code in the above examples is provided for learning purposes only however it has been tested on a 10.2.0.1 database, and thorough testing is necessary before implementing this on production.

As per references on ORACLE-BASE, this should work well on 9i as well

http://www.oracle-base.com/articles/9i/ExportBlob9i.php

How To Rename a Oracle Database Using DBNEWID Tool (nid)

Introduction
============

The NID (New Database ID)is a new utility introduced with Oracle 9.2.  The NID
utility allows you to change only the DBNAME, or only the DBID or both
DBNAME and DBID in the same command.

1. If you change the DBID you must open the database with the RESETLOGS
   option, which re-creates the online redo logs and resets their sequence
   to 1.

2. If you change the DBNAME without changing the DBID then this does not
   require you to open with the RESETLOGS option, so database backups and
   archived logs are not invalidated.  You must change the DB_NAME initialization
   parameter after a database name change to reflect the new name.  Also,
   you may have to re-create the Oracle password file.  If you restore an old
   backup of the control file (before the name change, then you should
   use the initialization parameter file and password file from before the
   database name change.


Purpose
=======

It is useful to troubleshoot from the situation when duplicate database ID's
cause errors such as the following.

RMAN-20002: target database already registered in recovery catalog
     Cause: target database is already registered in the recovery catalog
    Action: If the target database is really registered, there is no need to
            register it again. Note that the recovery catalog enforces that
            all databases have a unique DBID. If the new database was created
            by copying files from an existing database, it will have the same
            DBID as the original database and cannot be registered in the
            same recovery catalog.

RMAN-20011, 1, "target database incarnation is not current in recovery catalog"
RMAN-20009, 1, "database incarnation already registered"

-or-

SQLPLUS> alter database mount standby database;
alter database mount standby database
*
ORA-01102: cannot mount database in EXCLUSIVE mode


Restrictions and Usage Notes
============================

The DBNEWID utility has the following restrictions:

- The utility is available only on the UNIX and Windows NT operating systems.
- The NID executable file should be owned and run by the Oracle owner because
 it needs direct access to the datafiles and control files.
- If another user runs the utility, then set the user ID to the owner of the datafiles and control files.
- The DBNEWID utility must access the datafiles of the database directly through a local connection.
 Although DBNEWID can accept a net service name, it cannot change the DBID of a nonlocal database.
- To change the DBID of a database, the database must be mounted and must have been shut down consistently
 prior to mounting. In the case of an Oracle Real Application Clusters database, the database must be
 mounted in NOPARALLEL mode. i.e set the init parameter CLUSTER_DATABASE=FALSE and then mount the database
- You must open the database with the RESETLOGS option after changing the DBID.
- Note that you do not have to open with the RESETLOGS option after changing only the database name.
- No other process should be running against the database when DBNEWID is executing.
 If another session shuts down and starts the database, then DBNEWID aborts.
- All online datafiles should be consistent without needing recovery.
- Normal offline datafiles should be accessible and writable.
 If this is not the case, you must drop these files before invoking the DBNEWID utility.
- All read-only tablespaces must be accessible and made writable at the operating system level prior
 to invoking DBNEWID. If these tablespaces cannot be made writable (for example, they are on a CD-ROM),
 then you must unplug the tablespaces using the transportable tablespace feature and then plug them back
 in the database before invoking the DBNEWID utility (see the Oracle9i Database Administrator's Guide).
- You can only specify REVERT when changing only the DBID.


Change Only the DBID
====================

1. Backup the database
2. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
   % nid TARGET=SYS/password@test_db
5. Shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization parameter
   file to the new database name
7. Create a new password file
8. Startup of the database with open resetlogs


Example:
========

1. C:\>set ORACLE_SID=TEST1BY
 C:\>sqlplus "/as sysdba"
 SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 24 11:16:52 2002
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 Connected to an idle instance.

 SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
 ORACLE instance started.

 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 Database mounted.
 Database opened.

2. check the DBID before change

 SQL> select dbid,name,open_mode,activation#,created from v$database;

       DBID NAME      OPEN_MODE  ACTIVATION# CREATED
 ---------- --------- ---------- ----------- ---------
 1395399949 TEST1BY   READ WRITE  1395404134 10-SEP-02


3. SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
 ORACLE instance started.

 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 Database mounted.
 SQL>exit

5. execute NID

 C:\>nid target=sys/oracle@TEST1BY

 DBNEWID: Release 9.2.0.1.0 - Production
 Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

 Connected to database TEST1BY (DBID=1395399949)

 Control Files in database:
   D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL

 Change database ID of database TEST1BY? (Y/[N]) => y

 Proceeding with operation
 Changing database ID from 1395399949 to 1397190693
 Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed
 Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed

 Database ID for database TEST1BY changed to 1397190693.
 All previous backups and archived redo logs for this database are unusable.
 Shut down database and open with RESETLOGS option.
 Succesfully changed database ID.
 DBNEWID - Completed succesfully.

6. SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.

7. create the new passwordfile

8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora
 ORACLE instance started.

 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 Database mounted.

9. SQL> alter database open resetlogs;
 Database altered.

10. check the new DBID

  SQL> select dbid,name,open_mode,activation#,created from v$database;

        DBID NAME      OPEN_MODE  ACTIVATION# CREATED
  ---------- --------- ---------- ----------- ---------
  1397190693 TEST1BY   READ WRITE  1397188261 10-SEP-02


NOTE: The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693

    
Change Only the DBNAME
======================

1. Backup the database
2. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
   % nid TARGET=SYS/password@test_db DBNAME=test_db2 SETNAME=Y
   - the value of DBNAME is the new dbname of the database
   - SETNAME must be set to Y. The default is N and causes the
     DBID to be changed also.
5. shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the initialization parameter
   file to the new database name
7. Create a new password file
8. Startup of the database(without resetlogs)

      
Change Both DBID and DBNAME
===========================

1. Backup of the database.
2. Shutdown IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
   % nid TARGET=SYS/password@test_db DBNAME=test_db2
   - the value of DBNAME is the new dbname of the database
5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database
6. Set the DB_NAME initialization parameter in the
   initialization parameter file to the new database name.
7. Create a new password file.
8. Startup of the database with open resetlogs


Example:
========

1. Shutdown the databse

 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

2. Mount the database

 SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
 ORACLE instance started.

 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 Database mounted.
 startup mount

3. Run NID utility

 C:\>nid target=sys/oracle@test1by dbname=test1by logfile=d:\oracle\base_test\nid.log

 DBNEWID: Release 9.2.0.1.0 - Production
 Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

 Connected to database TEST1 (DBID=849281895)

 Control Files in database:
   D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL

 Changing database ID from 849281895 to 1395399949
 Changing database name from TEST1 to TEST1BY
 Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed, wrote new name
 Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed, wrote new name
 Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed, wrote new name

 Database name changed to TEST1BY.
 Modify parameter file and generate a new password file before restarting.
 Database ID for database TEST1BY changed to 1395399949.
 All previous backups and archived redo logs for this database are unusable.
 Shut down database and open with RESETLOGS option.
 Succesfully changed database name and ID.
 DBNEWID - Completed succesfully.

4. Shutdown immediate of the database

5. Modfiy the file init.ora of the database
 change the parameter BD_NAME=TEST1 TO DBNAME=TEST1BY

6. create a new password file

 Usage: orapwd file= password= entries=
 where
  file - name of password file (mand),
  password - password for SYS (mand),
  entries - maximum number of distinct DBA and OPERs (opt),

7. mount the database

 SQL> startup mount pfile=d:\oracle\admin\test1by\pfile\inittest1by.ora
 ORACLE instance started.

 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 Database mounted.

8. Open the databse with RESETLOGS
 SQL> alter database open resetlogs;
 Database altered.

9. check the dictionary view for the new DBID and DBNAME
 select dbid, name, open_mode, activation#, created from v$database;


Other Useful Information
========================

Except the parameters like LOGFILE=, APPEND, HELP, there is a specific option
REVERT which allows us to go back on the steps of executing.  If the value of
REVERT is YES that means that a failed change of DBID should be reverted but a
successfully completed change of DBID cannot be reverted.  REVERT=YES is only
valid when a DBID change failed.  Default value is NO.


Documentation
=============

Oracle9i Database Utilities Release 2 (9.2)

Steps to Manually Clone a Oracle Database

Steps to clone the database

The following steps can be used to clone a
database on the same server or on another server,

We will clone a database with name proddb
the cloned database will have the name clonedb,
and clonedb will reside on the same server as proddb,

The cloning process mainly has 2 steps,
1. Take a cold backup of the production database
2. Restore the backup on to the clone destination and startup the clone database

Optionally you can rename the clone database, before starting it up.
the steps below describe the process of cloning a database and also renaming the
database.


Perform Cold Backup of the Production database

1. Note down the datafile, controlfile and redologfile locations on proddb

OS> sqlplus / as sysdba

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files

2. Shutdown the production database proddb

OS> sqlplus / as sysdba

SQL> shutdown immediate
Note : Ensure clean shutdown.

3. Copy the database files (datafiles, logfiles, controlfiles and parameter file) into the clone destination.

Using the information gathered in step 1, change directory to the production database directories

OS> cd /u01/

Note : for simplicity sake we'll assume all files are in the same directory

OS> cp /u01// /u01//

..
.. repeat the same for all datafiles, controlfiles and redologfiles
..

OS> cp $ORACLE_HOME/dbs/initproddb.ora $ORACLE_HOME/dbs/initclonedb.ora

4. Modify the parameter CONTROL_FILES in the pfile for clonedb to reflect
the new path of the control files.

Using your favorite text editor edit the pfile to reflect the new path
of the control files

vi initclonedb.ora

..
..
CONTROL_FILES = '/u01/clonedb/control/control01.ctl','/u01/clonedb/control/control02.ctl'
..
...



Rename the database as per requirements and start the clone database
We rename the database only if needed, if you plan to retain the
same name, the whole process simplifies to taking a cold backup
and restoring it in the target location and starting up the database,
however renaming becomes mandatory if the cloning happens on the same
server and in the same Oracle Home.

If the database version is less than version 9.2,
please go to section "Renaming database with version lower than 9.2"


Note :
Ensure DB_NAME is still proddb in initclonedb.ora, clonedb pfile,
Please note that the production database should be shutdown throughout the
clone process, this is because we are cloning the database on the same server
and using the same Oracle Home, if either one is different, database can be started
immediately after copying the files.


1. Mount the database and rename the datafiles and redolog files.

export ORACLE_SID=clonedb

OS> sqlplus / as sysdba
OS> startup mount
OS> alter database rename file '/u01//' to '/u01//'

2. Rename the database with the dbnewid (nid) utility
For information on how to use the DBNEWID utility, please refer this note
Metalink Note 224266.1 Title: How to Change the DBID and the DBNAME by using NID

Note : If you are using a database of version lesser than 9.2 then the
DBNEWID (NID) utility cannot be used as it was introduced only from 9.2


3. Edit the pfile and update the new database name

vi initclonedb.ora

---> Modify parameter DB_NAME to reflect the new database name.

also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.

4. Open the clone database.
5. Startup and open the production database.

Note : These steps may not be applicable to databases using OMF files as there are restrictions on renaming the datafiles, controlfiles and logfiles when they are Oracle Managed, it is Likely that you will receive a ORA-1276 when you rename a OMF file.

Renaming database with version lower than 9.2

Note : In this case it is not required to copy controlfiles or redologfiles
as they will be created newly, also note that when using this method to rename the database, the production database can be started immediately following the copying of the datafiles to the clone location.

1. Generate a controlfile backup trace file with the command
SQL> alter database backup controlfile to trace as '/path/filename.trc';

2. Edit the pfile and update the new database name

vi initclonedb.ora

---> Modify parameter DB_NAME to reflect the new database name.
also ensure you make necessary changes to the environment for ORACLE_SID, ORACLE_HOME
as needed.

3. Make a script to create a new controlfile

Editing the trace generated in
step 1 of this section, ensure to use the SET option to set the new database name.

Example
CREATE CONTROLFILE SET DATABASE "CLONEDB" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/clonedb/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/clonedb/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/clonedb/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/clonedb/system.dbf',
'/home/oracle/databases/clonedb/undo.dbf',
'/home/oracle/databases/clonedb/data.dbf'
CHARACTER SET WE8ISO8859P1
;

File saved as createcontrol.sql


4. Connect to the clone instance and start in the NOMOUNT stage.

export ORACLE_SID=clonedb
sqlplus /nolog
SQL> conn / as sysdba
SQL> startup nomount
SQL> @createcontrol.sql

Control File Created.

SQL> alter database open resetlogs ;


Please note you may have to add tempfiles to temporary tablespaces
as needed.

These steps complete the cloning process.