OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P22

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

65
lượt xem 18
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P22: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P22

Using EM Support Workbench 981 Gathering Additional Diagnostic Information On the Problem Details screen, under the Investigate and Resolve section, you have various options. On the Self Service tab, you have links available to gather more information about the problem. If you prefer self-service, you can run more checks on the database, diagnose the current problem, or resolve the issue by running the SQL Repair Advisor (if the error is caused by a SQL Statement). The following are your options on the Self Service tab: NÛ Assess Damage NÛ Run Checkers: Run the database health check again to find more issues. NÛ Database Instance Health: Show the database health screen, which shows the num- ber of incidents and problems by hour for the last 24 hours. NÛ Diagnose NÛ Alert Log: Shows the alert log entries related to the incident. NÛ Related Problems Across Topology: Shows any other incidents that may be related to the current problem. NÛ Diagnostic Dumps for Last Incident: Shows the dump and trace files associated with the incident. NÛ Go to Metalink and Research: Metalink is Oracle’s support site, where you can search and find solutions to the issue. NÛ Resolve: NÛ SQL Repair Advisor: Since the problem is caused by a SQL statement, you can run the SQL Repair Advisor to fix the SQL issue. You can use the tools under Diagnose to gather more information about the incident and problem. If you have identified the SQL causing the problem and you find an issue with the SQL, you can resolve the problem by yourself. You may also get an answer by searching Oracle’s support website, Metalink. Contacting Metalink is discussed later in the chapter. After self-service, if you could not resolve the problem or could not find more informa- tion about the problem of symptoms, you can contact Oracle Support. EM Support Work- bench makes contacting Oracle Support easy without having to remember the URL or phone number and gathers all the information that would be of help to the support analyst. Let’s go through the next steps of Support Workbench to contact Oracle Support for a reso- lution to the example problem. Creating a Service Request If you cannot identify or resolve the issue yourself, the next logical step is to contact Oracle Support for help. On the Problem Details page shown in Figure 17.13, click the Oracle Sup- port tab under the Investigate and Resolve section. Figure 17.14 shows the Oracle Support tab.
982 Chapter 17 N Moving Data and Using EM Tools F i g U r E 17.1 4 Contacting the Oracle Support section of Support Workbench The Create a Service Request with Metalink link takes you to the Metalink login page, where you can log in and enter a service request. The URL for Metalink is https:// metalink.oracle.com. Once the service request (SR) is created, you can use the Edit button on the Problem Details screen to update the SR number. After you create the SR, the next step is to send relevant trace and dump files to Oracle for analysis. Support Workbench provides a packaging service to make this task easier for the DBA. Oracle Support Services (OSS) is a 24/7 operation providing support to all Oracle cus- tomers throughout the world. The primary method of contacting OSS is using the web page at https://metalink.oracle.com. You have to register for an account first and provide information such as the customer service identifier (CSI) number, your contact information, so on. Once you log in to Metalink, you can find a wealth of information, including the following: NÛ Searches for known issues NÛ Forum to discuss various issues NÛ Opening an SR NÛ Patches and updates NÛ Product certification NÛ Bug information NÛ Knowledge Base articles NÛ User documentation NÛ Electronic technical reference documents The Metalink forums enable you to interact with other customers to share ideas and provide solutions. You can download patches and patch documentation.
Using EM Support Workbench 983 See Metalink note 166650.1, “Global Customer Support Working Effectively with Support,” for more information on using the Oracle Support Services. Packaging Diagnostic Data You can invoke packaging in two ways. On the Support Workbench page, you can select the problem and click the Package button. This gives you the option to create a quick pack- age or a custom package. A quick package gathers information for a single problem with all the default options. With a custom package, you have the option to edit the package contents, remove any sensitive data, and add more traces and test cases. Another option to invoke packages is from the Problem Details screen, where you can invoke quick packaging for the problem. You can also optionally upload the packaged information to Oracle under the service request number. Figure 17.15 shows the Quick Packaging screen. F i g U r E 17.1 5 Quick Packaging screen On this screen, you have the option to upload the created package to Oracle Support. If you want Support Workbench to upload the information, provide your Metalink username and password along with the CSI number. If you have not created an SR yet, you have the option to create a new SR from this screen. Enter the SR number for uploading diagnostic information for an existing SR.
984 Chapter 17 N Moving Data and Using EM Tools If you choose custom packaging, the screen will look like Figure 17.16. Here Oracle will show you all the incidents to report and the files that are being packaged. You can exclude the files you do not want to send. Also, you have the option to add more incidents to the same package. F i g U r E 17.1 6 Customize Package screen In the Packaging Tasks section, you have the option to add more problems or exclude problems. You can also edit the files you sent to Oracle. Once you have added all the neces- sary files, click the Finish Contents Preparation link. You will see a confirmation screen with all the files you choose to include in the packaging and an option to generate the upload file. After the file is generated, the Send to Oracle button will be enabled, and you can use it to send the information to Oracle. Configuring Incident Packaging You can customize the package-retention and -configuration rules by using the Incident Packaging Configuration link in the Related Links section of the Support Workbench screen. Figure 17.17 shows the default retention and packaging settings.
Using EM Support Workbench 985 F i g U r E 17.17 Incident packaging configuration Click the Edit button to change the defaults. You can change the following values: Incident Metadata Retention Period Information such as the incident ID, time, and prob- lem are known as the metadata. The default is to keep this information for 365 days. Incident Files Retention Period Specify how long you want to keep the files (data) related to an incident; the default is 30 days. Cutoff Age for Incident Inclusion Include the incidents that are not older than the value specified here in days. Leading Incidents Count and Trailing Incidents Count If a problem has several incidents, the packaging by default includes only three incidents from the time it started occurring and three incidents from the latest occurrence. Correlation Time Proximity Specify the interval in minutes that should be treated as “happened at the same time” for related incidents. The default is 90 minutes. Time Window for Package Content This is the time in minutes to include incidents in the package; the default is 24 minutes. Tracking and Closing the Incident You can track the progress of the incident by adding comments to the activity log. The activ- ity log is available on the Problem Details screen as well as on the packaging screens. The Activity Log tab shows the system-generated operations that have occurred on the problem
986 Chapter 17 N Moving Data and Using EM Tools so far. This tab enables you to add your own comments while investigating the problem. Figure 17.18 shows the Activity Log tab. F i g U r E 17.1 8 Problem activity log You can add an entry using the Add Comment button. If the problem is related to an Oracle bug, you can add the bug number on the Problem Details screen by clicking the Edit button (shown earlier in Figure 17.13). As you saw on the Self Service tab of the Problem Details screen, the Oracle advisors that can help you repair critical errors are the SQL Repair Advisor and the Data Recovery Advisor. When you have a resolution for the issue, you can close the incident by clicking the Close button on the Support Workbench screen or by clicking the Close the Problem link on the Problem Details screen. By default closed incidents are not shown on the Problem Details screen. All incidents (open and closed) are purged after 30 days. You can disable the incident purging on the Incident Details page. You can get to the Inci- dent Details page by clicking the incident ID on the Problem Details screen (shown earlier in Figure 17.13). Click the Disable Purging button to disable the purging of the incident. In the next section, you will learn how Enterprise Manager can help DBAs manage patches. Using EM to Manage Patches As a DBA, you have several reasons to download and apply patches. In Oracle 11g, Oracle has made the patch research and application easier by integrating OSS with Enterprise Manager. Oracle Corporation releases the following types of patches for its database product: Patch release A patch release is a major patch that changes the version number of the database. Oracle release numbers have the format 11.1.0.6.0, where 11 is the major release
Using EM to Manage Patches 987 number, 1 is the maintenance release number, 0 is the application-server release number, 6 is the component-specific release number, and 0 is the platform-specific release number. When a patch release is applied, the component-specific release number will change. Patch releases go through rigorous regression testing. Patch releases are cumulative, which means the latest patch release will include most interim patches and critical patch updates, as well as lower patch releases. Interim patches Interim patches are one-off patches to fix a specific issue on a platform. The Oracle release numbers do not change when applying interim patches. Interim patches do not go through regression testing. Critical patch updates Critical patch updates (CPUs) include security patches and other patches that depend on the security patches. CPUs are cumulative, which means previ- ous CPUs are included. CPUs go through regression testing, and they do not advance the release numbers. Patch releases are installed using the Oracle Universal Installer (OUI). You can install interim patches and CPUs using the OPatch utility. You can use opatch lsinventory to review all the patches applied to an Oracle Home installation: $ $ORACLE_HOME/OPatch/opatch lsinventory Invoking OPatch 11.1.0.6.0 Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.1.0/db_1 Central Inventory : /u01/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.1.0.6.0 OUI version : 11.1.0.6.0 OUI location : /u01/app/oracle/product/11.1.0/db_1/oui Log file location : /u01/app/oracle/product/11.1.0/db_1/  cfgtoollogs/opatch/opatch2008-11-16_23-08-22PM.log Lsinventory Output file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/  lsinv/lsinventory2008-11-16_23-08-22PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.1.0.6.0 There are 1 products installed in this Oracle Home.
988 Chapter 17 N Moving Data and Using EM Tools Interim patches (1) : Patch 6529615 : applied on Sun Nov 16 23:08:05 CST 2008 Created on 7 Nov 2008, 04:01:26 hrs US/Pacific Bugs fixed: 6529615 -------------------------------------------------------------------------------- OPatch succeeded. $ To apply a patch using OPatch, first read the README.txt file accompanying each patch for instructions. Most of the patches are installed by using the opatch apply statement, from the patch staging directory. Enterprise Manager can be used to review, download, and apply patches. The patch-management links are in the Database Software Patching section on the Software and Support tab of Database Control, as shown in Figure 17.19. F i g U r E 17.1 9 Database Software Patching links on EM I will discuss each link in the following sections. Using the Patch Advisor The Patch Advisor shows the critical patch updates and the recommended patches for the release of the database on your server. Before you can use the Patch Advisor, you must set up Metalink login credentials and perform Metalink integration with EM. You can do this in two steps: 1. Enter the Metalink username and password. Click the Setup link on the top-right corner of EM Database Control. Click Patching Setup on the left menu, as shown in Figure 17.20. 2. Run the Refresh from Metalink job. Under the Related Links section on the Database Control home page, click the Jobs link to invoke the Job Activity screen. Under the Create drop-down list, choose Refresh from Metalink, and click Go, as shown in Figure 17.21. Click the Patch Advisor link on the screen shown in Figure 17.19. The Patch Advi- sor screen shows critical security patches that need to be applied to ORACLE_HOME and
Using EM to Manage Patches 989 recommended patches, as shown in Figure 17.22. Oracle can show the recommended patches based on the features used in the database. Select All from the drop-down box to show all the recommended patches. F i g U r E 17. 2 0 Software patching setup in EM F i g U r E 17. 2 1 Refresh from Metalink job setup in EM
990 Chapter 17 N Moving Data and Using EM Tools F i g U r E 17. 2 2 Patch Advisor screen on EM You can also invoke the patching setup screen by clicking the Patching Setup link in the Related Links section. Viewing the Patch Cache The patch cache is the location on the server where all your patches are downloaded and kept. One advantage of having the patch cache is that you can apply the patch to multiple Oracle Homes from one download. Figure 17.23 shows the Patch Cache screen. F i g U r E 17. 2 3 Patch Cache screen in EM You can click the View ReadMe button to read the patch application details, and you can click the Patch button to apply the patch.
Using EM to Manage Patches 991 Finding the Patch Prerequisites Click the Patch Prerequisite Check link to get the screen to evaluate the standard prerequisite checks on Oracle Home and Server with deployment-specific checks. Figure 17.24 shows the Patch prerequisite checker screen. F i g U r E 17. 2 4 Patch Prerequisite Checker screen in EM Staging a Patch You can download patches from Metalink and stage them for later application. Figure 17.25 shows the staging patch screen. You can select the patch to download by specifying the patch number or by specifying the product and release-number criteria. When the patches are displayed, choose the patch you want to stage. Click Next to prepare for staging or applying the patch. On the next screen, specify the targets or destination of the patch. You can choose the Oracle Instance name or the Oracle home location. On the Set Credentials screen, specify the host operating-system username and password. On the Stage or Apply screen, you can choose to download the patch or to apply the patch after downloading. On the Schedule screen, specify whether you want the patch to be downloaded immediately or at a later time. Figure 17.26 shows the Summary screen. Review the patch sizes, where the patches will be applied, and so on, and click the Finish button to download the patch.
992 Chapter 17 N Moving Data and Using EM Tools F i g U r E 17. 2 5 Staging patch screen in EM F i g U r E 17. 2 6 Patch: Summary screen in EM Staged patches are stored under the $ORACLE_HOME/EMStagedPatches directory in this example.
Using EM to Manage Patches 993 Applying a Patch When you click the Apply Patch link on the Software and Support page, you invoke the Patch Wizard. Figure 17.27 shows the first screen, where the wizard prompts you to select the patches. F i g U r E 17. 2 7 Apply Patch Wizard in EM Click the Add Patches button to select the patches to apply. The Add Patches button brings you to the page shown in Figure 17.28, where you can search for patches and select the patch. The Target List step is skipped for non-RAC instances; the Library Step Properties step is also skipped most of the time, unless you have customized the deployment procedures. The Credentials and Schedule screen prompts you to enter the Oracle software-owner user- name and password. For the schedule, you can specify one time (immediately), one time (later), or repeating.
994 Chapter 17 N Moving Data and Using EM Tools F i g U r E 17. 2 8 Searching for and selecting patches in EM Figure 17.29 shows the review screen. The patch will be downloaded and applied when you click the Finish button. F i g U r E 17. 2 9 Apply patch Summary screen in EM As you can see from the previous screens and options, EM Database Control helps DBAs be proactive about the critical and recommended patches they need.
Exam Essentials 995 Summary In this chapter, I discussed how to move data using Oracle Data Pump, using SQL*Loader, and using external tables. You also learned how to use Enterprise Manger to diagnose and contact Oracle Support as well as manage patches. Data Pump is a very high-speed infrastructure for data and metadata movement. The client utilities expdp and impdp are used to unload and load data and metadata. The Data Pump architecture includes the data and metadata movement engine DBMS_DATAPUMP, the Direct Path API that supports a stream interface, the metadata API DBMS_METADATA, the external tables API, and the client utilities. Data Pump export and import are performed on the server. You can attach to a job from any computer and monitor its progress or make resource adjustments. In the interactive mode, you can add a file to export a dump-file set, kill a job, stop a job, change the paral- lelism, and enable detailed status logging. SQL*Loader is used to load ASCII files to the Oracle database. You can invoke Data Pump and SQL*Loader using EM Database Control. You can also use external tables to move data. You can use the ORACLE_DATAPUMP access driver to write data into an external table, and you can use the ORACLE_LOADER access driver to read flat files into Oracle Database. EM Database Control infrastructure enhancements include Support Workbench and patch management. Using Support Workbench, you can identify, investigate, diagnose, and resolve incidents. The Patch Advisor provides the patches that are needed on the database and can be used to stage and apply patches. Exam Essentials Know how to create database directory objects. Directory objects are required for use in the Data Pump export and Data Pump import programs. Know that directory objects are not owned by individual schema. Directory objects are not schema objects. Instead, they are owned by the database like roles or profiles. Be aware of the Data Pump export and import modes. Data Pump export has database, schema, table, and tablespace modes, and Data Pump import has full, schema, table, and tablespace modes. Although these modes sound similar, they differ between the two tools. Be familiar with the Data Pump options that let you transfer both data and metadata from one schema to another. The content= parameter controls whether data, metadata, or both are copied. The remap_schema parameter allows you to transfer data from one schema to another.
996 Chapter 17 N Moving Data and Using EM Tools Be aware of the limitations of SQL*Loader direct-path mode, including unusable indexes. SQL*Loader direct-path mode has several limitations, the most prominent being that it locks the table in exclusive mode for the duration of the load. Unique indexes are marked unusable if unique violations are found after a direct path load. These unique violations must be resolved before the index can be rebuilt. Know the external table access drivers. ORACLE_DATAPUMP and ORACLE_LOADER are the access drivers used with external tables. The ORACLE_DATAPUMP access driver can be used to read and write to an external table. The ORACLE_LOADER access driver is read-only. Understand Support Workbench’s capabilities. Support Workbench can identify, diagnose, and package an incident to contact Oracle Support Services for help. Be familiar with the Patch Advisor and patch staging screens. EM Database Control makes patch management easy. You can get information about the patches relevant to the database, you can stage patches, and you can apply patches.
Review Questions 997 Review Questions 1. Which two PL/SQL packages are used by Oracle Data Pump? A. UTL_DATAPUMP B. DBMS_METADATA C. DBMS_DATAPUMP D. UTL_FILE E. DBMS_SQL 2. These options list the benefits of Oracle Data Pump; pick two that are not true. A. Data Pump supports fine-grained object selection using the EXCLUDE, INCLUDE, and CONTENT options. B. Data Pump has the ability to specify the target version of the database so that the objects exported are compatible. This is useful in moving data from Oracle 10g to Oracle9i. C. Data Pump has the ability to specify the maximum number of threads to unload data. D. The DBA can choose to perform the export using direct path or external tables. E. The Data Pump job can be monitored from another computer on the network. 3. The Data Pump job maintains a master control table with information about Data Pump. Choose the right statement. A. The master table is the heart of Data Pump operation and is maintained in the SYS schema. B. The master table contains one row for the operation that keeps track of the object being worked so that the job can be restarted in the event of failure. C. During the export, the master table is written to the dump file set at the beginning of export operation. D. The Data Pump job runs in the schema of the job creator with that user’s rights and privileges. E. All of the above. 4. When using the expdp and impdp clients, the parameters LOGFILE, DUMPFILE, and SQLFILE need a directory object where the files will be written to or read from. Choose the non- supported method for non-privileged users. A. Specify the DIRECTORY parameter. B. Specify the filename parameters with directory:file_name. C. Use the initialization parameter DATA_PUMP_DIR. D. None of the above (all are supported).
998 Chapter 17 N Moving Data and Using EM Tools 5. Which command-line parameter of expdp and impdp clients connects you to an existing job? A. CONNECT_CLIENT B. CONTINUE_CLIENT C. APPEND D. ATTACH 6. Which option unloads the data and metadata of the SCOTT user, except the tables that begin with TEMP? The dump file also should have the DDL to create the user. A. CONTENT=BOTH TABLES=(not like ‘TEMP%’) SCHEMAS=SCOTT B. SCHEMAS=SCOTT EXCLUDE=TABLE:”LIKE ‘TEMP%’” C. INCLUDE=METADATA EXCLUDE=TABLES:”NOT LIKE ‘TEMP%’” SCHEMAS=SCOTT D. TABLES=”NOT LIKE ‘TEMP%’” SCHEMAS=SCOTT 7. Which parameter is not a valid one for using the impdp client? A. REMAP_INDEX B. REMAP_TABLE C. REMAP_SCHEMA D. REMAP_TABLESPACE E. REMAP_DATAFILE 8. When do you use the FLASHBACK_TIME parameter in the impdp utility? A. To load data from the dump file that was modified after a certain time. B. To discard data from the dump file that was modified after a certain time. C. When the NETWORK_LINK parameter is used. D. FLASHBACK_TIME is valid only with expdp, not with impdp. 9. To perform a Data Pump import from a live database, which parameter needs to be set? A. db_link B. network_link C. dumpfile D. directory 10. Choose two statements about EM Support Workbench that are true. A. It can identify problems, contact Oracle Support, and resolve problems automatically. B. It helps collect diagnostic data and package it to send to Oracle Support. C. Multiple incidents of similar nature are combined as a problem. D. It is primarily used to track service requests created with Oracle Support.
Review Questions 999 11. Which types of patches do not undergo rigorous testing? A. Interim patches B. Critical patch updates C. Patch releases D. None of the above 12. When is it most appropriate to use external table? A. When you need to read binary files (PDF and photos) into Oracle Database B. To query a large file without loading the data into the database C. When the expdp and impdp utilities are not licensed for use D. To load a large file into the database quickly 13. Which constraint is not enforced during the direct path load using SQL*Loader? A. Primary key. B. Unique key. C. Not null. D. Check. E. All the constraints are enforced. F. No constraints are enforced. 14. Which utility can be used to identify the patches applied to your Oracle Database home location? A. ADRCI B. OPatch C. Oracle Universal Installer (OUI) D. All of the above 15. Choose the correct statement about Oracle Support Services. A. Support can be contacted using the metalink.oracle.com web page. B. Anyone can register and search Oracle Support’s Knowledge Base. C. There is no published phone number to contact OSS. D. Support analysts are available only during U.S. Pacific time zone work hours. 16. When using EM Database Control to load data into Oracle Database from a flat file, you should do which of the following? A. Cut and paste the file content into the data text box. B. Always build your own control file and specify it for the data load. C. Keep the log file, bad file, and data file in the same directory. D. Load the data file from the server or on your client machine. E. Load the data from the client machine.
1000 Chapter 17 N Moving Data and Using EM Tools 17. Choose the statement that is not true from the following about direct path load. A. Direct path load cannot occur if active transactions against the table are being loaded. B. Triggers do not fire during direct path loads. C. During direct path loads, foreign key constraints are disabled at the beginning of the load and then reenabled after the load. D. Only primary key, unique, and NOT NULL constraints are enforced. E. Direct path load allows other users to perform DML operations on the table while the direct load operation is in progress. 18. Which two advisors can help you repair critical errors? A. SQL Tuning Advisor B. SQL Repair Advisor C. SQL Syntax Advisor D. Data Recovery Advisor 19. When using EM Support Workbench, how is a problem closed? A. When the error is no longer appearing B. When Oracle Support Services closes the SR in Metalink C. When the DBA manually closes the incident D. All of the above 20. To register for Oracle Support Services Metalink access, you must do which of the following? (Choose all that apply.) A. Have a valid driver’s license B. Be an Oracle customer with a valid CSI number C. Get approval from the CSI administrator D. Be a member of the IOUG or OAUG user group