oracle dba questions
The DBA should posses the following skills
(1) A good knowledge of the operating system(s) (2) A good knowledge of physical database design (3) Ability to perform both Oracle and also operating system performance monitoring and the necessary adjustments. (4) Be able to provide a strategic database direction for the organisation. (5) Excellent knowledge of Oracle backup and recovery scenarios. (6) Good skills in all Oracle tools. (7) A good knowledge of Oracle security management. (8) A good knowledge of how Oracle acquires and manages resources. (9) Sound knowledge of the applications at your site. (10) Experience and knowledge in migrating code, database changes, data and menus through the various stages of the development life cycle. (11) A good knowledge of the way Oracle enforces data integrity. (12) A sound knowledge of both database and program code performance tuning. (13) A DBA should possess a sound understanding of the business. (14) A DBA should have sound communication skills with management, development teams, vendors, systems administrators and other related service providers.
Generic responsibilities
(1) Creates and maintains all databases required for development, testing, education and production usage. (2) Performs the capacity planning required to create and maintain the databases. The DBA works closely with system administration staff because computers often have applications or tools on them in addition to the Oracle Databases. (3) Performs ongoing tuning of the database instances. (4) Install new versions of the Oracle RDBMS and its tools and any other tools that access the Oracle database. (5) Plans and implements backup and recovery of the Oracle database. (6) Controls migrations of programs, database changes, reference data changes and menu changes through the development life cycle. (7) Implements and enforces security for all of the Oracle Databases. (8) Performs database re-organisations as required to assist performance and ensure maximum uptime of the database. (9) Puts standards in place to ensure that all application design and code is produced with proper integrity, security and performance. The DBA will perform reviews on the design and code frequently to ensure the site standards are being adhered to. (10) Evaluates releases of Oracle and its tools, and third party products to ensure that the site is running the products that are most appropriate. Planning is also performed by the DBA, along with the application developers and System administrators, to ensure that any new product usage or release upgrade takes place with minimal impact. (11) Provides technical support to application development teams. This is usually in the form of a help desk. The DBA is usually the point of contact for Oracle Corporation. (12) Enforces and maintains database contraints to ensure integrity of the database. (13) Administers all database objects, including tables, clusters, indexes, views, sequences, packages and procedures. (14) Assists with impact analysis of any changes made to the database objects. (15) Troubleshoots with problems regarding the databases, applications and development tools. (16) Create new database users as required. (17) Manage sharing of resources amongst applications. (18) The DBA has ultimate responsibility for the physical database design.
what is a control file and what does it do
A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files.
what is a database
A database is a set of files, located on disk, that store data. These files can exist independently of a database instance
what is a tablespace and where is it located
A database is divided into logical storage units called tablespaces. A tablespace is the logical container for a segment. Each tablespace contains at least one data file.
WHAT is DBMS
A database management system (DBMS) is software that controls the storage, organization, and retrieval of data. Typically, a DBMS has the following elements: • Kernel code This code manages memory and storage for the DBMS. • OS( Operating System) Unix, Linux, HP, Solaris, AIX(IBM) • Repository of metadata This repository is usually called a data dictionary. • Query language (SQL) This language enables applications to access the data.
what is a schema
A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes.
what is a server
A database server is the key to information management. a server reliably manages a large amount of data in a multi-user environment so that users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
DEADLOCKS
A deadlock is a specific type of lock conflict where two sessions waiting for data are locked by each other so that neither can complete its transaction.
what is a profile
A profile is collection of attributes that apply to a user. It enables a single point of reference for any of multiple users that share those exact attributes.
what is a role
A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user.
SCHEMA OBJECTS
A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
WHAT IS A TABLE
A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes).
WHAT IS RDBMS
An RDBMS distinguishes between the following types of operations: LOGICAL and Physical operations
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.
what is an instance
An instance is a set of memory structures that manage database files such SGA, PGA, and background process
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.
DATABASE FAMILY TREE
DATABASE--> TABLESPACE--->DATAFILE ---- SEGMENT--->>>EXTENT---> DATA BLOCKS
list the four logical storage structures
Data blocks, Extents, Segments, Tablespaces
Data Definition Language Statements
Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to: ■ Create, alter, and drop schema objects and other database structures, including the database itself and database users (CREATE, ALTER, DROP). ■ Change the names of schema objects (RENAME). ■ Delete all the data in schema objects without removing the objects' structure (TRUNCATE). ■ Grant and revoke privileges and roles (GRANT, REVOKE). ■ Turn auditing options on and off (AUDIT, NOAUDIT). ■ Add a comment to the data dictionary (COMMENT). DDL statements implicitly commit the preceding commands and start new transactions. Some examples of DDL statements are: CREATE TABLE plants (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); DROP TABLE plants; GRANT SELECT ON employees TO scott; REVOKE DELETE ON employees FROM scott;
list the 3 physical storage structures
Data files, Control files, Online redo log files
Data Manipulation Language Statements
Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to: ■ Retrieve or fetch data from one or more tables or views (SELECT); fetches can be Scrollable. ■ Add new rows of data into a table or view (INSERT). ■ Change column values in existing rows of a table or view (UPDATE). ■ Update or insert rows conditionally into a table or view (MERGE). ■ Remove rows from tables or views (DELETE). ■ View the execution plan for a SQL statement (EXPLAIN PLAN). ■ Lock a table or view, temporarily limiting other users' access (LOCK TABLE). DML statements are the most frequently used SQL statements. Some examples of DML statements are: SELECT last_name, manager_id, commission_pct + salary FROM employees; INSERT INTO employees VALUES (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); DELETE FROM employees WHERE last_name IN ('WARD','JONES');
Embedded SQL Statements
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to: ■ Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE). ■ Specify a database and connect to Oracle Database (DECLARE DATABASE, CONNECT). ■ Assign variable names (DECLARE STATEMENT). ■ Initialize descriptors (DESCRIBE). ■ Specify how error and warning conditions are handled (WHENEVER). ■ Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE). ■ Retrieve data from the database (FETCH).
what is a datafile and what does it do
Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files.
what is a user
In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account
What are the Common Oracle DBA 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.
Application and Networking Architecture
Oracle Database enables processing to be split between the database server and the client programs. The computer running the RDBMS handles the database server responsibilities while the computers running the applications handle the interpretation and display of data.
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.
PL/SQL is the?
Oracle procedural language extension to SQL. PL/SQL combines the ease and flexibility of SQL with the procedural functionality of a structured programming language, including such routines as IF ... THEN, WHILE, and LOOP. When designing a database application, consider the following advantages of using stored PL/SQL:
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.
What is SQL and what does it do
SQL is a set-based declarative language that provides an interface to an RDBMS such as Oracle Database. SQL is nonprocedural and describes what should be done.
creating a role in sql
SQL> Create role select_data_role; SQL> Grant select on emp, dept, bonus to select_data_role; SQL> GRANT select_data_role TO Robert; SQL> REVOKE select_data_role FROM Robert;
WHAT IS IN SCHEMAs
Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes
4.) Session Control Statements
Session control statements manage the properties of a particular user's session. For example, they enable you to: ■ Alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESSION). ■ Enable and disable roles (groups of privileges) for the current session (SET ROLE).
5.) System Control Statements
System control statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), terminate a session, and perform other tasks.
What are different Oracle database objects?
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES
oracle dba responsibilities
Task 1: Evaluate the Database Server Hardware Task 2: Install the Oracle Software Task 3: Create and Open the Database Task 4: Back Up the Database Task 5: Enroll System Users Task 6: Implement the Database Design Task 7: Back Up the Fully Functional Database Task 8: Tune Database Performance
what is the SYS schema and what does it do
The SYS schema stores the base tables and views for the data dictionary.
what is the SYSTEM SCHEMA and what does it do
The SYSTEM schema stores additional tables and views that display administrative information, and internal tables and views
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.
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.
3.) Transaction Control Statements
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to: ■ Make changes to a transaction permanent (COMMIT). ■ Undo the changes in a transaction, since the transaction started or since a savepoint (ROLLBACK). ■ Set a point to which you can roll back (SAVEPOINT). ■ Establish properties for a transaction (SET TRANSACTION).
Transaction Control Statements
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to: ■ Make changes to a transaction permanent (COMMIT). ■ Undo the changes in a transaction, since the transaction started or since a savepoint (ROLLBACK). ■ Set a point to which you can roll back (SAVEPOINT). ■ Establish properties for a transaction (SET TRANSACTION).
Assigning Tablespace Quotas
You can assign a user's tablespace quotas when you create the user, or add or change quotas later.
database hierachy
daabase tablespace users tables indeses
what is inside system tablespace
data dictionary system objects users objects prebundled code
Data consistency means that
each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.
Packages
encapsulate and store related procedures, functions, variables, and other constructs together as a unit in the database. They offer increased functionality. For example, global package variables can be declared and used by any procedure in the package. Packages also improve performance, because all objects of the package are
Data concurrency means that
many users can access data at the same time
Triggers are?
named database objects that are implicitly fired when a triggering event occurs. The trigger action can be run before or after the triggering event. Triggers are similar to stored procedures but differ in the way that they are invoked.
What is ORDBMS
object-relational database management system (ORDBMS).
what is inside a sysaux tablespace
performance information matrix AWE- automatic workload Repository snapshots
whats in undo tablespace
rollback values saved here data manipulation language values
what command counts users in a database
select count (*) from dba_users
Procedures and functions are?
sets of SQL and PL/SQL statements grouped together as a unit to solve a specific problem or to perform a set of related tasks. They are created and stored in compiled form in the database and can be run by a user or a database application. Procedures and functions are identical, except that functions always return a single value to the user. Procedures do not return values.
whats in temporary tablespace
stores temp data
name the different types of tablespaces
system sysaux temp sys users undo
whats in users tablespace
users
what is online redo log files
which is a set of two or more online redo log files. An online redo log is made up of redo entries (also called redo records), which record all changes made to data.
DBA Skills Required
• Good understanding of the Oracle database, related utilities and tools • A good understanding of the underlying operating system • A good knowledge of the physical database design • Ability to perform both Oracle and operating system performance tuning and monitoring • Knowledge of ALL Oracle backup and recovery scenarios • A good knowledge of Oracle security management • A good knowledge of how Oracle acquires and manages resources • A good knowledge Oracle data integrity • Sound knowledge of the implemented application systems
