Interview Questions

1. How do you move the tablespaces or datafiles from one server to another server with archivelog mode.
2. How do you copy database from production to new server using RMAN.
3. Session hangs, user unable to connect the database, what is your analysis and how do you resolve it.
4. Archive destination is full 100%,database hangs and configured RMAN, how do you resolve the issues.
5. What is LOCAL LISTENER, how does it work.
6. You have created one index on Primary database, how do you check whether it is avaliable on Standby database.
7. If you change SYS password on Primary database, will standby database work.
8. When you fire (DML) statement, what is doing oracle archicture.
9. If you add datafiles on primary database, what will happend on standy database. What parameter is required and What will happend if you set AUTO or Manual
10. How do you apply critical patch.

How to shrink sgement in Oracle10g

In Oracle10g, we have the option to shrink a segment, which will help DBAs to manage the space in better way.This feature also help for better performance for query.

Oracle 10g Segment shrink
=========================
Mandatory
=========
Init.ora parameter 'Compatible' must be >=10.0
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).

How it works
============
1. Enable row movement for the table.
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

2. Shrink table but don't want to shrink HWM (High Water Mark).
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

3. Shrink table and HWM too.
SQL> ALTER TABLE scott.emp SHRINK SPACE;

4. Shrink table and all dependent index too.
SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

5. Shrink table under MView.
SQL> ALTER TABLE table_name SHRINK SPACE;

6. Shrink Index only.
SQL> ALTER INDEX table_name SHRINK SPACE;

Restrictions on the shrink_clause, 10gR1
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

2. Segment shrink is not supported for LOB segments even if CASCADE is specified.

3. Segment shrink is not supported for tables with function-based indexes.

4. This clause does not shrink mapping tables or overflow segments of index-organized tables, even if you specify CASCADE.

5. You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

6. Table with a domain index is not supported.

Restrictions on the shrink_clause, 10gR2
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

2. Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

3. This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.

4. You cannot specify this clause for a compressed table.

5. You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

6. Table with a domain index is not supported.

Query/DML Concurrency
=======================
The online phase of segment shrink is done with DML-compatible locks. Hence DMLs can coexist during this phase. During the space-release/HWM adjustment phase, incompatible locks will be acquired on the table, hence, DMLs will block on shrink.

There are no user visible errors that shrink will cause on DMLs.

Queries cache the segment HWM. Oracle guarantees that the HWM always moves forward,hence CR is not required on segment header and extent map blocks. The only operationsthat cause the segment HWM to move backward are drop and truncate.

We allow queries to coexist with drop/truncate DDLs since queries do not acquire locks. If after the drop/truncate, the space gets reused in some other segment, then the queries get "8103 - object does not exist" external error message.

During segment shrink, the segment’s incarnation number is changed in the bitmap blocks and segment header when the segment HWM is adjusted. Subsequent data block news happen at this newer incarnation number.

Queries that span this phase can die with an external error "10632 - invalid rowid" if

1) They read the updated bitmap blocks (that have new inc#). Note that this failure happens if the space is not reused

2) The space got reused for some other object or the same object.

Limitations on Online Segment Shrink, 10gR2:
============================================
Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
- IOT mapping tables- Tables with rowid based materialized views- Tables with function-based indexes
Dependency Maintenance and Restrictions on Shrink
==========================================
The only dependency that will be taken care of during segment shrink is the index. The indexes will not be in an unusable state after shrink.

The compaction phase of segment shrink will be done as insert/delete pairs. The DML triggers will not be fired during data movement phase. Since the data does not change, it is not required to fire the triggers.

ROWID based triggers should be disabled before issuing a shrink since it will not fire during shrink.

Segment shrink cannot be done on objects with on-commit materialized views. Materialized views that are based on primary key need not be refreshed or rebuilt after shrink.
However, it is the DBA’s responsibility to refresh/rebuild the materialized views that are rowid based.

Availability
============
Segment shrink is done online, thereby it increases the availability of the object. While conventional DML operations can coexist with segment shrink, parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase. During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.
However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

Security
========
The privileges required to execute segment shrink on an object will be the same as that for ALTER object.

Detail Example
===============

SQL> ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
Table altered.

SQL> ALTER TABLE t_shrink SHRINK SPACE COMPACT;
Table altered.

SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = 'v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = 'v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = 'v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = 'v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = 'v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = 'v_full_blocks);
24 end;
25 /


Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 1
PL/SQL procedure successfully completed.



SQL> ALTER TABLE t_shrink SHRINK SPACE;
Table altered.

SQL> run the above anonymos block again,

Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 1
PL/SQL procedure successfully completed.


Reference:
QL Reference Manual, 10.1 and 10.2
Oracle® Database Administrator's Guide10g Release 1 and 2
Doc ID: 242090.1

Oracle DBA Questions- All topics

Oracle DBA interview questions - Upload a Document to Scribd

Reference: http://www.orafaq.com/faqdbacv.htm

1. Did you use online or off-line backups?
2. What version of Oracle were you running?
3. Haw many databases and what sizes?
4. If you have to advise a backup strategy for a new application, how would you approach it and what questions will you ask?
5. If a customer calls you about a hanging database session, what will you do to resolve it?
6.Compare Oracle to any other database that you know. Why would you prefer to work on one and not on the other?

Reference: http://www.databasejournal.com/features/oracle/article.php/3678046
1. Why is a UNION ALL faster than a UNION?
The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.
2. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?
You can script the process to include it in a set of install scripts you deliver with a product.
You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
You can log the output and review it for errors.
You learn more about the process of database creation, such as what options are available and why.
3. What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?
Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through 10's of thousands of attempts quickly, this can be a very affective way to break into a database. A good password therefore should not be a dictionary word, it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to ten characters, including upper and lowercase, special characters, and even alternate characters if possible.
Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.
In the security arena, passwords can be made better, and it is a fairly solvable problem. However, what about in the real-world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake.
4. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.
5. How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
6. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.
7. What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?
A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.
A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.
A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link. Alternatively, a current user database link will connect as the current user you are logged in as.