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.

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.