Performance Tuning Interview Questions and Answers

performance tuning questions and solutions

1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not?
Expected answer: Multiple extents in and of themselves aren't bad. However if you also have chained rows this can hurt performance.

2. How do you set up tablespaces during an Oracle installation?
Expected answer: You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

3. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Expected answer: Ensure that users don't have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Expected answer: Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

5. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Expected answer: Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.

6. What is the fastest query method for a table?
Expected answer: Fetch by rowid

7. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad, how do you correct it?
Expected answer: If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.

9. When should you increase copy latches? What parameters control copy latches?
Expected answer: When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.

10. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
Expected answer: You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio; which should be used for tuning?
Expected answer: Hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.

12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Expected answer: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won't fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

13. When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?
Expected answer: Buffer busy waits may indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.

14. If you see contention for library caches how can you fix it?
Expected answer: Increase the size of the shared pool.

15. If you see statistics that deal with "undo" what are they really talking about?
Expected answer: Rollback segments and associated structures.

16. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?
Expected answer: The SMON process won't automatically coalesce its free space fragments.

17. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
Expected answer: In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';' command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the 'alter tablespace coalesce;' is best. If free space isn't contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.

18. How can you tell if a tablespace has excessive fragmentation?
If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.

19. You see the following on a status report:
redo log space requests 23
redo log space wait time 0
Is this something to worry about? What if redo log space wait time is high? How can you fix this?
Expected answer: Since wait time is zero, no. If wait time was high it might indicate a need for more or larger redo logs. 

20. What can cause a high value for recursive calls? How can this be fixed?
Expected answer: A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.

21. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
Expected answer: This indicates that the shared pool may be too small. Increase the shared pool size.

22. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Expected answer: Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.

23. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
Expected answer: A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.

24. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
Expected answer: A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.

25. In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS 
--------------------- -------------------------- 
R01 11 
R02 8
R03 12
R04 9
SYSTEM 4
You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action?
Expected answer: No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.

26. You see multiple extents in the temporary tablespace. Is this a problem?
Expected answer: As long as they are all the same size this isn't a problem. In fact, it can even improve performance since Oracle won't have to create a new extent when a user needs one. 

How to Enable Archive in Oracle 11g Database

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 4 12:00:26 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Current log sequence           6

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             184549840 bytes
Database Buffers          343932928 bytes
Redo Buffers                5804032 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL>

Best Realtime Oracle DBA Interview Questions with Answers

1) How can you see the Current SCN number of the database?

> Select current_scn from v$database;

2) How can you see the Current log sequence number the logwriter is writing in to?
> Select * from v$log;

3) If you are given a database, how will you know how many datafiles each tablespace contain?
> Select distinct tablespace_name,file_name from dba_data_files;

4). How will you know which temporaray tablepsace is allocated to which user?
> Select temporary_tablespace from dba_users where username=’SCOTT’;

5) If you are given a database,how will you know whether it is locally managed or dictionary managed?
> Select extent_management from dba_tablespaces where tablespace_name=’USERS’;
6) How will you list all the tablespaces and their status in a database?
> Select tablespace_name,status from dba_tablespaces;

7) How will you find the system wide 1) default permanent tablespace, 2) default temporary tablespace 3) Database time zone?
> Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;

8) How will you find the current users who are using temporary tablespace segments?
> V$TEMPSEG_USAGE

9) How will you convert an existing dictionary managed permanent tablespace to temporary tablespace?
> Not possible

10) Is media recovery requird if a tablespace is taken offline immediate?
> Not required

11) How will you convert dictionary managed tablespace to locally managed tablespace?
> Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);

12) If you have given command to make a tablespace offline normal, but its not happening.it is in transactional read-only mode. How will you find which are the transactions which are preventing theconversion?
> By looking at queries using by those SID (u can get script from net). I suspect question is not clear.

13) If you drop a tablespace containing 4 datafiles, how many datafiles will be droped at a time by giving a single drop tablespace command?

> All datafiles

14) If database is not in OMF,How will you drop all the datafiles of a tablespace without dropping the tablespace itself?
> Alter database datafile ‘PATH’ offline drop;

15) How will you convert the locally managed tablespace to dictionay managed?What are the limitations?
> Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);

SYSTEM tablespace should be dictionary

16) Which parameter defines the max number of datafile in database?
> Db_files and MAXDATAFILES in control file

17) Can a single datafile be allocated to two tablespaces?Why?
> No. because segments cannot space multiple datafiles

18) How will you check if a datafile is Autoextinsible?
> Select autoextensible from dba_data_files where file_name=’’;

19) Write command to make all datafiles of a tablespace offline without making the tablspace offline itself?
> Alter database datafile ‘PATH’ offline normal;

20) In 10g, How to allocate more than one temporary tablespace as default temporary tablespace to a single user?
> By using temporary tablespace group

21) What is the relation between db_files and maxdatafiles parameters?
> Both will restrict no of datafiles in the database

22) Is it possible to make tempfiles as read only?
> yes

23) What is the common column between dba_tablespaces and dba_datafiles?
> Tablespace_name

24) Write a query to display the names of all dynamic performance views?
> Select table_name from dictionary where table_name like ‘v$%’;

25) Name the script that needs to be executed to create the data dictionary views after database creation?
> Catalog.sql

26) Grant to the user SCOTT the RESTRICTED SESSION privilege?
SQL> grant restricted session to scott;
Grant succeeded.

27) How are privileged users being authenticated on the database you are currently working on? Which initialization parameter would give me this information?
> Question not clear

28) Which dynamic performance view gives you information about all privileged users who have been granted sysdba or sysoper roles? Query the view?
SQL> desc v$pwfile_users

29) What is the purpose of the DICTIONARY table?
> To know data dictionary and dynamic performance view names

30) Write a query to display the file# and the status of all datafiles that are offline?
> Select file#,status from v$datafile where status=’OFFLINE’;

31) Write the statement to display the size of the System Global Area (SGA)?
> Show parameter sga
Or
> Show sga

32) Obtain the information about the current database? What is its name and creation date?
> Select name,created from v$database;

33) What is the size of the database buffer cache? Which two initialization Parameters are used to determine this value?

> Db_cache_size or db_block_buffers

34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you need to set up Operating System authentication?
> exclusive

35) Which initialization parameter holds this value? What does the shared pool comprise of?
> Library cache and data dictionary cache.
> Parameter : shared_pool_size

36) Which initialization parameter holds the name of the database?
> Db_name

37) Which dynamic performance view displays information about the active transactions in the database? Which view returns session related information?
> V$transaction, v$session

38) Which dynamic performance view is useful for killing user sessions? Which columns of the view will you require to kill a user session? Write the statement to kill any of the currently active sessions in your database?
> V$session (SID, SERAIL#)
> Alter system kill session ‘SID,SERIAL#’;

39) What is the difference between the ALTER SYSTEM and ALTER SESSION commands?
> Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session

40) Write down the mandatory steps that a DBA would need to perform before the CREATE DATABASE command may be used to create a database?
> Create a pfile or spfile
> Create password file
> If windows, create instance using ORADIM utility

41) What does the script utlexcpt.sql create? What is this table used for?

> It will create EXECEPTIONS table. See below link

42) In which Oracle subdirectory are all the SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc…? Located?
> $ORACLE_HOME/rdbms/admin/

43) Which dynamic performance view would you use to display the OPTIMAL size of the rollback segment RBS2. Write a query to retrieve the OPTIMAL size and Rollback segment name?
> V$undostat (but many scripts are available in google or even in my blog)
44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Extent size

45) How would I start the database if only users with the RESTRICTED SESSION privilege need to access it?
> Startup restrict

46) Which data dictionary view would you query to find out information about free extents in your database? Write a query to display a count of the number of free extents in your database?
> We can use scripts. Exactly its difficult to know

47) Write a query to display the tablespace name, datafile name and type of extent management (local or dictionary) from the data dictionary?
> You need to combine dba_data_files and dba_tablespaces

48) Which two types of tablespace cannot be taken offline or dropped?
> SYSTEM and UNDO

49) When a tablespace is offline can it be made read only? Perform the
Required steps to confirm your answer?
> Didn’t got the answer

50) Which parameter specifies the percentage of space in each data block that is reserved for future updates?
> PCTFREE
51) write down two reasons why automatic extent allocation for an extent may fail?
> If the disk space reached max limit
> If autoextend reached maxsize limit

52) Query the DBA_CONSTRAINTS view and display the names of all the constraints that are created on the CUSTOMER table?
> Select constraint_name from dba_constraints where table_name=’CUSTOMER’;

53) Write a command to display the names of all BITMAP indexes created in the database?
> Select index_name from dba_indexes where index_type=’BITMAP’;

54) Write a command to coalesce the extents of any index of your choice?
> Alter tablespace <tablespace_name> coalesce;
> Don’t know for extents

55) . What happens to a row that is bigger than a single block? What is this called? Which data dictionary view can be queried to obtain information about such blocks?
> Row will be chained into multiple blocks. CHAINED_ROWS is the view

56) Write a query to retrieve the employee number and ROWIDs of all rows that belong to the EMP table belonging to user SCOTT?
> Select rowid,empno from scott.emp;

57) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Repeated question

58) How to compile a view? How to compile a table?
> Alter view <view_name> compile;
> Tables cannot be compiled

59) What is the block size of your database and how do you see it?
> Db_block_size

60) At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.?
> We can recover it from alert log file which contains non-default values

61) You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
> By configuring backup retention policy to redundancy 3

Oracle 11g DBA Interview Questions with Answers

Oracle DBA Interview Questions/FAQs Part1

1. What is an instance?
SGA + background processes.

2. What is SGA?
System/Shared Global Area.

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

4. What are new memory parameters in Oracle 10g?
SGA_TARGET
PGA_TARGET

5. What are new memory parameters in Oracle 11g?
MEMORY_TARGET

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

7. What are the optional background processes?
ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.

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

10. What are the new features in Oracle 10g?

11. What are the new features in Oracle 11g?

12. What are the new features in Oracle 11g R2?

13. What are the new features in Oracle 12c?

14. What process will get data from datafiles to DB cache?
Server process

15. What background process will writes data to datafiles?
DBWR

16. What background process will write undo data?
DBWR

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

18. What are logical components of Oracle database?
Blocks, Extents, Segments, Tablespaces.

19. What is segment space management?
LMTS and DMTS.

20. What is extent management?
Auto and Manual.

21. What are the differences between LMTS and DMTS?
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

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

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

23. What is the use of redo log files?

24. What are the uses of undo tablespace or redo segments?

25. How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;

26. What is ORA-01555 – snapshot too old error and how do you avoid it?

27. What is the use/size of temporary tablespace?

28. What is the use of password file?

29. How to create password file?
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

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

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

31. What is bitmap index & when it’ll be used?
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.

32. What is B-tree index & when it’ll be used?
B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.

33. How you will find out fragmentation of index?
AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.

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

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

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

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

36. What is the difference between schema and user?
Schema is collection of user’s objects.

37. What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK

SYSASM can do anything SYSDBA can do.

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

39. How to improve sqlldr (SQL*Loader) performance?

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

41. What are materialized view refresh types and which is default?
Complete, fast, force(default)

42. How fast refresh happens?

43. How to find out when was a materialized view refreshed?
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;

44. What is materialized view log (type)?

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

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

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

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

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

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

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

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

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

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

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

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

53. What UNIX parameters you will set while Oracle installation?
shmmax, shmmni, shmall, sem,

54. What is the use of inittrans and maxtrans in table definition?

55. What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.

56. What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.

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

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

59. What are the entries/location of oraInst.loc?
/etc/oraInst.loc is pointer to central/local Oracle Inventory.

60. What is the difference between central/global inventory and local inventory?

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

62. What is transportable tablespace (and across platforms)?

63. How can you transport tablespaces across platforms with different endian formats?
Ans:
RMAN

64. What is xtss (cross platform transportable tablespace)?

65. What is the difference between restore point & guaranteed restore point?

66. What is the difference between 10g/11g OEM Grid control and 12c Cloud control?

67. What are the components of Grid control?
Ans:
OMS (Oracle Management Server)
OMR (Oracle Management Repository)
OEM Agent

68. What are the new features of 12c Cloud control?

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

means you are on 64 bit oracle.

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

70. How to find opatch Version ?
Ans:
opatch is utility to apply database patch, In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”

LOSS OF ALL CONTROLFILES (NO CATALOG)

SQL> insert into myobjects select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328   >>>> need to check this record count after recovery

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     5

Next log sequence to archive   7

Current log sequence           7

Note - current log sequence is 7 - not archived but contains the last committed changes that we made

Note - archive logs will not be found in $ARCV area, but in the flashback location

Simulate a failure

SQL> select name from v$controlfile;

NAME

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

/u01/ORACLE/testdb/control01.ctl

/u01/ORACLE/testdb/control02.ctl

/u01/ORACLE/testdb/control03.ctl

SQL> !rm /u01/ORACLE/testdb/*.ctl

SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  893386752 bytes

Fixed Size                  2076816 bytes

Variable Size             432017264 bytes

Database Buffers          452984832 bytes

Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

Restore the controlfile

RMAN> run {

2> restore controlfile from autobackup;

3> }

Starting restore at 18-SEP-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_area

database name (or database unique name) used for search: TESTDB

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/ORACLE/testdb/control01.ctl

output filename=/u01/ORACLE/testdb/control02.ctl

output filename=/u01/ORACLE/testdb/control03.ctl

Finished restore at 18-SEP-07

Mount and recover the database

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07

Starting implicit crosscheck backup at 18-SEP-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07

using channel ORA_DISK_1

Finished implicit crosscheck copy at 18-SEP-07

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

using channel ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc

archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log

archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6

archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied

media recovery complete, elapsed time: 00:00:09

Finished recover at 18-SEP-07

SQL> alter database open resetlogs;

Database altered.

 conn scott/tiger

Connected.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328

How to recover corrupted UNDO tablespace in Oracle 10g Database

How to recover corrupted UNDO tablespace

the following error reported in the alert.log file

Sun Jul 31 09:27:35 2011

Flush retried for xcb 0x333b8e78, pmd 0x32898888

Doing block recovery for file 2 block 330

Block recovery from logseq 1462, block 70 to scn 42078052

Sun Jul 31 09:27:36 2011

Recovery of Online Redo Log: Thread 1 Group 3 Seq 1462 Reading mem 0

  Mem# 0 errs 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

Block recovery completed at rba 1462.408.16, scn 0.42078054

Sun Jul 31 09:27:38 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:41 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:45 2011

DEBUG: Replaying xcb 0x333ad298, pmd 0x33bc9e54 for failed op 8

Doing block recovery for file 2 block 227

Sun Jul 31 09:27:45 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

No block recovery was needed

Sun Jul 31 09:27:49 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

PMON: terminating instance due to error 472

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j009_1608.trc:

ORA-00472: PMON  process terminated with error

Kindly use the following solution if you encountered with the exact above situation.

Step 1

--------

SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;

SQL> Shutdown immediate;

Step 2

-------

Modify the corrupt.ora and set Undo_managment=Manual

SQL> Startup mount pfile='/tmp/corrupt.ora'

SQL> Show parameter undo

it should show manual

SQL> Alter database open ;

If it comes up

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles

Step 3

-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace= scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported

Live Example:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 31 10:52:25 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> create pfile = 'c:\corrupt.ora' from spfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount pfile='c:\corrupt.ora';

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> create rollback segment r01;

Rollback segment created.

SQL> alter rollback segment r01 online;

Rollback segment altered.

SQL> create undo tablespace undotbs01 datafile 'C:\oracle\product\10.2.0\oradata

\ORCL\undotbs001.dbf' size 100m;

Tablespace created.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> alter system set undo_tablespace=undotbs01 scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL>