How To create and associate a temporary file with a temporary tablespace on a read-only physical standby database

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:

How To create and associate a temporary file with a temporary tablespace on a read-only physical standby database

Symptoms:
Create a Temporary Tablespace
To create temporary tablespace files, find the temporary tablespaces from the dba_tablespaces view. For all of the temporary tablespaces defined in the primary database, datafiles will need to be added in the logical standby database. This is because the temporary tablespace files are not backed up or copied to the standby site


Solution:



To create a temporary tablespace for use on a read-only physical standby database

If you did not have a temporary tablespace on the primary database when you created the physical standby database, perform the following steps on the primary database:
1. Enter the following SQL statement:
2. SQL> CREATE TEMPORARY TABLESPACE temp1
3. TEMPFILE '/disk1/oracle/dbs/temp1.dbf'
4. SIZE 20M REUSE
5. EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
6.
2. Switch the log to send the redo data to the standby database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
To create and associate a temporary file with a temporary tablespace on a read-only physical standby database

The redo data that is generated on the primary database automatically creates the temporary tablespace in the standby control file after the archived redo log is applied to the physical standby database. However, even if the temporary tablespace existed on the primary database before you created the physical standby database, you must use the ADD TEMPFILE clause to actually create the disk file on the standby database.

On the physical standby database, perform the following steps:
1. Start managed recovery, if necessary, and apply the archived redo logs by entering the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

2. Cancel managed recovery and open the physical standby database for read-only access using the following SQL statements:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;

Opening the physical standby database for read-only access allows you to add a temporary file. Because adding a temporary file does not generate redo data, it is allowed for a database that is open for read-only access.
3. Create a temporary file for the temporary tablespace. The size and names for the files can differ from the primary database. For example:
SQL> ALTER TABLESPACE temp1
ADD TEMPFILE '/disk1/oracle/dbs/s_temp1.dbf'
SIZE 10M REUSE;

Get Oracle Certifications for all Exams
Free Online Exams.com