Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds. Expert instruction and hands-on demonstrations will provide you with real world experience. By engaging in hands-on exercises to reinforce your learning, you'll develop in demand skills to effectively manage an Oracle Database. We are gathering the data for this course from the other countries… this will take a little time… please be patient.
This course will help you evaluate your own recovery requirements and develop appropriate strategies for these procedures. Learn how to manage an Oracle multitenant container database and any associated pluggable databases in an effective and efficient manner.
Public Classroom Price. Note: Remember that this consumes space on the disk and that you must regularly back up older archive logs to some other storage. Configure redundant archive log destinations. Practices Overview In these practices, you will create backups of your database so that you can recover from various types of failures.
Practice Backing Up the Control File. Overview In this practice, you back up your control file to a trace file, creating a file of SQL commands that can be used to re-create the control file. Assumptions Practices for lesson 13 have been successfully completed.
Navigate to the orcl database home page. Log in to the orcl database with your named credential. On the Control Files page, click Backup to Trace. An update message is displayed. Note the location of the trace file. You can also perform a control file back up to trace by executing a SQL command. Navigate to the directory you noted in step 6 and view the end of the alert log to verify the creation of the trace files and the names of the files.
Overview In this practice you configure automatic backups of the control file and server parameter file SPFILE when a backup of the database is made and when there is a structural change to the database. Return to the orcl database home page in Enterprise Manager Cloud Control. On the Backup Settings page, click the Policy tab. Scroll to the bottom of the page.
In the Host Credentials section select New. Enter oracle as the username and password. Click Test to ensure that you entered the values properly. Practice Creating a Whole Database Backup. Overview In this practice, you back up your entire database, including the archived redo log files. The backup should be the base for an incremental backup strategy. In the Customized Backup section, select Whole Database.
Confirm that the host credentials are set with oracle as the user name and password. Click Schedule Customized Backup. In the Backup Mode section, select Online Backup. On the Settings page, select Disk as the backup destination. Click Next. On the Schedule page, accept the defaults. A confirmation message is displayed. Click View Job. Click the refresh icon on the right side of page until you see that the job has completed successfully.
Practices Overview In these practices, you will use the Data Recovery Advisor to recover a lost data file. Practice Recovering from the Loss of a Data File. Java created. Procedure created.
This script simulates a failure in the database environment by deleting a data file. Wait a couple minutes before proceeding. Use Enterprise Manager Cloud Control to troubleshoot the error and recover the data file.
Log in with your saved database credentials. Expand Oracle Database. Note that there is a critical error listed indicating a data failure. Expand Availability. Specify a named host credential or enter oracle as the user name and password. View the failure information. Click Advise. Review the RMAN script that will be used to restore the file and perform recovery. A processing page appears and then the Job Activity page appears.
Click the job name link. Once again query the HR. Return to Enterprise Manager Cloud Control. Select the incident and click Clear. Click OK to confirm.
Return to the orcl database home page. Practices Overview Background: In the recent past, you received a number of questions about the HR schema. To analyze them without interfering in daily activities, you decide to use Data Pump export to export the HR schema to a file. When you perform the export, you are not sure into which database you will be importing this schema.
In the end, you learn that the only database for which management approves an import is the orcl database. So you perform the import with Data Pump import, remapping the HR schema. You then export the HR schema so that you can then import the tables that you want into the DBA1 schema. Be sure you know the OS directory where the Data Pump import file will be placed. Use the Data Pump export utility to export the HR schema.
Specify the DBA1 user to execute the export operation. Export: Release Enter the following entire command string. Import: Release These errors are expected. Data and control files are provided. Duplicate rows will be loaded and the Primary Key Index will become unusable. Ensure that you are accessing the orcl database. Ensure that the OE user is unlocked. User altered. Load completed - logical record count 8.
Allow all discards. End of generated control file for possible reuse. Bind array size not used in direct path. Column array rows : Stream buffer bytes: Read buffer bytes: Elapsed time was: Select the rows inserted in the OE.
Ensure that your environment is configured for the orcl database by running oraenv. Path used: Conventional Commit point reached - logical record count Table OE.
Read the log file. Record 1: Rejected - Error on table OE. ORA integrity constraint OE. Record 2: Rejected - Error on table OE. Record Rejected - Error on table OE. Space allocated for bind array: bytes 64 rows Read buffer bytes: Total logical records skipped: 0 Total logical records read: 64 Total logical records rejected: 51 Total logical records discarded: 0.
The load could not be successfully completed due to constraint violations. Path used: Direct. Load completed - logical record count You can see that constraints were automatically disabled. Constraint OE. The following index es on table OE. View the rows inserted into the table. Practices Overview Background: You want to proactively monitor your orcl database so that common problems can be fixed before they affect users.
Users, developers, and unanticipated changes in the way applications are used can bring serious performance problems. As DBA you are seldom informed about what changed, you are instead told there is a generic problem. At that point you must find the problem based often on misleading information from users. In this scenario, a developer is providing scripts for you to run to provision changes to an.
These supplied scripts create a problem so that you can familiarize yourself with the tools that are available. Examine each script to satisfy yourself that the script is doing what you have been told. These practices have been scripted because delays in performing the tasks can have a large effect on the results you see due to the short time that the workload runs.
Practice Database Maintenance 1. A new tablespace is being added to hold the new tables. Examine the script and then execute it. Note: Because this is the first time you have run this script, the error shown when the tablespace is dropped is expected.
The test workload that is provided runs only a few minutes. It also creates a snapshot in AWR. In a terminal window, enter:. Run the workload script. This creates an activity to be analyzed. DO NOT wait for the script to finish continue to the next step. In a terminal window, enter the following.
Summary Navigate to the orcl database home page. This is your activity to be analyzed. By looking at the graph, you can determine that this instance is suffering from concurrency problems. This script forces the creation of a new snapshot and gathers statistics on your SPCT table. Note: Causing the same performance problem in all environments is not easy. To help make your test more successful, wait an extra minute or so after the spike has completely finished before running the script.
Automatic Database Diagnostic Notice the findings. Review the Detail findings. Look at the Performance Analysis findings in order of their impact. There are several access paths to this information. The results should look similar to the following:. Looking at the Performance Analysis section, you see that the first finding has a high percentage in this example, So your first step is to look at this finding in more detail.
Click the link in the Finding column. Look at the Buffer Busy findings in particular by clicking the link in the Finding column.
SPCT table. The findings may appear in a different order than shown. If you do not see results similar to the ones outlined in the preceding screenshot, you may need to restart this practice. Ask your instructor for assistance if this is the case.
Take care not to increase the load too much or you will slow your system down too much. You decide to implement the recommendation to use Automated Segment Space Management. To do this, you must re-create the object. Tablespaces Click Create. Add Datafile Enter File Name: tbsspc Create Tablespace Click the Storage tab. Execute your workload again.
DO NOT wait for the script to complete continue to next task. On the orcl Performance Home page,. View performance data in real time with a seconds refresh cycle. After a while, you should see a spike on the Average Active Sessions graph.
Hint: This is that same procedure you used in Task 5. This script forces the creation of a new snapshot and gathers statistics on the table in the workload test. Select Performance Home in the Performance menu. Watch for the spike in the Active Sessions chart to complete. You see that the impact value for the Buffer Busy finding indicating read-and-write.
Note: You may see additional Buffer Busy findings at a lower impact percentage and other further recommendations that could improve performance, but you are not going to pursue them at this time. Practices Overview Background: Users are complaining about slower-than-normal performance for operations involving the human resources and order-entry applications. When you question other members of the DBA staff, you find that maintenance was recently performed on some of the tables belonging to the HR schema.
You need to troubleshoot and make changes as appropriate to resolve the performance problems. Other directories are individually named. Practice Managing Performance 1. This -- helps fix any migrated rows, as well as compacting unused space -- in the segment that may have been caused by deleting data.
Table altered. You get calls from HR application users saying that a particular query is taking longer than normal to execute. Using Cloud Control, locate the HR session in which the above statement was just executed, and view the execution plan for that statement. Cloud Control Navigate to the orcl database target. Enter HR. Select the Tabular radio button. Enter HR in the Schema field.
View Index: HR. This is a problem you need to fix. Reorganize Objects: Objects Click Add. Objects: Add Select Indexes in the Type menu.
Click Search. Reorganize Objects: Objects Check that the six unusable indexes are listed. Reorganize Objects: Options Accept the default options. Processing: Generating Script Displays briefly. When return is Test Successful, click Next.
Job Activity A Confirmation message appears. Then repeat the steps to see the plan of the last SQL statement executed by this session. Repeat the tasks listed in step 3 to view the execution plan for the query. Now the icon indicates the use of an index.
Click View Tabular. Note that the plan now uses an index unique scan. What is the difference in execution plans, and why?
Answer: The statement execution uses a unique index scan instead of a full table scan, because the index is usable after you reorganized the indexes. Please note the SID value that is reported. This script takes about 20 minutes to complete. So, run it in a separate terminal window and continue with this practice exercise while it runs.
SID is Load is now being generated. Go back to Cloud Control and examine the performance of your database. Note: You may need to wait a minute or two to see the effects of the load generation script. Wait to see some levels of activity in the Average Active Sessions graph before proceeding. Question 1: In the Average Active Sessions graph, which are the two main categories that active sessions are waiting for? CPU is also showing high wait activity. Your results may differ from what is shown here.
Question 2: In the Configuration category of waits, what is one of the contributors to the wait time? Database Instance: orcl Click Configuration in the legend. Answer: Any one of the waits listed in the screen shot below, but free buffer waits and log file switch completion seem to be the highest contributors:.
Active Sessions Waiting: Click the browser back button. Configuration b. Database Instance: orcl Click Settings. Performance Home c. Question 3: Which process is doing the most writing to the disk? Note: The graph you see may vary. You may need to scroll to the bottom to see the horizontal scroll bar.
SQL Details: 0qqwcxx1quwuv. Kill the session that is generating the load. Use the session ID recorded in step 9. This is found under the heading Detail for Selected 5 Minute Interval. Confirmation Click Yes d. This warning means you are attempting to refresh information about a session that has already been killed. You can ignore this warning. Top Activity View the Top Activity graph.
Note that the session activity in the database has declined considerably. Practices for Lesson Managing Performance Log out of Enterprise Manager Cloud Control. Practice Using Automatic Memory Management.
Overview In this practice you review memory management capabilities. File created. Then press Enter to continue. Tablespace created. Press [Enter] to continue Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd?
Uploaded by AlexMV. Did you find this document useful? Is this content inappropriate? Report this Document. Flag for inappropriate content. For Later. Related titles. Carousel Previous Carousel Next. Jump to Page. Search inside document. Page 5. Ashok Sodha. Nelson Castro. Sumit Pawar. Tarun Chaudhary. Pandiarajan Dharmalingam. Suddhasatwa Bhaumik. Bhaveen Joshi. Student Guide — Volume Murali Palepu. Srinivas Ellendula. Gayan Saparamadu. Gro Ver Rabanal Atalaya. More From bugzbinny. Popular in Oracle Database.
Compitech gupta.
0コメント