rman interviews questions and answers ---2 in oracle
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.
Comments
Post a Comment