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.
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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)