10 Replies Latest reply: Oct 24, 2012 6:35 PM by jgarry RSS

    Change the maxsize of the datafiles in DataGuard 10gR2

    568397
      We have the DataGuard 10gR2, 1 is primary and other is physical standby in production.
      My questions:
      1/ The maxsize of the datafiles is being modified by either increasing or decreasing in primary, does it modification change automatically also in standby ? Note: the parameter is set in the init.ora file standby_file_management = AUTO.
      If it does not change in standby, what can we do to change it in stanby

      2/ We want to resize the datafile in primary using the below command, does it modification change automatically also in standby ?
      If it does not change in standby, what can we do to change it in stanby

      ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
        • 1. Re: Change the maxsize of the datafiles in DataGuard 10gR2
          rp0428
          >
          We have the DataGuard 10gR2, 1 is primary and other is physical standby in production.
          My questions:
          1/ The maxsize of the datafiles is being modified by either increasing or decreasing in primary, does it modification change automatically also in standby ? Note: the parameter is set in the init.ora file standby_file_management = AUTO.
          If it does not change in standby, what can we do to change it in stanby

          2/ We want to resize the datafile in primary using the below command, does it modification change automatically also in standby ?
          If it does not change in standby, what can we do to change it in stanby

          ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
          >
          DDL changes are not maintained by Oracle; you need to apply the same changes to the standby database.
          Hopefully the underlying file structure is the same for both databases.

          1) - You change it on the standby the same way you change in on the master.

          2) - No - you have to change standby to match.

          See '9.1.12.1 DDL Transactions Containing File Specifications' in the Data Guard Concepts and Administration doc
          http://docs.oracle.com/cd/B14117_01/server.101/b10823/manage_ls.htm
          >
          9.1.12 Recovering from Errors
          Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream. Tables in the SYS schema are never maintained, because only Oracle metadata is maintained in the SYS schema.

          If SQL Apply fails, an error is recorded in the DBA_LOGSTDBY_EVENTS table. The following sections demonstrate how to recover from two such errors.

          9.1.12.1 DDL Transactions Containing File Specifications

          DDL statements are executed the same way on the primary database and the logical standby database. If the underlying file structure is the same on both databases, the DDL will execute on the standby database as expected. However, if the structure of the file system on the standby system differs from the file system on the primary system, it is likely that an error might result because the DB_FILE_NAME_CONVERT will not convert the filenames of one or more sets of datafiles on the primary database to filenames on the standby database for a logical standby database.
          • 2. Re: Change the maxsize of the datafiles in DataGuard 10gR2
            mseberg
            Hello;

            This will depend upon this parameter :

            STANDBY_FILE_MANAGEMENT=AUTO


            If set to AUTO as shown then the filesize will change on the Primary. If set to MANUAL then it won't.

            For additional information see :

            http://docs.oracle.com/cd/B14117_01/server.101/b10755/initparams206.htm

            rp0428 is correct the document does not confirm your question. However it's still true.

            And his document is completely wrong. Wrong version of Oracle. 10.1 and not 10.2 and he points to "Managing a Logical Standby".
            See '9.1.12.1 DDL Transactions Containing File Specifications' in the Data Guard Concepts and Administration doc
            http://docs.oracle.com/cd/B14117_01/server.101/b10823/manage_ls.htm
            This is information about a Logical Standby. You state you have a physical and version 10.2.

            Best Regards

            mseberg

            Edited by: mseberg on Oct 22, 2012 8:45 PM
            • 3. Re: Change the maxsize of the datafiles in DataGuard 10gR2
              rp0428
              >
              This will depend upon this parameter :

              STANDBY_FILE_MANAGEMENT=AUTO

              If set to AUTO as shown then the filesize will change on the Primary. If set to MANUAL then it won't.
              >
              I don't see anything in your doc reference that confirms that statement.

              Also, can you please reconcile your statement with the excerpt from the doc that I quoted above.
              >
              9.1.12 Recovering from Errors
              Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream.
              • 4. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                Karan
                Even if standby_file_management is manual your resize command which is executed on primary will also work very will on standby without any1's intervention, however as i said if standby_file_management is manual and is you execute something like alter tablespace users add datafile .. then on standby you will get this new file as /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005 and then you need to alter database create datafile /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005 as '....' and then you need to start mrp cagain by executing SQL> alter database recover managed standby database disconnect from session; and then go ahead and set alter system set standby_file_management = auto;

                Also i dont see anything like resize here in http://docs.oracle.com/cd/B19306_01/server.102/b14239/manage_ps.htm#i1022518


                Regards
                Karan

                Edited by: Karan on Oct 23, 2012 11:04 AM
                • 5. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                  568397
                  I've run this cmd on standby, I got error "ORA-01109: database not open "

                  alter database datafile '/u02/stuff01.dbf' autoextend on maxsize 4000M;
                  • 6. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                    mseberg
                    Hello again;

                    I would not do this. A Physical Standby is a clone of the Primary. If the Primary doesn't resize the file, there's no reason to even attempt a resize on the Standby.

                    The error is because the DB is in READ ONLY mode.

                    Consider taking a step back and thinking about what you really need to do.

                    The only example of MAXSIZE in Oracle Data Guard Concepts and Administration 10g Release 2 (10.2) B14239-05 is adding a new Datafile. See :

                    "8.3.1.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO"

                    Maybe run this on the Primary :
                    select tablespace_name, file_name, autoextensible from dba_data_files;
                    Best Regards

                    mseberg
                    • 7. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                      Fran
                      http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams238.htm
                      • 8. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                        jgarry
                        rp0428 wrote:
                        >
                        This will depend upon this parameter :

                        STANDBY_FILE_MANAGEMENT=AUTO

                        If set to AUTO as shown then the filesize will change on the Primary. If set to MANUAL then it won't.
                        >
                        I don't see anything in your doc reference that confirms that statement.

                        Also, can you please reconcile your statement with the excerpt from the doc that I quoted above.
                        >
                        9.1.12 Recovering from Errors
                        Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream.
                        >
                        The OP specified physical standby, not logical.
                        • 9. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                          rp0428
                          OP is ALTERing a file - not adding/deleting files. That section does not apply to ALTER of existing files.
                          • 10. Re: Change the maxsize of the datafiles in DataGuard 10gR2
                            jgarry
                            The idea of a physical standby is to have the data files be identical on both primary and standby databases. The idea of standby_file_management set to auto is to syncronize the two, whatever physical commands to change the primary data files propagate to the standby, without the hassle of making the same changes manually. This has been in there a long time. It's easy enough to see, look for the word resize here: http://gavinsoorma.com/2009/07/data-guard-adding-and-resizing-a-datafile-on-the-primary-database/

                            I know it used to be in a MOS doc long ago - that's how I learned it, in the days before DG - but things get aged out there. The DDL is in the redo stream, the question is whether and how the standby honors it. I'd guess it's probably a doc error that resize got left out.