Skip to main content

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 ServerStandby Server
OS ReleaseRed Hat Enterprise Linux release 8.3 (Ootpa)Red Hat Enterprise Linux release 8.3 (Ootpa)
Kernel4.18.0-240.8.1.el8_3.x86_644.18.0-240.8.1.el8_3.x86_64
ReleaseOracle Database 12.1.0.2.0Oracle Database 12.1.0.2.0
IP Address192.168.201.24110.100.20.242
Host Namedg1dg2
User NameAdminAdmin

Data Guard Environment

Primary DatabaseStandby Database
Oracle ReleaseOracle Database 12.1.0.2.0Oracle Database 12.1.0.2.0
SQL*PlusRelease 12.1.0.2.0Release 12.1.0.2.0
DATABASE_ROLEPRIMARYPHYSICAL STANDBY
db_unique_nameorclproddgoneorclproddgtwo
db_nameORCLPRODORCLPROD
IS_CDBYESYES
OPEN_MODEREAD WRITEREAD ONLY WITH APPLY
LOG_MODEARCHIVELOGARCHIVELOG
FLASHBACK_ONYESYES
Oracle Data Guard broker utilityNot UseNot Use
remote_login_passwordfileEXCLUSIVEEXCLUSIVE
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:

Login to the Standby server and switch to oracle user.

[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

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='CDB$ROOT';

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

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;

8.1. Connect to the Standby Database:

[oracle@dg2 ~]$ 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

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Upgrading Issue for RHEL 7 to 8 With Leapp

Overview The Leapp utility is a framework for updating and upgrading operating systems as well as applications. The operations of this utility consist of two phases 1. the preupgrade Phase – that chack the upgrade possibilities and 2. the actual upgrade phase – that map packages between previous and current versions of the software packages. Issue – 01: After running ‘ sudo leapp preupgrade ‘ sometimes you find the below issue in ‘ /var/log/leapp/leapp-report.txt ‘. Detail: Risk Factor: high (inhibitor) Title: Leapp detected loaded kernel drivers which have been removed in RHEL 8. Upgrade cannot proceed. Summary: Support for the following RHEL 7 device drivers has been removed in RHEL 8: – pata_acpi Key: f08a07da902958defa4f5c2699fae9ec2eb67c5b Remediation: 1. Disable detected kernel drivers in order to proceed with the upgrade process using the rmmod or modprobe -r . rmmod – Simple program to remove a module from the Linux Kernel modprobe – Add and remove modules from the Linux Ke...
Post a Comment
Read more

Upgrading Oracle Linux 6 to 7

Overview It is possible to upgrade an Oracle Linux 6 system to Oracle Linux 7.6 under the following conditions: The system meets the minimum installation requirements for Oracle Linux 7 as described in Chapter 1, System Requirements and Limits. The Oracle Linux 6 system has been completely updated from the ol6_x86_64_latest channel or ol6_latest repository. UEK R3 or UEK R4 has been installed on the system to be upgraded and is the default boot kernel. Upgrading from UEK R2 is not supported. Note that the system is upgraded to use the UEK R5 release provided with Oracle Linux 7.6. Upgrading is supported only for systems that are installed with the Minimal Install base environment. If additional packages are installed from an alternative repository or channel, upgrade might fail or the resulting upgrade might not function as expected. reference: https://docs.oracle.com/en/operating-systems/oracle-linux/7/relnotes7.6/ol7-install.html#ol7-upgrade-ol6 Verifying the system before Upgrade: #...
Post a Comment
Read more

Install Oracle Database 12c Release 2 On Red Hat 8

Overview According to Oracle, Oracle Database 12c is ‘the first database designed for the cloud’; the suffix ‘c’ stands for the cloud. There are many new features in this release such as multitenant architecture, pluggable database, in-memory, etc. The multitenant architecture is designed to simplify consolidation without requiring any changes to the applications. The rapid provisioning and portability capabilities are enhanced by the pluggable databases. Another new feature is in-memory makes it the first Oracle database to offer real-time analytics. This article describes the installation of Oracle Database 12c release 2 (12.2.0.1.0) 64-bit on Red Hat 8 64-bit. Lab Environment Server Machine Work Station or Client Machine OS Release Red Hat Enterprise Linux release 8.4 (Ootpa) Red Hat Enterprise Linux release 8.4 (Ootpa) Kernel 4.18.0-305.3.1.el8_4.x86_64 4.18.0-305.7.1.el8_4.x86_64 Release Oracle Database 12.1.0.2.0 Oracle SQL Developer Version 19.2.1.247 IP Address 192.168.201.116 ...
Post a Comment
Read more