rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

set 1


1.What is RMAN?

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

2.What is the difference between using recovery catalog and control file?

 When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.

In recovery catalog we can store scripts.

 Recovery catalog is central and can have information of many databases.

3.Can we use same target database as catalog?

     No, The recovery catalog should not reside in the target database (database should be backed up), because the database can’t be
   recovered in the mounted state.

4.How do you know that how much RMAN task has been completed?

   By querying v$rman_status or v$session_longops

5.From where list & report commands will get input?

Both the commands command quering v$ and recovery catalog views. V$BACKUP_FILES or many of the recovery catalog views such as
RC_DATAFILE_COPY or RC_ARCHIVED_LOG.

6.Command to delete archive logs older than 7days?

RMAN> delete archivelog all completed before sysdate-7;

How many times does oracle ask before dropping a catalog?

7.The default is two times one for the actual command, the other for confirmation.

8.How to view the current defaults for the database.

RMAN> show all;

9.What is the use of crosscheck command in RMAN?

Crosscheck will be useful to check whether the catalog information is intact with OS level information. This command only updates
repository records with the status of the backups.

e.g. If user removes archived logs from disk with an operating system command, the repository still indicates that the logs are on
 disk, when in fact they are not.



10.What are the differences between crosscheck and validate commands?

Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified
backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape.
The crosscheck command only processes files created on the same device type as the channel running crosscheck.

11.Which one is good, differential (incremental) backup or cumulative (incremental) backup?

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Cumulative backups are preferable to differential backups when recovery time is more important than disk space,
****because during recovery each differential backup must be applied in succession. Use cumulative incremental backups
instead of differential, if enough disk space is available to store cumulative incremental backups.

This is command for taking Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

12.What is the difference between backup set and backup piece?

Backup set is logical and backup piece is physical.

13.RMAN command to backup for creating standby database

RMAN> duplicate target database

14.You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have
backup of this (newly created) datafile. How do you restore/recover file?

Create data file and recover datafile.

SQL> alter database create datafile ‘/u01/app/oracle/oradata/xyz.dbf’ size 2G;

RMAN> recover datafile file_id;

15.What is obsolete backup & expired backup?

A status of “expired” means that the backup piece or backup set is not found in the backup destination.

A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since
 RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

16.What is the difference between hot backup & RMAN backup?

For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.



17.How to put manual/user-managed backup in RMAN (recovery catalog)?

By using catalog command.

RMAN> CATALOG START WITH ‘/tmp/backup.ctl’;

18. What are the Architectural components of RMAN?

RMAN Executables
Sercer process
Channels
Target database
Recovery catalog database (optional)
Media management Layer (optional)
Backups, backup sets and backup pieces

19.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:

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
Maximize size of files created on I/O devices
Maximize rate at which database files are read
Maximize number of files open at a time

20.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.

21.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.

22.What are the benefits of using RMAN?

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

23.What are the various reports available with RMAN

RMAN>list backup;

RMAN> list archive;

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

using RMAN BLOCK RECOVER command

25.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’;

26.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.

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

Using v$block_corruption view you can find which blocks corrupted.

RMAN> blockrecover datafile <fileid> block <blockid>;

Using the above statement You 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 datafile 2 block 507;

the above command recover the block 507

Now just verify it…..

Rman>blockrecover corruption list;

28.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.

29.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

30.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;

31.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 Os Database related Files as Backup”.

32.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

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

SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

34.How do you set the flash recovery area?


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

35.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 within the same RUN command.

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

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

37.How can you display warning messages?

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

38.How do you backup the entire database?

RMAN> BACKUP DATABASE;

39.How do you backup an individual tablespaces?

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN> BACKUP TABLESPACE system;

40.How do you backup datafiles and control files?

RMAN> BACKUP DATAFILE 3;

RMAN> BACKUP CURRENT CONTROLFILE;
41. What is the difference between to back up the current control file and to backup up control file copy?  
                                                                         If you backup “current control file” you backup control file which is currently open by an instance where as If you backup “controlfile file
copy" you backup the copy of control file which is created either with SVRMGRL command "alter system backup controlfile to .." or with
RMAN command "copy current controlfile to ...".
In the other words, the control file copy is not current controlfile backup current controlfile creates a BACKUPSET containing controlfile.
You don't have to give the FILENAME where as backup controlfile copy creates a BACKUPSET from a copy of controlfile.
You have to give the FILENAME.                                                                                                                                                                                                                      

42. How much of overhead in running BACKUP VALIDATE DATABASE and RESTORE VALIDATE DATABASE commands to check for block corruptions using RMAN?
Can I run these commands anytime?      
Backup validate works against the backups not against the live database so no impact on the live database, same for restore validate
they do not impact the real thing (it is reading the files there only).                                                                                                                          

43. Is there a way to force rman to use these obsolete backups or once it is marked obsolete?                                                        
As per my understanding it is just a report, they are still there until you delete them.
                                                                       
44. Can I use the same snapshot controlfile to backup multiple databases(one after another) running on the same server?      
This file is only use temporarily like a scratch file.  Only one rman session can access the snapshot controlfile at any time so
this would tend to serialize your backups if you do that.        

45. Why does not oracle keep RMAN info after recreating the controlfile?
  Creating the new controlfile from scratch how do you expect the create controlfile to "make up" the missing data?
 that would be like saying similarly we have drop and recreated my table and now it is empty similarly here recreating from the
  scratch means the contents there will be naturally will be gone.
  Use the rman catalog to deal this situation. It is just a suggestion.
                                       
46 What is the advantage of using PIPE in rman backups? In what circumstances one would use PIPE to backup and restore?  
                                                                                                                                                                                   
It lets 3rd parties (anyone really) build an alternative interface to RMAN as it permits anyone                                                                
that can connect to an Oracle instance to control RMAN programmatically.
                                                                                                                                                                                                                       
47. How To turn Debug Feature on in rman?                                                                                                                                  

run {                                                                                                                                                            
allocate channel c1 type disk;                                                                                                                                            
debug on;                                                                                                                                                                              
}                                                                                                                                                                              
rman>list backup of database;                                                                                                                                    
now you will see a output                                                                                                                                  
You can always turn debug off by issuing                                                                                                                              
rman>debug off;    
             
8) Assuming I have a "FULL" backup of users01.dbf containing employees table that contains 1000 blocks of data.
If I truncated employees table and then an incremental level 1 backup of user’s tablespace is taken, will RMAN include 1000 blocks that once contained data in the incremental backup?
                     
The blocks were not written to the only changes made by the truncate was to the data dictionary (or file header) so no, it won't see them as changed blocks since they were not changed.                                                                                                                      
9)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 is that the target database will be shutdown while datafiles are restored.              

8)How many times does oracle ask before dropping a catalog?

The default is two times one for the actual command, the other for confirmation.                    
9) What are the various reports available with RMAN?

rman>list backup;
rman> list archive;
   
10) What is the use of snapshot controlfile in terms of RMAN backup?
   
Rman uses the snapshot controlfile as a way to get a read consistent copy of the controlfile,
 it uses this to do things like RESYNC the catalog (else the controlfile is a ‘moving target’, constantly changing and Rman would get blocked and block the database)                                    
11) Can RMAN write to disk and tape Parallel? Is it possible?

Rman currently won't do tape directly, y
ou need a media manager for that, regarding disk and tape parallel not as far as I know, you would run two backups separately (not sure).
May be trying to maintain duplicate like that could get the desired.
                                                                                                                                       
12) 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 backed up 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;                                                                                          
13) Is it possible to restore a backupset (actually backup pieces) from a different location to where RMAN has recorded them to be.    
                                                                                                                                                         
With 9.2 and earlier it is not possible to restore a backupset (actually backup pieces) from a                                                                                                                                                                                      
different location to where RMAN has recorded them to be. As a workaround you would have to create a link using the location of where the backup was originally located.
Then when restoring, RMAN will think everything is the same as it was.                                                      
Starting in 10.1 it is possible to catalog the backup pieces in their new location into the                                                                  
controlfile and recovery catalog. This means they are available for restoration by RMAN without creating the link.                                                                                                          
14) What is difference between Report obsolete and Report obsolete orphan
                                                                                   
Report obsolete backup are reported unusable according to the user’s retention policy where as Report obsolete orphan report the backup that are unusable because they belong to incarnation of the database that are not direct ancestor of the current incarnation.                                  

15) How to Increase Size of Redo Log

1. Add new log files (groups) with new size                                                                                                            
ALTER DATABASE ADD LOGFILE GROUP…                                                                                                                                                                                                                                              
2. Switch with ‘alter system switch log file’ until a new log file group is in state current                                                      
3. Now you can delete the old log file                                                                                                    
ALTER DATABASE DROP LOGFILE MEMBER…    
                                                                 
16)  What is the difference between alter database recover and sql*plus recover command?
                                                                                   
ALTER DATABASE recover is useful when you as a user want to control the recovery where as SQL*PLUS recover command is useful when we prefer automated recovery.                            
Difference of two view V$Backup_Set and Rc_Backup_Set in respect of Rman                                                                                      
The V$Backup_Set is used to check the backup details when we are not managing Rman catalog that is the backup information is stored in controlfile where as Rc_Backup_Set is used when we are using catalog as a central repository to list the backup information.                                  
17) Can I cancel a script from inside the script? How I cancil a select on Windows client?          
Use ctl-c                                                                                                                      
18) How to Find the Number of Oracle Instances Running on Windows Machine                                                                                

C:\>net start |find “OracleService”                                                                                  
19) How to create an init.ora from the spfile when the database is down?
 
Follow the same way as you are using                                                                                                              
SQL> connect sys/oracle as sysdba                                                                                                                  
SQL> shutdown;                                                                                                                                        
SQL> create pfile from spfile;                                                                                                                            
SQL> create spfile from pfile;          
                                                                       
20) When you shutdown the database, how does oracle maintain the user session i.e.of sysdba?
You still have your dedicated server                                                                                                                            
!ps -auxww | grep ora920                                                                                                                                
sys@ORA920> !ps -auxww | grep ora920                                                                                                                      
sys@ORA920> shutdown                                                                                                                                            
sys@ORA920> !ps -auxww | grep ora920                                                                                                                                                                                                                                                        
You can see you still have your dedicated server. When you connect as sysdba, you fire up dedicated server that is where it is.                                                                                                                                                                              

21) What is ORA-002004 error? What you will do in that case?

A disk I/O failure was detected on reading the control file.
Basically you have to check whether the control file is available, permissions are right on the control file,
spfile/init.ora right to the right location, if all checks were done still you are getting the error, then from the multiplexed control file overlay on the corrupted one.                                                                                                                                                                                                                                                            
Let us say you have three control files control01.ctl, control02.ctl and control03.ctl and now you are getting errors on control03.ctl then just copy control01.ctl over to control03.ctl and you should be all set.                                                                                                            
In order to issue ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
database should be mounted and in our case it is not mounted then the only other option available is to restore control file from backup or copy the multiplexed control file over to the bad one.                                                                                                                                                                                        

22) Why do we need SCOPE=BOTH clause?    

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again. If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.                                                                                                                                                                                            

23) How to know Number of CPUs on Oracle  

Login as SYSDBA                                                                                                                                                        
SQL>show parameter cpu_count                                                                                                                                                        
NAME TYPE VALUE                                                                                                                                                              
cpu_count integer 2
             
24) Could you please tell me what are the possible reason for Spfile corruption and Recovery?
                                                                         
It should not be corrupt under normal circumstances, if it were, it would be a bug or failure of some component in your system. It could be a file system error or could be a bug.                      
You can easily recover however from                                                                                                                                      
a) Your alert log has the non-default parameters in it from your last restart.                                                                                            
b) it should be in your backups                                                                                                                                              
c) strings spfile.ora > init$ORACLE_SID.ora - and then edit the resulting file to clean it up would be options.                                                                                                                                                                                                  

25) How you will check flashback is enabled or not?
 
Select flashback_on from v$database;                
                                                           
26) In case Revoke CREATE TABLE Privilege from an USER giving ORA-01952. What is the issue? How to do in that case?                                                                                                
SQL> revoke create table from Pay_payment_master;
ORA-01952: system privileges not granted to ‘PAY_PAYMENT_MASTER’                                                                                          
This is because this privilege is not assigned to this user directly rather it was assigned through role “CONNECT”
If you remove connect role from the user then you will not be able to create session (Connect) to database.
So basically we have to Revoke the CONNECT Role and Grant other than create table privilege to this user.                                                                                                                                                                                                                                                                        

27) What kind of information is stored in UNDO segments?  

Only before image of data is stored in the UNDO segments. If transaction is rolled back information from UNDO is applied to restore original datafile. UNDO is never multiplexed.            

28) How to Remove Oracle Service in windows environment?

We can add or remove Oracle Service using oradim which is available in ORACLE_HOME/bin                                      
C:\Oradim –delete –sid                                                                                                                                                              
or                                                                                                                                                                    
Oradim –delete –svrc                                                                                                      
29) Why ORA-28000: the account is locked? What you will do in that case?

The Oracle 10g default is to lock an account after 10 bad password attempts and giving ORA-28000: the account is locked.
In that case one of the solutions is increase default limit of the login attempts.                                                                                                    
SQL> Alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;                                    
30) How to Reduce the Physical Reads on Statistics?                                                                                                                    
You need to increase the Buffer Cache                                                                                                                          
Consider the situation Buffer Cache of the database is 300MB.
One SQL gave the Physical read as 100. I increased as 400MB and now the same SQL giving the Physical read value is 0                                                                                                  
31) How many redo groups are required for a Oracle DB?                                                                                                                
At least 2 redo groups are required for a Oracle database to be working normally.                    
32) My spfile is corrupt and now I cannot start my database running on my laptop. Is there a way to build spfile again?                                                                                                                                                                                              
if you are on unix then                                                                                                                                                
$ cd $ORACLE_HOME/dbs                                                                                                                                              
$ strings spfilename  temp_pfile.ora                                                                                                                                  
edit the temp_pfile.ora, clean it up if there is anything "wrong" with it and then                                                                                  
SQL> startup pfile=temp_pfile.ora                                                                                                                                        
SQL> create spfile from pfile;                                                                                                                                            
SQL> shutdown                                                                                                                                                      
SQL> startup                                                                                                                                                                      
On windows -- just try editing the spfile [do not try with the prod db first try to check on test db. It can be dangerous], create a pfile from it.  save it,
and do the same or if you got problem you can startup the db from the command line using sqlplus create a pfile, do a manual startup (start the oracle service, then use sqlplus to start the database)                                                                                                                                                                                                                                            

33) What is a fractured block? What happens when you restore a file containing fractured block?                                                                  
A block in which the header and footer are not consistent at a given SCN.
 In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file.
It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half,
while the second half contains older data. In this case, the block is fractured.                                                                                                                                            
For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem.
When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.                                                  
The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time.
If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.                                                                                                      
34) You recreated the control file by “using backup control file to trace” and using alter database backup controlfile to ‘location’ command what have you lost in that case?                              
You lost all of the backup information as using backup controlfile to trace where as using other ALTER DATABASE BACKUP CONTROLFILE to ‘D:\Backup\control01.ctl’.
All backup information is retained when you take binary control file backup.    
                                                     
35) If a backup is issued after “shutdown abort” command what kind of backup                                                                                          
is that?                                                                                                                                                                                                                                                        
It is an inconsistent backup.
If you are in noarchivelog mode ensure that you issue the shutdown immediate command or startup force is another option that you can issue:
startup force->shutdown abort;
followed by
shutdown immediate;                                                                                                    
36)  What is split brain

What is split brain ?
In RAC environment, server nodes communicate with each other using High speed private interconnects
network. A split brain situation happens when all the links of the private interconnect fail to respond to
each other but instances are still up and running. So each instance thinks that the other nodes/instances are
dead and that it should take over the ownership.
In split brain situation, instances independtly access the data and modify the same blocks and the database
will end up with changed database overwritten which could lead to data corruption. To avoid this, various
algorithm are implemented to handle split brain scenario.
In RAC, the IMR (Instance Membership Recovery) service is one of the one of the efficient algorithm
used to detect & resolve the split-brain syndrome. When one instance fails to communicate with other
instances or when one instance becomes inactive due to any reason and is unable to issue the control file
heartbeat, the split brain is detected and the detecting instance will evict the failed instance from the
database.This process is called node eviction.

37) What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?

Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell

38) What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages

(ONS, VIP, listener, instances, etc.)?

Ans: $ocrcheck

39) How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?

using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:

*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect

40) 15.What is the Purpose of the voting disk in Oracle 10g Clusterware?

Voting disk record node membership information.
Oracle Clusterware uses the voting disk to determine which instances are members of a cluster.
The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks.
If you configure a single voting disk, then you should use external mirroring to provide redundancy.
You can have up to 32 voting disks in your cluster.

41) Data Guard Protection Modes :

In some situations, a business cannot afford to lose data at any cost.
In other situations, some applications require maximum database performance and can tolerate a potential loss of data.
 Data Guard provides three distinct modes of data protection to satisfy these varied requirements:

*Maximum Protection—> This mode offers the highest level of data protection.
Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode.
 If the last standby database configured in this mode becomes unavailable, processing stops on the primary database.
This mode ensures no-data-loss, even in the event of multiple failures.

*Maximum Availability—> This mode is similar to the maximum protection mode, including zero data loss.
However, if a standby database becomes unavailable (for example, because of network connectivity problems),
processing continues on the primary database.
When the fault is corrected, the standby database is automatically resynchronized with the primary database.
This mode achieves no-data-loss in the event of a single failure (e.g. network failure, primary site failure . . .)

*Maximum Performance—> This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode.
 In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database.
The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database.
If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.

42) Connection hanging? what are the possibilities?

possibilities for Oracle hanging include:
External issues - The network being down, Kerberos security issues, SSO or a firewall issue can cause an Oracle connection to hang.
One way to test this is to set sqlnet.authentication_services=(none) in your sqlnet.ora file and retry connecting.
Listener is not running - Start by checking the listener (check lsnrctl stat). Also, see my notes on diagnosing Oracle network connectivity issues.
No RAM - Over allocation of server resources, usually RAM, whereby there is not enough RAM to spawn another connection to Oracle.
Contention - It is not uncommon for an end-user session to “hang” when they are trying to grab a shared data resource that is held by another end-user.
The end-user often calls the help desk trying to understand why they cannot complete their transaction, and the Oracle professional must quickly identify the source of the contention."

43) What is Partition Pruning ?

Partition Pruning: Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

44) FAN in RAC.

With Oracle RAC in place, database client applications can leverage a number of high availability features including:
Fast Connection Failover (FCF): Allows a client application to be immediately notified of a planned or unplanned database service outage by subscribing to Fast Application Notification (FAN) events.
Run-time Connection Load-Balancing: Uses the Oracle RAC Load Balancing Advisory events to distribute work appropriately across the cluster nodes and to quickly react to changes in cluster configuration, overworked nodes, or hangs.
Connection Affinity (11g recommended/required): Routes connections to the same database instance based on previous connections to an instance to limit performance impacts of switching between instances.
RAC supports web session and transaction-based affinity for different client scenarios.


45) Why extra standby redo log group?

Determine the appropriate number of standby redo log file groups.
Minimally, the configuration should have one more standby redo log file group
than the number of online redo log file groups on the primary database....
(maximum number of logfiles for each thread + 1) * maximum number of threads
Using this equation reduces the likelihood that the primary instance's log
writer (LGWR) process will be blocked because a standby redo log file cannot be
allocated on the standby database. For example, if the primary database has 2
log files for each thread and 2 threads, then 6 standby redo log file groups
are needed on the standby database."

I think it says that if you have groups #1 and #2 on primary and #1, #2 on
standby, and if LGWR on primary just finished #1, switched to #2, and now it
needs to switch to #1 again because #2 just became full, the standby must catch
up, otherwise the primary LGWR cannot reuse #1 because the standby is still
archiving the standby's #1. Now, if you have the extra #3 on standby, the
standby in this case can start to use #3 while its #1 is being archived. That
way, the primary can reuse the primary's #1 without delay.

46) how to take voting disk backup ?

On 10gR2 RAC used "Can be done online" for backup voting disk but in 11g you cannot use DD online (use oracle command to do it).

First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes if you want to add/restore voting disk.

Then, determine the current voting disk by issuing the following command:

crsctl query votedisk css

issue the dd or ocopy command to back up a voting disk, as appropriate.

Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name

47) What is the Oracle Recommendation for backing up voting disk?

Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.

48) How do you restore a voting disk?

To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk

49) How can we add and remove multiple voting disks?

If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands,
where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path

50) How do we stop Oracle Clusterware?When do we stop it?

Before making any modification to the voting disk, as root user,
stop Oracle Clusterware using the crsctl stop crs command on all nodes.

51) How do we add voting disk?

To add a voting disk, issue the following command as the root user,
replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force

52) How do we move voting disks?

To move a voting disk, issue the following commands as the root user,
 replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force

53) How do we remove voting disks?

To remove a voting disk,
issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force

54) What should we do after modifying voting disks?

After modifying the voting disk,
restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk

55) When can we use -force option?

If our cluster is down, then we can include the -force option to modify the voting disk configuration,
without interacting with active Oracle Clusterware daemons.
However, using the -force option while any cluster node is active may corrupt our configuration.

56) How to find Cluster Interconnect IP address from Oracle Database ?

Hello, The easiest way to find the cluster interconnect is to view the “hosts” file. The “hosts” file is located under: UNIX .......... /etc
Windows ...... C:\WINDOWS\system32\drivers\etc
Following are the ways to find the cluster interconnect through Oracle database:
1) Query X$KSXPIA
The following query provides the interconnect IP address registered with Oracle database:
view plaincopy to clipboardprint?
SQL> select IP_KSXPIA from x$ksxpia where PUB_KSXPIA = 'N';
IP_KSXPIA
----------------
192.168.10.11
This query should be run on all instances to find the private interconnect IP address used on their respective nodes.

2) Query GV$CLUSTER_INTERCONNECTS view
Querying GV$CLUSTER_INTERCONNECTS view lists the interconnect used by all the participating instances of the RAC database.
view plaincopy to clipboardprint?
SQL> select INST_ID, IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;
INST_ID IP_ADDRESS
---------- ----------------
1 192.168.10.11
2 192.168.10.12

57)  How to Identify master node in RAC ?

Grep crsd log file
# /u1/app/../crsd>grep MASTER crsd.log | tail -1

(or)

cssd >grep -i  "master node" ocssd.log | tail -1

OR You can also use V$GES_RESOURCE view to identify the master node.

58) how to monitor block transfer interconnects nodes in rac ?

The v$cache_transfer   and v$file_cache_transfer  views are used to examine RAC statistics.
The types of blocks that use the cluster interconnects in a RAC environment are monitored with the v$ cache transfer series of views:

v$cache_transfer: This view shows the types and classes of blocks that Oracle transfers over the cluster interconnect on a per-object basis.
 The forced_reads and forced_writes columns can be used to determine the types of objects the RAC instances are sharing.
Values in the forced_writes column show how often a certain block type is transferred out of a local buffer cache due to the current version being requested by another instance.

59) what is global cache service monitoring?

Global Cache Services (GCS) Monitoring

The use of the GCS relative to the number of buffer cache reads, or logical reads can be estimated
by dividing the sum of GCS requests (global cache gets   + global cache converts   + global cache cr blocks received   + global cache current blocks received )
by the number of logical reads (consistent gets   + db block gets ) for a given statistics collection interval.
A global cache service request is made in Oracle when a user attempts to access a buffer cache to read or modify a data block and the block is not in the local cache.
A remote cache read, disk read or change access privileges is the inevitable result.
These are logical read related. Logical reads form a superset of the global cache service operations.


===========================================================================================================================================================================================================================================

Rman Interview Questions:

2. Difference between using recovery catalog and control file?
When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.
In recovery catalog, we can store scripts.
Recovery catalog is central and can have information of many databases.

3. Can we use same target database as catalog?
No. The recovery catalog should not reside in the target database (database to be backed up), because the database can't be recovered in the mounted state.

4. How do u know how much RMAN task has been completed?
By querying v$rman_status or v$session_longops

5. From where list & report commands will get input?

6. Command to delete archive logs older than 7days?
RMAN> delete archivelog all completed before sysdate-7;

7. How many days backup, by default RMAN stores?

8. What is the use of crosscheck command in RMAN?
Crosscheck will be useful to check whether the catalog information is intact with OS level information.

9. What are the differences between crosscheck and validate commands?

10. Which is one is good, differential (incremental) backup or cumulative (incremental) backup?
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

11. What is Level 0, Level 1 backup?
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

12. Can we perform level 1 backup without level 0 backup?
If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility < 10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup. If compatibility is >= 10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN.

13.  Will RMAN put the database/tablespace/datafile in backup mode?
Nope.

14. What is snapshot control file?

15. What is the difference between backup set and backup piece?
Backup set is logical and backup piece is physical.

16. RMAN command to backup for creating standby database?
RMAN> duplicate target database to standby database ....

17. How to do cloning by using RMAN?
RMAN> duplicate target database …

18. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?
create the datafile and recover that datafile.
SQL> alter database create datafile ‘…path..’ size n;
RMAN> recover datafile file_id;

19. What is obsolete backup & expired backup?
A status of "expired" means that the backup piece or backup set is not found in the backup destination.
A status of "obsolete" means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

20. What is the difference between hot backup & RMAN backup?
For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.

21. How to put manual/user-managed backup in RMAN (recovery catalog)?
By using catalog command.
RMAN> CATALOG START WITH '/tmp/backup.ctl';

22. What are new features in Oracle 11g RMAN?

23. What is the difference between auxiliary channel and maintenance channel?



===========================================================================================================================================================================================================================================





RMAN Question & Answers

What is RMAN and How to configure it?

  RMAN is an Oracle Database client
  It performs backup and recovery tasks on your databases and automates administration of your backup strategies
  It greatly simplifies the dba jobs by managing the production database's backing up, restoring, and recovering database files
  This tool integrates with sessions running on an Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups
 There is no additional installation required for this tool
  It is by default get installed with the oracle database installation
  The RMAN environment consists of the utilities and databases that play a role in acking up your data
  We can access RMAN through the command line or through Oracle Enterprise Manager

2) Why to use RMAN?
     RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

Automatic specification of files to include in a backup
Establishes the name and locations of all files to be backed up

Maintain backup repository
   Backups are recorded in the control file, which is the main repository of RMAN metadata
  Additionally, you can store this metadata in a recovery catalog

Incremental backups
Incremental backup stores only blocks changed since a previous backup
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery
Unused block compression:
  In unused block compression, RMAN can skip data blocks that have never been used

Block media recovery
 We can repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup

Binary compression
A binary compression mechanism integrated into Oracle Database reduces the size of backups

Encrypted backups
  RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format

Corrupt block detection
RMAN checks for the block corruption before taking its backup

3) How RMAN works?
 RMAN backup and recovery operation for a target database are managed by RMAN client
 RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations
  The RMAN client itself does not perform backup, restore, or recovery operations
  When you connect the RMAN client to a target database, RMAN allocates server sessions on the target instance and directs them to perform the operations
  The work of backup and recovery is performed by server sessions running on the  target database
  A channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance
  The channel reads data into memory, processes it, and writes it to the output device
  When you take a database backup using RMAN, you need to connect to the target database using RMAN Client
  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
  For backup you need to allocate explicit or implicit channel to the target database
An RMAN channel represents one stream of data to a device, and corresponds to one database server session.
 This session dynamically collect information of the files from the target database control file before taking the backup or while restoring
  For example if you give ' Backup database ' from RMAN, it will first get all the datafiles information from the controlfile
  Then it will divide all the datafiles among the allocated channels. (Roughly equal size of work as per the datafile size)
  Then it takes the backup in 2 steps

Step1:
The channel will read all the Blocks of the entire datafile to find out all the formatted blocks to backup

Note:
  RMAN do not take backup of the unformatted blocks

Step2:
  In the second step it takes back up of the formatted blocks

Example:
This is the best advantage of using RMAN as it only takes back up of the required blocks
  Lets say in a datafile of 100 MB size, there may be only 10 MB of use full data and rest 90 MB is free then RMAN will only take backup of those 10 MB

4) What O/S and oracle user privilege required using RMAN?
  RMAN always connects to the target or auxiliary database using the SYSDBA privilege
  RMAN always connects to the target or auxiliary database using the SYSDBA privilege
  Its connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database
  The O/S user should be part of the DBA group
  For remote connection it needs the password file Authentication
  Target database should have the initialization parameter REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE or SHARED

5) RMAN terminology:

A target database:
  An Oracle database to which RMAN is connected with the TARGET keyword
  A target database is a database on which RMAN is performing backup and recovery operations
  RMAN always maintains metadata about its operations on a database in the control file of the database

A recovery Catalog:
  A separate database schema used to record RMAN activity against one or more target databases
  A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file
  The database may overwrite older records in the control file, but RMAN maintains records forever in the catalog unless deleted by the user

Backup sets:
RMAN can store backup data in a logical structure called a backup set, which is the smallest unit of an RMAN backup
  One backup set contains one or more datafiles a section of datafile or archivelogs

Backup Piece:
 A backup set contains one or more binary files in an RMAN-specific format
  This file is known as a backup piece
  Each backup piece is a single output file
  The size of a backup piece can be restricted; if the size is not restricted, the backup set will comprise one backup piece
  Backup piece size should be restricted to no larger than the maximum file size that your filesystem will support

Image copies:
An image copy is a copy of a single file (datafile, archivelog, or controlfile)
  It is very similar to an O/S copy of the file
  It is not a backupset or a backup piece
  No compression is performed

Snapshot Controlfile:
When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file
  The default name for the snapshot control file is port-specific

Database Incarnation:
Whenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database
  The new version of the reset database is called a new incarnation
  The reset database command directs RMAN to create a new database incarnation record in the recovery catalog
  This new incarnation record indicates the current incarnation


6) What is RMAN Configuration and how to configure it?

The RMAN backup and recovery environment is preconfigured for each target database
  The configuration is persistent and applies to all subsequent operations on this target database, even if you exit and restart RMAN
  RMAN configured settings can specify backup devices, configure a connection to a backup device , policies affecting backup strategy, encryption algorithm, snap shot controlfile loaion  and others
  By default there are few default configuration are set when you login to RMAN
  You can customize them as per your requirement
  Any time you can check the current setting by using the "Show all” command
  CONFIGURE command is used to create persistent settings in the RMAN environment, which apply to all subsequent operations, even if you exit and restart RMAN

7) How to check RMAN configuration?
RMAN>Show all;

8) How to reset to default configuration?
To reset the default configuration setting use Connect to the target database from sqlplus and run
SQL> connect @target_database;
SQL> execute dbms_backup_restore.resetConfig;

RMAN Catalog Database

9) What is Catalog database and How to configure it?
 This is a separate database which contains catalog schema
  You can use the same target database as the catalog database but it’s not at all recommended

10) How Many catalog database I can have?

You can have multiple catalog databases for the same target database
  But at a time you can connect to only 1 catalog database via RMAN. Its not recommended to have multiple catalog database

11) Is this mandatory to use catalog database?
       No! It’s an optional one



12) What is the advantage of catalog database?
 Catalog database is a secondary storage of backup metadata
  It’s very useful in case you lost the current controlfile, as all the backup information are there in the catalog schema
  Secondly from contolfile the older backup information are aged out depending upon the control_file_record_keep_time
  RMAN catalog database mainten the history of data

13) What is the difference between catalog database & catalog schema?
Catalog database is like any other database which contains the RMAN catalog user's schema


14)  What happen if catalog database lost?

Since catalog database is an optional there is no direct effect of loss of catalog database
  Create a new catalog database and register the target database with the newly created catalog one All the backup information from the target database current controlfile will be updated to the catalog schema
  If any backup information which is aged out from the target database then you need to manually catalog those backup pieces

RMAN backup:

15)  What are the database file's that RMAN can backup?
 RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile

16) What are the database file's that RMAN cannot backup?
RMAN can not take backup of the pfile, Redo logs, network configuration files, password files, external tables and the contents of the Oracle home files

17) Can I have archivelogs and datafile backup in a single backupset?

    No.  We can not put datafiles and archive logs in the same backupset

18)  Can I have datafiles and contolfile backup in a single backup set?
 Yes
  If the controlfile autobackup is not ON then RMAN takes backup of controlfile along with the datafile 1, whenever you take backup of the database or System tablespace

19) Can I regulate the size of backup piece and backup set?
 Yes!
  You can set max size of the backupset as well as the backup piece
  By default one RMAN channel creates a single backupset with one backup piece in it
  You can use the MAXPIECESIZE channel parameter to set limits on the size of backup pieces
  You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets

20) What is the difference between backup set backup and Image copy backup?

 A backup set is an RMAN-specific proprietary format, whereas an image copy is a bit-for-bit copy of a file
  By default, RMAN creates backup sets

21) What is RMAN consistent backup and inconsistent backup?

 A consistent backup occurs when the database is in a consistent state
  That means backup of the database taken after a shutdown immediate, shutdown normal or shutdown transactional
  If the database is shutdown with abort option then its not a consistent backup
A backup when the database is up and running is called an inconsistent backup
  When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs
  You can not take inconsistent backup when the database is in Noarchivelog mode

22)  Can I take RMAN backup when the database is down?

   No!
   You can take RMAN backup only when the target database is Open or in Mount stage
   It’s because RMAN keep the backup metadata in controfile
   Only in open or mount mode controlfile is accessible

23)  Do I need to place the database in begin backup mode while taking RMAN inconsistent backup?

       RMAN does not require extra logging or backup mode because it knows the format of data blocks
       RMAN is guaranteed not to back up fractured blocks
       No extra redo is generated during RMAN backup


24) Can I compress RMAN backups?

ü  RMAN supports binary compression of backup sets
ü  The supported algorithms are BZIP2 (default) and ZLIB
ü  It’s not recommended to compress the RMAN backup using any other OS or third party utility

Note:
ü  RMAN compressed backup with BZIP2 provides great compression but is CPU intensive
ü  Using ZLIB compression requires the Oracle Database 11g Advanced Compression Option and is only supported with an 11g database
ü  The feature is not backward compatible with 10g databases

25) Can I encrypt RMAN backup?

ü  RMAN supports backup encryption for backup sets
ü  You can use wallet-based transparent encryption, password-based encryption, or both
ü  You can use the CONFIGURE ENCRYPTION command to configure persistent transparent encryption
ü  Use the SET ENCRYPTION, command at the RMAN session level to specify password-based encryption

26)  Can RMAN take backup to Tape?

ü  Yes!
ü  We can use RMAN for the tape backup
ü  But RMAN can not able to write directly to tape
ü  You need to have third party Media Management Software installed
ü  Oracle has published an API specification which Media Management Vendor's who are members of Oracle's Backup Solutions Partner program have access to
ü  Media Management Vendors (MMVs) then write an interface library which the Oracle server uses to write and read to and from tape
Starting from oracle 10g R2 oracle has its Own Media management software for the database backup to tape called OSB


27) How RMAN Interact with Media manager?

ü  Before performing backup or restore to a media manager, you must allocate one or more channels or configure default channels for use with the media manager to handle the communication with the media manager
ü  RMAN does not issue specific commands to load, label, or unload tapes
ü  When backing up, RMAN gives the media manager a stream of bytes and associates a unique name with this stream
ü  When RMAN needs to restore the backup, it asks the media manager to retrieve the byte stream
ü  All details of how and where that stream is stored are handled entirely by the media manager

28) What is Proxy copy backup to tape?

ü  Proxy copy is functionality, supported by few media manager in which they handle the entire data movement between datafiles and the backup devices
ü  Such products may use technologies such as high-speed connections between storage and media subsystems to reduce load on the primary database server
ü  RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data

29) What is Oracle Secure backup?

ü  Oracle Secure Backup is a media manager provided by oracle that provides reliable and secure data protection through file system backup to tape
ü  All major tape drives and tape libraries in SAN, Gigabit Ethernet, and SCSI environments are supported

30) Can I restore or duplicate my previous version database using a later version of Oracle?
For example, is it possible to restore a 9i backup while using the 10g executables?

It is possible to use the 10.2 RMAN executable to restore a 9.2 database (same for 11.2 to 11.1 or 11.1 to 10.2, etc) even if the restored datafiles will be stored in ASM
RMAN is configured so that a higher release is able to restore a lower release, but it is strongly suggested you use only the same version

31) Can I restore or duplicate between two different patchset levels?

ü  As you can restore between different Oracle versions, you can also do so between two different patchset levels
Alter database open resetlogs upgrade;
OR
alter database open resetlogs downgrade;

32) Can I restore or duplicate between two different versions of the same operating system?
For example, can I restore my 9.2.0.1.0 RMAN backup taken against a host running Solaris 9 to a different machine where 9.2.0.1.0 is installed but where that host is running Solaris 10?

 If the same Oracle Server installation CDs (media pack) can be used to install 9.2.0.1.0 on Solaris 9 and Solaris 10, this type of restore is supportable

33) Is it possible to restore or duplicate when the bit level (32 bit or 64 bit) of Oracle does not match?
For example, is it possible to restore or duplicate my 9.2. 64-bit database to a 9.2.32-bit installation?
 It is preferable to keep the same bit version when performing a restore/recovery
  However, excluding the use of duplicate command, the use of the same operating system platform should allow for a restore/recovery between bit levels (32 bit or 64 bit) of Oracle
  Note, this may be specific to the particular operating system and any problems with this should be reported to Oracle Support
  If you will be running the 64-bit database against the 32-bit binary files or vice versa, after the recovery has ended the database bit version must be converted using utlirp.sql
If you do not run utlirp.sql you will see errors including but not limited to:
ORA-06553: PLS-801: INTERNAL ERROR [56319]

34) Can I restore or duplicate my RMAN backup between two different platforms such as Solaris to Linux?
In general, you cannot restore or duplicate between two different platforms

35) What are the corruption types?

ü  Datafile Block Corruption - Physical/Logical
ü  Table/Index Inconsistency
ü  Extents Inconsistencies
ü  Data Dictionary Inconsistencies


Scenarios:
Goal: How to identify all the corrupted segments in the database reported by RMAN?

Solution:

Step 1: Identify the corrupt blocks (Datafile Block Corruption - Intra block corruption)
RMAN> backup validate check logical database;

To make it faster, it can be configured to use PARALLELISM with multiple channels:

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

Step2:  Using the view v$database_block_corruption:
SQL> select * from v$database_block_corruption;

          FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------------------------------------------------------------------------------------------------------------
              6              10                          1          8183236781662                      LOGICAL
              6              42                          1                  0                                      FRACTURED
              6              34                          2                  0                                      CHECKSUM
              6              50                          1      8183236781952                          LOGICAL
              6              26                          4                  0                                      FRACTURED

5 rows selected.


Datafile Block Corruption - Intra block corruption
It refers to intra block corruptions that may cause different errors like ORA-1578, ORA-8103, ORA-1410, ORA-600 etc.
  Oracle classifies the corruptions as Physical and Logical
ü  To identify both Physical and Logical Block Corruptions use the "CHECK LOGICAL" option
ü  It checks the complete database for both corruptions without actually doing a backup

Solution1:

$ rman target /
RMAN> backup check logical validate database;

$ rman target /
RMAN> backup check logical database;

Solution2:
ü  Chek the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN

Solution3: DBVerify - Identify Datafile Block Corruptions
ü  DBVERIFY identify Physical and Logical Intra Block Corruptions by default
ü  Dbverify cannot be run for the whole database in a single command
ü  It does not need a database connection either

dbv file= blocksize=

RMAN Vs DBVerify - Datafile Intra Block Corruption

When the logical option is used by RMAN, it does exactly the same checks as DBV does for intra block corruption.
RMAN can be run with PARALLELISM using multiple channels making it faster than DBV which can not be run in parallel in a single command
DBV checks for empty blocks. In 10g RMAN may not check blocks in free extents when Locally Managed Tablespaces are used. In 11g RMAN checks for both free and used extents.
Both DBV and RMAN (11g) can check for a range of blocks. RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;.  DBV: start=10 end=100
RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption). DBV does not.
RMAN may not report the corruption details like what is exactly corrupted in a block reported as a LOGICAL corrupted block. DBV reports the corruption details in the screen or in a log file.
DBV can scan blocks with a higher SCN than a given SCN.
DBV does not need a connection to the database.

dentify TABLE / INDEX Inconsistency
Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by "analyze validate structure cascade".
The tool to identify TABLE / INDEX inconsistencies is the ANALYZE command:
analyze table validate structure cascade;


When an inconsistency is identified, the above analyze command will produce error ORA-1499 and a trace file.



35) What Happens When A Tablespace/Database Is Kept In Begin Backup Mode?

ü  One danger in making online backups is the possibility of inconsistent data within a block
ü  For example, assume that you are backing up block 100 in datafile users.dbf
ü  Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block
ü  In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block
ü  The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN

Therefore oracle internally manages the consistency as below :
The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes
Normally only the changed bytes (a redo vector) is written
In hot backup mode, the entire block is logged the first time
This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously
Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block -- the head and tail are from two points in time.
We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.

2.  The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.


===========================================================================================================================================================================================================================================


ASM Interview questions:
*****************************

1) What are the background processes in ASM

Ans:

RABL- Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.

ARBx - Actual Rebalancer: They perform the actual rebalancing activities.
The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.

ASMB - ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources.
It is also used to update statistics and provide a heartbeat mechanism.


2) What is the use of ASM (or) Why ASM preferred over filesystem?

ANS: ASM provides striping and mirroring.


3) What are the init parameters related to ASM?

ANS:
INSTANCE_TYPE = ASM
ASM_POWER_LIMIT = 11
ASM_DISKSTRING = '/dev/rdsk/*s2', '/dev/rdsk/c1*'
ASM_DISKGROUPS = DG_DATA, DG_FRA


4) What is rebalancing (or) what is the use of ASM_POWER_LIMIT?

ANS:

ASM_POWER_LIMIT is dynamic parameter, which will be useful for rebalancing the data across disks.
Value can be 1(lowest) to 11 (highest).


5) What are different types of redundancies in ASM & explain?

ANS:

External redundancy,
Normal redundancy,
High redundancy.

6) How to copy file to/from ASM from/to filesystem?

ANS:

By using ASMCMD cp command

7) How to find out the databases, which are using the ASM instance?

ANS:

ASMCMD> lsct
DB_Name   Status     Software_Version  Compatible_version  Instance_Name  Disk_Group
amxdcmp1  CONNECTED        11.2.0.2.0          11.2.0.2.0  amxdcmp1       DG1_DCM_DATA
amxddip1  CONNECTED        11.2.0.2.0          11.2.0.2.0  amxddip1       DG1_DDI_DATA
ASMCMD>

(or)

SQL> select DB_NAME from V$ASM_CLIENT;

8) What are different types of stripings in ASM & their differences?

ANS:

Fine-grained striping
Coarse-grained striping

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   6835200  1311391                0         1311391              0             N  DG1_DCM_DATA/
MOUNTED  EXTERN  N         512   4096  1048576    486400   154487                0          154487              0             N  DG1_DDI_DATA/
ASMCMD>


SQL> select NAME,ALLOCATION_UNIT_SIZE/1024/1024 "MB" from v$asm_diskgroup;

NAME                                   MB
------------------------------ ----------
DG1_DCM_DATA                            1
DG1_DDI_DATA                            1



9) What is allocation unit and what is default value of au_size and how to change?

ANS:


Every ASM disk is divided into allocation units (AU).
An AU is the fundamental unit of allocation within a disk group.
A file extent consists of one or more AU. An ASM file consists of one or more file extents.
CREATE DISKGROUP disk_group_2 EXTERNAL REDUNDANCY DISK '/dev/sde1' ATRRIBUTE 'au_size' = '32M';

10) What process does the rebalancing?

ANS:

RBAL, ARBn

11) How to add/remove disk to/from diskgroup?

ANS:

add disk:

ALTER DISKGROUP DG1_ZABBIX_DATA ADD DISK
'/zabbix_u03/oradata/zbxprd1/ZBX_DATA_DISK009' name ZBX_DATA_DISK009,
'/zabbix_u04/oradata/zbxprd1/ZBX_DATA_DISK010' name ZBX_DATA_DISK010,
'/zabbix_u05/oradata/zbxprd1/ZBX_DATA_DISK011' name ZBX_DATA_DISK011;

remove disk:

alter diskgroup DG1_CIE_DATA drop disk
DG_CIE_DATA_DISK001,
DG_CIE_DATA_DISK002,
DG_CIE_DATA_DISK003,
DG_CIE_DATA_DISK004;


*******************************************************************************************************************************************


Oracle RMAN Interview Questions/FAQs:
**************************************

1) Difference between catalog and nocatalog?

ANS: CATALOG is used when you use a repository database as catalog.
NOCATALOG is used when you used the controlfile to register your backup information.
Default in NOCATALOG.

2) Difference between using recovery catalog and control file?

ANS:

When new incarnation happens, the old backup information in control file will be lost.
It will be preserved in recovery catalog.
In recovery catalog, we can store scripts.
Recovery catalog is central and can have information of many databases.


3) Can we use same target database as catalog?

ANS:


No.

The recovery catalog should not reside in the target database (database to be backed up),
because the database can't be recovered in the mounted state.


4) How do u know how much RMAN task has been completed?

ANS:

By querying v$rman_status or v$session_longops


5) From where list & report commands will get input

LIST:

The primary purpose of the LIST command is to list backup and copies. For example, you can list:
Backups and proxy copies of a database, tablespace, datafile, archived redo log, or control file
Backups that have expired
Backups restricted by time, path name, device type, tag, or recoverability
Archived redo log files and disk copies

REPORT:

You can use the REPORT command to answer important questions, such as:
Which files need a backup?
Which files have had unrecoverable operations performed on them?
Which backups are obsolete and can be deleted?
What was the physical schema of the target database or a database in the Data Guard environment at some previous time?
Which files have not been backed up recently?


6) Command to delete archive logs older than 7days?

ANS:

RMAN> delete archivelog all completed before sysdate-7;


7) What is the use of crosscheck command in RMAN?

ANS:

Crosscheck will be useful to check whether the catalog information is intact with OS level information.


8) What are the differences between crosscheck and validate commands

ANS:

Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.
Use the VALIDATE command to check for corrupt blocks and missing files, or to determine whether a backup set can be restored.

9) Which is one is good, differential (incremental) backup or cumulative (incremental) backup?

ANS:

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

10) What is Level 0, Level 1 backup?

ANS:

A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data,
backing the datafile up into a backup set just as a full backup would.
A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0


11)  Can we perform level 1 backup without level 0 backup?

ANS:

If no level 0 backup is available, then the behavior depends upon the compatibility mode setting.
If compatibility < 10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup.
If compatibility is >= 10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup.
In other words, the SCN at the time the incremental backup is taken is the file creation SCN.


12) Will RMAN put the database/tablespace/datafile in backup mode ?

RMAN does not require you to put the database in backup mode.

13) What is snapshot control file?

ANS:

The snapshot CONTROLFILE is a copy of the CONTROLFILE that RMAN utilizes during long running operation (such as backup).
RMAN needs a read consistent view of the CONTROLFILE for the backup operation, but by its nature the control file is extremely volatile.
Instead of putting lock on the control file and causing all kinds of db enqueue problems, RMAN makes a copy of controlfile called snapshot controlfile.
The snapshot is refreshed at the beginning of every backup.


14) what is controlfile auto backup ?

ANS:

then RMAN automatically backs up the control file and server parameter file after every backup and after database structural changes.
The control file autobackup contains metadata about the previous backup, which is crucial for disaster recovery.


15) What is the difference between backup set and backup piece?

ANS:

Backup set is logical and backup piece is physical.


16) What is obsolete backup & expired backup?

A status of "expired" means that the backup piece or backup set is not found in the backup destination.
A status of "obsolete" means the backup piece is still available, but it is no longer needed.
The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed,
or so many backups have been performed.

17)  What is the difference between hot backup & RMAN backup?

For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.


18)  How to put manual/user-managed backup in RMAN (recovery catalog)?
By using catalog command.
RMAN> CATALOG START WITH '/tmp/backup.ctl';


19)  What is the difference between auxiliary channel and maintenance channel ?


AUXILIARY:
Specifies a connection between RMAN and an auxiliary database instance.
An auxiliary instance is used when executing the DUPLICATE or TRANSPORT TABLESPACE command,
and when performing TSPITR with RECOVER TABLESPACE . When specifying this option, the auxiliary instance must be started but not mounted.
See Also: DUPLICATE to learn how to duplicate a database, and CONNECT to learn how to connect to a duplicate database instance

CHANNEL:

Specifies a connection between RMAN and the target database instance.
The channel_id is the case-sensitive name of the channel.
The database uses the channel_id to report I/O errors.
Each connection initiates an database server session on the target or auxiliary instance: this session performs the work of backing up, restoring, or recovering RMAN backups.
You cannot make a connection to a shared server session.
Whether ALLOCATE CHANNEL allocates operating system resources immediately depends on the operating system.
On some platforms, operating system resources are allocated at the time the command is issued.
On other platforms, operating system resources are not allocated until you open a file for reading or writing.
Each channel operates on one backup set or image copy at a time.
RMAN automatically releases the channel at the end of the job.




*******************************************************************************************************************************************

Oracle RAC Interview Questions/FAQs Part1 :
----------------------------------------------

1) What is the use of RAC

ANS:

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing clustering.

2) What are the prerequisites for RAC setup ?


3) What are Oracle Clusterware/Daemon processes and what they do?

Ans:
ocssd, crsd, evmd, oprocd, racgmain, racgimon

4) What are the special background processes for RAC (or) what is difference in stand-alone database & RAC database background processes?

ANS:

DIAG, LCKn, LMD, LMSn, LMON

5) What are structural changes in 11g R2 RAC?

Ans:
http://satya-racdba.blogspot.com/2010/07/new-features-in-9i-10g-11g-rac.html
Grid & ASM are on one home,
Voting disk & ocrfile can be on the ASM,
SCAN,
By using srvctl, we can mange diskgroups, home, ons, eons, filesystem, srvpool, server, scan, scan_listener, gns, vip, oc4j,GSD

6) What is cache fusion?

Ans:
Transferring of data between RAC instances by using private network.
Cache Fusion is the remote memory mapping of Oracle buffers,
shared between the caches of participating nodes in the cluster.
When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,
it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk.



7) What is the purpose of Private Interconnect?

Ans:

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP).



8) What are the Clusterware components?

Ans:

Voting Disk - Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) - Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

Virtual IP (VIP) - When a node fails, the VIP associated with it is automatically failed over to some other node
and new node re-arps the world indicating a new MAC address for the IP.
Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients.
This results in the clients getting errors immediately.
crsd – Cluster Resource Services Daemon
cssd – Cluster Synchronization Services Daemon
evmd – Event Manager Daemon
oprocd / hangcheck_timer – Node hang detector



9) What is OCR file?

Ans:
RAC configuration information repository that manages information about the cluster node list and instance-to-node mapping information.
The OCR also manages information about Oracle Clusterware resource profiles for customized applications.
Maintains cluster configuration information as well as configuration information about any cluster database within the cluster.
The OCR must reside on shared disk that is accessible by all of the nodes in your cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.


10) What is Voting file/disk and how many files should be there?

Ans:
Voting Disk File is a file on the shared cluster system or a shared raw device file.
Oracle Clusterware uses the voting disk to determine which instances are members of a cluster.
Voting disk is akin to the quorum disk, which helps to avoid the split-brain syndrome.
Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances
in case of network failures. The voting disk must reside on shared disk.

11) How to take backup of OCR file?

Ans:
#ocrconfig -manualbackup
#ocrconfig -export file_name.dmp
#ocrdump -backupfile my_file
$cp -p -R /u01/app/crs/cdata /u02/crs_backup/ocrbackup/RAC1


12) How to recover OCR file?

Ans:
#ocrconfig -restore backup_file.ocr
#ocrconfig -import file_name.dmp


13) What is local OCR?

Ans:
/etc/oracle/local.ocr
/var/opt/oracle/local.ocr


14) How to check backup of OCR files?

Ans:
#ocrconfig –showbackup


15) How to take backup of voting file?

Ans:
dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
crsctl backup css votedisk         -- from 11g R2

16)  How do I identify the voting disk location?

Ans:
# crsctl query css votedisk

17) How do I identify the OCR file location?

check /var/opt/oracle/ocr.loc or /etc/ocr.loc

Ans:
# ocrcheck


18) If voting disk/OCR file got corrupted and don’t have backups, how to get them?

Ans:
We have to install Clusterware.


19) Who will manage OCR files?

Ans:
cssd will manage OCR.



20)  Who will take backup of OCR files?

Ans:
crsd will take backup.


21) What is split brain syndrome?

Ans:
Will arise when two or more instances attempt to control a cluster database.
In a two-node environment, one instance attempts to manage updates simultaneously while the other instance attempts to manage updates.


22) What are various IPs used in RAC? Or How may IPs we need in RAC?

Ans:
Public IP, Private IP, Virtual IP, SCAN IP


23) What is the use of virtual IP?

Ans:
When a node fails,
the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP.
Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients.
This results in the clients getting errors immediately.

Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error.
As a result, you don't really have a good HA solution without using VIPs.

24) What is the use of SCAN IP (SCAN name) and will it provide load balancing?

Ans:
Single Client Access Name (SCAN) is a new Oracle Real Application Clusters (RAC) 11g Release 2,
feature that provides a single name for clients to access an Oracle Database running in a cluster.
The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

25)  How many SCAN listeners will be running?

Ans:
Three SCAN listeners only.

26) What is FAN?

Ans:
Applications can use Fast Application Notification (FAN) to enable rapid failure detection, balancing of connection pools after failures,
and re-balancing of connection pools when failed components are repaired.
The FAN process uses system events that Oracle publishes when cluster servers become unreachable or if network interfaces fail.


27) What is FCF?

Ans:
Fast Connection Failover provides high availability to FAN integrated clients, such as clients that use JDBC, OCI, or ODP.NET.
If you configure the client to use fast connection failover, then the client automatically subscribes to FAN events and can react to database UP and DOWN events.
In response, Oracle gives the client a connection to an active instance that provides the requested database service.


30) What is TAF and TAF policies?

Ans:
Transparent Application Failover (TAF) - A runtime failover for high availability environments,
such as Real Application Clusters and Oracle Real Application Clusters Guard, TAF refers to the failover and re-establishment of application-to-service connections.
It enables client applications to automatically reconnect to the database if the connection fails, and optionally resume a SELECT statement that was in progress.
This reconnect happens automatically from within the Oracle Call Interface (OCI) library.

31) What are nodeapps?

Ans:
VIP, listener, ONS, GSD


32) What is gsd (Global Service Daemon)?   [ http://www.datadisk.co.uk/html_docs/rac/rac_cs.htm ]

runs on each node with one GSD process per node.
The GSD coordinates with the cluster manager to receive requests from clients such as the DBCA, EM, and the SRVCTL utility to execute administrative job tasks such as instance startup or shutdown.
The GSD is not an Oracle instance background process and is therefore not started with the Oracle instance

33) How to do load balancing in RAC?


Client Side Connect-Time Load Balance:
---------------------------------------
The client load balancing feature enables clients to randomize connection requests among the listeners.
This is done by client Tnsnames Parameter: LOAD_BALANCE.
The (load_balance=yes) instructs SQLNet to progress through the list of listener addresses in the address_list section of the net service name in a random sequence. When set to OFF, instructs SQLNet to try the addresses sequentially until one succeeds.

Client Side Connect-Time failover
-------------------------------------
This is done by client Tnsnames Parameter: FAILOVER
The (failover=on) enables clients to connect to another listener if the initial connection to the first listener fails. Without connect-time failover, Oracle Net attempts a connection with only one listener.

Server Side Listener Connection Load Balancing.
-------------------------------------------------
With server-side load balancing, the listener directs a connection request to the best instance currently providing the service.
Init parameter remote_listener should be set. When set, each instance registers with the TNS listeners running on all nodes within the cluster.

There are two types of server-side load balancing:
--------------------------------------------------
Load Based — Server side load balancing redirects connections by default depending on node load. This id default.
Session Based — Session based load balancing takes into account the number of sessions connected to each node and then distributes the connections to balance the number of sessions across the different nodes.

From 10g release 2 the service can be setup to use load balancing advisory. This mean connections can be routed using SERVICE TIME and THROUGHPUT. Connection load balancing means the goal of a service can be changed, to reflect the type of connections using the service.

Transparent Application Failover (TAF) :
----------------------------------------------
Transparent Application Failover (TAF) is a feature of the Oracle Call Interface (OCI) driver at client side. It enables the application to automatically reconnect to a database, if the database instance to which the connection is made fails. In this case, the active transactions roll back.
Tnsnames Parameter: FAILOVER_MODE

e.g (failover_mode=(type=select)(method=basic))
Failover Mode Type can be Either SESSION or SELECT.

Session failover will have just the session to failed over to the next available node. With SELECT, the select query will be resumed.
TAF can be configured with just server side service settings by using dbms_service package.

Fast Connection Failover (FCF):
-----------------------------------
Fast Connection Failover is a feature of Oracle clients that have integrated with FAN HA Events.
Oracle JDBC Implicit Connection Cache, Oracle Call Interface (OCI), and Oracle Data Provider for .Net (ODP.Net) include fast connection failover.

With fast connection failover, when a down event is received, cached connections affected by the down event are immediately marked invalid and cleaned up.



34) What are the uses of services? How to find out the services in cluster?

Ans:
Applications should use the services to connect to the Oracle database.
Services define rules and characteristics (unique name, workload balancing, failover options, and high availability) to control how users and applications connect to database instances.

35) How to find out the nodes in cluster (or) how to find out the master node?

Ans:

# olsnodes  -- Which ever displayed first, is the master node of the cluster.

select MASTER_NODE from v$ges_resource;

To find out which is the master node, you can see ocssd.log file and search for "master node number".


36) How to know the public IPs, private IPs, VIPs in RAC?

Ans:
# olsnodes -n -p -i
node1-pub       1       node1-prv       node1-vip
node2-pub       2       node2-prv       node2-vip


37) What utility is used to start DB/instance?

Ans:
srvctl start database –d database_name
srvctl start instance –d database_name –i instance_name


38) How can you shutdown single instance?

Ans:
Change cluster_database=false

srvctl stop instance –d database_name –i instance_name

39) What is HAS (High Availability Service) and the commands?

Ans:
HAS includes ASM & database instance and listeners.

crsctl check has
crsctl config has
crsctl disable has
crsctl enable has
crsctl query has releaseversion
crsctl query has softwareversion
crsctl start has
crsctl stop has [-f]


40) How many nodes are supported in a RAC Database?

Ans:
10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.



41) What is fencing?

Ans:
I/O fencing prevents updates by failed instances, and detecting failure and preventing split brain in cluster.
When a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or diskgroups.
This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.


42) Why Clusterware installed in root (why not oracle)?

Oracle Clusterware works closely with the operating system, system administrator access is required for some of the installation tasks.
In addition, some of the Oracle Clusterware processes must run as the special operating system user, root.


43) What are the wait events in RAC?

Ans:
http://satya-racdba.blogspot.com/2012/10/wait-events-in-oracle-rac-wait-events.html

http://orainternals.wordpress.com/2009/12/23/rac-performance-tuning-understanding-global-cache-performance/

gc buffer busy
gc buffer busy acquire
gc current request
gc cr request
gc cr failure
gc current block lost
gc cr block lost
gc current block corrupt
gc cr block corrupt
gc current block busy
gc cr block busy
gc current block congested
gc cr block congested.
gc current block 2-way
gc cr block 2-way
gc current block 3-way
gc cr block 3-way
(gc current/cr block n-way, n is number of nodes)
gc current grant 2-way
gc cr grant 2-way
gc current grant busy
gc current grant congested
gc cr grant congested
gc cr multi block read
gc current multi block request
gc cr multi block request
gc cr block build time
gc current block flush time
gc cr block flush time
gc current block send time
gc cr block send time
gc current block pin time
gc domain validation
gc current retry
ges inquiry response
gcs log flush sync


44) What are the initialization parameters that must have same value for every instance in an Oracle RAC database?
Ans:
http://satya-racdba.blogspot.com/2012/09/init-parameters-in-oracle-rac.html

ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT


45) What is the difference between cr block and cur (current) block?



46) New features in Oracle Clusterware 12c ?


Oracle Flex ASM - This feature of Oracle Clusterware 12c claims to reduce per-node overhead of using ASM instance.
Now the instances can use remote node ASM for any planned/unplanned downtime. ASM metadata requests can be converted by non-local instance of ASM.

ASM Disk Scrubbing - From RAC 12c, ASM comes with disk scrubbing feature so that logical corruptions can be discovered.
Also Oracle 12c ASM can automatically correct this in normal or high redundancy diskgroups.

Oracle ASM Disk Resync & Rebalance enhancements.
Commands Databases Supporting To the application Gameing Game What is raid

Application Continuity (AC) - is transparent to the application and in-case the database or the infrastructure is unavailable, this new features which work on JDBC drivers, masks recoverable outages.
This recovers database session beneath the application so that the outage actually appears to be delayed connectivity or execution.
Transaction guard (improvements of Fast Application Notification).

IPv6 Support - Oracle RAC 12c now supports IPv6 for Client connectivity, Interconnect is still on IPv4.

Per Subnet multiple SCAN - RAC 12c, per-Subnet multiple SCAN can be configured per cluster.

Each RAC instance opens the Container Database (CDB) as a whole so that versions would be same for CDB as well as for all of the Pluggable Databases (PDBs). PDBs are also fully compatible with RAC.

Oracle installer will run root.sh script across nodes. We don't have to run the scripts manually on all RAC nodes.

new "ghctl" command for patching.



47) New features in Oracle 9i/10g/11g RAC ?  [ http://satya-racdba.blogspot.in/2010/07/new-features-in-9i-10g-11g-rac.html ]


Oracle Real Application Clusters New features

Oracle 9i RAC:
---------------------
OPS (Oracle Parallel Server) was renamed as RAC
CFS (Cluster File System) was supported
OCFS (Oracle Cluster File System) for Linux and Windows
watchdog timer replaced by hangcheck timer

Oracle 10g R1 RAC :
-------------------
Cluster Manager replaced by CRS
ASM introduced
Concept of Services expanded
ocrcheck introduced
ocrdump introduced
AWR was instance specific

Oracle 10g R2 RAC :
-------------------
CRS was renamed as Clusterware
asmcmd introduced
CLUVFY introduced
OCR and Voting disks can be mirrored
Can use FAN/FCF with TAF for OCI and ODP.NET
The Waiting The Wait Latest News Resource Manager Installing Music Downloads


Oracle 11g R1 RAC :
---------------------
--> Oracle 11g RAC parallel upgrades - Oracle 11g have rolling upgrade features whereby RAC database can be upgraded without any downtime.
-->Hot patching - Zero downtime patch application.
-->Oracle RAC load balancing advisor - Starting from 10g R2 we have RAC load balancing advisor utility.
11g RAC load balancing advisor is only available with clients who use .NET, ODBC, or the Oracle Call Interface (OCI).
-->ADDM for RAC - Oracle has incorporated RAC into the automatic database diagnostic monitor, for cross-node advisories.
The script addmrpt.sql run give report for single instance, will not report all instances in RAC, this is known as instance ADDM.
But using the new package DBMS_ADDM, we can generate report for all instances of RAC, this known as database ADDM.
--> Optimized RAC cache fusion protocols - moves on from the general cache fusion protocols in 10g to deal with specific scenarios where the protocols could be further optimized.
--> Oracle 11g RAC Grid provisioning - The Oracle grid control provisioning pack allows us to "blow-out" a RAC node without the time-consuming install, using a pre-installed "footprint".

Oracle 11g R2 RAC :
-----------------------
--> We can store everything on the ASM. We can store OCR & voting files also on the ASM.
--> ASMCA
--> Single Client Access Name (SCAN) - eliminates the need to change tns entry when nodes are added to or removed from the Cluster.
RAC instances register to SCAN listeners as remote listeners. SCAN is fully qualified name.
Oracle recommends assigning 3 addresses to SCAN, which create three SCAN listeners.
--> Clusterware components: crfmond, crflogd, GIPCD.
--> AWR is consolidated for the database.
--> 11g Release 2 Real Application Cluster (RAC) has server pooling technologies so it’s easier to provision and manage database grids.
This update is geared toward dynamically adjusting servers as corporations manage the ebb and flow between data requirements for datawarehousing and applications.By default, LOAD_BALANCE is ON.
--> GSD (Global Service Deamon), gsdctl introduced.
--> GPnP profile.
--> Cluster information in an XML profile.
--> Oracle RAC OneNode is a new option that makes it easier to consolidate databases that aren’t mission critical, but need redundancy.
--> raconeinit - to convert database to RacOneNode.
--> raconefix - to fix RacOneNode database in case of failure.
--> racone2rac - to convert RacOneNode back to RAC.
--> Oracle Restart - the feature of Oracle Grid Infrastructure's High Availability Services (HAS) to manage associated listeners, ASM instances and Oracle instances.
--> Oracle Omotion - Oracle 11g release2 RAC introduces new feature called Oracle Omotion, an online migration utility.
This Omotion utility will relocate the instance from one node to another, whenever instance failure happens.
Omotion utility uses Database Area Network (DAN) to move Oracle instances.
Database Area Network (DAN) technology helps seamless database relocation without losing transactions.
--> Cluster Time Synchronization Service (CTSS) is a new feature in Oracle 11g R2 RAC, which is used to synchronize time across the nodes of the cluster. --> CTSS will be replacement of NTP protocol.
--> Grid Naming Service (GNS) is a new service introduced in Oracle RAC 11g R2. With GNS, Oracle Clusterware (CRS) can manage Dynamic Host Configuration Protocol --> (DHCP) and DNS services for the dynamic node registration and configuration.
--> Cluster interconnect: Used for data blocks, locks, messages, and SCN numbers.
--> Oracle Local Registry (OLR) - From Oracle 11gR2 "Oracle Local Registry (OLR)" something new as part of Oracle Clusterware. OLR is node’s local repository, --> similar to OCR (but local) and is managed by OHASD. It pertains data of local node only and is not shared among other nodes.
--> Multicasting is introduced in 11gR2 for private interconnect traffic.
--> I/O fencing prevents updates by failed instances, and detecting failure and preventing split brain in cluster. When a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or diskgroups. This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.
--> Re-bootless node fencing (restart)? - instead of fast re-booting the node, a graceful shutdown of the stack is attempted.
--> Clusterware log directories: acfs*
--> HAIP (IC VIP).
--> Redundant interconnects: NIC bonding, HAIP.
--> RAC background processes: DBRM – Database Resource Manager, PING – Response time agent.
--> Virtual Oracle 11g RAC cluster - Oracle 11g RAC supports virtualization.





*************************************************************************************************************************************************************

Oracle GoldenGate Interview Questions/FAQs :
**********************************************

1) What are processes/components in GoldenGate?

Ans:

Manager, Extract, Replicat, Data Pump

2) What is Data Pump process in GoldenGate ?

he Data Pump (not to be confused with the Oracle Export Import Data Pump) is an optional secondary Extract group that is created on the source system. When Data Pump is not used, the Extract process writes to a remote trail that is located on the target system using TCP/IP. When Data Pump is configured, the Extract process writes to a local trail and from here Data Pump will read the trail and write the data over the network to the remote trail located on the target system.

The advantages of this can be seen as it protects against a network failure as in the absence of a storage device on the local system, the Extract process writes data into memory before the same is sent over the network. Any failures in the network could then cause the Extract process to abort (abend). Also if we are doing any complex data transformation or filtering, the same can be performed by the Data Pump. It will also be useful when we are consolidating data from several sources into one central target where data pump on each individual source system can write to one common trail file on the target.


3) What is the command line utility in GoldenGate (or) what is ggsci?


ANS: Golden Gate Command Line Interface essential commands – GGSCI

GGSCI   -- (Oracle) GoldenGate Software Command Interpreter


4) What is the default port for GoldenGate Manager process?

ANS:

7809

5) What are important files GoldenGate?

GLOBALS, ggserr.log, dirprm, etc ...


6) What is checkpoint table?

ANS:

Create the GoldenGate Checkpoint table

GoldenGate maintains its own Checkpoints which is a known position in the trail file from where the Replicat process will start processing after any kind of error or shutdown.
This ensures data integrity and a record of these checkpoints is either maintained in files stored on disk or table in the database which is the preferred option.


7) How can you see GoldenGate errors?

ANS:

ggsci> VIEW GGSEVT
ggserr.log file


*************************************************************************************************************************************************************

Oracle Data Guard Interview Questions/FAQs :
************************************************


1)  How to setup Data Guard?

2) What are different types of modes in Data Guard and which is default?

ANS:

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.

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.


3) How many standby databases we can create (in 10g/11g)?

ANS:

Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases..

4) What are the parameters we’ve to set in primary/standby for Data Guard ?

ANS:

DB_UNIQUE_NAME
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_MAX_PROCESSES
DB_CREATE_FILE_DEST
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n
LOGARCHIVE_DEST_STATE_n
FAL_SERVER
FAL_CLIENT
STANDBY_FILE_MANAGEMENT


5) What is the use of fal_server & fal_client, is it mandatory to set these ?


ANS:

FAL_SERVER
specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.


FAL_CLIENT
specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the
FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).


6) What are differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?

Physical standby – in mount state, MRP will apply archives
ADG – in READ ONLY state, MRP will apply archives
Logical standby – in READ ONLY state, LSP will run
Snapshot standby databases – Physical standby database can be converted to snapshot standby database, which will be in READ WRITE mode, can do any kind of testing, then we can convert back snapshot standby database to physical standby database and start MRP which will apply all pending archives.

7) How to find out backlog of standby?

select round((sysdate - a.NEXT_TIME)*24*60) as "Backlog",m.SEQUENCE#-1 "Seq Applied",m.process, m.status
from v$archived_log a, (select process,SEQUENCE#, status from v$managed_standby where process like '%MRP%')m where a.SEQUENCE#=(m.SEQUENCE#-1);

8) 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?

ANS:

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


9) How can u recover standby which far behind from primary (or) without archive logs how can we make standby sync?

ANS:

By using RMAN incremental backup.


10) What is snapshot standby (or) How can we give a physical standby to user in READ WRITE mode and let him do updates and revert back to standby?

ANS:

Till Oralce 10g, create guaranteed restore point, open in read write, let him do updates, flashback to restore point, start MRP.

From Oracle 11g, convert physical standby to snapshot standby, let him do updates, convert to physical standby, start MRP.


11)  What are new features in 11g Data Guard?

ANS:

Here is some data guard category and there enhancement

1) Data Protection
Advanced Compression
Lost-write protection
Fast-Start Failover
2) Increase ROI
Active Data Guard
Snapshot Standby
3) High Availability
Faster Redo Apply
Faster failover & switchover
Automatic Failover using ASYNC
4) Manageability
Mixed Windows/Linux

12) What are the uses of standby redo log files


A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived.
This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

13) What is dg_config ?

ANS:

Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.
The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data.

14) What is RTA (real time apply) mode MRP?

ANS:

real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file.
This standby logfile is written to standby log file on standby server. There is no loss of any committed transaction whatsoever in Real-Time Apply scenario.

•In Real Time Apply, once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log at the Primary

MRP - Managed recovery process - For Data Guard, the background process that applies archived redo log to the standby database.

15)  What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?

ANS:


The difference between Redo Apply & Real-Time Apply
------------------------------------------------------
Normally, by default, Archiver processes will be responsible for Redo Transport from Primary to Standby.

Once a log switch happens on the Primary, the online redo log is archived in the Local Archive destination as pointed to by Log_archive_dest_1
by an Archiver process.
Another Archiver process will then transmit the redo to the remote standby destination as indicated by Log_archive_dest_2.
Data Guard Remote File Server (RFS) Process on the Standby then writes redo data from the Standby redo log file to archive redo log file.
Log apply services then makes use of Managed Recovery Process (MRP) process to apply the redo to the standby database.
This method of propagating redo from the primary to standby is called Redo Apply and it happens only on log switch at the Primary.
When using Redo Apply mode, the status of MRP in v$managed_standby view will show as WAIT_FOR_LOG.

Real Time Apply, in contrast, uses either LGWR or Archiver on the Primary to write redo data to Standby Redo log on the Standby and Log Apply Services can apply the redo data in real-time without the need of the current standby redo log being archived. Once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log.

When using Real Time Apply mode, the status of MRP in v$managed_standby view will show as APPLYING_LOG.


16) What is the difference between SYNC/ASYNC, LGWR/ARCH, and AFFIRM/NOAFFIRM ?

ANS:

Specifies that network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).

Specifies whether redo transport services use archiver processes (ARCn) or the log writer process (LGWR) to collect transaction redo data and transmit it to standby destinations. If neither the ARCH or LGWR attributes are specified, the default is ARCH.

Controls whether redo transport services use synchronous or asynchronous I/O to write redo data to disk

AFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and completes successfully before the log writer process continues.

NOAFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed asynchronously; the log writer process on the primary database does not wait until the disk I/O completes before continuing.


17) What is StaticConnectIdentifier property used for?

ANS:

11gr2 new database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.

18) What is failover/switchover (or) what is the difference between failover & switchover

ANS:

Switchover – This is done when both primary and standby databases are available. It is pre-planned.
Failover – This is done when the primary database is NO longer available (ie in a Disaster). It is not pre-planned.


29) What are the background processes involved in Data Guard?

ANS:

MRP, LSP,


21)


*************************************************************************************************************************************************************

Oracle Export/Import (exp/imp)- Data Pump (expdp/imp) Interview Questions/FAQs :

*******************************************************************************************


1) What is use of CONSISTENT option in exp?

Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2) What is use of DIRECT=Y option in exp?

Setting direct=yes, to extract data by reading the data directly, bypasses the SGA,
bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.


3) What is use of COMPRESS option in exp?

Imports into one extent. Specifies how export will manage the initial extent for the table data.
This parameter is helpful during database re-organization.
Export the objects (especially tables and indexes) with COMPRESS=Y.
If table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced.
Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on disk (tablespace), and do not want imports to fail.


4) How to improve exp performance?

ANS:

a). Set the BUFFER parameter to a high value. Default is 256KB.
b). Stop unnecessary applications to free the resources.
c). If you are running multiple sessions, make sure they write to different disks.
d). Do not export to NFS (Network File Share). Exporting to disk is faster.
e). Set the RECORDLENGTH parameter to a high value.
f). Use DIRECT=yes (direct mode export).


5) How to improve imp performance?

ANS:

a). Place the file to be imported in separate disk from datafiles.
b). Increase the DB_CACHE_SIZE.
c). Set LOG_BUFFER to big size.
d). Stop redolog archiving, if possible.
e). Use COMMIT=n, if possible.
f). Set the BUFFER parameter to a high value. Default is 256KB.
g). It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import.
Indexes can easily be recreated after the data was successfully imported.
h). Use STATISTICS=NONE
i). Disable the INSERT triggers, as they fire during import.
j). Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.


6) What is use of INDEXFILE option in imp?

ANS:

Will write DDLs of the objects in the dumpfile into the specified file.

7) What is use of IGNORE option in imp?

ANS:

Will ignore the errors during import and will continue the import.


8) What are the differences between expdp and exp (Data Pump or normal exp/imp)?

ANS:

Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.


9) Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.


10)  How to improve expdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.


11) How to improve impdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.


12) In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.

Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.


13) What is the order of importing objects in impdp?

 Tablespaces
 Users
 Roles
 Database links
 Sequences
 Directories
 Synonyms
 Types
 Tables/Partitions
 Views
 Comments
 Packages/Procedures/Functions
 Materialized views



14) How to import only metadata?

ANS:

CONTENT= METADATA_ONLY


15) How to import into different user/tablespace/datafile/table?

ANS:

REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

16) Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?

ANS:

Import data pump can always read export datapump dumpfile sets created by older versions of database. In your case it works, normal expdp on 10g and impdp on 11g
VERSION parameter in datapump is for other way around, if you want to import data taken from 11g into 10g database you need
to specify VERSION while taking backup.

17) How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?

ANS: [http://satya-dba.blogspot.in/2010/01/oracle-transportable-tablespaces-tts.html ]

We can use the transportable tablespaces feature to copy/move subset of data (set of user tablespaces), from an Oracle database and plug it in to another Oracle database. The tablespaces being transported can be either dictionary managed or locally managed.

With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.

With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.

With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).

 select * from v$transportable_platform order by platform_id;



18)
How to determine the Schemas inside an Oracle Data Pump Export file ?

 strings dumpfile.dmp | grep SCHEMA_LIST
(or)
$ strings myfile.dmp|more

*************************************************************************************************************************************************************

Oracle Performance Related Interview Questions/FAQs :
**********************************************************

1) What you’ll check whenever user complains that his session/database is slow?


2) What is the use of statistics?

ANS:

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.


3) How to generate explain plan?

ANS:

EXPLAIN PLAN FOR ;


4) How to check explain plan of already ran SQLs?

ANS:

select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));


5) How to find out whether the query has ran with RBO or CBO?


ANS:

ts very simple..from sql alone you cannot tell wheather they used CBO or RBO..its like this

If your optimizer_mode=choose
then all sql statements will use the CBO
when the tables they are acessing will have statistics collected..
then all sql statements will use the RBO
when the tables they are acessing will have no statistics..


6) What are top 5 wait events (in AWR report) and how you will resolve them?

ANS:

http://satya-dba.blogspot.in/2012/10/wait-events-in-oracle-wait-events.html

db file sequential read  => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. Caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.
db file parallel read  => tune SQL, tune indexing, tune disk I/O, increase buffer cache. If you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file parallel write  => if you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file single write  => if you see this event than probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync    => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write  => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space   => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed)    => Indicates archive files are written too slowly.
redo buffer allocation retries  => shows the number of times a user process waited for space in the redo log buffer.
redo log space wait time  => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session  => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits  => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain  => Freelist issues, hot blocks.
no free buffers   => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch  => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains  => check hot objects.
latch: cache buffer handles  => Freelist issues, hot blocks.
direct path write => You wont see them unless you are doing some appends or data loads.
direct Path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.
library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock  => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.
undo segment extension  => If excessive, tune undo.
wait for a undo record   => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events   => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client



7) What are the init parameters related to performance/optimizer?

ANS:

optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;



8) What are the values of optimizer_mode init parameters and their meaning?

ANS:

optimizer_mode = choose


9) What is the use of AWR, ADDM, ASH?

10)  How to generate AWR report and what are the things you will check in the report?

11). How to generate ADDM report and what are the things you will check in the report?

12). How to generate ASH report and what are the things you will check in the report?

13)  How to generate TKPROF report and what are the things you will check in the report?

ANS:


The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.


14)



*************************************************************************************************************************************************************

UNIX Interview Questions/FAQs for Oracle DBAs:
************************************************

1) What’s the difference between soft link and hard link?

Ans:
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system, because they share same inode number and an inode table is unique to a file system, both must be on the same file system.


2) How you will read a file from shell script?
Ans:
while read line
do
 echo $line
done < file_name


3) 3. What’s the use of umask?
ANS:
Will decide the default permissions for files.

4) What is crontab and what are the arguments?
Ans:
The entries have the following elements:
field             allowed values
-----             --------------
minute            0-59
hour                0-23
day of month   1-31
month             1-12
day of week     0-7 (both 0 and 7 are Sunday)
user                 Valid OS user
command         Valid command or script

? ? ? ? ? command
|  | |  | |_________day of the week (0-6, 0=Sunday)
|  | |  |___________month (1-12)
|  | |_____________day of the month (1-31)
|  |_______________hour (0-23)
|_________________minute (0-59)


5) How to find operating system (OS) version?
Ans:
uname –a

6)  How to find out the run level of the user?
Ans:
uname –r


7) How to delete 7 days old trace files?
Ans:
find ./trace –name *.trc –mtime +7 –exec rm {} \;

8) What is top command?
Ans:
top is a operating system command, it will display top processes which are taking high cpu and memory.


9) 8. How to get 10th line of a file (by using grep)?


10)

*************************************************************************************************************************************************************

Architecture:

Oracle DBA Interview Questions/FAQs Part1 :


1) What is an instance?
ANS:

SGA + background processes.

2) What is SGA?
ANS:
System/Shared Global Area.

3) What is PGA (or) what is pga_aggregate_target?
ANS:
Programmable Global Area.

4) What are new memory parameters in Oracle 10g?
ANS:
SGA_TARGET, PGA_TARGET

5)  What are new memory parameters in Oracle 11g?
ANS:
MEMORY_TARGET

6) What are the mandatory background processes?
ANS:
DBWR LGWR SMON PMON CKPT RECO.

7)  What are the optional background processes?
ANS:

ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.

8) What are the new background processes in Oracle 10g?
ANS:
MMAN MMON MMNL CTWR ASMB RBAL ARBx

9) What are the new features in Oracle 9i?
http://satya-dba.blogspot.com/2009/01/whats-new-in-9i.html

10) . What are the new features in Oracle 10g?
http://satya-dba.blogspot.com/2009/01/whats-new-in-10g.html

11). What are the new features in Oracle 11g?
http://satya-dba.blogspot.com/2009/01/whats-new-in-11g.html

12). What are the new features in Oracle 11g R2?
http://satya-dba.blogspot.com/2009/09/whats-new-in-11g-release-2.html

13) What process will get data from datafiles to DB cache?

ANS:
Server process

14) What background process will writes data to datafiles?

ANS:
DBWR

15) What background process will write undo data?

ANS:
DBWR

16) What are physical components of Oracle database?

ANS:

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.
Password file and parameter file also come under physical components.


17) What are logical components of Oracle database?
ANS:
Blocks, Extents, Segments, Tablespaces.

18) What is segment space management?
ANS:
LMTS and DMTS.

19)  What is extent management?

ANS:

Auto and Manual.

20) What are the differences between LMTS and DMTS?

Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces,
and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

*************************************************************************************************************************************************************

Oracle DBA Interview Questions/FAQs Part2 :
********************************************

1) What is a datafile?
ANS:
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

2) What are the contents of control file?

ANS:
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

3) What is the use of redo log files?

ANS:

Online redo logs serve to protect the database in the event of an instance failure. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR.

4) What are the uses of undo tablespace or redo segments?

ANS:
Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features

5) How undo tablespace can guarantee retain of required undo data?

ANS:

Alter tablespace undo_ts retention guarantee;


6) What is 01555 - snapshot too old error and how do you avoid it?

ANS:

http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

7) What is the use/size of temporary tablespace?

ANS:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables

8) What is the use of password file?

ANS:

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

9)  How to create password file?

ANS:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

10) How many types of indexes are there?
ANS:

Clustered and Non-Clustered

1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index

Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.


11)  What is bitmap index & when it’ll be used?

ANS:
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.


12) What is B-tree index & when it’ll be used?

ANS:

B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.


13) How you will find out fragmentation of index?

ANS:

AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.

analyze index validate structure;

This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.


14) What is the difference between delete and truncate?]

ANS:

Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.

15)  What's the difference between a primary key and a unique key?
ANS:

Both primary key and unique enforce uniqueness of the column on which they are defined.
But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default.
Primary key doesn't allow NULLs, but unique key allows one NULL only.

16) What is the difference between schema and user?

Schema is collection of user’s objects.


17)  What is the difference between SYSDBA, SYSOPER and SYSASM?
ANS:

SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do.

18) What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

19) What is the difference between view and materialized view?

View is logical, will store only the query, and will always gets latest data.
Mview is physical, will store the data, and may not get latest data.


20)

************************************************************************************************************************************************************

Oracle DBA Interview Questions/FAQs Part3 :
*********************************************

1) What are materialized view refresh types and which is default?
ANS:
Complete, fast, force(default)

2) How to find out when was a materialized view refreshed?

ANS:

Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;


3) What is atomic refresh in mviews?
ANS:
From Oracle 10g, complete refresh of single materialized view can do delete instead of truncate.
To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.

ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=FALSE);

4) How to find out whether database/tablespace/datafile is in backup mode or not?
ANS:
Query V$BACKUP view.

5) What is row chaining?
ANS:
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.

6) What is row migration?
ANS:
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks.
Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves entire row to new block.


7) What are different types of partitions?
ANS:
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.

8)  What is local partitioned index and global partitioned index?
ANS:
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

9) How you will recover if you lost one/all control file(s)?

10) Why more archivelogs are generated, when database is begin backup mode?

ANS:

During begin backup mode datafile headers get freezed and as result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch and in turn more archive logs.
Normally only deltas (change vectors) are logged to the redo logs.
When in backup mode, Oracle will write complete changed blocks to the redo log files.

Mainly to overcome fractured blocks. Most of the cases Oracle block size is equal to or a multiple of the operating system block size.

e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.

11) What UNIX parameters you will set while Oracle installation?
ANS:
shmmax, shmmni, shmall, sem,

12) What is the use of inittrans and maxtrans in table definition?

13) What are differences between dbms_job and dbms_schedular?

Through dbms_schedular we can schedule OS level jobs also.

14) What are differences between dbms_schedular and cron jobs?

Through dbms_schedular we can schedule database jobs, through cron we can’t set.

15) Difference between CPU & PSU patches?

CPU - Critical Patch Update - includes only Security related patches.
PSU - Patch Set Update - includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.

16)  What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?

17) What are the entries/location of oraInst.loc?
ANS:

/etc/oraInst.loc is pointer to central/local Oracle Inventory.


18) What is the difference between central/global inventory and local inventory?

ANS:


19)

*************************************************************************************************************************************************************

Oracle DBA Interview Questions/FAQs Part4 :
**********************************************

1) What is the use of root.sh & oraInstRoot.sh?
Ans:
Changes ownership & permissions of oraInventory
Creating oratab file in the /etc directory
In RAC, starts the clusterware stack


2) How can you transport tablespaces across platforms with different endian formats?
Ans:
RMAN

3) What is transportable tablespace (and across platforms)?

4)  What is xtss (cross platform transportable tablespace)?

5)  What is the difference between restore point & guaranteed restore point?

6) How to find if your Oracle database is 32 bit or 64 bit?
Ans:
execute the command "file $ORACLE_HOME/bin/oracle", you should see output like /u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1

means you are on 64 bit oracle.

If your oracle is 32 bit you should see output like below
oracle: ELF 32-bit MSB executable SPARC Version 1


7) How to find opatch Version ?
Ans:
opatch is utility to apply database patch, In order to find opatch version execute"$ORACLE_HOME/OPatch/opatch version"


8) suppose i created one table after few days i did some insert,update how can i know when will i did ddl or dml operation is undergone on that table ?

ANS:

DDL:

select OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME,from dba_objects where OBJECT_NAME='&object_name';


DML:

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from PS_PAY_TAX;

MAX(ORA_ROWSCN) SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
--------------- ---------------------------------------------------------------------------
     6016929147 04-JAN-12 08.41.20.000000000 AM

SQL>

SQL> select table_name, inserts, updates, deletes, timestamp,truncated  from user_tab_modifications where table_name='TEST1';

TABLE_NAME  INSERTS    UPDATE  DELETES   TIMESTAMP         TRU DROP_SEG

---------   --------  ------- -------- ------------------- --- --------

TEST1        4         0        0     04.08.2008 12:03:32  NO   0


=====================================================================

THE END
====================================================================









This is what happens when we issued "backup validate ;"
-------------------------------------------------------------------------------------------------
RMAN>backup validate X;
Where X is database|tablespace|datafile spec. When using the VALIDATE option:

the WHOLE datafile is fully scanned
no physical backuppiece is written
VALIDATE runtime represents time spent scanning the input files
the difference in runtime between this and the normal backup represents time spent writing to the output device
-------------------------------------------------------------------------------------------------


JUL
22
2008
04:22 AM
6268
Views

8
Ans

RMAN without Catalog
shanthiavari RMAN Interview Questions
Where RMAN keeps information of backups if you are using RMAN without Catalog?
Nisha Srivastava
 Nov 10th, 2014
By Default RMAN Stores all the Backup information to
Target Database Control file.

pankaj kumar
 Sep 3rd, 2014
In nocatalog, mode all information stored in reusable section of control file

Answer Question  Select Best Answer
JUN
09
2008
09:35 PM
5321
Views

4
Ans

Tape Backup Solutions
shanthiavari RMAN Interview Questions
What are the various tape backup solutions available in the market?
Sumit
 Apr 14th, 2014
We are using EMC for Storage

jayaprakash
 Aug 31st, 2013
I am using HP proliant.

Answer Question  Select Best Answer
JUN
10
2008
01:02 AM
5936
Views

3
Ans

DBID and Incarnation
shanthiavari RMAN Interview Questions
What is the significance of incarnation and DBID in the RMAN backups?
Priyan
 Mar 26th, 2013
Incaranation help to go backup to old position of the database.

nareshkumarmca
 Jan 23rd, 2010
DBID means database id.Incunation means:  Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter ...

Answer Question  Select Best Answer
JUN
10
2008
12:49 AM
3402
Views

1
Ans


SCN based hot backup recovery
Outline the steps involved in SCN based recovery from the full database from hot backup?
 shanthiavari    Jun 10th, 2008 1 3402
Questions by shanthiavari

RMAN Interview QuestionsAnswerFirstPrevNextLast
Showing Answers 1 - 1 of 1 Answers
Mahender
 Nov 1st, 2012

The steps are:
1) Shutdown the database
2) Restore data files
3) Mount the database
4) Recover the database
(sql> recover database until time 2005-12-14:12:10:03 Time base)
(sql> recover database until change scn base)
5) Open the database with the resetlogs option
(sql>atler database open resetlogs)
JUN
10
2008
01:03 AM
2665
Views

2
Ans

DBID in Cloned Environment
shanthiavari RMAN Interview Questions
Outline the steps for changing the DBID in a cloned environment?
abdul_dy
 Jun 15th, 2012
Nid utility is use to change DBID and SID

saneshkale
 Apr 5th, 2010
Note: Mount the databse $export ORACLE_SID=test$ nid target=system/managerIt will change and automaticaly generate new id and asign it to the database.

Answer Question  Select Best Answer
JUN
10
2008
12:42 AM
2456
Views

1
Ans

RMAN Duplicate
shanthiavari RMAN Interview Questions
Is it possible to specific tables when using RMAN DUPLICATE feature? If yes, how?
Sandhya.Kishan
 May 12th, 2012
It is possible to use RMAN duplicate features to specific tables,with RMAN Duplicate we first create a target or duplicate single instance database and then convert the single instance database to a RAC database.

Answer Question  Select Best Answer
JUN
10
2008
12:38 AM
6837
Views

2
Ans

Obsolete and Expired RMAN backups
shanthiavari RMAN Interview Questions
What is the difference between obsolete RMAN backups and expired RMAN backups?
vineet tyagi
 May 8th, 2012
Obsolete: means the backup piece is still available,but it is no longer needed.It depends on retention policy,i,e.recovery window .

Expired: means the backup piece or backup set is not found in the backup destination.

rawishsiddiqui
 Jul 19th, 2008
The term obsolete does not mean the same as expired. In short, obsolete means "not needed," whereas expired means "not found."

Answer Question  Select Best Answer
JUL
22
2008
02:51 AM
5992
Views

6
Ans

RMAN Backup Time
How RMAN improves backup time?
 shanthiavari    Jul 22nd, 2008 6 5995
Questions by shanthiavari

RMAN Interview QuestionsAnswerFirstPrevNextLast
Showing Answers 1 - 6 of 6 Answers
kalyan_ck
 Jul 26th, 2008

RMAN backup time consumption is very less than compared to regular online backup, as RMAN copies only modified blocks
  Was this answer useful?  Yes
Reply
thakur_rakeshb
 Dec 17th, 2009

Add channel to improve the performance of rman, but it create session on DB and I/O on disk will increase, so configure channel at proper number.
  Was this answer useful?  Yes
Reply
Ravikumar
 Sep 7th, 2011

Add channel to improve the Rman backup time
  Was this answer useful?  Yes
Reply
Ajay Chaudhary
 Oct 12th, 2011

Oracle database help us to reduce time for taking backup using followings
1) Oracle performs BLOCKlevel backup.
2) DBA can allocate channels which will start the backup activity in PARALLEL, but we need to ensure that not too many channels have been allocated. Number of allocated channel should not be more than number of CPU.
  Was this answer useful?  Yes
Reply
atif waqar
 Mar 31st, 2012

In 10g You can minimize backup time By "duration 00:30 minimize time;"

run{backup format /complete path duration 00:30 minimize time database}

ADD CHANNELS FOR IMPROVE PERFORMANCE ...
  Was this answer useful?  Yes
Reply
gzhang1970
 May 6th, 2012

run rman BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG incr_update DATABASE;
RECOVER COPY OF DATABASE WITH TAG incr_update;

If this is the first time to run it will create an incremental 0 backup copy of database and then subsequent backup will be incremental 1 and then recover the image copy by using this incremental 1 backup. It this way, we only backup 500Gb DB once and level 0 copy of database get forwarded each time.
OCT
19
2011
04:58 AM
4777
Views

2
Ans

RMAN backup
manish03.dba RMAN Interview Questions
How would you speed up the RMAN backup of a 500GB database?
gopi
 Feb 21st, 2012
Allocate channels / parallelism.

AJ SAR
 Feb 1st, 2012
You can use incremental backup either differential or accumalative. so you dont have to backup the whole database everytime just the changes made

Answer Question  Select Best Answer
JUN
10
2008
12:32 AM
13553
Views

1
Ans


Online Redo Logs
Outline the steps for recovery with missing online redo logs?
 shanthiavari    Jun 10th, 2008 1 13553
Questions by shanthiavari

RMAN Interview QuestionsAnswerFirstPrevNextLast
Showing Answers 1 - 1 of 1 Answers
Lokesh M
profile Profileanswers Answers by Lokesh Mquestions Questions by Lokesh M

 Dec 8th, 2011

Create pfile from spfile
Edit pfile with _allow_resetlog_currpuation=true

1) Connect / as sysdba
2) Startup
3) Recover database until cancel;
4) Alter database open resetlogs;

In RMAN restore:

RMAN> RESTORE CONTROLFILE FROM '[[Location]]';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME "[[Time]]";
RMAN> ALTER DATABASE OPEN RESETLOGS;
JUL
06
2011
01:57 AM
4179
Views

1
Ans

RMAN Encryption Algorithm
What is the meaning of configure encryption algorithm in rman and why we need to set this parameter in rman configuration
 gyanudba    Jul 6th, 2011 1 4179
Questions by gyanudba   answers by gyanudba

RMAN Interview QuestionsAnswerFirstPrevNextLast
Showing Answers 1 - 1 of 1 Answers
John X
 Sep 8th, 2011

The encryption of backup sets by using one of the algorithms listed in V$RMAN_ENCRYPTION_ALGORITHMS. RMAN can transparently encrypt data written to backup sets and decrypt those backup sets when they are needed in a RESTORE operation. RMAN offers three modes of encryption: transparent, password-protected, and dual-mode.
JUN
09
2008
11:01 PM
2360
Views

3
Ans

Hot Backup Pre-requirements
shanthiavari RMAN Interview Questions
When do you recommend hot backup? What are the pre-reqs?
a.rakesh
 Aug 17th, 2011
When there is no down time for database (24/7) ,we should go for hot backup.

pre-req:database must be in archive log mode

leopeter
 Jun 15th, 2011
Database should me archive log mode.

Answer Question  Select Best Answer
JUL
09
2011
07:15 AM
3197
Views

1
Ans

Taking Manual backup with RMAN
hkravipati RMAN Interview Questions
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
dbxplorer
 Jul 24th, 2011

If you already have copy of datafile using OS command, then you can let RMAN know about it using command "CATALOG".

We have copy of datafile "user01.dbf" using OS.

RMAN> Connect target=/
RMAN> CATALOG datafilecopy 'C:/Backup/users01.dbf';

For verification:
RMAN> LIST COPY


Answer Question  Select Best Answer
JUN
10
2008
12:56 AM
2853
Views

3
Ans

Identify Expired, Active, Obsolete Backups
shanthiavari RMAN Interview Questions
How do you identify the expired, active, obsolete backups? Which RMAN command you use?
mangeshchede
 Jun 16th, 2011
1) RMAN>crosscheck backup;This command will give you the output for the Active and Expired Backups.2) RMAN>report obsolete;This command will shows you the obsolete backups.

saneshkale
 Apr 5th, 2010
Use command:Rman > crosscheck backup; Rman > crosscheck archivelog all; Rman > list backup;Rman > list archive logall;

Answer Question  Select Best Answer
JUL
22
2008
02:51 AM
3118
Views

3
Ans

RMAN Backup Information
shanthiavari RMAN Interview Questions
How do you see information about backups in RMAN?
mangeshchede
 Jun 16th, 2011
RMAN>list backup;Above command gives you the information about RMAN backups.

shrikant2711
 Mar 20th, 2009
Use this SQL to checkSQL> SELECT sid,totalwork,sofar FROM v$session_longops
WHERE sid=153;
Here give SID when back start it will show SID
Answer Question  Select Best Answer
JUN
09
2008
11:03 PM
2737
Views

2
Ans

RMAN Recovery Catalog
shanthiavari RMAN Interview Questions
How do you install the RMAN recovery catalog?
leopeter
 Jun 15th, 2011
taya_ronak
 Dec 17th, 2008

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;







RMAN Interview Questions

1. What is RMAN ?

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

Which Files can be backed up using rman?

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.





Oracle Database RMAN Interview Questions
Posted on February 12, 2014 by Brijesh Gogia — 12 Comments ?
Some of  the good Database Backup-Recovery & RMAN related questions are listed below.

Q 1
What is SCN?

A 1
The SCN is an Oracle server–assigned number that indicates a committed version of the database. It’s quite possible that different datafiles in the database might have a different SCN at any given point in time.
At checkpoint, the server will makes all database file SCNs and control file SCN consistent with respect to an identical SCN.The datafiles will not contain any database changes beyond that common SCN. This synchronization of the SCNs will make sure we have a consistent backup of database.
When you are doing hot backup, you may end up with backups of the various datafiles at various time points and different SCNs and you can not open a database without synchronizing the SCN on all data files, so you will have to apply archive logs to make the data current and synchronize the SCNs across the datafiles.







Q 2
What is the significance of fast_start_mttr_target parameter?

A 2
You use the Oracle initialization parameter fast_start_mttr_target to specify the number of seconds you want the crash recovery to take. Oracle will try to recover the
instance as close as possible to the time that you specify for the fast_start_mttr_target parameter. The maximum value of this parameter is 3600 seconds (1 hour).

During instance recovery, in the first roll forward operation, the database server must apply all transactions between the last checkpoint and the end of the redo log to the datafiles. Thus, in order to tune instance recovery, you control the gap between the checkpoint position and the end of the redo log. This is called Mean Time to Recover (MTTR).





Q 3
There was a media failure. How can you find which files you must recover?

A 3
By querying the V$RECOVER_FILE view, which lists all files that need media recovery.





Q 4
What are the benefits of RMAN over user-managed backup-recovery process?



A 4
– powerful Data Recovery Advisor feature
– simpler backup and recovery commands
– automatically manages the backup files without DBA intervention.
– automatically deletes unnecessary backup datafiles and archived redo log files both from disk and tape.
– provides you with detailed reporting of backup actions
– Easy to duplicate a database or create standby database.
– Without actually restoring data, you can test whether you will be able to do it or not
– Incremental backup! only RMAN can do that.
– Active Duplication! (11g new feature), you can perform database duplication without backups by using the network enabled database duplication feature
– automatically detects corrupt data blocks during backups
– Data block level recovery possible in case only few DB blocks are corrupt
– Encrypted backups possible
– Use RMAN with a variety of third-party storage systems easily

With so many benefits, RMAN shold be used as primary backup-recovery tool.





Q 5
How important is Database Redundancy Set and where you should plan to keep it?



A 5
Database Redundancy Set is essential set of recovery-related files. As a DBA, you need to be well prepared for any kind of contingency situation.
It should contain below:
– Recent backups of all datafiles & control file (duplex or triplex the control file at database level)
– All archived redo logs made after the last backup
– Current control files and online redo file copies (duplex online redo files at database level)
– Oracle database-related configuration file copies (spfile, password file, tnsnames.ora and listener.ora files etc)

Operating system mirroring is good, but you should do database level mirroring wherever possible.
If you using ASM, try to have atleast Normal redundancy.

When setting up production systems, use at least two disk drives(one for the redundancy set and the other for the datafiles). They should be completely separated by using different volumes, file systems, disk controllers, and RAID devices to hold the two sets of files
You can set up FRA for keeping the redundancy set. Oracle recommends the flash recovery area as a logical candidate to keep a copy of all the files belonging to the redundancy set (which includes the most recent database backup) on disk.





Q 6
What is the benefit of making automatic control file backup to ON?
A 6

Remember that control file is absolutely necessary during a recovery.

Below command can be used to have automatic control file backup to be taken. This is highly recommended.



RMAN> configure controlfile autobackup on

Now at the end of every RMAN backup command, RMAN automatically backs up the control file.

Even when you make some changes via SQL*Plus( say creating a new tablespace or adding or renaming a datafile or an online redo log member), the control file is automatically backed up.

Also, you can restore RMAN’s backup and recovery information (called RMAN’s repository), when you lose all your control files and aren’t using the optional recovery catalog.



Q 7
What is the significance of RMAN view V$RMAN_OUTPUT?



A 7
V$RMAN_OUTPUT displays messages reported by RMAN. This is an in-memory view (means will not persist thru a database restart)and is not recorded in the controlfile. This information straight out of the database by using SQL queries.

Sometimes RMAN log files may have already been overwritten by the next backup or simply just deleted. This is an alternative way of accessing the RMAN output information

For Example:

Review the last two days worth of rman output:



select output
from v$rman_output
where session_recid in (select session_recid from v$rman_status
where start_time > sysdate-2)
order by recid ;

OUTPUT
———————————————————————————————————————————-
connected to target database: BRIJ (DBID=3142459675)
using target database control file instead of recovery catalog

echo set on

backup archivelog all not backed up delete all input;

Starting backup at 09-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=BRIJ devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=126 instance=BRIJ devtype=DISK
….
…





Q 8
What all you can store in Flash Recovery Area(FRA)? Can one FRA directory be used for more than one database?
A 8
Oracle can store different kind of files under FRA:

– backupset: for RMAN regular backups.
– datafile: for RMAN image copies.
– autobackup: for control file autobackups.
– flashback: If your database runs in flashback mode, you will see flashback logs in this subdirectory.
– archivelog: for Archived redo logs
– controlfile: The control file, if configured to go to the flash recovery area.
– onlinelog: Online redo logs can also be made to go to the flash recovery area

You can use the same flash recovery area for as many databases as you want. The RMAN backup process will create a subdirectory called <SID_NAME>, the same name as the database you are backing up.



Q 9
Which views can be used for Checking Space Usage in the FRA?



A 9

Check Below..

SQL> select NAME,SPACE_LIMIT/1024/1024/1024 TOTAL_GB,SPACE_USED/1024/1024/1024 USED_GB,SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$recovery_file_dest;
NAME TOTAL_GB USED_GB SPACE_RECLAIMABLE NUMBER_OF_FILES
————————————————– ———- ———- —————– —————
/u01/oracle/DB11G/fast_recovery_area 16 1.13197899 0 3



SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– —————— ————————- —————
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .12 0 1
BACKUP PIECE 6.95 0 2
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.





Q 10
Is putting control file and online redo logs in Flash Recovery Area (FRA) advisable?

A 10
Control file is very important file for the database operation. Loosing a single control file will make the database unstable and will lead to interruption in service.
So we will always try to put control file in a safe and stable place.

Similarly online logs are equally important and loosing them can also cause database to crash, incomplete recovery and possible data loss.

CASE 1:
Usually the flash recovery area and the main database disks are located in such a way that the probability of both going down at the same time is very slim.
And If your flash recovery area is in a storage location as reliable as the main database storage, then you should put one control file and one redo member/log group there.
It will surely help you in quick and complete recovery.

CASE 2:
If your flash recovery area is NOT as reliable as the main database storage, the chance of failure in the flash recovery area is greater compared to the main database disks. If the flash recovery area fails, then you lose one of the control files and the online redo log. You will be able to start database easily by removing that control file from the control file parameter in the initialization parameter file (copying online log from the secondary Non-FRA location) and restarting it but you will have an interruption of production service, which is very undesirable.

Scenario A)
Besides FRA, we have multiplexed Control files to two other separate location, so risk of loosing control file (and fear of not able to do complete recovery) is minimized
We won’t be putting even a single control file in the FRA.

Scenario B)
Besides FRA, we have multiplexed Control files to only one other separate location, so risk of loosing control file and (and fear of not able to do complete recovery) is more. Complete recovery of database is of primary importance to you than the database interruption.

Here we can go and put the control file in FRA.





Q 11

How can you make sure that only one of the redo log member is created in FRA?



A 11
If you want only one member of the group in the flash recovery area and the other one in the regular database file location, you should define two parameters—the flash recovery area and db_create_file_dest.

SQL> Show parameter db_recovery_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/oracle/DB11G/fast_recovery_area

SQL> alter system set db_create_file_dest =’/u01/oracle/DB11G/oradata';

System altered.

SQL> alter database add logfile group 4;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
———- ——- ——- ————————————————– ———————–
3 ONLINE /u01/oracle/DB11G/oradata/brij/redo03.log NO
2 ONLINE /u01/oracle/DB11G/oradata/brij/redo02.log NO
1 ONLINE /u01/oracle/DB11G/oradata/brij/redo01.log NO
4 ONLINE /u01/oracle/DB11G/oradata/brij/BRIJ/onlinelog/o1_mf_5_9hl8zjmt_.log NO
4 ONLINE /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_5_9hl8zjsz_.log YES





Q 12

How can you create log of your RMAN activity?

A 12

Two ways to do it

A) WHILE STARTING RMAN

$ rman log=’tmp/rman_log.log’ OR $ rman log /tmp/rman_log.log append

(use with append clause if you don’t wanna overwrite existing file)



Also If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, use tee command:



$ rman | tee /tmp/rman.log

B) WHILE INSIDE RMAN

$rman

RMAN> spool log to ‘/tmp/rman_log.log';

(use with append clause if you don’t wanna overwrite existing file “spool log to ‘/tmp/rman_log.log’ append.)

..
RMAN> spool log off;
** you can use any writeable directory and log file name.





Q 13
Can Recovery Catalog database also be shutdown from RMAN prompt like TARGET Database can be done?



A 13
All the shutdown and startup commands applies only to the target database. You can’t start and stop the recovery catalog instance from RMAN. The only way to start up and shut down the recovery catalog instance is by connecting to the recovery catalog database as the target database and by issuing the relevant commands to start or stop the instance.





Q 14
How to check the syntax of RMAN commands?



A 14

Start the RMAN client with the operating system command-line argument checksyntax.

$ rman checksyntax

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 12 14:36:22 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

The command has no syntax errors

RMAN> backup database;

The command has no syntax errors

You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file.

Example:

$ rman checksyntax @/tmp/rmancmdfile



 Q 15

Does using recovery catalog means RMAN won’t use the control file to store information?



A 15
No. Even when you choose to use a recovery catalog, backup information will continue to be stored in the control file as well by default.





Q 16
What is the benefit of using Recovery Catalog?



A 16

– provides larger storage capacity, thus enabling access to a longer history of backups

– you can create and store RMAN scripts in the recovery catalog and Any client that can connect to the recovery catalog and a target database can use these stored scripts

– Can service many target databases

– you can use ‘KEEP FOREVER’ clause of RMAN backup command.

– Allows you to list the data files and tablespaces that are or  were in the target database at a given time



Q 17
What is the significance of ‘resync catalog’ RMAN command?



A 17
The resync catalog command is used in order to update or resynchronize a recovery catalog from the target database control file. Since sync is done from Target database control file, so you must connect to the recovery catalog as well as to the target database in order to perform the resynchronization.

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

When above command is executed, RMAN will first create a snapshot control file. It’ll then compare the contents of the recovery catalog to the contents of the snapshot control file and update the recovery catalog by adding the missing information and modifying the changed backup and schema related records.

It means that we will keep parameter control_file_record_keep_time longer than our backup interval, so that the data in the control file gets transferred to the recovery catalog before that data is overwritten.

When you issue certain RMAN commands such as the backup command, RMAN automatically performs a resynchronization.





Q 18

You want to move your recovery catalog from one database to another. How you can do it?



A 18
Moving Recovery catalog to another database is simple process

a) Create a new recovery catalog in the target database. You don’t need to register any database to it
b) Use the import catalog command in RMAN after connecting to the target database:



$ rman

RMAN> connect catalog rman_new/rman@target_database

RMAN> import catalog rman_old/rman@source_database;



The import catalog command will import the source recovery catalog contents into the target recovery catalog.





Q 19
What are the most important recovery catalog views?



A 19
Below are some of the frequently used views:

RC_STORED_SCRIPT: Information about RMAN scripts stored in the recovery catalog.
RC_UNUSABLE_BACKUPFILE_DETAILS: Lists the unusable backup files recorded in the recovery catalog.
RC_RMAN_STATUS: Similar to V$RMAN_STATUS view and shows the status of all RMAN operations. This view doesn’t contain information about any operations that are currently executing.
RC_RMAN_CONFIGURATION: Information about persistent configuration settings.
RC_DATAFILE: Shows all datafiles registered in the recovery catalog.
RC_DATABASE: Shows the databases registered in the recovery catalog.
RC_ARCHIVED_LOG: Provides historical information on both archived as well as unarchived redo logs.





Q 20
How to check the version of your recovery catalog?



A 20
You can easily check the version of your recovery catalog by issuing the following command from SQL*Plus after logging in as the recovery catalog owner:

RMAN@rmandb > select * from rcver;

VERSION
————
11.02.00.04

Q 21
What all files can NOT be backed up by RMAN?



A 21
1) Oracle home-related files
2) External files
3) Network configuration files
4) Password files





1. Which types of backups you can take in Oracle?

2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6. You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7. Where should you place Archive logfiles, in the same disk where DB is or another disk?

8. Can you take online backup of a Control file if yes, how?

9. What is a Logical Backup?

10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11. Why do you take tablespaces in Backup mode?

12. What is the advantage of RMAN utility?

13. How RMAN improves backup time?

14. Can you take Offline backups using RMAN?

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

16. What is a Recovery Catalog?

17. Should you place Recovery Catalog in the Same DB?

18. Can you use RMAN without Recovery catalog?

19. Can you take Image Backups using RMAN?

20. Can you use Backupsets created by RMAN with any other utility?

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

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

23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24. Which is more efficient Incremental Backups using RMAN or Incremental Export?



25. Can you start and shutdown DB using RMAN?

26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and backup of only this datafile which is just 1 day old. From which backup should you restore this file?

31. You loss controlfile how do you recover from this?

32. The current logfile gets damaged. What you can do now?

33. What is a Complete Recovery?

34. What is Cancel Based, Time based and Change Based Recovery?

35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

38. How do you recover from the loss of a controlfile if you have backup of controlfile?

39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

41. Have you faced any emergency situation. Tell us how you resolved it?

42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.






What is RMAN?
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recoveryactivities.
What is the difference between using recovery catalog and control file?
When new incarnation happens, the old backup information in control file will be lost. It will be preservedin recovery catalog.In recovery catalog we can store scripts.Recovery catalog is central and can have information of many databases.
Can we use same target database as catalog?
No, The recovery catalog should not reside in the target database (database should be backed up),because the database can’t be recovered in the mounted state.
How do you know that how much RMAN task has been completed?
By querying v$rman_status or v$session_longops
From where list & report commands will get input?
Both the commands command quering v$ and recovery catalog views. V$BACKUP_FILES or many of the recovery catalog views such asRC_DATAFILE_COPY or RC_ARCHIVED_LOG.
Command to delete archive logs older than 7days?
RMAN> delete archivelog all completed before sysdate-7;
How many times does oracle ask before dropping a catalog?
The default is two times one for the actual command, the other for confirmation.
How to view the current defaults for the database.
RMAN> show all;
What is the use of crosscheck command in RMAN?
Crosscheck will be useful to check whether the catalog information is intact with OS level information.This command only updates repository records with the status of the backups.e.g. If user removes archived logs from disk with an operating system command, the repository stillindicates that the logs are on disk, when in fact they are not
What are the differences between crosscheck and validate commands?Validate
command is to examine a backup set and report whether it can be restored. RMAN scans allof the backup pieces in the specified backup sets and looks at the checksum to verify that the contentsare intact so that backup can be successfully restored if necessary.
Crosscheck
command is to verify the status of backups and copies recorded in the RMAN repositoryagainst media such as disk or tape. The crosscheck command only processes files created on thesame device type as the channel running crosscheck.
Which one is good, differential (incremental) backup or cumulative (incremental) backup?
 A differential backup, which backs up all blocks changed after the most recent incremental backup atlevel 1 or 0RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
A cumulative backup, which backs up all blocks changed after the most recent incremental backup atlevel 0RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;Cumulative backups are preferable to differential backups when recovery time is more important thandisk space, because during recovery each differential backup must be applied in succession. Usecumulative incremental backups instead of differential, if enough disk space is available to storecumulative incremental backups.This is command for taking Level 0 backup.RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
What is the difference between backup set and backup piece?
Backup set is logical and backup piece is physical.
RMAN command to backup for creating standby database
RMAN> duplicate target database
 You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backupof 1 week/day old and don’t have backup of this (newly created) datafile. How do yourestore/recover file?
Create data file and recover datafile.SQL> alter database create datafile ‘/u01/app/oracle/oradata/xyz.dbf’ size 2G;RMAN> recover datafile file_id;
What is obsolete backup & expired backup?
 A status of “expired” means that the backup piece or backup set is not found in the backup destination. A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backuppiece is no longer needed since RMAN has been configured to no longer need this piece after so manydays have elapsed, or so many backups have been performed.
What is the difference between hot backup & RMAN backup?
For hot backup, we have to put database in begin backup mode, then take backup.RMAN won’t put database in backup mode.
 How to put manual/user-managed backup in RMAN (recovery catalog)?
By using catalog command.RMAN> CATALOG START WITH ‘/tmp/backup.ctl’
What are the Architectural components of RMAN?
1. RMAN Executables2. Sercer process3. Channels4. Target database5. Recovery catalog database (optional)6. Media management Layer (optional)7. Backups, backup sets and backup pieces
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 theallocation of channels that you govern I/O characteristics:
Type of I/O device being read or written to, either a disk or an sbt_tapeNumber of processes simultaneously accessing an I/O deviceMaximize size of files created on I/O devicesMaximize rate at which database files are readMaximize number of files open at a time
Why is the catalog optional?
Because RMAN manages backup and recovery operations, it requires a place to store necessaryinformation 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.
What is a Backup set?
 A logical grouping of backup files — the backup pieces — that are created when you issue an RMANbackup command. A backup set is RMAN’s name for a collection of files associated with a backup. Abackup set is composed of one or more backup pieces.
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 onlinebackups.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.
What are the various reports available with RMAN
RMAN>list backup;RMAN> list archive;
In catalog database, if some of the blocks are corrupted due to system crash, How will yourecover?
using RMAN BLOCK RECOVER command
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 to2> ‘$HOME/BACKUP/RMAN/ F.bkp’;
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 thetarget database and give list backup this will give you the backup information with date and timing.
How do you identify the block corruption in RMAN database? How do you fix it?
Using v$block_corruption view you can find which blocks corrupted.RMAN> block recover datafile <fileid> block <blockid>;Using the above statement You recover the corrupted blocks. First check whether the block is corruptedor not by using this commandSQL>select file# block# from v$database_block_corruption;file# block2 507the above block is corrupted…conn to RmanTo recover the block use this command…RMAN>blockrecover datafile 2 block 507;the above command recover the block 507Now just verify it…..Rman>blockrecover corruption list;
How do you clone the database using RMAN software? Give brief steps? When do you usecrosscheck command?
Check whether backup pieces proxy copies or disk copies still exist.Two commands available in RMAN to clone database:1) Duplicate2) Restore.
List some of the RMAN catalog view names which contain the catalog information?
RC_DATABASE_INCARNATION RC_BACKUP_COPY_DETAILSRC_BACKUP_CORRUPTIONRC_BACKUP-DATAFILE_SUMMARY
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 arecovery_catalog_owner privilege.3) Login into RMAN with connection stringa) export ORACLE_SIDb) rman target catalog @connection string4) rman> create catalog;
Oracle RMAN Interview Questions/FAQs
Oracle RMAN Interview Questions/FAQs


1. Difference between catalog and nocatalog?
Duplicate Download Pdf Level 1 How to download pdf 7days Catalogs Centrales

2. Difference between using recovery catalog and control file?
When new incarnation happens, the old backup information in control file will be lost. It will be preserved in recovery catalog.
In recovery catalog, we can store scripts.
Recovery catalog is central and can have information of many databases.

3. Can we use same target database as catalog?
No. The recovery catalog should not reside in the target database (database to be backed up), because the database can't be recovered in the mounted state.

4. How do u know how much RMAN task has been completed?
By querying v$rman_status or v$session_longops

5. From where list & report commands will get input?

6. Command to delete archive logs older than 7days?
RMAN> delete archivelog all completed before sysdate-7;

7. How many days backup, by default RMAN stores?

8. What is the use of crosscheck command in RMAN?
Crosscheck will be useful to check whether the catalog information is intact with OS level information.

9. What are the differences between crosscheck and validate commands?

10. Which is one is good, differential (incremental) backup or cumulative (incremental) backup?
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

11. What is Level 0, Level 1 backup?
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

12. Can we perform level 1 backup without level 0 backup?
If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility < 10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup. If compatibility is >= 10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN.

13.  Will RMAN put the database/tablespace/datafile in backup mode?
Nope.

14. What is snapshot control file?

15. What is the difference between backup set and backup piece?
Backup set is logical and backup piece is physical.

16. RMAN command to backup for creating standby database?
RMAN> duplicate target database to standby database ....

17. How to do cloning by using RMAN?
RMAN> duplicate target database …

18. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?
create the datafile and recover that datafile.
SQL> alter database create datafile ‘…path..’ size n;
RMAN> recover datafile file_id;

19. What is obsolete backup & expired backup?
A status of "expired" means that the backup piece or backup set is not found in the backup destination.
A status of "obsolete" means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

20. What is the difference between hot backup & RMAN backup?
For hot backup, we have to put database in begin backup mode, then take backup.
RMAN won’t put database in backup mode.

21. How to put manual/user-managed backup in RMAN (recovery catalog)?
By using catalog command.
RMAN> CATALOG START WITH '/tmp/backup.ctl';

22. What are new features in Oracle 11g RMAN?

23. What is the difference between auxiliary channel and maintenance channel?





Oracle DBA Interview Questions and Answers - Backup and Recovery
Oracle Backup and Recovery Interview Questions and Answers


How would you decide your backup strategy and timing for backup?In fact backup strategy is purely depends upon your organization business need.
If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup.
If sufficient downtime is there and loss of data would not affect your business then you can run your database in noarchivelog mode and backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection.


What is difference between Restoring and Recovery of database?
Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.

What is the difference between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.

What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode.


If an oracle database is crashed? How would you recover that transaction which is not in backup?If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.

What is the difference between HOTBACKUP and RMAN backup?
For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode.

Can we use Same target database as Catalog database?
No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.

Incremental backup levels:
Level 0 – full backup that can be used for subsequent incrementals
RMAN> backup incremental level 0 database;
Differential Level 1–only the blocks that have changed since the last backup (whether it is level 0 or level 1)
RMAN> backup incremental level 1 differential database;
Cumulative Level 1 – all changes since the last level 0 incremental backup
RMAN> backup incremental level 1 cumulative database;
A full backup cannot be used for a cumulative level 1 backup.
A cumulative level 1 backup must be done on top of an incremental level 0 backup.



Why RMAN incremental backup fails even though full backup exists?If you have taken the RMAN full backup using the command ‘Backup database’, where as a level 0 backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository so it can be used as the parent for a level 1 backup. Simply the ‘full backup without level 0’ can not be considered as a parent backup from which you can take level 1 backup.


Can we perform RMAN level 1 backup without level 0?If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version).
If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup.
If the compatibility is greater than 10.0.0, RMAN copies all block changes since the file was created, and stores the results as level 1 backup.

How to put Manual/User managed backup in RMAN?In case of recovery catalog, you can put by using catalog command:
RMAN> CATALOG START WITH ‘/oracle/backup.ctl’;



How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plus
SQL> Select * from rcver;

What happens actually in case of instance Recovery?While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started. Instance recovery occurs in 2 steps:
Cache recovery: Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery: When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

My Database has Level 1 backup, tell me what are all backed up ? with Example?


Database is UP and has taken Level 0 backup, is the backup taken is Consistent or Inconsistent?

How do you say a backup is Consistent or Inconsistent, Oracle Terminology?

Can we take backup when the Database is down?

If i have a RMAN full backup Level 0 of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.What type of backup do you get and what is actually backedup?

If i have a RMAN full backup of Sun @9PM, on Mon 9PM taken incremental Level 1 backup.On Tuesday Database Crashed.What type of backup do you get and what is actually backedup?

There is no Backup available, Can we take a Level 1 backup?

A table got dropped between 9AM - 11AM how to get the Table backup using RMAN,
DB size 500GB available mount point space for table recovery is 15GB?

Sys Admin has changed the time from 10:00 AM to 9:30 AM, table dropped, How do you recover the Table?

A DATAFILE is corrupted and there is no backup, How to recover the datafile?

All Controlfiles are corrupted, How to recover the controlfile?










DBA interview Question and Answer Part 22
I have configured the RMAN with Recovery window of 3 days but on my backup destination only one days archive log is visible while 3 days database backup is available there why?
I go through the issue by checking the backup details using the list command. I found there is already 3 days database as well as archivelog backup list is available. Also the backup is in Recoverable backup. Thus it is clear due to any reason the backup is not stored on Backup place.
Connect rman target database with catalog
List backup Summary;
List Archivelog All;
List Backup Recoverable;
When I check the db_recovery_dest_size, it is 5 GB and our flash-recovery area is almost full because of that it will automatically delete archive logs from backup location. When I increase the db_recovery_dest_size then it is working fine.
If one or all of control file is get corrupted and you are unable to start database then how can you perform recovery?
If one of your control file is missing or corrupted then you have two options to recover it either delete corrupted CONTROLFILE manually from the location and copy the available rest of CONTROLFILE and rename it as per the deleted one. You can check the alert.log for exact name and location of the control file. Another option is delete the corrupted CONTROLFILE and remove the location from Pfile/Spfile. After removing said control file from spfile and start your database.
In another scenario if all of your CONTROLFILE is get corrupted then you need to restore them using RMAN.
As currently none of the CONTROLFILE is mounted so RMAN does not know about the backup or any pre-configured RMAN setting. In order to use the backup we need to pass the DBID (SET DBID=691421794?) to the RMAN.
RMAN>Restore Controlfile from ‘H:\oracle\Backup\ C-1239150297-20130418’
You are working as a DBA and usually taking HOTBACKUP every night. But one day around 3.00 PM one table is dropped and that table is very useful then how will you recover that table?
If your database is running on oracle 10g version and you already enable the recyclebin configuration then you can easily recover dropped table from user_recyclebin or dba_recyclebin by using flashback feature of oracle 10g.
SQL> select object_name,original_name from user_recyclebin;
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 PAY_PAYMENT_MASTER
SQL> flashback table table2 to before drop;
Flashback complete.
In that case when no recyclebin is enabled with your database then you need to restore your backup on TEST database and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 2:55 PM here.
It is not recommended to perform such recovery on production database directly because it is a huge database will take time.
Note: If you are using SYS user to drop any table then user’s object will not go to the recyclebin for SYSTEM tablespace, even you have already set recyclebin parameter ‘true’.
And If you database is running on oracle 9i you require in-complete recovery for the same.
Sometimes why more archivelog is Generating?
There are many reasons such as: if more database changes were performed either using any import/export work or batch jobs or any special task or taking hot backup (For more details why hot backup generating more archive check my separate post).You can check it using enabling log Minor utility.
How can I know my require table is available in export dump file or not?
You can create index file for export dump file using ‘import with index file’ command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.
What is Cache Fusion Technology?
Cache fusion provides a service that allows oracle to keep track of which nodes are writing to which block and ensure that two nodes do not updates duplicates copies of the same block. Cache fusion technology can provides more resource and increase concurrency of users internally. Here multiple caches can able to join and act into one global cache. Thus solving the issues like data consistency internally without any impact on the application code or design.
Why we should we need to open database using RESETLOGS after finishing incomplete recovery?
When we are performing incomplete recovery that means, it is clear we are bringing our database to past time or re-wind period of time. Thus this recovery makes database in prior state of database. The forward sequence of number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs open database with new sequence number of redo log and archive log.
Why export backup is called as logical backup?
Export dump file doesn’t backup or contain any physical structure of database such as datafiles, redolog files, pfile and password file etc. Instead of physical structure, export dump contains logical structure of database like definition of tablespace, segment, schema etc. Due to these reason export dump is call logical backup.
What are difference between 9i and 10g OEM?
In oracle 9i OEM having limited capability or resource compares to oracle 10g grids. There are too many enhancements in 10g OEM over 9i, several tools such as AWR and ADDM has been incorporated and there is SQL Tuning advisor also available.
Can we use same target database as catalog DB?
The recovery catalog should not reside in the target database because recovery catalog must be protected in the event of loss of the target database.
What is difference between CROSSCHECK and VALIDATE command?
Validate command is to examine a backup set and report whether it can be restored successfully where as crosscheck command is to verify the status of backup and copies recorded in the RMAN repository against the media such as disk or tape.
How do you identify or fix block Corruption in RMAN database?
You can use the v$block_corruption view to identify which block is corrupted then use the ‘blockrecover’ command to recover it.
SQL>select file# block# from v$database_block_corruption;
file# block
10 1435
RMAN>blockrecover datafile 10 block 1435;
What is auxiliary channel in RMAN? When it is required?
An auxiliary channel is a link to auxiliary instance. If you do not have automatic channel configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.
Explain the use of Setting GLOBAL_NAME equal to true?
Setting GLOBAL_NAMES indicates how you might connect to the database. This variable is either ‘TRUE’ or ‘FALSE’ and if it is set to ‘TRUE’ which enforces database links to have the same name as the remote database to which they are linking.
How can you say your data in database is Valid or secure?
If data of the database is validated we can say that our database is secured. There is different way to validate the data:
1. Accept only valid data
2. Reject bad data.
3. Sanitize bad data.
Write a query to display all the odd number from table.
Select * from (select employee_number, rownum rn from pay_employee_personal_info)
where MOD (rn, 2) <> 0;
-or- you can perform the same things through the below function.
set serveroutput on;
begin
for v_c1 in (select num from tab_no) loop
if mod(v_c1.num,2) = 1 then
dbms_output.put_line(v_c1.num);
end if;
end loop;
end;
What is difference between Trim and Truncate?
Truncate is a DDL command which delete the contents of a table completely, without affecting the table structures where as Trim is a function which changes the column output in select statement or to remove the blank space from left and right of the string.
When to use the option clause "PASSWORD FILE" in the RMAN DUPLICATE command?
If you create a duplicate DB not a standby DB, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance and if you create a standby DB, then RMAN copies the password file by default to the standby host overwriting the existing password file.
What is Oracle Golden Gate?
Oracle GoldenGate is oracle’s strategic solution for real time data integration. Oracle GoldenGate captures, filters, routes, verifies, transforms, and delivers transactional data in real-time, across Oracle and heterogeneous environments with very low impact and preserved transaction integrity. The transaction data management provides read consistency, maintaining referential integrity between source and target systems.
What is meaning of LGWR SYNC and LGWR ASYNC in log archive destination parameter for standby configuration.
When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC means LGWR doesn’t wait to finish network I/O and continuing write processing.
What is the truncate command enhancement in Oracle 12c?
In the previous release, there was not a direct option available to truncate a master table while child table exist and having records.
Now the truncate table with cascade option in 12c truncates the records in master as well as all referenced child table with an enabled ON DELETE constraint.



Here are some questions which are real time based. If you could answer that will be very helpful.

1. Archivelog destination gets filling up and it is now 98%. I have more transactions at this time. DB is going to crash or hang in another 2 minutes? what is the step to be followed here?

2. First day you are taking up archivelog backup? next day morning you find some archivelogs missing? will RMAN show some logs are missing or it will take up backup when you give backup archive log command?

3.During RMAN backup of database, if your destination drive (mount point) found corrupted, how do you mount the new mount point with the same name as the corrupted mount point in another server , if no catalog database or with catalog db?

4.While at a peak time, where there is a huge transactions takesplace, you just happended to find PMON died ? will there be transactions happen after PMON's crash or database will go crash after finishing up all the transactions? Will database do instance recovery after bouncing up db after such a scenario?

5. You are issuing sqlplus username/password@connstring. what is actually happening at the background in db after you give that sqlplus command?
For this question my answer was oracle is creating a session for that particular user process. is this right?

6.If a user is telling you that i could not connect to the database? what should be checked here?




1. Archivelog destination gets filling up and it is now 98%. I have more transactions at this time. DB is going to crash or hang in another 2 minutes? what is the step to be followed here?
You can move old archive to other mount point. (Be careful moving will increase server load). Then take those archive backup and delete.
2. First day you are taking up archivelog backup? next day morning you find some archivelogs missing? will RMAN show some logs are missing or it will take up backup when you give backup archive log command?
I m not sure

3.During RMAN backup of database, if your destination drive (mount point) found corrupted, how do you mount the new mount point with the same name as the corrupted mount point in another server , if no catalog database or with catalog db?
You can ask OS teams to create another mount point in the same server for your backup and reconfigure the rman. You can configure the catalog of database in another server but you cannot configure rman backup destination to another server till 10g ( I dont know about 11g) meance your backup can be configured only to your target db server.
4.While at a peak time, where there is a huge transactions takesplace, you just happended to find PMON died ? will there be transactions happen after PMON's crash or database will go crash after finishing up all the transactions? Will database do instance recovery after bouncing up db after such a scenario?
If your PMON killed your entire process will go die within few sec but if your current online redo is present, oracle will do instance recovery on next startup
5. You are issuing sqlplus username/password@connstring. what is actually happening at the background in db after you give that sqlplus command?
For this question my answer was oracle is creating a session for that particular user process. is this right?

if you use @connecting string meance with the help of your tnsnames.ora file user process will resolving the service name. Based on the informations , it will go to the server and check the port mentioned, where listner is linstening.. if the listner is running and if he registerd with the same instance which you are requesting for, you will asign with a server process (if it is a dedicated connection) ... afer that listner will go back to listen for another request.. then based on the username and password you provide, server process will allow you to get the informations from the database.
6.If a user is telling you that i could not connect to the database? what should be checked here?
So many things based on the error messege, you can ask to the interveiwer what is the error is he/she getting?

Regards
Nishant Santhan
Like Show 0 Likes (0) Actions
7. Re: Real time scenarios - interview questions
 500872
Newbie
500872 May 25, 2009 8:52 AM (in response to Nishant Santhan)
Thanks very much Nishant.Now i could come to a conclusion. i got some ideas from your answers..

Let me post some of the questions later..

hope this would be useful for all of us..
Like Show 0 Likes (0) Actions
8. Re: Real time scenarios - interview questions
 500872
Newbie
500872 May 25, 2009 8:53 AM (in response to Nishant Santhan)
Thanks very much Nishant.Now i could come to a conclusion. i got some ideas from your answers..

Let me post some of the questions later..

hope this would be useful for all of us..
Like Show 0 Likes (0) Actions
9. Re: Real time scenarios - interview questions
 baskar.l
Expert
baskar.l May 25, 2009 8:58 AM (in response to 500872)
Thanks Balu for your good questions...it helps all to share their knowlege...

baskar.l
Like Show 0 Likes (0) Actions
10. Re: Real time scenarios - interview questions
 Girish Sharma
Legend
Girish Sharma May 25, 2009 9:42 AM (in response to 500872)
Ans 1.
a)If archive location is full then database will stop to operate i.e. hung or cease to opeate. All pending transations will be suspended and an alert entry will be written in alertsid.log.
b)SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST='your new filesystem' SCOPE=MEMORY;
New archive log generated files will be easily generated at new location. Archiver process keeps tracks of every file having full path at the time of writing; so that RMAN can get the file name and path in further recovery operations i.e. where are archive logs have been kept. You only have to worry about space availability; rest wories have been well handeled by Oracle designers.

Ans 2.
a)select name, sequence#, status from v$archived_log; you need to determine the archivelog gap sequence between current and last backup to see if you would have any possible data loss due to the missing archive logs.
b)If the backup of archive logs done by rman fails for the reason some or one archive log file to be backed up is not available.. you may validate the archive logs by issuing change validate archive log all command from rman prompt and then back up the archive logs.
c)for the missing archive logs... you can do the following

connect target /
connect catalog rman/pwd@rmancatalog
run {
change archivelog from logseq = xxx until logseq =
yyy unavailable;
}

xxx and yyy are the logseg numbers, the one's you are missing.
Got from a link but forgot to add in my oracle notes.

Ans 3.
RMAN catalog is not a server; it is a instance of the db. We can'nt assign mount point of another server for backup.
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14192/setup004.htm#i1017851

Ans 4.
http://www.oracle-base.com/forums/viewtopic.php?f=1&t=851
and read the reply given by Tim sir.
http://www.adp-gmbh.ch/ora/concepts/processes/pmon.html is also a good link for PMON.

Ans 5.
username/pw@XXX. Here XXX means tns entry in tnsnames.ora. User process is going to evoke based on the information in XXX entry.

Ans 6.
"I am not a


ble to connect the db". You will ask, "ok, what error is coming" or "tell me how you are going to connect the db". It depends, what error message you are getting and go for resolve it.

Regards
Girish Sharma
Like Show 0 Likes (0) Actions
11. Re: Real time scenarios - interview questions
 500872
Newbie
500872 May 25, 2009 10:00 AM (in response to Girish Sharma)
Hi Girish,

Thanks very much..some more ideas..great.






Different RMAN Recovery Scenarios
In this article we will discuss about different type of database recovery scenarios. The target database name as well as catalog database can be different. Consider you have sufficient backup for this example such as daily incremental backup for all targets database on (Sat-Thurs day) and Weekly full backup on (Friday). The Daily and weekly backup scripts includes datafile, archive log and control & spfile autobackup. The target databases versions are Oracle 9i/10g where as platform can be windows 2003 and LINUX.  The motive of this article is to give the fresher or Junior DBA confidence “How to apply different Recovery scenario in different situation”. Some of the scenario’s recovery logs are from our Production database and some of them taken from other DBA’s.
One fine morning our DBserver is restarted due to hard disk crashed and OS issue. Once the disk is repaired and OS is restored then the database is mounted successfully but we cannot able to open the database. It needs media recovery with the following errors. As most of the tablespace datafiles are corrupted, we decided to do complete database recovery.
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: ‘D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'
The corruption happens Wednesday morning session so we have daily incremental night backup of Sun-Wed with full backup of Saturday (29th September 2012) and entire month archive log on the disk. We just mounted the database.
C:\rman target sys/****@sadhan catalog catalog/catalog@rman
Recovery Manager: Release 9.2.0.1.0 – Production Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
RMAN> SHUTDOWN IMMEDIATE;
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
RMAN> RESTORE DATABASE;
Starting restore at 03-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2981_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
restoring datafile 00011 to G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
restoring datafile 00012 to G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2983_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
restoring datafile 00013 to F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2985_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
restoring datafile 00010 to F:\ORACLE\SADHAN\SDH_EDSS01.DBF
restoring datafile 00014 to D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2982_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 03-OCT-12
RMAN> RECOVER DATABASE;
Starting recover at 03-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3036_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3038_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3037_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3040_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3039_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3041_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3043_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3042_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3045_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3044_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/03/2012 20:00:52
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [37833581], [1], [4504], [70575], [244], [], []
ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)
ORA-10564: tablespace SDH_HRMS_DBF
ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918
RMAN> ALTER DATABASE OPEN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/03/2012 20:01:30
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'
At this stage we do not have to be panic. We have already restored the database successfully. This is due to the reason. It is very old database created in noresetlogs mode. We cannot open it without performing incomplete recovery.
Now Login with SQL*Plus in mount phase
SQL> recover database until cancel;
Press enter as long as you reach to the missing log
CANCEL
SQL> alter database open resetlogs;
Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" to connect rman target to take fresh backup.
C:\CONNECT RMAN TARGET SYS/SYSMAN@SADHAN.WORD CATALOG CATALOG/CATALOG@RMAN
RMAN> RESET DATABASE;

One of the junior DBA while working with Production environment on Thursday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. As the DBA restarted the database, one of data file is starts complaining. Then the DBA decided to recover that particular datafile.
SQL> startup;
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF'
He made the corrupted data file OFFLINE and opened the database, so users can use the database while recovering that particular data file (but in case of system01.dbf you need sufficient downtime to recover).
SQL> alter database datafile ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> Select file_id from dba_data_files where file_name = ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF';
FILE_ID
----------
6
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Now connect the RMAN with catalog and restore and recover the datafile 6
C:\>rman target sys/****@mujazhr.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 25 14:30:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MUJAZORC (DBID=1165034825)
connected to recovery catalog database
RMAN>run
    {
    restore datafile 6;
    recover datafile 6;
    }
Starting restore at 25-JUN-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=6 name=D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 25-JUN-12
Starting recover at 25-JUN-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-JUN-12
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 25 14:34:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database datafile ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' ONLINE;
Database altered.
SQL> Select distinct status from dba_data_files;
STATUS
---------
AVAILABLE

One of our Production database is related with Retail & Trading firm on Windows 2003 env. On Friday morning database goes down. As we restarted the database, DB complaining one of datafile is corrupted, may be this is due to the hard disk repair work happened on Thursday Evening where as other tablespaces datafiles are as it is having no issue. So we decided to recover only that particular tablespace.
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF '
We made this tablespace offline and open the database. So that end users can use the database while recovering the tablespace (as this is not the system tablespace).
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF' OFFLINE;
Database altered.
SQL> alter database open;
alter database open
* ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF'
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF' OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Production With the Partitioning, OLAP and Data Mining options
C:\>rman target sys/****@ISSCOHR.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 12 10:13:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISSCOHR (DBID=2613999945)
connected to recovery catalog database
RMAN> run{
      restore tablespace EDSS_DBF;
      recover tablespace EDSS_DBF;
      }
Starting restore at 12-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=7 name= D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF
channel ORA_DISK_1: reading from backup piece H:\ORABACK\ WEEKLY_20130112_FULL_ISSCOHR-2587_1.DB
channel ORA_DISK_1: restored backup piece 1
piece handle= H:\ORABACK\WEEKLY_20130112_FULL_ISSCOHR-2588_1.DB
channel ORA_DISK_1: restore complete, elapsed time: 00:13:11
Finished restore at 12-JAN-13
Starting recover at 12-JAN-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 36 is already on disk as file E:\ORACLE\ARCHIVE\ ARC02244.001
archive log thread 1 sequence 37 is already on disk as file E:\ORACLE\ARCHIVE\ ARC02245.001
archive log thread 1 sequence 38 is already on disk as file E:\ORACLE\ARCHIVE\ ARC02246.001
archive log thread 1 sequence 39 is already on disk as file E:\ORACLE\ARCHIVE\ ARC02247.001
media recovery complete, elapsed time: 00:01:23
Finished recover at 12-JUN-13
SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF' ONLINE;
Database altered.
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF' ONLINE;

If SYSTEM tablespace gets corrupted and others are intact. Then you need sufficient downtime to recover this tablespace as you can not open the database without recovering the SYSTEM tablespace.
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
Take the SYSTEM tablespace offline and recover that tablespace only. The recovery log taken from any other Test database.
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF’ OFFLINE;
Database altered.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
* ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> run
     {
     restore tablespace system;
     recover tablespace system;
     }
Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open;
alter database open
* ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' online;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select distinct status from dba_tablespaces;
STATUS
---------
ONLINE
SQL> select distinct status from dba_data_files;
STATUS
---------
AVAILABLE

One afternoon, you restarted your database and realized that all the redo log files are gets corrupted but fortunately you did not lost the controlfile. Thus you are able to mount the database. To recover all those redo log files, you have decided to perform incomplete recovery.
SQL> startup;
ORACLE instance started.
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
As you have taken the backup in the morning mount the database and run the RMAN backup first, just to make sure all the archived redo log files are backed up before start the actual recovery process.
Once RMAN backup is completed, you have to perform incomplete recovery and recovered until the last archived redo log. The recovery logs are taken from any other source.
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> backup archivelog all;
Starting backup at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=31 stamp=687541158
input archive log thread=1 sequence=3 recid=32 stamp=687542042
input archive log thread=1 sequence=4 recid=33 stamp=687542062
comment=NONE
Starting Control File and SPFILE Autobackup at 23-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-09
RMAN> list backup of archivelog from time='sysdate-1';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-09
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T224042
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_07KFPFVA_7_1

List of Archived Logs in backup set 10692
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 551206 23-MAY-09 551263 23-MAY-09
RMAN> restore database until sequence=6 thread=1 force;
Starting restore at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20090523T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-09
RMAN> recover database until sequence=6 thread=1 ;
Starting recover at 23-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-MAY-09
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommended to takes the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side.

Unfortunately, if you lost all the data files, control files, redo log files. But luckily the current archived redo log files were intact.  In that case you are not able to mount the database since lost your controlfile too.
SQL> startup;
ORACLE instance started.
ORA-00205: error in identifying control file, check alert log for more info
You need to recover first the control file from RMAN backup. Once control file is recovered, you are able to mount the database. After the database is mounted run the RMAN backup to make sure, all the current archive log files are backed up and recover the data base until last the sequence of archived log file.
C:\>set oracle_sid=orcl
C:\>rman catalog=rman/rman@catdb target=sys/password
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00 tag=TAG20090524T152409
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-09
SQL> alter database mount;
Database altered.
SQL> select archivelog_change#-1 from v$database;
ARCHIVELOG_CHANGE#-1
--------------------
547010
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
{
       set until scn 547010;
       restore database;
       recover database;
       alter database open resetlogs;
       }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAY-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

One of the Application programmer truncated the critical table in the evening around 3.46 PM and they need to recover the truncated table. They have to decide to go to the time based incomplete recovery of the exact 03.45 PM.
Find the Exact time for recovery and set the date and time format along with time based recovery scripts.
SQL> Select count(*) from employee;
COUNT(*)
----------
14
SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
from dual;
TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42
SQL> truncate table employee;
Table truncated.
SQL> select count(*) from employee;
COUNT(*)
----------
0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL>
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
      {
      set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
      restore database;
      recover database;
      }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;
COUNT(*)
----------
14
Note: If you use the ‘alter database open resetlogs’ command from SQL*PLUS then you must need to use ‘reset database’ command to connect RMAN catalog for fresh backup.




set3






RMAN Backup and Recovery Scenario Point-In-Time Recovery
Backup and Restore Scenario from existing Level 0 backup
-------------------------------------------------------
steps to recover the database:-

1. Take Level 0 backup.
2. Lost SYSTEM datafile.
3. Start the instance without mounting the database.
4. Restore Controlfile.
5. Mount the database.
6. Restore database.
7. Recovery database.
8. Open the database and reset logs.

1. Take Level 0 backup:

run
{
allocate channel channel1 type disk;
allocate channel channel2 type disk;
allocate channel channel3 type disk;
backup incremental level=0 database;
backup archivelog all;
backup current controlfile;
}

2. Lost SYSTEM datafile.

Note:
----
Need controlfile, backup and parameter file for recoverying database.

3. Start the instance without mounting the database.

$sqlplus "/as sysdba"

SQL> startup nomount;
SQL> exit

4. Restore Controlfile.

$rman target /
RMAN> restore controlfile;

5. Mount the database.
RMAN> alter database mount;

6. Restore and recover & Point-In-Time Recovery of all data back to a particular date/time in the past.
run
{
set until time "to_date('2013-03-20:14:40:00','YYYY-MM-DD:hh24:mi:ss')";
restore database;
recovery database;
}

7.Open the database and reset logs.

SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

NOTE:
----
As soon as you have done a resetlogs run a full backup, this is important as should you suffer a second failure you will not be able to perform a second recovery because after resetting the logs the SCN numbers will no longer match any older backup files.






set 4



1) How can you see the Current SCN number of the database?
> Select current_scn from v$database;

2) How can you see the Current log sequence number the logwriter is writing in to?
> Select * from v$log;

3) If you are given a database, how will you know how many datafiles each tablespace contain?
> Select distinct tablespace_name,file_name from dba_data_files;

4). How will you know which temporaray tablepsace is allocated to which user?
> Select temporary_tablespace from dba_users where username=’SCOTT’;

5) If you are given a database,how will you know whether it is locally managed or dictionary managed?
> Select extent_management from dba_tablespaces where tablespace_name=’USERS’;
6) How will you list all the tablespaces and their status in a database?
> Select tablespace_name,status from dba_tablespaces;

7) How will you find the system wide 1) default permanent tablespace, 2) default temporary tablespace 3) Database time zone?
> Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;

8) How will you find the current users who are using temporary tablespace segments?
> V$TEMPSEG_USAGE

9) How will you convert an existing dictionary managed permanent tablespace to temporary tablespace?
> Not possible

10) Is media recovery requird if a tablespace is taken offline immediate?
> Not required

11) How will you convert dictionary managed tablespace to locally managed tablespace?
> Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);

12) If you have given command to make a tablespace offline normal, but its not happening.it is in transactional read-only mode. How will you find which are the transactions which are preventing theconversion?
> By looking at queries using by those SID (u can get script from net). I suspect question is not clear.

13) If you drop a tablespace containing 4 datafiles, how many datafiles will be droped at a time by giving a single drop tablespace command?

> All datafiles

14) If database is not in OMF,How will you drop all the datafiles of a tablespace without dropping the tablespace itself?
> Alter database datafile ‘PATH’ offline drop;

15) How will you convert the locally managed tablespace to dictionay managed?What are the limitations?
> Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);

SYSTEM tablespace should be dictionary

16) Which parameter defines the max number of datafile in database?
> Db_files and MAXDATAFILES in control file

17) Can a single datafile be allocated to two tablespaces?Why?
> No. because segments cannot space multiple datafiles

18) How will you check if a datafile is Autoextinsible?
> Select autoextensible from dba_data_files where file_name=’’;

19) Write command to make all datafiles of a tablespace offline without making the tablspace offline itself?
> Alter database datafile ‘PATH’ offline normal;

20) In 10g, How to allocate more than one temporary tablespace as default temporary tablespace to a single user?
> By using temporary tablespace group

21) What is the relation between db_files and maxdatafiles parameters?
> Both will restrict no of datafiles in the database

22) Is it possible to make tempfiles as read only?
> yes

23) What is the common column between dba_tablespaces and dba_datafiles?
> Tablespace_name

24) Write a query to display the names of all dynamic performance views?
> Select table_name from dictionary where table_name like ‘v$%’;

25) Name the script that needs to be executed to create the data dictionary views after database creation?
> Catalog.sql

26) Grant to the user SCOTT the RESTRICTED SESSION privilege?
SQL> grant restricted session to scott;
Grant succeeded.

27) How are privileged users being authenticated on the database you are currently working on? Which initialization parameter would give me this information?
> Question not clear

28) Which dynamic performance view gives you information about all privileged users who have been granted sysdba or sysoper roles? Query the view?
SQL> desc v$pwfile_users

29) What is the purpose of the DICTIONARY table?
> To know data dictionary and dynamic performance view names

30) Write a query to display the file# and the status of all datafiles that are offline?
> Select file#,status from v$datafile where status=’OFFLINE’;

31) Write the statement to display the size of the System Global Area (SGA)?
> Show parameter sga
Or
> Show sga

32) Obtain the information about the current database? What is its name and creation date?
> Select name,created from v$database;

33) What is the size of the database buffer cache? Which two initialization Parameters are used to determine this value?

> Db_cache_size or db_block_buffers

34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you need to set up Operating System authentication?
> exclusive

35) Which initialization parameter holds this value? What does the shared pool comprise of?
> Library cache and data dictionary cache.
> Parameter : shared_pool_size

36) Which initialization parameter holds the name of the database?
> Db_name

37) Which dynamic performance view displays information about the active transactions in the database? Which view returns session related information?
> V$transaction, v$session

38) Which dynamic performance view is useful for killing user sessions? Which columns of the view will you require to kill a user session? Write the statement to kill any of the currently active sessions in your database?
> V$session (SID, SERAIL#)
> Alter system kill session ‘SID,SERIAL#’;

39) What is the difference between the ALTER SYSTEM and ALTER SESSION commands?
> Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session

40) Write down the mandatory steps that a DBA would need to perform before the CREATE DATABASE command may be used to create a database?
> Create a pfile or spfile
> Create password file
> If windows, create instance using ORADIM utility

41) What does the script utlexcpt.sql create? What is this table used for?

> It will create EXECEPTIONS table. See below link

42) In which Oracle subdirectory are all the SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc…? Located?
> $ORACLE_HOME/rdbms/admin/

43) Which dynamic performance view would you use to display the OPTIMAL size of the rollback segment RBS2. Write a query to retrieve the OPTIMAL size and Rollback segment name?
> V$undostat (but many scripts are available in google or even in my blog)
44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Extent size

45) How would I start the database if only users with the RESTRICTED SESSION privilege need to access it?
> Startup restrict

46) Which data dictionary view would you query to find out information about free extents in your database? Write a query to display a count of the number of free extents in your database?
> We can use scripts. Exactly its difficult to know

47) Write a query to display the tablespace name, datafile name and type of extent management (local or dictionary) from the data dictionary?
> You need to combine dba_data_files and dba_tablespaces

48) Which two types of tablespace cannot be taken offline or dropped?
> SYSTEM and UNDO

49) When a tablespace is offline can it be made read only? Perform the
Required steps to confirm your answer?
> Didn’t got the answer

50) Which parameter specifies the percentage of space in each data block that is reserved for future updates?
> PCTFREE
51) write down two reasons why automatic extent allocation for an extent may fail?
> If the disk space reached max limit
> If autoextend reached maxsize limit

52) Query the DBA_CONSTRAINTS view and display the names of all the constraints that are created on the CUSTOMER table?
> Select constraint_name from dba_constraints where table_name=’CUSTOMER’;

53) Write a command to display the names of all BITMAP indexes created in the database?
> Select index_name from dba_indexes where index_type=’BITMAP’;

54) Write a command to coalesce the extents of any index of your choice?
> Alter tablespace <tablespace_name> coalesce;
> Don’t know for extents

55) . What happens to a row that is bigger than a single block? What is this called? Which data dictionary view can be queried to obtain information about such blocks?
> Row will be chained into multiple blocks. CHAINED_ROWS is the view

56) Write a query to retrieve the employee number and ROWIDs of all rows that belong to the EMP table belonging to user SCOTT?
> Select rowid,empno from scott.emp;

57) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Repeated question

58) How to compile a view? How to compile a table?
> Alter view <view_name> compile;
> Tables cannot be compiled

59) What is the block size of your database and how do you see it?
> Db_block_size

60) At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.?
> We can recover it from alert log file which contains non-default values

61) You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
> By configuring backup retention policy to redundancy 3
















set 5



1.       Which types of backups you can take in Oracle?

2.       A database is running in NOARCHIVELOG mode then which type of backups you can take?

3.       Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4.       Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5.       How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6.       You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7.       Where should you place Archive logfiles, in the same disk where DB is or another disk?

8.       Can you take online backup of a Control file if yes, how?

9.       What is a Logical Backup?

10.   Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11.   Why do you take tablespaces in Backup mode?

12.   What is the advantage of RMAN utility?

13.   How RMAN improves backup time?

14.   Can you take Offline backups using RMAN?

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

16.   What is a Recovery Catalog?

17.   Should you place Recovery Catalog in the Same DB?

18.   Can you use RMAN without Recovery catalog?

19.   Can you take Image Backups using RMAN?

20.   Can you use Backupsets created by RMAN with any other utility?

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

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

23.   You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24.   Which is more efficient Incremental Backups using RMAN or Incremental Export?

25.   Can you start and shutdown DB using RMAN?

26.   How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27.   You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28.   You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29.   How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30.   You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

31.   You loss controlfile how do you recover from this?

32.    The current logfile gets damaged. What you can do now?

33.   What is a Complete Recovery?

34.   What is Cancel Based, Time based and Change Based Recovery?

35.   Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36.   Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37.   A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

38.   How do you recover from the loss of a controlfile if you have backup of controlfile?

39.   Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40.   Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?


41.   Have you faced any emergency situation. Tell us how you resolved it?

42.   At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

43.   What is Consistent Backup?
A Consistent backup is one in which the files being backed up contain all changes upto the same system change number (SCN)

44.   What is fractured Block?
Because the database continues writing to the file during an online backup, there is the possibility of backing up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the old data in the top half of the block and the new data in the bottom top half of the block. The block is a fractured block, meaning that the data in this block is not consistent.
45.   What are the steps to performing complete recovery on the whole database?
Mount the database
Ensure that all datafiles you want to recover are online
Restore a backup of the whole database or the files you want to recover
Apply online or archived redo logs, or a combination of the two

46.   What are the steps to performing complete recovery on a tablespace or datafile?
Take the tablespace or datafile to be recovered offline if the database is open
Restore a backup of the datafiles you want to recover
Apply online or archived redo logs, or a combination of the two



47.   What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

48.   What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

49.   What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

50.   What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

51.   Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

52.   What is schema?
A schema is collection of database objects of a user.

53.   What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

54.   Can objects of the same schema reside in different tablespaces?
Yes.

55.   Can a tablespace hold objects from different schemes?
Yes.

56.   What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

57.   What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

58.   Do a view contain data?
Views do not contain or store data.

59.   Can a view based on another view?
Yes.

60.   What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.

61.   What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

62.   What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.

63.   What are the types of synonyms?
There are two types of synonyms private and public.

64.   What is a private synonym?
Only its owner can access a private synonym.

65.   What is a public synonym?

66.   Any database user can access a public synonym.


67.   What are synonyms used for?
- Mask the real name and owner of an object. - Provide public access to an object - Provide location transparency for tables, views or program units of a remote database. - Simplify the SQL statements for database users.

68.   What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

69.   How are the index updates?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

70.   What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

71.   What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

72.   What is index cluster?
A cluster with an index on the cluster key.

73.   What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

74.   When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

75.   What is database link?
A database link is a named object that describes a "path" from one database to another.

76.   What are the types of database links?
Private database link, public database link & network database link.

77.   What is private database link?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

78.   What is public database link?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

79.   What is network database link?
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

80.   What is data block?
Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

81.   How to define data block size?
A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

82.   What is row chaining?
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

83.   What is an extent?
An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

84.   What is a segment?
A segment is a set of extents allocated for a certain logical structure.

85.   What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.

86.   What is a data segment?
Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

87.   What is an index segment?
Each index has an index segment that stores all of its data.

88.   What is rollback segment?
A database contains one or more rollback segments to temporarily store "undo" information.



89.   What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

90.   What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

91.   What is a datafile?
Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tablesand indexes is physically stored in the data files allocated for a database.

92.   What are the characteristics of data files?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

93.   What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.

94.   What is the function of redo log?
The primary function of the redo log is to record all changes made to data.

95.   What is the use of redo log information?
The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

96.   What does a control file contains?
- Database name - Names and locations of a database's files and redolog files. - Time stamp of database creation.

97.   What is the use of control file?
When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.