Introduction and simple using of Oracle

1. Introduction and simple using of Oracle Logistics Information System Yaxian Yao yyx@pusan.ac.kr

2. Outline • Introduction of Oracle 11g • How to install Oracle 11g • The steps for installing • Create a new Database • The difference between 11g and former ones • The basic use of Oracle 11g • The advanced functions of Oracle 11g

3. Introduction • Oracle 11g is a new software of Database released by Oracle at 2007. 7. 12 • Oracle 11g has more than 400 functions • The testing hours reach 15 million for it • Oracle 11g has lots of new functions than former versions

4. How to install Oracle 11g • Download Oracle 11g from internet • Choose the basic way to install Oracle 11g

5. How to install Oracle 11g • Test your computer capacity before installing

6. How to install Oracle 11g • Oracle Configuration Manager register

7. How to install Oracle 11g • Summary looks for your choice

8. How to install Oracle 11g • The process for installing

9. How to install Oracle 11g • The process for installing

10. How to install Oracle 11g • The process for installing

11. How to install Oracle 11g • Finish creating Database, the information we can find in a certain box

12. How to install Oracle 11g • Make passwords

13. How to install Oracle 11g • Finish installing

14. Enter the Database by web page

15. Create a new Database • We use Database Configuration Assistant to create a new database

16. Create a new Database • The first step - choose the option you want

17. Create a new Database • The second step – choose the database module

18. Create a new Database • The third step – name your Database

19. Create a new Database • Step four – deploy enterprise manager

20. Create a new Database • Step five - set the password

21. Create a new Database • Step six - Set the storage function

22. Create a new database • Step seven – The place where Database locates

23. Create a new Database • Step eight – recover deployment

24. Create a new Database • Step nine – content of Database

25. Create a new Database • Step ten – initial the characters

26. Create a new Database • Step eleven – security deployment

27. Create a new Database • Step twelve – open the auto-maintain

28. Create a new Database • Step thirteen – Database storage

29. Create a new Database • Step fourteen – create Database

30. The difference between 11g and former ones • Before Oracle 10g, we must install a client-side on the customer’s computer • For Oracle 10g and 11g, we can operate the database on the web page. So we don’t need to install a client-side on each computer • The defect in my opinion is that it’s not convenient for us to use the web page, so I advice we use the software “Toad for Oracle”

31. The basic use of Oracle 11g • Like access, we can use it to create table, insert data modify data and save it

32. Create table

33. Insert data

34. Select data

35. The advanced functions of Oracle 11g • 1. Database Replay • You can use Database Replay to capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to test the effects of a system change without affecting the production system. • Step 1. On the production system, capture the workload into capture files • Step 2. Copy the capture files to the test system and preprocess them • Step 3. On the test system, replay the preprocessed files • Step 4. Using the reports generated by Database Replay, perform detailed analysis of both the workload capture and workload replay

36. The advanced functions of Oracle 11g Database Replay Workflow

37. The advanced functions of Oracle 11g • 2. SQL Plan Management • SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. • The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

38. The advanced functions of Oracle 11g • 3. Automatic Diagnostic Repository (ADR) • The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more. • It has a unified directory structure across multiple instances and multiple products.

39. The advanced functions of Oracle 11g ADR Directory Structure for an Oracle Database Instance

40. The advanced functions of Oracle 11g • 4. Incident Packaging Service • A DBA can automatically and easily gather all diagnostic data (traces, health check reports, SQL test cases, and more) pertaining to a critical error and package the data into a zip file suitable for transmission to Oracle Support.

41. The advanced functions of Oracle 11g • 5. Automatic SQL Tuning Advisor • Oracle Database automatically runs the SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. • Once automatic SQL tuning begins, the following steps are performed: • 1. Identify SQL candidates in the AWR for tuning. • 2. Tune each SQL statement individually by calling the SQL Tuning Advisor. • 3. Test SQL profiles by executing the SQL statement. • 4. Optionally implement the SQL profiles provided they meet the criteria of threefold performance improvement.

42. The advanced functions of Oracle 11g • 6. Automatic Shared Memory Management • Automatic Shared Memory Management simplifies the configuration of the SGA(System Global Area ). • In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools: • Database buffer cache (default pool) • Shared pool • Large pool • Java pool • Streams pool

43. The advanced functions of Oracle 11g • 7. Database Resource Manager • The Database Resource Manager solves many resource allocation problems that an operating system does not manage so well: • Excessive overhead • Inefficient scheduling • Inappropriate allocation of resources • Inability to man • age database-specific resources • With the Database Resource Manager, you can do the following: • Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users • Distribute available processing resources by allocating percentages of CPU time or I/O requests per second to different users and applications

44. The advanced functions of Oracle 11g • 8. Securefile • LOB storage parameter that allows deduplication, encryption, and compression • 9. Automatic Database Diagnostic Monitor (ADDM) • This lets Oracle Database diagnose its own performance and determine how identified problems could be resolved. It runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.

45. The advanced functions of Oracle 11g • 10. Automatic Workload Repository (AWR) • A built-in repository in every Oracle Database. At regular intervals, the Oracle Database makes a snapshot of all its vital statistics and workload information and stores them here.

46. The advanced functions of Oracle 11g If we want to know more, we can read the Oracle Database document library.