<![CDATA[Oracle DBA Discussion Forum - Basic Database Administration]]> http://naturebios.com/natureboard/ Mon, 06 Sep 2010 15:50:30 -0700 MyBB <![CDATA[CURRENT_TIMESTAMP IN ORACLE]]> http://naturebios.com/natureboard/showthread.php?tid=2920 Thu, 19 Nov 2009 07:37:38 -0800 http://naturebios.com/natureboard/showthread.php?tid=2920
Code:
SELECT CURRENT_TIMESTAMP FROM dual;

Loading current timestamps

The Loader can insert values into columns with a timestamp data type based on a reading of the time-of-day clock. For example, the STARTDATE and ENDDATE of an offer in WebSphere Commerce can have values based on the time at which the offer is inserted into the table. To support this functionality, the Loader package uses the MLTIME table to keep the timestamp instances. The schema for this table is as follows:
Code:
table MLTIME
    (
    INSTANCEID BIGINT not null,
    MLTIMESTAMP TIMESTAMP
    )

1. Modify the values of the properties specified in the new Loader customizer property file.

The name of the table and its columns can be customized by changing the following properties in the Loader customizer property file:
Code:
TimestampTableName = MLTIME
        TimestampIdColumn = INSTANCEID
        TimestampValueColumn = MLTIMESTAMP

The input data for specifying current-timestamp values are based on timestamp string patterns. The following masks are used for specifying the durations for the timestamp:

%D for days
%M for months
%Y for years
%H for hours
%m for minutes
%s for seconds

You can customize current-timestamp formats by modifying or adding masks in the Loader customizer property file. The following input masks are provided:

Code:
InputCurrentTimestampFormat.1 = CURRENT TIMESTAMP
        InputCurrentTimestampFormat.2 = CURRENT TIMESTAMP %D DAYS
        InputCurrentTimestampFormat.3 = CURRENT TIMESTAMP %D DAYS %M MONTHS
        InputCurrentTimestampFormat.4 = CURRENT TIMESTAMP %D DAYS %M MONTHS %Y YEARS
        InputCurrentTimestampFormat.5 = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
        InputCurrentTimestampFormat.6 = SYSDATE
        InputCurrentTimestampFormat.7 = ADDDAYS(SYSDATE,%D)
        InputCurrentTimestampFormat.8 = ADDDAYS(ADDMONTHS(SYSDATE,%M),%D)
        InputCurrentTimestampFormat.9 = ADDDAYS(ADDMONTHS(ADDYEARS(SYSDATE,%Y),%M),%D)

Input data for the current timestamp is matched with the specified patterns. If the data matches a specified input pattern, that pattern is used to parse the input data and the Loader converts the data into the appropriate output format before inserting it into the database. New patterns can be added to the above list provided the subscript numbers are ordered sequentially.

2. Set the target output formats for specifying current timestamps:
1. CurrentTimestampFormat.Load is used when the Loader is operating in load or import mode.
2. CurrentTimestampFormat.JDBC is used when the Loader uses JDBC to insert, update, or delete values in the database.

The default target patterns in the Loader are as follows:

Code:
CurrentTimestampFormat.Load = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
          %h HOURS %m MINUTES %s SECONDS
        CurrentTimestampFormat.JDBC = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
          %h HOURS %m MINUTES %s SECONDS

When you customize the CurrentTimestampFormat.Load and CurrentTimestampFormat.JDBC properties, you should make sure that the syntax of the resulting statement is valid for the given database management system.

The CurrentTimestampLiteral property is used by the Loader to make an early determination of whether the value for the timestamp column is in a current-timestamp format, thus avoiding expensive computations to determine that the value is not a string representation of timestamp.

CurrentTimestampLiteral = CURRENT TIMESTAMP]]>
Code:
SELECT CURRENT_TIMESTAMP FROM dual;

Loading current timestamps

The Loader can insert values into columns with a timestamp data type based on a reading of the time-of-day clock. For example, the STARTDATE and ENDDATE of an offer in WebSphere Commerce can have values based on the time at which the offer is inserted into the table. To support this functionality, the Loader package uses the MLTIME table to keep the timestamp instances. The schema for this table is as follows:
Code:
table MLTIME
    (
    INSTANCEID BIGINT not null,
    MLTIMESTAMP TIMESTAMP
    )

1. Modify the values of the properties specified in the new Loader customizer property file.

The name of the table and its columns can be customized by changing the following properties in the Loader customizer property file:
Code:
TimestampTableName = MLTIME
        TimestampIdColumn = INSTANCEID
        TimestampValueColumn = MLTIMESTAMP

The input data for specifying current-timestamp values are based on timestamp string patterns. The following masks are used for specifying the durations for the timestamp:

%D for days
%M for months
%Y for years
%H for hours
%m for minutes
%s for seconds

You can customize current-timestamp formats by modifying or adding masks in the Loader customizer property file. The following input masks are provided:

Code:
InputCurrentTimestampFormat.1 = CURRENT TIMESTAMP
        InputCurrentTimestampFormat.2 = CURRENT TIMESTAMP %D DAYS
        InputCurrentTimestampFormat.3 = CURRENT TIMESTAMP %D DAYS %M MONTHS
        InputCurrentTimestampFormat.4 = CURRENT TIMESTAMP %D DAYS %M MONTHS %Y YEARS
        InputCurrentTimestampFormat.5 = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
        InputCurrentTimestampFormat.6 = SYSDATE
        InputCurrentTimestampFormat.7 = ADDDAYS(SYSDATE,%D)
        InputCurrentTimestampFormat.8 = ADDDAYS(ADDMONTHS(SYSDATE,%M),%D)
        InputCurrentTimestampFormat.9 = ADDDAYS(ADDMONTHS(ADDYEARS(SYSDATE,%Y),%M),%D)

Input data for the current timestamp is matched with the specified patterns. If the data matches a specified input pattern, that pattern is used to parse the input data and the Loader converts the data into the appropriate output format before inserting it into the database. New patterns can be added to the above list provided the subscript numbers are ordered sequentially.

2. Set the target output formats for specifying current timestamps:
1. CurrentTimestampFormat.Load is used when the Loader is operating in load or import mode.
2. CurrentTimestampFormat.JDBC is used when the Loader uses JDBC to insert, update, or delete values in the database.

The default target patterns in the Loader are as follows:

Code:
CurrentTimestampFormat.Load = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
          %h HOURS %m MINUTES %s SECONDS
        CurrentTimestampFormat.JDBC = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS
          %h HOURS %m MINUTES %s SECONDS

When you customize the CurrentTimestampFormat.Load and CurrentTimestampFormat.JDBC properties, you should make sure that the syntax of the resulting statement is valid for the given database management system.

The CurrentTimestampLiteral property is used by the Loader to make an early determination of whether the value for the timestamp column is in a current-timestamp format, thus avoiding expensive computations to determine that the value is not a string representation of timestamp.

CurrentTimestampLiteral = CURRENT TIMESTAMP]]> <![CDATA[Viewing and Updating the Incident Package Activity Log]]> http://naturebios.com/natureboard/showthread.php?tid=2177 Thu, 05 Nov 2009 15:45:34 -0800 http://naturebios.com/natureboard/showthread.php?tid=2177
The Support Workbench maintains an activity log for each incident package. Most activities that you perform on a package, such as adding or removing files or creating a package zip file, are recorded in the log. You can also add your own notes to the log. This is especially useful if more than one database administrator is working with packages.

To view and update the incident package activity log:

1. Access the Package Details page for the desired incident package.


2. Click the Activity Log link to view the Activity Log subpage.

The activity log is displayed.

3. To add your own note to the activity log, enter text into the Note field, and then click Add Note.

Your note is timestamped and appended to the list.]]>
The Support Workbench maintains an activity log for each incident package. Most activities that you perform on a package, such as adding or removing files or creating a package zip file, are recorded in the log. You can also add your own notes to the log. This is especially useful if more than one database administrator is working with packages.

To view and update the incident package activity log:

1. Access the Package Details page for the desired incident package.


2. Click the Activity Log link to view the Activity Log subpage.

The activity log is displayed.

3. To add your own note to the activity log, enter text into the Note field, and then click Add Note.

Your note is timestamped and appended to the list.]]> <![CDATA[Removing Incident Package Files]]> http://naturebios.com/natureboard/showthread.php?tid=2176 Thu, 05 Nov 2009 15:44:56 -0800 http://naturebios.com/natureboard/showthread.php?tid=2176
You can remove one or more files of any type from the incident package.

To remove incident package files:

1. Access the Customize Package page for the desired incident package.

2. Click the Files link to view the Files subpage.

A list of files in the package is displayed.

If you have not yet generated a physical file for this package, all package files are displayed in the list. If you have already generated a physical file, a View list appears above the files list. It enables you to choose between viewing only incremental package contents or the full package contents. The default selection is incremental package contents. This default selection displays only those package files that were created or modified since the last time that a physical file was generated for the package. Select Full package contents from the View list to view all package files.
3. Select the files to remove, and then click Exclude.

Note:
If you do not see the desired files, they may be on another page. Click the Next link to view the next page. Continue clicking Next, or select from the list of file numbers (to the left of the Next link) until you see the desired files. You can then select the files and click Remove.]]>
You can remove one or more files of any type from the incident package.

To remove incident package files:

1. Access the Customize Package page for the desired incident package.

2. Click the Files link to view the Files subpage.

A list of files in the package is displayed.

If you have not yet generated a physical file for this package, all package files are displayed in the list. If you have already generated a physical file, a View list appears above the files list. It enables you to choose between viewing only incremental package contents or the full package contents. The default selection is incremental package contents. This default selection displays only those package files that were created or modified since the last time that a physical file was generated for the package. Select Full package contents from the View list to view all package files.
3. Select the files to remove, and then click Exclude.

Note:
If you do not see the desired files, they may be on another page. Click the Next link to view the next page. Continue clicking Next, or select from the list of file numbers (to the left of the Next link) until you see the desired files. You can then select the files and click Remove.]]> <![CDATA[Setting Incident Packaging Preferences]]> http://naturebios.com/natureboard/showthread.php?tid=2175 Thu, 05 Nov 2009 15:44:05 -0800 http://naturebios.com/natureboard/showthread.php?tid=2175
This section provides instructions for setting incident packaging preferences. Examples of incident packaging preferences include the number of days to retain incident information, and the number of leading and trailing incidents to include in a package for each problem. (By default, if a problem has many incidents, only the first three and last three incidents are packaged.) You can change these and other incident packaging preferences with Enterprise Manager or with the ADRCI utility.

To set incident packaging preferences with Enterprise Manager:

1. Access the Support Workbench home page.


2. In the Related Links section at the bottom of the page, click Incident Packaging Configuration.

The View Incident Packaging Configuration page appears. Click Help to view descriptions of the settings on this page.
3. Click Edit.

The Edit Incident Packaging Configuration page appears.
4. Edit settings, and then click OK to apply changes.]]>
This section provides instructions for setting incident packaging preferences. Examples of incident packaging preferences include the number of days to retain incident information, and the number of leading and trailing incidents to include in a package for each problem. (By default, if a problem has many incidents, only the first three and last three incidents are packaged.) You can change these and other incident packaging preferences with Enterprise Manager or with the ADRCI utility.

To set incident packaging preferences with Enterprise Manager:

1. Access the Support Workbench home page.


2. In the Related Links section at the bottom of the page, click Incident Packaging Configuration.

The View Incident Packaging Configuration page appears. Click Help to view descriptions of the settings on this page.
3. Click Edit.

The Edit Incident Packaging Configuration page appears.
4. Edit settings, and then click OK to apply changes.]]> <![CDATA[Custom Packaging]]> http://naturebios.com/natureboard/showthread.php?tid=2174 Thu, 05 Nov 2009 15:42:42 -0800 http://naturebios.com/natureboard/showthread.php?tid=2174
This is the more manual method, with more steps. It is intended for expert Support Workbench users who want more control over the packaging process. With custom packaging, you can create a new package with one or more problems, or you can add one or more problems to an existing package. You can then perform a variety of operations on the new or updated package, including:

* Adding or removing problems or incidents
* Adding, editing, or removing trace files in the package
* Adding or removing external files of any type
* Adding other diagnostic data such as SQL test cases
* Manually finalizing the package and then viewing package contents to determine if you must edit or remove sensitive data or remove files to reduce package size.

You might conduct these operations over a number of days, before deciding that you have enough diagnostic information to send to Oracle Support.

With custom packaging, you create the zip file and request upload to Oracle Support as two separate steps. Each of these steps can be performed immediately or scheduled for a future date and time.]]>
This is the more manual method, with more steps. It is intended for expert Support Workbench users who want more control over the packaging process. With custom packaging, you can create a new package with one or more problems, or you can add one or more problems to an existing package. You can then perform a variety of operations on the new or updated package, including:

* Adding or removing problems or incidents
* Adding, editing, or removing trace files in the package
* Adding or removing external files of any type
* Adding other diagnostic data such as SQL test cases
* Manually finalizing the package and then viewing package contents to determine if you must edit or remove sensitive data or remove files to reduce package size.

You might conduct these operations over a number of days, before deciding that you have enough diagnostic information to send to Oracle Support.

With custom packaging, you create the zip file and request upload to Oracle Support as two separate steps. Each of these steps can be performed immediately or scheduled for a future date and time.]]> <![CDATA[Quick Packaging]]> http://naturebios.com/natureboard/showthread.php?tid=2173 Thu, 05 Nov 2009 15:42:01 -0800 http://naturebios.com/natureboard/showthread.php?tid=2173 Quick Packaging

This is the more automated method with a minimum of steps, organized in a guided workflow (a wizard). You select a single problem, provide a package name and description, and then schedule upload of the package contents, either immediately or at a specified date and time. The Support Workbench automatically places diagnostic data related to the problem into the package, finalizes the package, creates the zip file, and then uploads the file. With this method, you do not have the opportunity to add, edit, or remove package files or add other diagnostic data such as SQL test cases. However, it is the simplest and quickest way to get first-failure diagnostic data to Oracle Support.

Note that when quick packaging is complete, the package that was created by the wizard remains. You can then modify the package with custom packaging operations at a later time and manually reupload.]]> Quick Packaging

This is the more automated method with a minimum of steps, organized in a guided workflow (a wizard). You select a single problem, provide a package name and description, and then schedule upload of the package contents, either immediately or at a specified date and time. The Support Workbench automatically places diagnostic data related to the problem into the package, finalizes the package, creates the zip file, and then uploads the file. With this method, you do not have the opportunity to add, edit, or remove package files or add other diagnostic data such as SQL test cases. However, it is the simplest and quickest way to get first-failure diagnostic data to Oracle Support.

Note that when quick packaging is complete, the package that was created by the wizard remains. You can then modify the package with custom packaging operations at a later time and manually reupload.]]> <![CDATA[Correlated Diagnostic Data in Incident Packages]]> http://naturebios.com/natureboard/showthread.php?tid=2172 Thu, 05 Nov 2009 15:39:24 -0800 http://naturebios.com/natureboard/showthread.php?tid=2172
To improve the diagnosability of a problem, it is sometimes necessary to examine not only diagnostic data that is directly related to the problem, but also diagnostic data that is correlated with the directly related data. Diagnostic data can be correlated by time, by process ID, or by other criteria. For example, when examining an incident, it may be helpful to also examine an incident that occurred five minutes after the original incident. Similarly, while it is clear that the diagnostic data for an incident should include the trace file for the Oracle Database process that was running when the incident occurred, it might be helpful to also include trace files for other processes that are related to the original process.

Thus, when problems and their associated incidents are added to a package, any correlated incidents are added at the same time, with their associated trace files.

During the process of creating the physical file for a package, the Support Workbench calls upon the Incident Packaging Service to finalize the package. Finalizing means adding to the package any additional trace files that are correlated by time to incidents in the package, and adding other diagnostic information such as the alert log, health checker reports, SQL test cases, configuration information, and so on. This means that the number of files in the zip file may be greater than the number of files that the Support Workbench had previously displayed as the package contents.

The Incident Packaging Service follows a set of rules to determine the trace files in the ADR that are correlated to existing package data. You can modify some of those rules in the Incident Packaging Configuration page in Enterprise Manager.

Because both initial package data and added correlated data may contain sensitive information, it is important to have an opportunity to remove or edit files that contain this information before uploading to Oracle Support. For this reason, the Support Workbench enables you to run a command that finalizes the package as a separate operation. After manually finalizing a package, you can examine the package contents, remove or edit files, and then generate and upload a zip file.

Note:
Finalizing a package does not mean closing it to further modifications. You can continue to add diagnostic data to a finalized package. You can also finalize the same package multiple times. Each time that you finalize, any new correlated data is added.]]>
To improve the diagnosability of a problem, it is sometimes necessary to examine not only diagnostic data that is directly related to the problem, but also diagnostic data that is correlated with the directly related data. Diagnostic data can be correlated by time, by process ID, or by other criteria. For example, when examining an incident, it may be helpful to also examine an incident that occurred five minutes after the original incident. Similarly, while it is clear that the diagnostic data for an incident should include the trace file for the Oracle Database process that was running when the incident occurred, it might be helpful to also include trace files for other processes that are related to the original process.

Thus, when problems and their associated incidents are added to a package, any correlated incidents are added at the same time, with their associated trace files.

During the process of creating the physical file for a package, the Support Workbench calls upon the Incident Packaging Service to finalize the package. Finalizing means adding to the package any additional trace files that are correlated by time to incidents in the package, and adding other diagnostic information such as the alert log, health checker reports, SQL test cases, configuration information, and so on. This means that the number of files in the zip file may be greater than the number of files that the Support Workbench had previously displayed as the package contents.

The Incident Packaging Service follows a set of rules to determine the trace files in the ADR that are correlated to existing package data. You can modify some of those rules in the Incident Packaging Configuration page in Enterprise Manager.

Because both initial package data and added correlated data may contain sensitive information, it is important to have an opportunity to remove or edit files that contain this information before uploading to Oracle Support. For this reason, the Support Workbench enables you to run a command that finalizes the package as a separate operation. After manually finalizing a package, you can examine the package contents, remove or edit files, and then generate and upload a zip file.

Note:
Finalizing a package does not mean closing it to further modifications. You can continue to add diagnostic data to a finalized package. You can also finalize the same package multiple times. Each time that you finalize, any new correlated data is added.]]> <![CDATA[Incident Packages]]> http://naturebios.com/natureboard/showthread.php?tid=2171 Thu, 05 Nov 2009 15:38:44 -0800 http://naturebios.com/natureboard/showthread.php?tid=2171
For the customized approach to uploading diagnostic data to Oracle Support, you first collect the data into an intermediate logical structure called an incident package (package). A package is a collection of metadata that is stored in the Automatic Diagnostic Repository (ADR) and that points to diagnostic data files and other files both in and out of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems. Because a problem can have many incidents (many occurrences of the same problem), by default only the first three and last three incidents for each problem are added to the package, excluding any incidents that are over 90 days old. You can change these default numbers on the Incident Packaging Configuration page of the Support Workbench.

After the package is created, you can add any type of external file to the package, remove selected files from the package, or edit selected files in the package to remove sensitive data. As you add and remove package contents, only the package metadata is modified.

When you are ready to upload the diagnostic data to Oracle Support, you first create a zip file that contains all the files referenced by the package metadata. You then upload the zip file through Oracle Configuration Manager.

Note:
If you do not have Oracle Configuration Manager installed and properly configured, you must upload the zip file manually through OracleMetaLink.]]>
For the customized approach to uploading diagnostic data to Oracle Support, you first collect the data into an intermediate logical structure called an incident package (package). A package is a collection of metadata that is stored in the Automatic Diagnostic Repository (ADR) and that points to diagnostic data files and other files both in and out of the ADR. When you create a package, you select one or more problems to add to the package. The Support Workbench then automatically adds to the package the problem information, incident information, and diagnostic data (such as trace files and dumps) associated with the selected problems. Because a problem can have many incidents (many occurrences of the same problem), by default only the first three and last three incidents for each problem are added to the package, excluding any incidents that are over 90 days old. You can change these default numbers on the Incident Packaging Configuration page of the Support Workbench.

After the package is created, you can add any type of external file to the package, remove selected files from the package, or edit selected files in the package to remove sensitive data. As you add and remove package contents, only the package metadata is modified.

When you are ready to upload the diagnostic data to Oracle Support, you first create a zip file that contains all the files referenced by the package metadata. You then upload the zip file through Oracle Configuration Manager.

Note:
If you do not have Oracle Configuration Manager installed and properly configured, you must upload the zip file manually through OracleMetaLink.]]> <![CDATA[Repairing Data Corruptions with the Data Recovery Advisor]]> http://naturebios.com/natureboard/showthread.php?tid=2170 Thu, 05 Nov 2009 15:35:04 -0800 http://naturebios.com/natureboard/showthread.php?tid=2170
You use the Data Recovery Advisor to repair data block corruptions, undo corruptions, data dictionary corruptions, and more. The Data Recovery Advisor integrates with the Enterprise Manager Support Workbench (Support Workbench), with the Health Monitor, and with the RMAN utility to display data corruption problems, assess the extent of each problem (critical, high priority, low priority), describe the impact of a problem, recommend repair options, conduct a feasibility check of the customer-chosen option, and automate the repair process.

The Data Recovery Advisor is automatically recommended by and accessible from the Support Workbench when you are viewing:

* Problem details for a problem that is related to a data corruption or other data failure.
* Health checker findings that are related to a data corruption or other data failure.

The Data Recovery Advisor is also available from the Advisor Central page. A link to this page can be found in the Related Links section of the Database Home page and of the Performance page.

Note:
The Data Recovery Advisor is available only when you are connected as SYSDBA.

You access the Data Recovery Advisor from the Support Workbench in the following ways:

* From the Problem Details page

[Image: dra_access_1.gif]

Click the Data Recovery Advisor link in the Investigate and Resolve section.

From the Checker Findings subpage of the Support Workbench home page
[Image: dra_access_2.gif]
Select one or more data corruption findings and then click Launch Recovery Advisor.]]>
You use the Data Recovery Advisor to repair data block corruptions, undo corruptions, data dictionary corruptions, and more. The Data Recovery Advisor integrates with the Enterprise Manager Support Workbench (Support Workbench), with the Health Monitor, and with the RMAN utility to display data corruption problems, assess the extent of each problem (critical, high priority, low priority), describe the impact of a problem, recommend repair options, conduct a feasibility check of the customer-chosen option, and automate the repair process.

The Data Recovery Advisor is automatically recommended by and accessible from the Support Workbench when you are viewing:

* Problem details for a problem that is related to a data corruption or other data failure.
* Health checker findings that are related to a data corruption or other data failure.

The Data Recovery Advisor is also available from the Advisor Central page. A link to this page can be found in the Related Links section of the Database Home page and of the Performance page.

Note:
The Data Recovery Advisor is available only when you are connected as SYSDBA.

You access the Data Recovery Advisor from the Support Workbench in the following ways:

* From the Problem Details page

[Image: dra_access_1.gif]

Click the Data Recovery Advisor link in the Investigate and Resolve section.

From the Checker Findings subpage of the Support Workbench home page
[Image: dra_access_2.gif]
Select one or more data corruption findings and then click Launch Recovery Advisor.]]> <![CDATA[Viewing, Disabling, or Removing a SQL Patch]]> http://naturebios.com/natureboard/showthread.php?tid=2169 Thu, 05 Nov 2009 15:32:09 -0800 http://naturebios.com/natureboard/showthread.php?tid=2169
After you apply a SQL patch with the SQL Repair Advisor, you may want to view it to confirm its presence, disable it, or remove it. One reason to remove a patch is if you install a later release of Oracle Database that fixes the bug that caused the failure in the patched SQL statement.

To view, disable, or remove a SQL patch:

1. Access the Database Home page in Enterprise Manager.

For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.

2. At the top of the page, click Server to display the Server page.

3. In the Query Optimizer section, click SQL Plan Control.

The SQL Plan Control page appears. See the online help for information about this page.

4. At the top of the page, click SQL Patch to display the SQL Patch subpage.

The SQL Patch subpage displays all SQL patches in the database.

5. Locate the desired patch by examining the associated SQL text.

Click the SQL text to view the complete text of the statement.

6. To disable the patch, select it, and then click Disable.

A confirmation message appears, and the patch status changes to DISABLED. You can later reenable the patch by selecting it and clicking Enable.

7. To remove the patch, select it, and then click Drop.

A confirmation message appears.]]>
After you apply a SQL patch with the SQL Repair Advisor, you may want to view it to confirm its presence, disable it, or remove it. One reason to remove a patch is if you install a later release of Oracle Database that fixes the bug that caused the failure in the patched SQL statement.

To view, disable, or remove a SQL patch:

1. Access the Database Home page in Enterprise Manager.

For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.

2. At the top of the page, click Server to display the Server page.

3. In the Query Optimizer section, click SQL Plan Control.

The SQL Plan Control page appears. See the online help for information about this page.

4. At the top of the page, click SQL Patch to display the SQL Patch subpage.

The SQL Patch subpage displays all SQL patches in the database.

5. Locate the desired patch by examining the associated SQL text.

Click the SQL text to view the complete text of the statement.

6. To disable the patch, select it, and then click Disable.

A confirmation message appears, and the patch status changes to DISABLED. You can later reenable the patch by selecting it and clicking Enable.

7. To remove the patch, select it, and then click Drop.

A confirmation message appears.]]> <![CDATA[Running the SQL Repair Advisor]]> http://naturebios.com/natureboard/showthread.php?tid=2168 Thu, 05 Nov 2009 15:31:00 -0800 http://naturebios.com/natureboard/showthread.php?tid=2168
You run the SQL Repair Advisor from the Problem Details page of the Support Workbench. The instructions in this section assume that you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.
2. In the Investigate and Resolve section, under the Self Service tab, under the Resolve heading, click SQL Repair Advisor.
[Image: sql_repair_advisor_results.gif]

The SQL Repair Advisor page appears.
3. Enter an optional task name, set an optional time limit for the advisor task, and adjust settings to schedule the advisor to run either immediately or at a future date and time.
4. Click Submit.

A "processing" page appears. After a short delay, the SQL Repair Results page appears.
Description of sql_repair_advisor_results.gif follows
Description of the illustration sql_repair_advisor_results.gif

A check mark in the SQL Patch column indicates that a recommendation is present. The absence of a check mark in this column means that the SQL Repair Advisor was unable to devise a patch for the SQL statement.
5. If a recommendation is present, click View to view the recommendation.

The Repair Recommendations page appears, showing the recommended patch for the statement.
6. Click Implement.

The SQL Repair Results page returns, showing a confirmation message.
7. (Optional) Click Verify using SQL Worksheet to run the statement in the SQL worksheet and verify that the patch successfully repaired the statement.]]>
You run the SQL Repair Advisor from the Problem Details page of the Support Workbench. The instructions in this section assume that you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.
2. In the Investigate and Resolve section, under the Self Service tab, under the Resolve heading, click SQL Repair Advisor.
[Image: sql_repair_advisor_results.gif]

The SQL Repair Advisor page appears.
3. Enter an optional task name, set an optional time limit for the advisor task, and adjust settings to schedule the advisor to run either immediately or at a future date and time.
4. Click Submit.

A "processing" page appears. After a short delay, the SQL Repair Results page appears.
Description of sql_repair_advisor_results.gif follows
Description of the illustration sql_repair_advisor_results.gif

A check mark in the SQL Patch column indicates that a recommendation is present. The absence of a check mark in this column means that the SQL Repair Advisor was unable to devise a patch for the SQL statement.
5. If a recommendation is present, click View to view the recommendation.

The Repair Recommendations page appears, showing the recommended patch for the statement.
6. Click Implement.

The SQL Repair Results page returns, showing a confirmation message.
7. (Optional) Click Verify using SQL Worksheet to run the statement in the SQL worksheet and verify that the patch successfully repaired the statement.]]> <![CDATA[SQL Repair Advisor]]> http://naturebios.com/natureboard/showthread.php?tid=2167 Thu, 05 Nov 2009 15:29:45 -0800 http://naturebios.com/natureboard/showthread.php?tid=2167
You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.]]>
You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.]]> <![CDATA[Repairing SQL Failures with the SQL Repair Advisor]]> http://naturebios.com/natureboard/showthread.php?tid=2166 Thu, 05 Nov 2009 15:29:26 -0800 http://naturebios.com/natureboard/showthread.php?tid=2166
In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.

This section covers the following topics:

* SQL Repair Advisor
* Running the SQL Repair Advisor
* Viewing, Disabling, or Removing a SQL Patch
]]>
In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.

This section covers the following topics:

* SQL Repair Advisor
* Running the SQL Repair Advisor
* Viewing, Disabling, or Removing a SQL Patch
]]> <![CDATA[Health Check Parameters]]> http://naturebios.com/natureboard/showthread.php?tid=2165 Thu, 05 Nov 2009 15:28:04 -0800 http://naturebios.com/natureboard/showthread.php?tid=2165

Health Check Parameters Reference


The following tables describe the parameters for those health checks that require them. Parameters with a default value of (none) are mandatory.


Table 8-5 Parameters for Data Block Integrity Check






Parameter Name Type Default Value Description

BLC_DF_NUM

Number

(none)

Block data file number

BLC_BL_NUM

Number

(none)

Data block number


<!-- class="tblformal" -->

Table 8-6 Parameters for Redo Integrity Check






Parameter Name Type Default Value Description

SCN_TEXT

Text

0

SCN of the latest good redo (if known)

<!-- class="tblformal" -->

Table 8-7 Parameters for Undo Segment Integrity Check






Parameter Name Type Default Value Description

USN_NUMBER

Text

(none)

Undo segment number


<!-- class="tblformal" -->

Table 8-8 Parameters for Transaction Integrity Check






Parameter Name Type Default Value Description

TXN_ID

Text

(none)

Transaction ID

<!-- class="tblformal" -->

Table 8-9 Parameters for Dictionary Integrity Check






Parameter Name Type Default Value Description

CHECK_MASK

Text

ALL

Possible values are:

  • COLUMN_CHECKS—Run column checks only. Verify column-level constraints in the core tables.


  • ROW_CHECKS—Run row checks only. Verify row-level constraints in the core tables.

  • REFERENTIAL_CHECKS—Run referential checks only. Verify referential constraints in the core tables.

  • ALL—Run all checks.

TABLE_NAME

Text

ALL_CORE_TABLES

Name of a single core table to check. If omitted, all core tables are checked.


]]>

Health Check Parameters Reference


The following tables describe the parameters for those health checks that require them. Parameters with a default value of (none) are mandatory.


Table 8-5 Parameters for Data Block Integrity Check






Parameter Name Type Default Value Description

BLC_DF_NUM

Number

(none)

Block data file number

BLC_BL_NUM

Number

(none)

Data block number


<!-- class="tblformal" -->

Table 8-6 Parameters for Redo Integrity Check






Parameter Name Type Default Value Description

SCN_TEXT

Text

0

SCN of the latest good redo (if known)

<!-- class="tblformal" -->

Table 8-7 Parameters for Undo Segment Integrity Check






Parameter Name Type Default Value Description

USN_NUMBER

Text

(none)

Undo segment number


<!-- class="tblformal" -->

Table 8-8 Parameters for Transaction Integrity Check






Parameter Name Type Default Value Description

TXN_ID

Text

(none)

Transaction ID

<!-- class="tblformal" -->

Table 8-9 Parameters for Dictionary Integrity Check






Parameter Name Type Default Value Description

CHECK_MASK

Text

ALL

Possible values are:

  • COLUMN_CHECKS—Run column checks only. Verify column-level constraints in the core tables.


  • ROW_CHECKS—Run row checks only. Verify row-level constraints in the core tables.

  • REFERENTIAL_CHECKS—Run referential checks only. Verify referential constraints in the core tables.

  • ALL—Run all checks.

TABLE_NAME

Text

ALL_CORE_TABLES

Name of a single core table to check. If omitted, all core tables are checked.


]]> <![CDATA[Health Monitor Views]]> http://naturebios.com/natureboard/showthread.php?tid=2164 Thu, 05 Nov 2009 15:26:40 -0800 http://naturebios.com/natureboard/showthread.php?tid=2164
Instead of requesting a checker report, you can view the results of a specific checker run by directly querying the ADR data from which reports are created. This data is available through the views V$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION.

The following example queries the V$HM_RUN view to determine a history of checker runs:

Code:
SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

    RUN_ID NAME         CHECK_NAME                         RUN_MODE SRC_INCIDENT
---------- ------------ ---------------------------------- -------- ------------
         1 HM_RUN_1     DB Structure Integrity Check       REACTIVE            0
       101 HM_RUN_101   Transaction Integrity Check        REACTIVE         6073
       121 TXNCHK       Transaction Integrity Check        MANUAL              0
       181 HMR_tab$     Dictionary Integrity Check         MANUAL              0
          .
          .
          .
       981 Proct_ts$    Dictionary Integrity Check         MANUAL              0
      1041 HM_RUN_1041  DB Structure Integrity Check       REACTIVE            0
      1061 HM_RUN_1061  Data Block Integrity Check         REACTIVE         7418
The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 1061:
Code:
SELECT type, description FROM v$hm_finding WHERE run_id = 1061;

TYPE          DESCRIPTION
------------- -----------------------------------------
FAILURE       Block 64349 in datafile 1: '/ade/sfogel_e
              mdb/oracle/dbs/t_db1.f' is media corrupt

FAILURE       Block 64351 in datafile 1: '/ade/sfogel_e
              mdb/oracle/dbs/t_db1.f' is media corrupt
]]>
Instead of requesting a checker report, you can view the results of a specific checker run by directly querying the ADR data from which reports are created. This data is available through the views V$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION.

The following example queries the V$HM_RUN view to determine a history of checker runs:

Code:
SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

    RUN_ID NAME         CHECK_NAME                         RUN_MODE SRC_INCIDENT
---------- ------------ ---------------------------------- -------- ------------
         1 HM_RUN_1     DB Structure Integrity Check       REACTIVE            0
       101 HM_RUN_101   Transaction Integrity Check        REACTIVE         6073
       121 TXNCHK       Transaction Integrity Check        MANUAL              0
       181 HMR_tab$     Dictionary Integrity Check         MANUAL              0
          .
          .
          .
       981 Proct_ts$    Dictionary Integrity Check         MANUAL              0
      1041 HM_RUN_1041  DB Structure Integrity Check       REACTIVE            0
      1061 HM_RUN_1061  Data Block Integrity Check         REACTIVE         7418
The next example queries the V$HM_FINDING view to obtain finding details for the reactive data block check with RUN_ID 1061:
Code:
SELECT type, description FROM v$hm_finding WHERE run_id = 1061;

TYPE          DESCRIPTION
------------- -----------------------------------------
FAILURE       Block 64349 in datafile 1: '/ade/sfogel_e
              mdb/oracle/dbs/t_db1.f' is media corrupt

FAILURE       Block 64351 in datafile 1: '/ade/sfogel_e
              mdb/oracle/dbs/t_db1.f' is media corrupt
]]> <![CDATA[Viewing Reports Using the ADRCI Utility]]> http://naturebios.com/natureboard/showthread.php?tid=2163 Thu, 05 Nov 2009 15:25:52 -0800 http://naturebios.com/natureboard/showthread.php?tid=2163
You can create and view Health Monitor checker reports using the ADRCI utility.

To create and view a checker report using ADRCI

1. Ensure that operating system environment variables (such as ORACLE_HOME) are set properly, and then enter the following command at the operating system command prompt:

Quote: ADRCI

The utility starts and displays the following prompt:

adrci>>

Optionally, you can change the current ADR home. Use the SHOW HOMES command to list all ADR homes, and the SET HOMEPATH command to change the current ADR home. See Oracle Database Utilities for more information.

2. Enter the following command:
Code:
show hm_run

This command lists all the checker runs (stored in V$HM_RUN) registered in the ADR repository.

3. Locate the checker run for which you want to create a report and note the checker run name. The REPORT_FILE field contains a filename if a report already exists for this checker run. Otherwise, generate the report with the following command:

Code:
create report hm_run run_name

4. To view the report, enter the following command:

Code:
show report hm_run run_name
]]>
You can create and view Health Monitor checker reports using the ADRCI utility.

To create and view a checker report using ADRCI

1. Ensure that operating system environment variables (such as ORACLE_HOME) are set properly, and then enter the following command at the operating system command prompt:

Quote: ADRCI

The utility starts and displays the following prompt:

adrci>>

Optionally, you can change the current ADR home. Use the SHOW HOMES command to list all ADR homes, and the SET HOMEPATH command to change the current ADR home. See Oracle Database Utilities for more information.

2. Enter the following command:
Code:
show hm_run

This command lists all the checker runs (stored in V$HM_RUN) registered in the ADR repository.

3. Locate the checker run for which you want to create a report and note the checker run name. The REPORT_FILE field contains a filename if a report already exists for this checker run. Otherwise, generate the report with the following command:

Code:
create report hm_run run_name

4. To view the report, enter the following command:

Code:
show report hm_run run_name
]]> <![CDATA[Viewing Reports Using DBMS_HM]]> http://naturebios.com/natureboard/showthread.php?tid=2162 Thu, 05 Nov 2009 15:24:35 -0800 http://naturebios.com/natureboard/showthread.php?tid=2162
You can view Health Monitor checker reports with the DBMS_HM package function GET_RUN_REPORT. This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example:

Code:
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')
-----------------------------------------------------------------------

Run Name                     : HM_RUN_1061
Run Id                       : 1061
Check Name                   : Data Block Integrity Check
Mode                         : REACTIVE
Status                       : COMPLETED
Start Time                   : 2007-05-12 22:11:02.032292 -07:00
End Time                     : 2007-05-12 22:11:20.835135 -07:00
Error Encountered            : 0
Source Incident Id           : 7418
Number of Incidents Created  : 0

Input Paramters for the Run
BLC_DF_NUM=1
BLC_BL_NUM=64349

Run Findings And Recommendations
Finding
Finding Name  : Media Block Corruption
Finding ID    : 1065
Type          : FAILURE
Status        : OPEN
Priority      : HIGH
Message       : Block 64349 in datafile 1:
               '/ade/sfogel_emdb/oracle/dbs/t_db1.f' is media corrupt
Message       : Object BMRTEST1 owned by SYS might be unavailable
Finding
Finding Name  : Media Block Corruption
Finding ID    : 1071
Type          : FAILURE
Status        : OPEN
Priority      : HIGH
Message       : Block 64351 in datafile 1:
               '/ade/sfogel_emdb/oracle/dbs/t_db1.f' is media corrupt
Message       : Object BMRTEST2 owned by SYS might be unavailable
]]>
You can view Health Monitor checker reports with the DBMS_HM package function GET_RUN_REPORT. This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example:

Code:
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_1061') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('HM_RUN_1061')
-----------------------------------------------------------------------

Run Name                     : HM_RUN_1061
Run Id                       : 1061
Check Name                   : Data Block Integrity Check
Mode                         : REACTIVE
Status                       : COMPLETED
Start Time                   : 2007-05-12 22:11:02.032292 -07:00
End Time                     : 2007-05-12 22:11:20.835135 -07:00
Error Encountered            : 0
Source Incident Id           : 7418
Number of Incidents Created  : 0

Input Paramters for the Run
BLC_DF_NUM=1
BLC_BL_NUM=64349

Run Findings And Recommendations
Finding
Finding Name  : Media Block Corruption
Finding ID    : 1065
Type          : FAILURE
Status        : OPEN
Priority      : HIGH
Message       : Block 64349 in datafile 1:
               '/ade/sfogel_emdb/oracle/dbs/t_db1.f' is media corrupt
Message       : Object BMRTEST1 owned by SYS might be unavailable
Finding
Finding Name  : Media Block Corruption
Finding ID    : 1071
Type          : FAILURE
Status        : OPEN
Priority      : HIGH
Message       : Block 64351 in datafile 1:
               '/ade/sfogel_emdb/oracle/dbs/t_db1.f' is media corrupt
Message       : Object BMRTEST2 owned by SYS might be unavailable
]]> <![CDATA[view run findings using Enterprise Manager]]> http://naturebios.com/natureboard/showthread.php?tid=2161 Thu, 05 Nov 2009 15:23:56 -0800 http://naturebios.com/natureboard/showthread.php?tid=2161
1. Access the Database Home page.

For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.
2. In the Related Links section, click Advisor Central.
3. Click Checkers to view the Checkers subpage.
4. Click the run name for the checker run that you want to view.

The Run Detail page appears, showing the findings for that checker run.
5. Click Runs to display the Runs subpage.

Enterprise Manager displays more information about the checker run.
6. Click View Report to view the report for the checker run.

The report is displayed in a new browser window.]]>
1. Access the Database Home page.

For Oracle Enterprise Manager Database Control, see Oracle Database 2 Day DBA for instructions. For Oracle Enterprise Manager Grid Control, go to the desired database target.
2. In the Related Links section, click Advisor Central.
3. Click Checkers to view the Checkers subpage.
4. Click the run name for the checker run that you want to view.

The Run Detail page appears, showing the findings for that checker run.
5. Click Runs to display the Runs subpage.

Enterprise Manager displays more information about the checker run.
6. Click View Report to view the report for the checker run.

The report is displayed in a new browser window.]]> <![CDATA[Viewing Checker Reports]]> http://naturebios.com/natureboard/showthread.php?tid=2160 Thu, 05 Nov 2009 15:23:13 -0800 http://naturebios.com/natureboard/showthread.php?tid=2160
After a checker has run, you can view a report of its execution. The report contains findings, recommendations, and other information. You can view reports using Enterprise Manager, the ADRCI utility, or the DBMS_HM PL/SQL package. The following table indicates the report formats available with each viewing method.

Results of checker runs (findings, recommendations, and other information) are stored in the ADR, but reports are not generated immediately. When you request a report with the DBMS_HM PL/SQL package or with Enterprise Manager, if the report does not yet exist, it is first generated from the checker run data in the ADR, stored as a report file in XML format in the HM subdirectory of the ADR home for the current instance, and then displayed. If the report file already exists, it is just displayed. When using the ADRCI utility, you must first run a command to generate the report file if it does not exist, and then run another command to display its contents.

The preferred method to view checker reports is with Enterprise Manager. The following sections provide instructions for all methods:

* Viewing Reports Using Enterprise Manager
* Viewing Reports Using DBMS_HM
* Viewing Reports Using the ADRCI Utility]]>
After a checker has run, you can view a report of its execution. The report contains findings, recommendations, and other information. You can view reports using Enterprise Manager, the ADRCI utility, or the DBMS_HM PL/SQL package. The following table indicates the report formats available with each viewing method.

Results of checker runs (findings, recommendations, and other information) are stored in the ADR, but reports are not generated immediately. When you request a report with the DBMS_HM PL/SQL package or with Enterprise Manager, if the report does not yet exist, it is first generated from the checker run data in the ADR, stored as a report file in XML format in the HM subdirectory of the ADR home for the current instance, and then displayed. If the report file already exists, it is just displayed. When using the ADRCI utility, you must first run a command to generate the report file if it does not exist, and then run another command to display its contents.

The preferred method to view checker reports is with Enterprise Manager. The following sections provide instructions for all methods:

* Viewing Reports Using Enterprise Manager
* Viewing Reports Using DBMS_HM
* Viewing Reports Using the ADRCI Utility]]> <![CDATA[run a Health Monitor Checker using Enterprise Manager]]> http://naturebios.com/natureboard/showthread.php?tid=2159 Thu, 05 Nov 2009 15:21:43 -0800 http://naturebios.com/natureboard/showthread.php?tid=2159
1. On the Database Home page, in the Related Links section, click Advisor Central.

2. Click Checkers to view the Checkers subpage.

3. In the Checkers section, click the checker you want to run.

4. Enter values for input parameters or, for optional parameters, leave them blank to accept the defaults.

5. Click Run, confirm your parameters, and click Run again.]]>
1. On the Database Home page, in the Related Links section, click Advisor Central.

2. Click Checkers to view the Checkers subpage.

3. In the Checkers section, click the checker you want to run.

4. Enter values for input parameters or, for optional parameters, leave them blank to accept the defaults.

5. Click Run, confirm your parameters, and click Run again.]]>