Useful Sqlplus tips

I want to share here the sqlplus tips i found in my daily life activities. I will keep this post open to share those tips and i will do my best to update this thread regularly.

Tips:

1-Replace the SQL> Prompt with the Current User Name

we can do this by using  set sqlprompt '_user> '

Example:

oracle@svtim02:[BSCSUAT] $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 4 14:54:31 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> set sqlprompt '_user> '

SYS>

SYS>

How to install OS watcher to monitor your OS and Submit the result to the support

I will try to explain today how to install and configure OS watcher that you must do on all your environment because it helps oracle support during the support for critical issues when you submit SR to them.
Below is the download link to the tool and in order to install it you must have acess to Oracle support website using your own username and password.
Download Link for the tool

after you download the tool you will have a folder which looks like oswebb730.tar.Upload this folder to your database machine.In case of RAC systems please upload it to all local nodes that are member of the cluster.
In my case i copied the file to /home/oracle/dbascripts/OSWATCHER.I then converted the tar file to a normal file using the below command:

oracle@svprtldb02:~/dbascripts/OSWATCHER$ tar -xvf oswbb730.tar
x oswbb, 0 bytes, 0 tape blocks
x oswbb/data, 0 bytes, 0 tape blocks
x oswbb/docs, 0 bytes, 0 tape blocks
x oswbb/docs/The_Analyzer, 0 bytes, 0 tape blocks
x oswbb/docs/The_Analyzer/OSWatcherAnalyzerOverview.pdf, 393822 bytes, 770 tape blocks
x oswbb/docs/The_Analyzer/oswbbaUserGuide.pdf, 2095591 bytes, 4093 tape blocks
x oswbb/docs/The_Analyzer/oswbba_README.txt, 29099 bytes, 57 tape blocks
x oswbb/docs/OSWatcher, 0 bytes, 0 tape blocks
x oswbb/docs/OSWatcher/oswbb_README.txt, 13481 bytes, 27 tape blocks
x oswbb/docs/OSWatcher/OSWatcherUserGuide.pdf, 3195426 bytes, 6242 tape blocks
x oswbb/Exampleprivate.net, 1864 bytes, 4 tape blocks
x oswbb/nfssub.sh, 740 bytes, 2 tape blocks
x oswbb/stopOSWbb.sh, 558 bytes, 2 tape blocks
x oswbb/call_du.sh, 67 bytes, 1 tape blocks
x oswbb/iosub.sh, 743 bytes, 2 tape blocks
x oswbb/profile, 0 bytes, 0 tape blocks
x oswbb/OSWatcherFM.sh, 4424 bytes, 9 tape blocks
x oswbb/ifconfigsub.sh, 772 bytes, 2 tape blocks
x oswbb/ltop.sh, 1486 bytes, 3 tape blocks
x oswbb/mpsub.sh, 542 bytes, 2 tape blocks
x oswbb/call_uptime.sh, 71 bytes, 1 tape blocks
x oswbb/gif, 0 bytes, 0 tape blocks
x oswbb/psmemsub.sh, 1445 bytes, 3 tape blocks
x oswbb/tar_up_partial_archive.sh, 4219 bytes, 9 tape blocks
x oswbb/oswnet.sh, 435 bytes, 1 tape blocks
x oswbb/vmsub.sh, 544 bytes, 2 tape blocks
x oswbb/call_sar.sh, 68 bytes, 1 tape blocks
x oswbb/oswib.sh, 414 bytes, 1 tape blocks
x oswbb/startOSWbb.sh, 2385 bytes, 5 tape blocks
x oswbb/Example_extras.txt, 626 bytes, 2 tape blocks
x oswbb/oswsub.sh, 524 bytes, 2 tape blocks
x oswbb/analysis, 0 bytes, 0 tape blocks
x oswbb/oswbba.jar, 235115 bytes, 460 tape blocks
x oswbb/locks, 0 bytes, 0 tape blocks
x oswbb/tmp, 0 bytes, 0 tape blocks
x oswbb/OSWatcher.sh, 34522 bytes, 68 tape blocks
x oswbb/topaix.sh, 537 bytes, 2 tape blocks
x oswbb/tarupfiles.sh, 746 bytes, 2 tape blocks
x oswbb/xtop.sh, 1486 bytes, 3 tape blocks
x oswbb/src, 0 bytes, 0 tape blocks
x oswbb/src/Thumbs.db, 10240 bytes, 20 tape blocks
x oswbb/src/OSW_profile.htm, 26288 bytes, 52 tape blocks
x oswbb/src/tombody.gif, 4175 bytes, 9 tape blocks
x oswbb/src/missing_graphic.gif, 8694 bytes, 17 tape blocks
x oswbb/src/coe_logo.gif, 2994 bytes, 6 tape blocks
x oswbb/src/watch.gif, 5621 bytes, 11 tape blocks
x oswbb/src/oswbba_input.txt, 135 bytes, 1 tape blocks
x oswbb/oswrds.sh, 825 bytes, 2 tape blocks


I then moved to the directory oswebb as per below:
/dbascripts/OSWATCHER/oswbb

Then run the below command to start the oswatcher:
 nohup ./startOSWbb.sh  30 168 gzip 2>/dev/null



The above command will keep the oswatcher running using nohup.The 30 is the interval per snapshot which means that every 30 seconds the os watcher will take snapshot.the maximum recommended time per oracle is 60 seconds between each snapshot.

The 168 will keep the data for 1 week.

the gzip will gzip all the files generated by the oswatcher.

Truncate table CASCADE is now avilable in 12c

Starting from 12c you can now truncate the table and all the tables referring to that table with referential integrity.

This new feature is only available in 12c. This new enhancement gets ride of the limitation existed in earlier releases.Starting from 12c you dont need to delete child tables before you delete the master table as you can now do it in one command as per below:

SQL> TRUNCATE TABLE mytable CASCADE; 

Cloning Grid control Oracle Agnet from running machine to a newly added clustered node

When you add a new node to the cluster then you need to install a new agent for the grid control on those new nodes.
These steps will help you clone already patched home and avoid the new installation procedures in few minutes.
To do so you have to follow the below steps:
On the Source machine:
tar cvf /{full path for the tar file to be created including the file name} {files or folders to be archived "Oracle Grid Home"}
After the tar file created then gzip it as below:

gzip agent11.tar
-bash-3.2$ ls
10.2                 agent10g.tar.gz      agent11g             stage
agent10g             agent11.tar.gz       oraInventory.tar.gz
-bash-3.2$ scp -rp agent11.tar.gz oracle@10.195.2.13:/app/portal/oracle/oracle
agent11.tar.gz       100% |************************************************************|   588 MB    00:51


On the destination machine:

set your desired time zone as below:

export TZ=Etc/GMT-2

Then install the home as below:

-bash-3.2$ ./runInstaller -clone -forceClone ORACLE_HOME=/app/portal/oracle/oracle/product/agent11g ORACLE_HOME_NAME=agent11g1 -silent b_chainedInstall=false oracle.sysman.top.agent:s_installType=AGENT OMS_HOST=csemp.bss.etisalat.eg OMS_PORT=4889 -local  CLUSTER_NODES={portaldb3}

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 29258 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-03-23_02-54-56PM. Please wait ...-bash-3.2$ Oracle Universal Installer, Version 11.1.0.8.0 Production

Copyright (C) 1999, 2010, Oracle. All rights reserved.

You can find the log of this install session at:

 /app/portal/oracle/oracle/oraInventory/logs/cloneActions2014-03-23_02-54-56PM.log

.................................................................................................... 100% Done.

Installation in progress (Sunday, March 23, 2014 2:55:06 PM EET)

...............................................................Install successful

Linking in progress (Sunday, March 23, 2014 2:55:13 PM EET)

.                                                                64% Done.

Link successful

Setup in progress (Sunday, March 23, 2014 2:55:58 PM EET)

......................                                          100% Done.

Setup successful

End of install phases.(Sunday, March 23, 2014 2:56:19 PM EET)

Starting to execute configuration assistants

Configuration assistant "Oracle Configuration Manager Clone" succeeded

Configuration assistant "Agent Configuration Assistant" succeeded

WARNING:

The following configuration scripts need to be executed as the "root" user.

#!/bin/sh

#Root script to run

/app/portal/oracle/oracle/product/agent11g/root.sh

To execute the configuration scripts:

    1. Open a terminal window

    2. Log in as "root"

    3. Run the scripts

The cloning of agent11g1 was successful.

Please check '/app/portal/oracle/oracle/oraInventory/logs/cloneActions2014-03-23_02-54-56PM.log' for more details.

Configuring the agent:
-bash-3.2$ ./agentca -f -c "{portaldb3}"

Stopping the agent using /app/portal/oracle/oracle/product/agent11g/bin/emctl  stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
Running agentca using /app/portal/oracle/oracle/product/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/app/portal/oracle/oracle/product/agent11g ACTION=Configure MODE=Perform RESPONSE_FILE=/app/portal/oracle/oracle/product/agent11g/response_file RERUN=TRUE INV_PTR_LOC=/app/portal/oracle/oracle/product/agent11g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform - mode is starting for action: Configure


Perform - mode finished for action: Configure

You can see the log file: /app/portal/oracle/oracle/product/agent11g/cfgtoollogs/oui/configActions2014-03-26_10-31-47-AM.log
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 29236 MB    Passed
The inventory pointer is located at /app/portal/oracle/oracle/product/agent11g/oraInst.loc
The inventory is located at /app/portal/oracle/oracle/oraInventory
'UpdateNodeList' failed.

Starting the agent using /app/portal/oracle/oracle/product/agent11g/bin/emctl  start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ............................ started.

Stopping the agent using /app/portal/oracle/oracle/product/agent11g/bin/emctl  stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

Running Agent Addon Configuration using /app/portal/oracle/oracle/product/agent11g/perl/bin/perl /app/portal/oracle/oracle/product/agent11g/sysman/install/AddonConfig.pl
Arguments passed

Configuring Addon from xml : oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml

Running Command : /app/portal/oracle/oracle/product/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/app/portal/oracle/oracle/product/agent11g ACTION=configure MODE=perform RERUN=true  RESPONSE_FILE=/app/portal/oracle/oracle/product/agent11g/vt_responsefile COMPONENT_XML={oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml}
 Setting the invPtrLoc to /app/portal/oracle/oracle/product/agent11g/oraInst.loc

perform - mode is starting for action: configure


perform - mode finished for action: configure

You can see the log file: /app/portal/oracle/oracle/product/agent11g/cfgtoollogs/oui/configActions2014-03-26_10-33-20-AM.log

 Agent Addon Configuration done

Starting the agent using /app/portal/oracle/oracle/product/agent11g/bin/emctl  start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Agent is already running

Clustering the new agent nodes:

Then update the cluster node list by running the below command on all rac nodes to make the agent clustered:

 /app/portal/oracle/oracle/product/agent11g/oui/bin/runInstaller ORACLE_HOME=/app/portal/oracle/oracle/product/agent11g -updateNodeList "CLUSTER_NODES={portaldb1,portaldb2,portaldb3,portaldb4}" -local

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 29257 MB    Passed

The inventory pointer is located at /var/opt/oracle/oraInst.loc

The inventory is located at /app/portal/oracle/oracle/oraInventory

'UpdateNodeList' was successful.

In case you face issues and want to deattach and attach the home again:

To deattach Home:
runInstaller –silent -detachHome -invPrtLoc /app/portal/oracle/oracle/oraInventoryoraInst.loc \
ORACLE_HOME_NAME="agent11g1" \
ORACLE_HOME="/app/portal/oracle/oracle/product/agent11g"
To attach Home:
runInstaller –silent -attachHome -invPrtLoc /app/portal/oracle/oracle/oraInventoryoraInst.loc \
ORACLE_HOME_NAME="agent11g1" \
ORACLE_HOME="/app/portal/oracle/oracle/product/agent11g"
To Update the node list with clustered nodes:
/app/portal/oracle/oracle/product/agent11g/oui/bin/runInstaller ORACLE_HOME=/app/portal/oracle/oracle/product/agent11g -updateNodeList "CLUSTER_NODES={portaldb1,portaldb2,portaldb3,portaldb4}" -local

References:

http://docs.oracle.com/cd/E17559_01/install.111/e16847/clone_agent_usng_zip.htm