Author: learnersreference

1) Is oracle user password encrypted by default?
Oracle user passwords aren’t encrypted by default.This is a security threat. This becomes an issue while the password gets transferred across the client-server network. Setting one parameter in the client side and one parameter in the server side we can ensure that Oracle always encrypts a password when it send it across a network.
Client Side : ora_encrypt_login=true
Server Side : dblink_encrypt_login=true
By setting these values, DES – data Encryption Standard will be used for encryption
2) Which oracle database user can make use of datapump utility?
All Oracle users can use the Data Pump utility by default. They can perform basic tasks using datapump utility. Datapump has grown beyond the purpose of performing logical backup all the way to support upgrade of databases, refresh of UAT, test environment databases after major or minor production releases, upgrades, critical bug fix implementation. The transportable tablespace a popular datapump feature is extensively used for database refresh by performing export and import of tablespace as a whole
3) What is needed for a user to perform advanced datapump tasks in an oracle database?
To perform advanced tasks user needs the special privileges EXP_FULL_DATABASE and IMP_FULL_DATABASE. Granting of the roles mentioned above makes user a privileged user and helps user perform the following tasks:
1) Export and Import database objects owned by any user
2) Attach to and modify jobs started by another user
3) Use the all new remapping capabilities during a Datapump Import job – This can be done at schema using remap_schema option (or) at tablespace level using remap_tablespace
4) Is oracle database recovery manual or automated while making use of RMAN?
RMAN automates the entire recovery process in an oracle database. It basically involves the usage of the following two commands :
1) RESTORE command– restores entire database, tablespace, single data file, control files, archived redo logs, SPFILE from RMAN backup set or from image copy on disk
2) RECOVER Command – This will perform the actual media recovery by applying necessary archived redo logs or incremental backups
5) When are traditional point-in-time recovery techniques used? What was the disadvantage of this procedure?
Traditional point-in-time recovery techniques are used in case of logical corruption. The process involves restore datafile backup copies and then use archived logs to advance the database forward
This used to be time-consuming and cubersome. As a fix flashback database feature got introduced in latest version of oracle databases
There is an interesting RMAN feature in latest version of oracle database called as block change tracking that lets perform block level restore and recovery in case of block corruptions with little to no downtime
6) What are the steps involved in configuring flashback database?
a) Check if database is in archivelog mode using sql command archive log list
First check if database has archivelog enabled as follows:
archive log list;
The above command should return enabled. If not turn on archivelog mode if as follows :
shutdown immediate;
startup mount;
alter database archivelog;
startup open;
Check and make sure archive logging is enabled as follows:
archive log list;
b) As a next step set up a flash recovery area
Set DB_FLASHBACK_RETENTION_TARGET to specify how far back we can flashback our database. The value specified is in minutes
alter system set db_flashback_retention_target = 1440; (1day=1440 minutes)
Shutdown and restart the database in mount mode
shutdown immediate;
startup mount;
Enable flashback database feature while database is in mount mode
alter database flashback on;
Startup open;
c) Check and make sure flashback feature is configured by issuing the following commands
SQL>select flashback_on from v$database;
The output should return YES
7) Give details on dirty-read problem in an Oracle database :-
Dirty-Read Problem occurs when a transaction reads data that has been updated by an ongoing transaction but has not been committed permanently
For example, transaction A has just updated the value of a column and it is now read by transaction B
What if transaction A rollback the changes, intentionally or because it aborts for some reason? The value of the updated column will also be rolled back as a result
But, transaction B has already read the new value of the column, which is now incorrect because of rolling back of transaction A
8) What privilege is needed to access DB console web application in an oracle database?
DB Console is the free web based application shipped for free since Oracle database 10g. From Oracle 11g onwards database db console web application is shipped for free.
SELECT ANY DICTIONARY Privilege is needed to access DB Console in Oracle Database
9) What is automatic shared memory management? How is this achieved?
Automatic shared memory management is the new feature from oracle database 10g onwards. It is set by setting the parameter MEMORY_TARGET in initialization parameter file. It is possible to disable automatic shared segment management by setting the value of MEMORY_TARGET parameter to zero
SQL> alter system set memory_target=0 scope=memory;
System altered.
Scope has been specified as memory. It can be set to SPFILE, BOTH, MEMORY
10) What does a Flashback Versions Query do in an Oracle database?
Allows us to view all the versions of the same row over a period of time so that we can undo logical errors
It can also provide an audit history of changes, effectively allowing us to compare present data against historical data without performing any DML activity
Restores a table to a point in time or to a specified SCN without restoring data files
This feature uses DML changes to undo the changes in a table
The flashback table feature relies on undo data
11) What are many different types of recovery processes in an oracle database environment?
Oracle Recovery Process comprises of :
1) Crash and Instance Recovery
2) Media Recovery
Oracle automatically performs recovery when a single instance suddenly fails, or when all the instances of a RAC database fails
When an instance is shutdown with SHUTDOWN ABORT command, we need to perform a crash recovery
Instance recovery is same as crash recovery. It is the process in which surviving instances bring back the crashed instances in a RAC environment
Media recovery isn’t automatic. DBA has to initiate the recovery process
RMAN – used to perform block-media recovery
13) When does the error ORA-00903: invalid table name occur? How will you fix it?
Oracle object names are case sensitive. If we try creating a table with name specified in single quotes it throws this error.
SQL> create table ‘singlequote’ (test date);
create table ‘singlequote’ (test date)
*
ERROR at line 1:
ORA-00903: invalid table name
This can be resolved by specifying table name inside double quote
SQL> create table “doublequote” (test date);
Table created.
14) What are the procedures that you need to execute after applying patch in an Oracle environment? Oracle patches are binary files that make changes to Oracle home, including dicitonary views. Hence it is a good practice to always run statistics collection against system objects after patch application. Follow these simple steps as stand alone or anonymous block to gather statistics after patch application
Standalone Steps For executing the statistics collection procedures that can be run one by one
execute DBMS_STATS.GATHER_SYSTEM_STATS;
execute DBMS_STATS.GATHER_DICTIONARY_STATS;
execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Anonymous Block that can include all the three statistics collection procedure and can be run as script as well:
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS;
DBMS_STATS.GATHER_DICTIONARY_STATS;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
15) What is the use of oradim utility? Is there a Linux equivalent of oradim utility?
oradim utility can be used in windows OS to perform a variety of tasks. There is no oradim equivalent utility for Linux/UNIX enironment
To startup an Oracle Service(instance) issue:
oradim -startup -sid sidname
To shutdown a service issue:
oradim -shutdown -sid sidname
If the following error occurs follow the steps below:
ORA-01017: invalid username/password; logon denied
oradim -shutdown -sid sisdname -syspwd sysuserpassword -shuttype srvc,instance -shutmode abort normal immediate
16) How does an undo tablespace size correspond to flashback database feature if one has been implemented?
Undo tablespace should be able to accommodate all the long-running transactions
It has to be big enough to accommodate flashback features we may implement in the database as oracle’s flashback lets us undo changes to the data at various levels
Some flashback features that utilize undo data are – Flashback query, flashback versions query, flashback table
17) How is sizing of individual undo segments done?
We should decide the size of the undo tablespace and the setting for the UNDO_RETENTION initialization parameter. Oracle will do the sizing of the individual undo segments
18) What is the use of Undo advisor present in OEM?
Undo advisor through OEM can be used to figure out the ideal size of undo tablespace and ideal duration to be specified for UNDO_RETENTION parameter. Using the current undo space consumption statistics, we can estimate future undo generation rates for the instance
19) Can you get details on database version as a normal user without SYSDBA privilege?
Database version is the first thing to determine any issue in case of issue investigation. If the DBA is not allowed to have details on SYS user password they can still get details with their normal user privilege
It is possible to identify the version of the oracle database instance when we are connected as normal user and SYS user with SYSDBA privilege. Here is a simple demonstration. Initially I’ve connected as SYS user with SYSDBA privilege.
SQL> select * from v$version;
20) How do I determine if a tablespace is unavailable (or) Dropped?
There are some core database dictionary objects that show detials on status of a tablespace. One such object is the ts$
create tablespace test datafile ‘d:oracledatabase’ 200M autoextend on;
SQL> select ts#,name,online$ from ts$ where name=’TEST’;
TS# NAME ONLINE$
———- —————————— ———-
14 TEST 1
SQL> drop tablespace test including contents;
Tablespace dropped.
SQL> select ts#,name,online$ from ts$ where name=’TEST’;
TS# NAME ONLINE$
———- —————————— ———-
14 TEST 3

21) What is advantage Of Using Direct-Path Loading Method Over Conventional Data Load?
SQL*Loader performs data loading using two methods:
1) Conventional loading
2) Direct-path loading
Conventional loading method used SQL INSERT statement to insert data into the tables one bind array size at a time.The direct-path loading option doesn’t use the SQL INSERT statement to put data into tables. It formats the Oracle datablocks and writes them directly to the database files.
This direct-write process eliminates much of the overhead involved in executing SQL statements to load tables.So it loads data much faster than the conventional loading method.For large data-loads direct-path loading method is the default loading method because of the speed constraints associated with the conventional loading method
22) What is the user-managed recovery procedure to recover control file in an oracle database?
User-Managed procedures can be followed to recover a control file.If we’ve lost all the control file, we can create a brand new control file using CREATE CONTROLFILE command
We can make use of control file created in trace
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE; – creates a file in udump directory
SQL> STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE “dbname” NORESETLOGS ARCHIVELOG
SQL> RECOVER DATABASE;
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
SQL> ALTER DATABASE OPEN;
REUSE option asks Oracle to overwrite any of the old control files if they exist in their default location
23) Give details on many different datapump files :-
Data Pump uses the following files for its operation :
Dump Files :
These hold the table data as well as the metadata that’s being loaded or unloaded
Log Files :
These are the standard files for logging the messages and results of Data Pump Operation
SQL Files :
Data Pump Import uses a special parameter called SQLFILE, which will write all the DDL statement it will execute during the job to a file
Data Pump doesn’t actually execute the statements but write the DDL statements to the file specified by the SQLFILE parameter.
All log files, SQLFILE will overwrite any existing file with the same file name
If an older dump file of the same name already exists we’ll get an error
24) How to perform log sequence based incomplete recovery?
In some cases we see gap in archived redo logs (i.e) archived redo logs are available only until a particular log sequence number. In such cases log based recovery comes handy
In SQL Prompt ,
SQL> startup mount pfile=’pfile location’;
In RMAN Prompt
rman target sys/syspassword@oracle_sid
Log onto RMAN prompt and make sure database is in mount mode
restore database until sequence sequencenmber+1 thread thread-number;
This is an incomplete recovery and hence open database in SQL prompt:
alter database open resetlogs;
Note that if we restore and recover upto log sequence number 99 we specify value as 100
25) How to move controlfiles across disks?
Moving Control Files across disks happens when the company’s high availability policy demands the control files to be placed in different disks.Follow the steps to move the control files.
SQL>show parameter control_files; – list showing control file location.
SQL>shutdown immediate; – instance down
SQL> host mv oldfile new-file; move the files
SQL> startup nomount; – Nomount state to modify the initSID.ora/spfileSID.ora file
SQL>alter system set control_files=new-file; rename the parameter in parameter files.
SQL> startup force; – start the instance
SQL> show parameter control_files; – check the new location values
26) Is table automatically analyzed upon index creation?
Database statistics play a crucial role in determining the performance of the database. Oracle database has many inbuilt tool that will let us analyze the database objects. The most important database object used to store information is the table. An index is based on one or more column of a table.
Whenever a table is created it is not ANALYZED. When an index is created it is automatically ANALYZED.
SQL> create table test_anal (c1 date);
Table created.
SQL> select num_rows,last_analyzed from dba_tables where table_name=’TEST_ANAL’;
NUM_ROWS LAST_ANAL
———- ———
Query the dba_tables data dictionary view to make sure that no rows have been analyzed upon table creation.
We’ll insert few rows into the table and see if it is analyzed.
SQL> insert into test_anal select created from dba_users;
36 rows created.
SQL> select num_rows,last_analyzed from dba_tables where table_name=’TEST_ANAL’;
NUM_ROWS LAST_ANAL
———- ———
We see that no rows have been analyzed upon insertion into the table.Now lets look at how an index is created and its rows are automatically analyzed upon creation.
SQL> create index test_anal_index on test_anal(c1);
Index created.
SQL> select distinct_keys,last_analyzed from dba_indexes where index_name=’TEST_ANAL_INDEX’;
DISTINCT_KEYS LAST_ANAL
————- ———
24 26-NOV-18
We see that an index has been created and automatically analyzed upon creation Num_rows
27) How will you resize a tablespace in an Oracle environment?
Tablespace is a logical data structure in Oracle database management system. Tablespace is composed of one or more data files. A datafile can belong to only one tablespace.We can increase or decrease the size of tablespace by increasing or decreasing the size of datafile using RESIZE option
28) What Happens When An Oracle Transaction Is Committed?
The transaction tables in the redo records are tagged with the unique system change number (SCN) of the committed transaction
The log writer writes the redo log information for the transaction from the redo log buffer to the redo log files on the disk, along with the transaction’s SCN.
This is the point at which commit is considered complete in Oracle
Any lock that Oracle holds are released, and Oracle marks the transaction as complete
29) What to do while Access is denied error happens during creating multiple copies of controlfile for multiplexing control files?
Follow the correct set of steps to fix this issue
1) Show parameter control_files
2) shutdown immediate;
3) host copy source destination
4) startup nomount;
So, be careful on steps followed and get it done. The error typically happens when we try to perform the copy of controlfile in nomount mode
30) What is the use of v$flashback_database_log view and how this is associated with DB_RECOVERY_FILE_DEST_SIZE initialization parameter?
The important initialization parameter to be initialized while configuring flashback database in oracle database is DB_RECOVERY_FILE_DEST_SIZE. After setting the value it is possible to verify it using show parameter command.
Also v$views such as v$flashback_database_log, v$parameter can be used to obtain details about this initialization parameter.
SQL>  select flashback_size from v$flashback_database_log;
FLASHBACK_SIZE
————–
24133632
SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest_size           big integer 3G
31) What is database troubleshooting?
The primary job role of DBA administering many different flavors of databases, different types of databases say from relational databases like DB2, oracle, sql server, mysql to most recent mongodb, couchdb, cassandra , HBase,nosql databases would be to troubleshoot the issues that arise as part of database systems in place
Database troubleshooting can involve any level of issue fixes that arise in the database environment. This can involve taking a quick look at database connection issues, network issues, fixing corruption to complex tasks involving performance tuning of databases. Here are some common database troubleshooting tasks that a DBA does. You can share this in detail to crack your next DBA interview
1) Connectivity Issues – Now-a-days databases have been migrated from data center to cloud infrastructure. Expect this common interview question. When you find network connectivity issue perform some steps to investigate and fix the issues
Look for database availability – Database must be up and running for proper connectivity. If database is down bring it back. If issue still persists, look at alert.log in trace folder to determine root cause of down situation and take it from there on
Look for listener availability – Listener service should be up and running to allow clients to connect to database. TNS listener error is recorded in alert.log file. If this service is down start listener service
Look for network issue between database and web/application server – Typically applications that are web based work on HTTP request response architectural implementation. When a webpage is requested web server contacts database to pull needed data for rendering back to end user. If the web page is blank, look at network connectivity issue between database and web/application server. This is very common in cloud implementations
Tools Used : In case of oracle tnsping can be used to determine connectivity
lsnrctl status – Used to determine listener status
ping command from database to webserver and vice versa will help us determine network issues
Fiddler Capture – This is a tool that can be run to monitor step by step taken by an application to access, render information
2) System performance issues – Make use of OS utilities like sar, top, cpustat, memstat, top to look for resource consumption at OS level. In case of oracle database awrrpt.sql, addmrpt.sql, asjrpt.sql are the tools that come real handy to diagnose and fix the issues
3) Data Dictionary Views – Oracle comes with pre-defined set of tables and views that are stored in SYS schema. They store metadata information about database, current database operations etc. They come handy to fix database issues
4) Lock issues – Oracle is a DBMS based on ACID principle. Locks are established for data consistency and concurrency. At times they can be problematic causing slowness, inappropriate waits etc. Look and fix lock issues if needed
5) Cursour issues – Cursors form main focal point while investigating performance issues
6) Sizing of temporary and undo tablespaces – TEMP tablespace should be sized appropriately as they are used for sorting operations. UNDO tablespace store before image and sizing of undo tablespace, proper setting of undo_retention parameter helps in fixing many database issues
32) How can you get details of undo tablespaces?
Undo tablespaces created in the oracle database and in use can be queried from the data dictionary view dba_tablespaces. Similar to table segments, undo tablespaces are created and they are formed on undo segments. Here is the query to get list of undo tablespaces created in the database, undo tablespace in use
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
TABLESPACE_NAME
——————————
UNDOTBS1
SQL> select value from v$parameter where name=’undo_tablespace’;
VALUE
—————————————————————————
UNDOTBS1
33) What is the use of undo_tablespace initialization parameter?
UNDO_TABLESPACE is the initialization parameter the defines what is the undo tablespace in use. Hence it is possible to query the v$parameter dynamic performance view to get details on current undo_Tablespcae. Show parameter undo_tablespace is going to fetch the same result
SQL> show parameter undo_tablespace;
NAME                                 TYPE        VALUE
———————————— ———– ———–
undo_tablespace                      string      UNDOTBS1
34) What is an oracle instance formed of?
An oracle instance is formed of two major components :
1) Background processes – the set of operating system processes that support the start, running, management of instances
2) Memory structures – the RAM structures that are allocated for many different database operations

35) From where can you get details about an oracle instance?
We can get information about the oracle instance by determining the current size of various memory structures that make up the instance.Clients such as SQL*PLUS, SQL Developer, TOAD etc can be used to obtain this information
1) Connect to database as privileged user SYSTEM
2) System global area, SGA as it is popularly called is a major component of memory structure
36) How to get details about SGA and PGA in an oracle instance?
Determine the current size, minimum size, maximum size of SGA. Components of SGA can be dynamically resized.
SQL> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE from v$sga_dynamic_components;
3) Next major component is Program Global Area(PGA).
SQL> select name,value from v$pgastat where name in(‘maximum PGA allocated’,’total PGA allocated’);
37) What is the significance of COMMIT_WRITE Initialization Parameter?
Default behavior is to have a disk I/O after each COMMIT. This causes a slight delay in finishing the transaction
We may avoid the delay resulting from frequent writing of redo log records and waiting for the confirmation of those writes by setting the COMMIT_WRITE to BATCH,NOWAIT
This parameter can be set at SYSTEM or SESSION level
COMMIT_WRITE = BATCH,NOWAIT
ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT
38) How will you format the sqlplus output?
When we query certain tables, views and such database objects, we get the output in an unformatted fashion. In Oracle it is possible to format the sql output. We can format the column to be displayed. We can set size of columns.
Syntax – column column-name format a(xx); – where xx – can take values like 1,2,3,4…
Here are the set of commands to format the output.
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
—————————————————————- ————
shared pool 327155712
large pool 8388608
java pool 8388608
streams pool 0
DEFAULT buffer cache 452984832
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE
—————————————————————- ————
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
We can format the column values and set it accordingly.
SQL> column component format a30;
SQL> column current_size format 9999999999;
SQL> select component,current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
—————————— ————
shared pool 327155712
large pool 8388608
java pool 8388608
streams pool 0
DEFAULT buffer cache 452984832
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE
—————————— ————
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
39) What is the advantage of compressing a backup?
When backup sets are compressed :
1) Number of backup pieces produced will be less
2) Space needed by backupset  gets reuced (by atleast 50% to 75%)
What is the advantage of ZLIB over bzip2?
BZIP2 – Traditional algorithm which uses most of the CPU
ZLIB – CPU usage not a problem while using this algorithm as it uses less CPU time
40) What is the command used for enabling compression in versions prior to Oracle database?
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

41) What causes rman-06171: not connected to target database? How to fix this error?
This error happens in case the target database which is being backed up is unavailable, connect string to connect to the target database is not set correctly, oracle networking is not set properly, oracle_sid is not mentioned correctly etc. As a first step to fix the issue see if the target database is available by connecting directly via sqlplus, make use of correct connect string within rman prompt
connect target /
42) You are upgrading your database using DBUA and happen to stop DBUA inbetween? Can you proceed further with upgrade?
If the DBUA is stopped during the upgrade process, manual upgrade procedure steps need to be followed to complete the upgrade. If there is a need to make use of DBUA then the original database needs to be restored to proceed further with upgrade
43) How do you set the compression algorithm to be used in Oracle database?
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘ZLIB’;
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BZIP2’;
44) What is need to compress data in oracle database and how is this being done?
Storage is always a costly entity. Saving space is very important as it saves cost for an organization. Oracle Corporation has come out with its properitory backup solution RMAN (Recovery Manager). RMAN has a feature to enable backup compression. The way backup is compressed is different in Oracle database 11g than in previous versions
45) You are getting error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. How will you fix this issue using oracle initialization parameter?
Oracle database has an interesting initialization parameter DDL_LOCK_TIMEOUT set at session level and system level. Whenever a DDL statement like ALTER is issued against a table a DDL lock is acquired in that object. If we try to perform any operation against that object, it results in an error.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
To avoid such errors, we can set initialization parameters DDL_LOCK_TIMEOUT which performs automatic retries within the time. Value specified is in seconds
We can set DDL_LOCK_TIMEOUT at session level
SQL> alter session set ddl_lock_timeout=60;
Session altered.
It is possible to set the values at the database level.
SQL> alter system set ddl_lock_timeout=50;
System altered

46) ITIL SLA(Service Level Agreement) demands recovery of most systems to be done in minutes. What is the best way to achieve this in an Oracle database environment?
Oracle Database RECOVER a tablespace using RMAN is a easy way of recovering a single tablespace or a list of tablespaces. We can perform recovery in a OPEN database. We don’t have to shutdown the database.If needed we can bring down database to mount mode
1) Bring the tablespace offline. Rest of the database function will not be affected
RMAN> Alter tablespace tablespace_name OFFLINE;
2) Restore the tablespace as follows :
RMAN> RESTORE TABLESPACE tablespace_name;
3) Recover the tablespace
RMAN> RECOVER tablespace tablespace_name;
4) Recovered tablespace can be brought online :
RMAN> Alter tablespace tablespace_name ONLINE;
47) What is an initialization parameter file? What is its significance in an oracle database environment?
Initialization parameter file is the file holding initialization parameters. Initialization parameters are also called as instance configuration parameters. Initialization parameter files are system location specific. Depends on OS:
LINUX/UNIX – $ORACLE_HOME/dbs
WINDOWS – $ORACLE_HOME%database
There are two types of initialization parameter file – SPFILE and text initialization parameter file(init.ora)
Oracle Database locates the initialization parameter file by examining the filenames in the following order:
1) SPFILE$ORACLE_SID.ora
2) spfile.ora
3) init$ORACLE_SID.ora (text initialization parameter file
48) Give details on oracle client in an oracle environment :-
Install oracle client software at client machines.Download client software from oracle website.
Versions of oracle server and oracle client need not be compatible.Oracle recommends the usage of compatible server and client versions
49) Can you create a database tablename with spaces in them? Is it possible to perform DML operations against this table?
It is possible to create a table the essentially important basic database object name with spaces in them. Specify them inside double quotes
SQL>  create table “with space sample”(“-Hyphen” date);
Table created.
SQL> insert into “with space sample” values(sysdate);
1 row created.
SQL> select * from with space sample;
select * from with space sample
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from “with space sample”;
-Hyphen
This produced the correct result if specified within double quotes. It is also interesting to know that DML (Data Manipulation language) operations such as INSERT,UPDATE,DELETE can be performed against these objects. Names must be specified within double quotes.
———
30-DEC-18
50) What is a database resource manager?
Database resource manager allows us to create resource plans,which specify how much of our resources should go to various consumer groups.We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.
51) What are the uses of a database resource manager?
1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.
2) Using the database resource manager we can set initial login priorities for various consumer groups
3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete
52) What are the four elements of a Database resource Manager?
Database resource manager is composed of the following four elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.
53) What is a resource consumer group?
A resource consumer group is used to group together similar users based on their resource needs.
54) What is a resource plan?
The resource plan lays o how resource consumer groups are allocated resources. Each resource plan contains a set of resource consumer groups that belong to this plan, together with instructions as to how resources are to be allocated among these groups.
For instance,a resource plan may dictate CPU resources be allocated among three resource consumer groups so that the first group gets 60 percent and the remaining two groups get 20 percent each of the total CPU time.
55) What is a subplan?
A subplan is a part of the resource plan that enables the allocation of resources in greater detailamong the resource consumer groups.
56) What is a resource allocation method?
The resource allocation method dictates the specific method we choose to use to allocate resources like the CPU.The available methods of allocating the database resources are CPU method, Idle time, Execution time limit,Undo pool,Active session pool,automatic consumer group switching,cancelling SQL and terminating sessions,parallel degree limit.
57) What is a CPU method?
Oracle uses multiple levels of CPU allocation to prioritize and allocate CPU usage among the competing user sessions.It is a type of resource allocation method.
58) What is an idle time?
It is a type of resource allocation method.We can direct that a user’s session be terminated after it has been idle for a specified period of time.We can also specify that only idle sessions blocking other sessions be terminated.
59) What is an execution time limit?
It is a type of resource allocation method.We can control resource usage by setting a limit on the maximum execution time of an operation.
60) What is an undo pool?
It is a type of resource allocation method.By setting an undo pool directive,we can limit the total amount of undos that can be generated by a consumer resource group.

61) From which v$ view can you get details on SCN in an oracle database?
Oracle database has many different dynamic performance views.One interesting view is the v$database which provides information on SCN (System Change Number).Whenever a change is made to the database, the change in recorded in redo log buffer in memory. Upon a commit the information is written to online redo log files. This process usually called the database checkpoint.
When the information is written a unique number called SCN (system change number) is associated with this in online redo log file. Information can be obtained from v$database view as follows
SQL> select current_scn from v$database;
CURRENT_SCN
———–
436843703
Now issue a DDL statement. DDL’s have implicit commit
SQL> alter table new modify id varchar(1000);
Table altered.
SQL> select current_scn from v$database;
CURRENT_SCN
———–
436843728
62) Do we need to set oracle_home before using oracle vault?
YEs. This is mandate. Oracle Database Vault is the security solution from Oracle Corporation. It is necessary to set the value of ORACLE_HOME before enabling oracle database vault
C:Usersusername>dvca -action enable
ORACLE_HOME not set. Set ORACLE_HOME and retry
C:Usersusername>set ORACLE_HOME=location-name
C:Usersusername>dvca -action enable
63) What is ASM?
ASM is the Automated storage manager. Originally file system management was taken care of at OS level. Oracle has come up with Oracle Automatic Storage Manager that makes storage management possible using Oracle ASM instance
64) What are the type of partitions?
Partitions can be broadly classified into three major categories
1) List partition
2) Range patition
3) Hash partition
65) What is Grid Computing?
Management of the organization’s infrastructure from a single console is called as grid computing. Oracle uses Oracle Enterprise Manager for Grid computing.
66) Purpose of OEM Grid Control:
The purpose of the OEM Grid Control is to facilitate the management of entire systems, including hosts, databases, web servers, listeners and other services.
It provides us with a powerful and convenient centralized means of managing our entire infrastructure, not just our oracle databases.We can manage our systems from just about anywhere including from mobile devices
67) How to address ORA-02270: no matching unique or primary key for this column-list?
I created two tables and tried creating a foreign key constraint. I didn’t specify any key as primary key in parent table. I modified a column as primary key in parent table and created a foreign key with this primary key.This fixed the issue.
SQL> create table dept(manager_id int, manager varchar(20));
Table created.
SQL> create table employee(emp_id int, employee_name varchar2(20), salary int);
Table created.
SQL> alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id);
alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
SQL> alter table employee modify emp_id primary key;
Table altered.
SQL> alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id);
Table altered.
68) How to Monitor Flashback Database?
Flashback database in oracle database 11g can be configured using steps mentioned in previous post. It is noted that flashback database can be in YES|NO status.

69) How do you maintain more than one copy of archived redo logs?
This can be done in many different ways:
1) Setup jobs to copy the archived redo logs at scheduled intervals
2) Manually copy the logs
3) Setup dataguard the oracle product that does log transport
These logs can be copied onto different disk drive within same server or different server
70) How will you change Flash Recovery Area Size Dynamically?
Flashrecovery area needs to be configured if we want to backup database using RMAN(Oracle Recovery Manager) backup/restore utility of oracle database. It is possible to change the flash recovery area size dynamically.
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size = 3G;
System altered.
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest_size big integer 3G
71) What is the use of SQL.BSQ Script?
SQL.BSQ Script is run to create base tables,roles at the database creation time.sql.bsq creates the following roles – connect,resource,dba,select_catalog_role,delete_catalog_role,execute_catalog_role
Role is the grouping of privileges that can be granted to users.As privileges are grouped they can be granted and revoked simultaneously.
72) What is the procedure to recover Lost SYSDBA Password in Oracle Database?
There are instances when the dba loses their sysdba passwords.
1) Identify the location of the password file.
2) Make a copy of the password file
3) Remove the password file
4) Use the orapwd utility from the OS prompt and create a new password file as follows :
orapwd file=(newpassword file) password=newsysdba password entries=no of entries
5) Try logging in with new password.
73) How to address DBMS_LOB Usage Oracle Error?
When we use dbms_lob package to manipulate objects like images,video,pictures we follow the steps:
1) create a directory
2) create a anonymous pl/sql procedure or prcedure to manupulate images
We encounter a common oracle error when we execute dbms_lob
File can’t be opened/file is not existing in locationpecified.
Solution:
grant read on directory directory_name to user;
grant write on directory directory_name to user;
This resolves the issue.
74) When a tablespace is in readonly mode can you create table in that tablespace?
I made the tablespace read only and tried creating table. It did throw error.I changed it back to read write and it worked fine without any issues.
SQL> alter tablespace user read only;
Tablespace altered.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> create table news(name varchar2(100) ) tablespace users;
Table created.
75) Give details on Oracle Datapump Export Modes :-
Datapump in oracle is an interesting technology that has replaced traditional exp/imp starting 10.1. In latest versions Oracle Datapump features have been enhanced. Using oracle datapump we can unload the database information traditionally called database export in one of the following modes
1) Full mode
2) Tables mode
3) Schemas mode
4) Tablespaces mode
5) Transportable tablespaces mode
76) Your manager wants to make sure if the performance of the database is same before and after upgrade. What is the proactive measure you will need to take towards this?
The best and easy way is to make use of AWR compare-period reports. As a first step capture periodic baselines before the upgrade, that can be used after upgrade for comparison purpose. This baseline can be year-end, month-end, quarter-end periods depending on requirement
77) What is the minimum SYSTEM, SYSAUX,UNDOTBS1, TEMP tablespace size required for upgrading to Oracle 12c?
SYSTEM tablespace should be 1262MB (1.3GB) in size, SYSAUX needs to be 1476MB (1.4GB) minimum in size , UNDOTBS1 needs to be 400 MB in size, TEMP tablespace needs to be 60MB minimum in size for successful 12c upgrade
78) What is the disadvantage of using database upgrade assistant DBUA for upgrading your database?
The DBUA tool is the graphic tool used for upgrade purpose. Inspite of GUI convenience there are some disadvantages of using this tool as follows :
a) As this is an automated tool the control over upgrade process is limited
b) This tool can’t be used to perform OS (or) hardware migration
c) This tool needs to meet a specific release level
79) What are the disadvantages of manual upgrade?
This is error prone owing to manual intervention of DBA and involves more work. Errors needs to be checked manually. Again this upgrade needs proper release level check before upgrade. Upgrade does not automatically takes care of hardware (or) OD migration
80) How to lock a table in exclusive mode?
A table can be locked in exclusive mode as follows:
SQL>lock table table_name in exclusive mode;
We can release the lock using:
SQL>commit;
We can also issue a ROLLBACK;

81) 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
82) 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
83) 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
84) 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
85) 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
86) 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
87) 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
88) 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
89) 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
90) 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
91) 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
92) 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
93) 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
94) 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

95) 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
96) 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
97) 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
98) 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
99) 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
100) 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

101) 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
102) What is an oracle initialization parameter?
An initialization parameter in an oracle database are the set of parameters used to create the oracle instance memory structure
103) Where are teh initialization parameters stored?
In oracle initialization parameters are stored in static parameter file such as PFILE or dynamic parameter file such as SPFILE
104) How many initialization parameters are there in Oracle database?
There are about 300 initialization parameters in Oracle database
105) What are hidden initialization parameters?
Oracle database has about 1500 initialization parameters not visible to even DBA. These parameters can be accessed and changed only upon confirmation from Oracle support.
106) What is the initialization parameter without default value?
Among the 300 initialization parameters present in the oracle database, 299 parameters have default values set for them. The only parameter without default value is the DB_NAME initialization parameter
107) How is automated memory management possible using initialization parameters in latest version of oracle?
Make use of parameters memory_target, memory_max_target parameters
108) Can we switch users between resource consumer groups/consumer groups?
DBA’s can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use.