Oracle 11g Learnings

***** Oracle 11G Database Deep Drive Training Session held on 17/11/2008 and 17/11/2008 at Seed Infotech, Pune. *****

Introduction by: Mr. Girish Belpu (Sonata Information Technology Limited) Coordinator: Mr. K. Pathanjali Trainers/Speakers: 1. Mr. Phani Kumar 2. Mr. Ramchandra Topics covered: • • • •

Oracle 11G RAC overview Oracle 11G RAT Overview o Oracle DB Replay and Lab. o Oracle SQL Performance Analyzer (SPA) overview and Lab Active Dataguard overview and Lab Advanced Compression overview.

New Features in 11G – Free of Cost 1. 2. 3. 4.

Rolling Upgrades Better Data Guard Implementation Data compression – up to 40% compression using Virtual columns – It doesn’t occupy a space. It is run time operation. We cannot modify the column. 5. DDL waits- You can set the retry the query for specified time in seconds). After this time it will throw error as resource busy wait. Features with additional License Fee 1. Active Data Guard- it reduces HW cost. 2. RAT 3. Data Compression Real Application Testing (RAT)

1. It captures the workload on production Server (we can specify). 2. This also has a feature to specify the start time and end time of capture (pick hours). 3. This activity can be scheduled. 4. It captures all ther running query and stores in binary file. 5. This can be loaded on Test environment to test the performance, test the changes done on OS side, Patches, database parameters etc. 6. Using SPA we can find the differences in the performance and tune the problem. 7. Capturing of Workload Production DB (Oracle 10.2) to Oracle 11G is possible. 8. RAT is useful for DB side. DB Replay 1. It supports for Single DB to RAC and vice versa. 2. It will help to monitor the change due to DB upgrade or patching, schema change, parameter change, addition of RAC node, interconnect, OS platform upgrade or patching, addition of CPU, RAM, storage media etc. 3. STS (SQL tuning States)  Transport STS 4. This feature has <2% overhead while capturing the workload. It does not capture DDL. Virtual Indexing We can set an existing index to invisible mode using alter Index invisible command before disabling the index. The benefit of this feature is that we can check the usability of an existing index with dropping and recreating the index. This activity will have performance impact on the server. Active Dataguard (in 10G) • • • • •

The Production server and Test server configured for Data guard. Each log switch will ship archive files to Test server. The archive files of Production will be shipped and not applied on Test server. The Test Server will always be in mount stage. To access the data from Test server, apply all archive files; take the database to open status. Then you can query the DB tables.

Active Dataguard (in 11G)

• • • •

The Production server and Test server configured for Data guard. Each log switch will ship archive files to Test server. The archive files of Production will be shipped and applied automatically on Test server. The Test Server can be kept in open (Read only) mode. This can be used for read only transactions like MIS activities.

Snapshot Standby Advanced compression • • • • • • • • •

This compression features compress the data at row and column level up to 3X. It is more benefited in OLAP servers. It uses symbol table. Its block level storage is changed compared to 10G (like threshold value) It is added with thresh hold % for compression. After reaching to this value this block cannot be further compressed. This activity is not in control of DBA. Internally it keeps pointer to repeating or duplicate column values. It adds little performance load at write time. It has no load while reading the compressed block/data. The compression may happen at peak time. We can not schedule the compression process. Uses alter table command to start the compression on a table.

Oracle RAC Architecture

Instance 2

Instance 2

Instance 2

Shared Storage • • •

Each instance is having separate undo tablesapce, Redo log file groups and alert file. Shared Storage will have its own redo log files groups, control files. All files required to maintain IO activities. User sessions are distributed across available instances.

• • • • • • • • • • •

OCSSD, EVMD, CRSD are the new background processes started to run RAC. OCSSD- It is used to check the health of a particular Instance. VD- is a process used to ping clustered instance. (log of this process is maintained) Maximum services on a cluster or maximum instance for a RAC setup is configurable. Oracle maintains cluster registry. EVMD- It writes entry in alert file related to instance failure. The log file is called death instance alert log file. LMSx – This process is used to communicate the Library cache result from Library cache of one instance to other. GCS- This is used as Global Case Service to coordinate between running instances. GES- This is used to coordinate between Shared pool memory structures of all running instances. GRD- It is used while querying the Meta data information (Data Dictionary) of the running database including all running instances.

ASM (File system + Volume Manager) • • • • • • • •

It operates on Normal, High and External modes for mirroring It helps to improve the IO access by splitting the IO load. It operates on extent mirroring and not at block mirroring. Extents are distributed on RAW disks. Allocation unit is 1MB by default and configurable. For ASM management a separate role is available in Oracle – SYSASM. Automatic Memory Tuning for PGA is available. PGA can grow and shrink. Memory target is (SGA+PGA – Dynamic growth and shrink). In 10G only SGA is dynamic.

Some Parameters • • • • • •

SEC_MAX_FAILED_LOGIN_ATTEMPTS=10 In 11G password of database users is case sensitive. UDUMP, BDUMP folder structure is changed and grouped in ADR folder. APEX DB is added for HTML DB support. Some features are also added in SQL for Developers. Database Vault- Before to 11G Oracle DBA was having access to all database. He can query the database to know the management

•

information. This feature can block such information even from DBA. Few Data dictionary views are added such as o V$SQL_MONITOR o V$SQL_PLAN_MONITOR o V$ACCESS o V$ACTIVE_INSTANCE

Lab activities: Dated 18/11/2008 DBMS_WORKLOAD_CAPTURE Steps 1. Start Capture 2. Stop Capture The steps can be placed as scheduler. It created 7 files at desired location. It does not capture following 1. Flash Back are information 2. Distributed Database 3. SQL loader queries 4. Non PL/SQL 5. Streams 6. Shared Servers Request 7. OCI object navigation Steps 1. Capture workload on production server. 2. Test Server: Process the captured workload. 3. Test Server: Replay the capture workload. 4. Test Server: Test and analyze. SPA 1. 2. 3. 4. 5. 6. 7.

Capture SQL workload Measure the performance on production. Make some changes on captured workload. Measure the performance after change. Remediate – make change. Measure the performance Compare and analyze.