Senior Oracle DBA Q&A

 

Q) My incremental bkp is running more time than usual any particular reason for that?

If you haven't seen this issue before and assuming database size didn't changed then you have to check the speed between database server and media/backup server.

Sometimes if we change the NIC card configuration linked with backup server then  such issues pop-ups

Also check with OS team first and ask them if they have seen same issues with OS backups also (if Scheduled).

 

Q) Why voting is disk is odd in number?

More 50 % always available concept by Oracle...

2 when you divide by 2 is 1 and 3 when divided by 2 is 1.5 (consider it 2 as a whole Number) .

So odd number will always be higher in case of a voting disk availability.

So more than 50% is availability achieved by it then.

Whoever scores more will survive, else will get eliminated.

 

Q) How long SQL_ID will be in shared pool ?

Till it is not flushed out

If there is a pressure on shared pool, old SQLs might get flushed

 

 

Q) Why SQL_plan_hash value is changed and why child_number change for SQLID?

The answer for why SQL PHV change is .

When a SQL of a particular SQL ID is parsed optimizer generate multiple plans and CBO to select the best plan among multiple. Plan based on the cost. After some days if you insert loads of data or stats gather the object then optimizer will again generate the best plan and if this plan is best in terms of cost it will start using this new plan.

 You can also manually change the phv of a SQL id by using DBMS _spm package

 

Table EMP with 1 Million Rows.

Index on GENDER Column. M is 98% and F is 2 %.

SELECT * FROM EMP WHERE GENDER=:B1 for M will be a Full Table Scan with a PLAN_HASH_VALUE and CHILD_NUMBER as 0.

The same query with value as F will be an Index Scan and therefore, will have different PLAN_HASH_VALUE and CHILD_NUMBER as 1.

A query can have multiple plans depending upon the bind variable values and each plan will create a child cursor.

 

Another example...

Table EMP with 1 Million Rows. No Index on Gender Column. M is 98% and F is 2%.

Query 1 : select * from emp where gender=:b1 and the value is M.

It goes for a Full Table Scan and Plan_hash_value is some value with child_number 0.

Now, I set optimizer_index_cost_adj to 10 at session level and re-run the same query for M.

Again a Full Table Scan as I do not have an Index on Gender.

The Plan_hash_value remains same, but child_number is now 1..so, same plan_hash_value and 2 Child_numbers..

 

Oracle will use histogram to identify skewedness of data in order to decide whether child no 0 or 1 is appropriate for execution.

Yes. Histogram will play a critical role here

Nice example for new child cursor creation. This is one case where new child cursor is created for same plan_hash_value.. there are multiple factor which cause new child creation like length of bind from application, different character sets for the bind, different cardinality estimate for bind aware cursor.

 

 

Q) How can we decide, when to use SQL profiling and SQL Baseline ?

We can decide to use SQL plan baseline when your SQL statement have multiple plane hash value. Out of those multiple phv if optimizer is using bad phv when there are good phv present you can use baseline to force optimizer to use good phv to avoid performance regression

Dba_sql_plan_baseline

See how phv that SQL has

Check the columns enabled accepted fixed

Of enabled is yes then that phv is currently in use

If accepted is no then that phv is accepted in baseline but not used

If you want to permanent allocate phv to a SQL then fix it

But in SQL profiling, we can use good phv based on elapsed time and IO.

 

Q) SQL profiling good or SQL Baseline?

SQL baseline will be good

SQL Profile for reactive situation and SQL baseline for Proactive

 

                           

Q) Why to rebuid indexes?

               Index data constantly changes due to the underlying table’s DML activity. Indexes often become too large if there are many deletions, because the space used by the deleted values is not reused automatically by the index. You can use the REBUILD command on a periodic basis to reorganize indexes to make them more compact and thus more efficient.

 

Q) What happens in background during patching?

               1.Opatch takes backup of those files from O_H which it is going to update

               2.It takes this backup in O_H/.patch_storage directory

               3.It then copies files from /etc and /files to O_H

               4.After that it takes backup of Inventory in /files

               5.Relinks all O_H binaries and update inventory with patch ID

               6.Finally it displays- patch applied

 

Q) Facing performance issues with these wait events not completing the processing requests.

 

Oracle Troubleshooting Questions & Answers

 1) Will RMAN take backups of read-only tablespaces?

    No

2)  Which log would you check if a database has a problem?

      Alert log

3)  How can you check the structure of a table from sqlplus?

     DESCRIBE or DESC1.

4) What command will you start to run the installation of Oracle software on Linux?

     runInstaller

5) What should you do if you encounter an ORA-600?

    Contact Oracle Support

6) Where can you find official Oracle documentation?

     tahiti.oracle.com

7)  What is the address of the Official Oracle Support?

     metalink.oracle.com or support.oracle.com

8)  What file will you use to establish Oracle connections from a remote client?

     tnsnames.ora

9) How can you check if the database is accepting connections?

    lsnrctl status or lsnrctl services

10) In which Oracle version was Data Pump introduced?

      Oracle 10g

11) What is the most important requirement in order to use RMAN to make consistent hotbackups?

      Your database has to be in ARCHIVELOG mode

12) Can you connect to a local database without a listener?

      Yes, you can

13) How can you view all the users account in the database?

      SELECT USERNAME FROM DBA_USERS;

14) How can you view all the current users connected in your database in this moment?

      SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;

15) Is it possible to backup your database without the use of an RMAN database to store thecatalog?

      Yes, but the catalog would be stored in the controlfile

16) Will RMAN take backups of read-only tablespaces?

      No

17) How to clear database buffer cache ,redolog buffer cache and shared pool.

      shared Pool-ALTER SYSTEM FLUSH SHARED_POOL;

      database buffer cache-

15) What are the scripts run at the time  of manual daataabase creation.

      1.Catalog.sql

      2.catproc.sql

         conn / as sysdba

      3.pupbld.sql

16) How to check how many databases are runing on OS level.

      ps -ef|grep pmon

17) How to open database in archive log mode.

      shutdown immediate

      alter database nomount

      alter database mount

      alter database archivelog;

      alter database open

18) Where Alert logfile locted.

       Background_dump_dest

19) What is being written into the Redo Log Files?

      Redo log records all changes made in datafiles.

20) What are the network file in oracle.

      sqlnet.ora tns.ora,Listener.ora

21) What is password  file;

       Password file is the authitication secure file to login to dataabse if it is present on the server .It is not case sensitive.

22) How to resize the size of tablespace.

      Alter database datafile '/u01/app/oracle/oradtaa/db01.dbf' resize 20m;

---------------With Backup-------------

      If datafile is deleted how to recover it by backup or without backup;

      RMAN>restore datafile 2;

      RMAN>Recover datafile 2;         (Backup)

      RMAN>sql'Alter database open';

------------Without Backup-------------

     1.tablespace offline.

     2.alter tablespace add datafile /u01/app/oracle/oradtaa/db01.dbf' size 20m;

     3.recover datafile 2;

     4.alter tablespace online

22) Can we create dictionary managed tablespace in 10g/11g?

      Yes, but SYSTEM tablespace also should be dictionary for that

23) Can we take SYSTEM and SYSAUX tablespace offline?

      We can take SYSAUX, but not SYSTEM

24) Can we make temporary tablespace read only?

      Not possible

25) Is it possible to reduce the size of datafile?

      Yes, but data level should not have been crossed the limit

26) If a transaction is running and DBA has taken particular rollback segment offline. What happens to that transaction?

      It will start using SYSTEM tablespace

27) How to convert dictionary managed permanent tablespace to temporary tablespace?

      It is not possible

28) You are trying to add a datafile, but its not allowing and saying you cannot add it. What might be the reason?

      Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile might have reached its max value

29) Your standby database was out of reach because of network issue. How will you synchronize it with primary database again?

      Data Guard automatically resynchronizes the standby following network or standby outages using redo data that has been archived at the primary.

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

31) 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.

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

      using RMAN BLOCK RECOVER command

33) There was a media failure. How can you find which files you must recover?

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

34) Can one FRA directory be used for more than one database?

       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.

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

      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.

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

      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.

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

      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:

38) 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.   

                                                                          

39) 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.          

40) 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.  

41) 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. 

42) 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. 

43) 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;

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

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

45)  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

46)  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

47)  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

48)  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

49) 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.

50) Can you backup the online redo log files?

      We cannot backup the online redo logs; online redo logs are protected by multiplexing and optionally by archiving

51) Can we take incremental backup without taking the full backup?

      No, full backup should be taken before the incremental backup.

52) Can you take offline backup with RMAN?

      Yes, you can take offline backup with RMAN

53) Can you us RMAN without recovery catalog?

      Yes, RMAN can be used without reocvery catalog.

54) If the database is running in the noarchivelog mode, then which type of backups can you take?

      In this case,  you can take only cold backups

55) Can you take online backup of a control file?

      Yes, by using the following statement: Alter database backup controlfile to '<location>' or trace.

 

56) Can you backup the online redo log files?

      We cannot backup the online redo logs; however online redo logs are protected by multiplexing and optionally by archiving.

57) Can you take online backups if the database is running in the NOARCHIVELOG mode?

      No

58) Can we take incremental backup without taking the full backup?

      No, full backup should be taken before the incremental backup.

59) Can you track changes to blocks during hot backup?

      Oracle database 11g offers block change tracking feature.

      It records the information in block change tracking file.

      RMAN uses this information to determine specific blocks to be backed up again without re-scanning the entire datafile.

 

60) Can you take the backup of log files if the database is running in the ARCHIVELOG mode?

      No, you cannot take the backup of log files if the database is running in the ARCHIVELOG mode.

 

61) How can you manage storage options during export or import?

      You can manage storage options during export or import by using the compress option. It ensures that the storage is compressed whenever the parameter value is Y.

 

61) Can you use RMAN without recovery catalog?

      Yes

 

62) How to check database is in archivelog mode or not.?

      sql>archivelog list;

      sql>select log_mode from v$database

63) Can we use backup set create by rman with any other utility.?

      NO

64) Can we take image backup in rman how.?

       Yes,

      rman>backup database as image copy device type to disk

65) Can we take online rman backup if database is runing in noarchivelog mode.?

      No database wants to be in archivelog mode.

66) Should u place catalog in same database.?

      No 

67) Loss of controlfile .how will u recover it.

      alter database recover using backup controlfile untill cancel;

Dataguard Questions & Answers

 1) What is Dataguard?

     Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

2) What is DG Broker?

     DG Broker “it is the management and monitoring tool”.

     Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.

     All management operations can be performed either through OEM, which uses the broker (or)  broker specified command-line tool interface “DGMGRL”.

3) What is the difference between Dataguard and Standby?

     Dataguard  : 

     Dataguard is mechanism/tool to maintain standby database.

     The dataguard is set up between primary and standby instance .

     Data Guard is only available on Enterprise Edition.

     Standby Database :

     Physical standby database provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis.

     Standby capability is available on Standard Edition. 

4) What are the types of Oracle Data Guard?

    Physical standby (Redo Apply technology)

    Logical standby (SQL Apply Technology)

5) What is Data Guard’s Automatic Gap Resolution?

     Your database is using ASYNC transport method and the instance load is at the peak. The LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby, the LNS automatically transitions to reading and sending from the Online Redo logs. Once the LNS is caught up, it automatically transitions back to reading & sending directly from the log buffer.

     Now in some cases there can be two or more log switches before the LNS has completed sending the redo information from online redo log files and in meantime if any such required online redo log files were archived then those redo information will be transmitted via Data Guard’s gap resolution process “Automatic Gap Resolution”.

6) What is the difference between Physical standby and Logical standby database?

     Physical Standby:

     In this case standby database is an exact, block-by-block, physical replica of the primary database.

     The change vectors received by RFS process are directly applied to the standby database by using media recovery.so here the apply process read data blocks, assemble redo changes from mappings, and then apply redo changes to data blocks directly.

     Physical Standby is the best choice for disaster recovery (DR) based upon their simplicity, transparency, high performance, and good data protection.

     Logical Standby:

     In this case standby database uses SQL Apply method to “mine” the redo by converting it to logical change records, and then building SQL

     transactions and applying SQL to the standby database.

     As this process of replaying the workload is more complex than the Physical Standby’s process, so it requires more memory, CPU, and I/O.

    One good advantage here is that a logical standby database can be opened read-write while SQL Apply is active which means you can update (create/insert/delete etc) local tables and schemas in the logical standby database.

7) What is Active Data Guard Option (Oracle Database 11g Enterprise Edition)?

     For physical standby database, prior to 11g, the database would have to be in the mount state when media recovery was active which means you were not able to query the standby database during media recovery stage as there was no read-consistent view.

     Active Data Guard 11g features solves the read consistency problem by use of a “query” SCN. The media recovery process on the standby database will advance the query SCN after all the changes in a transaction have been applied . The query SCN will appear to user as the CURRENT_SCN column in the V$DATABASE view on the standby database. So Read-only users will only be able to see data up to the query SCN, and hence guaranteeing the same read consistency as the primary database.

    This enables a physical standby database to be open as read-only while media recovery is active, making it useful for doing read-only workloads.

8) What are different Data Guard protection modes?

    Data Guard protection modes implement rules that controls how the configuration will respond to failures, enabling you to achieve specific objectives for data protection, availability, and performance.

9) What is Switchover event?

      Switchover is useful for minimizing downtime during planned maintenance. It is a planned event in which Data Guard reverses the roles of the primary and a standby database.

      The primary database runs unaffected while we are making the required changes on our standby database (e.g. patchset upgrades, full Oracle version upgrades, etc).

      Once changes are complete, production is switched over to the standby site running at the new release.

10) What is Failover event?

       The Failover process is similar to switchover event except that the primary database never has the chance to write an EOR record as this is an unplanned event.

11) Which tools can be used for Data Guard Management?

      1) SQL*Plus – traditional method, can prove most tedious to use

      2) Data Guard broker – automates and centralizes the creation, maintenance, and monitoring of a Data Guard configuration. Simplifies and automates many administrative

          tasks. It has its own command line (DGMGRL) and syntax.

      3) Enterprise Manager – requires that the Data Guard broker be enabled. a GUI to the Data Guard broker, replacing the DGMGRL command line and interfacing directly with the broker’s monitor processes.

12) What is Data Guard 11g snapshot standby?

      With 11g, you can thoroughly test your changes on a true replica of your production system and database using actual production workload.

      Data Guard 11g physical standby can now be converted to a snapshot standby, independent of the primary database, that is open read-write and able to be used for preproduction testing. It uses Flashback Database and sets a guaranteed restore point (GRP) at the SCN before the standby was open read-write.

13) What are the advantages in using Oracle Data Guard?

      High Availability.

      Data Protection.

      Off-loading Backup operation to standby database.

      Automatic Gap detection and Resolution in standby database.

      Automatic Role Transition using Data Guard Broker.

14) What are the different services available in Oracle Data Guard?

       Redo Transport Services.

       Log Apply Services.

       Role -Transitions.

15) What are the different Protection modes available in Oracle Data Guard?

      Maximum Protection

      Maximum Availability

      Maximum Performance

16) How to check what protection mode of primary database in your Oracle Data Guard?

      SELECT PROTECTION_MODE FROM V$DATABASE;

17) What are the advantages of using Physical standby database in Oracle Data Guard?

      High Availability.

      Load balancing (Backup and Reporting).

      Data Protection.

      Disaster Recovery.

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

      Till Oracle 10g, 9 standby databases are supported.

      From Oracle 11g R2, we can create 30 standby databases.

19) What are the services required on primary database?

       Primary database requires log writer to generate log information, archiver process to generate archive log files, and fetch archive log server to request the archive log files from standby database.

20) What happens when stand by database is not available?

       If standby database is not available and the changes are made in primary database, then there will be a gap in the sequence of archive logs. The information about archive logs in these gaps can be found in the v$archieve_gap view.

RMAN Backup & Recovery Questions & Answers

 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) How do you know that how much RMAN task has been completed?

    By querying v$rman_status or v$session_longops

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

    RMAN> delete archivelog all completed before sysdate-7;

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

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

6) How to view the current defaults for the database.

    RMAN> show all;

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

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

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

     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.

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

     Backup set is logical and backup piece is physical.

10) 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.

11) 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.

12) What are the Architectural components of RMAN?

       RMAN Executables

       Server process

       Channels

       Target database

       Recovery catalog database (optional)

      Media management Layer (optional)

      Backups, backup sets and backup pieces

13) 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.

14) 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.

15) 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.

16) 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

17) 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.

18) 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

19) How do you install the RMAN recovery catalog?

      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;

20) What is the difference between physical and logical backups?

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

21) What is RAID? What is RAID0? What is RAID1?

       RAID: It is a redundant array of independent disk

       RAID0: Concatenation and stripping

       RAID1: Mirroring

22) 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.

23) 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;

24) How can you display warning messages?

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

25) How do you backup the entire database?

      RMAN> BACKUP DATABASE;

26) How do you backup an individual tablespaces?

      RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

      RMAN> BACKUP TABLESPACE system;

27) How do you backup datafiles and control files?

      RMAN> BACKUP DATAFILE 3;

      RMAN> BACKUP CURRENT CONTROLFILE;

28) What are the benefits of RMAN over user-managed backup-recovery process?

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

29) How important is Database Redundancy Set and where you should plan to keep it?

      Database Redundancy Set is essential set of recovery-related files. 

– Recent backups of all datafiles & control file 

– All archived redo logs made after the last backup

– Current control files and online redo file copies 

– Oracle database-related configuration file copies 

30) What is the benefit of making automatic control file backup to ON?

       Remember that control file is absolutely necessary during a recovery.

       RMAN> configure controlfile autobackup on

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

31) What all you can store in Flash Recovery Area(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

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

      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.

33) How to check the syntax of RMAN commands?

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

      $ rman checksyntax

34) What is the benefit of using Recovery Catalog?

– 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

35) How to check the version of your recovery catalog?

      RMAN@rmandb > select * from rcver;

36) What all files can NOT be backed up by RMAN?

       1) Oracle home-related files

       2) External files

       3) Network configuration files

       4) Password files

37) 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.

38) How much of overhead in running BACKUP VALIDATE DATABASE and RESTORE VALIDATE DATABASE commands to check for block corruptions using RMAN? 

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

39) 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

40) 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.                 

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

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

42) What are the various reports available with RMAN?  

       rman>list backup; 

       rman> list archive;     

43) 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)                                       

44) 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… 

45) How you will check flashback is enabled or not?   

      Select flashback_on from v$database;  

46) RMAN command to backup for creating standby database?

      RMAN> duplicate target database to standby database ....

47) How to do cloning by using RMAN?

      RMAN> duplicate target database …

48) How to check RMAN configuration?

      RMAN>Show all;

49) 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

50) 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

51) 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

52) What are the database file's that RMAN can backup?

       RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile

53) 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 

54) 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

55) 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

56) 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

57) Difference between catalog and nocatalog?

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

58) 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.

59) 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.

60) 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.

61) Which tools can you use for full backup?

      Oracle recommended the use of RMAN.

62) what is catalog command and how is it used?

      Add information of backup pieces and images copies in the repository that are on disk.

      Record information about the level 0 backup in the RMAN repository.

      Record information about the copy taken by the operating system.

62) How do you mark the beginning of backup and what happens after that/

      sql> alter database begin backup;

      freezing the header of datafiles.

     After that, the files cannot be changed. the changes in the data will be recorded in the files after the backup is complete.

63) How do you find the total database size in the database/

     dba_segments

     dba_data_files

     v$log

64) How does RMAN improve performance of backup?

      RMAN uses multiple channels and does not take backup of free blocks. this is the reason why performance of RMAN backup is better.

64) what is a recovery catalog/

      It is an inventory of the backup taken by RMAN.

65) what does RMAN backup consist of/

      It is a backup of all or part of database. the results from issuing an RMAN backup command. A backup consists of one or more backup sets.

66) What is instance recovery?

       Instance recovery is used in Real Application cluster (RAC) environment only.

       It occurs in an open database when one instance detects that another instance has crashed.

67) What is complete recovery?

      Complete recovery uses redo data or incremental backups combined with a backup of a database, datafile or tablespace to update it to the most current point in time.

      Oracle applies all the redo changes contained in the archived and online logs to the backup.

      During a complete recovery, all the changes made to the restored file since the time of the backup are redone.

 

68) What is full backup?

       A full backup is a backup of all the datafiles, control files and SPFILE.

       A full backup can be made with RMAN or the operating system commands while the database is open or closed.

       As a rule, you must perform full backup, if your database is not running in the archiving log mode.

 

69) What is a recovery catalog?

       Recovery catalog is an inventory of the backup taken by RMAN for the database.

       It is used to restore a physical backup, reconstruct it, and make it available to the Oracle server.

70) What are channels?

      RMAN process uses channel to communicate with I/O devices.

      You can control the type of I/O device, parallelism, number of files and size of files by allocating channels.

71) What is the difference between incremental backup and differential backup?

       Both, incremental and differential backup files that have been modified or created after the previous backup.

       However, attributes are reset after the incremental backup but not after the differential backup.

 

72) What is logical backup?

       Logical backup is a process of extracting data in the form of SQL statements, where it is useful to recover in case the objects are lost.

       The main drawback of using this backup is that MEAN TIME TO RECOVER is high.

73) Which tools can you use for full backup?

       You can use either the operating system utilities or the RMAN utility for full backup.

       However, Oracle recommends the use of RMAN utility.

 

74) What is catalog command?

      Catalog command is used to register different types of information with RMAN’s repository.

 

75) What are time based and change based recoveries?

       Time based and change based recoveries are forms of incomplete recovery.

       These are used when one knows the time or system change number up to which one wants to recover the database.

76) What is a backup set?

       Backup set is a logical grouping of backup files that are created when you issue an RMAN backup command.

       It is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

 

77) Which is more efficient – incremental backups using RMAN or incremental export?

       RMAN incremental backup is more efficient than the incremental export.

78) What is the difference between hot backup and cold backup?

       Hot backup is taken when database is still online while cold backup is taken when database is offline.

       Database needs to be in the archive log mode for the hot backup but there is no such requirement for the cold backup.

79) Which files must be backed up?

       Database files

       Control files

       Archived log files

       Password files

       INIT.ORA

80) What does RMAN backup consist of?

       RMAN backup consists of a backup of all or part of a database.

       This result from issuing an RMAN backup command.

       A backup consists of one or more backup sets.

    

81) What is catalog command and how it is used?

      Catalog command is used to register different types of information with RMAN's repository.

82) What are the benefits of using RMAN?

       Incremental backups that only copy data blocks, which have changed since the last backup

       Tablespaces are not put in the backup mode; therefore, there is no extra redo log generation during online backups.

       Detection of corrupt blocks during backups

       Parallelization of I/O operations

       Automatic logging of all the backup and recovery operations

       Built in reporting and listing commands.

83) What is factured block or corrupted 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.

84) How do u came to know tht which table is dropped.?

      Data pump exports - If you care about the data in this table, you would have exported it nightly.

      RMAN - You can recover a single dropped table by restoring the entire database into your test environment using RMAN, and then roll forward. You can then extract the table and copy it from test to production using CTAS over a database link and then re-add the indexes and constraints.

      LogMiner - Oracle LogMiner can used to recover a dropped table.  See here, the steps to recover a dropped table using LogMiner. 

      Flashback - It's easy to recover a dropped table with these steps in flashback.

85) Which type of backup u can take in oracle.

       1.RMAN 2.User Managed  3.IMP/EXP

86) How will u take backup of controlfile.

      1.alter database backup conrtrol file to trace;

      2.alter database backup controlfile to '/u01/opt/control02.ctl';

Oracle-Tablespace Question & Answers

 1) What is a Tablespace?

     A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.

2) Why use materialized view instead of a table?

     Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

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

    Alter tablespace undo_ts retention guarantee;

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

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

5 )What is the difference between dictionary managed tablespace and locally managed tablespace?

     In dictionary managed tablespace, free block information is maintained in data dictionary cache there by increases IO. In locally managed tablespace, the same information is maintained in datafile header itself there by reducing the IO. Hence LMT is better than DMT

6) How to change the segment management type for an existing tablespace?

    Once defined, we cannot change segment space management for any tablespace

7) What happens when you take a tablespace/datafile offline immediate?

     Any existing transactions will be stopped and datafiles of this tablespace need recovery when we make them online

8) How to identify which datafiles are modified today?

    By looking at the timestamp of the files at OS level

9) What is the new feature of 11g tablespace management?

    Encryption of the tablespace

10) What is bigfile tablespace and its use?

       It gives easy manageability for VLDB by providing terabytes size to a single datafile itself

11) How will do capacity planning for your production databases?

     We will take every quarter for normal databases and every week for critical and fast growing databases

12) What is OMF? What are its advantages and disadvantages?

       It helps in managing files by oracle automatically. But naming convention will be the problem

13) How you will get timezone of database?

      NLS_TIMESTAMP_TZ_FORMAT

14) How will you find out the current users who are using temporary tablespace segments?

      By checking in v$tempseg_usage

15) How to drop a datafile without dropping a tablespace?

      SQL> alter database datafile ‘path’ offline drop;

16) How to check SCN of the database?

      SQL> select current_scn from v$database;

17) What is the relation between db_files and maxdatafiles?

       Both will specify how many max datafiles can be there in the database

18) How do you add a datafile to a tablespace?

      You can add a datafile to a tablespace by using the ALTER TABLESPACE ADD DATAFILE SIZE; statement.

19) What are the advantages of using locally managed tablespace?

      Reduced contention on data dictionary tables

      No rollback generated

      No coalescing required

      Reduced recursive space management

20) What view would you use to determine free space in a tablespace?

      The DBA_FREE_SPACE view can be used to determine free space in a tablespace.

Oracle DBA Interview Questions and Answers - Architecture

 1) What is the primary duty of the DBA?

    The primary duty of the DBA is to protect the database and provide continuous access to that data for the user community.

    Duties of a DBA might include software and hardware installation, configuration of new hardware and software, security, performance tuning, backup and recovery, routine maintenance, trouble shooting and failure recovery.

 

2) What is difference between oracle SID and Oracle service name?

    Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

3) What are bind variables?

    With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

4) What is the difference between data block/extent/segment?

     A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

5) What is SGA? Define structure of shared pool component of SGA?

    The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.

    Shared pool portion contain three major area: 

    Library cache

    data dictionary cache

    data buffer cache

6) What is the difference between SMON and PMON processes?

     SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.

     PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

7) What is the main purpose of ‘CHECKPOINT’ in oracle database?

     A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery.

8) What happens when we fire SQL statement in Oracle?

    First it will check the syntax and semantics in library cache, after that it will create execution plan. 

    If already data is in buffer cache it will directly return to the client. 

    If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

9) What is the use of large pool, which case you need to set the large pool?

    You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

10) What does database do during the mounting process?

      While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.

11) What is log switch?

      The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch.

      ALTER SYSTEM SWITCH LOGFILE;

12) How to check Oracle database version?

      SQL> Select * from v$version;

13) Explain Oracle Architecture?

Oracle Instance:

a means to access an Oracle database, always opens one and only one database and consists of memory structures and background process.

Oracle server:

a DBMS that provides an open, comprehensive, integrated approach to information management, Consists of an Instance and a database.

Oracle database:

a collection of data that is treated as a unit, Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Instance memory Structures:

System Global Area (SGA):

SGA Memory structures:

Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer, Data Dictionary Cache, Database Buffer Cache, User process, Server process

Program Global Area (PGA):  

Memory area used by a single Oracle server process.

Allocated when the server process is started, deallocated when the process is terminated and used by only one process.

Used to process SQL statements and to hold logon and other session information.

Background processes: 

Started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

      1.      Mandatory background processes

      2.      Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT,  LGWR,  SMON

Optional background processes:

– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

System Monitor (SMON) Responsibilities:

• Instance recovery

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

14) Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

      orainstRoot.sh needs to be run to change the Permissions and group name to 770 and to dba.

      Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

      orainstRoot.sh

15) What is oraInventory ?

      oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.

16) Difference between RESETLOGS and NORESETLOGS ?

      I) The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

      II) Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

17) What is SCN (System Change Number) ?

      The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.

      Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.

18) What is Database Incarnation ?

      Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.

      Database incarnation falls into following category Current, Parent, Ancestor and Sibling

19) How to view Database Incarnation history of Database ?

      Using SQL> select * from v$database_incarnation;

      Using RMAN>LIST INCARNATION;

20) What is SGA_TARGET and SGA_MAX_SIZE ?

       SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don't use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow.

       SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE.

21) What is an Oracle Instance?

       An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The combination of the background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same Oracle Database Server, where each instance connects to its database.

22) What information is stored in Control File?

Oracle Database must have at least one control file. It’s a binary file contains like:

The database name and unique ID

The timestamp of database creation

The names and locations of associated datafiles and redo log files

Tablespace information

Datafile offline ranges

Archived log information and history

Backup set and backup piece information

Backup datafile and redo log information

Datafile copy information

Log records: sequence numbers, SCN range in each log

RMAN Catalog

Database block corruption information

23) When you start an Oracle DB which file is accessed first?

      Oracle first opens and reads the initialization parameter file (init.ora)

24) What is Instance Recovery?

      While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started.

Instance recovery occurs in two steps:

Cache recovery:

Changes being made to a database are recorded in the database buffer cache. These changes are also recorded in online 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 the data in the database buffer cache are written to data files, Oracle uses the data recorded in the 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. The data stored in the undo segment is used to restore the original values in case a 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.

25) What is a Tablespace

       A tablespace is a logical storage unit within an Oracle database. Tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.

       A datafile belongs to exactly one tablespace. Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table’s or index’ data is stored.

      Permanent tablespaces

      Undo tablespaces

      Temporary tablespaces

26) Which default Database roles are created when you create a Database?

      CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

27) What is a Checkpoint?

       A checkpoint occurs when the DBWR (database writer) process writes all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks. Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.

28) Which Process reads data from Datafiles?

       Server Process – There is no background process which reads data from datafile or database buffer. Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA. Oracle can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.

29) Which Process writes data in Datafiles?

       Database Writer background process DBWn (20 possible) writes dirty buffers from the buffer cache to the data files. In other words, this process writes modified blocks permanently to disk.

30) What is a Shared Pool?

       The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

31) What is kept in the Database Buffer Cache?

      The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

32) What is difference between PFile and SPFile?

       A PFILE is a static, text file located in $ORACLE_HOME/dbs – UNIX

       An SPFILE (Server Parameter File) is a persistent server-side binary file that can only be modified with the “ALTER SYSTEM SET” command.

34) Can you change SHARED_POOL_SIZE online?

       YES. That’s possible.

       SQL>alter system set shared_pool_size=500M scope=both;

35)  You want users to change their passwords every 2 months. How do you enforce this?

       Oracle password security is implemented via Oracle “profiles” which are assigned to users.

        PASSWORD_LIFE_TIME – limits the number of days the same password can be used for authentication.

First, start by creating security “profile” in Oracle database and then alter the user to belong to the profile group.

1) creating a profile:

2) Create user and assign user to the all_users profile

3) To “alter profile” parameter, say; change to three months:

36) What is Automatic Management of Segment Space setting?

      Oracle9i New Feature Series: Automatic Segment Space Management

      Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps.

      It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.

      ASSM can be specified only with the locally managed tablespaces (LMT).

     Oracle uses bitmaps to manage the free space. Bitmaps allow Oracle to manage free space more automatically.

37) Compare and contrast TRUNCATE and DELETE for a table?

      Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

38) How do you move tables from one tablespace to another tablespace?

      There are several methods to do this;

      1) export the table, drop the table, create the table definition in the new

           tablespace, and then import the data (imp ignore=y).

      2) Create a new table in the new tablespace with the CREATE TABLE statement AS SELECT all from source table

SQL Statement:

CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM source_table;

Then drop the original table and rename the temporary table as the original:

DROP TABLE real_table;

RENAME temp_name TO real_table;

39) Define transaction?

      A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.

40) What is the difference between sql&oracle?

       SQL is Stuctured Query Language.Oracle is a Database.SQL is used to write queries against Oracle DB.

41) What are different Oracle database objects?

       TABLES

       VIEWS

       INDEXES

       SYNONYMS

       SEQUENCES

       TABLESPACES

42) What are the dictionary tables used to monitor a database spaces ?

       DBA_FREE_SPACE

       DBA_SEGMENTS

       DBA_DATA_FILES

43) What is a database instance?

       A database instance is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.

44) What is the difference between a view and a synonym?

       Synonym is just a second name of table used for multiple link of database.View can be created with many tables, and with virtual columns and with conditions.But synonym can be on view.

45) What command would you use to create a backup control file?

       Alter database backup control file to trace.

46) What is the difference between database and instance?

       Database is a collection of data files that contain the information of interest; whereas, instance is the combination of background processes and memory structure.

47) What are the three types of files that make up an Oracle database?

      The three types of files that make up an Oracle database are :

      Datafiles

      Control files and Redo log files.

48) What are the types of privileges?

      Oracle has two kinds of privileges :

      System Privileges

      Object Privileges

49) What is the difference between system and object privileges?

      This statement when executed will display all the available system privileges such as Create Session, Drop User, Alter Database and so on.

50) What are the different types of memory structure available in Oracle?

       System Global Area(SGA)

       Program Global Area(PGA)

51) What is log switch?

       A log switch is a point when loG WRiter (LGWR)fills one online redo log group and writing to another.At every log switch a checkpoint,occurs.

52) 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.

53)  What Oracle object is used for read consistency?

        The rollback segments are used for read consistency.

54) What is the control file?

      Control file is a file that contains all the information about the physical structure of the database,such as the number of log files and their location.

55) Which files must be backed up?

      The following files must be backed up :

      Database files

      Control files

       Archived log files

56) What is SQL*Loader?

       SQL*Loader is a tool to load data from file to a database table.

57) What is a synonym?

       A synonym is simply an alias to another object in the database. This object can be a table, view, sequence or program unit.

58) What is archive log file?

       Archive log file stores redo log information persistently. A redo log is overwritten with time; therefore, archive log maintains that lost information.

 

59) How do you control the maximum number of redo log files in a database?

      The maximum number of redo log files can be controlled by the parameter MAXLOGFILES.

 

60) What is the difference between a privilege and a role?

       A privilege is the authority to perform a certain act or command.

       A role is a collection of privileges and roles that can be assigned to a user.

61) What is the difference between a temporary table space and a permanent table space?

       A temporary tablespace provides temporary storage during the processing of database function, such as sorting; whereas

       A permanent tablespace is used to store permanent database objects, such as tables, partitions, indexes and clusters.

62) Define Database Management System (DBMS) and RDBMS?

      DBMS is a program that defines the rules for data storage and retrieval.

      RDBMS is a special type of DBMS that stores the data in the relational format as described in the relational theory by E.F. Codd.

63) How do you add a datafile to a tablespace?

      You can add a datafile to a tablespace by using the ALTER TABLESPACE ADD DATAFILE SIZE; statement.

64) What view would you use to determine free space in a tablespace?

       The DBA_FREE_SPACE view can be used to determine free space in a tablespace.

65) What is the control file? What kind of information is stored in a control file?

       Control file is a file that contains all the information about the physical structure of the database, such as number of log files and their location.

       Oracle database server uses control file to find its physical component.

Control file stores information about log switches, checkpoints, and modification in disk resources

66) How to check how many database is running from os level?

      ps -ef|grep pmon

67) What is maxbytes?

       The MAXBYTES function counts the number of bytes in the longest line of a multiline text expression. The result returned by MAXBYTES has the same dimensions as the specified expression.

68) How to check size of table?

      select segment_name,segment_type,bytes/1024/1024 MB

      from dba_segments

      where segment_type='TABLE' and segment_name='<yourtablename>';

69) Difference between static listener and dynamic listener?

      1. Static Registration :- A static registration of database to a listener is used when we want to remotely start our database. Static registration is like hardcoding a instance details in listener.ora file. In a static registration an instance is registered with the listener whether its up or not. When a client request comes listener opens a dedicated connection , and server later find out that instance is not up then it gives error message as "Oracle not available". 

      2. Dynamic Registration :- In Dynamic registration , registration is performed by PMON process. Once a Database instance starts, its PMON process registers instance details with associated listener. Dynamic registration does not require any manual configuration in the listener.ora file where as Static configuration does. From Oracle 8i dynamic configuration introduced. 

70) 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.                                                                                                                                                                                               

71) 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.  

72)  What are new memory parameters in Oracle 11g?

       MEMORY_TARGET

73) What are the mandatory background processes?

      DBWR LGWR SMON PMON CKPT RECO.

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

      Server process

75) What background process will writes data to datafiles?

      DBWR

76) What background process will write undo data?

      DBWR

77) What are physical components of Oracle database?

      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.

78) What are logical components of Oracle database?

       Blocks, Extents, Segments, Tablespaces.

79) What is a datafile?

       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.

80) What are the contents of control file?

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

81) What is the use of redo log files?

      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.

82) What is the use of password file?

       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.

83)  How to create password file?

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

84) How many types of indexes are there?

      Clustered and Non-Clustered

      1.B-Tree index

      2.Bitmap index

      3.Unique index

      4.Function based index

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

       Bitmap indexes are preferred in Data warehousing environment.

       Preferred when cardinality is low.

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

       B-tree indexes are preferred in OLTP environment.

       Preferred when cardinality is high.

87) 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.

88) What is the use of root.sh & oraInstRoot.sh?

      Changes ownership & permissions of oraInventory

      Creating oratab file in the /etc directory

      In RAC, starts the clusterware stack