How To Add Temp File In Data Guard Environment

Overview

Adding the Temp File on the primary database of a data guard environment only adds the tablespace entry to the standby, it doesn’t create a temp file associated with the temporary tablespace. So, we need to create temp file manually to the standby Database.

Let’s start to learn how to add the temp file In Data Guard Environment. I recommended, applying this process for Oracle Database 12c Release 1 and Release 2 and also for Oracle Database 19c and 21c link.

Lab Environment

	Primary Server	Standby Server
OS Release	Red Hat Enterprise Linux release 8.3 (Ootpa)	Red Hat Enterprise Linux release 8.3 (Ootpa)
Kernel	4.18.0-240.8.1.el8_3.x86_64	4.18.0-240.8.1.el8_3.x86_64
Release	Oracle Database 12.1.0.2.0	Oracle Database 12.1.0.2.0
IP Address	192.168.201.241	10.100.20.242
Host Name	dg1	dg2
User Name	Admin	Admin

Data Guard Environment

	Primary Database	Standby Database
Oracle Release	Oracle Database 12.1.0.2.0	Oracle Database 12.1.0.2.0
SQL*Plus	Release 12.1.0.2.0	Release 12.1.0.2.0
DATABASE_ROLE	PRIMARY	PHYSICAL STANDBY
db_unique_name	orclproddgone	orclproddgtwo
db_name	ORCLPROD	ORCLPROD
IS_CDB	YES	YES
OPEN_MODE	READ WRITE	READ ONLY WITH APPLY
LOG_MODE	ARCHIVELOG	ARCHIVELOG
FLASHBACK_ON	YES	YES
Oracle Data Guard broker utility	Not Use	Not Use
remote_login_passwordfile	EXCLUSIVE	EXCLUSIVE

Table 02: Lab Environment – Database

Step 1: Check the Database Status:

At first, we check the Database status in both the Primary and Standby instance.

1.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdbaAnd set line size and column format by running the following code.

SQL> set linesize 9000
SQL> column db_unique_name format a14
SQL> column name format a8
SQL> column open_mode format a20
SQL> column LOG_MODE format a10
SQL> column FLASHBACK_ON format a12
SQL> column database_role format a16
SQL> column PROTECTION_MODE format a19
SQL> column PROTECTION_LEVEL format a19
--for line size and column formatting

Now, Run the below SQL statement to get the information of the Primary Database.

SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

We see our Primary Database is up and running in READ WRITE mode with the PRIMARY role.

1.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Now, Run the same SQL statement that we run in the Primary Database to get the information of the Standby Database.

SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

Great! our Standby Database is running in READ ONLY WITH APPLY with the PHYSICAL STANDBY role.

Step 2: Check the Data Guard Status:

2.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Run the below SQL statement on the Standby database.

SQL> set linesize 9000
SQL> column name format a25
SQL> column value format a20
SQL> column time_computed format a25
--for line size and column formatting

SQL> SELECT name, value, time_computed FROM v$dataguard_stats;
Happy to see that, our Data Guard environment is well sync with the Primary Database.

Step 3: Check the Temporary Tablespace Info:

3.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Check how many Pluggable Database are there in your server and also the temporary tablespace and data file associated with the PDB by using the below command. If you learn more about temporary tablespace check the link.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting
SQL> select ROWNUM serial,c.CON_ID,c.NAME container,c.OPEN_MODE,ts.NAME tablespace,tf.NAME temp_file from v$containers c inner join v$tablespace ts on (c.con_id=ts.con_id) inner join v$tempfile tf on (ts.con_id=tf.con_id and ts.TS#=tf.TS#) order by con_id;

3.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Check how many Pluggable Database are there in your server and also the temporary tablespace and data file associated with the PDB by using the below command.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting

SQL> select ROWNUM serial,c.CON_ID,c.NAME container,c.OPEN_MODE,ts.NAME tablespace,tf.NAME temp_file from v$containers c inner join v$tablespace ts on (c.con_id=ts.con_id) inner join v$tempfile tf on (ts.con_id=tf.con_id and ts.TS#=tf.TS#) order by con_id;

From the step number #03, the temporary tablespace in our Data Guard environment is consistents both the side. But the temp file not matched. Because temp file not automatically sync in Data Guard environment. Now we try to add the remaining Temp File to the Standby Database.

Step 4: Stop Log Shipping From Primary To Standby

4.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

4.2. Check the Data Guard Status:

Check the Data Guard Sync status.

SQL> set linesize 9000
SQL> column name format a25
SQL> column value format a20
SQL> column time_computed format a25
--for line size and column formatting

SQL> SELECT name, value, time_computed FROM v$dataguard_stats;

So, our Data Guard is synced.

4.3. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Check the log archive dest state value.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SHOW PARAMETER log_archive_dest_state_2;

In my case I have only one log archive dest and it is enable. Let’s change the value of archive dest to DEFER for stop the log shipping from primary to standby

SQL> ALTER SYSTEM CHECKPOINT;
SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER';
SQL> SHOW PARAMETER log_archive_dest_state_2;

We successfully changed the log archive dest state in the Primary Database.

Step 5: Stop the Redo Apply on Standby Database

5.1. Login to the Standby Server:

[rony@Client ~]$ ssh -X Admin@192.168.201.242
[rony@Client ~]$ su - oracle

And stop the listener.

[oracle@dg2 ~]$ lsnrctl status
[oracle@dg2 ~]$ lsnrctl stop

5.2. Stop Log Apply On Standby:

Now connect to Standby Database,

[oracle@dg2 ~]$ sqlplus / as sysdba

And stop the log apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 6: Open standby Database In Read Only Mode

6.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Check the Database status.

SQL> set lines 200
SQL> column db_unique_name format a13
SQL> column name format a12
SQL> column open_mode format a20
SQL> column LOG_MODE format a12
SQL> column FLASHBACK_ON format a12
SQL> column database_role format a20
SQL> column PROTECTION_MODE format a20
SQL> column PROTECTION_LEVEL format a20
--for line size and column formatting

SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

In my case, The Standby Database is already in the Read Only mode. If your Data Guard is not in the Active Data Guard Mode then you see the Database is in the Mount mode. If your Database is in the Mount mode, run the below command to switch to Read Only Mode.

SQL> ALTER DATABASE OPEN READ ONLY;

Step 7: Add Temp File To Exiting Temporary TableSpace

7.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Step 7.2: Add Temp File To CDB$ROOT

By default, when we connect without any sqlplus parameter it established a connection to CDB$ROOT. Let’s check the connected container and the user name.

SQL> show con_name;
SQL> show user;

Check the temporary tablespace of CDB$ROOT name and temp file location.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting

Now add the temp file.

SQL> alter tablespace temp add tempfile '/oracle/oradata/orclproddgtwo/temp02.dbf' size 2M;

Step 7.3: Add Temp File To PDB$SEED

Connect to PDB$SEED container.

SQL> show pdbs;
SQL> alter session set container=PDB$SEED;
SQL> show con_name;
SQL> show user;

Check the temporary tablespace name of PDB$SEED and the temp file location.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting

SQL> select ROWNUM serial,c.CON_ID,c.NAME container,c.OPEN_MODE,ts.NAME tablespace,tf.NAME temp_file from v$containers c inner join v$tablespace ts on (c.con_id=ts.con_id) inner join v$tempfile tf on (ts.con_id=tf.con_id and ts.TS#=tf.TS#) where c.NAME='PDB$SEED';

Now add the temp file.

SQL> alter tablespace temp add tempfile '/oracle/oradata/orclproddgtwo/pdbseed/temp01dbf' size 2M;

Step 7.4: Add Temp File To PDB

In my case, I have one PDB and it’s name is PDBORCL. So, I need to connect PDBORCL container.

SQL> show pdbs;
SQL> alter session set container=PDBORCL;
SQL> show con_name;
SQL> show user;

Check the temporary tablespace name of PDBORCL and the temp file location.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting

SQL> select ROWNUM serial,c.CON_ID,c.NAME container,c.OPEN_MODE,ts.NAME tablespace,tf.NAME temp_file from v$containers c inner join v$tablespace ts on (c.con_id=ts.con_id) inner join v$tempfile tf on (ts.con_id=tf.con_id and ts.TS#=tf.TS#) where c.NAME='PDBORCL';

Now add the temp file.

SQL> alter tablespace temp add tempfile '/oracle/oradata/orclproddgtwo/pdborcl/temp02.dbf' size 2M;

Step 8: Check the Temp File Both Side

8.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Run the below command to check the temp files.

SQL> set line 400 pages 150
SQL> col serial for 99
SQL> col CON_ID for 99
SQL> col container for a9
SQL> col OPEN_MODE for a10
SQL> col tablespace for a10
SQL> col temp_file for a78
--for line size and column formatting

8.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

SQL> select ROWNUM serial,c.CON_ID,c.NAME container,c.OPEN_MODE,ts.NAME tablespace,tf.NAME temp_file from v$containers c inner join v$tablespace ts on (c.con_id=ts.con_id) inner join v$tempfile tf on (ts.con_id=tf.con_id and ts.TS#=tf.TS#) order by con_id;

In the stop number 08, we can see the number of temp files according to the temporary tablespace is consistent. So, in the next step we need to enable data guard sync.

Step 9: Start Log Shipping From Primary To Standby

91. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Check the log archive dest state value.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> SHOW PARAMETER log_archive_dest_state_2;

In my case I have only one log archive dest and in setp number 04 we disable it. Let’s change the value of archive dest to ENABLE for Start the log shipping from primary to standby

SQL> ALTER SYSTEM CHECKPOINT;
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE';
SQL> SHOW PARAMETER log_archive_dest_state_2;

Step 10: Start the Redo Apply on Standby Database

10.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Start the log apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

In my case, my instance is configured with Active Data Guard, so I do not need to mount to open read only mode. If your instance is not Active Data Guard mode, then switch your standby database to mount state from read only mode.

And Start the listener.

SQL> !lsnrctl Status SQL> !lsnrctl Start

Step 11: Check the Data Guard Status:

11.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Run the below SQL statement on the Standby database.

SQL> set linesize 9000
SQL> column name format a25
SQL> column value format a20
SQL> column time_computed format a25
--for line size and column formatting

SQL> SELECT name, value, time_computed FROM v$dataguard_stats;

Step 12: Check the Database Status:

And finaly, we check the Database status in both the Primary and Standby instance.

12.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

SQL> set lines 200
SQL> column db_unique_name format a13
SQL> column name format a12
SQL> column open_mode format a20
SQL> column LOG_MODE format a12
SQL> column FLASHBACK_ON format a12
SQL> column database_role format a20
SQL> column PROTECTION_MODE format a20
SQL> column PROTECTION_LEVEL format a20
--for line size and column formatting

Now, Run the below SQL statement to get the information of the Primary Database.
SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

12.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

SQL> set lines 200
SQL> column db_unique_name format a13
SQL> column name format a12
SQL> column open_mode format a20
SQL> column LOG_MODE format a12
SQL> column FLASHBACK_ON format a12
SQL> column database_role format a20
SQL> column PROTECTION_MODE format a20
SQL> column PROTECTION_LEVEL format a20 --for line size and column formatting

Now, Run the below SQL statement to get the information of the Standby Database.SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;So, After adding the temp file our database mode and status is ok. Also we check the replication process by applying manual log switch.

13. Check Replication Process:

To check the replication process we switch the log manually on Primary Database and Check the repo apply will happened or not on Standby Database.

13.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Run the below command to switch the log.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> alter system switch logfile;
SQL> ALTER SYSTEM CHECKPOINT;

Then check log switch is happening or not on Standby Database.

13.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Run the below code.

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

Most vital thing, the log switch, is working smoothly.

Conclusion

This tutorial helps you to add the Temporary File in Data Guard environment step by step. If you have any queries please comment to us.

References:

In this tutorial, I follow the official docs as reference Oracle Data Guard Administration Guide.

InfoLipi