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.
3 Comments