Transactions are the lifeblood of a relational database and Oracle is no exception. Key to this concept is the ability to replay those transactions should the need arise to recover ‘lost’ data due to a database crash, or worse, corruption requiring a restore and recovery. Yes, recording the transactions does take time but that time is usually minimal when compared to the overall transaction time. Some, however, feel the need to suppress such logging to ‘speed things up’ and Oracle allows that to happen in certain cases. Disabling logging ensures that affected transactions will not be recoverable, putting recovery at risk. Let’s look at a scenario (that should NEVER happen in production) and see what can be done to discover unrecoverable data files and how to fix such an issue.
Redo generation can be ‘successfully’ suppressed for a small set of statements, including CREATE TABLE AS SELECT, INSERT INTO … SELECT and CREATE INDEX. When such commands are executed the associated redo statements will not be found in the redo logs (and, later, the archive logs) so they cannot be ‘replayed’ during recovery and won’t appear in the logs at standby sites, which makes the standby ‘out of synch’ with the primary (not a good situation since it may be necessary at some point to activate the standby database as production and it won’t match the primary). Executing an example may help illustrate this; a table is created as we begin our trip down Nologging Lane:
SQL> create table t_rex tablespace yerg as
2 select rownum as t_id, 'Jnorg floppneqt aspleezius vung' as t_col
3 from dual connect by level <= 1e5;
Table created.
SQL>
Let’s backup that datafile now, before we go any further:
C:Users>rman target sys/%%%%%%%%%%%
Recovery Manager: Release 12.1.0.2.0 - Production on Thu Apr 13 10:56:18 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: FNORG (DBID=3119604680)
RMAN> @fnorg_yerg_hot_backup_rman.rmn
RMAN> run {
2> allocate channel d1 type disk;
3> backup
4> tablespace yerg
5> format 'c:backupsfnorgfnorg_yerg_hot_%t_%s_%p';
6> }
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=129 device type=DISK
Starting backup at 13-APR-17
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00007 name=C:APPDNS-DDFORADATAFNORGYERG01.DBF
channel d1: starting piece 1 at 13-APR-17
channel d1: finished piece 1 at 13-APR-17
piece handle=C:BACKUPSFNORGFNORG_YERG_HOT_941194592_3_1 tag=TAG20170413T105631 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 13-APR-17
released channel: d1
RMAN>
RMAN> **end-of-file**
RMAN>
All of our changes have now been preserved, although since the table was created LOGGING recovery would have restored it without the current backup. We verify that with the following query:
SQL> select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
2 from v$datafile where file#=(select file_id from dba_data_files where tablespace_name = 'YERG');
FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
7
SQL>
It’s time to throw a monkey-wrench into the works; let’s create an index on table T_REX and do it NOLOGGING:
SQL> create index t_rex_idx on t_rex(t_id) tablespace yerg nologging;
Index created.
SQL>
Let’s run that same unrecoverable query again:
SQL> select file#,to_char(UNRECOVERABLE_TIME,'yyyy-mm-dd:hh24:mi:ss')
2 from v$datafile where file#=(select file_id from dba_data_files where tablespace_name = 'YERG');
FILE# TO_CHAR(UNRECOVERAB
---------- -------------------
7 2017-04-13:11:10:52
SQL>
For even more proof, we ask RMAN to report unrecoverable items:
RMAN> report unrecoverable;
using target database control file instead of recovery catalog
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
7 full or incremental C:APPORADATAFNORGYERG01.DBF
RMAN>
It’s a good thing we checked on this; it’s not a usual step a DBA takes in his/her day-to-day duties but it might be a good check to run on occasion just to verify recovery would succeed for a given database or tablespace. If this is left in its current state a restore and recover of the tablespace from the current backup will not restore the index (which should be obvious given the NOLOGGING creation of it). Any backup of the database or tablespace prior to the NOLOGGING changes will essentially be useless. [Such changes should be limited to the Development database and developers who perform NOLOGGING operations should discuss this with the DBA prior to implementing such a strategy so that objects and/or data can be recovered if need be. On the other hand, the developers should have such tasks scripted so they can be repeated at will, which may make the life of the DBA easier.] Thinking the ‘unthinkable’ the developer’s code was migrated to production ‘as-is’, leaving the NOLOGGING directive intact. Luckily, she noticed this and notified the DBA so a current tablespace backup could be taken:
RMAN> @fnorg_yerg_hot_backup_rman.rmn
RMAN> run {
2> allocate channel d1 type disk;
3> backup
4> tablespace yerg
5> format 'c:backupsfnorgfnorg_yerg_hot_%t_%s_%p';
6> }
allocated channel: d1
channel d1: SID=246 device type=DISK
Starting backup at 13-APR-17
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00007 name=C:APPDNS-DDFORADATAFNORGYERG01.DBF
channel d1: starting piece 1 at 13-APR-17
channel d1: finished piece 1 at 13-APR-17
piece handle=C:BACKUPSFNORGFNORG_YERG_HOT_941197231_4_1 tag=TAG20170413T114031 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 13-APR-17
released channel: d1
RMAN>
RMAN> **end-of-file**
Checking again for unrecoverable objects we get a different report:
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
RMAN>
NOLOGGING operations are intentional, as the NOLOGGING keyword needs to be added to any DDL statement executed in the database (there is no user-accessible setting that will put the entire database in NOLOGGING mode). It should not be left to the DBA to discover such actions so corrective measures can be taken and it certainly should not be acceptable practice to execute DDL in production databases NOLOGGING.
Yes, sometimes the unthinkable happens, but it should be a rare (or, ideally, non-existent) occurrence in a properly managed production environment. Being aware that such possibilities exist puts the DBA in a better position to ensure the database is properly backed up so recovery will not encounter issues should that need arise. Forewarned is forearmed, it is said, and that’s very true in situations like this; since the primary responsibility of the DBA is backup and recovery above all else it might be a good idea to add a check for unrecoverable objects and datafiles, just to be safe. You never know when the unimaginable may happen.
