CTAS in a NOARCHIVELOG database is a NOLOGGING operation

Normally, a CTAS (CREATE TABLE ... AS SELECT ....) operation generates redo and logging. However, if the database is in NOARCHIVELOG mode, Oracle silently converts it to a NOLOGGING operation.

This also means that a CTAS in a Test database (that is NOARCHIVELOG mode) will run faster than a CTAS in a Production database (that is ARCHIVELOG mode) , allowing for differences in server and I/O performance.

Here is a demonstration :

I first create a Tablespace and run a Backup, with the database in NOARCHIVELOG mode :

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL>
SQL> create tablespace CTAS_NOARCH ;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace_name = 'CTAS_NOARCH';

FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf

SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             213911176 bytes
Database Buffers          314572800 bytes
Redo Buffers                5840896 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     295
Current log sequence           297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace

The RMAN backup is executed :

RMAN> backup tablespace CTAS_NOARCH;

Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-NOV-11

Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766369695_7c84s6ks_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11

RMAN>

I then run a CTAS in the Tablespace :

SQL> alter database open;

Database altered.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  5479486

SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 00:31

SQL>
SQL> -- run CTAS
SQL> create table CTAS_NOARCH_TBL tablespace CTAS_NOARCH as select * from dba_objects;

Table created.

SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2  from v$datafile, dba_data_files
3  where file#=file_id
4  and tablespace_name = 'CTAS_NOARCH'
5  /

UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
                  0

SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace CTAS_NOARCH offline;

Tablespace altered.

SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile

SQL>

I remove the datafile and RESTORE and RECOVER the tablespace :

[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf'? yes
[root@localhost ~]#


RMAN> restore tablespace CTAS_NOARCH;

Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00017 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c84n1w9_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T003023_7c84s28w_.bkp tag=TAG20111105T003023
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 05-NOV-11

RMAN> recover tablespace CTAS_NOARCH;

Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 05-NOV-11

RMAN> sql 'alter tablespace CTAS_NOARCH online';

sql statement: alter tablespace CTAS_NOARCH online

RMAN>

These are the alert.log messages :

Sat Nov 05 00:31:45 2011
alter tablespace CTAS_NOARCH offline
Completed: alter tablespace CTAS_NOARCH offline
Sat Nov 05 00:34:40 2011
Full restore complete of datafile 17 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf.  Elapsed time: 0:00:08
 checkpoint is 5479138
Sat Nov 05 00:34:41 2011
Checker run found 3 new persistent data failures
Sat Nov 05 00:34:59 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace CTAS_NOARCH
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace CTAS_NOARCH
Sat Nov 05 00:35:13 2011
alter tablespace CTAS_NOARCH online

I then attempt to access the table :

SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_NOARCH_TBL;
select count(*) from CTAS_NOARCH_TBL
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 17, block # 131)
ORA-01110: data file 17: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_ctas_noa_7c850q6y_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


SQL>
SQL>
SQL>
SQL> spool off
SQL> !oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//*        redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.

SQL>

So, Oracle now finds that the blocks for the table have been loaded via a NOLOGGING operation. I did not create the tablespace explicitly as NOLOGGING, I did not create the table as NOLOGGING. Yet, the CTAS was (silently) executed as a NOLOGGING operation.

.
.
.