Oracle NZ - Francisco Munoz Alvarez

By Francisco Munoz Alvarez

Before you start with an installation of Grid Control, I’ll recommend you to go thru the documentation to understand all the minimum requirements and the installation process. For reference purpose, I’ll resume some important points of it here.

Let’s start taking  a close look in the components of the Grid Control  and some important information:

Component	Definition
Grid Control Console	From the Grid Control console, you can monitor and administer your entire computing environment from one location on the network. All the services within your enterprise, including hosts, databases, listeners, application servers, Oracle Collaboration Suite applications, and Web applications are easily managed from one central location (console).
Management Agent	The Management Agent is a process that is deployed on each monitored host. It is responsible for monitoring all targets on the host, for communicating that information to the middle-tier Management Service, and for managing and maintaining the host and its targets.
Management Service	The Management Service is a J2EE Web application that renders the user interface for the Grid Control console. It works with all Management Agents to process monitoring and jobs information, and uses the Management Repository to store data.
Management Repository	The Management Repository consists of objects such as database jobs, packages, procedures, views, and two tablespaces in Oracle Database that contain all available information about administrators, targets, and applications managed within Enterprise Manager.

The Management Service uploads the monitoring data received from the Management Agents to the Management Repository. The Management Repository then organizes all data collected, so that it can be retrieved by the Management Service and displayed in the Grid Control console, making our life easy. Because all data is stored in the Management Repository, it can be shared between any number of administrators accessing the Grid Control console.

Licensing Information

Although the installation media in your media pack contain many Oracle components, you are permitted to use only those components for which you have purchased licenses. Oracle Support Services does not provide support for components for which licenses have not been purchased. For more information please refer to Oracle Enterprise Manager Licensing Information.

Oracle Directory

If you choose to install Enterprise Manager Grid Control using a new database on a computer with no other Oracle software installed, Oracle Universal Installer creates an Oracle base directory for you. If Oracle software is already installed, then one or more Oracle base directories already exist. In the latter case, you must specify the Oracle base directory into which you want to install Oracle Database.

You are not required to create an Oracle base directory before installation, but you can do so if desired. You can set the ORACLE_BASE environment directory to point to this directory, which the Oracle Universal Installer will recognize.

Multiple Oracle Home Support

Enterprise Manager is installed on multiple Oracle homes within the Oracle base directory. This means that a typical Enterprise Manager Grid Control installation creates three Oracle homes in different Oracle home directories. For example, oms10g, db10g, and agent10g.

Permissions Required for Executing UTL_FILE

The management audit log package of the scheme owner uses the UTIL package. For this package to function properly, the Enterprise Manager schema user (for example, sysman) must have permissions to execute this package.

To grant permissions, run this command (where sysman is the schema user):

grant execute on utl_file to sysman;

Enterprise Manager 10g Grid Control Certification Matrix

Before you download the software, Oracle recommends you to read the Oracle Enterprise Manager 10g Grid Control Certification matrix. The certification matrix shows the operating systems and browser versions on which Enterprise Manager Grid Control and Management Agent are certified.

The Enterprise Manager 10g Grid Control Certification matrix is available on Oracle Metalink at:

https://metalink.oracle.com/

Login and select the Certify tab. On the Certify page, click View Certifications by Product and select Enterprise Manager 10g Grid Control, and then click Submit.

Preinstallation Requirements for Enterprise Manager

For small environments (100 monitored targets):

• 1 Host with 1 CPU (3GHz) 2 GB RAM and 2 GB Space to install the Oracle Management Service.
• 1 Host with 1 COU (3GHz) 2 GB RAM and 10 GB space to the Oracle Management Repository

Note: On small environments it’s ok to share the same host for the Oracle Management Service and Repository.

• 400 MB hard disk space to install the Oracle Management Agent for all Unix platforms and 500 MB for Windows,

For information regarding Preinstallation requirements, please refer to the OEM Documentation here.

Certified Enterprise Manager Targets

This is a resume of the list, for full list, please refer to the documentation.

Supported Targets	Release
Oracle Application Server	9.0.4.2 and later patchsets 10.1.2.0.0 (Phase 1) 10.1.0.2.0.1 (SEONE) 10.1.2.0.2 (Phase 2) 10.2.0.2.1 Patchset 10.1.3 (Standalone OC4J)
Oracle Database, Listener	8.1.7.4 9.0.1.5 9.2.0.7 and later patchsets 10.1.0.4 and later patchsets 10.2
Oracle Real Application Clusters Database	9.2.0.6 10.1.0.4 10.1.0.5 10.2
Oracle Collaboration Suite	9.0.4.2 and later 10.1.1

Note: Oracle recommends that the target host on which you are installing the Management Agent have a static IP address and not DHCP.

Reference material: Enterprise Manager Grid Control Installation and Basic Configuration Guide.

 By Francisco Munoz Alvarez

TIPS USING NOLOGGING MODE 

DIRECT PATH INSERT 

To use Direct Path Insert use the /*+ APPEND */ hint as follow:

• INSERT /*+ APPEND */ into … SELECT …

When direct path insert is used oracle does the following:

• Format the data to be inserted as oracle blocks.

• Insert the blocks above the High Water Mark (HWM)

• When commit takes place the HWM is moved to the new place (The process is done bypassing the buffer cache).

It is clear that direct load is useful for bulk inserts. Using it to insert few hundred records at a time can have bad effect on space and performance.

It is very important to understand how Direct Path Inserts affects redo generation. As mentioned above it does not affect indexes but it is affected by the following factors:

• The database Archivelog mode.
• Using the /*+ APPEND */ hint.
• The LOGGING mode of the table.
• The FORCE LOGGING mode of the database (from 9i R2).

If the database is in FORCE LOGGING mode then Oracle will treat the table as if it was in LOGGING mode regardless of its mode. To find out if the database is in FORCED LOGGING or not run:

• select FORCE_LOGGING from v$database ;

If the /*+ APPEND */ Hint is not used then the insertion will generate the normal amount of redo regardless of the other factors.

This table will show the relation between ARCHIVELOG mode and having the table in LOGGING mode when the /*+ APPEND */ hint is used. This does not include index and  data dictionary changes redo generation.

LOGGING MODE	ARCHIVELOG	NOARCHIVELOG
LOGGING	Redo	No Redo
NOLOGGING	No Redo	No Redo

For Bulk DML  

Bulk Inserts 

To load bulk data using Direct Path.

• set table in nologging mode. Alter table table_name nologging;
• alter index index_name unusable ;
• alter session set skip_unusable_indexes=true ;(*)
•  Insert /*+ APPEND */ into table_name select …
•  Alter index index_name rebuild nologging;
• Alter table table_name logging ;
• Alter index index_name logging ;
• Backup the data.

(*)skip_unusable_indexes is an instance initialization parameter in 10g and defaulted to true. Before 10g, skip_unusable_indexes needs to be set in a session or the user will get an error. It is a good practice to set it in a session, regardless of the database version, when the above is done.

There is no direct way (at the time of writing this document) of reducing redo generation for bulk update and delete. The user needs to reduce the workload on the database.

Bulk Delete 

1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); The NOLOGGING comes after the TABLE_NAME not at the end of the statement.
2. Insert /*+ Append */ into new_table select the records you want to keep from current_table.
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

(*) If the data left is so small or there are a lot of dependencies on the table (views, procedures, functions) the following steps can be used instead of 3-6 above:

1. Disable constrains on current_table;
2. Truncate current_table;
3. make indexes unusable;
4. alter current table NOLOGGING ;
5. Insert /*+ APPEND */ into current_table select * from new_table ;
6. commit;
7. rebuild indexes with NOLOGGING;
8. enable constraints
9. Put current table and indexes in LOGGING mode
10. backup the data
11. drop table new_table;

Bulk Update 

Follow the steps for bulk Delete but integrate the update within the select statement. Lets say that you want to update the value column in the goods table by increasing it by 10% the statement will be like:

1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); (The nologging comes after the table_name, not at the end of the statement.).
2.  Insert /*+ Append */ into new_table select (update statement eg: col1, col2* 1.1,…)
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

Backup and Nologging 

If required, it is possible that the data loaded using NOLOGGING can be loaded again. If the database crashed before backing up the new data then this data can not be recovered.

Here are the two scenarios of backing up:

Export (exp or expdp) 

This method will allow you to recover the loaded data up to the point the export was taken but not later.

For customers using 10g Oracle Streams, there is also the option of using Data Pump Export and Import Direct Path API. For more details please refer to the Utilities Guide

Hot Backup 

In order to recover any additional data or modification to the table you bulk inserted into using NOLOGGING the least you need to do is a hot backup of that tablespace. Remember you still generate redo for DML on the table when it is in NOLOGGING mode but you are strongly advised to put it in LOGGING mode in case you run one of the operations mentioned in the Disabling Logging section.

Wait for next part, I will talk about Redo Log I/O related wait events..  

What is a Redo? 

Let’s conduct a brief summary about the redo process. When Oracle blocks are changed, including undo blocks, oracle records the changes in a form of vector changes which are referred to as redo entries or redo records. The changes are written by the server process to the redo log buffer in the SGA. The redo log buffer is then flushed into the online redo logs in near real time fashion by the log writer LGWR.  

The redo logs are written by the LGWR when: 

• When a user issue a commit.
• When the Log Buffer is 1/3 full.
• When the amount of redo entries is 1MB.
• Every three seconds 
• When a database checkpoint takes place. The redo entries are written before the checkpoint to ensure recoverability. 

Redo Generation and Recoverability 

The purpose of redo generation is to ensure recoverability.  This is the reason why, Oracle does not give the DBA a lot of control over redo generation. If the instance crashes, then all the changes within SGA will be lost. Oracle will then use the redo entries in the online redo files to bring the database to a consistent state. When a tablespace is put in backup mode the redo generation behaviour changes. As long as the table space is in backup mode Oracle will write the entire modified block to the redo log. This is done due to the reason Oracle can not guaranty that a block was not copied while it was updating as part of the backup. 

Important points about LOGGING and NOLOGGING 

Despite the importance of the redo entries, Oracle gave users the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode.  NOLOGGING affect the recoverability. Before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, this are some points regarding it: 

• NOLOGGING is designed to handle bulk inserts of data which can be easy re-produced.
• Regardless of LOGGING status, writing to undo blocks causes generation of redo.
•  LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. (Means that the NOLOGGING attribute will not have any effect on the segments) If the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.
• Any change to the database dictionary will cause redo generation. This will happen to protect the data dictionary.  An example: if we allocated a space above the HWM for a table, and the system fail in the middle of one INSERT /*+ APPEND */ , the Oracle will need to rollback that data dictionary update.  There will be redo generated but it is to protect the data dictionary, not your newly inserted data (Oracle will undo the space allocation if it fails, where as your data will disappear).
• The data which are not logged will not be able to recover. The data should be backed up after the modification.
• Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
• NOLOGGING is not needed for Direct Path Insert if the database is in NO ARCHIVE LOG MODE. (See table 1.1) 

Table Mode	Insert Mode	ArchiveLog Mode	Result
LOGGING	APPEND	ARCHIVE LOG	REDO GENERATED
NOLOGGING	APPEND	ARCHIVE LOG	NO REDO
LOGGING	NO APPEND	ARCHIVE LOG	REDO GENERATED
NOLOGGING	NO APPEND	ARCHIVE LOG	REDO GENERATED
LOGGING	APPEND	NO ARCHIVE LOG	NO REDO
NOLOGGING	APPEND	NO ARCHIVE LOG	NO REDO
LOGGING	NO APPEND	NO ARCHIVE LOG	REDO GENERATED
NOLOGGING	NO APPEND	NO ARCHIVE LOG	REDO GENERATED

Table 1.1 

• The data which is not able to reproduce should not use the NOLOGGING mode. If data which can not be reloaded was loaded using NOLOGGING. The data cannot be recovered when the database crashes before backing the data.
• NOLOGGING does not apply to UPDATE, DELETE, and INSERT.
• NOLOGGING will work during certain situations but subsequent DML will generate redo. Some of these situations are:
  • direct load INSERT (using APPEND hint),
  • CREATE TABLE … AS SELECT,
  • CREATE INDEX.
• If the LOGGING or NOLOGGING clause is not specified when creating a table, partition, or index the default to the LOGGING attribute, will be the LOGGING attribute of the tablespace in which it resides. 

If the database has a physical standby database, then NOLOGGING operations will render data blocks in the standby “logically corrupt” because of the missing redo log entries. If the standby database ever switches to the primary role, errors will occur when trying to access objects that were previously written with the NOLOGGING option, you will an error like this:

 

ORA-01578: ORACLE data block corrupted (file # 3, block # 2527)

ORA-01110: data file 1: ‘/u1/oracle/dbs/stdby/tbs_nologging_1.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option”

 

That doesn’t sound good, and certainly I can’t imagine a happy DBA called at 3:00 AM to recover a database and that error message comes up.

**If you want to read the full paper please download it from: Logging or Not Logging, This is the Question. 

Cheers,

Francisco Munoz Alvarez