Oracle NZ - 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.

If you see the error EXP-00056 with Oracle error 19206 (like show bellow) when executing a export of an entire schema in Oracle 9i or 10g, don’t panic. The solution is easy.

EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at “SYS.DBMS_XMLGEN”, line 83
ORA-06512: at “SYS.DBMS_METADATA”, line 345
ORA-06512: at “SYS.DBMS_METADATA”, line 410
ORA-06512: at “SYS.DBMS_METADATA”, line 449
ORA-06512: at “SYS.DBMS_METADATA”, line 1156
ORA-06512: at “SYS.DBMS_METADATA”, line 1141
ORA-06512: at line 1
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at “SYS.DBMS_XMLGEN”, line 83
ORA-06512: at “SYS.DBMS_METADATA”, line 345
ORA-06512: at “SYS.DBMS_METADATA”, line 410
ORA-06512: at “SYS.DBMS_METADATA”, line 449
ORA-06512: at “SYS.DBMS_METADATA”, line 1156
ORA-06512: at “SYS.DBMS_METADATA”, line 1141
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

This problem happens when the metadata tables needed to perform an export are missing or invalid and can sometimes occur when you dropped or modified the XMLDB schema or due to an upgrade.

The solution is very easy, like I said before. Just run the catmeta.sql scrip located at ”$ORACLE_HOME/rdbms/admin/catmeta.sql” when connected as sysdba, and the problem will be fixed.

Cheers,

Francisco Munoz Alvarez

 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..  

Hi all

We are opening the call for papers for the first CLOUG (Chilean Oracle Users Group) National Conference at Santiago, Chile.

Topics we are particularly interested in hearing , but not limited to:

- Database
- Developer; Forms, JDeveloper, J2EE, JSF, XML, Web Services, APEX, BPEL, SOA, Hubs, .Net, and Fusion Middleware
- App’s; all incl GL, AR, AP, HR, Payroll, CRM,
- Higher Education
- Spatial
- Oracle eBusiness suite, Seibel and Retek, HR Management
- Hyperion
- Tools & Security

Each individual paper will consist of a 50 minute presentation (inc Q & A) in one of the follow streams: Development; Applications; Database Administration; Business; Siebel; Hyperion; and Vendor.

We are expecting over 600 attendants to this event. Participants profile expected: 30% Database, 30% Developers, 20% EBS, 15% APPS, 5% Others.

You can download the forms to submit your abstract here.

Best Regards,

Francisco Munoz Alvarez
Vice President CLOUG
Oracle ACE
www.cloug.org

The first CPU of 2009 is already out,  was released on January 13, 2009. Like always, Oracle strongly recommends applying the patches as soon as possible.

 To know more about  the January CPU like products and components affected, please refer to the following link:

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpujan2009.html

 Cheers,

Francisco Munoz Alvarez

 

Let start this New Year with a new mentality or a new resolution if you prefer call this way, let stop being reactive and start being proactive. Being proactive will reduce your database administration costs and increase your level of efficiency, and best of all, save your hours of sleep when you are on call.

Why check the problems only when they are critical, or when is too late and the database is down, or the users are screaming?

Being proactive is the best approach to keep your DB healthy and to show your company, or your clients that you really care about them.

Many DBA’s expend most of their time being firefighters only, fixing problems and working on user’s requests all the time. They don’t do any proactive work; this mentality only will cause an overload of work to them, thousands of dollars of overtime, several hours without access to the data to the users,  poor performance to the applications, and what is worse of all, several unhappy users thinking that you doesn’t have the knowledge needed to take care of their data.

Let’s mention a small example, you have the archive log area alert set to fire when it is 95% full, and this happens in the middle of the night, some DBA’s will take seriously the alert and solve the problem quickly, others will wait until the next day to take care of it because they are tired, or sleeping, or they are in a place without internet access at the moment the alert arrived. Will be a lot easier if they set a proactive alert to be fire when 75% or 85%, or even better ,take a look in the general health status of the DB before leave their work shift, to try to detect and solve any possible problem before be a real problem and be awake in the middle of the night or during the weekend (Remember how important is your personal and family time). I’ll always recommend to DBA’s to run 2 checklists daily, one in the start of their shift and other before they leave their shift.

I know several DBA’s that complain all the time that they got so many calls when they are on call, but they don’t do anything to solve the root problem, they only expend their time to solve the symptoms.

Here you can find an Oracle checklist script that will help to make your life a little easier (This is not my complete script, but will be a good start for you). This script is a compilation of several normal checklists and you can setup them with your own requirement and thresholds and always remember to have a baseline to compare. This script will not only help you to detect future or current problems, but also will help you to detect possible tuning requirement.

Here is an example of the script first phase outcome:

– ———————————————————————– –
– Oracle Instance Information
– ———————————————————————– –
Cpu_Count                                    4   |      Host_Name             OLIVER
Instance_Name                      prod   |      Database_Status    ACTIVE
Status                                     OPEN   |      Startup_Time        10-01-2009 19:50
Version                            11.1.0.7.0   |      Instance_Role        PRIMARY_INSTANCE
Database Space (Mb)       36604    |     SGA (Mb)                 511
Nb. Datafiles                                 43    |    Nb. Tempfiles          1

Archive destination LOCATION=E:\oracle\oradata\prod\archive
Database log mode ARCHIVELOG
Background Dump Dest d:\oracle\diag\rdbms\prod\prod\trace
Spfile D:\ORACLE\PRODUCT\11.1\PROD\DATABASE\SPFILEPROD.ORA
Redo size (Kb) 102400

– ———————————————————————– –
– Instance CheckList –
– ———————————————————————– –
Instance Status                                  OK    |      Listener Status      OK
– ———————————————————————– –
– Performance Memory CheckList –
– ———————————————————————– –
Total Sessions < 700                           OK - 19
Active sessions number <15               OK - 9
Data Buffer Hit Ratio > 80                   OK - 97
L.Buffer Reload Pin Ratio > 99         OK - 99
Row Cache Miss Ratio < 0.015         NO - 1.351
Dict.Buffer Hit Ratio > 80                     OK - 99
Log Buffer Waits = 0                             NO - 110
Log Buffer Retries < 0.0010               OK - 0
Switch number (Daily Avg) < 5          OK - 1
Jobs Broken = 0                                     OK 0
Shared_Pool Failure = 0                    OK - 0
– ———————————————————————– –
– Storage CheckList                                            –
– ———————————————————————– –
Dba_Tablespaces Status                   OK   |       V$Log Status             OK
V$Datafile Status                                  OK   |       V$Tempfile Status   OK
V$Recover_File                                    OK   |       V$Recovery_Log      OK
Tablespace in Backup Mode = 0     OK - 0
Tablespace < 95%                               OK- 0
Objects Invalid = 0                                NO - 147
Indexes unusable = 0                          OK - 0
Trigger Disabled = 0                            NO- 5
Constraint Disabled = 0                       NO - 2
Objects close max extents = 0           OK - 0
Objects can not extent = 0                  NO - 552
User Objects on Systems = 0              NO - 26
FK Without Index = 0                           NO - 138
– ———————————————————————– –
– Datagard CheckList                                          –
– ———————————————————————– –
Datagard Errors = 0                               OK- 0
Datagard Gap = 0                                OK - 0
Archives not Aplied < 5                       OK - 2
– ———————————————————————- –
– Installed options :
– ———————————————————————- –
- Objects option
- Connection multiplexing option
- Connection pooling option
- Database queuing option
- Incremental backup and recovery option
- Instead-of triggers option
- Parallel load option
- Proxy authentication/authorization option
- Plan Stability option
- Coalesce Index option
- Transparent Application Failover option
- Sample Scan option
- Java option
- OLAP Window Functions option

You also have several tools available in the market that can help you to monitor and setup your DB alerts, and help you with the proactive monitoring like: Grid Control, Enterprise Manager, Insider (FourthElephant), Spotlight (Quest) or if you prefer, your own scripts. The idea is to use them always on a proactive way, never reactive.

Let’s change our mentality, let stop being a firefighter and start to be a real hero!

Cheers,

Francisco Munoz Alvarez

Here are some pictures from our current family vacation in the NZ south island.

Happy 2009,

Francisco Munoz Alvarez