Configuring for Recoverability-CH4
RMAN Concepts
1
Configuring for Recoverability-CH4
Title Page Number
Database Storage Architecture 4
Logical and Physical Database Structures 5
Backup and Recovery 6
Oracle Physical Backup 7
Configuring Database for Recovery 8
Configuring ARCHIVELOG Mode 9
Multiplexing control file 12
Database Startup 12
Oracle Database Backup Cloud Service 15
2
Configuring for Recoverability-CH4
Key words:
RMAN, multiplexing, Archivelog, noarchivelog, Spfile, Cold
backup, hot backup, full backup, partial backup, incremental
backup, Differential backup, database recovery, instance startup.
Brief:
In this chapter, we will prepare the database to be optimized for
backup and recovery operations that we will cover in the upcoming
chapters.
Objective:
After completing this lesson, you should be able to:
▪ Database Storage Architecture
▪ Logical and Physical Database Structures
▪ Backup and Recovery
▪ Oracle Physical Backup
▪ Configuring Database for Recovery
▪ Configuring ARCHIVELOG Mode
▪ Multiplexed copies of online redo log files
▪ Multiplexing control file
▪ Database Startup
▪ Oracle Database Backup Cloud Service
3
Configuring for Recoverability-CH4
1. Database Storage Architecture:
The files that constitute an Oracle database are organized into
the following see (Figure 1_4):
Control files:
Contain data about the database itself (that is, physical
database structure information). These files are critical to the
database. Without them, you cannot open data files to access
the data in the database. It can also contain metadata related to
backups.
Data files:
Contain the user or application data of the database, as well as
metadata and the data dictionary
Online redo log files:
Allow for instance recovery of the database. If the database
server crashes and does not lose any data files, the instance
can recover the database with the information in these files.
The following additional files are important to the successful
running of the database:
Parameter file: Is used to define how the instance is configured
when it starts up
Password file: Allows sysdba, sysoper, and sysasm to connect
remotely to the instance and perform administrative tasks
Backup files: Are used for database recovery. You typically
restore a backup file when a media failure or user error has
damaged or deleted the original file.
Archived redo log files: Contain an ongoing history of the data
changes (redo) that are generated by the instance.
Trace and Alert files: The alert log of a database is a
chronological log of messages and errors When an internal error
is detected by a process, the process dumps information about
the error to its trace file.
Figure 1-4
4
Configuring for Recoverability-CH4
2. Logical and Physical Database Structures
2.1. Databases, Tablespaces, and Data Files
A database is divided into logical storage units called
tablespaces, which group related logical structures or datafiles
together. The relationship among databases, tablespaces, and
data files is illustrated in the (Figure 2_4). Each database is
logically divided into two or more tablespaces.
Data files:
One or more data files are explicitly created for each tablespace
to physically store the data of all segments in a tablespace.
Data Blocks:
At the finest level of granularity, an Oracle database’s data is
stored in data blocks. One data block corresponds to a specific
number of bytes of physical space on the disk. A data block size
is specified for each tablespace when it is created. A database
uses and allocates free database space in Oracle data blocks.
Figure 2-4
3. Backup and Recovery
3.1. Purpose of Backup and Recovery
The purpose of backup and recovery is to restore a failed
database. Backups protect the database against problems such
as:
5
Configuring for Recoverability-CH4
• Hardware failure
• Media failure
• User errors
• Application errors
Media errors cause data problems by failing at the hardware
level; a bad controller or disk drive can introduce either subtle or
obvious errors. Users can also cause data errors, simply by
issuing commands that should not be issued. Those same types
of errors can be caused by an application with a bug.
Backups can also be used for data preservation and historical
retention. Backups taken and preserved in ARCHIVELOG mode
can be used to recover a database to a past point in time, which
can be useful to meet compliance regulations.
You can also use backup and recovery tools to move data to
other databases even in other locations. A backup of a database
is one possible way to duplicate it at another location.
3.2. Typical Backup and Recovery Tasks:
A robust backup and recovery plan are important for a database
that you cannot afford to lose. The plan includes the following
tasks:
• Configuration: A backup and recovery strategy needs to be
configured for your environment. This should include backup
method, destination for backups, backup retention time and
deletion of backups, and backup protection (encryption), if
needed.
• Scheduling: Backups should be scheduled to run
automatically during nonpeak hours.
• Testing: Periodically test backups and recovery practices.
• Monitoring: Monitor the effects of the backup operations to
determine performance degradation on production databases
and improve backup efficiency, when necessary.
• Restoration: A corrupted data file is overwritten from a
backup of the data file. The data file is at a prior point of time
than the current database.
• Recovery: Recovery applies the changes to the individual
blocks, using archive and redo information, to move the
database forward to the current point in time.
6
Configuring for Recoverability-CH4
3.3. Oracle Physical Backup: The following are major backup
types:
Cold (off-line) backups: Full database only Require downtime
Do not provide flexibility for point in time recovery (PiTR)
Hot (on-line) backups:
• Different types of backups: full, incr. (cumulative, differential)
• Different scopes: full database, tablespace(s) or datafile(s) ,do
not require database downtime. Can be used to recover full
database, single/multiple tablespace(s)/datafile(s) or a
corrupted block
3.4. Oracle Logical Backup: Logical copy of data in the
database support for different backup granularity can be taken
either with legacy Export/Import tools or with Data Pump
3.5. Backup Terminology
Backup strategy may include:
Entire database (whole) : all files of the database are backed up
Portion of the database (partial): part of the database is backed up
Backup type may indicate inclusion of:
All data blocks within your chosen files (full) , or only information
that has changed since a previous backup (incremental)
Cumulative (changes since last level 0), Differential (changes
since last incremental)
Backup mode may be: Offline (consistent, cold) , online
(inconsistent, hot)
Backups may be stored as:
• Image copies: Are duplicates of data or archived log files
(similar to simply copying the files by using operating system
commands)
• Backup sets: Are collections of one or more binary files that
contain one or more data files, control files, server parameter
files, or archived log files. With backup sets, empty data
blocks are not stored, thereby causing backup sets to use
less space on the disk or tape. Backup sets can be
compressed to further reduce the space requirements of the
backup.
7
Configuring for Recoverability-CH4
3.6. Recovery Manager (RMAN):
Recovery Manager (RMAN) is an Oracle utility (with graphic and
command-line interfaces) that uses to manage the backup,
restore, and recovery operations on Oracle databases. RMAN
has a powerful command language that is independent of the
operating system. Invoke RMAN at the operating system
command line and specify the appropriate options. The following
are the most commonly used options:
• target: The connect-string for the target database
• catalog: The connect-string for a recovery catalog
• nocatalog: Specifies there is no recovery catalog. This is the
default.
• cmdfile: The name of an input command file
• log: The name of the output message log file
3.7. Separation of DBA Duties
The sysbackup administrative privilege :Includes permissions
for backup and recovery (connecting to a closed database) does
not include data access privileges such as SELECT ANY TABLE
is granted to the SYSBACKUP user that is created during
database installation can be explicitly used in RMAN
connections by a SYSBACKUP privileged user example:
Oracle_home\bin> rman target "' / as sysbackup '"
Rman> backup database;
Rman> list backup;
[Link] Database for Recovery
4.1. Archivelog Mode
As modifications to data in the database are made, the redo
data is written out to the online redo log file. A given file is
specified as being written to at a given time. When it is full, the
Archiver process (ARCn) copies the online log file to another
location that serves as an archive of that file, which can be
preserved for as long as you need it. This provides more
opportunities for recovery, because you can save, back up, and
restore all of the archive redo logs ever generated.
Because the online redo log files are reused in a circular
fashion, there is a protocol for controlling when one is allowed to
be reused. In ARCHIVELOG mode, the database only begins
8
Configuring for Recoverability-CH4
writing to an online redo log file if it has been archived. This
ensures that every redo log file has a chance to be archived.
4.2. Configuring ARCHIVELOG Mode
Placing the database in ARCHIVELOG mode prevents redo logs
from being overwritten until they have been archived.
To facilitate the creation of archive log files:
• Specify a naming convention for your archive logs
• Specify a destinations for storing your archive logs
• Place the database in ARCHIVELOG mode
To issue the SQL command to put the database in
ARCHIVELOG mode, the database must be in MOUNT mode. If
the database is currently open, you must shut it down cleanly
(not abort), and then mount it. The following shows the
commands to shut down an open database, put it in
ARCHIVELOG mode, and then open it:
SQL> SHUTDOWN IMMEDTE;
SQL> STARTUP MOUNT ;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
4.3. Using a Fast Recovery Area
The Fast Recovery Area is a unified storage location for all
recovery-related files and activities in an Oracle database. All
files that are needed to completely recover a database from a
media failure are part of the Fast Recovery Area.
4.3.1. Permanent Items
Control file: Depending on the setting of several initialization
parameters, a copy of the control file is created in the fast
recovery area location when you create a new database or
control file
Multiplexed copies of online redo log files: A mirrored
copy from each redo log group can be here. When you create
a database, you can specify the location of the online redo
log files by using the LOGFILE clause. If you do not include
that clause, the locations are set according to the values of
the following initialization parameters:
- DB_CREATE_ONLINE_LOG_DEST_n: If one or more of
these variables is set, then these are the only locations used.
9
Configuring for Recoverability-CH4
- DB_CREATE_FILE_DEST: If this is set, this is the primary
file location.
- DB_RECOVERY_FILE_DEST: If this is set, in addition to
DB_CREATE_FILE_DEST,
this location is used as the mirror.
4.3.2. Transient Items
Archived redo log files: When the fast recovery area is
configured , LOG_ARCHIVE_DEST_1 is automatically set to
the fast recovery area location
Flashback logs: Flashback logs are generated when
Flashback Database is enabled.
Control file autobackups: The default location for control file
autobackups created by RMAN and autobackups generated
by the Oracle database server is the fast recovery area.
Data file copies: The BACKUP AS COPY command creates
image data file copies in the fast recovery area.
RMAN files: The fast recovery area is the default location
that is used by RMAN for backups and restoration of the
archive log content from tape for a recovery operation.
Configuring the Fast Recovery Area
To define Recovery Area the parameters
DB_RECOVERY_FILE_DEST and
DB_RECOVERY_FILE_DEST_SIZE must be set
The minimum size of the FRA should be at least large enough
to contain archived redo log files and backup files
Sql> alter system set db_recovery_file_dest = ‘d:\backup’;
Sql> alter system set db_recovery_file_dest_size=12G;
4.4. Multiplexed copies of online redo log files:
A mirrored copy from each redo log group must be done, this
means that each redo log group can consist of more than one
online redo log file. Each file is known as a member. Each
member should be located on a different disk, to protect the
group from losing all of its members in the event a disk failure
should occur. Oracle writes to those members in parallel, to
ensure that the database is always recoverable while
maintaining performance (Figure 3-4)
10
Configuring for Recoverability-CH4
Figure 3-4
Add logfile to existence group for Multiplexing
SQL> alter database add
logfile member 'd:\backup\[Link]' TO GROUP 1;
Add new group
alter database add logfile group 4 ('c:\oracle\oradata\svu\[Link]',
'd:\oracle\oradata\svu\[Link]') size 50m;
4.5. Parameter file backup
Parameter file is not a crucial part of the backup, DBA can
rebuild it any time, but to save efforts of rebuilding it, DBA can
backup parameter file in many ways. Parameter file is accessed
only while starting the database, so you may copy it using
operating system commands any time, you may also use RMAN
“Control file auto backup” feature to take a copy of the control
file and parameter file with each backup .
4.6. Multiplexing control file
Multiplexing is the process of maintaining a copy of same
control file on different disk drivers, to use one of the valid
copies in case another is damaged.
1. Alter System set CONTROL_FILES
SQL> Alter System set CONTROL_FILES =
‘c:\app\oradata\orcl\[Link]’,
’d:\oradata\orcl\[Link]’ scope = spfile ;
2. Shut down the instance.
11
Configuring for Recoverability-CH4
3. Copy the control file to more locations using operating
system command.
4. Start the instance
5. Database Startup
5.1. Starting Up an Oracle Database Instance:
NOMOUNT
When starting the database instance see (Figure 4_4), select
the state in which it starts. The following scenarios describe
different stages of starting up an instance. An instance is
typically started only in NOMOUNT mode during database
creation,
during re-creation of control files, or during certain backup
and recovery scenarios.
Searching $ORACLE_HOME/dbs for a file of a particular name
in this sequence:
1. Search for spfile<SID>.ora.
2. If spfile<SID>.ora is not found, search for [Link].
3. If [Link] is not found, search for init<SID>.ora.
This is the file that contains initialization parameters for the
instance. Specifying the PFILE parameter with STARTUP
overrides the default behavior.
• Allocating the SGA
• Starting the background processes
• Opening the alert_<SID>.log file and the trace files
Mount
Mounting a database includes the following tasks:
• Associating a database with a previously started instance
• Locating and opening all of the control files specified in the
parameter file
• Reading the control files to obtain the names and statuses
of the data files and online redo log files (However, no
checks are performed to verify the existence of the data
files and online redo log files at this time.)
To perform specific maintenance operations, start an instance
and mount a database, but do not open the database.
For example, the database must be mounted but must not be
12
Configuring for Recoverability-CH4
opened during the following tasks:
• Renaming data files (Data files for an offline tablespace
can be renamed when the database is open.)
• Enabling and disabling online redo log file archiving
options
• Performing full database recovery
A normal database operation means that an instance is started
and the database is mounted and opened. With a normal
database operation, any valid user can connect to the database
and perform typical data access operations.
OPEN
Opening the database includes the following tasks:
• Opening the data files
• Opening the online redo log files
If any of the data files or online redo log files are not present
when you attempt to open the database, the Oracle server
returns an error.
During this final stage, the Oracle server verifies that all data
files and online redo log files can be opened, and checks the
consistency of the database. If necessary, the System Monitor
(SMON) background process initiates instance recovery.
You can start up a database instance in restricted mode so that
it is available to users with administrative privileges only. To start
an instance in restricted mode, select the “Restrict access to
database” option on the Advanced Startup Options page
Figure 4-4
13
Configuring for Recoverability-CH4
Using the sqlplus utility:
SQL> Startup;
SQL> Startup nomount;
SQL> alter database mount;
SQL> alter database open;
6. Oracle Database Backup Cloud Service
Oracle Database Backup Cloud Service is a secure, scalable,
on-demand storage solution for backing up Oracle databases to
Oracle Cloud. The service complements existing backup
strategy by providing an off-site storage location in the public
cloud.
To use Oracle Database Backup Cloud Service:
• Subscribe to the service,
• Install the Oracle Database Cloud Backup Module,
• Configure environment to send backups to the cloud.
Recovery Manager (RMAN) commands can be used to perform
backup, restore, recovery, and maintenance operations.
With Oracle Database Backup Cloud Service, cloud backups are
always accessible over the Internet and are immediately
available for recovery when needed. Data is replicated across
multiple storage nodes, which protects against hardware failure
and data corruption. Additionally, Oracle Database Backup
Cloud Service supports two Oracle Database optimizations:
6.1. RMAN backup encryption
Using RMAN encryption, data is encrypted at the source,
securely transmitted to the cloud, and securely stored in the
cloud. The keys are kept at local site, not in the cloud.
6.2. All RMAN backup compression algorithms
Using RMAN backup compression, improve performance by
reducing the size of backups before they’re sent to the cloud for
storage.
14
Configuring for Recoverability-CH4
Oracle Database Backup Cloud Service is used to store Oracle
Database backups only. Oracle Database Backup Cloud Service
can also be used to back up and restore Oracle Database Cloud
Service and Oracle Database Exadata Cloud Service
deployments. Backing up Oracle Database Cloud Service
database deployments, protect the software, configuration and
database against loss if a failure occurs, By restoring from a
backup, deployment’s software, configuration, and database to
their state at the time of the backup.
Database Cloud Service provides a backup feature that backs
up:
• The database
• Database configuration files
• Grid Infrastructure configuration files
• Important system and cloud tooling files
• Default Backup Configuration
When creating a database deployment, one of the following
backup destinations allowed:
Both Cloud Storage and Local Storage: Backups are
configured to be created automatically and stored both on local
compute node storage and on an Oracle Storage Cloud Service
container.
Cloud Storage Only: Backups are configured to be created
automatically and stored on an Oracle Storage Cloud Service
container. If you want backups of your database, Oracle
recommends that you choose one of the automatic backup
configurations instead of manually creating backups.
The backup configuration created when you choose a
destination other than None follows a set of Oracle best-practice
guidelines: Full (level 0) backup of the database followed by
rolling incremental (level 1) backups on a seven-day cycle (a 30-
day cycle for the Cloud Storage Only destination)
6.3. About Local Storage for Backups
When a database deployment is created on Database Cloud
Service, Oracle Compute Cloud Service storage volumes are
created and associated with the compute nodes.
The storage volume reserved for backups is named fra.
15
Configuring for Recoverability-CH4
The space allocated for the local storage used for backups on
fra is 1.7 times the space allocated for data storage. For
example, if you chose 100 GB for Usable Database Storage
when creating the database deployment, 170 GB are allocated
for backups
16