iSelfSchooling - Final Exam Evaluation - Questions and Answers

Q: Set the USER Commits Per Sec metric with a warning threshold set 3, and a critical threshold set to 7. Your observation period should be for one minute, and the number of consecutive occurrences should be set to two.

SQL> BEGIN

DBMS_SERVER_ALERT.set_threshold (

DBMS_SERVER_ALERT.user_commits_sec,

DBMS_SERVER_ALERT.operator_ge, 3,

DBMS_SERVER_ALERT.operator_ge, 7,

1,2, ‘school’,

DBMS_SERVER_ALERT.object_type_system, null);

END:

Check that the metrics thresholds have been set.

SQL> COL object_name FORMAT a30

SQL> COL metrics_name FORMAT a30

SQL> COL warning_value FORMAT a10

SQL> COL critical_value FORMAT a10

SQL> SELECT object_name, metrics_name, warning_value, critical_value

FROM dba_thresholds;

Q: How do you examine your outstanding alerts and alert history?

SQL> SELECT reason FROM dba_outstanding_alerts;

SQL> SELECT reason FROM dba_alert_history

WHERE upper(reason) like ‘%COMMIT%’

ORDER BY creation_time desc

Q: How to clean up your threshold set up?

A: Do the following statement as sysdba. Set threshold values to NULL.

SQL> BEGIN

DBMS_SERVER_ALERT.set_threshold (

DBMS_SERVER_ALERT.user_commits_sec,

NULL,

1, 1, ‘SCHOOL’,

DBMS_SERVER_ALERT.object_type_system, NULL);

END;

Q: How do you disable your ALERT_MYUSER and remove subscriber?

SQL> EXEC dbms_aqadm.disable_db_access(‘ALERT_MYUSER’,’SYSTEM’);

SQL> BEGIN

dbms_aqadm.remove_subscriber(

‘SYS.ALERT_QUE’,AQ$_AGENT(‘ALERT_MYUSER’,’’,0);

END;

Application tuning

Q: What is the OPTIMIZER_DYNAMIC_SAMPING setting default?

A: To enhance Query Optimization, the OPTIMIZER_DYNAMIC_SAMPING is set to 2 by default.

Q: How do you disable the Automatic PGA Memory Management?

A: In order to disable the Automatic PGA Memory Management set the parameter to 0.

Q: How do you gather statistics on dictionary tables in the Oracle 10g Database?

A: In Oracle Database 10g, you can gather statistics on dictionary tables (both fixed and real) to get the best performance. You use the DBMS_STATS.GATHER_DATABASE_STATS procedure with GATHER_SYS argument set to TRUE or DBMS_STATS.GATHER_DICTIONARY_STATS. To use this, you should have the ANALYZE ANY DICTIONARY system privilege. For example:

SQL> BEGIN

DBMS_STATS.GATHER_DATABASE_STATS(options=’GATHER AUTO’);

END;

/ -- Note: you should use GATHER only if you are using release 8i

Q: What is the Automatic Tuning Optimizer (ATO)?

A: It is a SQL automatic tuning optimaizer. When the optimizer is tuning a SQL statement using ATO, it is called Automatic SQL Tuning.

Q: How do you perform automatic SQL tuning?

Create a binding variable and then move your query into it.

SQL> VARIABLE my_query VARCHAR2(1000)

SQL> BEGIN

:my_query := ‘SELECT ename FROM iself.emp WHERE empno = 100;’

END;

Q: How do you use the DBMS_SQLTUNE package to create a tuning task by calling the CREATE_TUNING_TASK function?

A: We use the DBMS_SQLTUNE package to create a tuning task by calling the CREATE_TUNING_TASK function. This procedure creates an advisor task and sets its corresponding parameters according to the user-provided input arguments. To execute this you need one more binding variable to keep your task name.

SQL> VARIABLE my_task VARCHAR2(100)

SQL> BEGIN

:my_task := DBMS_SQLTUNE.create_tuning_task (

SQL_TEXT => :my_query,

BIND_LIST => SQL_BINDS(anydata.ConvertNumber(100)),

USER_NAME => ‘ISELF’,

SCOPE => ‘COMPREHENSIVE’,

TIME_LIMIT => 60,

TASK_NAME => ‘my_tuning_task’,

DESCRIPTION => ‘Query on EMP table …’);

END;

Q: How do you use the EXECUTE_TUNING_TASK procedure to start the tuning process?

A: You need to invoke the EXECUTE_TUNING_TASK procedure to start the tuning process.

SQL> BEGIN

DBMS_SQLTUNE.execute_tuning_task (TASK_NAME=>:my_task);

END;

Q: How do you call the REPORT_TUNING_TASK function to visualize the tuning results?

A: The following is an example of how to call the REPORT_TUNING_TASK function to visualize the tuning results.

SQL> SQL> SELECT DBMS_SQLTUNE.report_tuning_task

(TASK_NAME=>:my_task)

FROM dual;

Q: How do you store a SQL profile in the data dictionary?

A: When the SQL Tuning Advisor recommends a SQL Profile, then create the SQL Profile by calling the ACCEPT_SQL_PROFILE function, which stores it in the data dictionary. You should have the CREATE ANY SQL PROFILE privilege.

SQL> VARIABLE my_profile VARCHAR2(1000)

SQL> BEGIN

:my_profile := DBMS_SQLTUNE.accept_sql_profile

(TASK_NAME => ’my_tuning_task’);

END;

SQL> SELECT :my_profile FROM dual;

Database Resource Manager

Q: How to do you use the DBMS_RESOURCE_MANAGER package to tell the PMON process to kill sessions that are idle for longer than 600 seconds and kill sessions that are idle for more than 300 seconds and are blocking other sessions. We assumed that you have already defined your plan as ‘MY_DAY_PLAN’ and your group as ‘MY_FIRST_GROUP.’

SQL> BEGIN

DBMS_RESOURCE_MANAGER.create_plan_directive (

PLAN => ‘MY_DAY_PLAN’,

GROUP_OR_SUBPLAN => ‘MY_FIRST_GROUP’,

COMMET => ‘Limit user idle time’,

MAX_IDLE_TIME => 600,

MAX_IDLE_BLOCKER_TIME => 300);

END;

Scheduler

Q: What is the DBMS_SCHEDULER package?

A: Oracle Database 10g provides scheduling capabilities through the database Scheduler. It uses the DBMS_SCHEDULER package. The Scheduler offers far more functionality than the DBMS_JOB package. You can create a job class a job class always belongs to the SYS schema. Since the priority of jobs change over a period, now you can also create a window. For example: you can create a window for the month of October that uses the END_OF_YEAR plan and is active every day from 8:00 a.m. to 6:00 p.m. Eastern standard Time (EST).

Q: How do you monitor a Job using the Database Control page?

A: Log in to EM Database Control as the ISELF user. From the Database Control home page click on the Administration tab. In the ‘Scheduler’ region, click the ‘Jobs’ link.

You should see:

One schedule, called DAILY_PURGE_SCHEDULE by clicking the Schedulers link,

Two windows, called WEEKNIGHT_WINDOW and WEEKEND_WINDOW by clicking the Windows link, and

Two job classes, called DEFUALT_JOB_CLASS, AUTO_TASKS_JOB_CLASS by clicking the Job Classes link.

Q: How do you add a Job using the Database Control page?

A: Click the Create button on the Scheduler Jobs page; fill out the applicable fields;

Back to the Create Job page; enter location of your job script in the Executable Name field; and then click the Schedule tab.

On the Schedule page, make sure that the immediate radio button and the Repeat field are set to Do Not Repeat.

Q: How to create a schedule named MY_SCHEDULE owned by ISELF that executes every five seconds.

SQL> CONNECT iself/schooling

SQL> BEGIN

DBMS_SCHEDULER.create_schedule (

SCHEDULE_NAME => ‘MY_SCHEDULE’,

START_DATE => SYSTIMESTAMP,

REPEAT_INTERVAL => ‘FREQ=SECONDLY;INERVAL=5’,

COMMENTS => ‘This is my first created schedule.’);

END;

Now, you should be able to see it in your Database Control page.

Q: How to schedule a job that calls your created online-backup every night at 10 p.m.?

A: You should have been granted CREATE JOB to be able to create a job. Jobs are created as disabled by default. You must enable them explicitly.

SQL> BEGIN

DBMS_SCHEDULER.create_job (

JOB_NAME => ‘ISELF.ONLINE_BACKUP’,

JOB_TYPE => ‘EXECUTABLE’,

JOB_ACTION => ‘/home/my_Nightly_online_backup.sh’,

START_DATE => TRUNC(SYSDATE+1)+22/24,

REPEAT_INTERVAL => ‘TRUNC(SYSDATE+1)+22/24’,

COMMENTS => ‘My nightly online backup’);

END;

Q: Assuming that you have a procedure that collects information daily called DAILY_DATA_GATHERING. Now, you should create a problem to call this procedure and create a job to run it daily. How you do that?

SQL> BEGIN

DBMS_SCHEDULER.create_program (

PROGRAM_NAME => ‘DAILY_GATHERING’,

PROGRAM_ACTION => ‘ISLEF.DAILY_DATA_GATHERING’,

PROGRAM_TYPE => ‘STORED_PROCEDURE’,

ENABLED => TRUE);

END;

SQL> BEGIN

DBMS_SCHEDULER.create_job (

JOB_NAME => ‘ISELF.DAILY_GATHERING_JOB’,

PROGRAM_NAME => ‘ISLEF.DAILY_GATHERING’,

START_DATE => TRUNC(SYSDATE+1)+22/24,

REPEAT_INTERVAL => ‘TRUNC(SYSDATE+1)+22/24’,

COMMENTS => ‘Daily Data Gathering Job.’);

END;

You could also use your created schedule:

SQL> BEGIN

DBMS_SCHEDULER.create_job (

JOB_NAME => ‘ISELF.DAILY_GATHERING_JOB’,

PROGRAM_NAME => ‘ISLEF.DAILY_GATHERING’,

SCHEDULE_NAME => ‘MY_SCHEDULE’);

END;

Tablespace Monitoring

Q: How an alert will raise or clear based on a tablespace size?

A: In the Oracle Database 10g, tablespace thresholds are defined in terms of a percentage of the tablespace size. When the threshold crosses their limits, an appropriate alert will raise or clear.

Q: When do you need to enable row movement on a segment?

A: Since a shrink operation may cause ROWIDs to change in heap-organized segment, before executing a shrink operation you should enable row movement on a segment.

For example:

SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;

SQL> ALTER TABLE emp SHRINK SPACE CASCADE;

Q: On the USERS tablespace, set a warning threshold of 80% and a critical threshold of 95%.

SQL> BEGIN

DBMS_SERVER_ALERT.set_threshold (

DBMS_SERVER_ALERT.tablespace_pct_full,

DBMS_SERVER_ALERT.operator_ge, 80,

DBMS_SERVER_ALERT.operator_ge, 95, 1, 1, NULL,

DBMS_SERVER_ALERT.object_type_tablespace, ‘USERS’);

END;

You can use the NULL value to return to the database-wide default values.

Q: How do you check the database-wide threshold values for the USERS tablespace?

SQL> SELECT warning_value, critical_value

FROM dba_thresholds

WHERE metrics_name = ‘Tablespace Space Usage’ AND

object_name = ‘USERS’

Q: How do you turn off the space-usage tracking for the USER tablespace?

SQL> BEGIN

DBMS_SERVER_ALERT.set_threshold (

DBMS_SERVER_ALERT.tablespace_pct_full,

DBMS_SERVER_ALERT.operator_do_not_check, ‘0’,

DBMS_SERVER_ALERT.operator_do_not_check, ‘0’, 1, 1, NULL,

DBMS_SERVER_ALERT.object_type_tablespace, ‘USERS’);

END;

Q: How do you reset the database-wide threshold values of the USERS tablespace to the default database values?

SQL> BEGIN

DBMS_SERVER_ALERT.set_threshold (

DBMS_SERVER_ALERT.tablespace_pct_full,

NULL, NULL, NULL, NULL, 1, 1, NULL,

DBMS_SERVER_ALERT.object_type_tablespace, ‘USERS’);

END;

Q: How do you check the status of your threshold?

SQL> SELECT reason, resolution

FROM dba_alert_history

WHERE object_name = ‘USERS’;

SQL> SELECT reason, message_level

FROM dba_outstanding_alerts

WHERE object_name = ‘USERS’;

Big and small file tablespaces

Q: What is a BIGFILE tablespace?

A: It is the Oracle Database 10g feature. A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size and on the other hand a smallfile tablespace can contain many data files. The size of a bigfile can reach to 128TB depending on the Oracle block size. An Oracle database can contain both bigfile and smallfile tablespaces. You can change the default tablespace type to BIGFILE or SMALLFILE.

Q: How do you set the default tablespace type to BIGFILE?

A: To set the default tablespace type to BIGFILE, you can use either CREATE DATABASE or ALTER DATABASE.

Q: how do you display the default tablespace type?

A: You use the DATABASE_PROPERTIES dictionary view to display the default tablespace type for the database:

SQL> SELECT property_value FROM database_properties

WHERE property_name = ‘DEFAULT_TBS_TYPE’;

Q: Use the DBA_TABLESPACES dictionary view to display whether all tablespace is bigfile (YES) or smallfile (NO).

SQL> SELECT tablespace_name, bigfile FROM dba_tablespaces;

Q: Use the V$TABLESPACE dynamic view to display whether all tablespace is bigfile (YES) or smallfile (NO).

SQL> SELECT name, bigfile FROM v$tablespace;

Q: What are the difference between a BIGFILE rowid and a small file rowid?

A: Extended ROWID format:

For Smallfile tablespaces is Object# - File# - Block# - Row#

For Bigfile tablespaces is Object# - Block# - Row#

Q: Create a temporary tablespace group that it consists of only temporary tablespaces.

SQL> CREATE TEMPORARY TABLESPACE mytemp1

TEMPFILE ‘temp_01.dbf’ SIZE 500M

TABLESPACE GROUP mygroup;

The mygroup group has one more temporary tablespace in its groups. If you do not want to assign any temporary tablespace to a group do the following:

SQL> CREATE TEMPORARY TABLESPACE mytemp2

TEMPFILE ‘temp_02.dbf’ SIZE 500M

TABLESPACE GROUP ‘’;

Q: Use the DBA_TABLESPACE_GROUPS view to display all tablespace associated to their groups.

SQL> SELECT tablespace, group_name FROM dba_tablespace_groups;

Q: Create a tablespace with a BIGFILE default tablespace type.

SQL> CREATE BIGFILE UNDO TABLEPSACE my_big_tbs

DATAFILE ‘/u01/oradatta/tbs_01.dbf’ SIZE 1G;

Q: Can you add more datafiles?

A: If you try to add more datafile to above tablespace, do the following.

SQL> ALTER TABLESPACE my_big_tbs

ADD DATAFILE ‘/u02/oradata/tbs_02.dbf’ SIZE 100k;

Notice, since a bigfile tablespace can contain only one data file, your command should fail.

Q: How do you get a BIGFILE ROWID?

A: To get its ROWID, you should use the following database package (DBMS_ROWID).

SQL> SELECT distinct DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID,’BIGFILE’)

FROM test_rowid;

General Storage Enhancements

Q: What is System Auxiliary Tablespace in the Oracle 10g Database?

A: The Oracle 10g database added one more tablespace (SYSAUX) to help the SYSTEM tablespace. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. It is a mandatory tablespace and should be created at database creation time. You cannot drop or rename this tablespace.

Q: What does the SYSAUX tablespace hold?

· Text, Ultra Search

· Intermedia, Spatial

· Scheduler

· OLAP

· XML DB

· Workspace Manager

· Data Mining

· Server Manageability Components

· Recovery Catalog

· EM Repository

· Analytical Workspace Object table

· LogMinor, Log Standby, Streems

· Statspack

Q: How can you monitor the space usage of each occupant inside the SYSAUX tablespace?

A: Use the following SQL statement.

SQL> SELECT occupant_name, space_usage_kbytes

FROM v$sysaux_occupants

Q: What are the Mandatory attributes of SYSAUX?

· PERMANENT

· READ WRITE

· EXTENT MANAGEMENT LOCAL

· SEGMENT SPACE MANAGEMENT AUTO

Q: What are its benefits?

A: It reduces the number of tablespaces to manage. You don’t need to create the TOOLS, OEM_REPOSITROY, DRSYS, CWMLITE, ODM, etc tablespaces.

You reduce the load on the SYSTEM tablespace.

Q: Can you rename SYSTEM or SYSAUX tablespaces?

A: Now you can rename tablespace in the Oracle 10g database. You can not rename the SYSTEM and SYSAUX tablespaces. You can rename any permanent or temporary tablespaces. All tablespaces must have their datafiles online. Your database spfile will be updated.

Q: How do you rename a tablespace?

A: To rename a tablespace do the following SQL statement.

SQL> ALTER TABLESPACE tools RENAME TO my_tools;

Q: Can you change the default Permanent Tablespace in the Oracle 10g database?

A: Now, you can change the default permanent tablespace for non-system users.

Q: How can you check what your default permanent tablespace is?

A: Query the following SQL statement.

SQL> SELECT property_value

FROM database_properties

WHERE property_name =

‘DEFAULT_PERMANENT_TABLESPACE’

Q: Change your default tablespace to USERS.

A: Assuming your default tablespace is SYSTEM, and then you change it to USERS.

SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Q: How can you copy files without using system operating system commands?

A: You can use the COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file from one place to another. You should make sure to create source and destination directories and you have a read access on the source directory and a write access on the destination directory.

Assuming we are going to copy file a1.dbf from location /u01/oradata/school to location /u02/oradata/school.

SQL> CREATE DIRECTORY my_source AS ‘/u01/oradata/school’;

SQL> CREATE DIRECTORY my_destination AS ‘/u02/oradata/school’;

SQL> BEGIN

DBMS_FILE_TRANSFER.COPY_FILE (

SOURCE_DIRECTORY_OBJECT => ‘MY_SOURCE’,

SOURCE_FILE_NAME => ‘a1.dbf’,

DESTINATION_DIRECTORY_OBJECT => ‘MY_DESTINATION’,

DESTINATION _FILE_NAME => ‘a1.dbf’);

END;

Q: What does the Redo Logfile size advisor in the Oracle 10g database?

A: It determines the optional smallest online redo log file size based on the current FAST_START_MTTR_TARGET setting and the corresponding statistics. To enable the Redo Logfile Size Advisor, you should set the FAST_START_MTTR_TARGET parameter. Note that an online redo log file size is considered optimal if it does not drive incremental check pointing more aggressively than needed by FAST_START_MTTR_TARGET.

Q: Make the retention period for one day with an interval off “0” which switches off snapshot collection.

SQ> EXEC dbms_workload_repository.modify_snapshot_settings (1440, 0); -- 1440 minute or 1 day and “0” switches OFF snapshot collection.

Automatic Storage Management (ASM)

Q: What does ASM provide?

A: ASM provides a vertical integration of the file system and the volume manager that is specifically built for Oracle database files.

Q: What are its key features and benefits?

· Stripes files rather than logical volumes

· Online disk reconfiguration and dynamic rebalancing

· Adjustable rebalancing speed

· Provides redundancy on a file basis

· Supports only Oracle database files

· Custer-aware

· Automatically installed

Q: Does ASM have a data dictionary?

A: ASM instance does not have a data dictionary and it is restricted to few SQL commands and Dynamic Performance view.

Q: How do you create a disk group by using ASM?

A: The following are examples of creating and deleting disk groups.

Creating a diskgroup:

SQL> CREATE DISKGROUP dgora1 NORMAL REDUNDANCY

FAILGROUP controller1 DISK

‘/dev/rdsk/c0t0d0s2’ NAME mydisk SIZE 200G FORCE,

‘/dev/rdsk/c0t1d0s2’,

‘/dev/rdsk/c0t2d0s2’

FAILGROUP controller2 DISK

‘/dev/rdsk/c1t0d0s2’,

‘/dev/rdsk/c1t1d0s2’,

‘/dev/rdsk/c1t2d0s2’;

Q: How do you delete a disk group by using ASM?

A: Dropping a diskgroup:

SQL> DROP DISKGROUP dgora1 INCLUDING CONTENTS;

Q: How do you add a disk to an existing disk group?

A: The following are examples of how to add disks to an existing disk group.

SQL> ALTER DISKGROUP dgora1

ADD FAILGROUP controller1

‘/dev/rdsk/c0t3d0s2’ NAME a5;

Q: How do you remove a disk to an existing disk group?

A: To remove a disk:

SQL> ALTER DISKGROUP dgora1 DROP DISK a5;

Q: Can you undo the removed disk? How?

A: Yes. To undo the removed disk do the following SQL statement. This only works if the status of drop is pending or the drop function was not completed yet.

SQL> ALTER DISKGROUP dgora1 UNDROP DISKS;

Q: How do you display a list of your diskgroups?

A: To display a list of diskgroups.

SQL> SELECT name FROM v$asm_diskgroup;

Q: How do you display a list of associated ASM disks?

A: To display a list of associated ASM disks.

SQL> COL name FORMAT a20

SQL> COL failgroup FORMAT a20

SQL> SELECT name, failgroup, bytes_read, bytes_written

FROM v$asm_disk

Q: How do you display a list of associated ASM files?

A: To display a list of associated ASM files.

SQL> SELECT group_number, file_number, bytes, type, striped

FROM v$asm_file

Q: How do you create a tablespace that uses an ASM disk group?

A: To create a tablespace that is stored in the ASM disk group dgora1.

SQL> CREATE TABLESPACE mytablespace2

DATAFILE ‘+dgora1’ SIZE 100m

Q: How do you add one addition disk to your system?

A: Do the following.

SQL> HOST dd if=/dev/zero of=/u02/oradata/school/diska abs=1024k count=200

SQL> SELECT name, failgroup, bytes_read, bytes_written

FROM v$asm_disk

SQL> ALTER DISKGROUP dgora1

ADD DISK ‘/u02/oradata/school/diska’

Execute the following query until you get ‘no rows selected.’

SQL> SELECT operation, est_minutes

FROM v$asm_operation

Again, display a list of associated ASM disks.

SQL> SELECT name, failgroup, bytes_read, bytes_written

FROM v$asm_disk

Now, you should see one more disk was added to disk group.

Security VPD

Q: What is VPD in the Oracle 10g Database?

A: In the Oracle 10g database, there is a feature called Virtual Private Database (VPD). It enables you to build applications that enforce your security policy. When a user requests a query, the server dynamically modifies the user’s SQL statement, which is not transparent to the user. The modification is based on a WHERE clause returned by a function.

The Oracle 10g database more features that are important to note

Q: How do you use the V$FAST_START_TRANSACTIONS view to monitor (in real-time) normal transaction rollback and transaction recovery by SMON?

SQL> SELECT * FROM v$fast_start_ transactions;

Q: How do you use the V$FAST_START_SERVERS view to display historical information about transaction recovery?

SQL> SELECT * FROM v$fast_start_servers;

Q: How do you use the DBA_ENABLED_TRACES view to display enabled and disabled trace?

SQL> SELECT * FROM dba_enabled_traces;

Q: What is case-insensitive sorting in the Oracle 10g?

A: In the Oracle 10g database, you can use the ALTER SESSION command to change NLS_SORT for your session to use case-insensitive binary sorting.

Q: What is Quote Operator q?

A: Now, you can eliminate previous additional quotation string. See the following example:

SQL> UPDATE customers

SET comments =

q’X In this example, ‘X’ is used as the quotation mark delimiter. X’;

WHERE ID = 100;

SQL> COMMIT;

Q: What does the UTL_MAIL package?

A: You can send e-mail to a user. In order to use the package, you should have already run the utlmail.sql and prvtmail.plb scripts located in the $ORACLE_HOME/rdbms/admin directory.

Oracle Grid

Q: What is the Oracle 10g Database Grid?

A: Yesterday was Internet and today is Grid. It looks like the technology finds its way to hang on something to grow. Now, there is a committee called the Global Grid Forum (GGF) that is developing standards for Grid computing.

Q: List some of the most important features of the Oracle 10g Grid.

A: The following are some of the most important features that enable Grid computing at the Oracle 10g Database level:

· Automatic Storage Management

· Portable Clusterware

· High-Speed InfiniBand Network Support

· Real Application Clusters and automatic workload management

· Resource Manager

· Oracle Streams

· Centralized Management with Enterprise Manager Grid Control

· Oracle Database 10g New self-management feature

· …

Q: What is OEM 10g?

A: It is Oracle Enterprise Manager with features that are more new. It can automatically alert you to new critical patches. You can use the patch wizard to select an interim patch and see if any of your system requires it. It is integrated with a build-in MetaLink connection.

Q: How do you stop the Oracle Management Server?

# emctl stop oms

Q: How do you start the Oracle Management Server?

# emctl start oms

Q: How do you get the status of the Oracle Management Server?

A: Do the following command:

# emctl status oms

Q: How do you start EM Database Control?

# emctl start dbconsole

Q: How do you stop the EM Database Control?

# emctl stop dbconsole

Q: How do you get the status of the EM Database Control?

# emctl status dbconsole

Q: What is the default port for your Web Browser?

A: The default port is 5500 and you can access it by opening your Web browser and enter the following URL: http://myserver:5500/em

Navigate the Oracle EM Grid Control

Q: How do you create an Oracle user by using the EM Database Control?

A: 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.

Q: How do you export tables from a schema by using the EM Database Control?

A: 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.

Q: How do you import tables from a schema by using the EM Database Control?

A: 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.

Q: How do you retrieve the latest ADDM and determine the cause of the problem by using the EM Database Control?

A: 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.

Q: How do you shutdown and startup your databases using an initialization parameter file by using the EM Database Control?

A: 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.

Q: How do you 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?

A: 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.

Q: How do you look at the corresponding metrics graphic rate by using the EM Database Control?

A: 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.

Q: How do you 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?

A: 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.

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

A: 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 isgoing on to the statement. Shorten the Seconds Per Execution option button to get a better understanding of the graph.

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

A: 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.

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

A: 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.

Q: How do you verify that your schedule was created by using the EM Database Control?

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

Q: How do you change the Tablespace Space Usage thresholds of a tablespace by using the EM Database Control?

A: 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.

Q: How do you run the Segment Advisor for a tablespace by using the EM Database Control?

A: 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.

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

A: 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.

Q: How do you change the size of flash recovery area by using the EM Database Control?

A: 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.

Q: How do you backup your whole database to tape by using the EM Database Control?

A: 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.

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

A: 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.

Q: How do you determine the Redo Log File size using the Sizing Advice by using the EM Database Control?

A: 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.