*******ORACLE Apps DBA & ORACLE DBA Blog*******: January 2016

1. Architecture - 1

1. Why do we need to maintain our data in database?

As we all know Database has a availability of a storage system, the most obvious of these being persistence. To sum it up, we can identify the following reasons for wanting a Database Management Systems short for DBMS:

We need to share data of all kinds between users (except for very small systems), between tools working on them, and usually between different computers. All should be able to work with the same set of data.

We need persistent storage and storage must be reliable.

Several users and/or several tools must be able to safely access the same data concurrently.

A DBMS may provide more efficient access to large amounts of data, through indexing and other optimizations.

A DBMS and its data model provides an abstraction; we do not need to care about the physical storage format.

A DBMS may also be used for communication between different users or applications.

2. What are the responsibilities of a Database Administrator?
Installing and upgrading theOracle Server and application tools.

Allocating system storage and planning future storage requirements for the database system.

Managing primary database structures (tablespaces) Managing primary objects (table, views, indexes)

Enrolling users and maintaining system security.

Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database.

Monitoring and optimizing the performance of the database.

Planning for backup and recovery of database information.

Maintain archived data on tape Backing up and restoring the database.

Contacting Oracle Corporation for technical support.
3. What are the Daily Activities of a Oracle DBA

1. Check the Database availability

2. Check the Listener availability

3. Check the alert log file for errors

4. Monitoring space availability in table spaces

5. Monitoring mount point (see capacity planning document)

6. Validate Database backup or Archive backup

7. Find objects which is going to reach max extents

8. Database Health check

9. CPU, Processor, Memory usage

4. What is the most challenging aspect of your job?
This question will give you clues about the mindset of the DBA. When asked what is challenging about the DBA job, the DBA’s answer will reveal a great deal about his or her personality and ability to work well with other people. The DBA who talks about the technical challenges of the job and difficulties administering and maintaining the database may not be ideally suited for a position where contact for the development staff is critical. On the other hand, the DBA who speaks openly of the challenges associated with getting the developers fast answers and accurate information might be a better consideration.

5. How do you perceive the relationship between the DBA and the development staff?
DBAs by virtue of their high pay and product-specific knowledge, tend to think of developers as underlings, in some cases, DBAs view developers with outright contempt, believing their queries to be naive. On the other hand, DBAs with the proper attitude will respond to this question by talking about the developers as clients to whom they provide data services essential to the application. In some shops, the DBAs may be responsible for code, reviewing SQL queries, or DML statements written by developers; so, a good relationship is vital.

6. What is a Database instance?
Explain A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

7. What are mandatory background processes in Oracle Database?

Smon, pmon, ckpt, dbwr, lgwr

8. What is Oltp database?
OLTP means Online Transaction Processing. OLAP means Online Analytical Processing. OLTP deals with processing of data from transactional systems. For example, an application that loads the reservation data of a hotel is an OLTP system. An OLTP system is designed mainly keeping in the mind the performance of the end application. It comprises of the application, database & the reporting system that directly works on this database. The database in an OLTP system would be designed in a manner as to facilitate the improvement in the application efficiency thereby reducing the processing time of the application.

9. What is Olap database?

OLAP systems were mainly developed using data in a warehouse. Having said that a need was felt to isolate older data, it was necessary to store them in a format that would be useful in easing out the reporting bottlenecks. A need was felt to isolate the data & redesign the application data to such a format & structure that this data repository would be the prime source of business decisions. Coming back to OLAP systems, these systems were mainly developed on the isolated data.

2. Architecture - 2

1. What is the basic element of Base configuration of an oracle Database?
It consists of one or more data files. One or more control files. Two or more redo log groups. The Database contains multiple users/schemas one or more rollback segments
one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database consists of SGA (Database buffer cache,Shared pool ,Redo log buffer)

SMON (System monitor)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGA.

2. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

3. What is Execution Plan?
The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

4. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.

5. What are the different Components of SGA?
1. The fixed SGA (Fixed SGA)
2. Default block buffer (Db cache)
3. Different Standard Block buffers
4. Redo log buffer (Redo log buffer)
5. Java pool (Java pool)
6. Large pool (Large pool)
7. Shared pool (Shared pool)
8. Stream pool (Stream pool)
9. Result cache

6. What is dictionary cache?

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

7. What is Database Buffers?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size

8. What is the functionality of SYSTEM table space?
System tablespace is a main part oforacle database. All the database information is in it.it is created when database is created.It is managed byoracle server . dba cannot change its contents. it contains the data dictionary objects .

9. What is the function of checkpoint (CKPT)?
Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.

10. When does LGWR write to the database?
Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.

11. What is Shared SQL Area ?
A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oraclecan deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.

12. What Does DBWR do?
Database writer writes modified blocks from the database buffer cache to the data files.

13. What is server processes?
A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,

14. Name the process which carries the request to the memory components,And also fetches from disk to buffer?
Server Process

3. Architecture - 3

1. Which background process write dirty blocks from database buffer cache to data files?
Database writer (DBWR)

2. Which background process writes data from log buffer to redo log files?
Log writer (LGWR)

3. Which background process performs Crash recovery?
SMON (system monitor)

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

5. What is On-line Redo Log?
The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

6. What are the steps involved in Instance Recovery?
Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

7. What does COMMIT do?
COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.

8. How do you know whether the process is Server Side Process?
By seeing the process in ps-ef as ORACLE_SID.

9. When ckpt occurs?
1. For every 3 seconds
     2. When 1/3rd of DB buffer fills
     3. When log swtich occurs
     4. When database shuts down

10. What are the two steps involved in instance recovery?

1. Roll forward (redofiles data to datafiles), 2.Roll backward (undo files to data files).

11. What is the use of SMON?

SMON is an Oracle mandatory background process. It is used for Instance recovery.

12. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?

1.    SYSTEM - Data dictionary tables.
2.    DATA - Standard operational tables.
3.    DATA2- Static tables used for standard operations
4.    INDEXES - Indexes for Standard operational tables.
5.    INDEXES1 - Indexes of static tables used for standard operations.
6.    TOOLS - Tools table.
7.    TOOLS1 - Indexes for tools table.
8.    RBS - Standard Operations Rollback Segments,
9.    RBS1, RBS2 - Additional/Special Rollback segments.
10.  TEMP - Temporary purpose tablespace
11.  TEMP_USER - Temporary tablespace for users.
12.  USERS - User tablespace.

13. How do you know when the process is started?

Using ps -ef grep process name

14. What is meant by redo log buffer?Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

4. Database Creation

1. What are the steps involved in Database Startup?
Start an instance, Mount the Database and Open the Database.

2. What are the steps involved in Database Shutdown? Close the Database; Dismount the Database and Shutdown the Instance.

3. What is Restricted Mode of Instance Startup?
An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

4. What mode the instance should be to create the database?

No mount

5. While creating database can you specify the size of control files?

6. Which parameter determines the size of SHARED POOL?

SHARED_POOL_SIZE.

7. After mounting a database using command STARTP MOUNT can you open your database in RESTRICTED MODE. Using command “alter database open restrict;”

No.

8. While creating database can you specify more than one datafile for SYSTEM Tablespace?

Yes

9. What is a trace file and how is it created?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

10. What are the minimum parameters should exist in the parameter file (init.ora)?
DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.
DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters
(DB_NAME & DB_DOMAIN) CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used.
DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.
PROCESSES - To determine number of operating system processes that can be connected toORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally

11. How do you get the create syntax of a table or index or function or procedure?
Select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

12. Explain about System Table space and sysaux tablespace?
System table space has all the view relating to oracle. Sysaux tablespace is used for storage of non-system related tables and indexes that traditionally were placed in the System Tablespace. Like RMAN recovery catalog, Automatic workload repository and ultra search.

13. What are the different modes of mounting a Database with the Parallel Server?
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

14. Where alert log is stored? What is the parameter?

In trace directory /disk2/oradata/prod/diag/rdbms/prod/prod/trace).parameter is diagnostic_dest (oracle 11g)

15. What is a Data Dictionary?
The Oracle data dictionary is one of the most important components of the Oracle DBMS.It contains all information about the structures and objects of the database such as tables,columns, users, data files etc. The data stored in the data dictionary are also often called metadata.

16. How many redo logs should you have and how should they be configured for maximum recoverability?
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle).

17. What is the Max Size of the SID?

15char

5. Tablespace Management

1. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces.A tablespace is used to grouped related logical structures together

2. What are the Characteristics of Data Files?
A data file can be associated with only one Tablespaces. One or more data files can be use as data of database storage called a tablespace.

3. How do you drop a tablespace, if it has database objects?
Drop tablespace tablespacename including contents

4. Can we create a tablespace with multiple datafiles in a single stroke?

Yes

5. Can a datafile be associated with two different tablespaces?

No.

6. Can we rename a datafile when the corresponding tablespace is in read-only mode?

7. For transportable tablespace what should be the tablespace status?

Read-only

8. How to rename a datafile?

Tablespace datafile rename:

-Take tablespace offline;

Sys>> Alter tablespace <TBSNAME> offline;

-Change the name at OS level

Linux> cp oldname to newname

-Change the datafile name;

Sys>> Alter tablepsace <TBSNAME> rename datafile 'oldname' to 'newname';

9. Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

10. How do you drop a tablespace? Drop tablespace ts1 including contents and datafiles;

11. What is the procedure for Transportable tablespace migration?

Transportable Tablespaces (TTS) allows you to copy a set of datafiles for a tablespace on one database and plug that tablespace into a different database.As we noted, you cannot transport a single partition of a table without transporting the entire table. Therefore, we will need to exchange the partition with a stand-alone table temporarily, so that the partition becomes its own table.

12. How to rename the tablespace?

Sys>alter tablespace <tablesapce name> rename to <tablespace name>

13. How to know the default tablespaces?

Sys> select * from database-properties what property-name like ‘%default%’;

14. How many datafiles can you add for a tablespace?

65,536

6. Storage Parameters

1. What is an Extent?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

2. What is a Segment?
It is a space demanding object created by user. A segment is a set of extents allocated for a certain logical structure.

3. What is the package and procedure name to conver dmts to lmts and vice versa?

Exec dbms_space_admin.tablespace_migrate_from_local(‘TS1’)

Exec dbms_space_admin.tablespace_migrate_to_local(‘TS1’)

4. What is the value for the storage clause pctincrease when the tablespace extent management is local (uniform)?

5. Can you change the SEGMENT SPACE MANAGEMENT after creation of Tablespace?

6. What are the different types of Segments?Data Segment, Index Segment, Rollback Segment and Temporary Segment.

7. How to define Data Block size?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can't be changed latter.

8. What are the dictionary views used to monitor a database spaces?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
9 What is the difference between Data blocks and Extents?
Data blocks are the smallest block for data storage of the oracle. Blocks contain actual data. Extents are made up of data blocks . Extents are continuous available blocks for storing a specific type of information.

10. What is an Index Segment?
Each Index has an Index segment that stores all of its data.
11. What is the significance of having storage clause?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.

12. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the table space?
All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

13. What is the extent management of system tablespace when database is created manually, DBCA?

Dictionary, local

7. User Management

1. Can objects of the same Schema reside in different tablespaces?
Yes.

2. Can a Tablespace hold objects from different Schemas?
Yes.

3. Is CONNECT a system privilege or a role? If the answer is 'a role', what system privileges are assigned to this role by default?

It is a role .create session.

4. Where do you get the information of quotas?

Dba_ts_quotas view, user_ts_quotas view.

5. What is the init parameter to make use of profile?

Resource_limit=true

6. While creating user can you assign any role?

Yes

7. Can a segment (table) present on more than one tablespace? No, Possible only when a table if create by using partition feature.

8. Can a segment (table) present on more than one datafiles?

Yes, Datafiles should belong to one tablespace.

9. Can we create the permanent objects in temporary tablespace?

10. Can you drop an object if tablespace is Offline?

Yes

11. What privileges u gives normally when you create users?

Create session

12. What is a View?
A view is a virtual table. Every view has a Query attached to it.(The Query is a SELECTstatement that identifies the columns and rows of the table(s) the view uses.)

13. Can a View based on another View?
Yes.
14. Does a View contain Data?
Views do not contain or store data.

15. When does a Transaction end?
When it is committed or Roll backed.

16. Define Transaction?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

17. What is default tablespace?
The Tablespace to contain schema objects created without specifying a tablespace name.
18. What is Tablespace Quota?
The collective amount of disk space available to the objects in a schema on a particular tablespace.

19. What is the use of Roles?
REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group.

DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.

SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.

APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.
20. What is a profile?Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

21. What are the roles and user accounts created automatically with the database?20 roles are created, 7 user accounts are created when database is created manually

22. What is user Account in Oracle database?A user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.

23. How do you create a table in another tablespace name?

Create table xyz (a number) tablespace system

24. What are roles? How can we implement roles?Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.

25. What does ROLLBACK do?ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
26. What is a deadlock? Explain.Two processes waiting to update the same rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of these processes being killed externally

27. How do you get you demo files get created in your user?

$ORACLE_HOME/rdbms/admin/utlsampl.sql, edit and execute this script

28. Can we drop default profile?

29. Can we edit default profile?

Yes

8. Undo Management

1. Tell me about ORA 1555 and how do you address if you get this error?

It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.There are three situations that can cause the ORA-01555 error:

A. An active database with an insufficient number of small-sized rollback segments

B.A rollback segment corruption that prevents a consistent read requested by the query

C.A fetch across commits while your cursor is open

2. Flashback Query is possible with UNDO as well as ROLLBACK?

FALSE (only possible with Undo)

3. What is the view to see UNDO for how much data flushed out and how much data it having now….how many active blocks, expired blocks?

V$undostat

4. Two users fired a same statement at same time. performance degrades or good. What happens if it is select statement and for insert statement?

Performance comes down….there is chance for getting 1555 error.

5. What does ROLLBACK do? ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

6. What are the init parameters you have to set to make use of undo management?

undo_tablespace= undotbs1

undo_management= auto

undo_retention= time in minutes

comment rollback_segment

7. What is Rollback Segment?A Database contains one or more Rollback Segments to temporarily store "undo" information.

8. Can flashback work on database with out undo and with rollback segments?

No.

9. Flashback Technology

1. Will a normal user is able to use flashback transaction query?

2. What is flashback query and flash back recovery?

Flashback query, a new feature of Oracle 9i. Flashback query enables us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before we or any other users made permanent changes to it. Flashback recovery can bring the complete database to the previous state based on SCN number, on timestamp, on restore point.

3. How to flush recycle bin?

We use “Purge” command to Flush Recycle bin. It will automatically remove old data from recycle bin if tablespace needs some more space. If you want to purge just one single table then you type "Purge table <tableName>"

4. What is flashback database?

Oracle 10g’s brilliant alternative to database point in time recovery is the the Flashback Database feature. With this feature in place you can do almost everything that you can with point in time recovery, without actually having to go through all the disruptions and hassle that a PITR necessarily details. Unlike other flashback features, which depend on undo data for reconstructing your lost data, Flashback Database uses flashback logs to access past versions of changed blocks and allied with some more information mined from the archive logs, you can easily revert your database to a point in time in the past. Whilst the end product is very much like a point in time recovery, Flashback database is much faster and less disruptive, because you do not restore from backups and flashback logs are maintained on the disk itself. Setting it up at the basic level is pretty simple. It all starts being in ARCHIVELOG mode.

5. Can we go for flashback drop table in 9i?

6. Can any user present in dictionary managed tablespace use recycle bin?

7. What is flashback data archive?
A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

By default, flashback archiving is off for any table. You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table. After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.

8. Limitations of data archive?
There are a number of restrictions for flashback archives:The tablespaces used for a flashback archive must use local extent management and automatic segment space management. The database must use automatic undo management.

9.Database views useful to view information about flashback data archive? Viewing information about FLASHBACK ARCHIVE DATA
dba_FLASHBACK_ARCHIVE display information about flashback data archive
dba_FLASHBACK_ARCHIVE_TS display tablespaces of flashback data archive
dba_FLASHBACK_ARCHIVE_TABLES display information about tables that are enabled for flashback archiving.

10. Advantages of data archive?

The primary advantages of using Flashback Data Archive for historical data tracking include:

1. Application transparency

2. Seamless access

3. Security

4. Minimal performance overhead

5. Storage optimization

6. Centralized management

11. What is the use of DBMS_FLASHBACK Package?
The DBMS_FLASHBACKpackage provides the same functionality as Oracle Flashback Query, but Oracle Flashback Query is sometimes more convenient.The DBMS_FLASHBACK package acts as a time machine: you can turn back the clock, carry out normal queries as if you were at that time in the past, and then return to the present. Because you can use the DBMS_FLASHBACK package to perform queries on past data without special clauses such as AS OF or VERSIONS BETWEEN, you can reuse existing PL/SQL code to query the database at times in the past.

10. Networking

1. What background process refreshes materialized views?
Cjqo (co-ordinate job queue)

2. What is the default location for tnsnames.ora and sqlnet.ora files and if you don't find them there, where do you look?
$ORACLE_HOME/network/admin/samples

3. Can we start multiple database services with in one listener service?
Yes

4. How to give password for listener?
By using $lsnrctl

5. Can we have two listeners for 1 database?
Yes

6. Can we have same listener names for two databases?
No

7. What is the password you have to set in the init.ora to enable remote login ?
remote_login_password_file=exclusive

8. How do you connect to db and startup and shutdown the db without having dba group?
Using remote_login_passwordfile

9. What is the environment variable to set the location of the listener.ora?
TNS_ADMIN

10. How do you know whether listener is running or not?
$ lsnrctl status
ps -ef grep tns

11. What need of password file?
Password file provides remote db authentication.

13. What is a runaway session?
You killed a session in the database but it still remains in the os level and vice versa. Its called runaway session. If runaway session is there CPU consumes more usage.

14. What is netstat? And it’s Usage?
It is a utility to know the port numbers availability. Usage: netstat -na grep port number

11. Db Links and Materialized Views

1. Which operations you can perform using database links?

Options :-> DDL or DML

DML

2. What is the object type of Materialized views?

TABLE

3. Which operations are not allowed on Materialized views?

DML

4. Can we manually refresh any materialized view? Using which package

Yes, DBMS_MVIEW

5. Where the Materialized view stored?In database (client side db).this is not a view.it’s read only table.

6. What is Database Link? A database link is a named object that describes a "path" from one database to another.

7. What is snapshot? Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. 8. What are the various types of snapshots?There are three types of materialized views:
Read only materialized view, Updateable materialized view, Writeable materialized view

9. Describe two phases of Two-phase commit?Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
10. What is snapshot log? It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
11. What are the benefits of distributed options in databases? Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit.
12. What are the options available to refresh snapshots?
COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE - Default value. If possible it performs a FAST refresh; otherwise it will perform a complete refresh.

13. What is Distributed database? A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
14. How can we reduce the network traffic?
  Replication of data in distributed environment.
  Using snapshots to replicate data.
  Using remote procedure calls.
15. Differentiate simple and complex? A simple snapshot is based on a query that does not contains GROUP BY clauses,   CONNECT BY clauses, Joins, sub-query or snapshot of operations.
- A complex snapshots contain atleast any one of the above.

16. What dynamic data replication?
Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.

17. What is Network Database link?

Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition. These database links creates a connection between a local database and a remote database.

18. What is Private Database Link?

Private database link is created on behalf of a specific user. A private database link can be used only by the owner.

19. What is function of RECO?
Recover (RECO) is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals,the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

12. Redolog, Controlfile Management

1. What is Archived Redo Log?
Archived Redo Log consists of Redo Log files that have archived before being reused.

2. What is Mirrored on-line Redo Log?
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.

3. What is the use of Control File?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

4. What does a Control file Contain?
A Control file records the physical structure of the database. It contains the following information. Database Name and locations of a database's files and redolog files. Time stamp of database creation.

5. What happens when archive log destination becomes 100% full when the database is running in ARCHIVELOG mode? How do you recover? The database gets shutdown. We should move old archives to different location and startup the database.

6. How do you know whether archive log mode is enabled or not?

Issue command 'archive log list' at sys prompt

7. What is a stale?

The redolog file which has not been used yet

8. How many maximum control files you can create in Oracle database?

What is the error number you get if u tries to create more than 8?

8, ora 208

9. If controlfile crashed, no backup. how to recover?

By recreating controlfile.

10. How do increase the count of datafiles?

Generate the control file syntax from the existing control file and recreate the control file by changing the parameter MAXDATAFILES = yourdesired size

Procedure:

1. Open the database

2. Generate the control file change the maxdatafiles

3. Open the db in nomount

4. Execute the syntax with noresetlogs

5. Alter database open

11. If you want to maintain one more archive destination which parameter you have to set?

It’s a dynamic parameter you have to set log_archive_dest_1=

12. How do you take backup of a controlfile?

Alter database backup controlfille to destination.file will be save in the your destination

13. How do you make your redolog group inactive?

By taking manual logswitch.alter system switch logfile;

14. What is the parameter allows you to create max no. of groups?

In controlfile recreation script , maxlogfiles=

15. How to create a trace file? “Alter database backup controlfile to trace;”

16. How do you rename a Database? -Alter System switch log file.

-Alter database backup control file to trace.

-Shutdown.

-Edit control file

Create control file reuse database 'oldname' resetlogs to create control file set database 'newname' resetlogs

remove the line stating recover database using backup controlfile.

-change the init.ora file.

-change TNS_names.ora

17. Can we rename the redologs? If yes, in which stage? In DB up or down?

Yes,

If inactive ------------- open stage

If current -------------- cannot rename in open state

18. What is a trace file and how is it created?
Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
19. How to implement the multiple control files for an existing database?  Shutdown the database
Copy one of the existing control file to new location
  Edit init.ora file by adding new control filename
  Restart the database.

20. How do you know how much archives are generated?

Using the view v$log_history

13. Spfile and OMF

1. Where do you get all hidden parameters?

In the table x$ksppi

Sys>select ksppinm,ksppdesc from x$ksppi;

2. How do you know whether the parameter is dynamic or static?

Check the value from the column ISINSTANCE_MODIFIABLE from V$PARAMETER.

3. Which parameters are to by set in parameter file to create database for OMF?

db_create_file_dest =

db_create_online_log_dest_1 =

4. Data dictionary views are dynamic or static?

Static

5. Is it possible to change the Instance name after creating database?

Yes

6. If you delete the alert log file what will happen?

New alert log will be created automatically after database startup.

7. What are the different Parameter types? Text Parameters, Data Parameters

8. Can we start database with pfile when both spfile and pfile present in dbs directory?Yes, Startup pfile=’$ORACLE_HOME/dbs/init$ORACLE_SID.ora’

9. From which version OMF got introduced?Oracle9i first introduced a new feature that simplifies tablespace creation. This new feature, Oracle Managed Files (OMF), makes life easier for OracleDBAs by removing the tedium from creating and managing Oracle data files.

10. Disadvantages using OMF?The major disadvantage of using OMF can be boiled down to:

1. non-human-sensical names

2. Oracle related bugs (*gasp*)

11. What is the difference between the values ‘DEFERRED’ and ‘IMMEDIATE’? DEFERRED means changes reflected from next connecting session .IMMEDIATE means changes reflected to all connected as well as connecting sessions.

14. Logical Backups

1. How do you perform Compression with EXPDP?

EXPDP itself will now compress all metadata written to the dump file and IMPDP will decompress it automatically—no more messing around at the operating system level.

Give Compress=Y in the parameter file of export file.

2. What is the use of INCTYPE option in EXP command?

Inctype export should be performed COMPLETE, CUMULATIVE, INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into a notion Completes. e. will be written.

3. What is the use of FILE option in IMP command?

The name of the file from which import should be performed.

4. What is the use of FULL option in EXP command?
It is helpful in taking full database export

5. What is the use of GRANT option in EXP command? It is a flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.

6. What is the use of INCTYPE option in EXP command? It is a type of export which performs OMPLETE, CUMULATIVE, INCREMENTAL

7. What is the use of TABLES option in EXP command?Tables – Indicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.

8. What is the use of ROWS option in EXP command?

Rows – [Y] Indicates whether or not the table rows should be exported at platform. The highest value is 64KB.

9. What are the different kinds of export backups?

Full back - Complete database

Incremental - Only affected tables from last

Incremental date/full backup date.

Cumulative backup - Only affected table from the last

Cumulative date/full backup date.

10. What is the use of PARFILE option in EXP command?
Parfile – The name of the file that contains the export parameter options. This file can be used instead of specifying all the options on the command line for each export.

11. What is the use of INDEXES option in IMP command?
Indexes – [Y] Determines whether indexes are imported.

12. What is the use of OWNER option in EXP command?
Owner – Only the owner’s objects will be exported.

13. What is the use of ROWS option in IMP command?
Rows – [Y] Indicates whether or not the table rows should be imported.

14. What is the use of INDEXES option in EXP command?
Indexes – [Y] Determines whether index definitions are exported. The index data is never exported.

15. What is the use of CONSISTENT (Ver 7) option in EXP command?

Consistent – [N] Specifies the set transaction read only statement for export, ensuring data consistency. This option should be set to “Y” if activity is anticipated while the exp command is executing. If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

16. What is the use of RECORD LENGTH option in EXP command? Record length – Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. If not specified, this parameter defaults to the value specific to that platform. The highest value is 64KB.

17. What is the use of FILE option in EXP command?File – The name of the export file. Multiple files can be listed, separated by commas. When export fills the filesize, it will begin writing to the next file in the list.

18. What is compress parameter? When u use it?Compress – When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be used. Although this option is only implemented on import, it can only be specified on export.It reduses the size of backup

19. If you have exported full database using EXP utility and while importing can I import only one table from full exported file?Yes

20. How do you perform Compression with EXPDP?

Using compress=y parameter

15. Physical Backup

1. Can Full Backup be performed when the database is open?
No.

2. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?

Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

3. How do you restore and recover a datafile while the database is up and running?

Make the datafile offline, restore and recover datafile, make the datafile online;

4. Can you take COLD backup while database is running?

5. HOT backup is consistent backup or inconsistent backup?

Inconsistent

6. Can you take logical backups in mount stage?

7. How do you know whether the specific tablespace is in begin backup mode?

Select status from v$backup. if it is active it means it is in begin backup mode

8. When will you take Cold back up especially?

During up gradation and migration

9. What are the modes/options in incomplete recovery?

Cancel based, change based, time based

10. How do you applying archive logs to cold backup of previous day?

Steps involved in recovery are:

- restore cold backup

- Startup mount

-recover database using backup control file until cancel

-alter database open resetlogs

-shutdown

-startup

11. What is hot backup and how it can be taken?

The database which is 24/7 those databases are never shutdown. Such databases backup should be taken when database is running on.This type of physical backup is called as Hot backup.

Steps to take Hot backup.

1 Begin backup

2 Cp *.dbf <Backup location>

3 End backup

4 Log switch

5 Take controlfile backup with sql statement.

12. What is cold backup?

Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.

1. All Data files.

2. All Control files.

3. All on-line redo log files.

4. The init.ora file (Optional)

13. What is Partial Backup?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

14. Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

15. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

6. RMAN

1. Which role you grant to rman user while configuring rman User? Recovery_catalog_owner role.

2. Temporarily recovery catalog database (for RMAN) is down. Can you still run a backup? How?

Yes, using no catalog mode

3. You have run a backup of database using RMAN nocatalog. How do you sync the recovery catalog with the metadata about the backup that was taken?

Resync catalog

4. What is compress parameter?

It reduces the size of backup

5. With our catalog can we connect 2 target databases at a time?

Yes …we can…

6. We have rman backup. can we restore it without using rman and run the Db?

7. Is it needs to take bkp of catalog db?

Not mandatory...

8. Why RMAN scripts not work in no catalog mode?

Rman scripts stored in a catalog. but in no catalog mode we not creating catalog. So no scripts.

9. What use of command line parameter cmd file?
It is a command line argument that allows you to specify a file that contains a set of arguments for run.

10. What is the use of nohup?
The execution of a specific task is performed in the server side with out any interrupting Usage : nohup cp -r * /tmp/. &

11. How do u set crontab to delete 5 days old trace files at daily 10'0 clock?

crontab –e 0 10 * * * /usr/bin/find /disk2/oradata/prod/diag/rdbms/prod/prod/trace -name "*.trc" -mtime +5 -exec rm -rf {} \; save and exit (wq!)

12. From RMAN, how do you check if an archive log file is backedup to tape or not?
Report need backup;

13. How do you send the data to tape?
Using 1. tar -cvf or 2. cpio

14. How do you enable/disable debugging mode in unix?
Set -x and set +x

15. How to configure RMAN?

Configuring RMAN can be done two types. Using catalog and with out using catalog. If you are using catalog it you will have to specify a separate tablespace for the catalog. This will remove the overhead on control file maintaining all the info in it.

16. How to configure RMAN on tape drives (VTL)?

There is a parameter called device_type just give it as sbt and specify the tape location.

17. RMAN improvements

Oracle10g comes with a just a ton of improvements (I like that word, plethora!) for RMAN. These include the following:

1. The Flash Recovery Area

2. Fast Recovery

3. Using the catalog and uncatalog commands

4. Dropping a database in RMAN

5. Unregistering a database in RMAN

6. Making and Using RMAN backup copies

7. Configuring default disk backup types.

8. Changes to incremental backups.

9. Recovering datafiles not backed up.

10. Changes in error reporting.

11. Compressing RMAN Backups

12. RMAN Related TSPITR changes.

18. What is difference between OBSOLETE and EXPIRED?

Obsolete backups mean inconsistency backups for recovery purpose. Expired backups means those backups physical files are not available on disk.

19. How speed up backup jobs in Rman?

By increase number of channels.

17. Performance Tuning

1. What is performance Tuning?

Performance tuning is the term used to improve the performance of a C.P.U to increase the speed of response time with minimum resource.

2. Why and when should one tune?One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.
One should do performance tuning for the following reasons:
1.The speed of computing might be wasting valuable human time (users waiting for response);
2.Enable your system to keep-up with the speed business is conducted; and
3.Optimize hardware usage to save money (companies are spending millions on hardware).

3. What database aspects should be monitored?One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracle’s Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.

Infrastructure availability: • Is the database up and responding to requests
                                         • Are the listeners up and responding to requests
                                         • Are the Oracle Names and LDAP Servers up and responding to requests
                                         • Are the Web Listeners up and responding to requests

Things that can cause service outages:• Is the archive log destination filling up?
                                                             • Objects getting close to their max extents
                                                             • Tablespaces running low on free space/ Objects what would not be able to extend
                                                             • User and process limits reached

4. What tuning indicators can a DBA use?The following high-level tuning indicators can be used to establish if a database is performing optimally or not:
1 Buffer Cache Hit Ratio
Formula: Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads
Action: Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to increase hit ratio
2Library Cache Hit Ratio
Action: Increase the SHARED_POOL_SIZE to increase hit ratio

5. What are the values that can be specified for OPTIMIZER MODE Parameter?All_rows,rule,first_rows_1000,first_rows_100,first_rows_10,first_rows_1,choose,first_rows.

6. When you enable tracing for a SQL statement, where do you look for the trace files?

In 11g /disk1/oradata/prod/diag/rdbms/db_name/instance_name/trace In 10g /disk1/oradata/prod/udump

7. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

8. What are the different approaches used by Optimizer in choosing an execution plan?
Rule-based and Cost-based.
9. What are the values that can be specified for OPTIMIZER MODE Parameter?
All_rows and rule.

10. What is COST-based approach to optimization?
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes.

11. What is RULE-based approach to optimization?
Choosing an executing plan based on the access paths available and the ranks of these access paths.

12. Diff between Production, Development& QA database?

PRODUCTION database is currently using by end users.

Development database using by developers.

13. Diff b/w patching and upgrading?
Patching for solving any bugs in the database.
Up gradation for changing versions and release no's

14. How do you check the locks in the database and determine if there is any deadlock issue?

Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes. The following script can be used to identify deadlocks in the database. The query depends upon objects that are created by the script ORACLE_HOME/rdbms/admin/dbmslock.sql. Log on as SYS or with SYSDBA authority and run this script in all databases

15. Which view is used to see dead locks?

v$lock

v$session

v$parameter

16. How does u know when system was last booted?

3 ways

1.uptime cmd

2.top

3.who -b

20. How do u know load on system?

1. W

2. Top

21. How do you know when the process is started?

Using ps -ef| grep process name

22. What type of default optimizer does 10g use?

Oracle 10g uses Cost Based Optimizer.

8. Application Tuning

1. What is Parallel Server?
Oracle Parallel Server is a robust computing environment that harnesses the processing power of multiple, interconnected computers. Oracle Parallel Server software and a collection of hardware known as a "cluster", unites the processing power of each component to become a single, robust computing environment. A cluster generally comprises two or more computers, or "nodes".(i.e) Multiple instances accessing the same database (Only In Multi-CPU environments)

2. What is mean by Program Global Area (PGA) ?

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. it typically contains a sort area, hash area, session cursor cache, etc.

3. How would you generating an EXPLAIN plan?

It is pre execution plan .If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

Usage:explain plan into table_name for your-precious-sql-statement;

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

4. Can you enable trace for a session?

Yes. We can enable SQL trace for a session using “ALTER SESSION SET sql_trace=TRUE”. But it is not advisable as it is a performance issue. It has to be used only when you want to trace a session to monitor performance related issues and then stop it.

5. What is the parameter to set the user trace enabling?

Sql_trace = true

6. How to create a trace file?

Set sql_trace=true

7. When 100 users connect to database,Hw u see which statement is taking long time and which statement is doing physical reading in Performance Tuning?

By using explain plan or TKPROF

8. What is Explain Plan? When do we take it?

Explain plan tells us how the query is being executed, whether it is using index or not if so what kind of index, how many loops are being used, what is the cost of each line in the SQL query, total cost involved, estimated rows returned, estimated KB returned, types of joins used and stuff like that.

9. What is the cache hit ratio, what impact does it have on performance of anOracle database?For the buffer cache hit ratio, it calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.

10. How does u improve the performance of Report program?There are having in so many ways.
1) You can use the sort after Declare the buffering in Read statement.
2) You don't using inner joines You can use for all entries.
3) Maintain the Work area.
4) Maintain the Variables

11. What is the use of tkprof and how to generate it?
Tkprof is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and execution plan.

12. What would you do to increasing the buffer cache hit ratio?
If the hit ratio is below 90%, and the dictionary cache has been tuned, increase the init.ora parameter DB_CACHE_SIZE to increase the buffer.

13. What is hit ratio?
It is a measure of well the data cache buffer is handling requests for data. It is a percentage of available and Non-available of data blocks in any memory component to increase performance.

14. What are hints in Oracle? HINTS are nothing but the comments used in a SQL statement to pass instructions to the Oracle optimizer.The optimizer uses these hints to an execution plan for the statement.

19. Database Tuning

1. How do you disable monitoring of a table?

Alter table tablename no monitoring

2. How do you enable monitoring of a table?

Alter table tablename monitoring

3. How do you find the files whose are more than 500k?
fnd . -name "*" -size +500k

4. What is a Parallel Server option in ORACLE?

Oracle Parallel Server is a robust computing environment that harnesses the processing power of multiple, interconnected computers. Oracle Parallel Server software and a collection of hardware known as a "cluster", unites the processing power of each component to become a single, robust computing environment. A cluster generally comprises two or more computers, or "nodes".

5. What does ADDM do?

Oracle10g offers more automatic mechanisms for rudimentary SQL tuning. The AWR tables allow Oracle10g to collect and maintain detailed SQL execution statistics, and this stored data is then used by the Advanced Database Diagnostic Monitor (ADDM, pronounced ‘adam’). ADDM attempts to supply a root cause analysis along with recommendations on what to do to fix the problem. An ADDM output might contain information that there is read/write contention, a free list problem, or the need to use locally managed tablespaces.

ADDM can identify high load SQL statements, which can, in turn, be fed into the SQL Tuning Advisor below. ADDM automatically detects common performance problems, including:

1. Excessive I/O

2. CPU Bottlenecks

3.Contention Issues

4. High Parsing
5.Lock Contention
6.Buffer Sizing Issues
7.RAC Tuning Issues
Creating a new snapshot with information populated in dba_hist_snapshot:

exec dbms_workload_repository.create_snapshot(); The addm_rpt.sql script can be used to view the output of the snapshot.

6. What is PGA?
A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total memory used by all individual PGAs is known as the total instance PGA memory, and the collection of individual PGAs is referred to as the total instance PGA, or just instance PGA.

7. Daily routine of dba
(1). Day running of the ORACLE database, log files, backup situation, the database space usage, the use of system resources to inspect, identify and solve problems.
(2). Every space on the expansion of the database objects, data growth monitoring, health checks done on the database, the state of the database objects for check-ups.
(3). Monthly tables and indexes, etc. Analyze, check the list of space debris, looking for opportunities forperformance tuning the database, the database performance tuning, space management plan proposed by the next step. ORACLE database on the state to conduct a comprehensive inspection.
Daily work

(1). Make sure all the INSTANCE state normal landing to all databases or routine testing ORACLEbackground process:
$ Ps-ef | grep ora
(2). Check the file system using the (free space). If the file system free space is less than 20%, need to delete unused files to free space. $ Df-k
(3). Check the log files and trace files record alert and trace files for errors.

(4). Check the validity of the database using rman utility .

(5). Check the state of the record data file is not "online" data file and do recovery.
Select file_name from dba_data_files where status = 'OFFLINE'
(6). Monitor database performance running bstat / estat report generation system or use statspack to collect statistical data

(7). Inspection database performance, records database, cpu use, IO, buffer hit ratio, etc. to use vmstat, iostat, glance, top, etc. command

20. Memory Tuning

1. Who is using which UNDO segment?
Execute the following query to determine who is using a particular UNDO or Rollback Segment:

SQL> SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, 'None') orauser,s.program,r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t, sys.v_$parameter x

WHERE s.taddr = t.addr

AND r.usn = t.xidusn(+)

AND x.name = 'db_block_size'

SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo

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

260,7 SCOTT sqlplus@localhost.localdomain _SYSSMU4$ 8K

(TNS V1-V3)

2. Where can one find the high water mark for a table?

There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:

SELECT BLOCKS FROM DBA_SEGMENTS

WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

ANALYZE TABLE owner.table ESTIMATE STATISTICS;

SELECT EMPTY_BLOCKS

FROM DBA_TABLES

WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

3. Define the SGA?
System Global Area.It consists of Shared pool, Large pool, Java pool, Buffer cache, Log buffer, Nonstandard block size buffer caches, Keep and recycle buffer caches, and Streams pool.

4. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?Ipcs
SQL> oradebug setmypid
SQL> oradebug ipc
SQL>oradebug tracfile_name

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

6. What are SGA_TARGET and SGA_MAX?SGA_Target is the amount SGA that is used by an instance. If this parameter is set in initialization parameter file then ASMM (Automatic shared memory management) is done where the buffer cache, Stream pool, Java pool size, Shared pool size and large pool are managed by Oracle.

SGA_MAX is the Maximum possible size of SGA allowed when you enable ASMM. SGA_MAX cannot be changed dynamically. If you raise the SGA_target to more that SGA_MAX you will get error.

7. What is different initialization parameters related to tuning?

Some of the parameters that effect performance are DB_CACHE_SIZE, SGA_MAX,PGA_AGGREGATE_TARGET, SHARED_POOL_SIZE, and SGA_TARGET when you use ASMM.

8. Name the parts of the database buffer cache.The database buffer cache consists of the keep buffer cache, recycle buffer cache, and the default buffer cache.The keep buffer cache retains the data block in memory.The recycle buffer cache removes the buffers from memory when it’s not needed.The default buffer cache contains the blocks that are not assigned to the other pools

9. Which memory structures are shared? Name two.The library cache contains the shared SQL areas, private SQL areas, PL/SQL procedures, and packages, and control structures. The large pool is an optional area in the SGA.

10. What is the maximum number of database writer processes allowed in anOracle instance?

The maximum is 20. Every Oracle instance begins with only one database writer process, DBW0. Additional writer processes may be started by setting the initialization parameter DB_WRITER_PROCESSES.

21. Network Tuning

1. What is Parallel Server?

Multiple instances accessing the same database (Only In Multi-CPU environments)
2. Describe a parallel server configuration.

In a parallel server configuration multiple instances known as nodes can mount one database. In other words, the parallel server option lets you mount the same database for multiple instances. In a multithreaded configuration, one shared server process takes requests from multiple user processes.

3. If you want to configure shared servers which three parameters you need to specify in init.ora file?

LOCAL_LISTENER, SHARED_SERVERS ,DISPATCHERS.

4. What is the function of Dispatcher (Dnnn) ?

Dispatcher (Dnnn) process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes

5. How many Dispatcher Processes are created ?
At least one Dispatcher process is created for every communication protocol in use.

6. View to see how many dispatchers are created by dba in database?
V$dispatchers

7. What are the Disadvantages of dedicated servers?
While using a dedicated server is ideal in many situations, there are also some disadvantages for those who choose this option.One of the biggest disadvantages to using a dedicated server is the cost. While most hosting packages that make use of shared servers are relatively inexpensive, purchasing a hosting package that is not shared is very costly.For users that are not the most technically savvy webmasters, sometimes a dedicated server can be more than they can handle. Another disadvantage to dedicated hosting is the lack of free scripts and other additional features that those on a shared server have access to. Most web hosts offer these preinstalled on their shared hosting packages, but leave them off the dedicated servers.

8. Disadvantages of shared servers?

1. Security issues:

2. Limited Resources:

3. Dynamic IP:

4. Not good For Large Data Base E-Commerce Sites:

9. Advantages of shared servers?

1. Cheap Cost or Affordable Price:

2. No Maintenance Cost:

3. Fast Setup:

4. Good For Small Sites:

10. What is a dispatcher?

DISPATCHER configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner.

22. Installation

1. What is SHMMAX in /etc/system file of Solaris?
Set shmsys:seminfo_shmmax=4294967295

2. What happens when root.sh is run?
Permissions of oracle home dir is changed.

3. Can one user install different oracle software versions like 8i,9i,10g?
Yes

4. It possible to install different oracle software on one operating system?
Yes

5. Can root user install Oracle S/W?
No

6. Can you create multiple databases using one operating system login id?
Yes

7. What is difference between 32-bit and 64-bit O/S?

The terms 32-bit and 64-bit refer to the way a computer's processor (also called a CPU), handles information. The 64-bit version of Windows handles large amounts of random access memory (RAM) more effectively than a 32-bit system.

8. Compatible parameter using for oracle version .it is giving in parameter file?

This parameter allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release (in case it becomes necessary to revert to the earlier release).it is given in pfile.

9. How do you kill a session from the database?

Alter system kill 'sid,serial#'

Usage : alter system kill '9,8' (Get the info from v$session)

10. In which file oracle inventory information is available?

/etc/oraInst.loc

11. Where do you configure your hostname in linux?

vi /etc/hosts

12. How do you check the Solaris Server configuration like amount of physical memory, number of CPUs?

Prtdiag

13. If our $O_H filled up with core files,what actions we will take?

Remove all old core files and create space in the filesystem.

14. Where do you get to know the version of your oracle software and what is your version?

From v$version(field is banner) and the version is 9.2.0.1.0

15. Tell me the location of Unix/Solaris log messages stored?

/var/log/messages(Unix)

/var/adm/messages (solaris)

16. Where all oracle homes and oracle sid information available?

/etc/oratab

17. What are Sémaphores?

A semaphore is a value in a designated place in operating system (or kernel) storage that each process can check and then change. Depending on the value that is found, the process can use the resource or will find that it is already in use and must wait for some period before trying again. Semaphones can be binary (0 or 1) or counting.

23 .Oracle 11^g New features

1. What is Assm?

Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused, freelists, and freelist groups’ storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.

2. What is Asmm?

ASMM (Automatic Shared Memory Management) is the collective name for the dynamic memory allocation technologies added in Oracle 9i and improved with each subsequent release. This reduces the amount of manual configuration required and allows the database to adapt to workload changes.

3. What is the use in memory_target parameter?

MEMORY_TARGET provides the following:

1. A single parameter for total SGA and PGA sizes
2. Automatically sizes SGA components and PGA
3. Memory is transferred to where most needed
4. Uses workload information
5. Uses internal advisory predictions

6. Can be enable by DBCA at the time of Database creation.

4. What is the use of Mman?

Mman stands for Memory Manager; it is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases. This process was introduced in Oracle 10g.

5. Oracle 11g new features?

1. Improved data compression ratios (up to 20x).

2. Ability to upgrade database applications while users remain online.

3. New ease-of-use features that make Grid computing more accessible.

4. Automation of key systems management activities

6. What is AWR and ADDM?

AWR (Automatic Workload Repository) is a built-in repository (in the sysaux tablespace) that exists in everyOracle Database. At regular intervals, the Oracle Database makes a snapshot of all of its vital statistics and workload information and stores them in the AWR.

ADDM (Automatic Database Diagnostic Monitor) can be describe as the database's doctor. It allows anOracle database to diagnose itself and determine how potential problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.

7. What is proactive tablespace management system?

The Proactive Tablespace Management (PTM) capability in the Oracle Database 10g brings efficient and powerful space monitoring, notification and space trending to the Oracle Database. Prior to Oracle Database 10g, the tools available for monitoring and setting up notifications regularly polled the database to monitor its space usage. Querying space usage information requires collecting data about the state of the database — state that is constantly changing in a production system. Because such queries are inherently expensive, the space monitoring tools typically run them infrequently, once a day or once every couple of hours. When they are run, the queries steal CPU, IO and memory (especially the buffer cache) resources away from critical business activity in the production system. It’s a health check that is either late or hurts the health of the system or worse, both!

8. What are the advantages of AWR?

Advantages of the new workload repository include:

1. AWR is a record of all database in-memory statistics historically stored. In the past, historical data could be obtained manually using the ‘statspack’ utility. AWR automatically collects more precise and granular information than past methods.

2. With a larger data sample, more informed decisions could be made. The self-tuning mechanism uses this information for trend analysis.

3. Another benefit is that AWR statistics are accessible to external users, who can build their own performance monitoring tools, routines, and scripts.
4. Awr collects database performance identical values from different layers like
1. C.P.U resources utilization
2. Memory utilization
3. Timing statistics
4. Typical executed Queries Latches statistics etc.

24. Data Guard

1. How do you findout the most recent log applied to physical standby?

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

2. Where do you look for Oracle alert log?

/disk2/oradata/prod/diag/rdbms/db_name/instance_name/trace ls alert_instance_name.log

3. What happens if you add a datafile in primary and there is no space on contingency server and how do you resolve it?

Increase disk space

4. How do you add a datafile to a tablespace in a database with physical standby setup? By setting standby_file_management=auto, if a tablespace is created on primary it will be automatically created on standby.

5. How do you troubleshoot if you suspect any problems with archive log shipping to standby database? Check for network configuration and standby archive location specified in primary init.ora

6. How do you open a physical standby database in managed recovery mode in 'READ-ONLY' mode?

Cancel the MRM, and then open the database using sql statement ‘alter database open;’

7. What is the difference between Physical and logical stand by databases?

1. Physical standby can be opened in read only mode, logical standby can be opened in read write mode

2. Redo apply will take place in physical standby and sql apply will be in logical standby

3. MRP process will work for redo apply,lsp process for sql apply

8. What is the state of stand by database; is it open?

Physical standby in read only

Logical standby in read write

9. Can we create logical standby from physical standby?

Yes.

10. What is an oracle stream?

Streams are a 10g feature. It is using for replicating data between two databases

11. How to create a logical standby?

Standby>>alter database recover managed standby database cancel;

Execute dbms_logstdby.build;

Alter database recover to logical standby new_db_name;

Shut immediate

Startup mount

Alter database open resetlogs;

Alter database start logical standby apply immediate;

12. What is Database maintain plan and Disaster Recovery plan?

Disaster recovery plans start with risk assessment. You need to identify all the risks that your data center faces and then determine the business impact should that risk become an event. For instance, typical risks that are considered are: loss of a disk drive, loss of a server, complete loss of your data center, etc.

There are many ways to handle your disasters. You'll have to look at the risks you face. Each risk should have a solution. In some cases, multiple risks have the same solution.

In order to be able to transport tablespaces between platforms, you must meet two criteria. One, you must be running Oracle 10g on both platforms. Two, both platforms must have the same "endianess" or you'll need to perform a conversion step. The endianness refers to the byte ordering on that platform. Solaris is big endian. Windows is little endian. If both platforms were big endian or both were little endian, then you would have no difficulties transporting the tablespaces.

13. What is the state of standby database, is it open?

Physical Standby database is in "Recovery Mode" in order to apply received archive logs from primary database. We can make it to "Read Only" mode to make it available for application users for reporting purposes. Once the database is applied in "Read Only" mode we cannot apply redo logs received from primary database(oracle 10g).

From 11g we can put the database in MRM mode even in open mode ---active standby

Logical standby database is logically identical to the primary database. Logical standby can be in open state and at the same time its tables are updated from primary database.

14. How do you configure a stand by database?

Steps to create standby database:

1. Enable forced logging

2. Create password file if it does not exist.

3. Set primary database initialization parameters

4. Enable archiving if it is not.

5. Create a backup copy of primary database datafiles.

6. Create standby control file.

7. Prepare the initialization parameters for Standby database.

8. Copy all the files to standby database – datafiles, control files, init.ora files

9. Configure Listener on standby.

10. Enable broken connection detection.

11. Create Oracle net service names.

12. Create Spfile for standby database.

13. Start standby database in read only mode.

14. Start redo apply when necessary.

15. Verify standby is working properly.

15. Give me the syntax to create a standby controlfile using RMAN?

Alter database create standby controlfile as ‘/disk2/oradata/prod/dg/stand.ctl’;

16. How do you perform the switch over from primary to standby and standby to primary?

Prod>>select switchover_status from v$database; To_standby

Prod>>alter database commit to switchover to standby;

Standby>>alter database commit to switchover to primary;

Standby>>alter database open;

17. Give me the Top 5 init parameters which need to be setup in the target db in the physical standby config?

Fal_server=to_stand #tns service of prod

Fal_client=to_prod #tns service of standby

Log_archive_dest_1=’location=/disk2/oradata/prod/arch’ #archive log dest of production

Log_archive_dest_1=’service=to_stand lgwr sync affirm reopen=10’

Standby_file_management=auto

Standby_archive_dest=/disk2/oradata/prod/arch

18. What is Failover and Switchover?

Switchover: The switchover feature provides you with the ability to switch the role of the primary database to one of the available standby databases. The chosen standby database becomes the primary database, and the original primary database then becomes a standby database.

Failover: You invoke a failover operation when a catastrophic failure occurs on the primary database and there is no possibility of recovering the primary database in a timely manner. During a failover operation, the failed primary database is removed from the Data Guard environment, and a standby database assumes the primary database role. You invoke the failover operation on the standby database that you want to fail over to the primary role.

19. Can you create clone database using HOT backup?

Yes

20. Without password file can we configure standby database?

25. Partitioning

1. What are the different types of partitioning?

In Oracle you can partition a table by

•Range Partitioning

•Hash Partitioning

•List Partitioning

•Composite Partitioning

2. How do you check the status of the table whether monitoring or not?

Select table_name, monitoring from dba_table where owner='scott;

3. In which table records of monitoring will be stored?

dba_tab_modificaitons

4. How can you rebuild an index?
ALTER INDEX <index_name> REBUILDS;

5. Explain what partitioning is and what its benefit is.
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

6. What is single partition transport?

The Transportable Table spaces mechanism for Data Pump could be used to specify only the physical tablespaces to be exported. This feature adds a partition mode, which can be used to move one or more partitions or subpartitions of a table without having to move the entire table or exchange out the partition or sub partition.

. Why Use Partitioning?

1. Continued data availability with partial failures.

2. Simplified data disk placement.

3. Scalable Performance with substantial growth in data volumes.

8. How to move the partition from one TS to other TS? Using Command?

Alter table<tablename> move partition <partition name> tablespace <tablesapce name>

9. How to add a new partitioning to an existing partition table?

Alter table <tablename> add partition<partition name> values less than (maxvalue) tablespace <tablespace name>

10. How to merge two partitions into one using command?

Alter table<tablename> merge partition<partition name> into partition<partition name> tablesapce<tablespace name>

11. How to rename existing Partition using command?

Alter table <tablename> rename partition<old partition> to <new partition>

12. Dropping Partition

Alter table <tablename> drop partition <partition name>

13. How to creating a local index on a range partitioned table using command.

Create Index <indexname> on table <deptno> local;

Indexes

1. What is an 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.

2. What are the types of indexes?

There are five types of indexes: unique and non-unique indexes, and clustered and non-clustered indexes, and system generated block indexes for multidimensional clustered (MDC) tables.

3. What is Local Index?

Local Partitional Index is easier to manage and each partition of local indexes are associated with that partition.

4. Types of Global Indexes?

*Global Non-Partitioned index.

*Global Partitioned Index.

5. What is Global Index?

Global Index used in OLTP environments and offer efficient access to any individual record.

6. How can you rebuild an index?
ALTER INDEX <index_name> REBUILDS;

7. What are virtual indexes?

Oracle Virtual Indexes are another undocumented feature used by Oracle. Virtual indexes, as the name suggests are pseudo-indexes that will not behave the same way that normal indexes behave, and are meant for a very specific purpose.A virtual index is created in a slightly different manner than the normal indexes. A virtual index has no segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry for this.
Oracle handles such indexes internally and few required dictionary tables are updated so that the optimizer can be made aware of its presence and generate an execution plan considering such indexes. As perOracle, this functionality is not intended for standalone usage.

8. What are bitmapped indexes? What is btree index?

Tree indexes are usually associated with the index that stores a list of ROWIDs for each key. While Bitmap is also organized as B tree but the leaf node stores a bitmap for each value instead of a list of ROWIDs. B tree can be used for OLTP while bitmap is used for data warehousing system.

9. What are invisible indexes?

An invisible index is an alternative to making an index unusable or even to drop it. An invisible index is maintained for any DML operation but is not used by the optimizer unless you explicitly specify the index with a hint.

Applications often have to be modified without being able to bring the complete application offline. Create invisible indexes temporarily for specialized non-standard operations, such as online application upgrades, without affecting the behavior of any existing application. Furthermore, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.

26. Row Migration & Row Chaining

1. What is the use of ANALYZE command?To perform one of these function on an index, table, or cluster: - To collect statistics about object used by the optimizer and store them in the data dictionary. - To delete statistics about the object used by object from the data dictionary. - To validate the structure of the object.. - To identify migrated and chained rows off the table or cluster.

2. What is Row Chaining and Row Migration?

Row Changing:: Row changing occurs when a row is too large to fit into a single empty data block. When this occurs, Oracle will spread the row across as many blocks as are needed. Changing the row from one block to the next.
Row Migration:: Row migration occurs when a row is updated and it will not fit back into the original block. When this happens, the Oracle server attempts to find another block that the entire row can be moved to. If it does find a block, the entire row is moved to the new block. The row piece(head ROWID) remains in the original block, with original ROWID and points to the migrated rowin the new block.

3. What is resumable tablespace?

A resemble statement allow you to:
1. Suspend large operations instead of receiving an error
2. Allows you to fix the problem while the operation is suspended, rather than start from scratch.

4. What is the parameter which helps us to enable resumable?

Resumable_timeout

5. When does resumable tablespace gets suspended?

A session remains suspended for the following reasons
1. Tablespace runs out of space
2. Max extents reached.
3. Space quota exceeded on tablespace.

6. Query to know information about suspended sessions?

Select a.username, a.sid, a.serial#, b.status
, b.timeout, b.suspend_time from v$session a
, dba_resumable b where a.sid = b.session_id and
a.user# = b.user_id;

7. Is resumable a privilege or a role?

Resumable is a Privilege.

8. How to know resumable time?

SQL> select dbms_resumable.get_timeout from dual;

9. Performance degradation is more for row chaining or row migration?

Row migration.

10. How to avoid Chained and Migrated Rows?

Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates. If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.

27. Log Miner

1. How to find out a record last updated?

This can done using logminer utility.

2. What is log miner?

Oracle Log Miner, which is part of Oracle Database, enables you to query online and archived log files through a SQL interface. Because redo log files contain information about the history of activity on a database.If there were a small number of transactions that required rollback,prior to Logminer utility. you would have to restore the table to earlier state and apply archived logfiles to bring the table forward to just before the corruption when restoring the table and applied the archived log files, you would have risk losing later transaction that you would like to retain.you can now use logminer to rollback only those trasactions without losing any transactions.

3. From which version logminer has started?

Oracle 8i

4. Benefits of using logminer?1.Determine what actions you should have to take to do fine-grained recovery at the transaction level. If you fully understood and take into an account existing dependencies, it may be possible to perform a table-specific undo operation to return the table to its original state.
2.Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts.
3..Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them.

5. From where we can get archivelog contents which logminer has digged?Query theV$LOGMNR_CONTENTS view.

6. What is the usage of mine_value? The following usage rules apply to the MINE_VALUEand COLUMN_PRESENT functions:

1. They can only be used within a Log Miner session.

2. They must be invoked in the context of a select operation from the V$LOGMNR_CONTENTSview.

3. They do not support LONG, LONG RAW, CLOB, BLOB, NCLOB, ADT, or COLLECTIONdatatypes.

7. What is the parameter which is used for logminer?

UTL_FILE_DIR = /oracle/database

8. Logminer configuration?

There are three basic objects in a Log Miner configuration that you should be familiar with: the source database, the Log Miner dictionary, and the redo log files containing the data of interest:
The source database is the database that produces all the redo log files that you want Log Miner to analyze.
The Log Miner dictionary allows Log Miner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.
Log Miner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, Log Miner returns internal object IDs and presents data as binary data.

9. What are Log Miner dictionary options?

Using the Online Catalog: Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.

Extracting a Log Miner Dictionary to the Redo Log Files: Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

Extracting the LogMiner Dictionary to a Flat File:This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.

10. Views related to logminer?

Once Log Miner is started, the contents of the logfiles can be queried using the following views:

1. V$LOGMNR_DICTIONARY - The dictionary file in use.

2. V$LOGMNR_PARAMETERS - Current parameter settings for Log Miner.

3. V$LOGMNR_LOGS - Which redo log files are being analyzed.

4. V$LOGMNR_CONTENTS - The contents of the redo log files being analyzed.

28. Sql Loader Auditing

1. What is Auditing?
Monitoring of user access to aid in the investigation of database use.

2. What is the default destination where Oracle creates audit trail files automatically when you login as a sysdba?

$ORACLE_HOME/rdbms/audit

3. A table deleted by a user, how u come to know which user was deleted? If he deleted alert log file also how u come to know?

By using AUDITING.

4. What are the different Levels of Auditing?Statement Auditing, Privilege Auditing and Object Auditing.
5. What is Statement Auditing?Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects6. What are the database administrator’s utilities available?SQL * DBA - This allows DBA to monitor and control an ORACLE database. SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables.
Export (EXP) and Import (imp) utilities allow you to move existing data in ORACLE
format to and from ORACLE database.
7. What is Privilege Auditing?Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
8. What is Object Auditing?Object auditing is the auditing of accesses to specific schema objects without regard to user.

9. What is the difference between SQL loader and exp/imp?

SQL is most used for ETL purpose and is mostly used for loading data from Non Oracle Databases. Exp/Imp is an oracle tool used of for moving data from oracle database to another oracle database.

10. Oracle 11g new feature in auditing level?Oracle Audit Vault is a new feature that will provide a solution to help customers address the most difficult security problems remaining today, protecting against insider threat and meeting regulatory compliance requirements.

11. Is it Mandatory to mention log file in Sql Loader?

No. Once you load data then automatically logfile will be created

12. How can you skip some records in Sql Loader?

By Using Skip Parameter

13. A file consists of 10 records. How can you load 5^th and 6^th records.

Sqlloader control=<controlfilename> skip=4 load=2

14. What a bad file contains?

Oracle rejected records

15. Can you insert some records for a non empty table?

No. It is not possible to insert the table should be empty

16. What is infile *?

* Indicates the data is present within the controlfile.,

29. Automatic Storage Management

1. What is ASM?

ASM is one file system which will build by an oracle on any raw disk for storing oracle database files including datafiles, redologs, Backups, controlfiles and spfiles. ASM allows administrators to add and remove disks while the database is on-line and available to users. And also DBA can manage storage of database with redundant technology. Data is automatically striped across all disks in a diskgroup and is optionally mirrored.

2. What are disadvantages of having raw devices?One single raw device storage space is completely dedicated to only any one datafile or any one redolog or to any one controlfile. The tar command cannot be used for physical file backup; instead we should use dd command.

3. What is advantage of having disk shadowing/ Mirroring? Shadow set of disks save like a backup for the safe-side of disk failure. In most Volume Manager if any disk failure occurs it automatically switches over to a working disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

4. It is possible to use raw devices as data files and what are the advantages over file system files? Yes. The advantages over file system files. I/O will be improved and database performance will increase.

5. What are ASM related init.ora parameters?

ASM_DISKGROUPS

ASM_DISKSTRING

ASM_POWER_LIMIT

ASM_PREFERRED_READ_FAILURE_GROUPS

DB_CACHE_SIZE

DIAGNOSTIC_DEST

INSTANCE_TYPE

LARGE_POOL_SIZE

PROCESSES

REMOTE_LOGIN_PASSWORDFILE

SHARED_POOL_SIZE

6. How many database instances can be handled by one ASM instance?

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).

7. How many disk groups should one have?One should have only one disk group for all database files - and, optionally a second for recovery files. Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).

ORACLE Apps DBA & ORACLE DBA Blog

Friday 8 January 2016

Oracle DBA Interview Questions