ASM

History

ASM was first released with Oracle 10g Release 1.

[edit]Features

• ASM takes control of disk devices (no disk partitioning needed)
• More space can be transparently added
• I/Os are spread over all the available disks (striping)
• ASM can maintain redundant copies of the data (mirror)
• Storage can be migrated between disks
• Same disk (or set of disks) can be shared amongst many DBs

[edit]Installing ASM

It is recommended to install ASM in a separate Oracle home so that upgrades can be done independently from the databases.

Use the DBCA utility to create the ASM instance.

[edit]Starting and stopping

Start the ASM instance:

$ export ORACLE_SID=+ASM
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 3 00:28:09 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area   83886080 bytes
Fixed Size                  1217836 bytes
Variable Size              57502420 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

Stop the ASM instance:

$ export ORACLE_SID=+ASM
$ sqlplus "/ as sysdba"
shutdown immediate

[edit]Adding diskgroups

SQL> create diskgroup orag2 external redundancy disk 'ORCL:VOL5';
Diskgroup created.

SQL> select group_number,disk_number,mode_status,name from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MODE_STATUS    NAME
------------ ----------- -------------- -------------------------------------
           0           5 ONLINE
           1           0 ONLINE         VOL1
           1           1 ONLINE         VOL2
           1           2 ONLINE         VOL3
           1           3 ONLINE         VOL4
           2           0 ONLINE         VOL5
6 rows selected.

[edit]Recreating a diskgroup

All ASM metadata must be cleared before attempting to re-create a diskgroup. Example Command:

dd if=/dev/zero of=/dev/rdsk/c1t4d0s4 bs=8192 count=12800

When done, restart ASM and create the diskgroup from scratch.

[edit]Rebalancing

The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.

To force rebalancing of a diskgroup:

ALTER DISKGROUP data REBALANCE POWER 11 WAIT;

[edit]Converting to ASM

One can use Rman to convert a datafile, tablespace or entire database from/to ASM. Here are the steps required to migrate an entire database to ASM:

[edit]Convert a database to ASM

Ensure the database is using an SPFILE and not a PFILE (it's about time after all!). Set parameters on the target database. For example, if we set both DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST we should get mirrored controlfiles and duplexed log files by default:

SQL> alter system set DB_CREATE_FILE_DEST = '+DATA';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 17G;
SQL> alter system set DB_RECOVERY_FILE_DEST = '+RECOVER';
SQL> alter system set CONTROL_FILES = '+DATA';

Start the database in NOMOUNT mode and restore the controlfile into the new location from the old location:

RMAN> connect target /
RMAN> STARTUP NOMOUNT
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';

Mount the database and copy the database into the ASM disk group:

RMAN> ALTER DATABASE MOUNT;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Switch all datafiles to the new ASM location and open the database:

RMAN> SWITCH DATABASE TO COPY;
RMAN> ALTER DATABASE OPEN;

Add new tempfiles and drop the old tempfiles:

SQL> alter tablespace temp add tempfile;
SQL> alter database tempfile '...' DROP;
SQL> select * from dba_temp_files; 

Optionally, move SPFILE into ASM:

SQL> CREATE SPFILE '+DATA' FROM PFILE;

Move redo log files into ASM - for each group:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 100M;

If a logfile is active and cannot be dropped, issue an ALTER DATABASE SWITCH LOGFILE; command and try again.

[edit]Convert a tablespace to ASM

Ensure the database in in archive log mode, and from rman:

connect target;
sql "alter tablespace TSNAME offline";
backup as copy tablespace TSNAME format '+DATA';
switch tablespace TSNAME to copy;
sql "alter tablespace TSNAME online";
exit;

[edit]Convert a datafile to ASM

Ensure the database in in archive log mode, and from rman:

connect target;
sql "alter database datafile '...' offline";
backup as copy datafile '...' format '+DATA';
switch datafile '..' to copy;
sql "alter database datafile '...' online";
exit;

[edit]Monitoring

Some of the views that can be used to monitor ASM configuration and activity:

• V$ASM_DISK - ASM disks
• V$ASM_DISK_STAT - cached view of V$ASM_DISK for faster access (used by Enterprise Manager)
• V$ASM_DISKGROUP - ASM diskgroups
• V$ASM_DISKGROUP_STAT - cached view of V$ASM_DISKGROUP for faster access (used by Enterprise Manager)
• V$ASM_OPERATION - status of ongoing disk operations (like rebalancing)

Index

ASM

Oracle Tuning Interview Questions

A.Oracle includes many performance tuning enhancements like:

1.Automatic Performance Diagnostic and Tuning Features
2.Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
3.Wait Model Improvements - A number of views have been updated and added to improve the wait model.
4.Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
5.Dynamic Sampling - enables the server to improve performance
6.CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time Optimizer Hints
7.Rule Based Optimizer Obsolescence - No more used
8.Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id
9.SAMPLE Clause Enhancements Hash Partitioned Global Indexes

Q.What is proactive tuning and reactive tuning?
A.In Proactive Tuning, the application designers can then determine which combination of system resources and available Oracle features best meet the needs during design and development.In reactive tuning the bottom up approach is used to find and fix the bottlenecks. The goal is to make Oracle run faster.

Q.Describe the level of tuning in oracle
A.System-level tuning involves the following steps:

1.Monitoring the operating system counters using a tool such as top, gtop, and GKrellM or the VTune analyzer’s counter monitor data collector for applications running on Windows.
2.Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.
3.SQL-level tuning:Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.

Distribution of data can be studied by the optimizer by collecting and storing optimizer statistics. This enables intelligent execution plans. Choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance. Tuning SQL Access workload with physical indexes and materialized views.

Q.What is Database design level tuning?
A.The steps involved in database design level tuning are:

1.Determination of the data needed by an application (what relations are important, their attributes and structuring the data to best meet the performance goals)
2.Analysis of data followed by normalization to eliminate data redundancy.
3.Avoiding data contention.
4.Localizing access to the data to the partition, process and instance levels.
5.Using synchronization points in Oracle Parallel Server.
6.Implementation of 8i enhancements that can help avoid contention are:
a.Consideration on partitioning the data
b.Consideration over using local or global indexes.

Q.Explain rule-based optimizer and cost-based optimizer.
A.Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.This decision can be made using one of two methods:

1.Rule Based Optimizer
If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used.This method will be deprecated in the future releases of oracle.

2.Cost Based Optimizer
The CBO method is used if internal statistics are present.The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.

Q.What are object datatypes? Explain the use of object datatypes.
A.Object data types are user defined data types. Both column and row can represent an object type. Object types instance can be stored in the database. Object datatypes make it easier to work with complex data, such as images, audio, and video. Object types provide higher-level ways to organize and access data in the database.The SQL attributes of Select into clause, i.e. SQL % Not found, SQL % found, SQL % Isopen, SQL %Rowcount.
1.% Not found: True if no rows returned
E.g. If SQL%NOTFOUND then return some_value

2.% found: True if at least one or more rows returned
E.g. If SQL%FOUND then return some_value

3.%Isopen: True if the SQL cursor is open. Will always be false, because the database opens and closes the implicit cursor used to retrieve the data

4.%Rowcount: Number of rows returned. Equals 0 if no rows were found (but the exception is raised) and a 1, if one or more rows are found (if more than one an exception is raised).

Q.What is translate and decode in oracle?
A.
1.Translate: translate function replaces a sequence of characters in a string with another set of characters. The replacement is done single character at a time.Syntax:
translate( string1, string_to_replace, replacement_string )

Example:
translate ('1tech23', '123', '456);

2.Decode: The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
Syntax:
DECODE (expr , search, result [, search , result]... [, default])

Example:
SELECT employee_name, decode(employee_id, 10000, ‘tom’, 10001, ‘peter’, 10002, ‘jack’ 'Gateway') result FROM employee;

Q.What is oracle correlated sub-queries? Explain with an example.
A.A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.Example:
Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.

select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);

Q.Explain union and intersect with examples.
A.
1.UNION: The UNION operator is used to combine the result-set of two or more SELECT statements Tables of both the select statement must have the same number of columns with similar data types. It eliminates duplicates.Syntax:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Example:
SELECT emp_Name FROM Employees_india
UNION
SELECT emp_Name FROM Employees_USA

2.INTERSECT allows combining results of two or more select queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Q.What is difference between open_form and call_form? What is new_form built-in in oracle form?
A.Open_form opens the indicated form. Call_form not just opens the indicated form, but also keeps the parent form alive.When new_form is called, the new indicted form is opened and the old one is exited by releasing the memory. The new form is run using the same Run form options as the parent form.

Q.What is advantage of having disk shadowing/ Mirroring in oracle?
A.Fast recovery of data in case of Disk failure.Improved performance since most OS supports volume shadowing that can direct file I/O request to use the shadow set of files instead of the main set of files.

Oracle Data Guard Interview Questions

1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?

Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.


2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

Active dataguard is mostly about the physical standby.

Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.

3. What is a Dataguard?

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.


4. What are the uses of Oracle Data Guard?

a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.

c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.


5. What is Redo Transport Services?
It control the automated transfer of redo data from the production database to one or more archival destinations.

Redo transport services perform the following tasks:

a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database.


6. What is apply services?

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

7. What is difference between physical and standby databases?

The main difference between physical and logical standby databases is the manner in
which apply services apply the archived redo data:

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of
an Oracle database.

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the
generated SQL statements on the logical standby database.


8. What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services
b) Manage an entire Data Guard configuration from any system in the configuration
c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases
d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.
e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

9. What are the Data guard Protection modes and summarize each?

Maximum availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

Maximum performance :

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection :

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

10. If you didn't have access to the standby database and you wanted to find out what error has occurred in a data guard configuration, what view would you check in the primary database to check the error message?

You can check the v$dataguard_status view.  Select message from v$dataguard_status;

11.  In Oracle 11g, what command in RMAN can you use to create the standby database while the target database is active?

Oracle 11g has made it extremely simple to set up a standby database environment because Recovery Manager (RMAN) now supports the ability to clone the existing primary database directly to the intended standby database siteover the network via the DUPLICATE DATABASE command set while the target database is active.  RMAN automatically generates a conversion script in memory on the primary site and uses that script to manage the cloning operation on the standby site with virtually no DBA intervention required.   You can execute this in a run block in RMAN:

duplicate target database for standby dorecover from active database;

12.  What additional standby database mode does Oracle 11g offer?

Oracle 11g has introduced the Oracle Snapshot Standby Database.   In Snapshot Standby Database a physical standby database can easily open in read-write mode and again you can convert it back to the physical standby database. This is suitable for test and development environments and also maintains protection by continuing to receive data from the production database and archiving it for later use.

13. In Oracle 11g how can speed up backups on the standby database?

In Oracle 11g, block change tracking is now supported in the standby database.

14.  With the availability of Active Data Guard, what role does SQL Apply (logical standby) continue to play?

Use SQL Apply for the following requirements: (a) when you require read-write access to a synchronized standby database but do not modify primary data, (b) when you wish to add local tables to the standby database that can also be updated, or (c) when you wish to create additional indexes to optimize read performance.  The ability to handle local writes makes SQL Apply better suited to packaged reporting applications that often require write access to local tables that exist only at the target database. SQL Apply also provides rolling upgrade capability for patchsets and major database releases.  This rolling upgrade functionality can also be used by physical standby databases beginning with Oracle 11g using Transient Logical Standby. 

15.  Why would I use Active Data Guard and not simply use SQL Apply (logical standby) that is included with Data Guard 11g?

If read-only access satisfies the requirement - Active Data Guard is a closer fit for the requirement, and therefore is much easier to implement than any other approach.  Active Data Guard supports all datatypes and is very simple to implement. An Active Data Guard replica can also easily support additional uses - offloading backups from the primary database, serve as an open read-write test system during off-peak hours (Snapshot Standby), and provide an exact copy of the production database for disaster recovery - fully utilizing standby servers, storage and software while in standby role.

16.  Why do I need the Oracle 11g Active Data Guard Option?

Previous capabilities did not allow Redo Apply to be active while a physical standby database was open read-only, and did not enable RMAN block change tracking on the standby database.  This resulted in (a) read-only access to data that was frozen as of the time that the standby database was opened read-only, (b) failover and switchover operations that could take longer to complete due to the backlog of redo data that would need to be applied, and (c) incremental backups that could take up to 20x longer to complete - even on a database with a moderate rate of change. Previous capabilities are still included with Oracle Data Guard 11g, no additional license is required to use previous capabilities.

17.  If you wanted to upgrade your current 10g physical standby data guard configuration to 11g, can you upgrade the standby to 11g first then upgrade the primary ?

Yes, in Oracle 11g, you can temporarily convert the physical standby database to a logical standby database to perform a rolling upgrade.  When you issue the convert command you need to keep the identity:

alter database recover logical standby keep identity;

18.  If you have a low-bandwidth WAN network, what can you do to improve the Oracle 11g data guard configuration in a GAP detected situation?

Oracle 11g introduces the capability to compress redo log data as it transports over the network to the standby database.  It can be enabled using the compression parameter.  Compression becomes enabled only when a gap exists and the standby database needs to catch up to the primary database.

alter system set log_archive_dest_1='SERVICE=DBA11GDR COMPRESSION=ENABLE';

19.  In an Oracle 11g Logical Standby Data Guard configuration, how can you tell the dbms_scheduler to only run jobs in primary database?

Oracle 11g, logical standby now provides support for DBMS_SCHEDULER.  It is capable of running jobs in both primary and logical standby database.  You can use  the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role.  You can specify that the jobs can run only when operating in that particular database role.

20.  How can you control when an archive log can be deleted in the standby database in oracle 11g ?

In Oracle 11g, you can control it by using the log_auto_delete initialization parameter.  The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours.  For archivelog retention to be effective, the log_auto_delete parameter must be set to true.

21.  Can Oracle Data Guard be used with Standard Edition of Oracle ? Yes and No.   The automated features of Data Guard are not available in the standard edition of Oracle.   You can still however, perform log shipping manually and write scripts to manually perform the steps.    If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server.  Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.

RMAN

1. What is RMAN ?Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

Which Files must be backed up? Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)

 2.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?

In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog).  

3.  What are the Architectural components of RMAN?

1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

4.  What are Channels?

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:

• Type of I/O device being read or written to, either a disk or an sbt_tape
• Number of processes simultaneously accessing an I/O device
• Maximum size of files created on I/O devices
• Maximum rate at which database files are read
• Maximum number of files open at a time

5.  Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.

6.  What does complete RMAN backup consist of ?
A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7.  What is a Backup set?A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8.  What is a Backup piece?A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:

  A datafile cannot span backup sets

  A datafile can span backup pieces as long as it stays within one backup set

  Datafiles and control files can coexist in the same backup sets

  Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.

9.  What are the benefits of using RMAN?

1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

10. RMAN Restore Preview

The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used:

# Spool output to a log file
SPOOL LOG TO c:\oracle\rmancmd\restorepreview.lst;

# Show what files will be used to restore the SYSTEM tablespace’s datafile
RESTORE DATAFILE 2 PREVIEW;

# Show what files will be used to restore a specific tablespace
RESTORE TABLESPACE users PREVIEW;

# Show a summary for a full database restore
RESTORE DATABASE PREVIEW SUMMARY;

# Close the log file
SPOOL LOG OFF;

11. Where should the catalog be created? 
The recovery catalog to be used by rman should be created in a separate database other than the target database. The reason been that the target database will be shutdown while datafiles are restored.
12. How many times does oracle ask before dropping a catalog? 
The default is two times one for the actual command, the other for confirmation.
13. How to view the current defaults for the database. 
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u02/app/oracle/product/10.1.0/db_1/dbs/snapcf_test.f’; # default
14. Backup the database. 

run

{

backup incremental level $level ${level_keyword}

tag INC${target_db}_$level database include current controlfile;

backup archivelog all not backed up 1 times delete input;

}

15. How to resolve the ora-19804 error
Basically this error is because of flash recovery area been full. One way to solve is to increase the space available for flashback database. 
sql>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; –It can be set to K,M or G.
rman>backup database;
……………….
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 04-JUL-05
channel ORA_DISK_1: finished piece 1 at 04-JUL-05
piece handle=/u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_04/o1_mf_ncsnf_TAG20050704T205840_1dmy15cr_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JUL-05
Oracle Flashback 
After taking a back up resync the database. 
Restoring the whole database. 
run {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}
16. What are the various reports available with RMAN
rman>list backup;
 rman> list archive; 
17. What does backup incremental level=0 database do? 
Backup database level=0 is a full backup of the database. rman>>backup incremental level=0 database; 
You can also use backup full database; which means the same thing as level=0; 
18. What is the difference between DELETE INPUT and DELETE ALL command in backup? 
Generally speaking LOG_ARCHIVE_DEST_n points to two disk drive locations where we archive the files, when a command is issued through rman to backup archivelogs it uses one of the location to backup the data. When we specify delete input the location which was backedup will get deleted, if we specify delete all all log_archive_dest_n will get deleted. 
DELETE all applies only to archived logs. delete expired archivelog all; 
19. How do I backup archive log? 
In order to backup archivelog we have to do the following:- 

run

{

allocate channel t1 type 'SBT_TAPE';

delete noprompt archivelog until time = 'sysdate-3/24';

delete noprompt obsolete;

release channel t1;

}

20. How do I do a incremental backup after a base backup?

run

{

backup incremental level $level ${level_keyword}

tag INC${target_db}_$level database include current controlfile;

backup archivelog all not backed up 1 times delete input;

}

21. In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?

using RMAN BLOCK RECOVER command

22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

You have to catalog that manual backup in RMAN's repository by command

RMAN> catalog datafilecopy '/DB01/BACKUP/users01.dbf';

restrictions:

> Accessible on disk

> A complete image copy of a single file

23. Where RMAN keeps information of backups if you are using RMAN without Catalog?

RMAN keeps information of backups in the control file.

CATALOG vs NOCATALOG

the difference is only who maintains the backup records like when is the last successful backup incremental differential etc.

In CATALOG mode another database (TARGET database) stores all the information.

In NOCATALOG mode controlfile of Target database is responsible.

24. How do you see information about backups in RMAN?

RMAN> List Backup;

Use this SQL to check

SQL> SELECT sid totalwork sofar FROM v$session_longops WHERE sid 153;

Here give SID when back start it will show SID

25. How RMAN improves backup time?

RMAN backup time consumption is very less than compared to regular online backup as RMAN copies only modified blocks

26. What is the advantage of RMAN utility?

Central Repository

Incremental Backup

Corruption Detection

Advantage over tradition backup system:

1). copies only the filled blocks i.e. even if 1000 blocks is allocated to datafile but 500 are filled with data then RMAN will only create a backup for that 500 filled blocks.

2). incremental and accumulative backup.

3). catalog and no catalog option.

4). detection of corrupted blocks during backup;

5). can create and store the backup and recover scripts.

6). increase performance through automatic parallelization( allocating channels) less redo generation.

27. List the encryption options available with RMAN?

RMAN offers three encryption modes: transparent mode, password mode and dual mode.

28. What are the steps required to perform in $ORACLE_HOME for enabling the RMAN backups with netbackup or TSM tape library software?

I can explain what are all the steps to take a rman backup with TSM tape library as follows

1.Install TDPO (default path /usr/tivoli/tsm/client/oracle/)

2.Once u installed the TDPO automatically one link is created from TDPO directory to /usr/lib.Now we need to Create soft link between OS to ORACLE_HOME

ln -s /usr/lib/libiobk64.a $ORACLE_HOME/lib/libobk.a(very imporatant)

3.Uncomment and Modify tdpo.opt file which in

/usr/tivoli/tsm/client/oracle/bin/tdpo.opt as follows

DSMI_ORC_CONFIG /usr/Tivoli/tsm/client/oracle/bin64/dsm.opt

DSMI_LOG /home/tmp/oracle

TDPO_NODE backup

TDPO_PSWDPATH /usr/tivoli/tsm/client/oracle/bin64

4.create dsm.sys file in same path and add the entries

SErvername <Server name >

TCPPort 1500

passwordacess prompt

nodename backup

enablelanfree yes

TCPSERVERADDRESS <Server Address>

5.Create dsm.opt file add an entry

SErvername <Server name >

6.Then take backup

RMAN>run

{

allocate channel t1 type 'sbt_tape' parms

'ENV (TDPO_OPTFILE /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

backup database include current controlfile;

release channel t1;

}

29. What is the significance of incarnation and DBID in the RMAN backups?

When you have multiple databases you have to set your DBID (Database Id) which is unique to each database. You have to set this before you do any restore operation from RMAN.

There is possibility that incarnation may be different of your database. So it is advised to reset to match with the current incarnation. If you run the RMAN command ALTER DATABASE OPEN RESETLOGS then RMAN resets the

target database automatically so that you do not have to run RESET DATABASE. By resetting the database RMAN considers the new incarnation as the current incarnation of the database.

30. List at least 6 advantages of RMAN backups compare to traditional hot backups?

RMAN has the following advantages over Traditional backups:

1. Ability to perform INCREMENTAL backups

2. Ability to Recover one block of datafile

3. Ability to automatically backup CONTROLFILE and SPFILE

4. Ability to delete the older ARCHIVE REDOLOG files, with the new one's automatically.

5. Ability to perform backup and restore with parallelism.

6. Ability to report the files needed for the backup.

7. Ability to RESTART the failed backup, without starting from beginning.

8. Much faster when compared to other TRADITIONAL backup strategies.

31. How do you enable the autobackup for the controlfile using RMAN?

issue command at rman prompt.....

RMAN> configure controlfile autobackup on;

also we can configure controlfile backup format......

RMAN> configure controlfile autobackup format for device type disk to

2> '$HOME/BACKUP/RMAN/ F.bkp';

$HOME/BACKUP/RMAN/ this can be any desired location.

32. How do you identify what are the all the target databases that are being backed-up with RMAN database?

You don’t have any view to identify whether it is backed up or not . The only option is connect to the target database and give list backup this will give you the backup information with date and timing.

33. What is the difference between cumulative incremental and differential incremental backups?

Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.

Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 orlower.

34. How do you identify the block corruption in RMAN database? How do you fix it?

using v$block_corruption view u can find which blocks corrupted.

Rman>> block recover datafile <fileid> block <blockid>;

Using the above statement u recover the corrupted blocks.

First check whether the block is corrupted or not by using this command

sql>select file# block# from v$database_block_corruption;

file# block

2 507

the above block is corrupted...

conn to Rman

To recover the block use this command...

Rman>blockrecover dataile 2 block 507;

the above command recover the block 507

Now just verify it.....

Rman>blockrecover corruption list;

35. How do you clone the database using RMAN software? Give brief steps? When do you use crosscheck command?

Check whether backup pieces proxy copies or disk copies still exist.

Two commands available in RMAN to clone database:

1) Duplicate

2) Restore.

36. What is the difference between obsolete RMAN backups and expired RMAN backups?

The term obsolete does not mean the same as expired. In short obsolete means "not needed " whereas expired means "not found."

37. List some of the RMAN catalog view names which contain the catalog information?

RC_DATABASE_INCARNATION RC_BACKUP_COPY_DETAILS

RC_BACKUP_CORRUPTION

RC_BACKUP-DATAFILE_SUMMARY to name a few

38. What is db_recovery_file_dest ? When do you need to set this value?

If Database Flashback option is on then use this option.

39. How do you setup the RMAN tape backups?

RMAN Target /

run

{

Allocate channel ch1 device type sbt_tape maxpiecesize 4g

Format' D_ U_ T_ t';

sql 'alter system switch logfile';

Backup database;

backup archivelog from time 'sysdate-7';

Backup Format ' D_CTLFILE_P_ U_ T_ t' Current controlfile;

release channel ch1;

}

This is backup script for Tivoli Backup Server

40. How do you install the RMAN recovery catalog?

Steps to be followed:

1) Create connection string at catalog database.

2) At catalog database create one new user or use existing user and give that user a recovery_catalog_owner privilege.

3)Login into RMAN with connection string

a) export ORACLE_SID           

b) rman target catalog @connection string

4) rman> create catalog;

5) register database;

41. When do you recommend hot backup? What are the pre-reqs?

Database must be Archivelog Mode

Archive Destination must be set and LOG_ARCHIVE_START TRUE (EARLIER VERSION BEFORE 10G)

If you go through RMAN then

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/oracle/autobackup/ F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO

'/u01/app/oracle/product/10.2.0/db_2/dbs/snapcf_dba.f'; # default

42. What is the difference between physical and logical backups?

In Oracle Logical Backup is "which is taken using either Traditional Export/Import or Latest Data Pump". Where as Physical backup is known "when you take Physical O/s Database related Files as Backup".

43. What is RAID? What is RAID0? What is RAID1? What is RAID 10?

RAID: It is a redundant array of independent disk

RAID0: Concatenation and stripping

RAID1: Mirroring

44. What are things which play major role in designing the backup strategy?

I Believe in designing a good backup strategy it will not only be simply backup but also a contingency plan. In this case you should consider the following:

1. How long is the allowable down time during recovery? - If short you could consider using dataguard.

2. How long is the backup period? - If short I would advise to use RMAN instead of user managed backup.

3. If limited disk space for backup never use user managed backup.

4. If the database is large you could consider doing full rman backups on a weekend and do a incremental backup on a weekday.

5. Schedule your backup on the time where there is least database activity this is to avoid resource huggling.

6. Backup script should always be automized via scheduled jobs. This way operators would never miss a backup period.

7. Retention period should also be considered. Try keeping atleast 2 full backups. (current and previous backup).

Cold backup: shutdown the database and copy the datafiles with the help of

O.S. command. this is simply copying of datafiles just like any other text file copy.

Hot backup: backup process starts even though database in running. The process to take a hot backup is

1) sql> alter database begin backup;

2) copy the datafiles.

3) after copying

sql> alter database end backup;

Begin backup clause will generate the timestamp. it'll be used in backup consistency i.e. when begin backup pressed it'll generate the timestamp. During restore database will restore the data from backup till that timestamp and remaining backup will be recovered from archive log.

45. What is hot backup and what is cold backup?

Hot backup when the database is online cold backup is taken during shut down period

46. How do you test that your recovery was successful?

SQL> SELECT count(*) FROM flashback_table;

47. How do you backup the Flash Recovery Area?

A:RMAN> BACKUP RECOVERY FILES;

The files on disk that have not previously been backed up will be backed up. They are full and incremental backup sets, control file auto-backups, archive logs and datafile copies.

48. How to enable Fast Incremental Backup to backup only those data blocks that have changed?

A:SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

49. How do you set the flash recovery area?

A:SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

50. How can you use the CURRENT_SCN column in the V$DATABASE view to obtain the currentSCN?

A:SQL> SELECT current_scn FROM v$database;

51. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

You have to catalog that manual backup in RMAN's repository by command

RMAN> catalogdatafilecopy '/DB01/BACKUP/users01.dbf';

restrictions:> Accessible on disk> A complete image copyof a single file

52. In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?

using RMAN BLOCK RECOVER command

53. List advantages of RMAN backups compare to traditional hot backups?

RMAN has the following advantages over Traditional backups:

1. Ability to perform INCREMENTALbackups

2. Ability to Recover one block of datafile

3. Ability to automatically backup CONTROLFILEand SPFILE

4. Ability to delete the older ARCHIVE REDOLOG files

54. How do you identify the expired, active, obsolete backups? Which RMAN command you use?

Use command:

Rman > crosscheck backup;

Rman > crosscheck archivelog all;

Rman > listbackup;

Rman > list archive logall

55. How do you enable the autobackup for the controlfile using RMAN?

RMAN> configure controlfile autobackup on;

also we can configurecontrolfile backup format......

RMAN> configure control file auto backup format for device type disk

56. How do you identify what are the all the target databases that are being backed-up with RMAN database?

You don’t have any view to identify whether it is backed up or not . The only option is connect to the target database and give list backup, this will give you the backup information with date and timing

57. What is the difference between cumulative incremental and differential incremental backups?

Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.

Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 or lower

                    

58. Explain how to setup the physical stand by database with RMAN?

$ Export ORACLE_SID=TEST $ rman target /

 RMAN> show all;

Using target database controlfile instead of recovery catalog RMAN configuration parameters are:

 CONFIGURE RETENTIONPOLICY TO RECOVERY WINDOW OF 1 DAYS;

CONFIGURE BACKUP OPTIMIZATION

59. What is auxiliary channel in RMAN? When do you need this?

An auxiliary channel is a link to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel with in the same RUN command.

60. What is backup set?

RMAN can also store its backups in an RMAN-exclusive format which is called backup set. A backupset is a collection of backup pieces, each of which may contain one or more datafile backups

61. What is RMAN and how does one use it?

Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recoveringOracle Databases. RMAN ships with the database server and doesn't require a separate installation. TheRMAN executable is located in your ORACLE_HOME/bin directory.

62. What kind of backup are supported by RMAN?

Backup SetsDatafiles CopiesOS BackupWhat is the Flash Recovery Area?

A: It is a unified storage location for all recovery-related files and activities in an Oracle Database. Itincludes Control File, Archived Log Files, Flashback Logs, Control File Autobackups, Data Files, andRMAN files.

63. How do you define a Flash Recovery Area?

A: To define a Flash Recovery Area set the following Oracle Initialization Parameters.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

64. How do you use the V$RECOVERY_FILE_DEST view to display information regarding the flashrecovery area?

A:SQL> SELECT name, space_limit, space_used,space_reclaimable, number_of_filesFROM v$recovery_file_dest;

65. How can you display warning messages?

A:SQL> SELECT object_type, message_type,message_level, reason, suggested_actionFROM dba_outstanding_alerts;

66. How to use the best practice to use Oracle Managed File (OMF) to let Oracle database to create andmanage the underlying operating system files of a database?

A:SQL> ALTER SYSTEM SETdb_create_file_dest = ‘/u03/oradata/school’;

SQL> ALTER SYSTEM SETdb_create_online_dest_1 = ‘/u04/oradata/school’;

67. How to enable Fast Incremental Backup to backup only those data blocks that have changed?

A:SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

68. How do you monitor block change tracking?

A:SQL> SELECT filename, status, bytes FROM v$block_change_tracking;

It shows where the block change-tracking file is located, the status of it and the size.

69. How do you use the V$BACKUP_DATAFILE view to display how effective the block change trackingis in minimizing the incremental backup I/O?

A:SQL> SELECT file#, AVG(datafile_blocks), AVG(blocks_read),AVG (blocks_read/datafile_blocks), AVG(blocks)FROM v$backup_datafileWHERE used_change_tracking = ‘YES’ AND incremental_level > 0GROUP BY file#;If the AVG (blocks_read/datafile_blocks) column is high then you may have to decrease the timebetween the incremental backups.

70. How do you backup the entire database?

A:RMAN> BACKUP DATABASE;

71. How do you backup an individual tablespaces?

A:RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP TABLESPACE system;

72. How do you backup datafiles and control files?

A:RMAN> BACKUP DATAFILE 3;

RMAN> BACKUP CURRENT CONTROLFILE;
Use a fast recovery without restoring all backups from their backup location to the location specified inthe controlfile.

A:RMAN> SWITCH DATABASE TO COPY;

73. RMAN will adjust the control file so that the data files point to the backup file location and then starts recovery.Why use Rman ?

A. 1. No Extra Costs.. It is available free.

2.RMAN introduced in Oracle 8 it has become simpler with new version and easier that user managed backups.

3.Proper Security

4.You are 100% sure your database has been backed up .

5.It contains details of backup taken in the central repository

6.Facility of Testing validity of backups also command like cross check to checkthe status of backup.

7.Oracle 10g has got further optimized incremental backups with has resulted inimprovement of performance during backup

8.and recovery time

9.Parrallel operation are supported

10.Better Querying facility for knowing different details of backup.

11.No Extra redo generated when backup is taken. compared to online backup

12.Without rman.which results in saving of space in hard disk.

13.RMAN is an intelligent tool

14.Maintains repository of backup metadata.

15.Remembers backup locations

16.Knows what needs backup set locations

17.Knows what needs to be backed up

18.Knows what is required for recovery

19.Know what backups are redundant

20.It handles database corruptions

74. Oracle Enhancement for Rman in 10g

A. 1.Flash Recovery Area

2.Incrementally Updated Backups

3.Faster Incremental Backups

4.SWITCH DATABASE COMMAND.

5.Binary Compression

6.Global Scripting

7.Duration Clause

8.Configure This

9.Oracle Enhancement for Rman in 10g

10.Automatic Channel Failover

11.Compress Backup Sets

12.Recovery Through Reset Logs

13.Cross Backup Sets

75. Global Scripting

A.RMAN> print script full_backup to file 'my_script_file.txt'

Oracle Database 10g provides a new concept of global scripts, which you can executeagainst any database registered in the recovery catalog, as long as your RMAN client isconnected to the recovery catalog and a target database simultaneously.CPISOLUTION.COM

RMAN> create global script global_full_backup

76. Outline the steps for recovery of missing data file?

Losing Datafiles Whenever you are in NoArchivelog Mode:
###################################################
If you are in noarchivelog mode and you loss any datafile then whether it is temporary or permanent media failure, the database will automatically shut down. If failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files. If you have permanent media failure then restore a whole database from a good backup. How to restore a database is as follows:

If a media failure damages datafiles in a NOARCHIVELOG database, then the only option for recovery is usually to restore a consistent whole database backup. As you are in noarchivelog mode so you have to understand that changes after taken backup is lost.

If you logical backup that is export file you can import that also.

In order to recover database in noarchivelog mode you have to follow the following procedure.

1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;

2)If possible, correct the media problem so that the backup database files can be restored to their original locations.

3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.

4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:

RECOVER DATABASE UNTIL CANCEL
CANCEL

5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;

In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
alter system set control_files='+ORQ/orq1/controlfile/control01.ctl','+ORQ/orq1/controlfile/control02.ctl' scope=spfile;
STARTUP FORCE MOUNT;

In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,
ALTER DATABASE RENAME FILE '+ORQ/orq1/datafile/system01.dbf';'
TO '+ORQ/orq1/datafile/system02.dbf';

Losing Datafiles Whenever you are in Archivelog Mode:

###################################################
If the datafile that is lost is under SYSTEM tablespace or if it is a datafile contain active undo segments then database shuts down. If the failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files.

If the datafile that is lost in not under SYSTEM tablespace and not contain active undo segments then the affected datafile is gone to offline. The database remains open. In order to fix the problem take the affected tablespace offline and then recover the tablespace. 

77. Outline the steps for recovery with missing online redo logs?

 Redo log is CURRENT (DB was shut down cleanly)
If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss. It is advisable to take a full backup of DB immediately after the STARTUP.
 Redo log is CURRENT (DB was not shut down cleanly)
When a current redo log is lost, the transactions in the log file are also lost before making to archived logs. Since a DB startup can no more perform a crash recovery (since all the now-available online log files are not sufficient to startup the DB in consistent state), an incomplete media recovery is the only option. We will need to restore the DB from a previous backup and restore to the point just before the lost redo log file. The DB will need to be opened in RESETLOGSmode. There is some transaction loss in this scenario.RMAN> RESTORE CONTROLFILE FROM '<backup tag location>';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";
RMAN> ALTER DATABASE OPEN RESETLOGS;

78. Outline steps for recovery with missing archived redo logs?

If a redo log file is already archived, its loss can safely be ignored. Since all the changes in the DB are now archived and the online log file is only waiting for its turn to be re-written by LGWR (redo log files are written circularly) the loss of the redo log file doesnt matter much. It may be re-created using the commandSQL> STARTUP MOUNT;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP <group#>;
This will re-create all groups and no transactions are lost. The database can be opened normally after this.

79. What is FRA ? When do you use this ?

Flash recovery area where you can store not only the traditional components  found in a backup strategy such as control files, archived log files, and Recovery Manager (RMAN) datafile copies but also a number of other file
components such as flashback logs. The flash recovery area simplifies backup operations, and it increases the availability of the database because many backup and recovery operations using the flash recovery area can be performed when the database is open and available to users.

Because the space in the flash recovery area is limited by the initialization parameter DB_ RECOVERY_FILE_DEST_SIZE , the Oracle database keeps track of which files are no longer needed on disk so that they can be deleted when there is not enough free space for new files. Each time a file is deleted from the flash recovery area, a message is written to the alert log.

A message is written to the alert log in other circumstances. If no files can be deleted, and the recovery area used space is at 85 percent, a warning message is issued. When the space used is at 97 percent, a critical warning is
issued. These warnings are recorded in the alert log file, are viewable in the data dictionary view DBA_OUTSTANDING_ALERTS , and are available to you on the main page of the EM Database Control

80. What is Channel? How do you enable the parallel backups with RMAN?

Channel is a link that RMAN requires to link to target database. This link is required when backup and recovery operations are performed and recorded. This channel can be allocated manually or can be preconfigured by using
automatic channel allocation.

The number of allocated channels determines the maximum degree of parallelism that is used during backup, restore or recovery. For example, if you allocate 4 channels for a backup operation, 4 background processes for the operation can run concurrently.

Parallelization of backup sets allocates multiple channels and assigns files to specific channels. You can configure parallel backups by setting a PARALLELISM option of the CONFIGURE command to a value greater than 1 or by
manually allocating multiple channels.

RMAN> CONFIGURE DEVICE TYPE PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

81. What are RTO, MTBF, and MTTR?

RTO: Recovery Time objective-is the maximum amount of time that the database can be unavailable  and still stasfy SLA's
MTBF (Meant tiem Between Failure)-
MTTR (Mean tie to recover)-  fast recovery solutions

82. How do you enable the encryption for RMAN backups?

If you wish to modify your existing backup environment so that all RMAN backups are encrypted, perform the following steps:

· Set up the Oracle Encryption Wallet
· Issue the following RMAN command:

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256'; -- use 256 bit encryption
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; -- encrypt backups

83. What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.

SQL> connect SYS as SYSDBA

SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

RMAN> run {

  set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');

  restore database;

  recover database;

}