Pages

Friday, June 29, 2018

Exadata Configuration Files Of Compute & Storage Nodes

Exadata Configuration Files Of Compute & Storage Nodes


=>Exadata Compute node:
/opt/oracle - contains dbserver software
/opt/oracle.cellos - Contains all cell OS configuration files, image executables, and so forth
/opt/oracle.exacli - Contains exacli and exadcli executables
/opt/oracle.ExaWatcher - Contains ExaWatcher utilities and output/archive files/directories
/opt/oracle.oswatcher - Contains OSWatcher utilities and output/archive files/directories
/opt/oracle.SupportTools - Contains support and configuration files
/opt/oracle.SupportTools/onecommand - Contains OneCommand files used for Exadata install/config
/etc/oracle/cell/network-config - Contains files that enable compute servers to communicate with storage servers
/etc/oracle/cell/network-config/cellinit.ora - Contains IB IP subnet for storage cells
/etc/oracle/cell/network-config/cellip.ora - Contains IB IP addresses for each storage cell
/u01/app/oracle - Oracle Base
/u01/app/12.1.0.x/grid - Grid Infrastructure Home
/u01/app/oracle/product/12.1.0.x/db - Oracle Database Home

=>Storage Cells:
/opt/oracle - Contains cell software
/opt/oracle.cellos - Contains Exadata cell software and utilities
/opt/oracle.cellos/cell.conf - Contains cell configuration file
/opt/oracle.exacli - Contains exacli and exadcli executables
/opt/oracle.ExaWatcher - Contains ExaWatcher utilities and output/archive files/directories
/opt/oracle.oswatcher - Contains OSWatcher utilities and output/archive files/directories
/opt/oracle.SupportTools - Contains OneCommand files used for Exadata install/config
/var/log/oracle - Contains cell server log files, alerts, and trace files
/var/log/oracle/cellos - Contains log and trace files for Cell Services utilities and cell server startup/shutdown events
/var/log/oracle/diag/asm - Contains log and trace files for cell storage-related events in your cell

Exadata Diagnostic Utilities

Exadata Diagnostic Utilities


-sundiag.sh
/opt/oracle.SupportTools/sundiag.sh

-exawatcher
/opt/oracle.ExaWatcher/GetExaWatcherResults.sh

-ipconfig
/opt/oracle.cellos/ipconf

-CheckSWProfile.sh
/opt/oracle.SupportTools/CheckSWProfile.sh

-verify-topology
/opt/oracle.SupportTools/ibdiagtools/verify-topology

-ibdiagnet
/usr/bin/ibdiagnet

-infinicheck
/opt/oracle.SupportTools/ibdiagtools/infinicheck

-CheckHWnFWProfile
/opt/oracle.SupportTools/CheckHWnFWProfile

-sosreport
/usr/sbin/sosreport

Exadata Utilities

Exadata Utilities


-dbmcli
Command-line administration tool for configuring database servers, and managing objects in the server environment.

-cellcli
Command-line administration tool for Oracle Exadata Storage Server Software

-dcli
Runs commands on multiple compute nodes and cells in parallel threads

-exacli
Command-line administration tool for Oracle Exadata Storage Server Software. It runs on compute nodes and enables you to manage cells remotely.

-exadcli
The exadcli utility runs commands on multiple cells in parallel threads. You can issue an ExaCLI command to be run on multiple cells.

-OEDA
The Exadata Deployment Assistant tool used to gather the information needed to deploy the Exadata Database Machine. The information entered in the tool is used to create the configuration files used to deploy the machine.

-checkip.sh
Run the Network Check script on the network where the machine will be deployed to validate the specified names and IP addresses have been added correctly to the name server, and they do not conflict with the existing network configuration.

-exachk
Exachk utility used to conduct a comprehensive Exadata Health Check on Exadata Database Machine to validate hardware, firmware and configuration.

Unix Interview Questions & Answers

 Unix Interview Questions & Answers


1.Search a file from root directory.(filename = sample.txt)
find / -name sample.txt

2.Display the numbers in sorted order
sort -nu

3.Display the updated lines in file
tail -f filename

4.How to zip a Directory
gzip -r /hom/bea/app

5.How to kill a prosess.
kill -9 pid

6.Display all java process in unix.
ps -ef |grep java

7.How to delete 10 lines in vi editor.
10dd

8.How to search a string and replace tin  vi editor.
:"%s/old string /new string/ gi"

9.How to search a string in vi editor from bottom to top.
?string name

10.How to insert a line above the current line in vi editor .
esc+o

11.How to rename a file .
mv file1 file2

12. copy the content one directory to another directory.
cp -r dir1/file1 dir2/file2

13. How can delete blank lines in directory.
grep -v "^$" sample > temp
mv temp sample

14.How can replace astring in unix.
sed "s/oldstring/newstring/g" filename

15.Syntax for zip and unzip afile.
gzip filename
guzip filename.gz

16.How can display top 10 disk usage files.
du -sh *|sort -nr|head -10

17.How to retrive a field in a file.
cut -f 1,2 stud.

18.what is command search a string in a file.
grep

19).How to goto the end of the line in Vieditor?
G

20).Copy 10 line in vi editor?
10yy

21).How to go to insert mode in vi editor?
Esc+i

22).How to search a string in a file(filename=sample,string=weblogic)?
grep weblogic sample

23).How to copyfile from one unix to other unix system and syntax (file=sample.txt,target host=192.168.11.128,target file path=/home/bea)?
scp -rp sample.txt username@192.168.11.128:/home/bea

24).How to find out CPU utiligation?
top

25).How to execute ascript using nohup(script name=startweblogic.sh)?
nohup ./startWeblogic.sh &

26).Which command is used to search and replace a string?
sed

27)which command is used to search astring in multiple files?
fgrep

28)How to search a string from top to bottom in vi Editor's(String =weblogic)?
 /weblogic

29)how to save and quit from vi Editor's?
a)WQ!

30)what are the Advantages of nohup command?
nohup will execute the process if you layout system.
Syn:nohup &.

31)Differance b/w the ping and tracert?
ping                              
1)It is check the connectivity.  
2)It is display all at a time.           
tracert
1.It is packet information one place to another place destination.
2.It display only 30 hubs in tracert.

32)How to execute unix commands in vi?
:ls

33)tar dir1 dir2 dir3 and new_dir?
syn:tar -cvf new_dir.tar dir1 dir2 dir3.

34)How to display the ipaddress and portnumber?
netstat -anp

35)how to delete directory with recursion and force?
rm -rf filename.

36)How to Open a file with page to page ?
more filename

37)How to Hide a file (file name=tuxedo)?
mv tuxedo .tuxedo

38)How to do undo in vi Editor's?
u

39)How to goto 100 Line in Vi Editor's?
100L

40)How to display last 100 Line from a file?
tail -100 filename

41)How to Reterive the fields from a file?
cut

42)How to zip a directory?(/home/directory)?
gzip -r /home/directory

43)How to go to end of the line in Vi?
G

44)Display the directiory count in current dir?
ls -lrt !wc -l

45)How to appeand data to the existing file?
cat>>filename

46)How to find out the diskspace of the fileSystem?
df -sh

47)write A syntax for the scp Commands?
Scp filename root@ipaddress:filename.

48)How to display the updated lines in file ?
tail -f filename.

49)How to display the ipaddress and portnumber?
netstat -anp

50)which command is used to connect to the remote server?
telenet ipaddress.

51)How to repalce and String in vi Editor's?
sed %s/oldstring/newstring/g

52)How to repalce and String in unix?
sed s/oldstring/newstring/g filename

53)How to display top 10 lines from a file?
head -10 filename.

54)syntax for tar and untar a file?
tar -cvf filename.tar file1 file2 file3.
tar -Xvf filename.tar

55)How to display hiddean a file?
ls -a.

56)How to Delete a blank line from a filename?
grep "^v" sample >temp
mv temp filename

57)How to display all cuurent running process?
ps -ef

58)syntax for zip and unzip file?
gzip filename.
gunzip filename.gz

Weblogic Deployment FAQs

Weblogic Deployment FAQs


Different ways ,modes,types to deploy an application?

-console mode of deployment
-java command line deployment
-wlst deployment
-python deployment
-antscript deployment

Deploying modes?

1.staging modes
2.No stage  mode
3. External stage mode

Types of deployment?

1.side by side deployment
2.Two phase deployment
3.Deployment using plan

Deployment steps for console mode?

1. click on lock & edit
2.click on deployments
3.click on install
4.select location
5.choose targeting style
6.select  deployment targets
7.select security roles and policies and modes
8.activate changes

Deployment steps for java command line deployment?

1.set the class path to execute java commands - setDomainEnv.cmd
2.execute java command for deployment – java weblogic.Deployer
Java weblogic.Deployer  –adminurl  t3://localhost:7001   –username  weblogic  –password  weblogic       –name  benefits  –source  path\benefits.war  –targets ms1,ms2  –deploy

steps for deploying an application using wlst?

1.invoke / connect to wlst-offline – wlst.cmd
2.connect to online- connect(‘weblogic’,’weblogic’,’t3://localhost:7001’)
3.edit ( )
4.startEdit ( )
5.execute - deploy (‘appname’,’path/app.war’,targets=’ms1,ms2’)
6.activate( )
7.disconnect( )

Steps for deploying  an application using ant script?

1.create   build.xml file
2.write  script in it.
3.execute command  ant  deploy {if we save the file name as build.xml}
Ant  –f  filename.xml {if the file name is created with filename.xml}
6.Auto Deployment?
1.copy the application in to Autodeployment folder,which is present in c:bea/user_projects/domains/domain1/servers/admin/autodeloyment
2.Now Run the admin server .
3. The application automatically deployed in admin server.

Different modes of deployment?

-Staging mode:  The admin server copies the deployment unit files to the staging directories of target servers and they are deployed using the local copy.
This mode is useful when deploying  small or moderate size applications.
This is default staging mode for managed servers .

-No stage mode:   the deployment units are deployed using the same physical copy  which must accessible by the admin server and target servers
This mode is useful when deploying large  applications to multiple targets
This is default staging mode for adminservers.

-External stage: we must copy the deployments units manually to the correct staging directories  before deployment.
We will use this mode for deployments when we want to manually control the distribution of deployment files to target servers.

Side by side deployment  ?

Use the weblogic server to redeploy a new version of a production application without interrupting the availability of application to new client requests.
This way the new client get connected to the new version of the application and the previous version  of the application is till in use bythe older clients and gets retired after the client disconnects.

How to clear weblogic cache?

Steps :
1.shut down the server.
2.delete the contents of the folder:
c\bea\user_projects\domains\domain1\servers\server_name\tmp\wl_user
3.Restart the server.

what happens if we don’t  clear the cache?

If we are redeploying an application or deploying a new version of same application, weblogic  server look up in the above directory.if there are older objects persists that will be conflict with new code objects.

Difference between .jar .war and .ear files?
.jar:  java archive files intended to hold generic libraries of java classes ,Resources,Auxillary  files etc.
Used in J2ee for packaging EJB’s  & client side java applications.
.war : web archive files intended to contain complete web applications.
Specially for web applications made from servlets , jsp’s supporting classes.
.ear : Enterprise archive files intended to contain complete enterprise applications.
Contains all of the components     that make up a particular  J2EE application.

what is two phase deployment?

There are two states in two phase deployment
They are
1.prepare state
2.active state
Deployment first prepares the application across all the target servers and then activates the application in a separate phase.
If  deployment of an application fails in either of the two phases then the cluster deployment is failed.

What are deployment descriptors?

It is a configuration file for web application or EJB application which is to be deployed to web or EJB container.
Deployment descriptors describes the deployment settings of an application or module or component.
It contains meta data describing the contents and structure of the enterprise beans, and runtime transaction and security information for EJB container.
It directs a deployment tool to deploy a module or application with specific container options and describes specific configuration requirements that a deployer must resolve.

Deployment process in current organization?

1.Developers develop the code,check into svn (sub version number)
2.Build team build the code using Hudson tool.
3.War,Ear files will be generated after the build.
4.we will stage these files from windows machine to unix machine using winscp tool.
5.once staging is complete we will clear weblogic servers cache .
6.next we will trigger the deployment using wlst script.
7.deployment will complete automatically ,we will get mail alert for success or failure status.
8. if in case of deployment failures ,we will check log files and fix issues.

Deployment issues?

1.Code issues
2.Out of memory issues
3.Connection pool issues
4.Clear cache issues
5.Library class path issues
6.Applications are not deployed

How to create boot.properties in weblogic

How to create boot.properties in weblogic


Today we are going to learn on how to create boot.properties fil in weblogic. Using boot.properties, you can control the server startup without passing the username and password.
 
1. Create a boot.properties file.
Create a plain text file called boot.properties with the following content:
username=weblogic
password=weblogic01

2. Copy the boot.properties to the following location:
/usr/local/weblogic103/user_projects/domains/devdomain/servers/<server_name>/security

About Nodemanager In Weblogic

About Nodemanager In Weblogic


Node Manager is a WebLogic Server utility that enables you to start, shut down, and restart Administration Server and Managed Server instances from a remote location.

A Node Manager process is not associated with a specific WebLogic domain but with a machine.
You can use the same Node Manager process to control server instances in any WebLogic Server domain, as long as the server instances reside on the same machine as the Node Manager process.
Node Manager must run on each computer that hosts WebLogic Server instances whether Administration Server or Managed Server that you want to control with Node Manager.

WebLogic Server provides two versions of Node Manager, Java-based and script-based, with similar functionality. However, each version has different configuration and security considerations.

Java-based Node Manager

Java-based Node Manager runs within a Java Virtual Machine (JVM) process.
This version of Node Manager determines its configuration from the nodemanager.properties file.
Java-based Node Manager provides more security than the script-based version.

Script-based Node Manager

For UNIX and Linux systems, WebLogic Server provides a script-based version of Node Manager.
This script is based on UNIX shell scripts, but uses SSH for increased security. SSH uses user-id based security
This version does not provide as much security as the Java-based version.
The advantage of the script-based Node Manager is that it can remotely manage servers over a network that has been configured to use SSH. No additional server installation is required. The scripts merely have to be copied to the remote machine.

Q)What You Can Do with Node Manager

Start, Shut Down, and Restart an Administration Server
Start, Shut Down, Suspend, and Restart Managed Servers
Monitor Servers and View Log Data

Starting Java-based Node Manager
Start scripts for Node Manager are installed in the WL_HOME\server\bin directory, where WL_HOME is the top-level installation directory for WebLogic Server.
Use startNodeManager.cmd on Windows systems and startNodeManager.sh on UNIX systems.
The scripts set the required environment variables and start Node Manager in WL_HOME/common/nodemanager. Node Manager uses this directory as a working directory for output and log files.

Note:
You can use the same nodemanager process to control server instances in any WebLogic Server domain, as long as the server instances reside in same machine as the Node Manager Process.

Q)Node Manager Types
Basically, there are 4 types of node managers available in weblogic
PLAIN
SSL
SSH
RSH

Accessing the Node Manager
You can access the Node Manager from the following clients:
Administration Console - From the Environments -- Machines -- Configurations-- Node Manager (Tab)

Q)Important Node Manager Configuration Files ?

-nodemanager.properties
This is the configuration file used by the Java-based version of Node Manager.
This file is located in WL_HOME/common/nodemanager.

-nodemanager.hosts
This file contains a list of all the trusted hosts that can issue commands to Node Manager.
This file is located in WL_HOME/common/nodemanager.

-nodemanager.domains
This file contains mappings between the names of domains managed by Node Manager and their corresponding directories.
This file is located in WL_HOME/common/nodemanager.

-nm_data.properties
This file stores the encryption data the Node Manager uses a symmetric encryption key. The data is stored in encrypted form.
This file is located in WL_HOME/common/nodemanager.

-nm_password.properties
This file stores a username/password pair specific to the Node Manager server that is managing this domain. This is known as the Node Manager secret. The username and password are appended to a salt value (obtained from the SerializedSystemIni.dat of the domain) and SHA-hashed.

This file is located in DOMAIN_HOME/config/nodemanager.

-boot.properties
Node Manager uses this file to specify a boot identity when starting a server. This file is located in domain-name/servers/server_name/data/nodemanager.

-startup.properties
Each Managed Server instance has its own startup.properties file with properties that control how Node Manager starts up and controls the server. Node Manager automatically creates this file by using properties passed to Node Manager when the Administrative Server was last used to start the server. This allows a Node Manager client or startup scripts to restart a Managed Server using the same properties last used by the Administrative Server.
These properties correspond to the server startup attributes contained in ServerStartMBean and the health monitoring attributes in ServerStartMBean.

-server_name.addr
This file is located in domain-name/servers/server_name/data/nodemanager.
server_name.addr stores the IP address added when a server starts or is migrated. This file is generated after the server IP address is successfully brought online during migration. server_name.addr is deleted when the IP address is brought offline. The server IP address is used to validate remove requests to prevent addresses being erroneously removed while shutting down the server.
This file is located in domain-name/servers/server_name/data/nodemanager.

-server_name.lck
server_name.lck is generated by each server and contains an internally used lock ID.


Tuesday, June 26, 2018

OPP: Output Post Processor TroubleShooting

OPP: Output Post Processor TroubleShooting


Issue 1

Output Post Processor is Down with Actual Process is 0 And Target Process is 1

1. Shutdown Concurrent Managers
2. To ensure concurrent manager down; check there is no FNDLIBR process running.
ps -ef | grep FNDLIBR
3. Run adadmin to relink FNDSVC executable.
a. Invoke adadmin from command prompt
b. Choose option 2 (2. Maintain Applications Files menu)
c. Choose option 1 (1. Relink Applications programs )
d. Then type FND When prompted; ( Enter list of products to link (all for all products) [all] : FND )
e. Ensure adrelink is exiting with status 0
4. Start Concurrent Managers
5. Check the Output Post Processor

Issue 2

Concurrent Processing  R12 Output Post Processor Service Not Coming Up 

Reason :
If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under Concurrent ->Manager ->Define form. If the Service Manager is not present/defined for a particular node,then this causes all the services provided by Service Manager like OPP,WF etc.. not to work.

1. Shutdown all the services.
Below Step 2 will create  Service Manager FNDSM
2. Log in as applmgr
cd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql
3. Relink FNDSM and FNDLIBR executables as mentioned below:
$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"
4. Run cmclean.sql
5. Start up the managers/services

Issue 3

How to restart OOP

Using System Administrator responsibility  navigate to
Concurrent-->Manager-->Administer ->query for the Output Post Processor
The Actual should equal  to the Target processes (eg: Actual=2 Target =2)

Deactivate and Activate

Alternatively you can click on Restart.
If not stopped/deactivated please check the processes and kill  and try to start
ps -ef |grep $LOGNAME | grep -v grep | grep FNDOPP

Issue 4

Output Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space

- Determine what the heap size per OPP process is currently:

select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

- The default should be:
J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m
- Increase the Heap Space per Process to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
- Bring the Concurrent managers down.
-Run cmclean.sql script from Note 134007.1 - CMCLEAN.SQL Non-Destructive Script to Clean Concurrent Manager Tables.
-Bring the managers up again.

OR

-Log into applications with the System Administrator responsibility.
- Navigate to Concurrent -> Program -> Define
-Query the XML Publisher Template Re-Generator program
-Set the following value for the Executable Options: -Xmx1024m
- Save changes.
- Retest the program.

Issue 5

Output Post Processor is Down with Actual Process is 0 And Target Process is 1

This can happen on a cloned instance.

- FNDSVC should exist under FND_TOP/bin
- Bring down all application services and relink the FNDSVC through adadmin or using the below command:
adrelink.sh force=y ranlib=y "FND FNDSVC"
- Restart all applications services and restest the issue.

Issue 6

Output Post Processing Fails Due To java.lang.ThreadDeath

-Increase the value of the Concurrent:OPP Timeout profile option to 10800 seconds.
-Enable the scalability feature of XML Publisher:
a. Login as SYSADMIN
b. Responsibility: XML Publisher Administrator
c. Function: Administration
d. Set the following properties:
e. Temporary Directory
f. Use XML Publisher's XSLT processor: True
g. Enable scalable feature of XSLT processor: True
h. Enable XSLT runtime optimization: True
- Restart the Concurrent Managers so that changes take effect

Issue 7

Timeout issue with OPP

Solution:

There are 2 new profiles options that can be used to control the timeouts.

Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its request for post processing.

Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually process the request.
The value for the above profile options can be increased to avoid timeouts.
The number of processes/threads for OPP can also be increased; however the concurrent manager has to be restarted for the changes to take effect.

Some use full Note IDs

BI Publisher - Troubleshooting Oracle Business Intelligence (XML) Publisher For The Oracle E-Business Suite (Doc ID 364547.1) ****
How to Determine the Version of Oracle XML Publisher for Oracle E-Business Suite 11i and Release 12 (Doc ID 362496.1)
Concurrent Requests Fail Due to Output Post Processing (OPP) Timeout (Doc ID 352518.1)
OPP Manager Error 'ORA-600 [kqludp2]' Unable To Publish XML Reports (Doc ID 1083510.1)
Problem with OPP Where Getting XML Output Instead of PDF (Doc ID 1178457.1)
EEO Output Post Processor errors: OPP Process Timeout and OPP Response Timeout (Doc ID 458242.1)
R12 - Output Post Processor (OPP) is Down With Actual Process is 0 And Target Process is 1 (Doc ID 1075598.1)
Output Post Processor (OPP) Manager Not Working Error: APPLTMP Environment Variable is not Set (Doc ID 1381474.1)
Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)

Sunday, June 24, 2018

How to check the Grid Infrastructure version

How to check the Grid Infrastructure version


[root@DELLDB ~]#  crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]

[root@DELLDB ~]#   crsctl query crs releaseversion 
Oracle High Availability Services release version on the local node is [11.2.0.3.0]

[root@DELLDB ~]#    crsctl query crs softwareversion
Oracle Clusterware version on node [DELLDBrac1] is [11.2.0.4.0]

What does crsctl query crs activeversion mean ?
If some nodes become unreachable in the middle of an upgrade, then you cannot complete the upgrade, because the upgrade script (rootupgrade.sh) did not run on the unreachable nodes. Because the upgrade is incomplete, Oracle Clusterware remains in the previous release version.

What does crsctl query crs releaseversion mean ?
Use the crsctl query crs releaseversion command to display the version of the Oracle Clusterware software stored in the binaries on the local node.

What does crsctl query crs softwareversion mean ?
Use the crsctl query crs softwareversion command to display latest version of the software that has been successfully started on the specified node.

Difference between crsctl start crs & crsctl start cluster while starting the cluster

Difference between crsctl start crs & crsctl start cluster while starting the cluster


crsctl start crs : To start the entire Oracle Clusterware stack on a node, including the OHASD process,which is responsible for starting up all other cluserware processes . This command is to be used only on the local node..

crsctl start cluster : To start Oracle Clusterware stack on local node . It does not include the OHASD process.

Saturday, June 23, 2018

How to resize temporary tablespace

How to resize temporary tablespace


step.1

Following query will give you tablespace name and datafile name along with path of that data file.
sql> select file_name, tablespace_name from dba_temp_files;

step.2

Following query will create temp tablespace named: 'temp2' with 800 MB size along with auto-extend and maxsize unlimited.
sql> create temporary tablespace temp2 tempfile  '/u01/oradata/temp_02.dbf' size 800m autoextend on next 10m maxsize unlimited;

step.3

Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )
sql> alter database default temporary tablespace temp2;

step.4

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.
sql> select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#, a.username,a.osuser, a.status from v$session a,v$sort_usage b WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.
sql> alter system kill session 'sid, serial#';
For example:
sql> alter system kill session '59,57391';

step.5

Now, we can drop old temporary tablespace without any trouble with following:
sql> drop tablespace temp2 including contents and datafiles;

If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

Sunday, June 3, 2018