Database Switchover was successful but the temporary tablespace got failures, why this issue happen
Posted by Mir Sayeed Hassan on March 22nd, 2026
Database Switchover was successful but the temporary tablespace got failures, why this issue happen
Before proceeding to this process, create the database outage for both the primary and standby databases.
Disable all the database jobs in both primary and standby database., check if the database jobs are running or schedules by crontab jobs or any other alternative tools used for this to run a job.
Status of database
SQL> select name, database_role, open_mode from V$database;
NAME DATABASE_ROLE OPEN_MODE
————--------------————————————---—————-
MIRDB PRIMARY READ_WRITE
Reason:
Check the temporary tablespace in both the primary and standby database as same size., In case if the temporary tablespace in standby database is lesser than primary database then after the successful switchover database it may cause the temporary tablespace issue as ora-01652.
Cause of the error:
Changes made to a temporary tablespace do not generate redo, and therefore such modifications are not propagated to the standby database, regardless of the standby_file_management setting.
For example, if a tempfile is resized from 8 GB to 16 GB on the primary database to address insufficient temporary space required by an application workload, this change will not be reflected on the standby. As a result, the standby will continue to retain the original tempfile size.
In such cases, it is essential to manually apply the same tempfile resize on the standby database. Otherwise, after a role transition (switchover or failover), the application may encounter the same temporary space issue again and fail due to insufficient temp segment allocation.
Solution:
Hence whenever the size of a tempfile is increased on the primary database, it is important to perform the same modification on the standby database to avoid issues after role transition.
Check the Pluggable Databases.,
SQL> show pdbs CON_ID CON_NAME OPEN_MODE RESTRICTED —————————————————————————-————--------------------- 2 PDB$SEES READ ONLY NO 3 MIR_PDB1 READ WRITE NO 4 MIR_PDB2 READ WRITE NO
Check the temporary tablespace
SQL> SELECT con_id, tablespace_name, file_name, sum(bytes/1024/1024/1204) from cdb_temp_files group by con_id, tablesapce_name, file_name;
CON_ID | TABLESPACE_NAME | FILE_NAME | SIZE_IN_GB
—————————————————————————-------------------------—
3 | TEMP | /u01/app/oracle/oradata/MIRDB/0SDFFA9D4D52SDSD26309015DED2042/datafile/ o1_mf_temp_grj6jhj_.dbf | .08759231
| |/u01/app/oracle/oradata/MIRDB/datafile/o1_mf_temp_grj6jhj_.tmp 1| TEMP | .08759
Check in Standby database
SQL> select name, database_role, open_mode from V$database; NAME DATABASE_ROLE OPEN_MODE ————----------------------------——————————————-———————- MIRDB PHYSICAL STANDBY READ ONLY WITH APPLY
Check the Pluggable database status.
SQL> show pdbs CON_ID CON_NAME OPEN_MODE RESTRICTED ————————————————————————--------------------------—-———— 2 PDB$SEES READ ONLY NO 3 MIR_PDB1 READ ONLY NO 4 MIR_PDB2 READ ONLY NO
Check the temporary tablespace
SQL> SELECT con_id, tablespace_name, file_name, sum(bytes/1024/1024/1204) from cdb_temp_files group by con_id, tablesapce_name, file_name;
CON_ID | TABLESPACE_NAME | FILE_NAME | SIZE_IN_GB
—————————————————————————------------------------—
3 | TEMP | /u01/app/oracle/oradata/MIRDB_STDBY/0SDFFA9D4D52SDSD26309015DED2042/datafile/ o1_mf_temp_grj6jhj_.dbf | .08759231
| |/u01/app/oracle/oradata/MIRDB_STDBY/datafile/o1_mf_temp_grj6jhj_.tmp 1| TEMP | .08759
Check the standby redo logs are configured in primary database
SQL> SELECT group#,sequence#,status,bytes from v$STANDBY_LOG; GROUP# | SEQUENCE# | STATUS | BYTES ———-----------------------——————————————-———————— 4 0 UNASSIGNED 209715200 5 0 UNASSIGNED 209715200 6 0 UNASSIGNED 209715200 7 0 UNASSIGNED 209715200
Check the same standby logs in Standby database
SQL> SELECT group#, sequence#, status, bytes from v$STANDBY_LOG; GROUP# | SEQUENCE# | STATUS | BYTES ————----------------------——————————————-———————— 4 10 ACTIVE 209715200 5 0 UNASSIGNED 209715200 6 0 UNASSIGNED 209715200 7 0 UNASSIGNED 209715200
Check the Primary and Standby database are in sync.
SQL> SELECT name,role,thread#,sequence#,block# from v$DATAGUARD_PROCCESS; NAME | ROLE | THREAD# | SEQUENCE# | BLOCK# ————-----------------——————————————-—————————————————————— LGWR log writer 0 1056 2359 TMON redo transport monitor 0 1032 9832 TT00 gap manager 0 4321 9831 TT01 redo transport timer 0 8634 983 ARC0 archive local 0 332 87 ARC1 archive redo 0 98648 8692 ARC2 archive redo 0 12982 21 TT02 async ORL multi 0 3455 09 TT03 heartbeat redo informer 0 690 96962 TT04 lastsync ORL Single 10 rows selected.
Note: Verify ASYNC ORL sync on the primary and recovery logmerger on the standby, ensuring the sequence# matches on both sides in primary and standby database.
Now connect with primary database and run the below convert it into the standby database command.
SQL> alter database commit to switchover to physical standby;
Database altered.
Note: After initiating a switchover on the primary, ensure the End-of-Redo (EOR) branch archive log (T-1, S-10) is successfully shipped to and applied on the standby to complete the switchover successfully.
Check the alert log of primary database.
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id:9832] (MIRDB)… (PID: 9832): Active, synchronised Physical Standby switchover target has been identified Switchover End-of-Redo Log thread 1 sequence 10 has been fixed Switchover: Primary highest seen SCN set to OX000000000000038822C 1 00 | 1 | 10 | 0 0 0 1 342213 10 0 1964 10 NET (PID:9832): End-Of-Redo Branch archival of T-I.S-10 NET (PID:9832): LGWR is scheduled to archive to LAD:2 after log switch Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/mirdb/MIRDB/trace/MIRDB_ora_6531.trc NET (PID:9832): Converting the primary database to anew standby database Clearing standby activation ID 543634252 (0x290bc14f) Connect to the Physical Standby database and run the below command for converting it into the Primary database role.
SQL> alter database commit to switchover to primary database; Database altered.
Check the Alert log in Standby database.
alter database commit to switchover to primary 2026-03-20T16:11:24.214590+05:30 ALTER DATABASE SWITCHOVER TO PRIMARY (MIRDB_STDBY) Maximum wait for role transition is 15 minutes..... (PID:4579): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030] Switchover: Complete - Database mounted as primary TMI: kcv_commit_to_so_to_primary Switchover from physical END 2026-03-20 16:11:55.230816
Completed: alter database commit to switchover to primary.
Now start the database in there respective mode after the role transition completed.
SQL> alter database open; SQL> alter pluggable database all open;
Check the status of database.
SQL> select name, database_role, open_mode from V$database;
NAME DATABASE_ROLE OPEN_MODE
———--------------- ————————————---—————
MIRDB PRIMARY READ_WRITE
Check the Pluggable databases
SQL> show pdbs CON_ID CON_NAME OPEN_MODE RESTRICTED ——————————————————————————-—————————————————————— 2 PDB$SEES READ ONLY NO 3 MIR_PDB1 READ WRITE NO 4 MIR_PDB2 READ WRITE NO
Note: – db_unique_name string MIRDB
Therefore start the new standby database in read only mode.
SQ> startup;
ORACLE instance started.
Total System Global Area 1728050736 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
Therefore start the new standby database in read only mode.
Check the standby database status
SQL> select name, database_role, open_mode from V$database; NAME DATABASE_ROLE OPEN_MODE ————————————————-———————-—————————————-———————- MIRDB PHYSICAL STANDBY READ ONLY WITH APPLY
Open all the Pluggable database.
SQL> alter pluggable database all open;
Check the Pluggable database status.
SQL> show pdbs CON_ID CON_NAME OPEN_MODE RESTRICTED ——————————————————————————-————————————————————————————-———— 2 PDB$SEES READ ONLY NO 3 MIR_PDB1 READ ONLY NO 4 MIR_PDB2 READ ONLY NO
-
db_unique_name string MIRDB
Start the MRP Process in Newly standby database.
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
Check the status of MRP Process
PROCESS | STATUS | THREAD# | SEQUENCE# ——————————————————————————-—————————————————— MRP0 | APPLYING_LOG | 1 | 1056 RFS | IDLE | 1 | 1057
Verify the standby is in sync with the primary database.
Note: Verify ASYNC ORL sync on the primary and recovery logmerger on the standby, ensuring the sequence# is identical on both sides of primary and standby database.
Hence check the status
SQL> SELECT role,thread#,sequence#,block# from v$DATAGUARD_PROCESS; ——————————————————————————-————————————————————————————-——————————— log writer redo transport monitor gap manager redo transport timer archive local archive redo archive redo archive redo managed recovery recovery logmerger recovery apply slave recovery apply slave RFS ping RFS async RFS archive RFS archive RFS archive 17 rows selected.