What is Control file

The control file is a small binary file necessary for the database to start and operate successfully. Control file records the physical structure of the database.Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use.The information in the control file can be modified only by the Oracle server.The information in the control file includes:

• Database name
• Names and locations of associated datafiles and online redo log files
• The timestamp of database creation
• Current log sequence number
• Checkpoint information
• Tablespace information
• Datafile offline ranges
• Redo log history
• Archived log information
• RMAN information means (database backup taken through RMAN)
• Datafile copy information.

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

Read More

Data Dictionary Question

1) Which of the following statements are true about the data dictionary?
a The data dictionary describes the database and its objects.
b The data dictionary includes two types of objects: base tables, data dictionary views.
c The data dictionary is a set of read-only tables.
d The data dictionary records and verifies information about its associated database.

2) Base tables are created using the catalog.sql script.
a True
b False

3) Which of the following statements are true about how the data dictionary is used? 
a The Oracle server modifies it when a DML statement is executed.
b It is used to find information about users, schema objects, and storage structures.
c Used by users and DBAs as a read-only reference.
d The data dictionary is a necessary ingredient for the database to function.
 

4) Data dictionary views are static views.
a True
b False
 

5) The information for a Dynamic View is gathered from the control file and data files.
a True
b False
 

6)  Which of the following questions might a Dynamic View answer?
a Is the object online and available?
b What locks are being held?
c Who owns the object?
d What privileges do users have?
e Is the session active?

Read More

Data Dictionary Contents

Data Dictionary Contents

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information
• The names of Oracle users
• Privileges and roles each user has been granted
• Auditing information, such as who has accessed or updated various schema objects

 Data dictionary static views that answer questions such as:

• Was the object ever created?
• What is the object a part of?
• Who owns the object?
• What privileges do users have?
• What restrictions are on the object?

Data dictionary dynamic Performance Views that answer questions such as:

• Is the object online and available?
• Is the object open?
• What locks are being held?
• Is the session active?

Read More

Data Dictionary Creation In Oracle

Oracle creates data dictionary automatically when you create database by using Database Configuration Assistant method.But when you create database manually then you must run several scripts to create data dictionary objects.
There are three scripts you use to create data dictionary objects:

 

• SQL>@?/rdbms/admin/catalog.sql: Creates the data dictionary and public synonyms for many of its views.
• SQL>@?/rdbms/admin/catproc.sql: Creates all data dictionary objects required for PL/SQL.
• SQL>@?/rdbms/admin/catclust.sql: Creates REAL Application Clusters data dictionary views.

The data dictionary base tables are the first objects created in any Oracle database. They are created in system tablespace.

Read More

Optimizer Statistics

To know about the database and the objects in the database we use optiimizer statistics.In simple optimzer statistics provides information about the database and the objects in the database. For every SQL statement there is execution plan, that execution plan is decided by query optimizer.Optimzer statistics include the Table statistics, Index statistics, Column statistics, System statistics:

- Table statistics

• Number of rows
• Number of blocks
• Average row length 

- Column statistics

• Number of distinct values(NDV) in column
• Number of nulls in column
• Data distribution

- Index statistics 

• Number of leaf blocks
• Levels
• Clustering factor 

- System statistics

• I/O performance and utilization 
• CPU performance and uitilization 

By using data dictionary you can view the optimizer statistics that are stored in the data dictionary and the views are:

• DBA_TABLES
• DBA_INDEXES
• DBA_CLUSTERS
• DBA_TAB_STATISTICS
• DBA_IND_STATISTICS
• DBA_TAB_PARTITIONS
• DBA_OBJECT_TABLES

Read More

Unregister Target Database and Reovery Catalog with RMAN

You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.

So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog.

Unregistering a Target Database from the Recovery Catalog
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.
 

rman target / CATALOG cat_user/cat_passwd@catdb
Recovery Manager: Release 10.2.0.1.0
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: catdb1 (DBID=1489461517)
connected to recovery catalog database

2)Issue UNREGISTER DATABASE command.

RMAN> UNREGISTER DATABASE;

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.

Resynchronizing the Recovery Catalog

 
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.

RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete

Related Post:-

• Create Recovery Catalog
• Register Target Database and Recovery Catalog

Read More

Register Target Database and Recovery Catalog with RMAN

After creating the recovery catalog the first step in using a recovery catalog with a target database is registering the database in the recovery catalog.To register database you have to follow fews steps and they are:-
1) The recovery catalog database must be open.
2) Connect RMAN to both the target database and recovery catalog database.

rman target/catalog cat_user/cat_passwd@catdb

Recovery Manager: Release 10.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation, All rights reserved.

connected to target database: catdb (DBID=1489461517)
connected to recovery catalog database

3) If this target database has not registered then register the target database, it should be registered in the connected recovery catalog

RMAN>REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete.

Related Post:- 

• Create Recovery Catalog
• Unregister Target Database and Recovery Catalog with RMAN

Read More

Create Recovery Catalog

A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. Recovery catalog is optional. RMAN can be used either with or without a recovery catalog. An experienced DBA suggest that the Enterprise Manager instance schema and RMAN catalog schema  placed in the same utility database on a server separate from the main servers. There are few steps to create recovery catalog as follow:

•     Configure Recovery Catalog
•     Create Recovery Catalog owner
•     Create Recovery Catalog  

 1)Create a database called cat and connect sqlplus SYS/pass@cat as SYSDBA

 2)Create a tablespace where all information of receovery catalog will be collected.

SQL>create tablespace cat_tbs datafile '/opt/cat/cattbs.dbf' size 100m;  

Tablespace created.

 3)Create a user in the recovery catalog database

SQL>create user cat_user identified by cat_passwd

 temporary tablespace temp default tablespace cat_tbs 

 quota unlimited on cat_tbs;  

User Created.

 4)Grant the recovery catalog owner role to the user.This will provide all privileges to maintain and query recovery catalog.

SQL>grant recovery_catalog_owner to cat_user;

Grant succeeded.

 5)Connect to the database that contains the catalog owner.

 rman catalog cat_user/cat_passwd@catdb

 6)Connect from the RMAN utility prompt

rman
RMAN>connect catalog cat_user/cat_passwd

7)Run the create catalog command to create the catalog.May it take several minutes.

 RMAN>create catalog;

recovery catalog created

Related Post:-

• Register Target Database and Recovery Catalog  with RMAN
• Unregister Target Database and Recover Catalog with RMAN  

Read More

Materialized Views

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:
 

In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to compute joins with or without aggregations. If compatibility is set to Oracle9i or higher, then materialized views can be used for queries that include filter selections.
 

Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.
 

In distributed environments, materialized views are used to replicate data at distributed sites and synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise has to be accessed from remote sites.
    In mobile computing environments, materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

Materialized views are similar to indexes in several ways:

• They consume storage space.
• They must be refreshed when the data in their master tables changes.
• They improve the performance of SQL execution when they are used for query rewrites.
• Their existence is transparent to SQL applications and users.

Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

A materialized view can be partitioned. You can define a materialized view on a partitioned table and one or more indexes on the materialized view.

Read More

What Is View

A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary. You can create view by using sql create view statement :

create view stu_name as

select roll_no, first_name

from students

where first_name='JOHN';

view created 

This command create a new view called stu_name. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this

select * from stu_name

where roll_no between 10 AND 25;

Advantage of using Views:-

• Views restrict access to the data because the view can display selective columns from the table.
• Views can be used to make simple queries to retrieve the results of complicated queries
• Views provide groups of users access to data according to their particular criteria

Related Post :-

• How To Create Simplex And Complex View
• Force And Inline View   
• Examples Of Different Types Of Views 

Read More

Data Dictionary Cache

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:

• The definitions of every schema object in the database, including default values for columns and    integrity constraint information.
•  The amount of space allocated for and currently used by the schema objects
• The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:

• Accesses the data dictionary to find information about users, schema objects, and storage structure
• Modifies the data dictionary every time that a DDL statement is issued

Contents of the Data Dictionary

The data dictionary consists of the following:

Base Tables

The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.

 Views

The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.

Related Post:- What Is View?

Storage of the Data Dictionary

The data dictionary base tables are the first objects created in any Oracle database. All data dictionary tables and views for a database are stored in the SYSTEM tablespace. Because the SYSTEM tablespace is always online when the database is open, the data dictionary is always available when the database is open.

How Oracle Uses the Data Dictionary

Data in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded. During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.

For example, if user Kathy creates a table named parts, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that Kathy has on the table. This new information is then visible the next time the dictionary views are queried.

Dictionary Cache is place in Shared Pool which contains Data Dictionary. Oracle frequently requires Data Dictionary. Most parts of Data Dictionary are cached into Dictionary Cache. Oracle utilizes Dictionary Cache information in query parsing. Dictionary cache is also called Row Cache as data inside Dictionary Cache is maintained into rows instead of buffer

Read More