How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g

How to drop and recreate UNDO Tablespace in Oracle 9i/10g/11g?

Solution:

1. Determine the size of your undo tablespace

SQL> select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES)/1024/1024/1024
-------------------------
                12.09375
               
2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.
SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 400M;

    Tablespace created.
   
3. Edit your init.ora file and change the parameter "undo_tablespace=UNDOTBS2" so it points to the newly created tablespace.
    Change undo_tablespace=UNDOTBS2
    Change undo_management=MANUAL

    Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.
   
4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.   
5. Startup the database
6. Confirm the new tablespace is in use:

    SQL> show parameter undo_tablespace

    NAME                 TYPE              VALUE
    -------------        -----------       ------------
    undo_tablespace      string            UNDOTBS2
   
7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.
    SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

    OWNER         SEGMENT_NAME         TABLESPACE_NAME         STATUS
    ------              --------------------------- ------------------------------ -----------
    PUBLIC         _SYSSMU3$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU2$                      UNDOTBS1                         OFFLINE
    PUBLIC         _SYSSMU19$                     UNDOTBS2                         OFFLINE

    ....etc.

    If the old segments are online, then they must be taken offline:

    SQL>alter rollback segment "_SYSSMU3$" offline;
    SQL>alter rollback segment "_SYSSMU2$" offline;

    This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:
    SQL>drop tablespace UNDOTBS1 including contents and datafiles;

    Tablespace dropped.

9. Edit your init.ora file do the following changes.

    undo_management='AUTO'
    undo_tablespace='UNDOTBS2'
   
10. Shutdown the database (shutdown immediate) and restart it.

11. Monitor the alert log simultaneously during all steps.

Reference Metalink Note: [ID 431652.1]

"Error Occurred While Attempting to Establish an Applications File Server Connection"

Error:

" An error occurred while attempting to establish an Applications File Server connection. There may be a network configuration problem, or the TNS listener may not be running on "
Solutions to Check

1. Is this a Multinode Install or a Single Node Install.
- If this is a Multinode Install, you will need access to the Middle Tier and the Backend Tier (Concurrent Processor) 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora files

- If this is a Single Node Install, you need access to the Concurrent Processor 8.0.6 ORACLE_HOME/network/admin/tnsnames.ora file

2. Run a report, is there actually a Report Output and Log file being produced at the Operating System level?
- Check the $APPLCSF/out and the $APPLCSF/log directories for the Request_ID out and log file
- If there are no log and output file being produces there Check the permissions on those directories (APPLMGR should have write)
- If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs

3. Confirm that there are 2 listener processes actively running for the Instance that you cannot view reports on -- 1 from 8.0.6 and from 8.1.6 directory structures
- type the following:
ps -ef | grep tnslsnr
- If there is only 1 listener running from the 8.1.6 directory structure
That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener
- To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env)
c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script:

adalnctl.sh start APPS_SID

(you can confirm the FNDFS Listener name from viewing the 8.0.6 ORACLE_HOME/network/admin/listener.ora file)

4. Check the actual machine name for the Concurrent Processor Server
- type the following:
uname -n

5. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen in the System Administrator Responsibility The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
- If it is the same then continue with next step
- If it is not the same,
Shutdown Concurrent Managers and update the FND table:
a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop
c. Connect to SQL*PLUS apps/apps

d. SQL > select target_node from FND_CONCURRENT_QUEUES;
--------------------
This may return the name of an old machine

e. SQL > update FND_CONCURRENT_QUEUES set target_node='' where target_node='';

f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start

6. In the 8.0.6 TNSNAMES.ora file, look at the FNDFS_ connection string entry.
Is the hostname value the same as is in questions 4?
- The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora
a. 1 with the FNDFS_
b. 1 with the FNDFS_
c. 1 with the FNDFS_
- The one that is the correct entry is FNDFS_ (hostname being what is returned from a "uname -n")
- If this file does not contain the correct Hostname value
Make a backup of it and edit it to change the FNDFS entry.

7. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install.
- At the Operating System Level
a. tnsping FNDFS_
- This should return the Hostname name and Port information identical to the FNDFS entry in the TNSNAMES.ora file

8. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility

CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
- Viewer: Text can Be set to "browser" to view with Netscape or IE

9. This step should be performed in a case where there might have been patches applied and possibly the FNDFS executable was not Relinked, or it may be missing from the file system or corrupted.
- Shutdown the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh stop APPS_SID
- Rename or Move the Current FNDFS executable before relinking
a. $FND_TOP/bin
mv FNDFS FNDFS.bak
b. As APPLMGR account user
adrelink.sh force=y "fnd FNDFS"
- Start the FNDFS Listener
a. $OAD_TOP/admin/scripts/adalnctl.sh start APPS_SID

Metalink Note : ID 117012.1