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.

What is a Checkpoint

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk. It offers Oracle the means for ensuring the consistency of data modified by transactions. The mechanism of writing modified blocks on disk in Oracle is not synchronized with the commit of the corresponding transactions.
A checkpoint has two purposes:
(1) to establish data consistency, and
(2) enable faster database recovery.
The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Depending on the number of datafiles in a database, a checkpoint can be a highly resource intensive operation, since all datafile headers are frozen during the checkpoint. Frequent checkpoints will enable faster recovery, but can cause performance degradation

Key Initialization parameters related to Checkpoint performance.

• FAST_START_MTTR_TARGET
• LOG_CHECKPOINT_INTERVAL
• LOG_CHECKPOINT_TIMEOUT
• LOG_CHECKPOINTS_TO_ALERT

FAST_START_MTTR_TARGET: It enables you to specify the number of seconds the database takes to perform crash recovery
of a single instance. Based on internal statistics, incremental checkpoint automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET. V$INSTANCE_RECOVERY.ESTIMATED_MTTR shows the current estimated mean time to recover (MTTR) in seconds. This value is shown even if FAST_START_MTTR_TARGET is not specified.

LOG_CHECKPOINT_INTERVAL: It influences when a checkpoint occurs, which means careful attention should be given to the setting of this parameter, keeping it updated as the size of the redo log files is changed. The checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that if the system crashes, more time will be needed for the database to recover. Shorter checkpoint intervals mean that the database will recover more quickly, at the expense of increased resource utilization during the checkpoint operation

LOG_CHECKPOINT_TIMEOUT: The parameter specifies the maximum number of seconds the incremental checkpoint target should lag the current log tail. In another word, it specifies how long a dirty buffer in buffer cache can remain dirty. Checkpoint frequency impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that more time will be required during database recovery.

LOG_CHECKPOINTS_TO_ALERT: It lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

Relationship between Redologs and Checkpoint: A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint. Maintain well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches. The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, checkpoints occur.

Checkpoint not complete: This message in alert log indicates that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log. In this case, Oracle must wait until the checkpoint position passes that log.When the database waits on checkpoints,redo generation is stopped until the log switch is done. This situation may be encountered if DBWR writes
too slowly, or if a log switch happens before the log is completely full, or if log file sizes are too small.

What information is Stored in Alert.log

Oracle's Alert Log [alert_sid.log/alert_(db_name).log] is a text file which can be opened with any text editor. The directory where it is found can be determined by the initialization parameter " background_dump_dest ", it records messages and errors that arise during the day to day database activities.
The messages in the alert log includes:

• Information of Instance Startup and Shutdown
• Errors causing generation of trace files.
• Create, alter and drop SQL statements executed on database, tablespaces and rollback segments.
• Errors arising during a materialized view is refreshed.
• ORA-00600 internal errors.
• Block Corruption (ORA-01578) Errors.
• Dead Locks(ORA-00060) Errors.
• Pointers to error generated trace files and dump files.

The alert log can be deleted and renamed if required for ex: if it reaches certain size, then you can delete or rename the alert log. Oracle will simply create a new alert log and start writing to it. Deleting the alert log or renaming won't affect the database functionality or performance.

Things a DBA must know

There are some things a good DBA must know.

1. This task? its easy, go to Google, DB Forums, etc.

But I know, however, that it cannot be a replacement for foundational knowledge of your chosen database server.
Sure, I can (and sometimes do) google the syntax for a command to get it right, but foundational knowledge is too important to be left to searches.

2. Continuing education is very Crucial, be aware of your technology.

This is in continue of point #1. Things change, even in the world of Oracle, so keep yourself up to date on the latest releases, patches, etc.

3. Monitoring is critical.

It doesn't matter which monitoring tool/system you use(OEM, Grid Control, third party tools, etc), just make sure you have one in place and it should be working! You should know when a server has too many connections, not enough memory, or if it's running out of disk space (or a hundred other things). Montoring is the base of tuning.

4. There is nothing more important than backups.

Performance tuning and building an awesome server are cool, but this is the bottom line: our data is sacred. Before anything else, you have to make regular backups and you have to make certain that those backups work properly. You must have a backup strategy, verify all the scheduled backups run successfully(by checking the backup logs) on a daily basis and perform test recoveries if needed to check the backup status.

5. Have fun at your job.

I hope you do what you do because it's your passion. It is for me, when I manage databases I am protecting client's most valuable resource and always learning something new. I get satisfaction from what I do. Because it matters a lot, whether you like your work or not.

Oracle Grid Control Architecture

Overview of Oracle Grid Architecture

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Difference between a cluster and a grid

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered - as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don't know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name, The Grid. This view of utility computing is, of course, a "client side" view.

From the "server side", or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.