5 Replies Latest reply on Apr 18, 2012 9:42 AM by CKPT

    Physical standby file resize

    user273366
      Hello.
      I have a physical standby database wherein I would like to resize some datafiles which are taking unnecessary space on the server.
      At the moment I do not have available space to be added in the mount point.
      Can I bring the physical standby database to mount state and fire the ALTER DATABASE DATAFILE commands? Would it impact the application, or would it failover the primary database to the standby if I take the physical standby to mount state?
      Please advice.
      In case of any queries, please let me know.

      Thanks.
        • 1. Re: Physical standby file resize
          Fran
          what is the value of STANDBY_FILE_MANAGEMENT? what's your Oracle version? what's your OS?
          • 2. Re: Physical standby file resize
            Obviously you can not set the standby database datafile to smaller than the primary database datafiles.
            You may need to explain more or reconsider any potential havoc you are going to cause. It is a physical standby, so you ship physical locators, not SQL statements.

            ---------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Physical standby file resize
              CKPT
              I have a physical standby database wherein I would like to resize some datafiles which are taking unnecessary space on the server.
              At the moment I do not have available space to be added in the mount point.
              Can I bring the physical standby database to mount state and fire the ALTER DATABASE DATAFILE commands? Would it impact the application,
              Even you fire the commands on primary when you bring standby database down, still those information exist in Archives also,
              If you start MRP the commands will be executed on standby also. You cant escape it if STANDBY_FILE_MANAGEMENT set to AUTO
              So consider to move the datafiles to different mount point , So that you can avoid such issues, no action need to do on primary at this time.

              or would it failover the primary database to the standby if I take the physical standby to mount state?
              What you are saying from primary to standby & vice versa is Switchover, what is the configuration?
              what is the protection mode?
              Broker enabled with FSFO, Observer?
              1 person found this helpful
              • 4. Re: Physical standby file resize
                user273366
                DG Broker is not configured. We are operating on 10.2.0.3 in Solaris 10.

                SQL> show parameter STANDBY_FILE_MANAGEMENT

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                standby_file_management string auto

                I would just like to login to the physical standby, mount it, and fire the ALTER DATABASE DATAFILE .. commands to resize my datafiles.
                Would that impact any operations? I am not failing over manually to the standby. Archinve logs are getting generated and shipped/applied properly.

                Please advice.
                Thanks.
                • 5. Re: Physical standby file resize
                  CKPT
                  user13021719 wrote:
                  DG Broker is not configured. We are operating on 10.2.0.3 in Solaris 10.

                  SQL> show parameter STANDBY_FILE_MANAGEMENT

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  standby_file_management string auto

                  I would just like to login to the physical standby, mount it, and fire the ALTER DATABASE DATAFILE .. commands to resize my datafiles.
                  Would that impact any operations? I am not failing over manually to the standby. Archinve logs are getting generated and shipped/applied properly.

                  Please advice.
                  Thanks.
                  Sorry, You cant resize datafiles when database in MOUNT status, So consider to move datafiles to another mount point or request to add some space on it.
                  1 person found this helpful