ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g Review

Oracle 10g Review

原创 Linux操作系统 作者:licheng79 时间:2012-02-23 14:36:38 0 删除 编辑

 Oracle 10g Review  

 

1.0       GENERAL INFORMATION

 

1.1    Purpose

Oracle Database 10g is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage enterprise information. It cuts costs of management while providing the highest possible quality of service. In addition to numerous quality and performance enhancements, Oracle Database 10g significantly reduces the costs of managing the IT environment, with a simplified install, greatly reduced configuration and management requirements, and automatic performance diagnosis and SQL tuning. These and other automated management capabilities help improve DBA and developer productivity and efficiency. This report is to evaluate the new feature, flexibility, enhancements on feature wide and performance, deployment of Oracle Database 10g

 

1.2    Scope

New feature enhancements in Oracle Database 10g over 70 items, Base on our resources and actual need, scope of the testing and evaluation report as:

 

a)    Installation of Oracle Database 10g software.

b)    Software, Database Patching.

c)    DataPump Export/Import Utilities.

d)    Automatic Management function review.

e)    Manageability Infrastructure.

f)     Flashback Any error.

g)    ASM implementation

h)  RMAN backup/restore

1.3    System Overview


The full testing cycle was conducted with CTM DBA team. System architecture is a two tiers DBA client and server structure.

 

 

Operating system platform. on server is windows 2003 server and database is Oralce10g enterprise version.  

 

In short, here is the summary of the system overview:

 

Responsible party: CTM IT Infrastructure DBA Team

System name: Oracle Database 10g

System category: Database Server

Operational status: Product evaluation

System environment and special conditions: Oracle Database 10g Enterprise Edition 32bits running on Windows 2003 32 bit.

 

1.4       Project References

 

Oracle Database 10g:

 

http://www.oracle.com/technology/products/database/oracle10g/index.html

 

1.5       Special terminology and Abbreviations

 

System Global Area (SGA)

The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

 

Enterprise Manager (EM)

From what I've heard & read, the 9.2 console won't work against a 10g database. You can use the db console for each individual server, usually http://servername:5500/em. If the server has more than one 10g instance on it, the port number increments by one for each instance - http://servername:5501/em, etc. You can see if it’s running by $10g_OH/bin/emctl status dbconsole

If you use 10g Grid Control instead of a 9.x OMS, you have to set up the new 10g agent on each server, even if it is 9i or 8i. You are supposed to be able to point the 9.2 console at the 10g Grid Control server, but thus far it hasn't worked for me. There is also a repo_mig tool that is supposed to migrate some of your old OMS settings to 10g GC, but this is problematic as well. And it doesn't seem to bring events over, which is the most tedious part.

 

Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well. (AWR) is a built-in repository in every Oracle Database. At regular intervals, the database makes a snapshot of all its vital statistics and workload information and stores them in AWR. By default, the snapshots are made every 30 minutes, but you can change this frequency. The snapshots are stored in the AWR for a period of time (seven days by default) after which they are automatically purged.

 

Automatic Storage Management (ASM) simplifies database administration. It eliminates the need for you, as a DBA, to directly manage potentially thousands of Oracle database files. It does this by enabling you to create disk groups, which are comprised of disks and the files that reside on them. You only need to manage a small number of disk groups.

In the SQL statements that you use for creating database structures such as tablespaces, redo log and archive log files, and control files, you specify file location in terms of disk groups. Automatic Storage Management then creates and manages the associated underlying files for you.

Automatic Storage Management extends the power of Oracle-managed files. With Oracle-managed files, files are created and managed automatically for you, but with Automatic Storage Management you get the additional benefits of features such as mirroring and striping.

Automatic Storage Management does not eliminate any existing database functionality. Existing databases are able to operate as they always have. Existing databases using file systems or with storage on raw devices can operate as they always have. New files can be created as ASM files while old ones are administered in the old way. Databases can have a mixture of ASM files, Oracle-managed files, and manually managed files all at the same time.

 

1.6       Points of Contact

1.6.1   Information

 

Larry Loi

Database Administrator

Companhia de TelecomunicaÇões de Macau S.A.R.L.

Tel: (853) 8913419/ Mobile: (853) 6880829

eMail: larry.ik.loi@ctm.com.mo

 

Thomas Vong

Database Administrator

Companhia de TelecomunicaÇões de Macau S.A.R.L.

Tel: (853) 8913417/ Mobile: (853) 6680205

eMail: mailto:thomas.kk.vong@ctm.com.mo

 

1.6.2   Coordination

 

1.6.3   Testing Procedures

 

After setting up the Windows 2003 server machine.

1.  Installation of Oracle 10g software enterprise edition 32bit

During the installation, found most of the steps same as previous version but there is only 1 CD for 10g Database server. And took around 20 minutes complete software installation. In previous version Oracle 9I 3 CDs for installation and at less 45 minutes for installation. After that, created 2 Database instances DB1 and DB2 there. The below service create in Windows Services control panel.

 

OracleCSService

OracleDBConsoleDB1

OracleDBConsoleDB2

OracleJobSchedulerDB1

OracleJobSchedulerDB2

OracleOraDB10g_home1Isql*Plus

OracleOraDB10g_home1SNMPPeerEncapsulator

OracleOraDB10g_home1SNMPPeerMasterAgent

OracleOraDB10g_home1TNSListener

OracleServiceDB1

OracleServiceDB2

 

Client PC access database via web browser by below URL.

http://hostname:5500/em

 


Passed login page the below database home will be appear.


 

With this home view, database general information, CPU utilization, active sessions information, HA, Space usage, diagnostic summary grouping into the first page.

 

 


 

 

Inside the EM console, it’s easy to check the availability and downtime time stamp, alert log message in alert log file


 

In the performance page tab, click on the band below the chart to select the historical 24 hour interval for which you want to view data in the graphs below. Use the active sessions data to help with your selection.

 

Sessions Waiting and Working, Instance Throughput curves will show.



 

 

Additional Monitoring Links includes Top Sessions, Top SQL, Blocking Session, Database Locks, Instance Activity, Top Consumers, Snapshots and Search Session. Etc.


 

 

 

Diagnostic Pack and Tuning Pack Enhancement.

 

Test Cases for Diagnostic Pack and Tuning Pack

Perform. a table scan on 900MB partition table on 2:53pm. SQL statement as below.


SQL> select distinct B_SUBSCRIBER from TRS_DATA_2000;

 

 

In Database Console web page Database DB1, go to performance page,

Session: Waiting and Working showed a heavy User I/O and CPU Used.

Go to Top SQL Page; In Spot SQL page, it shows all the SQL statements utilization within 1 hour select the time period that FTS running.

 


All the Top SQL usage shows in a table and pie chart; select the top one or click the biggest part of pie chart. SQL Detail will show as below


 

 

 

 

 

 

 

 

Another SQL run on 3:23pm

 

select B_SUBSCRIBER from (

select distinct B_SUBSCRIBER from TRS_DATA_2000

where B_SUBSCRIBER in (

'9899732',

'9899733',

'9899736',

'9899737',

'9899768',

'9899780',

'9899788',

'9899789',

'998711867443',

'998712763127'))

 

Performance page show the below


 

Go to Top SQL; Spot SQL page

 

 

 

 

 

 

 

 

 

 


 

Select the Top one SQL that occupy 74.14% activity.

 


SQL details will show as above, more information found in this page, like Statistic, Execution Plan.

 

Click Run SQL Tuning Advisor.

 

Tuning Pack will run SQL Tuning process.

 


After SQL Tuning Advisor process finished. A recommendation will generate.


 

 

 

 

 

 

 

 

FlashBack recovery.

 

In past releases, when you used the rollback segment method of managing undo space, you were said to be operating in the manual undo management mode. Now, you use the undo tablespace method, and you are said to be operating in the automatic undo management mode. You determine the mode at instance startup using the UNDO_MANAGEMENT initialization parameter. The default value for this parameter is MANUAL. You set it to AUTO to enable automatic undo management.

 

Notes:

 

You cannot use both methods in the same database instance, although for migration purposes it is possible, for example, to create undo tablespaces in a database that is using rollback segments, or to drop rollback segments in a database that is using undo tablespaces. However, you must shut down and restart your database in order to take effect the switch to another method of managing undo.

 

When operating in automatic undo management mode, any manual undo management SQL statements are ignored and no error message is issued. For example, ALTER ROLLBACK SEGMENT statements will be ignored.

 

The following initialization parameter setting causes the STARTUP command to start an instance in automatic undo management mode:

 

UNDO_MANAGEMENT = AUTO

 

If the database contains multiple undo tablespaces, you can optionally specify at startup that you want an Oracle Database instance to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter. For example:

 

UNDO_TABLESPACE = undotbs_01

 

Initialization Parameter Description

UNDO_MANAGEMENT If AUTO, use automatic undo management mode. If MANUAL, use manual undo management mode. The default is MANUAL.

UNDO_TABLESPACE An optional dynamic parameter specifying the name of an undo tablespace to use. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

UNDO_RETENTION A dynamic parameter specifying the minimum length of time to retain undo. The default is 900 seconds. The setting of this parameter should take into account any flashback requirements of the system.

 

Undo Retention

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. However, for consistent read purposes, long-running queries sometimes require old undo information for undoing changes and producing older images of data blocks. The success of several Flashback features can also depend upon older undo information.

 

Automatic Tuning of Undo Retention

Oracle Database 10g automatically tunes undo retention by collecting database use statistics and estimating undo capacity needs for the successful completion of the queries. You can set a low threshold value for the UNDO_RETENTION parameter so that the system retains the undo for at least the time specified in the parameter, provided that the current undo tablespace has enough space. Under space constraint conditions, the system may retain undo for a shorter duration than that specified by the low threshold value in order to allow DML operations to succeed.

 

In order to guarantee the success of queries even at the price of compromising the success of DML operations, you can enable retention guarantee. The RETENTION GUARANTEE clause of the CREATE UNDO TABLESPACE and CREATE DATABASE statements ensures that undo information is not overwritten. This option must be used with caution, because it can cause DML operations to fail if the undo tablespace is not big enough. However, with proper settings, long-running queries can complete without risk of receiving the "snapshot too old" message, and you can guarantee a time window in which the execution of Flashback features will succeed.

 

Test Cases for Flash Back recovery

 

C:\Documents and Settings\ctmdba>sqlplus trs/trs@db1

 

SQL*Plus: Release 10.1.0.3.0 - Production on Mon Dec 13 12:29:04 2004

 

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

 

SQL> CREATE TABLE employees_demo

  2    ENABLE ROW MOVEMENT tablespace users AS SELECT * FROM HR.EMPLOYEES@HR;

 

Table created.

 

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2400

      2200

      2100

      2400

      2200

 

SQL> UPDATE employees_demo

  2    SET salary = salary * 1.1

  3    WHERE salary < 2500;

 

5 rows updated.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2420

      2310

      2420

 

 

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2400

      2200

      2100

      2400

      2200

 

SQL> UPDATE employees_demo

  2    SET salary = salary * 1.1

  3    WHERE salary < 2500;

 

5 rows updated.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2420

      2310

      2420

 

SQL>

SQL>

SQL>

SQL> FLASHBACK TABLE employees_demo

  2    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

 

Flashback complete.

 

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2400

      2200

      2100

      2400

      2200

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TRS_DATA_2000                  TABLE

EMPLOYEES_DEMO                 TABLE

SYS_TEMP_FBT                   TABLE

 

SQL> drop table EMPLOYEES_DEMO;

 

Table dropped.

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TRS_DATA_2000                  TABLE

SYS_TEMP_FBT                   TABLE

BIN$hStpDS0jTEKEW0XKvg/Apw==$0 TABLE

 

SQL> SELECT object_name, droptime FROM user_recyclebin;

 

OBJECT_NAME                    DROPTIME

------------------------------ -------------------

BIN$hStpDS0jTEKEW0XKvg/Apw==$0 2004-12-13:12:33:56

 

SQL> FLASHBACK TABLE employees_DEMO TO BEFORE DROP;

 

Flashback complete.

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TRS_DATA_2000                  TABLE

EMPLOYEES_DEMO                 TABLE

SYS_TEMP_FBT                   TABLE

 

SQL>

SQL> SELECT salary

  2    FROM employees_demo

  3    WHERE salary < 2500;

 

    SALARY

----------

      2400

      2200

      2100

      2400

      2200

 

SQL>

 

 

 

 

Oracle Data Pump

 

Oracle Data Pump is a new feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a web-based Enterprise Manager interface. 

 

- Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.

 

- Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.

 

- Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.

 

- Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).

 

 

Test Cases for comparison of Data Pump and normal Export/Import

 

SQL> connect system@db1

Enter password:

Connected.

SQL>

 

SQL> CREATE OR REPLACE DIRECTORY my_dir as 'D:\expdp_out';

 

Directory created.

 

SQL> CREATE OR REPLACE DIRECTORY my_dir_log as 'D:\expdp_out\log';

 

Directory created.

 

SQL> GRANT read,write ON DIRECTORY my_dir TO trs

  2  ;

 

Grant succeeded.

 

SQL> GRANT read,write ON DIRECTORY my_dir_log TO trs;

 

Grant succeeded.

 

SQL> EXIT

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options

 

 

D:\expdp_out\log>expdp trs/trs dumpfile=my_dir:trs_data_200001.dp TABLES=TRS_DAT

A_2000:TRS_DATA_200001 LOGFILE=my_dir_log:imp_data_200001.log

 

Export: Release 10.1.0.3.0 - Production on Friday, 03 December, 2004 12:18

 

Copyright (c) 2003, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Starting "TRS"."SYS_EXPORT_TABLE_01":  trs/******** dumpfile=my_dir:trs_data_200

001.dp TABLES=TRS_DATA_2000:TRS_DATA_200001 LOGFILE=my_dir_log:imp_data_200001.l

og

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 952 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TRS"."TRS_DATA_2000":"TRS_DATA_200001"     860.4 MB 22140486 rows

Master table "TRS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TRS.SYS_EXPORT_TABLE_01 is:

  D:\EXPDP_OUT\TRS_DATA_200001.DP

Job "TRS"."SYS_EXPORT_TABLE_01" successfully completed at 12:20

 

 

D:\expdp_out\log>

 

 

D:\expdp_out>impdp trs/trs dumpfile=my_dir:trs_data_200001.dp TABLES=TRS_DATA_20

00:TRS_DATA_200001 LOGFILE=my_dir_log:imp_data_200001.log content=data_only

 

Import: Release 10.1.0.3.0 - Production on Friday, 03 December, 2004 14:25

 

Copyright (c) 2003, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Master table "TRS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "TRS"."SYS_IMPORT_TABLE_01":  trs/******** dumpfile=my_dir:trs_data_200

001.dp TABLES=TRS_DATA_2000:TRS_DATA_200001 LOGFILE=my_dir_log:imp_data_200001.l

og content=data_only

Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

. . imported "TRS"."TRS_DATA_2000":"TRS_DATA_200001"     860.4 MB 22140486 rows

Job "TRS"."SYS_IMPORT_TABLE_01" successfully completed at 14:28

 

 

------------------------------------------------------------------------------------------

Traditional Export Import;

 

Export: Release 10.1.0.3.0 - Production on Fri Dec 3 15:08:48 2004

 

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

 

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

server uses UTF8 character set (possible charset conversion)

Note: grants on tables/views/sequences/roles will not be exported

Note: indexes on tables will not be exported

Note: constraints on tables will not be exported

 

About to export specified tables via Conventional Path ...

. . exporting table                  TRS_DATA_2000

. . exporting partition                TRS_DATA_200001   22140486 rows exported

Export terminated successfully without warnings.

 

Finished on Dec 3 15:18:48 2004

 

 

 

 

Import: Release 10.1.0.3.0 - Production on Fri Dec 3 16:16:47 2004

 

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

 

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

 

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

import server uses UTF8 character set (possible charset conversion)

. . importing partition "TRS_DATA_2000":"TRS_DATA_200001"   22140486 rows import

ed

Import terminated successfully without warnings.

 

Finished on Dec 3 16:31:48 2004

 

 

 

Table size of TRS_DATA_2000 around 970MB

 

Normal export dump file size 1.3 GB

Export Pump file: 860MB

 

Export Monitoring

 

While Data Pump Export (DPE) is running, press Control-C; it will stop the display of the messages on the screen, but not the export process itself. Instead, it will display the DPE prompt as shown below. The process is now said to be in "interactive" mode:

 

 

Export>

 

 

This approach allows several commands to be entered on that DPE job. To find a summary, use the STATUS command at the prompt:

 

 

D:\expdp_out>expdp system/veritas dumpfile=my_dir:trs_data_200001A.dp TABLES=TRS

.TRS_DATA_2000:TRS_DATA_200001 LOGFILE=my_dir_log:exp_data_200001.log job_name=C

ASES_EXPORT

 

Export: Release 10.1.0.3.0 - Production on Tuesday, 07 December, 2004 16:35

 

Copyright (c) 2003, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."CASES_EXPORT":  system/******** dumpfile=my_dir:trs_data_2000

01A.dp TABLES=TRS.TRS_DATA_2000:TRS_DATA_200001 LOGFILE=my_dir_log:exp_data_2000

01.log job_name=CASES_EXPORT

Estimate in progress using BLOCKS method...

 

Export> status

 

Job: CASES_EXPORT

  Operation: EXPORT

  Mode: TABLE

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: D:\EXPDP_OUT\TRS_DATA_200001A.DP

    bytes written: 4,096

 

Worker 1 Status:

  State: EXECUTING

  Object Schema: TRS

  Object Name: TRS_DATA_2000

  Object Type: TABLE_EXPORT/TABLE/TABLE

  Completed Objects: 1

 

Export> CONTINUE_CLIENT

Total estimation using BLOCKS method: 952 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

 

Export> status

 

Job: CASES_EXPORT

  Operation: EXPORT

  Mode: TABLE

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: D:\EXPDP_OUT\TRS_DATA_200001A.DP

    bytes written: 143,360

 

Worker 1 Status:

  State: EXECUTING

  Object Schema: TRS

  Object Name: TRS_DATA_2000

  Object Type: TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 1

  Completed Rows: 7,732,302

 

Export> CONTINUE_CLIENT

. . exported "TRS"."TRS_DATA_2000":"TRS_DATA_200001"     860.4 MB 22140486 rows

Master table "SYSTEM"."CASES_EXPORT" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.CASES_EXPORT is:

  D:\EXPDP_OUT\TRS_DATA_200001A.DP

Job "SYSTEM"."CASES_EXPORT" successfully completed at 16:38

 

 

D:\expdp_out>

 

 

 

Management Pack Access

 

Oracle Enterprise Manager 10g Database Control provides central management for your entire Oracle environment. Some premium functionality contained within this release of Enterprise Manager 10g Database Control requires a separate Oracle license. Use the buttons below to enable or disable access to each Management Pack.  Grant Access Remove Access

 

Oracle Diagnostic Pack

The Oracle Diagnostic Pack provides automatic performance diagnostic and

advanced system monitoring functionality. The Diagnostic Pack includes the

following features:

Automatic Workload Repository

Automatic Database Diagnostic Monitor (ADDM)

Performance monitoring (database and host)

Event notifications: notification methods, rules, and schedules

Event history and metric history (database and host)

Blackouts

Oracle Tuning Pack

The Oracle Tuning Pack provides database administrators with expert

performance management for the Oracle environment, including SQL tuning

and storage optimizations. In order to use the Tuning Pack, you must also have

the Diagnostic Pack. The Tuning Pack includes the following features:

SQL Access Advisor

SQL Tuning Advisor

SQL Tuning Sets

Reorganize objects

Oracle Configuration Management Pack

The Oracle Configuration Management Pack enables database administrators to

track hardware and software configuration information for hosts and databases

managed by Enterprise Manager. That information can then be browsed,

searched, compared, exported, and tracked historically. The pack also offers

policy management and patch management capabilities based on the

configuration information. Finally, to facilitate deployments, cloning

functionality for database instance and Oracle home is also provided. The

Configuration Management Pack includes the following features:

Extensive searching on configuration data, such as Oracle home patch

status, versions deployed, parameter settings, database feature use, and

so forth.

Ability to compare the configuration of two databases

Host-to-host and host-to-multiple-hosts configuration comparison

Exporting of host configuration information on the same or a different

instance of Enterprise Manager for later browsing or comparison

Patch management, including automated determination of what patches

apply to a given Oracle home, using data obtained directly from

MetaLink, as well as mass deployment of patches

Database and Oracle home cloning

Policy management to alert the administrator to deviations from best

practices

Automated in-context Critical Patch advisory assessment

 

 

 

Automatic Shared Memory Management

 

The Automatic Shared Memory Management feature in Oracle Database 10g does exactly that. You can decide the total size of the SGA and then set a parameter named SGA_TARGET that decides the total size of the SGA. The individual pools (db block buffer cache, shared pool, Java pool, and large pool) within the SGA will be dynamically configured based on the workload. A non-zero value of the parameter SGA_TARGET is all that is needed to enable the automatic memory allocation.

 

Just issue the below command as sysdba

alter system set sga_target = 100M scope=both;

 

or click enable Automatic Shared Memory Management in Database Console.


 

 

 

2.0       TEST ANALYSIS

2.1    Security Considerations

 

 

2.2    Testing and Verification

 

Test Time and location

Nov 22,2004 ~ Dec 10, 2004

CTM TEC building 5F

 

 

 

Hardware specification for the server:

CPU: Pentium 4 1.8 G (single CPU)

RAM: 768 M

 

Operating system and software specification for the client and server:

Operating system: Windows 2003 server with SP1

Database: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0

 

Test result:

Software installation succeeded

Create database succeeded

Configured/maintenance database by database web console. 80% Pass.

Diagnostic Pack, Tuning Pack test succeeded.

Data Pump succeeded

Flash Back Table succeeded

Software, Database Patching via EM Web Console failed.

ASM implementation succeeded.

Grid control installation failed.

 

In summary:

It would be impossible to cover all the new features relevant to DBAs in Oracle Database 10g, but we have made an attempt to present a select few in these few weeks. In our test cases, 80% function pass. We can see there is a big changes in function wide and DBA area. It is simplify, easier to use, more powerful and many functions being automatic.

 

2.2.1   Expected Outcome

In this version we expected, there are enhancements in DBA part. That including Software installation, configuration, maintenance, new parameters, functionality, automatically. More efficiency, Self-monitoring, Self-tuning, simplify DBA task.

2.2.2   Functional Capability

With these few weeks, we do not have a chance to well test all the functionality or new features of Oracle Database 10g. We only point out some major functions and new features covering our DBA task. Let say DB monitoring, configuration, maintenance, data loading, ASM, EM web console.

 

 

2.2.3   Performance

Base on some new parameters and features. Most of Database configuration / maintenance tasks we can do thru the EM web Console and easy to work with it.

For Data Loading, a new tools Datapump Export/Import is faster than normal Export / Import very much, almost 5 times. Overall performance is greater than previous version.

 

2.2.4   Deviations from Test Plan

In the first day, we prefer to build up a database in ASM. After ASM instance built up, we do create a database on ASM. But we still not family with it and the Database instance sometimes hang. Because the ASM is new for us and isn’t a file system structure, maybe we need more time to evaluate it fully. So that we create Database under file system rather than ASM for this evaluation.

 

For the Grid control we have tried to implement but there is some error during the installation. We’re still checking the problem and it is no impact for our test plan since it is on a standalone environment.

 

During the our test Plan, we found EM web console and database agent processes always down or hang by unknown reason.

 

After create database instance, we try to use our existing OMS 9I server to manage it .  but it failed to discovered the 10g DB. And found from Oracle information that need 10 Grid Control to manage it.


3.0       Summary and conclusions

 

3.1    Demonstrated Capability

 

Ongoing the evaluation period, we have not enough time to cover all the new features relevant to DBAs in Oracle Database 10g, but we have made an attempt to present a select few in these few weeks.

 

ADDM is a powerful tool that has the "brains" to analyze performance metrics and offer recommendations based on best practices and accepted methodologies professed by seasoned Oracle professionals, all automatically. This functionality can tell the DBA not only what happened and why, but most important, what to do next.

 

The memory requirements of various pools in Oracle SGA are not static—rather, they vary based on the demand on the system. Automatic Shared Memory Management in Oracle Database 10g allows DBAs to manage system memory more efficiently by dynamically reallocating resources to where they are needed most while enforcing a specified maximum to prevent paging and swapping. More efficient memory management also leads to fewer memory requirements, which can make leaner hardware more viable.

 

Object handling has steadily improved over the course of several recent Oracle versions. Oracle8i introduced the table move from one tablespace to another, Oracle 9i Database R2 introduced the column renaming, and now—the last frontier—the renaming of a tablespace itself is possible. These enhancements significantly ease DBA tasks, especially in data warehouse or mart environments.

 

Oracle Flashback Database introduces the FLASHBACK DATABASE statement in SQL. It let you quickly bring your database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error.

 

Analyzing performance problems become very easy with the enhancement of the wait model in Oracle Database 10g. The availability of the history of session waits helps you diagnose the problem after the session has finished experiencing them. Classification of waits into wait classes also helps you understand the impact of each type of wait.

 

Functionalities become stronger and DBA task become simple.

 

 

3.2    System Deficiencies

 

EM Web Console is a good idea for DBA but it present unstable performance. It also affect DBA work.

 

In EM Web Console, Error always happen when submit jobs.

 

Some operations done on EM Web Console but not effect to Database.

Oracle Software database patching via EM Web Console failed when access metalink

 

Backup/Restore is a major task for DBA, but also need to afford a lot of time for implementation.

 

3.3    Expected Improvements

 

·          

 

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-717016/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    66939