Posted on

Oracle interview questions and answers for freshers


1) You want to upgrade your database to 12c version. Which upgrade script will you make use of?
Starting 12c upgrade catctl.pl can be used for upgrade purpose. This is a new script instead of catupgrd.sql from 12c onwards
2) In 12c upgrade can we still make use of catupgrd.sql?
Yes. It is possible to make use of catupgrd.sql in 12c upgrade with two rules to be taken into consideration :
a) Specify PARALLEL=NO as follows :
@$ORACLE_HOME/rdbms/admin/catupgrd.sql PARALLEL=NO
b) Once this script run completes catuppst.sql script must be run manually
3) Is is necessary to manually run capuppst.sql script after running catctl.pl for upgrade purpose?
No need. Only if catupgrd.sql is used this script needs to be run manually
4) What is the default parallelism while running catctl.pl script?
This script runs with default parallelism of 4. This value can be changed using option -n. If set to 0 this script runs in noparallel aka serial mode. The maximum value of -n can be upto 8
5) You want to collect spool and log files while running catctl.pl script. Which option can be used for this purpose?
Make use of option -l to specify spool and log files
6) You are starting to perform a database upgrade and startup using normal open mode instead of startup upgrade mode. Can you proceed with upgrade?
Nope. For performing database upgrade the startup upgrade option must be made use of. In addition to bringing up database in maintenance mode this mode automatically handles certain system parameter values needed for upgrade. Some other things that gets done in startup upgrade includes :
a) Restricts logon to as sysdba sessions. So, a normal user can’t connect to database by mistake
b) Disables system triggers
c) Performs additional operations that prepares environment for the upgrade
d) Suppresses ORA-00942 error for drop table statements that get generated during upgrade
e) Makes sure only genuine errors are recorded in the logfile
7) When do you make sure upgrade is successful?
Check the upgrade log files and make sure there are no ORA- and PLS- errors to ensure that this is successful upgrade. However, this is first level checking that is done after upgrade
8) After an upgrade completes which scripts will you run to recompile invalid objects?
Once the upgrade completes check the dba_objects view and make sure there are no errors :
select owner, object_name, status from dba_objects where status<>’VALID’;
select owner, object_name, status from dba_objects where status=’INVALID’;
Once you find some invalids run the scripts :
@$ORACLE_HOME/rdbms/admin/utlrp.sql
@$ORACLE_HOME/rdbms/admin/utluiobj.sql
9) What does the oracle release numbers indicate?
When you start handling an environment be it test or production the major question that comes into picture happens to be question about which release the current database is running upon now. Typically this is a five digit number separated by dots
First Number – Major version of the database
Second is the maintenance release number
Third position is applicable only for application server release number
Fourth is the major patch release number
Fifth is the platform-specific release number
10) What happens when we apply a patch set update the PSU to an oracle database?
Application of a PSU changes the last digit (i.e) the fifth digit in the oracle release number
11) You plan to apply a major patch release that changes the fourth digit in an oracle release number. Can you install it on the fly?
Nope. For applying the major patch release say from 12.1.0.1.0 to 12.1.0.2.0 we need to install oracle software in a separate oracle home directory
12) What is an indirect upgrade in an oracle database environment?
It is also called as migration wherein the oracle software versions are not updated as opposed to data being extracted and migrated to latest database version. This is not an easy upgrade method as a direct upgrade using DBUA the graphical tool used for upgrade purpose
13) What is the advantage of indirect upgrade?
One major advantage is that it provides the ability to easily rollback to an older database. The source database is kept intact in this method which in most cases minimizes outage window. In case of failure the rollback process involves just starting up the old database service and listener in case DB was brought down during upgrade making it simple
14) What are the scenarios that demand using migration the indirect upgrade method?
There are some situations that demand the usage of migration like some situations below
a) Used for application testing. Mainly when the database changes are done and application needs to be tested with database this is used
b) Only chosen objects have been changed in database that does not demand full upgrade
c) The database is in very old version that the upgrade path is not supported. This demands data migration
d) The upgrade does not come with outage downtime window
e) The hardware server needs to be changed during upgrade
f) The Operating system needs to be changed during the upgrade
g) We want to minimize the downtime needed for upgrade
15) What are the indirect upgrade methods (or) migration methods supported by oracle database 12c?
Following are some of the migration methods supported by oracle database 12c:
a) Data pump tool can be used for migration. This tool has been introduced from Oracle 10g version. As far as traditional version goes exp/imp tools can be made use of. This is a preferred tool is the server migration, hardware migration is performed (or) versions where direct migration is not supported
b) CREATE TABLE AS SELECT method using database link can be used that creates tables and copies data from source to destination database
c) A major method that helps in reducing downtime happens to be transportable tablespaces a feature of datapump. This demands source database version to be 11.2.0.3 or higher
d) Advanced products like dataguard, oracle golden gate if available can help with online database upgrade
16) What is the issue with using CREATE TABLE AS SELECT method for migration?
One practical issue happens to be no support for datatype like LONG. There are several other restrictions that make this not a preferred method of migration
17) You are performing upgrade using transportable tablespaces. The target platform endianness is different than source platform. What needs to be done to fix this?
Convert datafile using RMAN to perform migration
18) Can you perform database upgrade in archivelog mode with flashback database enabled?
Though it is possible it is not recommended. The first step in upgrade is to bring down the database in shutdown immediate mode, mount the database and disable archive logging using alter database noarchivelog, open it and use it for upgrade purpose. This is a major step to avoid performance issues that can occur during upgrade in archivelog mode
19) Is there an advantage of performing upgrade in archivelog mode with flashback enabled?
Yes. In case of error during upgrade it is possible to rollback database to an earlier state using flashback logs and guaranteed restore point
20) To which location are the DBUA log files and scripts written by default?
By default DBUA log files and scripts are written to $ORACLE_BASE/cfgtoollogs/dbua/db_unique_name/upgradeN directory
21) What is use of upgrade logs?
They can be used to fully understand the upgrade process, look for errors and fix issues in case the DBUA is used for upgrade and screen is stalled during process of upgrade