This discussion is archived
10 Replies Latest reply: Oct 24, 2012 4:35 PM by jgarry RSS

Change the maxsize of the datafiles in DataGuard 10gR2

568397 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams238.htm
  • 8. Re: Change the maxsize of the datafiles in DataGuard 10gR2
    jgarry Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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