A Comprehensive Website for Oracle Learning

Go to the EM Database Control home page as sysdba and click on Administration > Users. On the Users page click on the Create button. You should specify the Name, Enter Password, Confirm Password, Default Tablespace, and Temporary Tablespace fields. Then click on the Roles tab and click on the Modify button; select Resource and DBA from the Available Roles list. Once done, click the Move link and click the OK button. When you are returned to the Create User page, click the OK button.

How to export tables from a schema by using the EM Database Control?

Go to the EM Database Control home page as sysdba. Click on the Administration tab; click on the Tables link in the Schema region; select your schema in the Schema field in the Search region; and click the Go button. Select your exported tables from the Results region; Select Show Dependencies from the Actions drop-down list; and then click on the Go button in the Results region. On the Export:Review page, click on the Submit Job button.

How to import tables from a schema by using the EM Database Control?

Go to the EM Database Control home page as sysdba. Click on the Maintenance link; click on the Import from Files link; make sure that the Database Version of Files to Import field is set to 10g or later; and click the Go button. In the Files region, select your directory from where the Data Pump Import job can retrieve your exported Dump File Set; in the Import Type region, select the Tables option button and make sure the host credentials are correct; then click on the Continue button. Click on the Add button and in the Import:Add Tables page, enter your schema in the Schema field in the Search region; then click on the Go button. Select your tables; click on the Select button and On the Import:Re-Mapping page, click the Add Another Row button in the Re-Map Schema region. Then click the Next button. On the Import:Review page, click on the Submit Job button; on the Status page, click the View Job button; refresh your browser page until the Status field reads Succeeded; and then click the Import link to look at the log file.

How to retrieve the latest ADDM and determine the cause of the problem by using the EM Database Control?

Go to the EM Database Control home page as sysdba and click on the Advisor Central link on the Related Links section; select ADDM in the Advisory Type drop-down list; select Last 24 Hours in the Advisor Runs drop-down list; and when it is done, click on the Go button. Then select the latest ADDM task completed by the ADDM user; click the View Result button; it brings you to the ADDM page and you can see the results for the Performance Analysis; To investigate further, click on the SQL Statements Consuming Significant Database Time Were Found link; it will brings you to the Performance Finding Details page. To tune the statement, click on the Run Advisor Now button; when the analysis finished, you will be directed to the Recommendations for SQL ID: � page.

How to shutdown and startup your databases using an initialization parameter file by using the EM Database Control?

Go to the EM Database Control home page as sysdba; click the Shutdown button; you should specify host and target database credential; save them and click on the OK button. On the Startup/Shutdown: Confirmation page click the Yes button; on your database page, click the Startup button. Click on the Advanced Options button; make sure that you select your initialization parameter file with its location and then click the OK button.

How to check or resize the size of the various SGA buffers using the EM Database Control and how to enable the Automatic Shared Memory Management by using the EM Database Control?

Go to the EM Database Control home page as sysdba; click on the Administration tab; click on the Memory Parameters link in the Instance region; and now you should see the status Automatic Shared Memory Management (disable or enable), Shared Pool size, Buffer Cache, Large Pool, Java Pool, Total SGA, Maximum SGA size, etc. If the Automatic Shared Memory Management was disabled on the Memory Parameters page, click on the Enable button to enable it.

How to look at the corresponding metrics graphic rate by using the EM Database Control?

Go to the EM Database Control home page as sysdba; click on the All Metrics link; expand the Throughput link; under this link, click on the User Commits (per second) link; make sure that the View Data field is set to very short period of time (ex: Real Time: 10 Seconds Refresh). Now here, depending on your workload, you should be able to see your graph.

How to find a high load SQL statement and fix it by looking at an ADDM report analysis during the problem by using the EM Database Control?

Go to the EM Database Control home page as sysdba.

If the time corresponding to the problem corresponds with the latest ADDM, then you should find the link corresponding to the correct performance analysis directly in the Diagnostic Summary region of the EM Database Control home page. If not, then go to the Advisor Central page and search for the correct ADDM task. To fix your problem, click the finding with the highest impact on the database time on the corresponding ADDM page. It should correspond to a SQL Tuning recommendation. On the Performance Finding Details page, you see the high-load SQL statement; click on the Run Advisor Now button for the highest high-load SQL statement detected. Once was done, you get the details of the corresponding recommendations. You can also click on the Original Explain Plan button to see the original plan. If you have a proposed SQL command, click on the Implement button after selecting the appropriate SQL command from the Recommendations table.

How to determine the most important wait category from the Sessions: Waiting and Working graph by using the EM Database Control?

Go to the EM Database Control home page as sysdba; click on the Performance tab; if the period for which you want to observe your database is on the Sessions: Waiting and Working graph use the current graph. If the period is no there, select the Historical value from the View Data drop-down list. On the Performance page click on the User I/O category in the graph�s legend. Select the top SQL statement and you should see the SQL Details page. Click on the Execution History tab to see what is going on to the statement. Shorten the Seconds Per Execution option button to get a better understanding of the graph.

How to use the SQL Access Advisor to generate recommendations for a SQL tuning set by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Advisor Central link; click on the SQL Access Advisor link; then select the Import Workload from SQL Repository option button; set the SQL Tuning Set field to your SQL tuning set; and click the Next button. On this page, click on the Both Indexes and Materialized Views and Comprehensive Mode buttons; click on the Show Advanced Options link and make sure your options are set and then click the Next button. On the SQL Access Advisor: Schedule page, select Standard in the Schedule Type file; on the Review page, click the Submit button. Refresh the page until you get the COMPLETED status. Now, select your SQL Access Advisor task and click the View Result button.

How to create a job and schedule it to run by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; in the Scheduler section, click on the Jobs link; on this page, click on the Create button; on the page, enter a job name the Name field; make sure the set the Logging Level (RUNS), Job Class (DEFAULT_JOB_CLASS), Auto Drop (FALSE), and the Restartable (FALSE). In the Command section, click the Change Command Type button; on the Select Command Option, select the In-line Program: Executable radio button; and the click the OK button. On the Create Job page, enter your shell executable job in the Executable Name field and click the Schedule tab. On this page, select your schedule option and click OK.

How to verify that your schedule was created by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; click the Schedules link on the Scheduler region.

How to change the Tablespace Space Usage thresholds of a tablespace by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; click on the Tablespaces link; select your tablespace; select Edit > Thresholds; select Specify Thresholds, by percent used; now specify the Warning(%) and Critical (%) fields; and then click the Apply button.

How to run the Segment Advisor for a tablespace by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; click on the Tablespace link; select your tablespace; select Run Segment Advisor from the Action field; click the Go button; check that the Comprehensive mode is selected; click the Continue button; check all the options on the Segment Advisor: Schedule, and Options pages. On the Review page, click the Submit button and click refresh until you see COMPLETED. Select your task and click the View Result button to go to the Segment Advisor Recommendations page. Accept all recommendation and then click the Schedule Implementation button.

How to use the Undo Advisor to get recommendations to size your UNDO tablespace by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; click on the UNDO Management link; on this page change the Analysis Time Period field to Last One Hour and click the Update Analysis button; on the UNDO Management page, you should see Oracle 10g recommendation; click the UNDO Advisor button to get more info. You can look at the Required Tablespace Size by Undo Retention Length graph. If you change the New Undo Retention field for example you will see the impact on your undo tablespace size. On the UNDO Management page, click the Edit Undo Tablespace button. Change the size or add datafile and click the Continue button. After you are back to the Edit Tablespace page click the Apply button.

How to change the size of flash recovery area by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Maintenance tab; click on the Configure Recovery Settings link; in the Flash Recovery Area Size field, you can enter a value that you want to change and then click on the Apply button.

How to backup your whole database to tape by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Maintenance tab; click on the Schedule backup link; select Customized from the Backup Strategy drop-down list; select Whole Database; make sure that you enter your host credentials and click the Next button; on the Options page, click NEXT; on the Setting page, select Tape, then NEXT; on the Schedule page, click NEXT; on the Review page, click Submit Job. Now you should be able to view your job by clicking on the View Job button. Once the job is done, click the Backup link on the Execution page. You see what you want to see.

How to use the flashback feature to recover a dropped table by using the EM Database Control?

Let assume that still your table is in the Recycle Bin area. Go to the Recycle Bin page; select your object; and click the Flashback Drop button. You will be prompted to the Perform Recovery: Rename page to change the original name if you wish. Leave the original name, and click NEXT. On the Review page, click the Submit button and click OK on the Confirmation page. Now you should see the Recycle Bin page and your object should have been disappeared.

How to determine the Redo Log File size using the Sizing Advice by using the EM Database Control?

Go to the Database Control home page as sysdba; click on the Administration tab; click on the Redo Log Groups link; on this page you can see the redo log group size; select Sizing Advice in the Actions drop-down list and click the Go button. Now, you should see the recommended optimal redo log file size in the Update Message region of the Redo Log Groups page.