This discussion is archived
3 Replies Latest reply: Nov 19, 2013 10:06 PM by MahirM.Quluzade RSS

Drop a datafile from physical standby's control file

Jibu Newbie
Currently Being Moderated

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
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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
    MahirM.Quluzade Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points