Chatta subito con l'assistenza
Chat con il supporto

Space Manager with LiveReorg 9.1 - User Guide

Smart Start for Space Manager Collect Statistics Profile DML Activity Manage Storage Reorganize Objects with Reorg Manager Partition Tables with the Partitioning Wizard Run and Monitor Scripts Run Reports Options Troubleshooting Administration Functions Appendix About Us

Server Agent Options

Use this page to specify options for the Server Agent.

Note: Specify options for each database connection, separately.

To specify Server Agent options

  1. Select Tools | Options.
  2. Select Server Agent.
  3. Specify the following Server Agent options:

    Option Description
    Automatically check Server Agent status every N minutes Specify an interval at which Space Manager checks the status of the Server Agent.
  4. Click Save to save your settings.

  

Miscellaneous Options

Some of these options are for use when troubleshooting an issue with Space Manager. Modify these settings if instructed to by Support.

Note: Specify options for each database connection, separately.

To specify Miscellaneous Options

  1. In the Explorer window, select the database for which you want to specify options.
  2. Select Tools | Options | Miscellaneous.
  3. Modify options by entering a new value. Review the following for additional information:

    Option Description
    ALWAYS USE SAMPLE_LONG ORA600 WORKAROUND FOR ANALYSIS

    default = FALSE

    SPC QUERIES HINT MODE

    default = LEGACY

  4. Click Save to save your settings.

  

Troubleshooting

Troubleshoot Script Execution Errors

Use the following information to help you investigate, troubleshoot, and resolve errors that occur during script execution.

See also:

General Guidelines

  1. Determine how to proceed based on the reorg type and the reorg stage.

    After you encounter an error, identify the type of reorganization that failed and the stage at which the reorganization process failed. Use this information to determine how to proceed. Use the following table to guide you through this process:

    Type of Reorganization Stage at which script failed General Guidelines
    Live Reorganization Before the switch

    Try to correct the issue and restart the script (see step 2).

    If you cannot resolve the issue, you can clean up and abandon the job (see step 3).

      Inside the switch Contact Quest Software Support.
      After the switch The database is operational but you can call Quest Software Support to report the error. However, this is not an urgent issue because the database is operational.
    Standard Reorganization  

    If the script fails during script execution for a standard reorg, you must fix the error and complete the script. If you do not, you would have tables without indexes, tables with wrong names, and other issues that would prevent you from using the database.

    Proceed to step 2.

    The Clean up command is disabled for Standard Reorganizations.

  2. For errors that did NOT occur inside the switch, try to resolve the issue and then restart the script.

    Identify and resolve issue: Review the error message and the script execution logs to identify the cause of the error and then fix the issue. See the following for additional information:

    1. For Oracle errors, see Common Oracle Errors Found in Script Execution or the Oracle documentation.
    2. For more information about the status code, see Status Codes for Script Execution.
    3. For more information about QSA errors, see QSA Error Messages.
    4. For help finding script logs, see Where to Find Script Logs.

    Restart script: After you resolve the problem that caused a reorganization script to fail, you can restart script execution using the Restart function in the Script/Job Monitor. See Restart Scheduled Scripts for more information.

  3. If you are unable to resolve the issue or restart the script, clean up the script.

    1. To clean up a script, right-click the script in the Script/Job Monitor and select Clean up. If the Clean up option is disabled, call Quest Software Support.
    2. After cleaning up the script, you can delete it (or attempt to re-run it if the objects to be reorganized are unchanged).

Common Oracle Errors Found in Script Execution

Review the following Oracle error messages and resolution guidelines.

Error Code Description / Guidelines

ORA-01651 to ORA-01656

Unable to extend <object>

If you encounter an "ORA-01653 unable to extend table..." or another unable to extend error, there is not enough space for the reorganization.

To resolve the issue, add space to accommodate the reorganization.

  1. Add space where needed.
  2. Restart the script.
  3. If you are unable to restart the script, use the Clean up command. Then delete the script (or attempt to re-run the script).

ORA-00054

Resource busy and acquire with NOWAIT specified

If you encounter an ORA-00054 error, the script is waiting for one or more tables that are currently locked by another session. You may also see the following Space Manager status message: 610 Blocking.

Background: Space Manager must get a very brief exclusive lock on a table during a reorganization and also may perform an ALTER TABLE/INDEX statement which requires a DDL lock. This can lead to locking issues.

To resolve this issue, do the following:

  1. Review the execution logs to determine which table or index is causing the locking issue.
  2. If possible, find the session that is locking this table. Then resolve any issues with this session, if necessary, to unlock the table allowing the script to continue.
  3. If you are unable to unlock the table, Cancel the script. Then do one of the following.
    • Restart the script at a later time when database activity is light.
    • Use the Clean up command and then delete the script. Then recreate and run the script later, when database activity is light.
    • If you cannot restart the script and the Clean up command is disabled, contact Quest Software Support.

Wait to Restart vs Clean Up and Recreate Script

Wait to restart: While the script is waiting to be restarted, Space Manager is tracking changes to the table. This information is stored in a table which grows as changes are tracked. In addition, the tracking activity adds traffic to the database. The advantage of this method is that you do not need rerun all the reorg steps from the beginning or recreate the script.

Clean up and recreate script: If you use the Clean up command, Space Manager stops tracking changes, removes tracking triggers and procedures, and deletes the copy table. This removes any tracking items and traffic that might interfere with database performance. However, if you want to run the script again, you must recreate it.

Where to Find Script Logs

You can investigate script execution errors from several different areas in the Space Manager interface. The area you work from is determined by how the script was executed: interactively in the Editor or as a scheduled script.

Scheduled Script Execution

Use the Script/Job Monitor to identify any errors that occur during scheduled script execution. The monitor displays a list of all scripts that are stored in the Space Manager repository for a database.

As you check for execution errors in the Script/Job Monitor, you can view script execution logs. A script log is generated by QSA for each script it runs. An additional log is generated each time a script is restarted.

  • To open script logs, right-click a script in the Detail pane and select Retrieve Script Logs.

Script logs contain information that can be used by Quest Software Support to diagnose script problems. This information includes platform version, Oracle version, execution start and stop times, steps performed by a script, and errors. Logs can be e-mailed to Support.

Log generation begins as soon as script generation begins. Script logs are stored along with the QSA log in the Log directory under QSA’s installation directory. How long log files are kept is determined by QSA’s MAX_LOG_AGE parameter. See QSA Server Agent Parameters for more information.

Note: The QSA log contains information on all activity performed by QSA. This information includes commands received, processes spawned, and scripts executed. The log for the current date is called QEXECD.LOG. The names of logs for previous dates include the date a log was generated (for example, QEXECD-01-01-2004.LOG).

Interactive Execution (in the Editor)

Interactive execution of reorganization scripts is performed from the SQL Editor. The status of each script statement is displayed in the status bar at the bottom of this window. If an error occurs, an error message is displayed in the status bar.

An execution log for all script statements is displayed in the Spool tab of the SQL Editor.

 

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione