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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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
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.
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.
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.
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
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.
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.
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.
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
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.
"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.
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.
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.
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
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.
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.
1. What are the components of physical database structure of
Oracle database?
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.
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)
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).
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
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.
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 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. 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