Oracle DBA Interview Questions PART 4

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.

Flashback Technology

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

No

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?

No

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

No

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.