Oracle NZ - Francisco Munoz Alvarez

One of my DBA’s just ask me why he was receiving the following error each time he tried to setup the host information thru the dbconsole (OEM):

- RemoteOperationException: ERROR: NMO not setuid-root (Unix-only)

The answer is very easy, this error occurs when you forgot to run the root.sh after you finished your installation, you can solve this problem easily running the root.sh  which is located in $ORACLE_HOME/root.sh (as root) and the  error will disappear.

Kind Regards,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ‘00′

column d_1 format a3 heading ‘01′

column d_2 format a3 heading ‘02′

column d_3 format a3 heading ‘03′

column d_4 format a3 heading ‘04′

column d_5 format a3 heading ‘05′

column d_6 format a3 heading ‘06′

column d_7 format a3 heading ‘07′

column d_8 format a3 heading ‘08′

column d_9 format a3 heading ‘09′

column d_10 format a3 heading ‘10′

column d_11 format a3 heading ‘11′

column d_12 format a3 heading ‘12′

column d_13 format a3 heading ‘13′

column d_14 format a3 heading ‘14′

column d_15 format a3 heading ‘15′

column d_16 format a3 heading ‘16′

column d_17 format a3 heading ‘17′

column d_18 format a3 heading ‘18′

column d_19 format a3 heading ‘19′

column d_20 format a3 heading ‘20′

column d_21 format a3 heading ‘21′

column d_22 format a3 heading ‘22′

column d_23 format a3 heading ‘23′

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00′,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01′,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02′,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03′,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04′,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06′,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07′,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08′,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09′,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10′,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11′,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12′,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13′,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14′,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15′,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16′,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17′,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18′,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19′,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20′,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21′,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22′,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0)),0,’-',sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23′,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez

Don’t miss the global launch webcast of Oracle Enterprise Manager 10g Release 5, this will be the 03/03/2009 09:00 AM (Pacific Standard Time), to assist please register here.

In the webcast you will learn about key new capabilities like:

• New Siebel 8.1.1 support, New management capabilities for Oracle Beehive and Oracle BRM
• New monitoring accelerators for Oracle Applications
• Comprehensive management for Oracle WebLogic Server and Oracle Service Bus
• More Oracle Database advisors, Real-time SQL monitoring, New security management, New cloning and masking automation and New change management automation
• New grid automation capabilities
• New virtualization management with an application perspective - manage both physical and virtual environments in a single console!

You can Download the OEM 10GR5  for Microsoft Windows 32-Bit and Linux x86  here.

Save this date on your agenda, and enjoy it

The big day for the APEX community is here! The new version of Apex (v3.2) is now available for download here.  (The apex_3.2.zip file size is only 97.3M)

• For New features, please refer to here.
• For New APEX 3.2 Oracle by Examples, please refer to  here.

Oracle Application Express 3.2 is supported on all Standard and Enterprise Edition versions of the Oracle database, 9.2.0.3 and higher, and specifically support the following platforms:

• Linux x86
• Microsoft Windows (32-bit)
• Mac OS X Server
• Linux on Power
• Linux x86-64
• z/Linux
• Linux Itanium
• Solaris x86
• Microsoft Windows (64-bit Itanium)
• Microsoft Windows (x64)
• Solaris Operating System (SPARC) (64 bit)
• AIX5L
• HP-UX PA-RISC
• HP Tru64 UNIX

Enjoy it

I’m very proud to announce that this morning I’ve been awarded the Oracle ACE Director   status, an Oracle ACE Director is someone who is not only recognized for past efforts and achievements but is also willing to commit themselves to ongoing participation and involvement in assisting the Oracle community.

I’d like to thank Lillian Buziak for all her help and support on this process, this will help me to intensify my activities around Asia Pacific and LAD in general.

Kind Regards, and  bye bye    welcome

Francisco Munoz Alvarez  

Finnaly we are able to test and check the new release of APEX (3.2), this version will enable you to load your Oracle Forms and Report source into an Oracle Application Express project and generate an initial APEX application.

To see more about this and other release 3.2 new features details, please click here!

Currently Oracle Application Express 3.2 is only available from Oracle hosted development site for   test it. It is not available for download yet, when ready, it will be released to download from the Oracle Technology Network  (OTN Web) at http://otn.oracle.com/apex.

Also check David Peak blog for more info about it: http://dpeake.blogspot.com/ 

Don’t lose more time, and let’s try it now!

Enjoy it,

Francisco Munoz Alvarez

As one of Auckland’s most popular summer events, the Auckland Lantern Festival brings Chinese New Year celebrations to a glowing finale with hundreds of beautiful lanterns, including brand new designs from China, some performers from New Zealand and overseas, as well as crafts, fortune-telling, fireworks and martial arts displays.

2009 is the Year of the Ox, great I’m an OX , here are some pictures of my family on these beatiful event:

Happy New Year!!!!

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:

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.

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.

Until last Thursday I was the Vice President of CLOUG (Chilean Oracle Users Group, www.cloug.org), but by request of the members I was now elected president of CLOUG. This will be a big challenge due to the distance between my primary house and my birth country (over 10.000 km), but I love it, and I’ll keep working hard to improve the Oracle knowledge in the region.

I want to express my gratitude to OTN, Oracle Chile, Tim Hall, Plinio Arbizu, Mauricio Naranjo, Marcelo Ochoa, Dennis Remmer, Dan Norris, Juan Camilo Ruiz, Robert Freeman, Ben Prusinski and Lillian Buziak. They believed on my project (my baby, CLOUG 2009), and thanks to them I’m 100% sure that CLOUG 2009 will be a huge success, this will be the first time that an Oracle event in South America will receive this number of Oracle celebrities and experts, the ACE’s are invading Chile, that is fantastic. I’ll also take this oportunity to invite our fellow Oracle professionals to nominate the best in their region to the ACE Program, please, help OTN to award the best of  the best in the Oracle world, If you know some one that is constantly contributing to the Oracle community, always willingly to share their knowledge and experiences. Please, don’t be shy and nominate it.

I’ll finish this note full of hope that other users groups  in the region will follow our example, and I’ll be glad to help and participate if requested.

Kind Regards,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

Redo Logging I/O-Related Wait Events

There are a number of Wait Events that happen during Redo Logging activities and most of them are I/O-related.

The two most important ones are ‘log file parallel write’ and ‘log file sync’. Oracle foreground processes wait for ‘log file sync’ whereas the LGWR process waits for ‘log file parallel write’.

Although we usually find ‘log file sync’ in the “Top 5 Wait/Timed Events” section of the Statspack report, in order to understand it we will first look at ‘log file parallel write’:

‘log file parallel write’

The LGWR background process waits for this event while it is copying redo records from the memory Log Buffer cache to the current redo group’s member log files on disk.

Asynchronous I/O will be used if available to make the write parallel, otherwise these writes will be done sequentially one member after the other. However, LGWR has to wait until the I/Os to all member log files are complete before the wait is completed. Hence, the factor that determines the length of this wait is the speed with which the I/O subsystem can perform the writes to the log file members. 

To reduce the time waited for this event, one approach is to reduce the amount of redo generated by the database:

• Make use of UNRECOVERABLE/NOLOGGING options.

• Reduce the number of redo group members to the minimum necessary to ensure  not all members can be lost at the same time.

• Do not leave tablespaces in BACKUP mode for longer than necessary.

• Only use the minimal level of Supplemental Logging required to achieve   the required functionality e.g. in LogMiner, Logical Standby or Streams.

Another approach is to tune the I/O itself:

• Place redo group members on storage locations so that parallel   writes do not contend with each other.

• Do not uses RAID-5 for redo log files.

• Use Raw Devices for redo log files.

• Use faster disks for redo log files.

• If archiving is being used setup redo storage so that writes for the current redo group members do not contend with reads for the group(s) currently being archived.

‘log file sync’ 

This Wait Event occurs in Oracle foreground processes when they have issued a COMMIT or ROLLBACK operation and are waiting for it to complete.

Part (but not all) of this wait includes waiting for LGWR to copy the redo records for the session’s transaction from Log Buffer memory to disk.

So, in the time that a foreground process is waiting for ‘log file sync’, LGWR will also wait for a portion of this time on ‘log file parallel write’.

The key to understanding what is delaying ‘log file sync’ is to compare average times waited for ‘log file sync’ and ‘log file parallel write’:

• If they are almost similar, then redo logfile I/O is causing the delay   and the guidelines for tuning it should be followed.

• If ‘log file parallel write’ is significantly different i.e smaller,   then the delay is caused by the other parts of the Redo Logging mechanism   that occur during a COMMIT/ROLLBACK (and are not I/O-related).   Sometimes there will be latch contention on redo latches, evidenced by   ‘latch free’ or ‘LGWR wait for redo copy’ wait events.

Handling Block Corruptions due to NOLOGGING

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by  the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed.  In Oracle8i an ORA-26040 is also signaled (”ORA-26040: Data block was loaded using the NOLOGGING option”) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery     through a NOLOGGING operation then you can use the Metalink Note 28814.1 onwards but note that:

•  Recovery cannot retrieve the NOLOGGING data

• No data is salvageable from inside the block

At this point basically you can do:

• the indexes with corrupt blocks can be dropped and re-created,

• the corrupt tables can be dropped and built from an alternative data source.

• the data file(s) impacted by NOLOGGING operations can be refreshed from the Primary or backup which completed after NOLOGGING operation.

• Or a combination of the above.

Currently in Oracle 9i and Oracle 10gR1, only the primary’s database V$DATAFILE view reflects NOLOGGING operations..   In 10gR2, the V$DATAFILE view will be enhanced to include information regarding when an invalidation redo is applied and the aforementioned corrupted blocks are written to the corresponding data file on a Redo Apply (or media recovery or standby) instance.

Repair NOLOGGING Changes on Physical and Logical Standby Databases

After a NOLOGGING operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future.  However there are additional steps required if you have an existing physical or logical standby database.    This is crucial if you want to preserve the data integrity of your standby databases.

For a physical standby database, Redo Apply will process the invalidation redo and mark the corresponding data blocks corrupt, follow these steps² to reinstate the relevant data files:

1. Stop Redo Apply (recover managed standby database cancel)
2. Offline corresponding datafile(s) (alter database datafile <NAME> offline drop ; )
3. Start Redo Apply (recover managed standby database disconnect)
4. Copy the appropriate backup datafiles over from the primary database (e.g. use RMAN to backup datafiles and copy them)
5. Stop Redo Apply (recover managed standby database cancel)
6. Online corresponding data files (alter database datafile <NAME> online ; )
7. Start Redo Apply (recover managed standby database disconnect)

For a logical standby database, SQL Apply skips over the invalidation redo completely; so, the subsequent corresponding table or index will not be updated.   However, future reference to missing data will result in ORA-1403 (no data found).   In order to resynchronize the table with the primary table, you need to re-create it from the primary database.   Follow the steps described in Oracle Data Guard Concepts and Administration

Note.   Basically, you will be using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure.

¹Invalidation redo containing information about the NOLOGGING operation and the range of blocks it affects.

²Please also refer to the Data Guard documentation:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10823/scenarios.htm#1015741

Wait for next part, I will talk about how to detect sessions generating a lot of Redo.