History of oracle

HISTORY OF ORACLE

More than three decades ago Larry Ellison saw an opportunity other companies missed: a description of a working prototype for a relational database. No company had committed to commercializing the technology, but Ellison and co-founders Bob Miner and Ed Oates realized the tremendous business potential of the relational database model.

l Software Development Laboratories – the company's name before it was called Oracle was founded by the Trio Larry Ellison, Bob Miner, and Ed Oates.

l Throughout its history Oracle has proved it can build technology for the future on the foundation of its innovations and, its intimate knowledge of customer challenges and successes analyzed by the best technical and business minds in the world.

l Thirty years later, Oracle is the gold standard for database technology and applications in enterprises throughout the world, from the largest multinational corporations to the corner coffee shop

1978: Oracle version 1

l Ran on PDP-11 under RSX, in 128 KB memory

l Written in assembly language

l Separated Oracle code (OPI) and user code (UPI)

1979: Oracle version 2

l • Written in PDP-11 assembly language

l • Ran on VAX/VMS in compatibility mode

1980: Oracle version 3

l Written in C: portable source code

l Retained split architecture

l Introduced the concept of atomic SQL execution

and transactions (commit, rollback)

1984: Oracle version 4

l Introduced read consistency

l Ported to many platforms

l Interoperability between PC and server

1986: Oracle version 5

l True client-server (distributed processing)

l VAX-cluster support

l Version 5.1: Distributed queries

1989: Oracle version 6 (major kernel rewrite)

l OLTP performance enhancements, savepoints

l Online backup and recovery

l Row-level locking, PL/SQL in the database

l Parallel Server (VAX clusters, nCube)

1993: Oracle7

l Declarative referential integrity

l Stored procedures and triggers

l Shared SQL, parallel execution

l Advanced replication

1997: Oracle8

l Object-relational extensions in the database

l From client/server to three-tier architecture

l Partitioning option

1999: Oracle8i

l Java in the database (JVM and SQLJ)

l Partitioning enhancements

l Data warehousing enhancements

l XML support

l Summary management

l Oracle Internet Directory (LDAP)

l Ported to Linux

2001: Oracle9i

l Real Application Clusters, with cache fusion

l Scalability on inexpensive clustered hardware

l Automatic segment-space management

l Internet security enhancements

l Integrated business intelligence functionality

l Data Guard (standby databases)

l Oracle managed files

l Globalization support (Unicode, time zones, locales)

2003: Oracle 10g

l Primary goal: Build a self-managing database that requires minimal human intervention.

l Reduction in administration cost without compromising high availability, scalability, and security.

l Minimal performance impact

l Effective for all configurations and workloads

RMAN COMPONENTS & COMPATIBILITY

Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recovering Oracle Databases, first introduced in Oracle8. Rman is a feature of the Oracle database, it is included as part of the Oracle license, no additional installation needed and very little setup and configuration required.

The important components of Rman are as follows:

Rman client: The client application that manages backup and recovery operations for a target database. The RMAN client can use Oracle Net to connect to a target database, so it can be located on any host that is connected to the target host through Oracle Net.

Recovery Catalog: A database containing the recovery catalog schema, which contains the metadata that RMAN uses to perform its backup and recovery operations.

Recovery Catalog Schema: The user within the recovery catalog database that owns the metadata tables maintained by RMAN. RMAN periodically propagates metadata from the target database control file into the recovery catalog.

Target Database: The database (control files, datafiles, and optional archived redo logs) that RMAN is in charge of backing up or restoring is known as target database. RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations. The work of backup and recovery is performed by server sessions running on the target database.

Rman Channel: An RMAN channel corresponds to a server session on the target database. The server session performs the backup, restore, and recovery.

RMAN Compatibility

Target/Auxiliary Database	RMAN client	Recovery Catalog Database	Recovery Catalog Schema
8.0.6	8.0.6	>=8.1.7	>=8.0.6
8.1.7	8.0.6.1	>=8.1.7	>=8.1.7
8.1.7	8.1.7	>=8.1.7	>=RMAN client
8.1.7.4	8.1.7.4	>=8.1.7	8.1.7.4
8.1.7.4	8.1.7.4	>=8.1.7	>= 9.0.1.4
9.0.1	9.0.1	>=8.1.7	>= RMAN client
9.2.0	>=9.0.1.3 and <= target database executable	>=8.1.7	>= RMAN client
10.1.0	>=9.0.1.3 and <= target database executable	>=9.0.1	>= RMAN client
10.2.0	>=9.0.1.3 and <= target database executable	>=9.0.1	>= RMAN client
11.1.0	>=9.0.1.3 and <= target database executable	>=9.0.1	>= RMAN client
11.2.0	>=9.0.1.3 and <= target database executable	>=9.0.1	>= RMAN client

General DBA Best Practices

Day to Day

• Check Alert Logs & OS Event Logs for unusual events.
• Verify that all scheduled jobs have run successfully.
• Confirm that backups have been made and successfully saved to a secure location.
• Monitor disk space to ensure your database won't run out of disk space.
• Throughout the day, periodically monitor database performance using OEM Database control
• Use Enterprise Manager/Management to monitor and identify blocking issues.
• Keep a log of any changes you make to database, including documentation of any performance issues you identify and corrected.
• Create OEM alerts to notify you of potential problems, and have them emailed to you. Take actions as needed.
• Take some time to learn something new as a DBA to further your professional
• development.

Installation

• Go through the installation guide provided with the software, before installing.
• Always fully document installs so that your oracle server can easily be reproduced in an emergency.
• If possible, install and configure all of your Oracle server consistently, following an agreed-upon organization standard.
• For best performance of Database running under Windows, turn off any operating system services that aren't needed.
• For optimum performance, dedicate your physical servers to only running a oracle software, no other applications.
• For best I/O performance, locate the database files (.dbf) and log files (.log) on separate file system on your server to isolate potentially conflicting reads and writes.
• If temp file will be used heavily, also put it on its own separate file system.

Job Maintenance

• Avoid overlapping jobs on the same oracle server. Ideally, each job should run separately at different times.
• When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
• Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers

Disaster Recovery

• You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
• As your databases change over time, don't forget to update your disaster recovery plan.
• Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not assume a DBA will be rebuilding the servers.
• Fully test your disaster recovery plan at least once a year.

Backup

• Whenever possible, perform a daily full backup of all databases.
• For all production databases, perform regular archive log backups, at least
  once an hour, depending on the archive logs generation per hour.
• Perform full backups during periods of low user activity in order to minimize the
  impact of backups on users.
• Periodically test backups to ensure that they are good and can be restored.
• Backup first to disk, then move to tape or some other form of backup media.

Performance Monitoring

• Regularly monitor your database for blocked transactions.
• Regularly monitor system performance using OEM Database Control. Use OEM for both real-time analysis and for historical/baseline analysis.
• If your Database Server total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
• Don't run any applications on your oracle server, with the exception of necessary utilities as required.
• Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
• Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• Remove indexes that are never used. Don't accidentally create redundant indexes.

DBCA (Database Configuration Assistant)

We can use DBCA for creation of database, creation of database using default templates, managing templates and configuring existing databases. It can be used in GUI mode or in silent mode with a template. Silent mode can be used to run without X Display (i.e. if GUI mode unavailable)You can use a default template or a template you've created earlier. In order to create database, you must use the following parameters:

-silent [must be use to run without any X Display]

-createDatabase [defines the DBCA operation, i.e. create a database]

-templateName [defines the name of the template you want to use to create your database. The template files are located in $ORACLE_HOME/assistants/dbca/templates and end with the .dbc extension (e.g General_Purpose.dbc)]

-gdbName [defines the global database name of the database. That impacts the dbname and db_domain parameters. (e.g. ORCL)]

-sid [defines the instance SID (e.g. ORCL).]

-SysPassword [defines the password for the SYS user that will be created in the password file and in the database (e.g mypass)]

-SystemPassword [defines the password for the SYSTEM user (e.g manager)]

-emConfiguration [allows you to register an Enterprise Manager Database Control or to register the database in an existing Enterprise Manager GridControl if an Agent has been installed previously (e.g. CENTRALLOCALALLNOBACKUPNOEMAILNONE)]

-datafileDestination [defines the location of all the files remaining on a filesystem]

-redoLogFileSize [allows you to define the redolog file size in MB (e.g. 100)]

-storageType [allows you to specify the type of storage you want to use. It can be a filesystem (FS), ASM (ASM), or a set of raw devices (RAW).]

-asmSysPassword [defines the password for the ASM SYS user. This parameter is mandatory if you plan to use an existing ASM instance (e.g change_on_install)]

-diskGroupName [defines the name of the diskgroup if ASM is used (e.g. DGDATA)]

-characterSet [defines the character set to be used (e.g. WE8ISO8859P1);]

-initParams [enables specific initialization parameters]

-totalMemory [specify the value for MEMORY_TARGET in megabytes (e.g. 500)]

SAMPLE FOR CREATE DATABASE:

dbca -silent -createDatabase -gdbName mytest -sid mytest -templateName General_Purpose.dbc -SysPassword oracle -SystemPassword oracle -storageType FS

-datafileDestination /u01/oradata/test/data –totalMemory 500

Create a clone template from an existing database by specifying the following parameters (Note: While creating the template the source database will be closed i.e. it will be brought to Mount stage):

-createCloneTemplate

-sourceSID <source database sid>

-templateName <new template name>

[-sysDBAUserName <user name with SYSDBA privileges from source db >

-sysDBAPassword <password for sysDBAUserName user name from source db >]

[-maintainFileLocations <true false>]

[-datafileJarLocation <directory to place the datafiles in a compressed format>]

SAMPLE FOR CREATE CLONE TEMPLATE:

dbca -silent -createCloneTemplate –sourceSID mytest2 –templateName mytest_temp –sysDBAUserName sys –sysDBAPassword oracle –maintainFileLocations false

Generate scripts to create database by specifying the following parameters:

-generateScripts

-templateName <name of an existing template>

-gdbName <global database name>

[-scriptDest <destination for all the scriptfiles>]

SAMPLE FOR GENERATING SCRIPT FROM TEMPLATE:

dbca –silent –generateScripts –templateName mytest_temp –gdbName mytest –scriptDest /u01/oradata/test

Delete a database by specifying the following parameters:

-deleteDatabase

-sourceDB <source database sid>

[-sysDBAUserName <user name with SYSDBA privileges>

-sysDBAPassword <password for sysDBAUserName user name>]

SAMPLE FOR DELETING A DATABASE:

dbca -silent -deleteDatabase -sourceDB mytest -sysDBAUserName sys -sysDBAPassword oracle

RMAN Recovery Scenario

SCENARIO: Server Crash, same server was used for catalog database. Now we have RMAN backup, DBID. Db files(ctl, bdf, tmp,log, spfile) and catalog db is not available.

How are we going to recover the database???

We'll assume that we are performing the restore & recovery on a new server having the same environment setup(OS, oracle software, etc) as the crashed server.

CONDITIONS:

For successful restore, the controlfile record keep time must suffice the backup set.

We must know the DBID.

We must have the RMAN backups and know the latest autobackup information.

STEPs:

Start RMAN without Catalog

rman target / nocatalog

RMAN> set dbid=1439101873

RMAN> startup nomount;

It will show error as spfile not found, along with starting instance with basic parameters. Now restore spfile & controlfile from autobackup, specify location as we do not have catalog database.

RMAN> restore spfile from '/ora100base/admin/mydb/rman/bkups/c-10330…';

RMAN> restore controlfile from '/ora100base/admin/mydb/rman/bkups/c-10330…';

RMAN> restore database;

RMAN> sql 'alter database mount';

RMAN> recover database noredo; (as we don't have the current redo logs)

RMAN> sql 'alter database open resetlogs';

Again for using catalog db just connect to catalog database and resync catalog.

rman connect / target catalog rman/rman@rcatdb

RMAN> resync catalog;

What is a Namespace

Namespace: A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name
Object types all sharing the same namespace:
Tables, Views, Sequences, Private synonyms, Stand-alone procedures, Stand-alone stored functions, Packages, Materialized views, User-defined types, etc.

Thus it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

Object types having their own namespace:
Indexes, Constraints, Clusters, Database triggers, Private database links, Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

Non schema objects with their own namespace:
User roles, Public synonyms, Public database links, Tablespaces, Profiles, etc.

Let's see an Example:

SQL> create table test (eno number);
Table created.

SQL> create index test on test(eno);
Index created.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER

SQL> create view test as select * from test;
create view test as select * from test
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here, we see that I can create an index with the same name as of table but I can't create a view with the same name as of a table as they share the same namespace.

Codd's Rules for RDBMS

Codd's Rules for RDBMS

In 1985, Edgar Codd published a set of 13 rules which he defined as an evaluation scheme for a product which claimed to be a Relational DBMS.

Although these rules were later extended - and they now number in the 100s - they still serve as a foundation for a definition of a Relational DBMS.

(0) The foundation rule:

This is Codd's Rule Zero which is the foundation for the other rules.

The rules states that, any system which claims to be a relation database management system must be able to manage databases entirely through its relational capabilities and means that the RDBMS must support:

A data definition language

A data manipulation language

A data integrity language

A data control language

all of which must work on several records at a time, that is, on a relation.

(1) Information Rule:
All information in a relational database including table names column names are
represented explicitly by values in tables. Knowledge of only one language is necessary to
access all data such as description of the table and attribute definitions integrity
constraints action to be taken when constraints are violated and security information.

(2)Guaranteed Access Rule:
Every piece of data in the relational database can be accessed by using a
combination of a table name a primary key value that identifies the row and a column
that identifies the cell. The benefit of this is that user productivity is improved
since there is no need to resort to using physical pointers addresses. Provides
data independence.

(3)Systematic treatment of Nulls Rule:
The RDBMS handles, that have unknown or inapplicable values in a predefined fashion.
RDBMS distinguishes between zeros blanks and nulls in records and handles such values in
a consistent manner that produces correct answers comparisons and calculations.

(4)Active On-Line Catalog Based on the Relational Model:
The description of a database and its contents are database tables and therefore
can be queried online via the data language. The DBA's productivity is improved since
changes and additions to the catalog can be done with the same commands that are
used to access any other table. All queries and reports can be done as with other tables.

(5)Comprehensive Data Sub-language Rule:
A RDBMS may support several languages but at least one of them allows the user to do all
of the following: define tables view query and update data set integrity constraints
set authorization and define transactions.

(6)View Updating Rule:
Any view that is theoretically updatable if changes can be made to the tables that
effect the desired changes in the view. Data consistency is ensured since changes
in the underlying tables are transmitted to the view they support. Logical data
independence reduces maintenance cost.

(7)High Level Inserts Update and Delete:
The RDBMS supports insertion updation and deletion at a table level. With this the
RDBMS can improve performance by optimizing the path to be taken to execute the action
ease of use improved since commands act on set of records.

(8)Physical data Independence :
The execution of adhoc requests and application programs is not affected by changes
in the physical data access and storage methods. Database administrators can make
the changes to physical acccess and storage methods which improve performance
but do not changes in the application programs or adhoc requests. This reduces
maintenance costs.

(9)Logical data Independence:
Logical changes in tables and view such as adding/deleting columns or changing
field lengths do not necessitate modifications in application programs or in the
format of adhoc requests.

(10)Integrity Independence:
Like table/view definitions integrity constraints are stored in the
on-line catalog and therefore can be changed without necessitating changes
in application programs or in the format of adhoc requests .
The following two integrity constraints must be supported.
(a)Entity Integrity:
No component of primary key is allowed to have a null value.
(b)Referential integrity:
For each distinct non-null foreign key value in a relational database
there must exist a matching primary key from the same range of data value.

(11)Distribution Independence:
Application programs and adhoc requests are not affected by changes
in the distribution of the physical data.

(12)Non sub-version Rule:
If the RDBMS has a language change that accesses the information
of a record at a time this language cannot be used to by-pass the
integrity constraints. In order to adhere to this rule the RDBMS must have an
active catalog that contains the constraints and must have a logical data independence.