Skip navigation

I needeed to increase the size of a TEMPORARY tablespace in a STANDBY database.  Hey, it's a standby database, so alterations must be made to the primary database, then DataGuard will propagate those changes to the standby database.

So I went to the primary database and ran:

14:00:09 22-01 14:00 SYS > alter database tempfile '/appli/oracle/.../oradata02/****_temp_01.dbf'  AUTOEXTEND ON NEXT 10M MAXSIZE 13G  ;

 

Database altered.

180122alterMAXISEofTEMP.JPG

 

On the standby database, I was monitoring its alert log with

adrci> show alert -tail -f

waiting for a message notifying me of the change on the temp file.

To trigger off that change, I switched logfiles on the primary to create a new archived log:

14:02:37 22-01 14:02 PRIMARYDB SYS > alter system switch logfile ;

 

System altered.

 

The alert log of the standby did mention applying the newly received archived logs but my temp file on the standby did not budge.  Since all I did on the primary was to alter the MAXSIZE of my temp file, I tried something more telling:

14:35:29 22-01 14:35 PRIMARYDB SYS > ALTER DATABASE TEMPFILE '/appli/oracle/..../oradata02/****_temp_01.dbf' RESIZE 7G;

 

Database altered.

180122RESIZEofTEMP.JPG

Again, I did alter system switch logfile  to propagate the change to the standby, but to no avail.

 

I googled for that phenomenon and read on a DBI-SERVICES blog post that changes to temp files on the primary are not propagated to the standby because they do not generate redo.  So I logged out of SQL+ and started a brand-new session, in which I checked the REDO that I generate (with V$MYSTAT)  when altering my temp file:

 

15:56:17 PRIMARYDB SYS AS SYSDBA > SELECT NAME statname,VALUE FROM V$MYSTAT NATURAL JOIN V$STATNAME WHERE NAME LIKE 'redo%entr%' OR NAME LIKE '%redo%size' or name like '%redo%writes' order by value desc;

 

STATNAME                                   VALUE

-------------------------------------- ---------

redo entries                                   0

redo size                                      0

redo entries for lost write detection          0

redo synch poll writes                         0

redo writes                                    0

redo synch writes                              0

redo size for direct writes                    0

 

15:56:26 PRIMARYDB SYS AS SYSDBA > alter database tempfile '/appli/oracle/..../oradata02/****_temp_01.dbf' autoextend on NEXT 10M MAXSIZE 11G  ;

 

Database altered.

 

15:56:37 PRIMARYDB SYS AS SYSDBA > SELECT NAME statname,VALUE FROM V$MYSTAT NATURAL JOIN V$STATNAME WHERE NAME LIKE 'redo%entr%' OR NAME LIKE '%redo%size' or name like '%redo%writes' order by value desc;

 

STATNAME                                   VALUE

-------------------------------------- ---------

redo size                                    816

redo entries                                   3

redo synch writes                              1

redo writes                                    0

redo synch poll writes                         0

redo entries for lost write detection          0

redo size for direct writes                    0

 

15:56:43  SYS AS SYSDBA > alter system switch logfile ;

SELECT NAME statname,VALUE FROM V$MYSTAT NATURAL JOIN V$STATNAME WHERE NAME LIKE 'redo%entr%' OR NAME LIKE '%redo%size' or name like '%redo%writes' order by value desc;

System altered.

 

15:56:59 PRIMARYDB SYS AS SYSDBA >

 

STATNAME                                   VALUE

-------------------------------------- ---------

redo size                                    816

redo entries                                   3

redo synch writes                              1

redo writes                                    0

redo synch poll writes                         0

redo entries for lost write detection          0

redo size for direct writes                    0

 

15:57:00 PRIMARYDB SYS AS SYSDBA > select BYTES/1024/1024,MAXBYTES/1024/1024,STATUS,USER_BYTES/1024/1024 UsedMB  from dba_temp_files ;

 

BYTES/1024/1024 MAXBYTES/1024/1024 STATUS                    USEDMB

--------------- ------------------ --------------------- ----------

           8192              11264 ONLINE                      8191

 

That test shows that on my 11.2.0.2 database, my alter database tempfile does generate redo (3 redo entries)

 

27-02 12:27 PRIMARYDB SYS > alter database tempfile '/appli/oracle/..../oradata02/****_temp_01.dbf' resize 8g;

 

Database altered.

 

27-02 12:30 PRIMARYDB SYS > SELECT NAME statname,VALUE FROM V$MYSTAT NATURAL JOIN V$STATNAME WHERE NAME LIKE 'redo%entr%' OR NAME LIKE '%redo%size' or name like '%redo%writes' order by value desc;

 

STATNAME                                   VALUE

---------------------------------------- -------

redo size                                    792

redo entries                                   3

redo synch writes                              1

redo writes                                    0

redo synch poll writes                         0

redo entries for lost write detection          0

redo size for direct writes

 

27-02 12:31 PRIMARYDB SYS >  alter system switch logfile ;

 

System altered.

 

27-02 12:32 PRIMARYDB SYS >  SELECT NAME statname,VALUE FROM V$MYSTAT NATURAL JOIN V$STATNAME WHERE NAME LIKE 'redo%entr%' OR NAME LIKE '%redo%size' or name like '%redo%writes' order by value desc;

 

STATNAME                                   VALUE

---------------------------------------- -------

redo size                                    792

redo entries                                   3

redo synch writes                              1

redo writes                                    0

redo synch poll writes                         0

redo entries for lost write detection          0

redo size for direct writes                    0

 

 

ALTER TABLESPACE TEMP ADD TEMPFILE '/appli/oracle/..../oradata02/****_temp_02.dbf' SIZE 2M ; => this works even though the STANDBY is opened READ ONLY WITH APPLY.

And this generates no redo (no wonder, it's open READ-ONLY):

STATNAME                                           VALUE

-------------------------------------------- -----------------

redo entries                                           0
redo size                                              0
redo entries for lost write detection                  0
redo synch poll writes                                 0
redo writes                                            0
redo synch writes                                      0
redo size for direct writes                            0

 

 

But to add a temporary tablespace, you must first create it on the PRIMARY:

27-02 16:21 PRIMARYDB SYS > CREATE TEMPORARY TABLESPACE "TEMP2" TEMPFILE '/appli/oracle/..../oradata02/****_temp2_01.dbf' SIZE 4M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K ;

Tablespace created.

 

27-02 16:36 PRIMARYDB SYS > select autoextensible auto,tablespace_name,bytes/1024/1024 "Mo",file_name from dba_temp_files;

AUTO      TABLESP         Mo FILE_NAME

--------- ------- ---------- ----------------------------------------------------------------------

NO        TEMP          8192 /appli/oracle/..../oradata02/****_temp_01.dbf

NO        TEMP2            4 /appli/oracle/..../oradata02/****_temp2_01.dbf

 

On the STANDBY, you will discover that there is no new TEMPFILE:

27-02 16:23 STANDBYDB SYS > select autoextensible auto,tablespace_name,bytes/1024/1024 "Mo",file_name from dba_temp_files;

AUTO      TABLESP         Mo FILE_NAME

--------- ------- ---------- ----------------------------------------------------------------------

NO        TEMP          5120 /appli/oracle/..../oradata02/****_temp_01.dbf

 

But that a new TEMPORARY TABLESPACE does exist:

27-02 16:39 STANDBYDB SYS >  select contents,STATUS,TABLESPACE_NAME   from dba_tablespaces where tablespace_name like 'TEMP%';

 

CONTENTS                    STATUS       TABLESP

--------------------------- ------------ -------

TEMPORARY                   ONLINE       TEMP

TEMPORARY                   ONLINE       TEMP2

 

So you must ADD a TEMPFILE to that empty TEMPORARY TABLESPACE (yes yes, that 's what Oracle says) :

27-02 16:42 STANDBYDB SYS > ALTER TABLESPACE temp2 ADD TEMPFILE '/appli/oracle/..../oradata02/****_temp2_01.dbf' SIZE 4M AUTOEXTEND ON MAXSIZE 20g;

 

Tablespace altered.

 

27-02 16:44 STANDBYDB SYS > select autoextensible auto,tablespace_name,bytes/1024/1024 "Mo",file_name from dba_temp_files;

 

AUTO      TABLESP         Mo FILE_NAME

--------- ------- ---------- ----------------------------------------------------------------------

NO        TEMP          5120 /appli/oracle/..../oradata02/****_temp_01.dbf

NO        TEMP             2 /appli/oracle/..../oradata02/****_temp_02.dbf

YES       TEMP2            4 /appli/oracle/..../oradata02/****_temp2_01.dbf

 

Nevertheless, if you DROP that new TEMPORARY TABLESPACE on the PRIMARY:

27-02 16:39 PRIMARYDB SYS > drop tablespace temp2 including contents and datafiles ;

Tablespace dropped.

 

It does reflect immediately on the STANDBY:

27-02 16:44 STANDBYDB SYS > /

 

AUTO      TABLESP         Mo FILE_NAME

--------- ------- ---------- ----------------------------------------------------------------------

NO        TEMP          5120 /appli/oracle/..../oradata02/****_temp_01.dbf

NO        TEMP             2 /appli/oracle/..../oradata02/****_temp_02.dbf

 

And while the the PRIMARY ALERT.LOG says:

2018-02-27 16:48:53.648000 +01:00

drop tablespace temp2 including contents and datafiles

Deleted file /appli/oracle/..../oradata02/****_temp2_01.dbf

Completed: drop tablespace temp2 including contents and datafiles

180228ADDTEMPFILEdansALERTLOG.JPG

The STANDBY ALERT.LOG says it differently:

2018-02-27 16:48:55.800000 +01:00

Deleted file /appli/oracle/..../oradata02/*_temp2_01.dbf

Recovery dropped temporary tablespace 'TEMP2'