Grid Computing on Windows

At Solutia, the IT goal is to get information to the people who need it, any time they need it. Solutia's high?volume manufacturing facilities are humming 24 hours a day, so critical database information must be accessible every minute. Downtime translates into lost production time, which ultimately results in lost revenues for the company.

At the core of Solutia's IT strategy is the simple idea that the required information must be easily available at a low cost. In addition, as the company grows, the capacity to manage and access the information must grow along with it. With 6,000 employees scattered across five continents, Solutia needs a scalable, reliable, high?performance database solution that can work with nearly any application in any location.

In addition to addressing its technical requirements, Oracle RAC enables Solutia to meet it business goals of cost and quality leadership, market innovation, and improving profits.

Get this white paper at: http://searchoracle.bitpipe.com/data/document.do?res_id=1207851849_349

Read More

Oracle Database Backup with RMAN

Backup related technical terms

Main category of doing database backup

• Whole database backups: a backup of every datafile in the database, plus the control file. Whole database backups are the most common type of backup.
  
• Partial database backups: backup of either tablespaces ,datafiles, control files or Archived Redo Log but not the whole database.
  

Backup Type

• Full backup : every time the backup has been doing it will create a whole database.
  
• Incremental backup : backup only the updated data, there must be an old backup file for comparison purpose.
  Backup Mode
  
• Offline backup/consistent backup: doing backup while the database is offline. The files in the backup contain all the data taken from a same point in time. This type of backup does not require recovery after it is restored.
  
• Online backup/inconsistent backup/hot backup : doing backup while the database is online.
  Backup Format
  
• Image Copies: this type of backup is copying physical file, this kind of format the original file size would be the same as the copy file size. Which would take space.
  
• Backup set: this format is the backup default and only used for Oracle database. The difference between Image Copied and Set Backup is Set Backup would do the backup only the block with data and we could configure the set level and the media type to keep the data on i.e. disk or tape
  

Recovery Manager (RMAN)

RMAN is an Oracle utility which come with Oracle Database from installation process. The main functionality of this utility is backup, restoring and recovering database.
You can also use operating system commands for backups and SQL*Plus for recovery. This method, also called user-managed backup and recovery, is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.

RMAN related technical terms

• Target Database: the database server we want to do the backup.
  
• Channel Process: the channel which RMAN would access the target database.
  
• RMAN Repository: Path that keep RMAN’s information, which will hold backup information of each instance. The default path is configured in control file of each instance. We could create a new database for the use of this purpose. With this new database we could keep the backup’ information of every instances we would do the backup
  
• Flash Recovery Area: The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area. RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape
  
• Recovery catalog: The database the store recovery catalog schema, which contain metadata that RMAN use for backup and recovery
  

RMAN Configuration Parameters

• DB_RECOVERY_FILE_DEST_SIZE: set how much flash_recovery size could go.
  
• DB_RECOVERY_FILE_DEST: set the path that keep backup set.
  

RMAN Related View List

• V$RECOVERY_FILE_DEST: This view shows the position of flash recovery.
  
• V$FLASH_RECOVERY_AREA_USAGE : this view shows how much the space had been taken for each file.
  

Physical File which RMAN could do the backup

• Database file = { data file, control file, spfile }
  
• Archived log file
  

Note: password file, network configure file (tnsname.ora, listener.ora, sqlnet.ora) we need to do manual backup.

Basic RMAN command

Start & Exit RMAN
# RMAN target /[@] [nocatalog | catalogname]
# RMAN
RMAN> EXIT

run script RMAN from file
# RMAN target / @file1
RMAN> @file1

command to validate RMAN syntax
# RMAN CHECKSYNTAX
# RMAN CHECKSYNTAX @file1

command to display parameters
RMAN> show ;
RMAN> SHOW ALL;
RMAN> CONFIGURE CLEAR;

Media management command

Specify the media
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO ;

Specify to compress the backup
RMAN> CONFIGURE DEVICE TYPE BACKUP TYPE TO ;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;
some of media managers need to configure through parameter.
RMAN> CONFIGURE CHANNEL DEVICE TYPE SBT PARMS=’ENV=mml_env_settings’;

backup data file command
RMAN> BACKUP DATABASE;

backup tablespaces
RMAN> BACKUP TABLESPACE system, users, tools;
RMAN> BACKUP DEVICE TYPE sbt MAXSIZE=10M TABLESPACE user, tool;

Backup data file
RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY ‘/tmp/system.dbf’;
RMAN> BACKUP DEVICE TYPE sbt TABLESPACE user INCLUDE CURRENT CONTROLFILE;
RMAN> BACKUP AS BACKUPSET DATAFILE
’ORACLE_HOME/oradata/trgt/users01.dbf’,
’ORACLE_HOME/oradata/trgt/tools01.dbf’;

to be able to set the incremental backup, which is level 1 we need to do level 0 first. In other word, we need to do the first backup with level 0. There is one caution to be made, that is when we do the incremental backup, the backup file level 0 และ 1 need to be together so we could completely do the restoration and recovery.
RMAN> BACKUP INCREMENTAL LEVEL [0 | 1] CUMULATIVE DATABASE;

backup control file
Set the configuration so that it could do the auto backup control file and parameter file when there were some changes and when there is some disasters RMAN would recover for that particular instance.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ;

change the autobackup default name
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE to ‘?/oradata/cf_%F’;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE clear;

to override autobackup value for any particular session (use run block on RMAN prompt)
RMAN> SET CONFIGURE AUTOBACKUP FORMAT
Backup current control file
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> BACKUP CURRENT CONTROLFILE TO ’/backup/curr_cf.copy’;

Backup control file
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT ‘/tmp/control01.ctl’;
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY ‘/tmp/control01.ctl’;

backup parameter file
RMAN> BACKUP SPFILE;
RMAN> BACKUP DEVICE TYPE sbt SPFILE;

backup archived log file
RMAN> BACKUP ARCHIVELOG …;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN ’SYSDATE-31’ AND ’SYSDATE-7’;

restore command
RMAN> RESTORE DATABASE;
RMAN> RESTORE TABLESPACE users;
RMAN> RESTORE DATAFILE <ชื่อ datafile>;
RMAN> RESTORE DATAFILE 7;


recovery command
RMAN> RECOVER DATABASE;
RMAN> RECOVER TABLESPACE users;
RMAN> RECOVER DATAFILE ;
RMAN> RECOVER DATAFILE 7;

backup sample
Backup Database including Archivelog file plus delete archive log file from backup
# RMAN TARGET backup_admin/backup_admin NOCATALOG
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

Backup Database including Control file plus delete un-updated backup.

RMAN> backup incremental level 0 cumulative device type disk tag '%TAG' database include current controlfile;
RMAN> allocate channel for maintenance type disk;
RMAN> delete noprompt obsolete device type disk;
RMAN> release channel;

restore & recovery sample
Recovering whole database
RMAN> STARTUP FORCE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

Recovering tablespace
RMAN> SQL 'ALTER TABLESPACE users OFFLINE';
RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Recovering Datafiles 7
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';

Backup Control File to Trace
To do trace backup control file for recovery when control file is corrupted
for OEM go to “Controlfiles” menu then “Backup To Trace”;
or you could use command line as below.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The result would be kept as configured parameter USER_DUMP_DEST
the default name is _ora_pid.trc
and default path is /admin//udump
the trace file would contain the sql command that could recreate the control file .

Read More

ORA-01422: exact fetch returns more than requested number of rows

ORA-01422: exact fetch returns more than requested number of rows

Cause: The number specified in exact fetch is less than the rows returned.

Action: Rewrite the query or change number of rows requested

---

With Oralce ORA-01422 error the cause would be you tried to execute a SELECT INTO statement and more than one row was returned. This error is on oracle PL/SQL lanugauge.

Here are the option that you could do.
1. rewrite your sql statement so it return only one record.
2. use an exception in case of unexpected scenario.
The exception syntax would look like this
Syntax:
BEGIN

your sql statement here
ie. select a, b, c into d, e, f from i;
EXCEPTION
WHEN exception1 [OR exception2...]] THEN
...
[WHEN exception3 [OR exception4...] THEN
...]
[WHEN OTHERS THEN
...]

your code need to be something like

BEGIN

your sql statement here
ie. select a, b, c into d, e, f from i;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
do something ie. raise error
WHEN OTHERS THEN
do something
END;

3. use cursor for more information on cursor take a look at
http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php.
http://www.psoug.org/reference/ref_cursors.html

Read More

ORA-00001: unique constraint (string.string) violated

ORA-00001: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction or do not insert the key.

---

the sample message might be as below

insert into student(pk_id, fieldname) values(1, '')
*
ERROR at line 1:
ORA-00001: unique constraint (PROMIS.SYS_C0012876) violated

With the example above we knew that it must be at table student. But if the query is more complex it might be hard to locate the problem constraint. If you already know where the prob is go to step 2 otherwise go to step 1

Step 1.> Locate the problem
To locate where might be the problem you may need to use the following query to find which table is your problem.
select distinct table_name
from all_indexes
where index_name = 'CONSTRAINT_NAME';

replace the CONSTRAINT_NAME with your constraint code (as the example above it is the text with the underline, SYS_C0012876. So the query would be:

select distinct table_name
from all_indexes
where index_name = 'SYS_C0012876';

and you will get the table name which cause the problem.

Step 2.> solve the problem
The options to resolve this Oracle ORA-00001 error are:

1. Modify your SQL so that a duplicate value is not created.
2. Change the constraint to allow duplicate values.
3. Diable the unique constraint.
4. Drop the unique constraint.

from 1-4 I would say it is likely that the first one is quite the right solution for me, as the constraint itself had the reason to be there from the first place. I suggest stick to number 1 to be on the save side, unless you know what you are doing.

Read More

ORA-00936: missing expression

ORA-00936: missing expression

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE (see case 2 below).

Action: Check the statement syntax and specify the missing component.


1. if you are using prepare statement to connect to oracle with vb or asp you might as get this error as using named parameters. The reason is that OLEDB doesn't support named parameters. e.g.


here i have the example of how to do it

Dim objCmd

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn

objCmd.CommandType = adCmdText


objCmd.CommandText = "{CALL procedurename(?,?,?,?,?,?)}"

objCmd.Parameters.Append objCmd.CreateParameter("@a", adVarChar, adParamInput,16)
objCmd.Parameters.Append objCmd.CreateParameter("@b", adVarChar, adParamInput,32)
objCmd.Parameters.Append objCmd.CreateParameter("@c", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@d", adVarChar, adParamOutput,255)
objCmd.Parameters.Append objCmd.CreateParameter("@e", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@f", adDouble, adParamOutput)

objCmd("@a") = sString
objCmd("@b") = sString2

objCmd.Execute

If ERR = 0 Then

o_errnum = objCmd("@c")
o_errStr = objCmd("@d")
o_usertype = objCmd("@e")
o_userid = objCmd("@f")

isValidLogin = True

End If
Set objCmd = Nothing

with this example you can use procedure to pass the paremeter to oracle and at the same time get the output parameters. The keyword is adParamOutput and adParamInput plus you need to specified parameter type make sure they are the same with your oracle procedure. Otherwise you will get an error.

2. If you are not running procedure. The other possibly cause of the error "ORA-00936: missing expression" might be that you tried to execute a SELECT statement and forget to the list of the columns in the SELECT statement.
i.e.

select
from tablename;

this also would raise you the same error

So the solution would be "Check the statement syntax and specify the missing component". If you are calling procedure using ? in your preparestatement not @ as I had already said "OLEDB doesn't support named parameters"

Read More

EXP-00056: ORACLE error 6550 encountered

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

You would get this error when you are trying to export your database with exp binary that is not compatible with your database
The cause of this error is, as the above message had already mentioned, that you are tring to export your database from client with oracle version higher
than the server version ie. using oracle 10g to export from oracle database 9i.



The solution are
- first option: you need exp program with the same version (or lower one) as your database, which mean
you need to find the client machine that have the same oracle version or lower installed
or
you may need to reinstall your oracle client.

- second option: if you have an access to your server, physically or remotely, simply just logon and use the exp command from the command line.

Read More

Step by Step Installing Red Hat Linux on VmWare

Installing Red Hat over VmWare - part 1




Installing Red Hat over VmWare - part 2





Installing Red Hat over VmWare - part 3




Installing Red Hat over VmWare - part 4

Read More

SUN'S REFERENCE ARCHITECTURE FOR ORACLE 11g GRID

Sun's Reference Architecture for Oracle 11g Grid is a portfolio of Sun and third-party products designed to reduce the risks, uncertainty, and costs associated with implementing an Oracle database within a grid computing environment. Designed, tested, implemented, and tuned at the Sun Competency Center for Oracle in Menlo Park, California, the reference architecture consists of recommended, integrated hardware and software stacks for a proven grid database solution. Along with this architecture, Sun provides a best practices framework for obtaining optimal performance, availability, and resiliency.

The most compelling benefits of Sun's Reference Architecture for Oracle 11g Grid come not from the individual components, but from the ability to integrate complementary elements - whether they come from Sun or another vendor. The combination of Sun Fire x64 servers with AMD Opteron processors and Cisco InfiniBand technology ensures higher database throughput, while the adoption of Solaris Cluster Advanced Edition for RAC enhances the reliability and availability of Oracle RAC 11g. The result is a solution that can help deliver the highest service levels and satisfy the most demanding business requirements of a database grid.

Get this white paper at:
http://searchoracle.bitpipe.com/data/document.do?res_id=1200499879_537

Read More

Oracle 10g step by step installation video on windows platform

I've just found a good tutorial video on how to install Oracle 10g step by step on windows platform from youtube.com. There are 4 videos altogether. These 4 video are a a basic oracle installation good for oracle newbie. Have a look for yourself.


Step by step guide on installation oracle 10g database part I



Step by step guide on installation oracle 10g database part II





Step by step guide on installation oracle 10g database part III




Step by step guide on installation oracle 10g database part IV



Reference:youtube.com

Read More

Testing Oracle 10g RAC Scalability

I have just found a good video on youtube about Oracle 10g RAC Scalability, This video is very informative. If you are considering of deploying Oracle RAC on your organization this is a cool one to watch. Below is the detail I grab from youtube video description:

Oracle Real Application Clusters (RAC) offer businesses the ability to create an environment where more than one database server, or node, can be setup to manage an Oracle database. Organizations often face multiple challenges when implementing RAC and maintaining RAC configurations, including:

Determining how many database servers are needed for sustained optimal
performance in a cluster
Defining the proper testing methodology of a cluster configuration for load testing, scalability and availability
This Webcast will provide you with:

A proven methodology for determining the most optimal configuration for your Oracle RAC environment.
A real-world scenario in which Quest's solutions were leveraged for Oracle RAC to conduct diagnostic and benchmark tests on Oracle Real Application Clusters.

and the video is here you don't have to leave to youtube to watch it, just click on the play icon.

Video Title: Testing Oracle 10g RAC Scalability.
Time: 35.49 minutes.

Read More

Ora Tip: How much did you assign your memory for oracle sga and pga?

If you want to know how much does your oracle database taking up memory for SGA and PGA just login to your oracle database with the following command on the command prompt

if you can access to the server physically or remotely
sqlplus "/ as sysdba"

otherwise use this command

sqlplus "sys@ora_service as sysdba"

SQL>show SGA
you would see something like the following lines but difference on number.

Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 109055512 bytes
Database Buffers 96468992 bytes
Redo Buffers 2412544 bytes

this is how your system Global Area taking your memory space every time your database is running.

to see what is your SGA size just run the following command

SQL>show parameter pga

and you would get the folloing result

pga_aggregate_target big integer 75M

this is how much your pga consume your memory.

Read More

TNS-12535: TNS:operation timed out

There might be sometime that when you try to connect to Oracle Server and you get the error "TNS-12535: TNS:operation timed out". It seem that the server does not exist or the listener does not start or both of them. The first thing you need to have a look is
the tnsnames.ora which could be found at
- $ORACLE_HOME/network/admin for unix like OS ie. every distribute of Linux, Solaris etc.
- ..\oracle\product\10.2.0\ for Windows Platform

in this tnsnames.ora will look like this

ora_service_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA_Server)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA_SID)
)
)

there are 2 things you need to have a look there.

The first one is to check if your Ora_SID is correct, if you don't know please contact your dba admin and ask for it.

The Second thing you need to take a look at is the HOST part host/ip parth (HOST=....) this is where your oracle server is.

If both Ora_SID and HOST are correct then try to ping the server to see if you can reach the server (there might be the case that the network admin turn the ping port of for security reason though, in this case you are behind firewall).

To make sure if you are really behind the firewall try telnet the server with this syntax
telnet server_IP 1521 ( or whatever your Oracle listening port is). If you get the following error

Connecting To your server_ipd...Could not open connection to the host, on port 1521:
Connect failed

then 90% you are behind firewall, please contact your network administrator to turn the firewall on port 1521 on.

Read More

ORA-00600: internal error code, arguments: [19004],

If you ever come across the error code "ORA-00600: internal error code, arguments: [19004], [], []...." while running query ralated to any table, view, index
here is the solution for it

login to your oracle database using pl/sql tool

run the below command on the command prompt

1. in case of error on querying table

ANALYZE TABLE table_name delete statistics;

2. in case of error on querying view

put the command above with related tables in the view. i.e run analyze tables for each table that form that view.

reference:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4005.htm

Read More

Oracle RAC Test on VMWare Linux + openfiler SAN/NAS storage

For people who don't want to spend much money on SAN storage just for the purpose of testing Oracle RAC feature.
One of the option you can do is installing VMWare on your computer and set up 2 Linux virtual servers on it. You will also need another virtual server to set up as openfiler to act as SAN storage. The hardware requirement you need to have are 3 GB of memory, 50 GB of hard disk (excluding your host OS space).
For more information on how to do this you can drop in to http://www.oracledba.ca/ there are step by step + image guide for you.

Read More

Runing Oracle on Linux + VMWare

There are many website talked about oracle installation on Linux which is running on VMWare.
There are many comments I come across mentioned that this must be for the testing or developing purpose only.

I'd myself rather think differently. If you have computer with huge enough resources you can set it up as your virtual servers and running on your network along with physical servers. It depend of the art of tuning your VMWare , Linux, Oracle, with new oracle improved functionality it could be easier to manage. I myself, used to train my 10+ clients and had them connected to my laptop (at the time 2GB Memory), runing VMWare with Linux and Oracle installed, it worked just fine.

The thing is, it's on the art of put the right tools/machines for the right task. With well configuration of your Oracle, VMWare, Linux plus your host resources, your virtual servers will work like a charm.

Read More