11 Replies Latest reply: Jul 21, 2013 9:42 AM by 1002959 RSS

    taking the datafile offline when the database is in NOARCHIVELOG mode

    1002959

      My question is when the database is in No Archive log mode i'm not able to take the datafile offline.

      When i tried in my computer i have noticed fallowing.

       

      case1:

      SYS>alter database datafile 5 offline;

       

      ERROR at line1;

      ORA-01145:offline immediate disallowed unless media recovery enabled.

       

      case 2:

      SYS>alter database datafile 5 offline immediate;

      ERROR at line 1;

      ORA-00933:SQL command not properly ended

       

      case3:

      I have tried the command alter database datafile 6 offline drop; (in NOARCHIVELOG mode) and it is showing the same effect as alter database datafile 6 offline; ( in ARCHIVELOG mode).

      *In the NOARCHIVELOG mode are we really dropping the datafile to take the datafile offline? Will you please tell me the effect of keyword drop.

        • 1. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
          sb92075

          If you really cared about the data in the DB, then the DB would be running with ARCHIVE mode enabled.

           

          Oracle wants to make sure that you REALLY, really want to remove the datafile permanently by specifying DROP.

          • 3. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
            Justin_Mungal

            Oracle is protecting you. Please study ARCHIVELOG and NOARCHIVELOG mode.

             

            When you take the data file offline in NOARCHIVELOG mode:

            1)The file is taken offline

            2)Eventually the SCN at which you took the data file offline will no longer be in the online redo logs

            3)At that point you'd have to do media recovery to bring the file online again (ie. restore the database)

             

            So you can see why you shouldn't be doing this.

             

            The OFFLINE DROP command does not actually remove the data file. It removes the details from the file from the control file and takes the data file offline. It can be used for recovery purposes or when you're actually planning on dropping data files.

            • 4. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
              1002959

              Thank you sir. Now i have cleared my doubt

              • 6. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                JohnWatson

                You've marked your question answered - was that a mistake?

                The answer is that you cannot directly take a datafile offline in noarchivelog mode. You must take the tablespace offline:

                 

                orcl112> select name,status from v$datafile;

                 

                NAME                                               STATUS

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

                C:\APP\ORACLE\ORADATA\ORCL112\SYSTEM01.DBF         SYSTEM

                C:\APP\ORACLE\ORADATA\ORCL112\SYSAUX01.DBF         ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\UNDOTBS01.DBF        ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\USERS01.DBF          ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\EXAMPLE01.DBF        ONLINE

                 

                orcl112> alter database datafile 'C:\APP\ORACLE\ORADATA\ORCL112\EXAMPLE01.DBF' offline;

                alter database datafile 'C:\APP\ORACLE\ORADATA\ORCL112\EXAMPLE01.DBF' offline

                *

                ERROR at line 1:

                ORA-01145: offline immediate disallowed unless media recovery enabled

                 

                 

                orcl112> alter tablespace example offline;

                 

                Tablespace altered.

                 

                orcl112> select name,status from v$datafile;

                 

                NAME                                               STATUS

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

                C:\APP\ORACLE\ORADATA\ORCL112\SYSTEM01.DBF         SYSTEM

                C:\APP\ORACLE\ORADATA\ORCL112\SYSAUX01.DBF         ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\UNDOTBS01.DBF        ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\USERS01.DBF          ONLINE

                C:\APP\ORACLE\ORADATA\ORCL112\EXAMPLE01.DBF        OFFLINE

                 

                orcl112>

                • 7. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                  Justin_Mungal

                  JohnWatson wrote:

                   

                  You've marked your question answered - was that a mistake?

                  The answer is that you cannot directly take a datafile offline in noarchivelog mode. You must take the tablespace offline:

                   

                  Hmm, are you certain about that? Perhaps I'm missing something.

                   

                  SQL> select file_id, file_name, online_status, status from dba_data_files where tablespace_name = 'USERS';

                   

                     FILE_ID FILE_NAME                                          ONLINE_ STATUS

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

                           4 /u01/app/oracle/oradata/TEST/users01.dbf           ONLINE  AVAILABLE

                   

                   

                  SQL> select log_mode from v$database;

                   

                  LOG_MODE

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

                  NOARCHIVELOG

                   

                   

                  SQL> alter database datafile 4 offline drop;

                   

                  Database altered.

                   

                   

                  SQL> select file_id, file_name, online_status, status from dba_data_files where tablespace_name = 'USERS';

                   

                     FILE_ID FILE_NAME                                          ONLINE_ STATUS

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

                           4 /u01/app/oracle/oradata/TEST/users01.dbf           RECOVER AVAILABLE

                   

                   

                  SQL>  ALTER SYSTEM FLUSH BUFFER_CACHE;  <-- may not have been necessary

                   

                  System altered.

                   

                   

                  SQL> select count(*) from scott.emp;

                  select count(*) from scott.emp

                                             *

                  ERROR at line 1:

                  ORA-00376: file 4 cannot be read at this time

                  ORA-01110: data file 4: '/u01/app/oracle/oradata/TEST/users01.dbf'

                   

                   

                  SQL> recover datafile 4;

                   

                  Media recovery complete.

                   

                  SQL> select file_id, file_name, online_status, status from dba_data_files where tablespace_name = 'USERS';

                   

                     FILE_ID FILE_NAME                                          ONLINE_ STATUS

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

                           4 /u01/app/oracle/oradata/TEST/users01.dbf           OFFLINE AVAILABLE

                   

                   

                  SQL> alter database datafile 4 online;

                   

                  Database altered.

                   

                  SQL> select file_id, file_name, online_status, status from dba_data_files where tablespace_name = 'USERS';

                   

                   

                     FILE_ID FILE_NAME                                          ONLINE_ STATUS

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

                           4 /u01/app/oracle/oradata/TEST/users01.dbf           ONLINE  AVAILABLE

                   

                   

                  SQL> select count(*) from scott.emp;

                   

                    COUNT(*)

                  ----------

                          14

                   

                   

                   

                  Also, please see the following link from the manual which discusses altering data file availability in noarchivelog mode.

                  Altering Datafile Availability

                  • 8. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                    JohnWatson

                    Do  a few log switches, and you will never be able to recover the datafile after an offline drop. I think the issue is that offlining a tablespace (as I did) causes the  datafiles to be checkpointed, therefore no recovery is needed. This means that you can, for instance, safely backup the datafile with an OS utility. Furthermore you can bring it back online at any time in the future. But the oflline drop does not trigger a tablespace checkpoint, so unless redo is available the file can never be used again.

                    I did not know that a file could ever be recovered after offline drop. I have learnt something today. 

                    • 9. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                      Justin_Mungal

                      My initial comment was meant to detract from using an offline drop unless actually planning on removing the data file. Regarding recovering a data file taken offline in NOARCHIVELOG mode, I also emphasized the problems surrounding that:

                       

                      "2)Eventually the SCN at which you took the data file offline will no longer be in the online redo logs

                      3)At that point you'd have to do media recovery to bring the file online again (ie. restore the database)"

                       

                      Really the only reason I responded to you was because of what you said:

                       

                      "You've marked your question answered - was that a mistake?

                      The answer is that you cannot directly take a datafile offline in noarchivelog mode. You must take the tablespace offline:"

                       

                      Hopefully you now agree that data files can be directly taken offline in noarchivelog mode, and that my answer was in fact correct. But I'm a human that makes mistakes like everyone else, so if I'm wrong I'd definitely want to know.

                      • 10. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                        StefanKoehler

                        Hi John,

                         

                        > I think the issue is that offlining a tablespace (as I did) causes the  datafiles to be checkpointed, therefore no recovery is needed.

                        Just for completion: The Eric S. Emrick Blog: Recovery of Offline Data Files

                         

                        Regards

                        Stefan

                        • 11. Re: taking the datafile offline when the database is in NOARCHIVELOG mode
                          1002959

                          Hi again. Ya I agree with you because before posting my doubt into the blog i have noticed the Case 3 (which i have mentioned in the posting). The restore and recovery process remained same whether the database is in NOARCHIVELOG mode or in ARCHIVELOG mode.

                           

                          case3:

                          I have tried the command alter database datafile 6 offline drop; (in NOARCHIVELOG mode) and it is showing the same effect as alter database datafile 6 offline; ( in ARCHIVELOG mode).