Tuesday, October 7, 2014

Tuning Interview Questions

Tuning Interview Questions



1. A table-space has a table with 30 extents in it. Is this bad? Why or why not.
Level: Intermediate
Expected answer: Multiple extents in and of themselves aren?t bad. However if you also have chained rows this can hurt performance.

2. How do you set up table-spaces during an Oracle installation?
Level: Low
Expected answer: You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

3. You see multiple fragments in the SYSTEM table-space, what should you check first?
Level: Low
Expected answer: Ensure that users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Level: Intermediate
Expected answer: Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

5. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Level: High
Expected answer: Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.

6. What is the fastest query method for a table?
Level: Intermediate
Expected answer: Fetch by rowid

7. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
Level: High
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or 

good? If bad -How do you correct it?
Level: Intermediate
Expected answer: 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.

9. When should you increase copy latches? What parameters control copy latches?
Level: high
Expected answer: When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.

10. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
Level: Low
Expected answer: You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?
Level: Intermediate
Expected answer: The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.

12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?
Level: high
Expected answer: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

13. When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?  

Level: high
Expected answer: Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.

14. If you see contention for library caches how can you fix it?
Level: Intermediate
Expected answer: Increase the size of the shared pool.

15. If you see statistics that deal with "undo" what are they really talking about?
Level: Intermediate
Expected answer: Rollback segments and associated structures.

16. If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?
Level: High
Expected answer: The SMON process won?t automatically coalesce its free space fragments.
 

17. If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
Level: High
Expected answer: In Oracle 7.0 to 7.2 The use of the 'alter session set events 'immediate trace name coalesce level ts#';? command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best. If the free space isn?t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.

18. How can you tell if a tablespace has excessive fragmentation?
Level: Intermediate
If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.

19. You see the following on a status report:
redo log space requests 23
redo log space wait time 0
Is this something to worry about? What if redo log space wait time is high? How can you fix this?

Level: Intermediate
Expected answer: Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.

20. What can cause a high value for recursive calls? How can this be fixed?
Level: High
Expected answer: A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.

21. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
Level: Intermediate
Expected answer: This indicate that the shared pool may be too small. Increase the shared pool size.

22. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Level: Intermediate
Expected answer: Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.

23. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
Level: High
Expected answer: A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.

24. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
Level: High
Expected answer: A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.

25. In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS
--------------------- --------------------------
R01 11
R02 8
R03 12
R04 9
SYSTEM 4
You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action?

Level: Intermediate
Expected answer: No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.

26. You see multiple extents in the temporary tablespace. Is this a problem?
Level: Intermediate
Expected answer: As long as they are all the same size this isn?t a problem. In fact, it can even improve performance since Oracle won?t have to create a new extent when a user needs one.

Installation/Configuration Interview Questions


1. Define OFA.
Level: Low
Expected answer: OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.

2. How do you set up your tablespace on installation?
Level: Low
Expected answer: The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.

3. What should be done prior to installing Oracle (for the OS and the disks)?
Level: Low
Expected Answer: adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.

4. You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem?
Level: Intermediate to high
Expected Answer: Check to make sure that the archiver isn?t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.

5. When configuring SQLNET on the server what files must be set up?
Level: Intermediate
Expected answer: INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file

6. When configuring SQLNET on the client what files need to be set up?
Level: Intermediate
Expected answer: SQLNET.ORA, TNSNAMES.ORA

7. What must be installed with ODBC on the client in order for it to work with Oracle?
Level: Intermediate
Expected answer: SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

8. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for?
Level: Intermediate
Expected answer: The first thing to check with a large SGA is that it isn?t being swapped out.
 

9. What OS user should be used for the first part of an Oracle installation (on UNIX)?
Level: low
Expected answer: You must use root first.

10. When should the default values for Oracle initialization parameters be used as is?
Level: Low
Expected answer: Never

11. How many control files should you have? Where should they be located?
Level: Low
Expected answer: At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.

12. How many redo logs should you have and how should they be configured for maximum recoverability?  

Level: Intermediate
Expected answer: You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.

13. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces?
Expected answer: At least 7, see disk configuration answer above.

          Data Modeler Interview Questions


1. Describe third normal form?

Level: Low
Expected answer: Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key

2. Is the following statement true or false:
"All relational databases must be in third normal form"
Why or why not?

Level: Intermediate
Expected answer: False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.

3. What is an ERD?
Level: Low
Expected answer: An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.

4. Why are recursive relationships bad? How do you resolve them?
Level: Intermediate
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a "may" both are "must") as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
 

5. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")?
Level: Low to intermediate
Expected answer: This means the two entities should probably be made into one entity.

6. How should a many-to-many relationship be handled?
Level: Intermediate
Expected answer: By adding an intersection entity table

7. What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used? 

  Level: Intermediate
Expected answer: A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

8. When should you consider denormalization?
Level: Intermediate
Expected answer: Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity. 




Oracle Concepts and Architecture Database Structures

  1. What are the components of physical database structure of Oracle database?
 Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.



2. What are the components of logical database structure of Oracle database?

 There are tablespaces and database's schema objects.


3. What is a tablespace?

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

4. What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
 7. What are Schema Objects?
 Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.
8. Can objects of the same schema reside in different tablespaces?
 Yes.
9. Can a tablespace hold objects from different schemes?
 Yes.
10. What is Oracle table?
 A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
 11. What is an Oracle view?
 A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
12. Do a view contain data?
 Views do not contain or store data.
 13. Can a view based on another view?
 Yes.
 14. What are the advantages of views?
 - Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
 - Hide data complexity.
 - Simplify commands for the user.
 - Present the data in a different perspective from that of the base table.
 - Store complex queries.
 15. What is an Oracle sequence?
 A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
 16.  What is a synonym?
 A synonym is an alias for a table, view, sequence or program unit.
 17. What are the types of synonyms?
 There are two types of synonyms private and public.
 18. What is a private synonym?
 Only its owner can access a private synonym.
 19. What is a public synonym?
 Any database user can access a public synonym.
 20. What are synonyms used for?
 -  Mask the real name and owner of an object.
 - Provide public access to an object
 - Provide location transparency for tables, views or program units of a remote database.
 - Simplify the SQL statements for database users.
 21. What is an Oracle index?
 An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
 22.  How are the index updates?
 Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
 23. What are clusters?
 Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
 24. What is cluster key?
 The related columns of the tables in a cluster are called the cluster key.
 25. What is index cluster?
 A cluster with an index on the cluster key.
 26. What is hash cluster?
 A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.
 27. When can hash cluster used?
 Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
28. What is database link?
A database link is a named object that describes a "path" from one database to another.
29. What are the types of database links?
 Private database link, public database link & network database link.
 30. What is private database link?
 Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.
31. What is public database link?
 Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
32.  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.
33. What is data block?
 Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
34. 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 cannot be changed latter.
35. What is row chaining?
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.
36. 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.
37.  What is a segment? 
A segment is a set of extents allocated for a certain logical structure.
38. What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.
39. What is a data segment?
Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.
40. What is an index segment?
Each index has an index segment that stores all of its data.
41. What is rollback segment?
A database contains one or more rollback segments to temporarily store "undo" information.
42. What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.
43. What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.
 44. What is a datafile?
 Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.
45. What are the characteristics of data files?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.
46. What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.
 47. What is the function of redo log?
 The primary function of the redo log is to record all changes made to data.
 48. What is the use of redo log information?
 The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.
49. What does a control file contains?
 - Database name
 - Names and locations of a database's files and redolog files.
- Time stamp of database creation.
 50. 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.


                RMAN Interview Questions

1. What is RMAN ?
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.
Which Files must be backed up?
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)
 2.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?
In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog). 
3.  What are the Architectural components of RMAN?
1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces
4.  What are Channels?
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:
Type of I/O device being read or written to, either a disk or an sbt_tape
Number of processes simultaneously accessing an I/O device
Maximum size of files created on I/O devices
Maximum rate at which database files are read
Maximum number of files open at a time
5.  Why is the catalog optional?
Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.
6.  What does complete RMAN backup consist of ?
A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7.  What is a Backup set?
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8.  What is a Backup piece?
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:
·       A datafile cannot span backup sets
·       A datafile can span backup pieces as long as it stays within one backup set
·       Datafiles and control files can coexist in the same backup sets
·       Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.
9.  What are the benefits of using RMAN?
1.      Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.
2.       

         General Backup and Recovery questions

 

1. Why and when should I backup my database?
Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:
    * Rate of data change/ transaction rate
    * Database availability/ Can you shutdown for cold backups?
    * Criticality of the data/ Value of the data to the company
    * Read-only tablespace needs backing up just once right after you make it read-only
    * If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
    * If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
    * Etc.
Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunication.
2. What strategies are available for backing-up an Oracle database?
 The following methods are valid for backing-up an Oracle database:
    * Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details.
    * Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.
    * Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.
    * RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry.
Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files.
3. What is the difference between online and offline backups?

 A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.
A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
4.What is the difference between restoring and recovering?
 Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
 Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
RMAN> run {
  set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
  restore database;
  recover database;
}
5. My database is down and I cannot restore. What now?
 This is probably not the appropriate time to be sarcastic, but, recovery without backups are not supported. You know that you should have tested your recovery strategy, and that you should always backup a corrupted database before attempting to restore/recover it.
Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.
Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!
DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database.
6. How does one backup a database using the export utility?
 Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports. For more information about the Oracle export and import utilities, see the Import/ Export FAQ.
7. How does one put a database into ARCHIVELOG mode?
 The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
Issue the following commands to put a database into ARCHIVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Alternatively, add the above commands into your database's startup command script, and bounce the database.
The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start         = TRUE
log_archive_dest_1        = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1  = ENABLE
log_archive_format        = %d_%t_%s.arc
NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE 2:' ARCHIVELOG mode was introduced with Oracle 6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE 5: When running Oracle Real Application Clusters (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode. See the RAC FAQ for more details.
[edit] I've lost an archived/online REDO LOG file, can I get my DB back?
The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.
NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.
_allow_resetlogs_corruption = true
This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).
Steps:
    * Do a "SHUTDOWN NORMAL" of the database
    * Set the above parameter
    * Do a "STARTUP MOUNT" and "ALTER DATABASE OPEN RESETLOGS;"
    * If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the "ALTER DATABASE OPEN RESETLOGS;" command.
    * Wait a couple of minutes for Oracle to sort itself out
    * Do a "SHUTDOWN NORMAL"
    * Remove the above parameter!
    * Do a database "STARTUP" and check your ALERT.LOG file for errors.
    * Extract the data and rebuild the entire database

           User managed backup and recovery

This section deals with user managed, or non-RMAN backups.
1. How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
2.How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyzFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE;   -- Force log switch to update control file headers           
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
3. My database was terminated while in BACKUP MODE, do I need to recover?
 If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the Scripts section of this site.
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
ALTER DATABASE END BACKUP;
This command must be issued when the database is mounted, but not yet opened.
4.Does Oracle write to data files in begin/hot backup mode?
When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files.
When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
To solve this problem, simply switch to RMAN backups.

                RMAN backup and recovery

This section deals with RMAN backups:
1. What is RMAN and how does one use it?
Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recovering Oracle Databases. RMAN ships with the database server and doesn't require a separate installation. The RMAN executable is located in your ORACLE_HOME/bin directory.
In fact RMAN, is just a Pro*C application that translates commands to a PL/SQL interface. The PL/SQL calls are stallically linked into the Oracle kernel, and does not require the database to be opened (mapped from the ?/rdbms/admin/recover.bsq file).
RMAN can do off-line and on-line database backups. It cannot, however, write directly to tape, but various 3rd-party tools (like Veritas, Omiback, etc) can integrate with RMAN to handle tape library management.
RMAN can be operated from Oracle Enterprise Manager, or from command line. Here are the command line arguments:
Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
timeout      integer        number of seconds to wait for pipe input
-----------------------------------------------------------------------------
Here is an example:
[oracle@localhost oracle]$ rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
RMAN> connect target;
connected to target database: ORCL (DBID=1058957020)
RMAN> backup database;
...
2. How does one backup and restore a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database. RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
  allocate channel t1 type disk;
  backup
    format '/app/oracle/backup/%d_t%t_s%s_p%p'
      (database);
   release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
  allocate channel t1 type disk;
  # set until time 'Aug 07 2000 :51';
  restore tablespace users;
  recover tablespace users;
  release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
3. How does one backup and restore archived log files?
One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup scripts:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4>   format '/app/oracle/archback/log_%t_%sp%p'
5>   (archivelog all delete input);
6> release channel dev1;
7> }
The "delete input" clause will delete the archived logs as they are backed-up.
List all archivelog backups for the past 24 hours:
RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';
Here is a restore example:
RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }
4. How does one create a RMAN recovery catalog?
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
5. How does one integrate RMAN with third-party Media Managers?
The following Media Management Software Vendors have integrated their media management software with RMAN (Oracle Recovery Manager):
    * Veritas NetBackup - http://www.veritas.com/
    * EMC Data Manager (EDM) - http://www.emc.com/
    * HP OMNIBack/ DataProtector - http://www.hp.com/
    * IBM's Tivoli Storage Manager (formerly ADSM) - http://www.tivoli.com/storage/
    * EMC Networker - http://www.emc.com/
    * BrightStor ARCserve Backup - http://www.ca.com/us/data-loss-prevention.aspx
    * Sterling Software's SAMS:Alexandria (formerly from Spectralogic) - http://www.sterling.com/sams/
    * SUN's Solstice Backup - http://www.sun.com/software/whitepapers/backup-n-storage/
    * CommVault Galaxy - http://www.commvault.com/
    * etc...
The above Media Management Vendors will provide first line technical support (and installation guides) for their respective products.
A complete list of supported Media Management Vendors can be found at: http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htm
When allocating channels one can specify Media Management spesific parameters. Here are some examples:
Netbackup on Solaris:
allocate channel t1 type 'SBT_TAPE'  PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';
Netbackup on Windows:
allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";
Omniback/ DataProtector on HP-UX:
allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';
or:
allocate channel 'dev_1' type 'sbt_tape' parms 'ENV=OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';
6. How does one clone/duplicate a database with RMAN?
The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.
Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.
Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":
connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /
run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';
allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;
duplicate target database to dupdb
logfile
  GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
  GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}
The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.
Notes: the "set newname" commands are only required if your datafile names will different from the target database.
The newly cloned DB will have its own unique DBID.
7. Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?
Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database. In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken. Let's look at an example:
Let's take a backup (partial in our case for ilustrative purposes):
$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog
RMAN> backup datafile 1;
Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04[/code]
Now, let's destroy one of the control files:
SQL> show parameters CONTROL_FILES
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oradata/orcl/control01.ctl,
                                                 /oradata/orcl/control02.ctl,
                                                 /oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl</pre>
Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:
SQL> startup NOMOUNT
ORACLE instance started.
Total System Global Area  289406976 bytes
Fixed Size                  1301536 bytes
Variable Size             262677472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes</pre>
Now, from SQL*Plus, run the following PL/SQL block to restore the file:
DECLARE
  v_devtype   VARCHAR2(100);
  v_done      BOOLEAN;
  v_maxPieces NUMBER;
  TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
  v_pieceName t_pieceName;
BEGIN
  -- Define the backup pieces... (names from the RMAN Log file)
  v_pieceName(1) :=
     '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
  v_pieceName(2) :=
     '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
  v_maxPieces    := 2;
  -- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
  v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
  -- Restore the first Control File...
  DBMS_BACKUP_RESTORE.restoreSetDataFile;
  -- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
  DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
  dbms_output.put_line('Start restoring '||v_maxPieces||' pieces.');
  FOR i IN 1..v_maxPieces LOOP
    dbms_output.put_line('Restoring from piece '||v_pieceName(i));
    DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
    exit when v_done;
  END LOOP;
  -- Deallocate the channel...
  DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_BACKUP_RESTORE.deviceDeAllocate;
      RAISE;
END;
/
Let's see if the controlfile was restored:
SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r-----   1 oracle   dba      3096576 Aug 20 16:45 /oradata/orcl/control01.ctl[/code]
We should now be able to MOUNT the database and continue recovery...
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl
SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl
SQL> alter database mount;
SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
Database altered.
SQL> alter database open resetlogs;
Database altered.

No comments:

Post a Comment