How to move the Oracle temp file to new location?

The purpose of this topic is to explain how to move the temp files from one file system to another file system with out impacting the application. The post is tested in Oracle 11.2.0.4.

The temp files are located at /orasys/oradata The goal is to move this temp files to dedicated file system  /oratemp/oradata

Step1 Copy the parsed temp file to new location as non parsed file.

cp --sparse=never /orasys/oradata/temp01.dbf /oratemp/oradata/temp01.dbf

Step2 create the temp file with new temp files
alter tablespace temp add tempfile '/oratemp/oradata/temp01.dbf' reuse;

Step3 Drop the temp files on the original location.

ALTER DATABASE TEMPFILE '/orasys/oradata/temp01.dbf' DROP INCLUDING DATAFILES;

Pre allocate space for Oracle temp files

Temporary tablespaces use sparse files  meaning that the actual file created on disk is initially very small and grows to the specified size only as data is written to the file.

Let us say, the file system size is 100G and day shift DBA added 30G temp file on the file system.  But we all know that, temp file will create with very small size initially and  it will grow only if oracle started using the temp segment. 

Again, night shift DBA joined the duty and he saw that, the file system free space shows 99G free space. He added 3 data files each 30G size and he filled up 90G on the file system. A few hours later, oracle started using the temp segment and gradually growing the temp space and exceeding the file system size which puts Database under risk. We are cheated to believe that there is more than enough free disk space.

It is always best practice to use dedicated file system for temp files.  Especially, Data Guard, it is highly important to use dedicated file system. 

How do we resolve this issue?  How do we create non sparse temp files at the beginning of creating temp file?

Option 1

Step 1 Create the sparse temp file, say temp01.dbf

alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 Execute the Cartesian join query as below to fill up the temp file

select * from dba_source a, dba_source b, dba_source c, dba_source d order by 1,2,3,4

Wait for ORA-01652:unable to extend temp segment by 128 in tablespace TEMP

Step 3 Copy the non-parsed temp file to another file called temp02.dbf

cp temp01.dbf temp02.dbf

Step 4 create the another temp file

Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Now we have no-parse temp files for both temp01.dbf and temp02.dbf


Option 2

Step 1 Create the sparse temp file, say temp01.dbf
alter tablespace temp add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf' ;

Step 2 copy the non-parsed temp file to another file called temp02.dbf
cp --sparse=never temp01.dbf temp02.dbf

Step 3 create the another temp file
Alter tablespace temp add tempfile ‘temp02.dbf’ reuse

Step 4 Drop the parsed temp file temp01.dbf
alter database tempfile ‘temp01.dbf’ drop including datafiles;


Repeat the step for adding another non-parsed temp files

Option 3

Create a normal datafile (via a transitory tablespace) and then add it to the temporary tablespace using the REUSE clause as follows:

create tablespace test_tbs  datafile '/oratemp/oradata/chfsdb20/temp01.dbf'  size 20480m;


Drop the tablespace without removing data files from file system

drop tablespace test_tbs;

alter tablespace TEMP add tempfile '/oratemp/oradata/chfsdb20/temp01.dbf'   reuse;

How to activate the Oracle11g standby database

This exercise was tested in oracle11gR2 version. When primary database went down and database is not coming up with in the expected time frame.  In this scenario, we need to bring the standby up with READ/WRITE mode.

Here is the steps. Login to standby database and run the below steps.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

Once activated, then we need rebuild the old primary as a standby database or flash back the database and apply recent archive log files.

Cloning an existing ORACLE_HOME on Unix

Before we go further into this section, we need to understand the purpose of cloning and advantage of cloning the Oracle home binaries.

At what circumstance, cloning  oracle home is useful?

1) When you setup the standby database, you don't want to install the oracle binary from the scratch. You might install some extra component or you might miss some component which is not in primary database.  The best practice, it is always primary and standby should be Apple to Apple. Binary cloning will be appropriate for setting up the standby database server.

2) If you are performing multiple oracle database installations on several server as Production, Pre-production, Development, QA and Test, then you may want to use binary cloning to make sure you install same component across the environment.  This approach is much faster than installing from the scratch and then applying  PSU/CPU patch on each server.

Source Database : devdb12
Target Database : devdb13

The Binary version is 11.2.0.4
Oracle home : /ora/app/oracle/product/11.2.0/db_4

Step 1(Clone the binary in source database)

Login to source database:

cd /ora/app/oracle/product/11.2.0

tar -cvf oracle_110204_binaries.tar db_4

check  tar -tvf /ora/app/oracle/product/11.2.0/oracle_11204_binaries.tar |more

The above command ensure the binaries are zipped.

scp the oracle_11204_binaries.tar file to devdb13 under /ora/app/oracle/product/11.2.0/

On step1,  oracle binaries are cloned and copied to target server.

Step 2(Untar the binaries in target server)

Login to devdb13

Extract Oracle 11.2.0.4 binaries

cd /ora/app/oracle/product/11.2.0

tar -tvf oracle_11204_binaries.tar|more

tar -xvf oracle_11204_binaries.tar

Delete or move the oracle_11204_binaries.tar file if you need to.

cd /ora/app/oracle/product/11.2.0

mv oracle_11204_binaries.tar /work/exports

Step 3 (create the clone script)

cd /homo/oracle

cat clone.sh
#!/bin/sh
echo "Clone started at `date`" | tee -a clone.log
perl /ora/app/oracle/product/11.2.0/db_4/clone/bin/clone.pl ORACLE_BASE=/ora/app/oracle ORACLE_HOME=/ora/app/oracle/product/11.2.0/db_4 ORACLE_HOME_NAME=OraDb11g_home4 '-O"LOCAL_NODE=devdb13.acsrr.com"'
echo "Clone ended at `date`" | tee -a clone.log

Step 3 (Clone the binary)

nohup ./clone.sh &

Here is the cloning log screenshot, We need to execute root,sh as requested in the log file.

Unix Admin can run the  root.sh as a root user. Unix admin can
also provide sudo privilege to oracle account and DBA can run
this script.

Here is the log for executing root.sh.

sh /ora/app/oracle/product/11.2.0/db_4/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_4

Enter the full pathname of the local bin directory: [/usr/local/bin]:

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.