Infrastructure at your Service

Oracle Team

Oracle 12cR2 – Howto setup Active DataGuard with Temporary Tablespace Groups

By William Sescu

Temporary Tablespaces Groups exist for quite a while now (since 10gR2), but they are, for whatever reason not so often used. Personally, I think they are quite cool. Very easy to setup, and especially in big environments with a lot of parallel processing very useful. But this blog will not be about Temporary Tablespace Groups. They are already explained in the 12.2 Admin Guide.

https://docs.oracle.com/database/122/ADMIN/managing-tablespaces.htm#ADMIN-GUID-55A6AE10-6875-4B73-9A5C-CB4965AD5AFE

For my Active DataGuard environment, I would like to use Temporary Tablespace Groups in combination with Temporary Undo, which is enabled by default as soon as you activate the ADG feature. And of course, I would like to do it online, without shutting down neither the primary nor the ADG standby.

Is this possible? We will see. I start first with creating a Temporary Tablespace Group called DBITEMP
with 3 bigfile temp tablespaces DBITEMP1/DBITEMP2/DBITEMP3.

-- On Primary

SQL> CREATE BIGFILE TEMPORARY TABLESPACE DBITEMP1 TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T TABLESPACE GROUP DBITEMP;

Tablespace created.

SQL> CREATE BIGFILE TEMPORARY TABLESPACE DBITEMP2 TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T TABLESPACE GROUP DBITEMP;

Tablespace created.

SQL> CREATE BIGFILE TEMPORARY TABLESPACE DBITEMP3 TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T TABLESPACE GROUP DBITEMP;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE DBITEMP;

Database altered.

SQL> alter system archive log current;

System altered.

SQL> select TABLESPACE_NAME, CONTENTS, BIGFILE from dba_tablespaces where CONTENTS = 'TEMPORARY' and TABLESPACE_NAME like '%DBI%';

TABLESPACE_NAME                CONTENTS              BIG
------------------------------ --------------------- ---
DBITEMP1                       TEMPORARY             YES
DBITEMP2                       TEMPORARY             YES
DBITEMP3                       TEMPORARY             YES

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DBITEMP                        DBITEMP1
DBITEMP                        DBITEMP2
DBITEMP                        DBITEMP3

SQL> select NAME, BYTES from v$tempfile where name like '%dbitemp%';

NAME                                                                         BYTES
-------------------------------------------------------------------- -------------
/u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp          33554432
/u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp2_d52c6yqv_.tmp          33554432
/u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp3_d52c7954_.tmp          33554432

SQL> !ls -l /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp
-rw-r----- 1 oracle oinstall 33562624 Dec 14 12:28 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp

SQL> !ls -l /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp2_d52c6yqv_.tmp
-rw-r----- 1 oracle oinstall 33562624 Dec 14 12:29 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp2_d52c6yqv_.tmp

SQL> !ls -l /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp3_d52c7954_.tmp
-rw-r----- 1 oracle oinstall 33562624 Dec 14 12:28 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp3_d52c7954_.tmp

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM database_properties
  2  WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME            PROPERTY_VALUE
------------------------ ----------------------
DEFAULT_TEMP_TABLESPACE  DBITEMP


DGMGRL> show database 'DBIT122_SITE1';

Database - DBIT122_SITE1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    DBIT122

Database Status:
SUCCESS

DGMGRL> show database 'DBIT122_SITE2';

Database - DBIT122_SITE2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 0 Byte/s
  Real Time Query:    ON
  Instance(s):
    DBIT122

Database Status:
SUCCESS

On the primary database, it looks good. Everything was setup correctly, and the Temporary Tablespace Group is ready to be used. Now lets check what we have on the Standby … almost nothing.

My temporary tablespaces and the group are there, however, the tempfiles are missing. So … not really useful at that stage.

SQL> select TABLESPACE_NAME, CONTENTS, BIGFILE from dba_tablespaces where CONTENTS = 'TEMPORARY' and TABLESPACE_NAME like '%DBI%';

TABLESPACE_NAME                CONTENTS              BIG
------------------------------ --------------------- ---
DBITEMP1                       TEMPORARY             YES
DBITEMP2                       TEMPORARY             YES
DBITEMP3                       TEMPORARY             YES

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DBITEMP                        DBITEMP1
DBITEMP                        DBITEMP2
DBITEMP                        DBITEMP3


SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM database_properties
  2  WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME            PROPERTY_VALUE
------------------------ ----------------------
DEFAULT_TEMP_TABLESPACE  DBITEMP

SQL> select NAME, BYTES from v$tempfile where name like '%dbitemp%';

no rows selected

Ok. Lets take a look at the alert.log, if Oracle is telling something. And yes, indeed, a big WARNING message is posted into the alert.log saying that the temporary tablespaces DBITEMP1/2/3 are empty.

*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: DBITEMP1
           Empty temporary tablespace: DBITEMP2
           Empty temporary tablespace: DBITEMP3
*********************************************************************

Ok. With 12.2 it is like it always was. You have to do it manually.

SQL> ALTER TABLESPACE DBITEMP1 ADD TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T;

Tablespace altered.

SQL> ALTER TABLESPACE DBITEMP2 ADD TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T;

Tablespace altered.

SQL> ALTER TABLESPACE DBITEMP3 ADD TEMPFILE SIZE 32M AUTOEXTEND ON MAXSIZE 12T;

Tablespace altered.

Now I have everthing in place. What about dropping a temporary tablespace out of the tablespace group First, I take it out of temporary tablespace group. This change is immediately reflected on the Standby.

-- Primary

SQL> alter tablespace DBITEMP3 TABLESPACE GROUP '';

Tablespace altered.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DBITEMP                        DBITEMP1
DBITEMP                        DBITEMP2

-- Standby 

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DBITEMP                        DBITEMP1
DBITEMP                        DBITEMP2

And now you can drop it.

-- Primary

SQL> drop tablespace DBITEMP3 including contents and datafiles;

Tablespace dropped.

alert.log
...
drop tablespace DBITEMP3 including contents and datafiles
2016-12-14T16:35:25.567597+01:00
Deleted Oracle managed file /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp3_d52c7954_.tmp
Completed: drop tablespace DBITEMP3 including contents and datafiles
...

-- Standby

alert.log
...
Deleted Oracle managed file /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp3_d52dv3dv_.tmp
Recovery deleting tempfile #4:'/u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp3_d52dv3dv_.tmp'
Recovery dropped temporary tablespace 'DBITEMP3'
...

ok. Dropping works fine. This change is also immediately reflected. What about resizing a temporary file? Is this change also immediately reflected on the standby? Due to OMF, we do have different names for the tempfiles, so should we use a “alter database tempfile <NAME> resize” or a “alter database tempfile <NUMBER> resize” to make it work. In regards of regular datafiles, it doesn’t matter, you can use both ways and Oracle does it correctly.

- Primary

SQL> select file#, name, bytes from v$tempfile where name like '%dbitemp%';

     FILE# NAME                                                                      BYTES
---------- -------------------------------------------------------------------- ----------
         2 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp1_d52dthwg_.tmp       33554432
         3 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp2_d52dtx7f_.tmp       33554432

		 
SQL> alter database tempfile '/u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp' resize 512M;

Database altered.

SQL> select file#, name, bytes from v$tempfile where name like '%dbitemp%';

     FILE# NAME                                                                      BYTES
---------- -------------------------------------------------------------------- ----------
         2 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp      536870912
         3 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp2_d52c6yqv_.tmp       33554432


-- Standby

SQL> select file#, name, bytes from v$tempfile where name like '%dbitemp%';

     FILE# NAME                                                                      BYTES
---------- -------------------------------------------------------------------- ----------
         2 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp1_d52dthwg_.tmp       33554432
         3 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp2_d52dtx7f_.tmp       33554432

Ok. Resizing by name is not reflected on the standby. But what about resizing the tempfile by using the file number instead of the name?

-- Primary
 
SQL> alter database tempfile 3 resize 768M;

Database altered.

SQL> select file#, name, bytes from v$tempfile where name like '%dbitemp%';

     FILE# NAME                                                                      BYTES
---------- -------------------------------------------------------------------- ----------
         2 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp1_d52c66g5_.tmp      536870912
         3 /u02/oradata/DBIT122_SITE1/datafile/o1_mf_dbitemp2_d52c6yqv_.tmp      805306368

		 
-- Standby

SQL> select file#, name, bytes from v$tempfile where name like '%dbitemp%';

     FILE# NAME                                                                      BYTES
---------- -------------------------------------------------------------------- ----------
         2 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp1_d52dthwg_.tmp       33554432
         3 /u02/oradata/DBIT122_SITE2/datafile/o1_mf_dbitemp2_d52dtx7f_.tmp       33554432

As you can see, I doesn’t matter. Resize operations on tempfiles are not replicated to the standby.

Conclusion

Temporary Tablespace Groups can be used with Active DataGuard, and of course, it can be done online. However, when you initially create them, or when you resized them, some manual work has to be done on the standby. And yes, the Temporary Tablespace Groups can be used in combination with the Temporary UNDO feature on the Active DataGuard Standby.

 

One Comment

  • Edward says:

    Hi. Great article. I’m dealing with a customer with a bigfile tempfile running 12.2.0.1 (no patches). When I try to add a tempfile it tells me that you cannot add a file to a bigfile tablespace. I’ve tried removing the datafile and I’m told that I can’t remove a tempfile from a bigfile tablespace. I even tried snapshot standby and the tempfile was not created. Any ideas?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team