https://gavinsoorma.com/2009/07/data-guard-adding-and-resizing-a-datafile-on-the-primary-database/
In a Data Guard environment, care should be taken while performing tasks like adding or resizing datafiles on the primary database. Ideally, the primary and standby sites should be identical in terms of file system structure. Also disk space allocated should be identical, or we could be faced with a case when resizing a datafile on the the primary site fails on the standby site as the disk space available is not adequate or maybe a file was added on a file system on the primary and the corresponding file system was not available on the standby site based on the parameter db_file_name_convert. In such cases the MRP0 process will stop on the standby site and redo apply operations will cease. Test case: Primary machine: itlinuxdevblade07 Standby machine: itlinuxdevblade08 We will use the USERS tablespace for the test • Add a datafile • Resize a datafile • Drop tablespace Database used for testing is dgtest9i. Firstly the following gives an explanation of init.ora parameters used in a dataguard set-up and whether they pertain to the primary or standby database. log_archive_dest_1 : used both when Primary and when Standby standby_archive_dest : used when Standby, ignored when Primary log_archive_dest_2 : used when Primary, ignored when Standby db_file_name_convert : used when Standby, ignored when Primary log_file_name_convert : used when Standby, ignored when Primary fal_server : used when Standby, ignored when Primary fal_client : used when Standby, ignored when Primary standby_file_management : used when Standby, ignored when Primary log_archive_format : used both when Primary and when Standby log_archive_start : used both when Primary and when Standby remote_archive_enable : must be TRUE on both Primary and Standby drs_start : must be TRUE to use Data Guard Broker compatible : must be identical on both Primary and Standby db_name, db_domain : must be identical on both Primary and Standby Primary SITE: . setdgtest9i SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf cd /opt/oracle/oradata/dgtest9i> ls –l [oracle@itlinuxdevblade07 dgtest9i]$ ls -l total 987948 drwxr-xr-x 2 oracle dba 4096 Feb 12 11:28 bkp -rw-r----- 1 oracle dba 41951232 Jan 25 12:20 temp01.dbf -rw-r----- 1 oracle dba 78651392 Mar 4 14:33 undotbs01.dbf -rw-r----- 1 oracle dba 22028288 Mar 3 14:41 users01.dbf In STANDBY database init.ora… *.standby_file_management='AUTO' .db_file_name_convert='/opt/oracle/','/opt/oracle/' *.log_file_name_convert='/opt/oracle/','/opt/oracle/' STANDBY SITE: cd / opt/oracle/oradata/dgtest9i> [oracle@itlinuxdevblade08 dgtest9i]$ /bin/ls –l…. only few files are shown here to save space. total 1035568 -rwxrwxr-x 1 oracle dba 41951232 Feb 23 22:38 temp01.dbf -rwxrwxr-x 1 oracle dba 78651392 Mar 4 14:37 undotbs01.dbf -rwxrwxr-x 1 oracle dba 22028288 Mar 4 09:08 users01.dbf 1.Add a datafile. In primary database : . setdgtest9i SQL> alter tablespace users add datafile '/opt/oracle/oradata/dgtest9i/users02.dbf' size 10m; Tablespace altered. SQL> alter system switch logfile; (To make sure the a log is shipped to the standby site and changes applied.) System altered. If you check in the standby site you will see the new file …. STANDBY SITE [oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt users* total 1045832 -rw-r----- 1 oracle dba 10493952 Mar 4 14:46 users02.dbf -rw-r----- 1 oracle dba 22028288 Mar 4 14:46 users01.dbf 2. Resize datafile. PRIMARY SITE: SQL> Alter database datafile ‘/opt/oracle/oradata/dgtest9i/users02.dbf' RESIZE 5m; Database altered. SQL> alter system switch logfile; System altered. SQL> / System altered. Now in STANDBY database machine… [oracle@itlinuxdevblade08 dgtest9i]$ ls –lrt users* total 1040708 -rw-r----- 1 oracle dba 5251072 Mar 4 18:00 users02.dbf -rwxrwxr-x 1 oracle dba 22028288 Mar 4 18:00 users01.dbf Notice the size of the users02.dbf file is 5 m on the standby site as well… PROBLEMS THAT MAY ARISE : Now we will look at the scenario where the db_file_convert parameter PATH was wrong.. SHUTDOWN both standby and primary databases….. Change the db_file_name_convert parameter from standby database init.ora file and recreate the spfile from pfile. EX: db_file_name_convert =/opt/oracle/, /opt/arjun arjun directory must not exist IN STANDBY machine... Now startup the standby db… SQL> Startup nomount; SQL> Alter database mount standby database; Restart PRIMARY DATABASE. Go back to standby database and SQL> recover managed standby database disconnect; SQL> show parameter convert NAME TYPE VALUE ------------------------------------ ----------- --------------------------------------- db_file_name_convert string /opt/oracle/, /opt/arjun Now go to PRIMARY database and add a datafile….. SQL> alter tablespace users add datafile '/opt/oracle/oradata/dgtest9i/users04.dbf' size 5m; Tablespace altered. SQL> alter system switch logfile; System altered. SQL> SQL> / System altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf In standby site… SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/arjun/oradata/dgtest9i/system01.dbf /opt/arjun/oradata/dgtest9i/undotbs01.dbf /opt/arjun/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf Notice file users04 is missing… In this case shutdown the standby database….and recreate the spfile making sure the path is right.. Then restart the standby database and start recovery…. SQL> select message from v$dataguard_status; MESSAGE -------------------------------------------------------------------------------- ARC0: Archival started ARC1: Archival started Media Recovery Log /opt/oracle/dgtest9i/arch/arch196.log Media Recovery Waiting for thread 1 seq# 197 10 rows selected. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf NEXT SCENARIO….ADDING DATAFILE IN ANOTHER AREA ON PRIMARY MACHINE… SQL> create tablespace arjun datafile '/u01/ORACLE/dgtest9i/arjun1.dbf' size 5m;..The directory /u01/ORACLE/dgtest9i does not exist on the STANDBY machine. Tablespace created. SQL> alter system switch logfile; SQL> SELECT NAME FROM V$DATAFILE; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf /u01/ORACLE/dgtest9i/arjun1.dbf On STANDBY site….. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf /opt/oracle/product9204/dbs/UNNAMED00007…. 7 rows selected. STANDBY SITE SQL> SHOW PARAMETER STANDBY; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_archive_dest string ?/dbs/arch standby_file_management string AUTO SQL> alter system set standby_file_management=manual ; System altered. SQL> alter database create datafile '/opt/oracle/product9204/dbs/UNNAMED00007' 2 as '/opt/oracle/oradata/dgtest9i/arjun1.dbf'; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf /opt/oracle/oradata/dgtest9i/arjun1.dbf 7 rows selected. SQL> alter system set standby_file_management=auto ; 3. DROPPING TABLESPACE. In PRIMARY… SQL> DROP TABLESPACE ARJUN INCLUDING CONTENTS AND DATAFILES; SQL> Alter system switch logfile; SQL> SELECT NAME FROM V$DATAFILE; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf 6 rows selected. In STANDBY Database…. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /opt/oracle/oradata/dgtest9i/system01.dbf /opt/oracle/oradata/dgtest9i/undotbs01.dbf /opt/oracle/oradata/dgtest9i/users01.dbf /opt/oracle/oradata/dgtest9i/users02.dbf /opt/oracle/oradata/dgtest9i/users03.dbf /opt/oracle/oradata/dgtest9i/users04.dbf 6 rows selected.
