Alter Database Datafile Offline Drop And Alter Tablespace Offline

https://community.oracle.com/thread/4067842

Recent question on oracle community on user dropping a datafile using offline drop wanted to recall what happens during datafile offline ,tablespace offline and tablespace read only modes.

To start with  concepts of dropping, oracle do not allow dropping of a datafile in a tablespace, since the extents of segments in tablespace will be spread across datafiles ,so it is expected not to allow datafile drop.

If you want to exclude datafile  for any reason, in noarchivelog mode you have to use offline drop clause, the datafile will continue to exist in dictionary and controlfile , it just marks in controfile that this datafile not needed to verify SCN during DB startup.

In archivelog mode, oracle ignores drop , it will be similar to keeping datafile offline.

Now the testing of checkpoint_change# when datafile and tablespace are taken offline/readonly.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31312696 SYSTEM
/u01/ORCL/SYSAUX.dbf 31312696 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31312696 ONLINE
/u01/ORCL/tools01.dbf 31312696 ONLINE
/u01/ORCL/ora01.dbf 31312696 ONLINE
/u01/ORCL/xmldbts01.dbf 31312696 ONLINE
/u01/ORCL/test1.dbf 31315480 ONLINE
/u01/ORCL/test2.dbf 31315508 ONLINE

8 rows selected.

SYS@ORCL>alter system checkpoint;

System altered.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31316163 SYSTEM
/u01/ORCL/SYSAUX.dbf 31316163 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31316163 ONLINE
/u01/ORCL/tools01.dbf 31316163 ONLINE
/u01/ORCL/ora01.dbf 31316163 ONLINE
/u01/ORCL/xmldbts01.dbf 31316163 ONLINE
/u01/ORCL/test1.dbf 31316163 ONLINE
/u01/ORCL/test2.dbf 31316163 ONLINE

8 rows selected.

 

Now all datafiles have same checkpoint SCN.

I will take one datafile from tablespace TEST offline, here as DB in archivelog mode, even offline drop will behave same.

SYS@ORCL>alter database datafile '/u01/ORCL/test2.dbf' offline drop;

Database altered.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31316163 SYSTEM
/u01/ORCL/SYSAUX.dbf 31316163 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31316163 ONLINE
/u01/ORCL/tools01.dbf 31316163 ONLINE
/u01/ORCL/ora01.dbf 31316163 ONLINE
/u01/ORCL/xmldbts01.dbf 31316163 ONLINE
/u01/ORCL/test1.dbf 31316163 ONLINE
/u01/ORCL/test2.dbf 31316163 31316201 RECOVER

8 rows selected.

v$datafile_header will show the status, FYI v$datafile_header as the name of dynamic view suggests, it actually what is there in actual datafile header,v$datafile is
controlfile information.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,STATUS,RECOVER from v$datafile_header;

NAME CHECKPOINT_CHANGE# STATUS REC
-------------------------------------------------- ------------------ ------- ---
/u01/ORCL/system01.dbf 31316163 ONLINE NO
/u01/ORCL/SYSAUX.dbf 31316163 ONLINE NO
/u01/ORCL/UNDOTBS1.dbf 31316163 ONLINE NO
/u01/ORCL/tools01.dbf 31316163 ONLINE NO
/u01/ORCL/ora01.dbf 31316163 ONLINE NO
/u01/ORCL/xmldbts01.dbf 31316163 ONLINE NO
/u01/ORCL/test1.dbf 31316163 ONLINE NO
/u01/ORCL/test2.dbf 31316163 OFFLINE YES

8 rows selected.

Datafile offline will not perform any file/tabespace checkpoint.

v$datafile.last_change# is stop scn , it is the SCN number up to which there is redo information available for a datafile as per controlfile.

In order to bring the datafile back online, we need to recover it , but till what point?
We only need the redologs which have the checkpoint information of offlined datafile , we do not need any archivelogs after that.

In above case we only need the redolog which has the redo information at SCN 31316201 which is in sequence# 642.

Even if we lose the other redologs , we can bring the datifile online.

SYS@ORCL>select sequence#, FIRST_CHANGE#,to_Char(NEXT_CHANGE#) from v$archived_log;

SEQUENCE# FIRST_CHANGE# TO_CHAR(NEXT_CHANGE#)
---------- ------------- ----------------------------------------
 641 31303644 31312696
 642 31312696 31316487
 643 31316487 31316490
 644 31316490 31316493
 645 31316493 31316496
 646 31316496 31316499

I am only recovering until last_change# scn and bring the datafile online.

RMAN> recover datafile 8 until scn 31316202;

Starting recover at 05-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK

starting media recovery

archived log for thread 1 with sequence 642 is already on disk as file 
/u01/ORCL/flash_recovery_area/ORCL/archivelog/2017_08_05/o1_mf_1_642_drcl59k8_.arc
archived log file name=/u01/ORCL/flash_recovery_area/ORCL/archivelog/2017_08_05/o1_mf_1_642_drcl59k8_.arc thread=1 sequence=642
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-AUG-17

 

SYS@ORCL>alter database datafile '/u01/ORCL/test2.dbf' online;

Database altered.

Last_change# will be set to null once it is back online.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31316499 SYSTEM
/u01/ORCL/SYSAUX.dbf 31316499 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31316499 ONLINE
/u01/ORCL/tools01.dbf 31316499 ONLINE
/u01/ORCL/ora01.dbf 31316499 ONLINE
/u01/ORCL/xmldbts01.dbf 31316499 ONLINE
/u01/ORCL/test1.dbf 31316499 ONLINE
/u01/ORCL/test2.dbf 31316646 ONLINE

8 rows selected.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,STATUS,RECOVER from v$datafile_header;

NAME CHECKPOINT_CHANGE# STATUS REC
-------------------------------------------------- ------------------ ------- ---
/u01/ORCL/system01.dbf 31316499 ONLINE NO
/u01/ORCL/SYSAUX.dbf 31316499 ONLINE NO
/u01/ORCL/UNDOTBS1.dbf 31316499 ONLINE NO
/u01/ORCL/tools01.dbf 31316499 ONLINE NO
/u01/ORCL/ora01.dbf 31316499 ONLINE NO
/u01/ORCL/xmldbts01.dbf 31316499 ONLINE NO
/u01/ORCL/test1.dbf 31316499 ONLINE NO
/u01/ORCL/test2.dbf 31316646 ONLINE NO

8 rows selected.

Now I will put entire tablespace in offline mode, tablespace offline will perform checkpoint on the respective datafiles(check last_change# and checkpoint_change#) , so no recovery needed when you bring the tablespace online.

SYS@ORCL>alter tablespace test offline;

Tablespace altered.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31316499 SYSTEM
/u01/ORCL/SYSAUX.dbf 31316499 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31316499 ONLINE
/u01/ORCL/tools01.dbf 31316499 ONLINE
/u01/ORCL/ora01.dbf 31316499 ONLINE
/u01/ORCL/xmldbts01.dbf 31316499 ONLINE
/u01/ORCL/test1.dbf 31316705 31316705 OFFLINE
/u01/ORCL/test2.dbf 31316705 31316705 OFFLINE

8 rows selected.

SYS@ORCL>alter tablespace test online;

Tablespace altered.

Same is the case when tablespace is kept in read only mode.

SYS@ORCL>alter tablespace test read only;

Tablespace altered.

SYS@ORCL>select name,CHECKPOINT_CHANGE#,LAST_CHANGE#,status from v$datafile;

NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/ORCL/system01.dbf 31316499 SYSTEM
/u01/ORCL/SYSAUX.dbf 31316499 ONLINE
/u01/ORCL/UNDOTBS1.dbf 31316499 ONLINE
/u01/ORCL/tools01.dbf 31316499 ONLINE
/u01/ORCL/ora01.dbf 31316499 ONLINE
/u01/ORCL/xmldbts01.dbf 31316499 ONLINE
/u01/ORCL/test1.dbf 31316936 31316936 ONLINE
/u01/ORCL/test2.dbf 31316936 31316936 ONLINE

8 rows selected.

SYS@ORCL>alter tablespace test read write;

Tablespace altered.

To summarize

  1. Datafile offline drop will not remove the datafile from dictionary/controlfile, it will only mark in controlfile that scn comparison isn’t needed when starting DB.
  2. Datafile offline will not perform checkpoint, so it needs recovery to bring it back online. We only need the sequence# which has the redo information until last_change# to bring the datafile online.
  3. Tablespace offline or read only mode will perform the checkpoint and so no recovery is needed to bring the tablespace online/read write mode.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s