3 Replies Latest reply: Nov 20, 2013 12:06 AM by Mahir M. Quluzade RSS

    Drop a datafile from physical standby's control file

    Jibu

      Hi,

       

      I am trying to create a physical standby database for my production...

       

      1) I have taken cold backup of my primary database on 18-Nov-2013...

      2) I added a datafile on 19-nov-2013 ( 'O:\ORADATA\SFMS\SFMS_DATA4.DBF' )

      3) Standby control file was generated on 20-ov-2013 (today) after shutting down and then mounting the primary database...

       

      When i try to recover the newly setup standby using archive files, i am getting the following error (datafile added on 19th Nov is missing)

       

      SQL> recover standby database;

      ORA-00283: recovery session canceled due to errors

      ORA-01110: data file 39: 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

      ORA-01157: cannot identify/lock data file 39 - see DBWR trace file

      ORA-01110: data file 39: 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

       

      How to overcome this situation...

       

      Can i delete the entry for the newly added datafile from the backup control file ?

       

      When i tried to delete datafile using "alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF';", it is showing that database should be  open..

       

      SQL> alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

      ;

      alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

      *

      ERROR at line 1:

      ORA-01109: database not open

       

       

       

       

      SQL> show parameter STANDBY_FILE_MANAGEMENT

       

       

      NAME                                 TYPE        VALUE

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

      standby_file_management              string      AUTO

      SQL> alter system set STANDBY_FILE_MANAGEMENT=manual;

       

       

      System altered.

       

       

      SQL> show parameter STANDBY_FILE_MANAGEMENT

       

       

      NAME                                 TYPE        VALUE

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

      standby_file_management              string      MANUAL

      SQL> alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

      ;

      alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

      *

      ERROR at line 1:

      ORA-01109: database not open

       

      Regards,

      Jibu

        • 1. Re: Drop a datafile from physical standby's control file
          Mahir M. Quluzade

          Jibu wrote:

           

          Hi,

           

          I am trying to create a physical standby database for my production...

           

          1) I have taken cold backup of my primary database on 18-Nov-2013...

          2) I added a datafile on 19-nov-2013 ( 'O:\ORADATA\SFMS\SFMS_DATA4.DBF' )

          3) Standby control file was generated on 20-ov-2013 (today) after shutting down and then mounting the primary database..

          Hi,

           

          What is your version?

           

          If you added new datafile or created new tablespace, take backup again for restore new created standby database.

          If your standby  database running well, DG configuration success, then this datafile will create on standby side, too.

           

          Set STANDBY_FILE_MANAGEMENT=AUTO best practice.

           

          When i try to recover the newly setup standby using archive files, i am getting the following error (datafile added on 19th Nov is missing)

           

          SQL> recover standby database;

          ORA-00283: recovery session canceled due to errors

          ORA-01110: data file 39: 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

          ORA-01157: cannot identify/lock data file 39 - see DBWR trace file

          ORA-01110: data file 39: 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

           

          How to overcome this situation...

           

           

          Can i delete the entry for the newly added datafile from the backup control file ?

          Not need any delete datafile from standby side, you must recreate standby database, or you can  take RMAN backup and restore to standby  side again.

           

          When i tried to delete datafile using "alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF';", it is showing that database should be  open..

           

          SQL> alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

          ;

          alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

          *

          ERROR at line 1:

          ORA-01109: database not open

           

           

           

           

          SQL> show parameter STANDBY_FILE_MANAGEMENT

           

           

          NAME                                 TYPE        VALUE

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

          standby_file_management              string      AUTO

          SQL> alter system set STANDBY_FILE_MANAGEMENT=manual;

           

           

          System altered.

           

           

          SQL> show parameter STANDBY_FILE_MANAGEMENT

           

           

          NAME                                 TYPE        VALUE

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

          standby_file_management              string      MANUAL

          SQL> alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

          ;

          alter tablespace SFMS_BR_DATA drop datafile 'O:\ORADATA\SFMS\SFMS_DATA4.DBF'

          *

          ERROR at line 1:

          ORA-01109: database not open

          It is not logical, Physical  standby must be bit-for-bit same with Primary  database.

           

           

          Regards

          Mahir M. Quluzade

          • 2. Re: Drop a datafile from physical standby's control file
            Jibu

            Thanks for the reply...

             

            I forgot to take standby control file on 18-Nov; whilte taking cold backup..

             

            Regards,

            Jibu

            • 3. Re: Drop a datafile from physical standby's control file
              Mahir M. Quluzade

              As you know, your database physical  structure changed 19-Nov, you must take standby controlfile and backup again.

              and why you  are using cold backup?

              You can  use RMAN backups for easy create standby  database. and RMAN Duplicate command

               

               

              Regards

              Mahir M. Quluzade