Getting To The Bottom Of NOLOGGING, NOARCHIVELOG and Standby Databases

Mark Rittman

• 9 min read

One of the customers I work with is looking to us for advice on how to set their data warehousing database up. They're looking to use the Enterprise Edition of the latest Oracle version (10.1.0.4) and are planning on using Oracle Warehouse Builder 10g as the ETL tool. Each environment (dev, test, prod) will have two database instances, one to hold the OWB Design repository (with an 8k block size) and one to hold the target data warehouse and the OWB Runtime Repository (with a 16K block size). Setting up the design repository instance isn't an issue, it's just a regular database set up using the default database template, but the target data warehouse instance is a bit more interesting as we're looking to minimize the amount of redo log generated during the warehouse initial build and then refresh.

Most people are aware that you can specify a NOLOGGING clause when creating tables, indexes, partitions and tablespaces which under certain circumstances causes redo generation to be switched off when the object is loaded. You're also probably aware that the database itself can be placed in NOARCHIVELOG mode, which disables the process of redo log archiving which can otherwise slow down a bulk load into a warehouse. However, what's often not clear is which of these options should be used and why you would use one rather than the other, and I therefore thought it worthwhile going back over the documentation and trying to set out what the optimal set of choices are. As usual, if you're reading this and you spot something that I've got wrong, or can add anything that makes the process clearer, add a comment at the end and I'll update the posting.

To put this in context, the client runs a number of other Oracle databases, almost exclusively as the backend for their transactional systems, and the general rule is that these databases are run in ARCHIVELOG mode, incremental hot backups are taken during the week using RMAN followed by full hot backups at the weekend, and the archived redo logs are stored off site and also used to refresh their DR databases. Given a preference, they would rather run the new data warehouse database in ARCHIVELOG mode, so that it can fit in with their existing arrangements, but they are willing to perform the warehouse load and refresh in NOLOGGING mode as long as we give them some downtime after the load to do a full, cold backup of the warehouse.

What we would like to do is to somehow disable redo log generation for initial loads into our dimension tables, and for initial and then subsequent loads into the fact tables. The rationale for this is that the initial dimension load, and all loads into the fact table, load a very large amount of data and we can speed up this process if we don't have to write redo information into the redo log files - in other words, we dramatically reduce the amount of disk I/O we need to perform to load a given object. If our database is running in ARCHIVELOG mode, we get an additional benefit in that we don't then need to archive off these redo log files, again reducing the time to perform the load as we don't have to wait around for the ARCn process to archive the filled-up redo log files. The downside of doing this though is that these data loads are unrecoverable - that is, if the instance crashes or we have a media (disk) failure, we wouldn't be able to recoverer these objects. Whilst that isn't a bit deal for the objects we're working with - we can just reload them from the source files - it means that we're going to have problems with any other objects that are also in that datafile. What this means in practice is that we have to do a full, cold backup of the datafiles that contain these objects before we can consider the database "recoverable".

I mentioned earlier on that you can specify a NOLOGGING clause when creating tables, indexes, partitions and tablespaces. Ignoring tablespaces for the moment, what this means in practice is that you can create the table (for example) using the NOLOGGING clause:

CREATE TABLE "PRODUCT"
(
"CLASS_DSC" VARCHAR2(50),
"CLASS_ID" NUMBER,
"FAMILY_DSC" VARCHAR2(50),
"FAMILY_ID" NUMBER,
"ITEM_BUYER" VARCHAR2(50),
"ITEM_DSC" VARCHAR2(50),
"ITEM_ID" NUMBER,
"ITEM_MARKETING_MANAGER" VARCHAR2(50),
"TOTAL_PRODUCT_DSC" VARCHAR2(50),
"TOTAL_PRODUCT_ID" NUMBER)
NOLOGGING
;

Then, you can carry out bulk loads into this table in NOLOGGING mode. For example:

INSERT
/*+ APPEND */
INTO
"PRODUCT"
("CLASS_DSC",
"CLASS_ID",
"FAMILY_DSC",
"FAMILY_ID",
"ITEM_BUYER",
"ITEM_DSC",
"ITEM_ID",
"ITEM_MARKETING_MANAGER",
"TOTAL_PRODUCT_DSC",
"TOTAL_PRODUCT_ID")
(SELECT
"PRODUCT_DIM"."CLASS_DSC" "CLASS_DSC",
"PRODUCT_DIM"."CLASS_ID" "CLASS_ID",
"PRODUCT_DIM"."FAMILY_DSC" "FAMILY_DSC",
"PRODUCT_DIM"."FAMILY_ID" "FAMILY_ID",
"PRODUCT_DIM"."ITEM_BUYER" "ITEM_BUYER",
"PRODUCT_DIM"."ITEM_DSC" "ITEM_DSC",
"PRODUCT_DIM"."ITEM_ID" "ITEM_ID",
"PRODUCT_DIM"."ITEM_MARKETING_MANAGER" "ITEM_MARKETING_MANAGER",
"PRODUCT_DIM"."TOTAL_PRODUCT_DSC" "TOTAL_PRODUCT_DSC",
"PRODUCT_DIM"."TOTAL_PRODUCT_ID" "TOTAL_PRODUCT_ID"
FROM "PRODUCT_DIM" "PRODUCT_DIM"
);

One point to note here is that the NOLOGGING clause doesn't mean that every DML operations on the table works in NOLOGGING mode - it's only bulk operations like INSERT /*+ APPEND */ and Direct Loader (SQL*Loader) that take advantage of it. Regular INSERTs, DELETEs and UPDATEs will generate normal redo, even when you specify NOLOGGING. This is a popular misconception as logic would suggest that the NOLOGGING table creation clause would suppress all redo log generation, not just direct path loads, but all the NOLOGGING clause does is specify NOLOGGING for the table creation process itself (in case you're using CREATE TABLE ... AS SELECT) and for any subsequent direct path insertions.

Another misconception is that NOLOGGING suppresses all redo, even when using direct path loads. What actually happens is that NOLOGGING operations generate minimal redo, not no redo whatsoever, with this minimal redo generated so that the data dictionary itself is preserved. It's only a relatively small amount, but it's worth noting that some will be produced, whatever steps you take.

With indexes and partition creation, it's a similar story - you can specify NOLOGGING in the object creation clause, and then certain operations (in the case of indexes, creation and ALTERs (rebuilds)) can take advantage of NOLOGGING.

So what about tablespaces? What do you get when you specify the NOLOGGING clause when creating a tablespace?

CREATE TABLESPACE dims
NOLOGGING
DATAFILE 'c:\oradata\dims01.dbf' SIZE 50M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

What this actually does is ensure that the default LOGGING|NOLOGGING attribute for objects created in this tablespace is set to NOLOGGING. You can still subsequently create a table as LOGGING in a tablespace you define as NOLOGGING, all the NOLOGGING attribute does for a tablespace is set the default for objects later created in the tablespace.

Once you've bulk loaded your data, created your table using CREATE TABLE ... AS SELECT or rebuilt your index, the important next step is to perform a baseline backup of the datafiles that contain your objects; once you've done this, and until such time as you carry out any other unrecoverable DML, you can then recover your objects like any other. This baseline backup can be a hot backup (as long as you're in ARCHIVELOG mode, which in our case we are) and this backup then ensures that we have a baseline copy of the object within our backup set if we then need to perform a full recovery.

So where does the ARCHIVELOG setting for databases come into things?

The general advice for setting up warehouse target databases is to put the database into NOARCHIVELOG mode. NOARCHIVELOG suppresses the archiving of redo log files and improves the performance of data loads as you don't have the ARCn processes running around in the background and holding things up as they copy the redo log files off into your archive locations. The obvious cost of this is that you will only have limited options when recovering your instance - basically if you've overwritten your redo log files and not had the contents archived off, and you have a media failure, you can only restore your instance back to the last full cold backup you took (as the redo required to roll forward from this point has probably been overwritten, and you didn't archive it off beforehand). However, for data warehouses, this isn't usually an issue, as you can just reload from the source files or databases, and the benefits of your data load being quicker are usually greater than the (slim) chance that you'll need to perform a media recovery.

What we have here then are two separate concepts - ARCHIVELOG and NOARCHIVELOG mode, which determines whether redo logs are archived off when they fill up, or just overwritten and the redo then lost, and LOGGING | NOLOGGING, where we either allow all redo to be generated as normal, or in certain circumstances (the important ones, when we need to load lots of data) suppress the majority of redo, at the cost of our operation being unrecoverable and subsequently needing to reload the objects from the source files. These two concepts interact in two ways:

  • If we're looking to suppress as much redo as possible, chances are we'd like to suppress archiving as well (and we might as well, as the objects we're loading are unrecoverable anyway), and
  • If you specify NOARCHIVELOG mode for a database, all indexes, tables and partitions are automatically set to NOLOGGING, and therefore all INSERT /*+ APPEND */ and SQL*Loader direct load operations will automatically generate minimal redo.

However, in our instance, the DBAs want to keep the database in ARCHIVELOG mode, so that they can manage the database in the same way as the OLTP databases and ship the archivelogs off to their DR site to maintain their standby databases. As I was reading through the various articles and questions on Asktom, I noticed this answer which suggested that there could be some complications when using NOLOGGING operations when you later on wanted to ship the archivelogs to a standby database:

"when using a standby database for disaster recovery -- you cannot perform nologging operations without performing additional, manual work (in fact, in 9i,
we can set a flag that says "ignore the request for nologging -- log it anyway" on the database to avoid this issue alltogether).

Please read the standby database documention -- it does explain this in some detail:"

The answer then points through to the Oracle 8.1.6 documentation for Standby Database which talks about the problems you'll get when you try and apply the (minimal) redo logs that were written for our unrecoverable transactions to your standby database:

"In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the redo log file. Even though the user specifies the NOLOGGING clause, a redo log record is still written to the redo log. However, when the redo log file is transferred to the standby site and applied to the standby database, a portion of the datafile is unusable and marked as being unrecoverable. When you either activate the standby database, or open the standby database with the read-only option, and attempt to read the range of blocks that are marked as "UNRECOVERABLE," you will see error messages similar to the following: 

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/vobs/oracle/dbs/stdby/tbs_1.f'
ORA-26040: Data block was loaded using the NOLOGGING option"

That doesn't sound good, and certainly I can't imagine our DBA chuckling to himself when at 2.30 in the morning he's called to recover our database and that error message comes up. It's not as bad as it seems actually, as the article then goes on to explain how you recover from this situation, but the point that Tom was trying to make is that NOLOGGING isn't something you should do and then tell no-one about - you should understand what it does, talk it through with the DBA and have a plan in mind for when you do eventually need to perform a recovery and work around the fact that you may have some objects that are unrecoverable - if you've done your baseline backup after your bulk load, it shouldn't affect you, but if your data centre gets hit by a meteorite then it may well affect the process of starting up your standby database. Given that Standby Database is now Data Guard with 10g, I checked the 9i Data Guard documentation (the 10g documentation seems to be offline at the moment) and found this comment, which suggests that in fact we might want to actually reconsider our use of NOLOGGING operations:

"For logical standby databases, when SQL apply operations encounter a redo log record for an operation performed with the NOLOGGING clause, it skips over the record and continues applying changes from later records. Later, if an attempt is made to access one of the records that were updated with NOLOGGING in effect, the following error is returned: ORA-01403 no data found

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Section 9.1.6.

Note: In general, use of the NOLOGGING clause is not recommended. Optionally, if you know in advance that operations using the NOLOGGING clause will be performed on certain tables in the primary database, you might want to prevent the application of SQL statements associated with these tables to the logical standby database by using the DBMS_LOGSTDBY.SKIP procedure."

So, in summary, in our situation where we want to minimise the amount of redo generated for our dimension and fact table loads, but we want to work in ARCHIVELOG mode and ship our logs to a standby database, I'd say the best approach would be to:

  • Keep the database in ARCHIVELOG mode
  • Create separate tablespaces, with their own datafiles, for the dimensions and facts that we are going to bulk load, and create these tablespaces as NOLOGGING
  • Create the tables to implement these dimensions and facts as normal (i.e. without the NOLOGGING clause, as this is already the default for their tablespaces)
  • Use INSERT /*+ APPEND */ to bulk load our data into these tables
  • Once the loads have completed, do a baseline hot backup of the datafiles that relate to the tables/tablespaces we've just loaded
  • At this point, our main database is completely recoverable.
  • If we need to recover the standby database, be prepared for the fact that some manual work might be needed to activate the standby database, and in fact be prepared for the DBA to actually stop us from using NOLOGGING through the use of the DBMS_LOGSTDBY.SKIP procedure.

Any comments?

Sign up for more like this.

Enter your email
Subscribe