There are several advantages of using ORACLE database over other database. The most important things are, 

• It does all the functionality like other database. User can create, delete, update, manage database; assign privileges; assign roles; security and backup and recovery.
• It provides both enterprise level products, patches, security and support as well as open source resources. 
• 
  

Customer Satisfaction

• One advantage of using Oracle databases is due to Oracles belief in customer satisfaction. For example, all Oracle databases are backward compatible. This allows businesses to upgrade their systems without a complete overhaul of their database system. This provides efficient and low-cost updates. In addition, new versions of Oracle databases provide new features while keep the popular features from older versions. This ensures that their product is based on the customer's function rather than what is cost-effective for Oracle.

Functionality

• Oracle databases are used for practically all corporation level applications. Thus, Oracle databases dominate the banking industry. Ten of the world's top banks use Oracle databases to conduct their business. This is primarily due to Oracle's functionality. They provide a combination of high-level technology and integrated business solutions. This is a perfect for practically all corporations that have huge amounts of data to store and access.

Reliability

• Another important advantage offered by Oracle databases are their reliability. Oracle is a database that delivers excellent performance when challenged with demanding tasks. The ACID test, which is an important tool used to ensure the integrity of data stored, was easily passed by Oracle databases. This test is important since reliable data storage is the main purpose of a database. Thus, Oracle databases have been proven to deliver high integrity of data storage.

Flashback Technology

• Oracle databases incorporate Flashback technology, which is a significant advantage. In the event of an application outage, due to any number of reasons, it is important not to lose data stored on a database system. Oracle's Flashback technology allows for efficient recovery of data incorrectly deleted or lost. Thus, Flashback technology essentially removes human error and increases database recovery time. In the end, this feature allows for a simplified management and administrative process.

Other Advantages

• Oracle databases have also successfully featured the four properties that all database systems must have. These four properties are atomicity, consistency, isolation and durability. All four of the properties are well maintained by Oracle databases, thus providing a reliable and competent database system.

Learn more »

There are four properties of database. They are as follows:

Atomicity

Atomicity refers to the ability of the database to guarantee that either all of the tasks of a transaction are performed or none of them are. Database modifications must follow an all or nothing rule. Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails.

Consistency

The consistency property ensures that the database remains in a consistent state before the start of the transaction and after the transaction is over (whether successful or not). For example, in a storefront there is an inconsistent view of what is truly available for purchase if inventory is allowed to fall below 0, making it impossible to provide more than an intent to complete a transaction at checkout time. An example in a double-entry accounting system illustrates the concept of a true transaction. Every debit requires an associated credit. Both of these happen or neither happen.
A distributed data system is either strongly consistent or has some form of weak consistency. Once again, using the storefront example, a database needs to provide consistency and isolation, so that when one customer is reducing an item in stock and in parallel is increasing the basket by one, this is isolated from another customer who will have to wait while the data store catches up. At the other end of the spectrum is BASE (Basically Available Soft-state Eventual consistency).
Weak consistency is sometimes referred to as eventual consistency, the database eventually reaches a consistent state. Weak consistency systems are usually ones where data is replicated; the latest version is sitting somewhere in the cluster, older versions are still out there. Eventually all nodes will see the latest version.

Isolation

Isolation refers to the requirement that other operations cannot access or see the data in an intermediate state during a transaction. This constraint is required to maintain the performance as well as the consistency between transactions in a database. Thus, each transaction is unaware of another transactions executing concurrently in the system.

Durability

Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases implement durability by writing all transactions into a transaction log that can be played back to recreate the system state right before a failure. A transaction can only be deemed committed after it is safely in the log.
Durability does not imply a permanent state of the database. Another transaction may overwrite any changes made by the current transaction without hindering durability.

Learn more »

DBA

1.     What do you mean by database architecture? Explain.

2.     What do you understand by database independence?

3.     Why is SQL plus popular? Explain its use with example.

4.     What do you mean by Redo Log files? How do we mange and monitor redo log files?

5.     What do you mean by backup, recovery and restoring of database? How do we enforce security in database?

6.     Explain what is view.  List its importance and characteristics.

7.     What is database corruption? Explain its causes and remedies.

8.     Explain steps required for RMAN.

9.     What is performance tuning? Explain its steps.

10.  What are performance-tuning reports? Explain with example.

11.  What is trigger? How is it created and why is it important? Explain with an example.

12. Explain parallel instance recovery.

13.  What do you mean by VPD? Why is it required?

14.  Describe the oracle architecture.

15.  What are the types of SQL?

16.  What are the roles and responsibilities of DBA?

17.  What is control file? Why is it important?

18.  What is ASM?

19.  What do you mean by auditing?

20.  Describe backup and recovery strategies.

Learn more »

The difference between system generated constraint naming and user generated constraint naming are as follows: There are two level of constraint naming:

• System generated system naming
• user defined system naming

Also there are two levels of defining constraint naming. They are:

• column level 
• table level

1. System generated, column level constraint 

   CREATE TABLE tbl_student_info (
   
   student_id_number primary key,
   FName varchar(25) NOT NULL,
   MName varchar(25),
   LName varchar(25) NOT NULL,
   Gender char(1) check (Gender IN('M','F','m','f')),
   DOB date NOT NULL,
   Email varchar (50) check (Email like '%@@'));
    example, 

          insert into tbl_student values(1,'Ram','bahadur','Karki','M','07/12/2012','ramg@gmal.com');

1. User defined with column level and table level defining constraints  

   CREATE TABLE course_info(
   
   course_id number constraint  course_info_course_id_pk primary key,
   MdName varchar(25) constraint course_info_MdName_nn NOT NULL,
   MdCredit number constraint course_info_MdCredit_nn NOT NULL,
   SsPeriod char(6) constraint course_info_SsPeriod_nn NOT NULL,
   constraint course_info_MdCredit_c check (MdCredit>=3),
   constraint course_info_SsPeriod_c check(SsPeriod IN ('SPRING','AUTUMN'))
   );
   
   Advantage of using user defined constraint
   When the database structure is large and contains huge amount of data, it helps in performance tuning. 
   
   

Learn more »

Following are the important database administration questions generally asked in the examinations.

LONG QUESTIONS

1. What is DBMS? Mention its clear architecture including its different types of files.
2. What do you mean by performance tuning? What are its importance? What are the various kinds of performance reports available in the oracle database management system?
3. Define and explain oracle server architecture.
4. Explain what do you mean by database users and security.
5. Compare and contrast control files, physical files, data files and redo log files.
6. What is VPD? Explain its importance and implementation with examples.
7. How do you multiplex:
• spfile from pfile
• redo log file
8. What are the different types of performance tuning methodology.
9. What do you mean by database security? Explain different methods and techniques invovled in database security.
10. What do you mean by roles in database? Explain its importance with examples.

SHORT QUESTIONS

1. What do you mean by SQL? Explain different types of SQL.
2. Differentiate between pfile and spfile.
3. Differentiate between system generated constraint name and user generated constraint name.
4. Differentiate between critical and non-critical recovery.
5. Define flashback recovery? Implement flashback recovery with and example.
6. What is backup and recovery? Inter relate among backup, restore and recovery.
7. What are the forms of database shutdown?
8. Differentiate between online backup and offline backup.
9. Differentiate between cold backup and hot backup.
10.  What are the roles and responsibilities of DBA.
11. What do you mean by AWR? Explain its architecture.
12. Explain state transition diagram with example.
13. What is RMAN? What steps are involved in RMAN?
14. Define profile, role and privilege. Why are they required?
15. What is view? Write importance of views?
16. What is ASM? Mention capabilities of ASM.
17. Write the command to add new redo group and drop redo group in the database.
18. What do you mean by parallel instance recovery?
19. Write a trigger to create log of every update and delete statement of product table. 
20. What are the steps involved in:
• steps to create backup device.
• internal database connector
21. What is SQL Tuning Advisor?
22. What are the importance of control files? If control files are corrupt, what is the state of database.
23. Wha are the importance of archive log file. Write down the steps to enable archive log files.
24. Write down the steps to create a redo log group with two associated members of size 10MB.
25. Explain DBMS architecture.
26. What do you mean by database independence?
27. What are the forms of database startup in oracle?

Learn more »

Following are the roles and responsibilities of Database Administrator:

• Installing and updating the Oracle server and application tools.
• Allocating system storage and planning future storage requirements for the database system.
• Creating primary database storage structures (table spaces) after application developers have designed an application.
• Creating primary objects (tables, views, indexes ) etc once application developers have designed an application.
• Modifying the database structure, as necessary from information giveny by application developers.
• Enrolling users and maintinging system security.
• Ensuring compliance with your oracle license agreements.
• Controlling and monitory user access to the database. 
• Monitoring and optimizing the performance of the database.
• Planning for backup and restoring the database. 
• Contacting Oracle corporation for technical support. 

Learn more »

Steps to Setup RMAN

This article is tested in oracle10gR2. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have seperate database to store catalog info. Here i am using seperate database to store backup catalog information.

 You might ask a question yourself, why would we need RMAN backup? Why do we need to setup RMAN, since my traditional backups are already running fine.... I already discussed this in another thread.. Please see this link to answer your question. Click

I am using windows OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environmnet.

Here i am using ORCL as primary database and CATDB as catalog database.

Step1

Enable the archive log in ORCL database. I already discussed this in another thread. Please refer this link to enable the database to archive log mode.

Step2

Create the tablespace and user in catalog database to hold backup information.

SQL> CONNECT sys/password@catdb AS SYSDBA

Connected.

SQL> CREATE TABLESPACE RMAN

2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\RMAN01.DBF' SIZE 6208K REUSE

3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M

4 EXTENT MANAGEMENT LOCAL

5 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> CREATE USER rman IDENTIFIED BY rman

2 TEMPORARY TABLESPACE temp

3 DEFAULT TABLESPACE rman

4 QUOTA UNLIMITED ON rman;

User created.

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Grant succeeded.

SQL>

Step3

 Create the recovery catalog in catalog database.

C:\>rman catalog=rman/rman@catdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 09:59:26 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:\>

Step4

 Register the database with Catalog database. Each database should be registered to catalog database to run RMAN backup.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:02:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> exit

Recovery Manager complete.

C:\>

Step5

Configure the persistent parameters.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 19 18:46:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215054467)

connected to recovery catalog database

RMAN> configure retention policy to recovery window of 2 days;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure default device type to disk;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure channel device type disk format 'C:\rmanbackup\Backup%d_DB_%U_%S

_%P';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\rmanbackup\Backup%d_DB_%U_%S_%P'

;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN>

Step 6

Take database full backup. The full database backup should be taken first time. Afterwards, archivelog backup will be taken.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:16:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)

connected to recovery catalog database

RMAN> run{

2> backup database plus archivelog;

3> delete noprompt obsolete;

4> }

starting full resync of recovery catalog

full resync complete

Starting backup at 21-MAY-09

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=687435222

input archive log thread=1 sequence=3 recid=2 stamp=687435237

input archive log thread=1 sequence=4 recid=3 stamp=687435270

input archive log thread=1 sequence=5 recid=4 stamp=687435279

input archive log thread=1 sequence=6 recid=5 stamp=687435420

channel ORA_DISK_1: starting piece 1 at 21-MAY-09

channel ORA_DISK_1: finished piece 1 at 21-MAY-09

piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFIRKU_1_1_%S_%P tag=TAG20090521T1017

01 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

Finished backup at 21-MAY-09

Starting backup at 21-MAY-09

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DB

F

channel ORA_DISK_1: starting piece 1 at 21-MAY-09

channel ORA_DISK_1: finished piece 1 at 21-MAY-09

piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFIRLF_1_1_%S_%P tag=TAG20090521T1017

19 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06

Finished backup at 21-MAY-09

Starting backup at 21-MAY-09

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=7 recid=6 stamp=687435506

channel ORA_DISK_1: starting piece 1 at 21-MAY-09

channel ORA_DISK_1: finished piece 1 at 21-MAY-09

piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFIRNJ_1_1_%S_%P tag=TAG20090521T1018

27 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09

piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-00 com

ment=NONE

Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 2 days

using channel ORA_DISK_1

no obsolete backups found

RMAN> exit

Recovery Manager complete.

C:\>

Now the RMAN setup is completed successfully. Here are the info about RMAN.

Primary DB = ORCL

Catalog DB = CATDB

RMAN Backup location = c:\rmanbackup.

Now the full backup is taken. Every day, the below script should run and backup the new archive log files.

C:\>rman catalog=rman/rman@catdb target=sys/password@orcl

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 21 10:25:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1215124933)

connected to recovery catalog database

RMAN> run{

2> delete noprompt obsolete;

3> backup archivelog all;

4> }

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 2 days

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=133 devtype=DISK

no obsolete backups found

Starting backup at 21-MAY-09

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=687435222

input archive log thread=1 sequence=3 recid=2 stamp=687435237

input archive log thread=1 sequence=4 recid=3 stamp=687435270

input archive log thread=1 sequence=5 recid=4 stamp=687435279

input archive log thread=1 sequence=6 recid=5 stamp=687435420

input archive log thread=1 sequence=7 recid=6 stamp=687435506

input archive log thread=1 sequence=8 recid=7 stamp=687435975

channel ORA_DISK_1: starting piece 1 at 21-MAY-09

channel ORA_DISK_1: finished piece 1 at 21-MAY-09

piece handle=C:\RMANBACKUP\BACKUPORCL_DB_05KFIS68_1_1_%S_%P tag=TAG20090521T1026

15 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

Finished backup at 21-MAY-09

Starting Control File and SPFILE Autobackup at 21-MAY-09

piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090521-01 com

ment=NONE

Finished Control File and SPFILE Autobackup at 21-MAY-09

RMAN> exit

Recovery Manager complete.

C:\>

How do we schedule the daily backup in Windows OS?

We need to write two script, one is batch file(named as daily_backup.bat) which should contain the following command.

rman catalog=rman/rman@catdb target=sys/password@orcl cmdfile daily_backup.sql

Another one is text file(named as daily_backup.sql) which should contain the following code.

run

{

backup archivelog all;

delete noprompt obsolete;

}

The batch file(daily_backup.bat) can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks). The above two windows scripts are tested in windows environment and works well.

How do we schedule the daily backup in Unix?

Here we need to write shell script and schedule the shell script in unix scheduler(crontab).

The shell script content should be like this....... Please remember, the below shell script is not tested in unix environment. Please do test yourself in unix environment.... This is sample shell script...

export ORACLE_HOME=/usr/app/oracle/product/10.2.0

export ORACLE_SID=orcl

export ALIAS=orcl

cd =/usr/app/oracle/product/10.2.0/bin

rman catalog=rman/rman@catdb target=sys/password@orcl <<

run

{

backup archivelog all;

delete noprompt obsolete;

}

exit

EOF

Learn more »