News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Data Guard – Adding and Resizing a datafile on the Primary database

  • Posted by Gavin Soorma
  • On July 4, 2009
  • 3 Comments
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

K. Subramanyam
  • Jul 24 2009
Hello folks, Resizing the datafile on the primary will take effect on standby? Please clarify. To my knowledge ALTER DATABASE command will not populate on standby when it's trigged on primary. Let me know if i wrong. Thanks.
Gavin Soorma
  • Jul 26 2009
Hi Subramanyam - if standby_file_management is set to AUTO, then any ALTER DATABASE ADD/RESIZE DATAFILE will also propagate to the standby database. We add another file to the USERS tablespace and switch a logfile to ensure the redo is copied to the standby site since we are operating in MAXIUMUM PERFORMANCE mode. SQL> alter tablespace users add datafile 2 '/u02/oradata/testdb/users03.dbf' size 100m; Tablespace altered. SQL> alter system switch logfile; System altered. If we look at the standby database alert log we will see that the new datafile has been added to the standby database as well Recovery created file /u02/oradata/testdb/users03.dbf Successfully added datafile 9 to media recovery Datafile #9: '/u02/oradata/testdb/users03.dbf' We can query the v$DATAFILE view on the standby to confirm the same as well SQL> select name from v$datafile where name like '%users03.dbf%'; NAME -------------------------------------------------------------------------------- /u02/oradata/testdb/users03.dbf
sunil
  • Feb 1 2010
Nice article.... Does adding a Tempfile on primary, make any difference ?

Leave Reply

Your email address will not be published. Required fields are marked *