Backup & Recovery

Offline Datafiles and Offline tablespaces

User Rating:

/ 0

Details: Parent Category: Articles; Created on Saturday, 06 June 2009 20:38; Last Updated on Monday, 12 March 2012 13:54; Published on Saturday, 06 June 2009 20:38; Written by Guy Lambregts; Hits: 19735

Since version I don' t know an Oracle Database can run in archivelog mode either in non archivelog mode. Archivelog mode gives us the opportunity to online backup our databases as well archivelog mode is a must in every zero data loss scenario. More about that elsewhere and later. Right now I will highlight some functionality with regard to offlining datafiles, tablespaces and I will highlight the differences between archivelog mode and non archivelog mode.

Taking tablespaces & datafiles offline makes them unavailable for the online database operations (sounds logic). Whereas it does not take a hero to use these powerfull options you should be ware of what you are doing.

In a nutshell and when your database runs in non archivelog mode

1. You cannot offline datafiles when the database is open

2. You can only offline normal a tablespace (but not the system tablespaces).

Since offline normal of a tablespace involves checkpointing of the datafiles of that tablespace, no recovery will be needed to online the tablespace in the future.

3. When your database is not open, you can offline drop some datafiles, which can be interesting, necessary when you need to do a partial restore of a database (may I assume you are aware that a system tablespace is always necessary ?) The offline drop means you are asking the database - the controlfile - to continue without that datafile.

In a nutshell and when your database runs in archivelog mode In addition to the above points 1 and 2

4. You can offline datafiles when the database is open. Please be aware the datafiles will have the status recover in v$datafile. In order to have them online again all the (archived) redo generated between the offline and online of the datafile(s) concerned must be available. BE AWARE OF THIS BEFORE OFFLINING A DATAFILE !!!

5. Despite the fact that offline normal is recommended whenever possible you can offline immediate either offline temporary tablespaces. Offline immediate will force all the datafiles towards status recover (v$datafile), they are not checkpointed whereas an offline temporary will checkpoint those datafiles which are available at the moment of offlining the tablespace. In order to have them online again all the (archived) redo generated between the offline and online of the tablespace(s) concerned must be available. BE AWARE OF THIS BEFORE OFFLINING A TABLESPACE !!!

6. In stead of point 4 when your database is not open, you can offline some datafiles, which can be interesting, necessary when you need to do a partial restore of a database (may I assume you are aware that a system tablespace is always necessary ?)

It' s time for some gym right now

Here I show you my database runs in archivelog mode, further I will list the tablespaces and datafiles with their status.

SQL> select name,log_mode from v$database;

NAME LOG_MODE

--------------- ------------

UTF8 ARCHIVELOG

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

UNDOTBS2 ONLINE USERS_16K ONLINE

CW4ORA_DATA ONLINE

CW4ORA_INDX ONLINE

USERS2 ONLINE

USER_LOB ONLINE

10 rows selected.

SQL> select file_name,status from dba_data_files;

C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF AVAILABLE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF AVAILABLE

9 rows selected.

SQL> select name,status from v$datafile;

C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSTEM_1DT8JGN2_.DBF SYSTEM C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_16_1M08DN7D_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_SYSAUX_1NBJ85GR_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_UNDOTBS2_1JOTM2S4_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_CW4ORA_D_1OH2VGQP_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_CW4ORA_I_1OH2XWW4_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS2_1V1OPZWO_.DBF ONLINE C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USER_LOB_1WKM1WSD_.DBF ONLINE

9 rows selected.

Exercise 1 : Let us offline normal the users tablespace

SQL> alter tablespace users offline;

Tablespace altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1249968 bytes

Variable Size 427822416 bytes

Database Buffers 92274688 bytes

Redo Buffers 2940928 bytes

Database mounted.

Database opened.

SQL> alter tablespace users online;

Tablespace altered.

Since offline normal of a tablespace involves checkpointing of the datafiles of that tablespace, no recovery will be needed to online the tablespace in the future. Exercise 2 : Let us offline immediate the users tablespace and have some dirty blocks from that tablespace in the buffer cache

SQL> create table my_utf.t_ofline tablespace users as select * from my_utf.wip_wo where rownum < 10000;

Table created.

SQL> alter tablespace users offline immediate;

Tablespace altered.

SQL> shutdown abort; — a crash occurs

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1249968 bytes

Variable Size 427822416 bytes

Database Buffers 92274688 bytes

Redo Buffers 2940928 bytes

Database mounted.

Database opened.

A shutdown abort always requires recovery the next startup. Since I did not delete any archive, online redologs ... this is what the alert file is showing me

Completed redo scan 10001 redo blocks read,101 data blocks need recovery

Tue Feb 07 17:26:34 2006

Started redo application at Thread 1: logseq 4406, block 182052

Tue Feb 07 17:26:35 2006

Recovery of Online Redo Log: Thread 1 Group 5 Seq 4406 Reading mem 0 Mem# 0 errs 0: C:\ORACLE\ORADATA\UTF8\BRUSSELS\ONLINELOG\O1_MF_5_1NW3T6PR_.LOG Mem# 1 errs 0: C:\ORACLE\FLASH_RECOVERY_AREA\BRUSSELS\ONLINELOG\O1_MF_5_1NW3TPTB_.LOG Tue Feb 07 17:26:37 2006 Completed redo application

Tue Feb 07 17:26:37 2006 Completed crash recovery at Thread 1: logseq 4406, block 192053, scn 10601680 101 data blocks read, 101 data blocks written, 10001 redo blocks read

Let us bring the users tablespace online again.

SQL> alter tablespace users online;

alter tablespace users online * ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: 'C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF'

Offline immediate will force all the datafiles towards status recover (v$datafile), they are not checkpointed whereas an offline temporary will checkpoint those datafiles which are available at the moment of offlining the tablespace. In order to have them online again all the (archived) redo generated between the offline and online of the tablespace(s) concerned must be available.

SQL> recover datafile 4; >>>> THAT' S THE DATAFILE IN THE USERS TABLESPACE Media recovery complete.

SQL> select status from v$datafile;

STATUS

-------

SYSTEM

ONLINE

OFFLINE >>>> THAT' S THE DATAFILE IN THE USERS TABLESPACE

ONLINE

9 rows selected.

SQL> select status from dba_tablespaces;

STATUS

---------

ONLINE

OFFLINE >>>> THAT' S THE USERS TABLESPACE

ONLINE

10 rows selected.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select status from dba_tablespaces; -- the users tablespace will be online

STATUS

---------

ONLINE

10 rows selected.

And what is the alertfile us telling ?

alter tablespace users online

Tue Feb 07 17:39:50 2006

ORA-1113 signalled during: alter tablespace users online...

Tue Feb 07 17:40:03 2006

ALTER DATABASE RECOVER datafile 4

Tue Feb 07 17:40:04 2006

Media Recovery Start

Tue Feb 07 17:40:04 2006

Tue Feb 07 17:42:02 2006

alter tablespace users online

Tue Feb 07 17:42:03 2006

Completed: alter tablespace users online

Exercise 3 : Let us offline a datafile

SQL> create table my_utf.t_offline tablespace users as select * from my_utf.wip_wo where rownum < 10001;

Table created.

SQL> select file#,status,checkpoint_change# from v$datafile;

FILE# STATUS CHECKPOINT_CHANGE#

---------- ------- ------------------

1 SYSTEM 10605504

2 ONLINE 10605504

3 ONLINE 10605504

4 ONLINE 10605504

5 ONLINE 10605504

6 ONLINE 10605504

7 ONLINE 10605504

8 ONLINE 10605504

9 ONLINE 10605504

9 rows selected.

SQL> alter database datafile 4 offline; -- Please note datafile 4 is the one from the users tablespace (see above)

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> select file#,status,checkpoint_change# from v$datafile;

FILE# STATUS CHECKPOINT_CHANGE#

---------- ------- ------------------

1 SYSTEM 10606294

2 ONLINE 10606294

3 ONLINE 10606294

4 RECOVER 10605504 >> Haven' t I told you : status recover and not checkpointed

5 ONLINE 10606294

6 ONLINE 10606294

7 ONLINE 10606294

8 ONLINE 10606294

9 ONLINE 10606294

9 rows selected.

SQL> alter database datafile 4 online;

alter database datafile 4 online

* ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: 'C:\ORACLE\ORADATA\UTF8\BRUSSELS\DATAFILE\O1_MF_USERS_1DT8LVYG_.DBF'

SQL> recover datafile 4;

Media recovery complete.

SQL> alter database datafile 4 online;

Database altered.

Please note I listed the interesting column checkpoint_change# available in v$database,v$datafile,v$datafile_header, I waited until the end with it, to renumerate the one who has read this entire page. Now it is up to you to test. (start with test databases !) Be prudent with offlining tablespaces and datafiles in production databases. !