Oracle DBA Interview Questions PART 5

Redolog, Controlfile Management

1. What is Archived Redo Log?
 Archived Redo Log consists of Redo Log files that have archived before being reused.

2. What is Mirrored on-line Redo Log?
 A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.

3. What is the use of Control File?
 When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

4. What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. Database Name and locations of a database's files and redolog files. Time stamp of database creation.

5. What happens when archive log destination becomes 100% full when the database is running in ARCHIVELOG mode? How do you recover? The database gets shutdown. We should move old archives to different location and startup the database.

6. How do you know whether archive log mode is enabled or not?

 Issue command 'archive log list' at sys prompt

7. What is a stale?

The redolog file which has not been used yet

8. How many maximum control files you can create in Oracle database?

   What is the error number you get if u tries to create more than 8?

8, ora 208            

9. If controlfile crashed, no backup. how to recover?

 By recreating controlfile.

10. How do increase the count of datafiles?

 Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size

Procedure:

1. Open the database

2. Generate the control file change the maxdatafiles

3. Open the db in nomount

4. Execute the syntax with noresetlogs

5. Alter database open

11. If you want to maintain one more archive destination which parameter you have to set?

It’s a dynamic parameter you have to set log_archive_dest_1=

12. How do you take backup of a controlfile?

 Alter database backup controlfille to destination.file will be save in the your destination

13. How do you make your redolog group inactive?

 By taking manual logswitch.alter system switch logfile;

14. What is the parameter allows you to create max no. of groups?

 In controlfile recreation script , maxlogfiles=

15. How to create a trace file?

 “Alter database backup controlfile to trace;”

16. How do you rename a Database?     -Alter System switch log file.

-Alter database backup control file to trace.

-Shutdown.

-Edit control file

   Create control file reuse database 'oldname' resetlogs to create control file set database 'newname' resetlogs

   remove the line stating recover database using backup controlfile.

-change the init.ora file.

-change TNS_names.ora 

17. Can we rename the redologs? If yes, in which stage? In DB up or down?

Yes,

If inactive -------------  open stage

If current -------------- cannot rename in open state                

18. What is a trace file and how is it created?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
19. How to implement the multiple control files for an existing database?  

Shutdown the database
 Copy one of the existing control file to new location
  Edit init.ora file by adding new control filename
  Restart the database.

20. How do you know how much archives are generated?

Using the view v$log_history

Spfile and OMF

1. Where do you get all hidden parameters?

 In the table x$ksppi

Sys>select ksppinm,ksppdesc from x$ksppi;

2. How do you know whether the parameter is dynamic or static?

Check the value from the column ISINSTANCE_MODIFIABLE from V$PARAMETER.

                                                                                                                                               

3. Which parameters are to by set in parameter file to create database for OMF?

db_create_file_dest =

db_create_online_log_dest_1 =

4. Data dictionary views are dynamic or static?

Static

5. Is it possible to change the Instance name after creating database?

Yes

6. If you delete the alert log file what will happen?

New alert log will be created automatically after database startup.

7. What are the different Parameter types?

Text Parameters, Data Parameters

8. Can we start database with pfile when both spfile and pfile present in dbs directory?

Yes, Startup pfile=’$ORACLE_HOME/dbs/init$ORACLE_SID.ora’

9. From which version OMF got introduced?

Oracle9i first introduced a new feature that simplifies tablespace creation. This new feature, Oracle Managed Files (OMF), makes life easier for OracleDBAs by removing the tedium from creating and managing Oracle data files.

10. Disadvantages using OMF?

The major disadvantage of using OMF can be boiled down to:

1. non-human-sensical names

2. Oracle related bugs (*gasp*)

11. What is the difference between the values ‘DEFERRED’ and ‘IMMEDIATE’?                                                      

 DEFERRED means changes reflected from next connecting session .IMMEDIATE means changes reflected to all connected as well as connecting   sessions.

Logical Backups

1. How do you perform Compression with EXPDP?

 EXPDP itself will now compress all metadata written to the dump file and IMPDP will decompress it automatically—no more messing around at the operating system level.

Give Compress=Y in the parameter file of export file.

2. What is the use of INCTYPE option in EXP command?

 Inctype export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into a notion Completes. e. will be written.

3. What is the use of FILE option in IMP command?

The name of the file from which import should be performed.

4. What is the use of FULL option in EXP command?

It is helpful in taking full database export

5. What is the use of GRANT option in EXP command? 

It is a flag to indicate whether grants on databse objects will be exported or not.  Value is 'Y' or 'N'.

6. What is the use of INCTYPE option in EXP command? 

It is a type of export which performs OMPLETE, CUMULATIVE, INCREMENTAL

7. What is the use of TABLES option in EXP command?

tables – Indicates that the type of export is table-mode and lists the tables to be   exported. Table partitions and sub partitions can also be specified.

8. What is the use of ROWS option in EXP command?

Rows – [Y] Indicates whether or not the table rows should be exported at platform. The highest value is 64KB.

9. What are the different kinds of export backups?

 Full back       - Complete database

Incremental          - Only affected tables from last

Incremental date/full backup date.

Cumulative backup   - Only affected table from the last

Cumulative date/full backup date.

10. What is the use of PARFILE option in EXP command?

Par file – The name of the file that contains the export parameter options. This file can be used instead of specifying all the options on the command line for each export.

11. What is the use of INDEXES option in IMP command? 

Indexes – [Y] Determines whether indexes are imported.

12. What is the use of OWNER option in EXP command?  

Owner – Only the owner’s objects will be exported.

13. What is the use of ROWS option in IMP command? 

Rows – [Y] Indicates whether or not the table rows should be imported.

14. What is the use of INDEXES option in EXP command?Indexes – [Y] Determines whether index definitions are exported. The index data is never exported.

15. What is the use of CONSISTENT (Ver 7) option in EXP command?

Consistent – [N] Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

16. What is the use of RECORD LENGTH option in EXP command? Record length – Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. If not specified, this parameter defaults to the value specific to that platform. The highest value is 64KB.

17. What is the use of FILE option in EXP command?

File – The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.

18. What is compress parameter? When u use it?

Compress – When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used. Although this option is only implemented on import, it can only be specified on export.It reduses the size of backup

19. If you have exported full database using EXP utility and while importing can I import only one table from full exported file?Yes

20. How do you perform Compression with EXPDP?

Using compress=y parameter