Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 22 May 2012

Interview question with Answer Part 9

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 <filename> creates a BACKUPSET from a copy of controlfile. You
have to give the FILENAME.
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).
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.
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.
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.
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.
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;
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.
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.
How many times does oracle ask before dropping a catalog?
The default is two times one for the actual command, the other for confirmation.
What are the various reports available with RMAN?
rman>list backup; rman> list archive;
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)
Can RMAN write to disk and tape Parallel? Is it possible?
Rman currently won't do tape directly, you 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.
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;
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.
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.
How to Increase Size of Redo Log
1. Add new log files (groups) with new size
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
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.
Can I cancel a script from inside the script? How I cancil a select on Windows client?
Use ctl-c
How to Find the Number of Oracle Instances Running on Windows Machine
C:\>net start |find “OracleService”
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;
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.
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.
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.
How to know Number of CPUs on Oracle
Login as SYSDBA
SQL>show parameter cpu_count
cpu_count integer 2
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.
How you will check flashback is enabled or not?
Select flashback_on from v$database;
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.
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.
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
Oradim –delete –svrc
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;
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
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.
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)
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.
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.
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;


  1. Bluehost is ultimately one of the best website hosting provider with plans for any hosting requirments.

  2. Looking for the Best Dating Site? Join and find your perfect match.