Thursday 6 October 2011

Oracle Architecture

                                image
Overview of Primary Components
The Oracle architecture includes  many primary components, which are discussed below.
 
Oracle instance: An Oracle instance is the combination of the background processes and the memory structures. The instance must be started to access the database. When instance started, a System Global Area (SGA) is allocated and Oracle background processes are started.  
Oracle database: An Oracle database consists of operating system files, also called database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.
User and server processes: The user and server processes are the primary processes which involved when a SQL statement is executed, however, other processes can also help the server complete the processing of the SQL statement.

Sunday 2 October 2011

PCTFree & PCTUsed


PCTFree:
It is used to mention how much space should be Free in the Data Block for future update:

PCTUsed:
Its is used to mention that how much Data Block should be filled before any new insertion. This parameter worked when the data block reached PCT Free threshold and new insertion stop filling and then this block will start insertion when data block get empty until reached PCTUsed limit.

Lets suppose you have specified PCTFree as 10 % and PCTUsed as 20 %.
  • Oracle will keep inserting new rows until the space is reached 90 %. It will reserve the remaining 10%(PCTFree) for future updates.
  • To start adding new rows again to the data block, Oracle will check the space in the Data Block and the PCTUsed parameter.
  • When the Data Block space falls below 20 %(PCTUsed, Oracle will start adding new rows to the Data Block.

Sunday 25 September 2011

RMAN-06059: expected archived log not found

RMAN-06059: expected archived log not found RMAN attempted to backup an archive log file, but couldn't find it. This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed, etc. Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN: change archivelog all crosscheck; It is advisable to perform a full backup of the database at this point. When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.

Blogger Labels: RMAN,backup,archive,destination,database,disk,logs,error,options,crosscheck

Sunday 19 June 2011

SQL Operators

There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.

Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.
Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.
The below table describes each comparison operator.

 Operator
 

Logical Operators:

There are three Logical Operators namely AND, OR and NOT.
Logical operators are discussed in detail in the next section.

SQL WHERE Clause

The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.
So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.
Syntax of SQL WHERE Clause:
WHERE {column or expression} comparison-operator value
Syntax for a WHERE clause with Select statement is:
SELECT column_list FROM table-name
WHERE condition;

  • column or expression - Is the column of a table or a expression
  • comparison-operator - operators like = < > etc.
  • value - Any user value or a column name for comparison
For Example: To find the name of a student with id 100, the query would be like:
SELECT first_name, last_name FROM student_details WHERE id = 100;
Comparison Operators and Logical Operators are used in WHERE Clause. These operators are discussed in the next chapter.
NOTE: Aliases defined for the columns in the SELECT statement cannot be used in the WHERE clause to set conditions. Only aliases created for tables can be used to reference the columns in the table.

How to use expressions in the WHERE Clause?

Expressions can also be used in the WHERE clause of the SELECT statement.
For example: Lets consider the employee table. If you want to display employee name, current salary, and a 20% increase in the salary for only those products where the percentage increase in salary is greater than 30000, the SELECT statement can be written as shown below
SELECT name, salary, salary*1.2 AS new_salary FROM employee WHERE salary*1.2 > 30000;
Output:
name salary new_salary
Khurram 35000 37000
Faraz 35000 37000
Ayaz 30000 360000

Friday 17 June 2011

Setting up Oracle DataGuard for 10g

Technorati Tags:
[courtesy: avdeo.com]
Introduction
Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
You can manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.
Primary Database
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
Standby Database
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
A standby database can be either a physical standby database or a logical standby database:
Physical standby database
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.
Logical standby database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database by transforming the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.


For Detailed tutorial please click here

ORA-12547: TNS:lost contact


I get this error while installing Oracle 10g R2 on Redhat Enterprise Linux 5. So I install the following package and problem resolved:
libaio-0.3.105-2.i386.rpm
Download it from : http://rpm.pbone.net/index.php3/stat/4/idpl/5184007/com/libaio-0.3.105-2.i386.rpm.html

Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.

I get the following Exception before start installation of Oracle 10g R2 on Redhat Enterprise Linux 5 :
Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at sun.awt.X11GraphicsEnvironment.access$000(X11GraphicsEnvironment.java:53)
at sun.awt.X11GraphicsEnvironment$1.run(X11GraphicsEnvironment.java:142)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:131)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:68)
at sun.awt.motif.MToolkit.<clinit>(MToolkit.java:93)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at java.awt.Toolkit$2.run(Toolkit.java:821)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:804)
at javax.swing.UIManager.initialize(UIManager.java:1262)
at javax.swing.UIManager.maybeInitialize(UIManager.java:1245)
at javax.swing.UIManager.getDefaults(UIManager.java:556)
at com.tibco.administrator.wizard.WizardDialog.setUIFont(WizardDialog.java:208)
at com.tibco.administrator.du.DomainUtility.a(DomainUtility.java:593)
at com.tibco.administrator.du.DomainUtility.main(DomainUtility.java:427)
 
 
So I just Issue the following command:
export DISPLAY=:0
and problem resolved

Uninstalling VMware Tools To remove VMware Tools from your Linux guest operating system


Uninstalling VMware Tools

To remove VMware Tools from your Linux guest operating system, log on as root (su -) and enter the following co
mmand:







  • From an RPM install
    $ rpm -e VMwareTools

  • Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr


    Edit the file $ORACLE_HOME/bin/dbstart to change this line. Issue following command:
    $ vi $ORACLE_HOME/bin/dbstart
    Change the following line:
    ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
    With the following:
    ORACLE_HOME_LISTNER=$ORACLE_HOME

    Set ORACLE_SID in Linux

    First check the oralce sid by:
    $ echo $ORACLE_SID
    If you want to change the SID then use following command:
    $ export ORACLE_SID=<database sid>

    Installing VMware Tools from the Command Line with the RPM Installer on Linux

    1- Power-On the virtual machine.
    2- From toolbar click VM -- >Install VMware Tools
    3- Right click on desktop and Open Terminal Window.
    4- Isuue the following command to mount the CD-Rom
    $ mount /dev/cdrom /mnt/cdrom
    Now there should be a CD-Rom Icon on desktop.
    Note: If you have a previous installation, delete the previous vmware-distrib directory before installing. The default location of this directory is
    /tmp/vmware-tools-distrib.
    5- Issue the following command
    $ cd /mnt/cdrom
    $ rpm –Uhv VMwareTools-5.0.0-<xxxx>.i386.rpm
    $ vmware-config-tools.pl
    Now VMware tool should start installation.
    6- Now at the end, unmount the CD-Rom
    $ umount /dev/cdrom
     
    Thats It..!!

    How to get the DBCA in Linux Environment


    Technorati Tags: ,,
    $ cd $ORACLE_HOME/bin
    $ dbca

    Saturday 4 June 2011

    Setting up Oracle DataGuard for 10g

    Setting up Oracle DataGuard for 10g


    [courtesy: avdeo.com]

    Introduction
    Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
    Data Guard Configurations:
    A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other. For example, you can have a standby database on the same system as the production database, along with two standby databases on other systems at remote locations.
    You can manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager.
    Primary Database
    A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.
    The primary database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
    Standby Database
    A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
    Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database.
    A standby database can be either a physical standby database or a logical standby database:
    Physical standby database
    Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database by recovering the redo data received from the primary database.
    Logical standby database
    Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database by transforming the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database. A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.


    For Detail plz click here

    Friday 20 May 2011

    Oracle 10g RAC On Windows 2003 Using VMware Server

    [Courtesy: http://www.oracle-base.com]

    This article describes the installation of Oracle 10g release 2 (10.2.0.1) RAC on Windows 2003 Server Standard Edition using VMware Server with no additional shared disk devices.

    Introduction

    One of the biggest obstacles preventing people from setting up test RAC environments is the requirement for shared storage. In a production environment, shared storage is often provided by a SAN or high-end NAS device, but both of these options are very expensive when all you want to do is get some experience installing and using RAC. A cheaper alternative is to use a FireWire disk enclosure to allow two machines to access the same disk(s), but that still costs money and requires two servers. A third option is to use VMware Server to fake the shared storage.
    Using VMware Server you can run multiple Virtual Machines (VMs) on a single server, allowing you to run both RAC nodes on a single machine. In additon, it allows you to set up shared virtual disks, overcoming the obstacle of expensive shared storage.
    Before you launch into this installation, here are a few things to consider.
    • The finished system includes the host operating system, two guest operating systems, two sets of Oracle Clusterware, two ASM instances and two Database instances all on a single server. As you can imagine, this requires a significant amount of disk space, CPU and memory. To complete this installation I used a dual 3.0G Xeon server with 4G of memory.
    • This procedure provides a bare bones installation to get the RAC working. There is no redundancy in the Clusterware installation or the ASM installation. To add this, simply create double the amount of shared disks and select the "Normal" redundancy option when it is offered. Of course, this will take more disk space.
    • During the virtual disk creation, I always choose not to preallocate the disk space. This makes virtual disk access slower during the installation, but saves on wasted disk space.
    • This is not, and should not be considered, a production-ready system. It's simply to allow you to get used to installing and using RAC.

    For Installation tutorial click here

    Tuesday 17 May 2011

    Oracle 10g RAC Versus DataGuard For High Availability


    Oracle 10g RAC Versus DataGuard For High Availability

    [courtesy: http://www.iheavy.com]


    Oracle has two very different technologies, each with it's own strengths and weaknesses that implement high availability solutions. In choosing between the two technologies, it's important to factor in the relevant risks, both small and large, to put the entire picture into perspective.

    Two Alternatives
    RAC or Real Application Clusters, is essentially an always-on solution. You have multiple instances or servers accessing the same database on shared storage in your network. With existing technology limitations, in practical terms, these different servers must be on the same local network, in the same datacenter.

    Oracle's DataGuard technology, formerly called Standby database in previous versions, provides a rolling copy of your production database. The standby database is started in read-only mode, constantly receiving change data, sent over from the production database, keeping it always in sync at all times, and at most only a few minutes behind. Were the production server to fail, that server could take over in less than the time the DNS change or IP swap would take. What's more the standby copy can be at another datacenter, or on another continent!

    For more detail....click here

    International Scores

    International Scores: "Get the latest scores of all the international cricket matches from Cricinfo. Add the Cricinfo International Scores widget now!"

    Thursday 12 May 2011

    SQL Alias


    SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable.
    For Example: To select the first name of all the students, the query would be like:
    Aliases for columns:
    SELECT first_name AS Name FROM student_details;
    or
    SELECT first_name Name FROM student_details;

    In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'.

    Wednesday 11 May 2011

    Learning SQL

    Click below links.

  • SQL Commands

  • SQL SELECT

  • SQL Alias

  • SQL WHERE

  • SQL Operators

  • SQL Logical Operators

  • SQL LIKE, IN

  • SQL ORDER BY

  • SQL Group Functions

  • SQL GROUP BY

  • SQL HAVING

  • SQL INSERT

  • SQL UPDATE

  • SQL DELETE

  • SQL CREATE TABLE

  • SQL ALTER TABLE

  • SQL Integrity Constrain

  • SQL JOINS

  • SQL VIEWS

  • SQL Subquery

  • SQL Indexes

  • SQL GRANT, REVOKE

  • Oracle Functions

  • SQL SELECT


     

    The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.
    Syntax of SQL SELECT Statement:
    SELECT column_list FROM table-name
    [WHERE Clause]
    [GROUP BY clause]
    [HAVING clause]
    [ORDER BY clause];

    • table-name is the name of the table from which the information is retrieved.
    • column_list includes one or more columns from which data is retrieved.
    • The code within the brackets is optional.

    SQL Commands

     

    SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

    • Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
    • Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
    • Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
    • Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

    Responsibilities of Database Administrators

     

    Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.

    A database administrator's responsibilities can include the following tasks:

    • Installing and upgrading the Oracle Database server and application tools
    • Allocating system storage and planning future storage requirements for the database system
    • Creating primary database storage structures (tablespaces) after application developers have designed an application
    • Creating primary objects (tables, views, indexes) once application developers have designed an application
    • Modifying the database structure, as necessary, from information given by application developers
    • Enrolling users and maintaining system security
    • Ensuring compliance with Oracle license agreements
    • Controlling and monitoring user access to the database
    • Monitoring and optimizing the performance of the database
    • Planning for backup and recovery of database information
    • Maintaining archived data on tape
    • Backing up and restoring the database
    • Contacting Oracle for technical support

    Writing SQL Statements

     

    • SQL statements are not case sensitive.
    • SQL statements can be on one or more lines.
    • Keywords cannot be abbreviated or split across lines.
    • Clauses are usually placed on separate lines.
    • Indents are used to enhance readability.

    Tuesday 10 May 2011

    Oracle Database Firewall Now Available

     

    Cost Effective Protection for Oracle and non-Oracle Databases

    Oracle Database Firewall, part of Oracle's comprehensive portfolio of database security solutions, is the first line of defense for both Oracle and non-Oracle databases. It monitors database activity on the network to help prevent unauthorized access, SQL injections, privilege or role escalation, and other external and internal attacks - all in real time. Based on innovative SQL grammar technology that can reduce millions of SQL statement into a small number of SQL characteristics, Oracle Database Firewall offers unmatched accuracy, scalability, and performance. Enforcement of positive (white lists) and negative (black lists) security models provides protection from threats without time consuming and costly false positives. Oracle Database Firewall also enables organizations to address SOX, PCI, HIPAA/HITECH, and other regulatory requirements without changes to existing applications or databases, and demonstrate compliance with built-in customizable reports.

    Click here for Official Page