Search My Oracle Blog

Custom Search

05 November, 2011

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.

.
.
.

5 comments:

Joel Garry said...

Hmmmmmm.... are you sure you can do media recovery without doing the whole database backup and losing any changes since the backup? I wonder if the 26040 is just slightly misleading, rather than showing a silent conversion, though the end result is the same.

In other words, proving something works by success is not commutative with proving something doesn't work with failure.

word: glorming

Hemant K Chitale said...

Joel,
See my next post http://hemantoracledba.blogspot.com/2011/11/tablespace-recovery-in-noarchivelog.html

The documentation shows a RECOVER DATABASE.
I do a RESTORE TABLESPACE and RECOVER TABLESPACE and this is possibly only if the Online Redo Logs have all the redo generated since the Backup Tablespace.


You may ask why I showed a RESTORE and RECOVER. I could have demonstrated the case with a query on V$MYSTAT for 'redo size'. But I deliberately wanted to show the ORA-26040 error to prove that Oracle finds the blocks as having been loaded in a NOLOGGING manner.

Hemant

Thomas said...

Hemanth, technically i will not agree with this test as we can not restore a data file and recover in NOARCHIVELOG mode. But, for the sake of showing the NOLOGGING option it is good enough.

Thanks
Thomas

Hemant K Chitale said...

Thomas,
My next post demonstrates RESTORE and RECOVERy of a Tablespace in NOARCHIVELOG mode. You can do so *if* you have all the redo still available in the Online Redo Logs.

Hemant

Hemant K Chitale said...

See the "Followup April 18, 2006 - 2pm Central time zone" entry at http://asktom.oracle.com/pls/asktom/f?p=100:11:3325444209131519::::P11_QUESTION_ID:485221567528

"create table as select - skips redo".
The exceptions are :
1. An Index would generate redo
2. FORCE LOGGING would generate redo even in NOARCHIVELOG mode


In NOARCHIVELOG mode, a CTAS is a Direct Path Insert and that is how it avoids generating redo for the table.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016