Oracle NZ - Francisco Munoz Alvarez

By Francisco Munoz Alvarez

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.

The following operations are a few that cannot make use of NOLOGGING mode:  

• Table redefinition cannot be done NOLOGGING.
• Temp files are always set to NOLOGGING mode. 

The FORCE LOGGING mode is a persistent attribute of the database.  That is, if the database is shut down and restarted, it remains in the same logging mode state.  FORCE LOGGING must be configured again after recreating the control file. 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. 

The options UNRECOVERABLE (introduced in Oracle7) and NOLOGGING (introduced in Oracle8) can be used to avoid the redolog entries generation for certain operations that can be easily recovered without using the database recovery mechanism.  This option sends the actual DDL statements to the redo logs (this information is needed in the data dictionary) but all data loaded, modified or deleted are not sent to the redo logs.

Even though you can set the NOLOGGING attribute for a table, partition, index, or tablespace,  this mode does not apply to every operation performed on the schema object for which you set the  NOLOGGING attribute. See more details on which operations are supported to be executed in this mode in the following topics. 

Why Oracle generates redo and undo for DML 

When you issue an insert, update or delete, Oracle actually makes the change to the data blocks that contain the affected data even though you have not issued a commit.  To ensure database integrity, Oracle must write information necessary to reverse the change (UNDO) into the log to handle transaction failure or rollback.  Recovery from media failure is ensured by writing information necessary to re-play database changes (REDO) into the log.  So, UNDO and REDO information logically MUST be written into the transaction log of the RDBMS (see below regarding temporary tables). 

While the RDBMS logically would only need to write UNDO and REDO into the transaction log, the UNDO portion must also be kept online (on disk and accessible to the RDBMS engine) to enable rollback of failed transactions. If UNDO data was only stored in the transaction log, the log could get archived and the RDBMS would have to try and read it from tape.  On some platforms, the tape could be sitting in the DBA’s desk drawer, so there are practical problems with this solution.  Every RDBMS must meet the basic requirement of online access to undo data, and Oracle does this by storing UNDO data in what we call Rollback Segments (rollback = undo). 

Because Oracle places UNDO data into a rollback segment and also must (logically) place this data into the transaction log, it is simpler to just treat rollback tablespaces like any other tablespace from a log generation perspective.  That is, Oracle generates REDO for a Rollback Segment, which is logically the same as UNDO for a data block (i.e. your table, index, etc.).  

Oracle’s transaction log is really called the REDO log because it only contains redo records.  There logically MUST be UNDO records stored in the log, but they are stored in the form of redo for rollback segments. For temporary tables, Oracle will need to do things like facilitate rollback, but it is not necessary to bring back temporary tables following media failure. 

The undo data is also needed for things like rollback to save point and read consistency, not just to reclaim space that was used by that temporary table.  

Amount of redo generated for temporary tables
 

The amount of log generation for temporary tables should be approximately 50% of the log generation for permanent tables.  However, you must consider that an INSERT requires only a small amount of “undo” data, whereas a DELETE requires a small amount of “redo” data.  If you tend to insert data into temporary tables and if you don’t delete the data when you’re done, the relative log generation rate may be much lower for temporary tables that 50% of the log generation rate for permanent tables.   

Can Redo Generation Be Disabled During Materialized View Refresh? 

There is no way to turn off redo generation when refreshing materialized views.Setting the NOLOGGING option during the materialized view creation does not affect this, as the option only applies during the actual creation and not to any subsequent actions on the materialized view. Enhancement requests have been raised to be able to turn off redo generation during a refresh but these were rejected as this could put the database into an inconsistent state and affect options such as Data Guard as well as backup and recovery.The amount of redo generated can be reduced by setting ATOMIC_REFRESH=FALSE in the DBMS_MVIEW.REFRESH options.   

Flashback and NOLOGGING

When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and data files affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 8:00 to 8:15 on April 7, 2008, and you later need to use Flashback Database to return to the target time 08:07 on that date, the objects and data files updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected data files immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode. 

Performance and Recovery considerations  

The NOLOGGING mode improves performance because it generates much less log data in the redo log files helping in eliminating the time needed to execute the redo generation (latch acquisition, redolog writing, etc.). The user is responsible for backing up the data after a NOLOGGING insert operation in order to be able to perform media recovery.   Be aware that this feature disables the recover mechanisms for this transaction: It will be required to repeat the process from the very beginning in case of a database or instance failure.

Wait for next part, I will talk about NOLOGGING: how, when and why to use.  

 

I’m pleased to confirm that my paper ” Logging or Nologging: That is the Question!” submitted to the Perth and Gold Coast conferences was accepted.

 See you at:

• October,6 and 7 - AUSOUG National Conference Series at Perth, AU
• October,13 and 14 - AUSOUG National Conference Series at Gold Coast, Conrad Jupiters, AU

For more information, please refer to Oracle with 20:20 Foresight .

Cheers,

Francisco Munoz Alvarez

About the DBA job: 

Most of the people that I talk to who have difficulties starting out in their DBA career really have an issue trying to absorb the mountainous volumes of information that a DBA needs to know. After all, System Administrators make a whole career out of learning the ins and outs of the OS. Application Developers make a whole career out of learning how to build and code excellent applications. Not only does a DBA have to know a great deal about these two different jobs, but then the DBA needs to spend even more time working on understanding the architecture of the database, and understanding how every piece of everything fits together! Does it sound too daunting of a task? There have been many who think so and after becoming frustrated, have left their DBA job for a completely different job. Then there are those individuals who thrive on disseminating and understanding all of this information, and using that information to make good, sound technical decisions. As I was fond of saying in my early days as a DBA, it all seems to me to be one great puzzle. The challenge is in getting all of the pieces to fit. Which type of person are you? 

Many DBAs are “on-call”. They get called at all hours of the day and night to resolve critical problems with their database. The database is the life-blood of the business’s IT infrastructure. Without data, there would be no need to have a computer system. It is the data that drives the business. Where would amazon.com be if their web site couldn’t search the database for products and if no one could place an order for their products? They wouldn’t be in business for very long. There are many companies that lose significant revenues when their database is down, even for the shortest period of time. For this reason, the DBA needs to be available to resolve issues as fast as possible, should they occur. Many shops have a team of DBAs who rotate being on-call. These DBAs support databases for 24×7 applications. Are you ready to be placed on-call if the job requires it? Some of the DBAs duties include applying patches to software or making database changes. Often times, these changes cannot be done while the company’s employees are at work, expecting that the database be up and running so that they can get their jobs done. This means that the DBA frequently has to come in real early in the morning, or real late at night, or even on the weekends to perform work that can only be done outside of normal business hours. Are you ready to work some strange hours at times, or are you looking for a 9 to 5 job? 

One key asset for a DBA to hold is what is commonly referred to as “soft skills”. The DBA needs to be able to work well in a team environment, commonly in diverse teams with System Administrators, Network Administrators, Application Developers, Project Managers and others. DBAs need to be able to explain difficult, technical concepts in plain English that others in the team environment can understand. DBAs need to be able to direct team members on database-related issues. How are your soft skills? While not an all-inclusive list, typical DBAs perform the following duties: 

• Monitor database instances on a daily basis to ensure availability. Resolve unavailability issues.
• Collect system statistics and performance data for trending and configuration analysis.
• Configure and tune dB instances for optimal performance under application specific guidelines.
• Analyze and administer dB security. Control and monitor user access to dB. Audit database usage when necessary.
• Monitor backup procedures. Provide recovery when needed. Develop and test backup and recovery procedures.
• Upgrade RDBMS software and apply patches when needed. Upgrade or migrate database instances as necessary.
• Support application developers with any and all dB related activities.
• Keep up with dB trends & technologies. Use new technologies when applicable. Install, test, and evaluate new Oracle related products.
• Perform storage and physical design. Balance design issues to achieve optimal performance.
• Create, configure and design new dB instances.
• Diagnose, troubleshoot and resolve any dB related problems. Work with Oracle Support if necessary to bring problems to a successful resolution.
• Ensure Oracle networking software (SQL*Net, Netx, Names, OiD) is configured and running properly.
• Work with System Administrators (Unix & NT) to ensure Oracle related matters are handled properly.
• Create any necessary scripts for effective and occasionally periodic dB maintenance activities.

Tips for your starting Career: 

Tip #1:Become educated. – Learn as much as you can you can about a database. This will most likely involve some time and effort on your part, outside of normal working hours. Take a database class at a local college or university. Many training companies offer classes on Database Administration. You may find that you have to pay for these yourself if your employer will not fund your education opportunities. Many DBA positions require at least a Bachelor’s degree in Computer Science or a related field, so you should have at least that credential. 

Tip #2: Practice being a DBA. – Many database vendors let you download trial, test, or evaluation copies of their database system. Download a copy and install the software on your own personal computer. Play with the database. Intentionally break the database and try to fix it. Try to perform as many of the DBA functions as you can think of. Test out and hone your skills on your own test platform so that you can be able to demonstrate some level of database administration ability. 

Tip #3: Get certified – Many database vendors now offer a certification for their database product. Many companies now look at certification as a measuring stick. One thing to keep in mind is that just being certified is not enough. Passing DBA certification tests do not automatically mean that you know how to administer a database. They just say to the potential employer that you now possess a certain set of skills. Being certified also tells a potential employer that you are serious about your pursuit of a DBA job. I’ve seen many people complain that they are certified with no experience, but still can’t get that first DBA job. Certification alone won’t land you the job, but it doesn’t hurt either. If nothing else, you’ve learned a great deal while trying to get certified. Just don’t rely on the certification to get you that job you are looking for. You will need more than that. But it will help in the end. 

Tip #4: Leverage your existing skill set – Many DBAs come from a System Administrator background. Others come from an Application Development background. If possible, see if you can use your existing skill set to get a job. The goal here is to make it a win-win situation for you and your employer. For instance, let’s assume that you are already a SysAdmin looking to break into the DBA field. Maybe you can find a job at a company that will be able to use your SysAdmin skills part of the time, while being able to get your feet wet in Database Administration the rest of the time. If you are already a DBA on one vendor’s platform but wish to move to another vendor’s platform, see if you can land a job which has both platforms. For instance, use your SQL Server DBA skills in a shop that also lets you backup the Oracle DBA. In this way, both the company and you get what you want. After you’ve had exposure to DBA work, you can try to get a position that will let you do it full time, maybe even with the same company.  

Tip #5: Take advantage of current opportunities – Sometimes, one gets into the DBA field just by being in the right place at the right time. If your current employer has an opportunity for you to work on any database project, jump at the chance! Any database experience is worth more than no database experience. Let your management know that you are actively seeking any database opportunities that come by. Hopefully, they will think of you when the next one comes along. After working on these database projects and seeing the desire in your eyes to become a DBA, they may decide to train you, and promote you. Many, many people get their first DBA job in exactly this manner, sliding into a Junior DBA position once they have worked on a few database-related projects. Often times, when a DBA leaves the company, that company will look at hiring an internal candidate if they feel that candidate is trainable. 

Tips to Become a Good DBA: 

You will find that there is an enormous amount of material that you must learn to become an effect Database Administrator. Your first year or two will be spent learning more than you may have ever learned in your career. If you find that the amount of information is leading to brain overload, just sit back, take a breather, and come back to it. To help you along the way, you can follow the roadmap below: 

Tip #1: Relational database theory – For this paper, I’m going to assume that the type of database you will be administering is a “relational” database. Other database models do exist, but the relational model is the dominant one in the industry for the last twenty years. If your database system follows a different model, then learn that theory. Relational database theory is very important. It is the background upon which everything has been built. I’ve seen many people who make the jump to database administration and never bother to learn solid relational database theory. Inevitably, their lack of a solid basis in this theory shows up as a shortcoming many times during their career. If you understand relational database theory well, then you will be able to make smoother transitions to any vendor’s Relational Database Management System (RDBMS). It doesn’t matter if I am using Oracle’s database, or IBM’s DB2, or Microsoft’s SQL Server. All of them are relational database systems. They all do basically the same things. The difference lies in how they do the same things. A solid relational database theory is not essential for a Junior DBA position. But it is vital if you ever want to grow your career past the Junior DBA level., also many college-level textbooks cover relational database theory very well.  

Tip #2: Learn the query language inside and out – Databases all have a language that lets you get data from the database, put data into the database, and modify the data that is in the database. For relational databases, that language is Structured Query Language (SQL). This one language is your tool to interface with the database. It is vital that this tool not be a barrier to further learning. In your test database, practice various SQL statements until they become second nature to you.  

Tip #3: Begin learning basic database administration functions – Isn’t this why you are here in the first place? So why is it third on the list? We are trying to build a pyramid of knowledge and I feel strongly that one needs to know relational database theory and SQL real well as they will become tools that you will use as you learn how to perform basic database administration functions. These functions can include starting and stopping a database, backing up and recovering a database, and creating/dropping/altering database objects. For Oracle database administration, there is a lot of material on oracle.tahiti.com that can gives you a good taste of what to expect. At this time, you should also be reading and understanding the Oracle 9i/10gR2/11g Concepts Guide, the Oracle 9i/10gR2/11g Administrator’s Guide, and the Oracle 9i/10gR2/11g Backup and Recovery Guide, all from the Oracle documentation. 

Tip #4: Read, read, and read – Since you just started your career as a DBA, you are just beginning to build a skill set. It takes a long time to build, absorb, and comprehend all of the information you will be learning. Undoubtedly, your Senior DBA will have work to do, so he or she will not always be able to devote a ton of time to your studies. You will have to learn many things on your own. This is where reading comes in. There are many books on the market, which answer a lot of database related topics. Oracle Press is Oracle Corp’s official publishing company with a large number of Oracle-related books. There are other publishing companies as well, like Wrox Press, Rampage, Apress and O’Reilly Press. You also have the Oracle documentation to read. And there are numerous web sites and newsgroups available as well. Read as much as you can get your hands on. And it’s not a bad idea to read these items more than once to absorb things you may have missed the first time. 

Tip #5: Create test cases – I often see beginner questions that ask the most basic questions that can easily be answered if the person just took the time to figure it out themselves. Undoubtedly, you will have many questions as you begin your Oracle studies. Decide if these are questions that you can answer yourself. For instance, I once had someone ask me if it was possible to insert NULL values into a column with a UNIQUE constraint. At first, this may not seem to be an easy question to answer. But it is really easy to test! Just create a simple table. On one of your columns, enable a UNIQUE constraint. Try to insert NULL values into that column. Does it work? You should be able to answer this question quite easily. So why create these test cases? One reason is that by doing so, you will be enhancing your problem solving skills. The same skills required to create these test cases are some of the same skills used in problem solving. Problem solving skills will greatly help your DBA career. Another reason is that you will often need to create more complex test cases as your career progresses in order to guarantee database and application success. Even simple test cases are building blocks for more complex database and application analysis in the future.  

Tip #6: Find a mentor – A mentor can be used to guide, or steer your DBA career (or any career for that matter). They can give pointers, answer questions, and help save some time as you grow your DBA career. Hopefully, this paper will serve as a mentor towards part of growing your career. If you are working in an environment with a Senior DBA, then that person should be responsible for mentoring a good portion of your career. You may choose to look at other mentors as well.  

Tip #7: Participate in local user groups – Many cities across the nation have local user groups which meet periodically to talk about database-related topics. Join one of these local user groups if possible. This gives you a great way to interact and network with others in your field.

by Grow That DBA Career

By Francisco Munoz Alvarez

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. The cost of maintaining the redolog records is an expensive operation involving latch management operations (CPU) and frequent write access to the redolog files (I/O). You can avoid redo logging for certain operations using the NOLOGGING feature. Regarding redo generation, I saw all the times two questions in the OTN Forums: 

Why I have excessive Redo Generation during an Online Backup? 

When a tablespace is put in backup mode the redo generation behavior changes but there is not excessive redo generated, there is additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode.

As long as the table space is in backup mode Oracle will write the entire block is dumped to redo when the ALTER TABLESPACE TBSNAME BEGIN BACKUP MODE is entered but later it generates the same redo. 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. Let’s go explain better this part: 

In hot backup mode only 2 things are different: 

• The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes.  Normally only the changed bytes (a redo vector) are written. In hot backup mode, the entire block is logged the first time.  This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.  Let’s say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time).  The backup program goes to read an 8k Oracle block.  The OS gives it 4k.  Meanwhile the DBWR has asked to rewrite this block.  The OS schedules the DBWR write to occur right now.  The entire 8k block is rewritten.  The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block.  The backup program has now gotten an impossible block — the head and tail are from two points in time.  We cannot deal with that during recovery.  Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself at least.  We can recover it from there.
• The datafile headers which contain the SCN of the last completed checkpoint are NOT updated while a file is in hot backup mode. DBWR constantly write to the datafiles during the hot backup.  The SCN recorded in the header tells us how far back in the redo stream one needs to go to recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablespace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up.  This will reduce the number of blocks that may have to be logged to the minimum possible. 

What are the differences between REDO and UNDO? To clear this question we have this table: 

	UNDO	REDO
Record of	How to undo a change	How to reproduce a change
Used for	Rollback, Read-Consistency	Rolling forward DB Changes
Stored in	Undo segments	Redo log files
Protect Against	Inconsistent reads in multiuser systems	Data loss

An undo segment is just a segment, like a table or an index or a hash cluster or a materialized view is a segment. The clue is in the name. And the rule is that if you modify part of a segment, any segment, regardless of its type, you must generate redo so that the modification can be recovered in the event of media or instance failure. Therefore, you modify EMP; the changes to the EMP blocks are recorded in redo. The modification to EMP also has to be recorded in UNDO, because you might change your mind and want to reverse the transaction before you commit. Therefore, the modification to EMP causes entries to be made in an undo segment. But that’s a modification to a segment -this time, an undo segment. Therefore, the changes to the undo segment also have to be recorded in redo, in case you suffer a media or instance failure. 

If your database now crashed and you had to restore a set of datafiles, including those for the undo tablespace, from 10 days ago, you would of course do what Oracle always does: start reading from your archived redo, rolling the 10 day old files forward in time until they were 9, then 8, then 7 then 6 and so on days old, until you get to the time where the only record of the changes to segments (any segment) was contained in the current online redo log, and then you’d use that redo log to roll the files forward until they were 8 minutes old, 7 minutes, 6, 5, 4,… and so on, right up until all changes to all segments that had ever been recorded in the redo had been applied. At which point, your undo segments have been re-populated. So now you can start rolling back those transactions which were recorded in the redo logs, but which weren’t committed at the time of the database failure.  

I can’t emphasize enough, really, that undo segments are just slightly special tables. They’re fundamentally not very different from EMP or DEPT, except that new inserts into them can over-write a previous record, which never happens to EMP, of course. If you generate change vectors when you update EMP, you generate change vectors when you generate undo.   Why do we store the before and after image in redo and then duplicate half of that by repeating the store of the before image in undo? Because redo is written and generated sequentially and isn’t cached for long in memory (most log buffers are a few megas in size, tops). Therefore, using redo to rollback a mere mistake or as a result of a change of mind, whilst theoretically do-able, would involve wading through huge amounts of redo sequentially, looking for one little before image in a sea of changes made by lots of people and all of that wading would be done by reading stuff off disk (like it is in a recovery scenario). Undo, on the other hand, is stored in the buffer cache (just as EMP is stored in the buffer cache), so there’s a good chance that reading that will only require logical I/O, not physical. And your transaction is dynamically linked to where it’s written its undo, so you and your transaction can jump straight to where your undo is, without having to wade through the entire undo generated by other transactions. In performance terms, there is no comparison. Splitting ‘you need this for recovery’ from ‘you need this for changes of mind’ was a stroke of genius on the part of Oracle: other databases merely have ‘transaction logs’ which serve both purposes, and suffer in performance and flexibility terms accordingly.  

Wait for next part, I will talk about Logging and No Logging: What is it, how, when and why. 

By Francisco Munoz Alvarez

Introduction

The main question about NOLOGGING I hear all the time is: does creating a table with the NOLOGGING option means there is “no generation of redo ever”, or just that the initial creation operation has no redo generation, but that DML down the road generates redo? How and when can the NOLOGGING option be employed?

Redo generation is a vital part of the Oracle recovery mechanism. Without it, an instance will not recover when it crashes and will not start in a consistent state. Excessive redo generation is the result of excessive work on the database.

This paper covers the subject of reducing redo generation using LOGGING and NOLOGGING options, the differences between them, how it happens, how to reduce it and when to use.

Also, you will find examples and tips regarding each one of them.

The main benefits of the NOLOGGING option suggested by the Oracle® Database Administrator’s Guide 10g Release 2 are:

• Space is saved in the redo log files

• The time it takes to create the table is decreased

• Performance improves for parallel creation of large tables

“A very important rule with respect to data is to never put yourself into an unrecoverable situation. The importance of this guideline cannot be stressed enough, but it does not mean that you can never use time saving or performance enhancing options.“

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. (See Figure 1)

  

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.

“If the log buffer is too small, then log buffer space waits will be seen during bursts of redo generation. LGWR may not begin to write redo until the _log_io_size threshold (by default, 1/3 of the log buffer or 1M whichever is less) has been exceeded, and the remainder of the log buffer may be filled before LGWR can complete its writes and free some space in the log buffer.

Ideally, the log buffer should be large enough to cope with all bursts of redo generation, without any log buffer space waits.

Commonly, the most severe bursts of redo generation occur immediately after a log switch, when redo generation has been disabled for some time, and there is a backlog of demand for log buffer space” by Steve Adams.

Redo log files record changes to the database as a result of transactions and internal Oracle server actions. (A transaction is a logical unit of work, consisting of one or more SQL statements run by a user.) Redo log files protect the database from the loss of integrity because of system failures caused by power outages, disk failures, and so on. Redo log files must be multiplexed to ensure that the information stored in them is not lost in the event of a disk failure. The redo log consists of groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of that group, and each group is identified by a number. The Log Writer (LGWR) process writes redo records from the redo log buffer to all members of a redo log group until the file is filled or a log switch operation is requested. Then, it switches and writes to the files in the next group. Redo log groups are used in a circular fashion (see figure 2).

Best practice tip:

Oracle recommends that redo log groups have at least two files per group, with the files distributed on separate disks or controllers so that no single equipment failure destroys an entire log group.

The loss of an entire log group is one of the most serious possible media failures because it can result in loss of data. The loss of a single member within a multiple-member log group is trivial and does not affect database operation, other than causing an alert to be published in the alert log.

Remember that redo logs heavily influence database performance because a commit cannot complete until the transaction information has been written to the logs. You must place your redo log files on your fastest disks served by your fastest controllers. If possible, do not place any other database files on the same disks as your redo log files. Because only one group is written to at a given time, there is no harm in having members from several groups on the same disk.

To avoid losing information that could be required to recover the database at some point, Oracle has an archive (ARCn) background process that archives redo log files when they become filled. However, it’s important to note not all Oracle Databases will have the archive process enabled. An instance with archiving enabled, is said to operate in ARCHIVELOG mode and an instance with archiving disabled is said to operate in NOARCHIVELOG mode.

You can determine with mode or if archiving is been used in your instance either by checking the value for the LOG_ARCHIVE_START parameter in your instance startup parameter file (pfile or spfile – This parameter is deprecated on version 10g), by issuing an SQL query to the v$database (“ARCHIVELOG” indicates archiving is enabled, and “NOARCHIVELOG” indicates that archiving is not enabled) or by issuing the SQL ARCHIVE LOG LIST command.

SQL> Select log_mode from v$database;

LOG_MODE

——————-

ARCHIVELOG  

SQL> archive log list 

Database log mode		Archive Mode

Automatic archival		Enabled

Archive destination		USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence	8

Next log sequence to archive	10

Current log sequence		10

Wait for next part, I will talk about Redo Generation and Recoverability: how, when and why.