RAC Attack .....

Last year, I built two VMs on Oracle Virtual box with ASM, etc and built myself a RAC Cluster using what in my opinion is the best resource on the web for building RAC on VMs. As I had been up to neck in work since I started this blog in 2011 and "Blogger"was blocked by that same place of work, oh the evil bloggers. I never kept it up to date, the road to hell and all that. I really wish I had all the notes but no good crying over spilt milk, anyway here is probably the only link you will ever need. You could do the course and get the t-shirt but to be honest, if I can do it (even with my years and years) anybody with half decent Unix / Oracle should be able to.



https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home

It was working like a dream, I could connect to sqlplus and "pull the plug" on one server and would fail over to the second. Great to try out stuff you would never get to do, even in a test environment when not your equipment. The place I was working at would never pay for a "sandpit" so VMs were about as good as it got.

However, my work laptop that I was using at the time (horrible thing) was supplied with a small SSD disk and I was supposed to use cloud storage for all files. Great from a security point of view but pretty useless from a DBA perspective. I would have loved to have built a local E-Business suite environment too but if I was struggling for space for RAC, E-Biz was an impossibility.

As I needed to show one of my colleagues some ZFS on Solaris I had to make the decision to pull the plug on my VMs and build an environment for him to work on. As soon as I had explained ZFS, needed a VM to test Weblogic for a Documentum build - it never ended.

Now, I have my own decent laptop to play with going to build a couple of VMs and do a guide to accompany "RAC Attack". Hopefully. RHEL 7 and Oracle Virtual Additions won't start to complain about anything and I can get a decent stable release. Watch this space!!

Removing an Oracle 12c database

Remember to stop the database, check the status before deleting the database.

DB03 /home/oracle > srvctl stop database -d prodx

DB03 /home/oracle > srvctl status database -d prodx -v

Instance prodx1 is not running on node db03

Instance prodx2 is not running on node db04

DB03 /home/oracle > dbca -silent -deleteDatabase -sourceDB prod

Connecting to database

9% complete
14% complete
19% complete
23% complete

28% complete
33% complete
38% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instances and datafiles
66% complete
80% complete
95% complete
100% complete

Look at the log file e.g. "/u01/app/oracle/cfgtoollogs/dbca/prodx1.log" for further details.

Schema tables with a describe

Asked by a user if he could have a quick and dirty solution to viewing the table name with their descriptions for a schema he was working on, only a handful of tables.

He already had the table names but would have been easy enough to spool them from sqlplus.
Probably a million and one ways to do this but came up with a quick shell script to give him what he wanted.


I only post this on here as you need to put an exit before the sqlplus - sure someone will correct me. If I get a chance will have a play around - took the shell specifier out as I wanted to try different shells.

Remember to use the silent, head off and feedback off to suppress the output.

array=(
table1
table2
table3
…etc
)

for i in "${array[@]}"
do
exit | sqlplus -silent user/passwd@database << EOF
set head off
set feedback off
select 'select ''${i}'' from dual;' from dual;
select 'desc '|| '${i}' ||' ' from dual;
EOF
done

from the output…..

TABLE
------
table1

Name Null? Type
----------------------------------------- -------- ----------------------------
ANOTHER_COL VARCHAR2(10)
ANOTHER_COL2 NUMBER


etc....

slight variation to do direct counts :

cat count.sh
array=(
OP_PARTITION
INTER_NCS_BATCH_CONTROL
INTER_NCS_QUEUE
INTER_NCS_QUEUE_BATCHES
INTER_NCS_QUEUE_SEQ
INTER_NCS_ROUTING
INTER_QUEUE_CONTROLLER
INTER_QUEUE_SETTINGS
NEXTFARE_INTER_NCS
RAW_SNMP_AFC_EVENT
)
for i in "${array[@]}"
do
exit | sqlplus -silent / as sysdba << EOF
set head off
set feedback off
SET LINESIZE 200
SET TRIMSPOOL ON
SET TRIMOUT ON
SET WRAP OFF
SET TERMOUT OFF
SET PAGESIZE 0
select '${i}' from dual;
select count(*) from NEXTFARE_MAIN.${i};
EOF
done



** IF you are using Windows similar could be achieved using Powershell

$sampleArray =
"EDIT_INCIDENT",
"EDIT_LOC",
"TEST3"

for ($i=1; $i -lt $sampleArray.Length; $i++) {
$sqlQuery = "select 'desc $($sampleArray[$i]);' from dual;"
$sqlQuery | sqlplus -silent sys/sys_pass@EDIT-01 as sysdba
}