Upgrade Grid infrastructure home from 11.2.0.3 to 11.2.0.4

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;