Top Oracle DBA Interview Questions And Answers

p Answers to Oracle DBA Interview Questions

1. Oracle DBA Characteristics

Criteria	Result
Main storage structures	Logical and Physical
Architecture	Grid architecture
ACID Compliance	Fully compliant
Data stored as	Data blocks, extents, segments and table spaces

2. What is Oracle?

Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi user environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.

3. What is a Oracle Database?

Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system. A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database. A Data Guard ensures data protection and high availability of data and control file records the physical structure of the database.

4. What are the roles of DBA?

DBA has the authority to create new users, remove existing users or modify any of the environment variables or privileges assigned to other users.

Manage database storage
Administer users and security
Manage schema objects
Monitor and manage database performance
Perform backup and recovery
Schedule and automate jobs

5. What are different Oracle database objects?

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES

6. What are the benefits of ORDBMS?

The objects as such can be stored in the database. The language of the DBMS can be integrated with an object- oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.

7. What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

8. What are the Common Oracle DBA Tasks?

As an Oracle DBA, you can expect to be involved in the following tasks:

Installing Oracle software
Creating Oracle databases
Performing upgrades of the database and software to new release levels
Starting up and shutting down the database
Managing the database’s storage structures
Managing users and security
Managing schema objects, such as tables, indexes, and views
Making database backups and performing recovery when necessary
Proactively monitoring the database’s health and taking preventive or corrective action as required
Monitoring and tuning performanceIn a small to midsize database environment, you might be the sole person performing these tasks. In large enterprise environments, the job is often divided among several DBAs, each with their own area of specialty, such as the database security administrator or database tuning expert.

9. List out the Tools for Administering the Database?

The following are some of the products, tools, and utilities you can use in achieving your goals as a database administrator.
-Oracle Universal Installer (OUI)
The Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
-Database Configuration Assistant (DBCA)
The Database Configuration Assistant creates a database from templates that are Oracle supplied, or you can create your own. It enables you to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch.
-Database Upgrade Assistant
This tool guides you through the upgrade of your existing database to a new Oracle release.
-Oracle Net Manager
This tool guides you through your Oracle Net network configuration.
-Oracle Enterprise Manager
The primary tool for managing your database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager as the single interface for managing your database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.

10. Differentiate between a cluster and a grid?

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.
At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered – as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don’t know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of course, a “client side” view.
From the “server side”, or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

11. Explain Oracle Grid Architecture?

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Oracle Architecture Interview Questions and Answers

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

What are the steps to install oracle on Linux system?
List two kernel parameter that effect oracle installation? Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.

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

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

What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer. Shared pool portion contain three major area: Library cache (parse SQL statement, cursor information and execution plan), data dictionary cache (contain cache, user account information, privilege user information, segments and extent information, data buffer cache for parallel execution message and control structure.

What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default. PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

What is a system change number (SCN)?
SCN is a value that is incremented whenever a dirty read occurs. SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks. SCN is a value that is incremented whenever database changes are made.

What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and enable faster database Recovery. The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database. LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

What happens when we fire SQL statement in Oracle?
First it will check the syntax and semantics in library cache, after that it will create execution plan. If already data is in buffer cache it will directly return to the client. If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

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

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

What are logfile states?
“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current. If a redo group containing redo‟s of a dirty buffer that redo group is said to be „ACTIVE‟ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media). And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten. One more state „UNUSED‟ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.

What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch. ALTER SYSTEM SWITCH LOGFILE;

How to check Oracle database version?
SQL> Select * from v$version;

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

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

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

Instance memory Structures:

System Global Area (SGA):
Allocated at instance startup, and is a fundamental component of an Oracle Instance.

SGA Memory structures:
Includes Shared Pool, Database Buffer Cache, Redo Log Buffer among others.

Shared Pool :
Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.

Library Cache:
Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.

Data Dictionary Cache :
Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.

Database Buffer Cache:
Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.

Redo Log Buffer :
Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process:
Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process:
Connects to the Oracle Instance and is Started when a user establishes a session. fulfills calls generated and returns results. Each server process has its own nonshared PGA when the process is started. Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information. In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.

Program Global Area (PGA):
Memory area used by a single Oracle server process. Allocated when the server process is started, deallocated when the process is terminated and used by only one process. Used to process SQL statements and to hold logon and other session information.

Background processes:
Started when an Oracle Instance is started.
Background Processes Maintains and enforces relationships between physical and memory structures
There are two types of database processes:
1. Mandatory background processes
2. Optional background processes

Mandatory background processes: – DBWn, PMON, CKPT, LGWR, SMON

Optional background processes: – ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:
• At commit
• When 1/3rd full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes

System Monitor (SMON) Responsibilities:
• Instance recovery – Rolls forward changes in redo logs – Opens database for user access – Rolls back uncommitted transactions
• Coalesces free space
• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:
• Signaling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information

Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?
orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba. Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete
root.sh
[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. Finished product-specific root actions. For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server. It creates the additional directories and sets appropriate ownership and permissions on files for root user. File type Extension Default location (when created with OMF) Pfile : ORA C:\oracle\product\10.2.0\admin\orcl\pfile Spfile: ORA C:\oracle\product\10.2.0\db_1\database Control file: CTL C:\oracle\product\10.2.0\oradata\orcl Redo log file: LOG C:\oracle\product\10.2.0\oradata\orcl Archive log file: LOG C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG Data file: DBF C:\oracle\product\10.2.0\oradata\orcl Alert log files: LOG C:\oracle\product\10.2.0\admin\orcl\adump Trace log files: TRC C:\oracle\product\10.2.0\admin\orcl\udump|bdump|cdump Password file: ORA C:\oracle\product\10.2.0\db_1\database

Oracle Database 11g New Feature for DBAs?
1) Automatic Diagnostic Repository [ADR] 2) Database Replay 3) Automatic Memory Tuning 4) Case sensitive password 5) Virtual columns and indexes 6) Interval Partition and System Partition 7) The Result Cache 8) ADDM RAC Enhancements 9) SQL Plan Management and SQL Plan Baselines 10) SQL Access Advisor & Partition Advisor 11) SQL Query Repair Advisor 12) SQL Performance Analyzer (SPA) New 13) DBMS_STATS Enhancements 14) The Result Cache 15) Total Recall (Flashback Data Archive) Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

What is the Difference Between Local Inventory and Global Inventory?

What is oraInventory ?
oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory. There are basically two kind of inventories, One is Local Inventory (also called as Oracle Home Inventory) and other is Global Inventory (also called as Central Inventory).

What is Global Inventory ?
Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories) You will see entry like HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

What is Local Inventory ?
Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

What is Oracle Home Inventory?
Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location: $ORACLE_HOME/inventory It contains the following files and folders: · Components File · Home Properties File · Other Folders

Can I have multiple Global Inventory on a machine ?
Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

What to do if my Global Inventory is corrupted ?
No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option -attachHome ./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name” CLUSTER_NODES=”{}”

What is RESULT Cache? 11G Backgroung Processes?
The following process are added in 11g as new background processes. 1 dbrm DB resource manager 2 dia0 Diagnosability process 3 fbda Flashback data archiver process 4 vktm Virtual Timekeeper 5 w000 Space Management Co-ordination process 6 smc0 Space Manager process NOTE : The above six are mandatory processes.
But 11g has 56 new processes added which can be queried using

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ? Background processes are started automatically when the instance is started. Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated. If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted. Any issues related to backgroud processes should be monitored and analyzed from the trace files generated and the alert log.
Literal Vs. Bind Variables?
Select * from emp where dept=10; what level should i configure for better performance Where Clause .... Literal or Bind Variables? Expalin?
What is a Baseline?
Incarnation?
Explain in detail?
Where the incarnation information will be stored?
Hard Parse Vs. Soft Parse?
What is semaphores, semaphores?
What is latch?
What is Enqueue?

What is SGA_TARGET and SGA_MAX_SIZE ?
SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don't use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow. SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE. SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE. SGA_MAX_SIZE sets the maximum value for sga_target. SGA_TAGET is 10G feature used to change the sga size dynamically .it specifies the total amount of SGA memory available to an instance. this feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size are affected.

SGA_MAX_SIZE & SGA_TARGET
http://maxwellmiranda.wordpress.com/2009/09/17/sga_max_size-sga_targe/
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic. The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value. I.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can‟t resize the SGA_TARGET value to more than 4GB.
It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET Do not dynamically set or unset the SGA_TARGET parameter. This should be set only at startup. SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing. SGA_TARGET provides the following: § Single parameter for total SGA size § Automatically sizes SGA components § Memory is transferred to where most needed § Uses workload information § Uses internal advisory predictions § STATISTICS_LEVEL must be set to TYPICAL § SGA_TARGET is dynamic § Can be increased till SGA_MAX_SIZE § Can be reduced till some component reaches minimum size § Change in value of SGA_TARGET affects only automatically sized components

If I keep SGA_TARGET =0 then what will happen ?
Disable automatic SGA tuning by setting sga_target=0
Disable ASMM by setting SGA_TARGET=0
http://www.orafaq.com/wiki/SGA_target
SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing. Default value 0 (SGA auto tuning is disabled)

What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN. The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files.

In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required, 1.after incomplete recovery (Point in Time Recovery) or 2.recovery with a backup control file. 3. recovery with a control file recreated with the reset logs option. http://onlineappsdba.com/index.php/2009/09/11/oracle-database-incarnation-open-resetlogs-scn/ http://web.njit.edu/info/limpid/DOC/backup.102/b14191/osrecov009.htm Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation..

Difference between RESETLOGS and NORESETLOGS ? http://oracleappstechnology.blogspot.in/2008/05/difference-between-resetlogs-and.html
After recover database operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS NORESETLOGS: The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

RESETLOGS:

CAUTION: Never use RESETLOGS unless necessary. Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!! Before using the RESETLOGS option take an offline backup of the database.

What is SCN (System Change Number) ?
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs. Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.

What is Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”. Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

How to view Database Incarnation history of Database ?
Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;
However, you can use the RESET DATABASE TO INCARNATION command to specify that SCNs are to be interpreted in the frame of reference of another incarnation.
•For example my current database INCARNATION is 3 and now I have used
FLASHBACK DATABASE TO SCN 3000;then SCN 3000 will be search in current incarnation which is 3. However if I want to get back to SCN 3000 of INCARNATION 2 then I have to use,
RMAN> RESET DATABASE TO INCARNATION 2;
RMAN> RECOVER DATABASE TO SCN 3000;

What is the difference between SGA_TARGET and SGA_MAX_SIZE?

SGA_MAX_SIZE parameter is the maximum allowable size of the SGA for the lifetime of the instance.

SGA_TARGET is set to some value from SGA_MAX_SIZE, it can be adjusted upto SGA_MAX_SIZE parameter, but not more than that value.

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized. They are

Buffer Cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)

If these pools are set to non-zero values, then those values are used by Automatic Shared Memory Management.

But the following pools are manually sized components and are not affected by ASMM.

Log Buffer

Other Buffer caches, such as KEEP,RECYCLE, and other block sizes.

What’s the difference between traditional Hot backup & RMAN backup?

RMAN will be faster and smaller
1) RMAN does not need to put the datafiles into hot backup mode to back them up
2) RMAN only backs up allocated blocks (whereas user-managed HOT backup backs up the whole file)
In traditional hot backup tablespaces are put in hot backup mode, where as in RMAN no need to do that.
The redolog generation is pretty high during traditional hot backup due to the whole block change is
recorded rather than just changed information. This makes the block consistent at recovery and thus avoids
the fractured block read phenomenon. RMAN, on the other hand, is not an operating-system utility but an
Oracle Database–aware product. Hence, all reads by RMAN for the purpose of backup are performed
using the block size of the database block itself, thus avoiding fractured block reads. When RMAN does
encounter inconsistencies while reading blocks, RMAN notes these errors in the alert log and re-reads the
block as may be required.

RMAN reduces the complexity of backup and recovery. RMAN can determine what needs to be backed up or restored and figure out the best way to do this. With Oracle 10g, RMAN backups can be automatically scheduled to the Flash Recovery Area so that you do not have to think about backups any more. OEM in 10g can be used to perform a manual backup if you need it.

RMAN has many other features that the traditional hot and cold backup scripts cannot perform. Those features include, but are not limited to:

Ability to perform incremental backups.
Ability to recover one block of a datafile.
Ability to perform the backup and restore with parallelization.
Ability to automatically delete archived redo logs after they are backed up.
Ability to automatically backup the control file and the SPFILE.
Ability to restart a failed backup without having to start from the beginning.
Ability to verify the integrity of the backup.
Ability to test the restore process without having to actually perform the restore.

Difference between Cold backup and Hot backup?

DBAs prefer a cold backup because the backup is consistent. More than anything, they like the comfort factor of a nice, consistent backup.
Hot backups will result in inconsistencies in the backed up datafiles.
RMAN will resolve these inconsistencies on recovery, after a restore. For many DBAs, it is a comfort factor.
If you perform a cold backup, you must have downtime for your database.
If you cannot afford the downtime, then a cold backup is not for you. If you want or need to perform a hot backup, then you must be running in archive log mode.

Cold backups are easy, do not require archived redo logs, but require downtime. Hot backups do not require any downtime, but are a little more complex and require archived redo logs.

Hot backup, also known as dynamic or online backup, is a backup performed on data while the database is actively online and accessible to users.

A hot backup is the standard way of doing most database backups. Oracle is a principal vendor of the process, but the company doesn't have a monopoly on the concept. Oracle Recovery Manager (RMAN) is the preferred method of backing up Oracle. A user can log in to RMAN and command it to back up a database. RMAN can write backup sets to disk and tape.

Neither cold backup nor hot backup identify the corrupted blocks in backup whereas rman can identify

RMAN will take the backup of database used block only where as hot backup will take physical existing database files completely

State five duties of an Oracle DBA?

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

What is the primary duty of the DBA?

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

What is database?

Databse is a structure that stores information about multiple types of entities, the attributes (or characteristics) of the entities, and the relationships among the entities.

What is a database instance?

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

What is differnce between DBMS and RDBMS?

DBMS is program that defines rules for data stroage and retrival,

RDBMS is special type of DBMS that stores the data in relational format as described in the relational.

What are the components of an oracle instances?

Background process and memory structure

What are the components of sga?

Buffer cache, log buffer, shared pool, large pool, and java pool.

Why do you need pga?

It is a memory area used y oracle instance. it is requried to stroe session specific informations.

What are the differents types of memory structure available in oracle?

Stroage Global Area (SGA) and Program Global Area(PGA)

What is database writer(DBWR)?

It writes changed data blocks from buffer cache to datafile.

When does DBWR write to the datafile?

When checkpoint occurs

When number of dirty blocks reaches a threshold

Every three seconds due to timeout

When serever process needs free space in buffer cache to read now blocks

What are the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

What is a Tablespace?

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

Why use materialized view instead of a table?

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

What is schema?

A schema is collection of database objects of a user.

What are Roles?

Roles are named groups of related privileges that are granted to users or other roles.

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

DBA_FREE_SPACE

DBA_SEGMENTS

DBA_DATA_FILES

Explain the difference between a data block, an extent and a segment?

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

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

Alter database backup control file to trace.

What is difference between UNIQUE constraint and PRIMARY KEY constraint?

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls. A table can have only one primary keys.

What is a data segment?

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

Name a tablespace, which is automatically created when you create a database?

The system tablespace is created automatically during database creation

Which flle is accessed first when you start an oracle database?

Parameter file, and spfile

What are the advantages of using spfile?

Its supportss dynamic changes in parameter values.

The changes in spfile can only be made by using in oracle statements.

Backup of spfile is possible throught RMAN.

How do you view parameter values when you are using pfile or spfile?

Show parameter command from sql*plus and query v$paramter command view to see values of the paqramters.

How can you convert spfile to pfile and pfile to spfile?

sql> create spfile from pfile;

sql> create pfile from spfile;

How can you backup the parameter files?

In Rman

RMAN> configure controlfile autobackup on;

IN Ran restore an spfile

RMAN> restore controfile from autobackup;

What happen alter a user process fails?

Pmon clean up the memory after user process fails

What is the difference between database and instance?

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

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

Datafiles

Control files and

Redo log files.

What is the Oracle Enterprise manager?

The Oracle Enterprise Manager is the new graphical administration tool designed to help the DBA manage one or more Oracle systems.

Name three Oracle Utilities?

The SQL*Loader

The Export utility and

The Import utility

What is the difference between PGA and SGA?

The basic difference between SGA and PGA is that PGA cannot be shared between multiple processes, in the sense, that it is used only for requirements of a particular process whereas the SGA is used for the whole instance and it is shared.

What are the types of privileges?

System Privileges

Object Privileges

What is the difference between system and object privileges?

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

What is log switch?

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

What is Global Resource Directory (GRD)?

GRD is used by GES and GCS to maintain status of datefiles and cached blocks. This process provides required information for cache fusion and maintains data integrity.

What background process refreshes materialized views?

The job queue

What is the Recovery Manager used for?

The Recovery Manager is used for backup and recovery. This product performs these operations and maintains a catalog of previous backups.

What is the difference between an index cluster and a hash cluster?

An index cluster is a cluster that has an index on the cluster key,

Whereas the hash cluster uses a hashing function to access the cluster key.

What is a recovery catalog?

Recovery catalog is an inventory of the backup taken by RMAN for the database.It is used to restore a physical backup, reconstruct it, and make it available to the server.

What is load balancing?

Load balancing is a server process that monitors loading on all of the forms servers. Each of the forms servers runs a load balancing client which keeps the load balancing server apprised of its load.

What in Streams pool in Oracle 10g?

Streams pool is a part of System Global Area (SGA) from which memory for streams is allocated if it is configured. It can be configured by specifying initialization parameter STREAMS_POOL_SIZE.

Name the four types of segments?

Data

Index

Rollback

Temporary

What is a dedicated server process?

A dedicated server process has a one to one correlation between the user process and the server process.

Each user process gets one server process.

What Oracle object is used for read consistency?

The rollback segments are used for read consistency.

What is RAC Cluster?

RAC Cluster is a database with a shared cache architecture that overcomes the limitations of traditional shared nothing and shared disk approaches.

It is a key component of Oracle’s private cloud architecture.

What is the control file?

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

What is the difference between incremental backup and differential backup?

Both, incremental and differential backup files that have been modified or created after the previous backup. However, attributes are reset after the incremental backup but after the differential backup.

Which files must be backed up?

Database files

Control files

Archived log files

What is the use of Oracle Cluster Registry(OCR)?

OCR stores information about cluster resources and their configuration. The CRS process uses that information to manage resources.

What are the different types of connection load balancing?

Server side load balancing

Client side load balancing

What is system change number?

SCN is an ID that Oracle generates for every transaction. It is recorded with the corresponding change in a redo entry.

What is an SID?

The SID is the system identifier. This environment variable is used to determine which database to connect to.

What is the difference between hot backup and cold backup?

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

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

What Oracle object is used for recovery operations?

The redo log files and the archieve log files are used for database recovery.

What is the difference between hot backup and cold backup?

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

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

What is SQL*Loader?

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

What are the components of SGA?

Buffer cache

Log buffer

Shared pool

Large pool

Java pool

What is a synonym?

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

What is archive log file?

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

What is a backup set?

Backup set is a logical grouping of backup files that are created when you issue an RMAN backup command. It is RMAN’s name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

What does database tier consist of?

Database tier consists of Oracle database, which stores all the data. The database server contains Oracle Home and data files.

What is an interconnect network?

An interconnect network is a network between nodes of a cluster. It uses switches to ensure that only nodes can access this network, Generally, this is a high speed connection.

What is a shared server process?

The shared server process handles more than one user process. The dispatcher queues the job and the shared server process executes it.

What is the parameter file?

The parameter file is a file that contains additional command line parameters.

What is rolling upgrade?

Rolling upgrade refers to the software upgrade while database is still functional without bringing the database down for upgrade. This is the new feature of Oracle 11g.

What is the difference between SPFILE and PFILE?

Oracle can use both SPFILE and PFILE as initialization parameter files.

SPFILE is a binary file while PFILE is a text file.

You can change the values in PFILE by directly editing it but those changes would not take effect until the restart of the database.

What is RAC?

Real application cluster (RAC) is a clustering solution.

It ensures high availability for database application.

A RAC setup contaions at least two nodes for a database.

RAC provides high availability and load balancing through these nodes.

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

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

What is the difference between a privilege and a role?

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

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

How many types of cloning are available?

Adclone

Rapidclone

What is LoGWRiter(LGWR)?

LGWR is the background process that writes redo information from redo log buffers to the log files.

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

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

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

What is RMAN?

RMAN is an Oracle supplied tool or utility that can be used to manage backup and recovery activities.

What is ACFS?

ACFS provides an Oracle Home shared file system or a general cluster file system on ASM storage. It needs ASM Dynamic Volume Manager(ADVM) to communicate to the operating system.

What are the disk components?

Data files

Redo Logs

Control files

Password files and

Parameter files

are the disk components.

What is a primary key?

A primary key is an attribute (or a collection of attributes) that uniquely identifies each row in the table. In other words, each entity instance in a table must be unique; and therefore, primary key is a way of ensuring this.

tains data types for attributes and indexes.

What is a foreign key?

A foreign key is an attribute in a child table that matches the primary key value in the parent table.

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

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

Shared pool portion contain three major area:

Library cache

data dictionary cache

data buffer cache

What is the difference between SMON and PMON processes?

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

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

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

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

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

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

Explain Oracle Architecture?

Oracle Instance:

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

Oracle server:

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

Oracle database:

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

Instance memory Structures:

System Global Area (SGA):

SGA Memory structures:

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

Program Global Area (PGA):

Memory area used by a single Oracle server process.

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

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

Background processes:

Started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

1. Mandatory background processes

2. Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT, LGWR, SMON

Optional background processes:

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

System Monitor (SMON) Responsibilities:

• Instance recovery

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

• Rolling back the transaction

• Releasing locks

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

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

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

orainstRoot.sh

What is oraInventory ?

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

Difference between RESETLOGS and NORESETLOGS ?

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

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

What is SCN (System Change Number) ?

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

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

What is Database Incarnation ?

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

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

How to view Database Incarnation history of Database ?

Using SQL> select * from v$database_incarnation;

Using RMAN>LIST INCARNATION;

What is SGA_TARGET and SGA_MAX_SIZE ?

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

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

What is an Oracle Instance?

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

What information is stored in Control File?

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

The database name and unique ID

The timestamp of database creation

The names and locations of associated datafiles and redo log files

Tablespace information

Datafile offline ranges

Archived log information and history

Backup set and backup piece information

Backup datafile and redo log information

Datafile copy information

Log records: sequence numbers, SCN range in each log

RMAN Catalog

Database block corruption information

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

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

What is Instance Recovery?

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

Instance recovery occurs in two steps:

Cache recovery:

Changes being made to a database are recorded in the database buffer cache. These changes are also recorded in online redo log files simultaneously. When there are enough data in the database buffer cache,they are written to data files. If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.

Transaction recovery:

When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment. The data stored in the undo segment is used to restore the original values in case a transaction is rolled back. At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

What is a Tablespace

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

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

Permanent tablespaces

Undo tablespaces

Temporary tablespaces

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

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

What is a Checkpoint?

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

Which Process reads data from Datafiles?

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

Which Process writes data in Datafiles?

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

What is a Shared Pool?

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

What is kept in the Database Buffer Cache?

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

What is difference between PFile and SPFile?

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

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

Can you change SHARED_POOL_SIZE online?

YES. That’s possible.

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

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

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

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

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

1) creating a profile:

2) Create user and assign user to the all_users profile

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

What is Automatic Management of Segment Space setting?

Oracle9i New Feature Series: Automatic Segment Space Management

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

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

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

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

Compare and contrast TRUNCATE and DELETE for a table?

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

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

There are several methods to do this;

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

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

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

SQL Statement:

CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM source_table;

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

DROP TABLE real_table;

RENAME temp_name TO real_table;

Define transaction?

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

What is the difference between sql&oracle?

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

What are different Oracle database objects?

TABLES

VIEWS

INDEXES

SYNONYMS

SEQUENCES

TABLESPACES

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

DBA_FREE_SPACE

DBA_SEGMENTS

DBA_DATA_FILES

What is a database instance?

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

What is the difference between a view and a synonym?

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

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

Alter database backup control file to trace.

What is the difference between database and instance?

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

What is the duration for DBWn process to invoke?
For every 3 seconds DBWn process will be invoked to write the committed data from buffer cache to data files. When a user executes commit command. Whenever checkpoint occurs. db_writer_processes cannot exceed a value of 20.

When will be logWr process will be invoke?
Log_writer_process will be invoked when 1/3rd of Redo Log Buffer is full then it will be invoked and transaction data will be shifted from Redo log buffer to DB Redo Log files. and Whenever a user commits a transaction and whenever checkpoint occurs.

Home

Q&A FAQs

Top Oracle DBA Interview Questions And Answers

No comments: