Oracle Tips and Tricks

Oracle 19c Preinstall-What does it actually do?

2021-06-18T15:49:00.002-07:00

I set up a lot of systems, lately about a new RAC cluster a week. We use a lot of automation and scripting, but if some of that can be done for you, that just makes it even easier. I love what the pre-install Oracle DB rpm provides. How do you use it?

yum install oracle-database-preinstall-19c.x86_64

About 30 seconds later, the system is 99% ready for a RAC install. In a previous post <<click here>>, I detailed how to download the version 12 RPM and modify it to work with Redhat. That's no longer necessary, the 19c version will install into RHEL...there's no longer a requirement on OEL. You may still want to look at that post to see how to make modifications to the RPM (for instance, how to change the parameters, the UID's and GID's to match your environment.)

Recently I had an issue where I was getting different results on my test system than other people were getting, and I suspected it came down to the OS parameters that were being set. I had used a minimal RHEL 7 install and added the pre-install rpm. I knew roughly what that did, but not the details. I looked up the Oracle documentation and all I found was this (for 12c):

When installed, the Oracle Preinstallation RPM does the following:

Automatically downloads and installs any additional RPM packages needed for installing Oracle Grid Infrastructure and Oracle Database, and resolves any dependencies
Creates an oracle user, and creates the oraInventory (oinstall) and OSDBA (dba) groups for that user
As needed, sets sysctl.conf settings, system startup parameters, and driver parameters to values based on recommendations from the Oracle RDBMS Pre-Install program
Sets hard and soft resource limits
Sets other recommended parameters, depending on your kernel version

Ok...that's not very detailed. If you go to a sysadmin and say "I set other recommended parameters..." he'll have lots of follow up questions for you....

So...I once again tore open the RPM, found where the logs were sent and went through them. This is what is ACTUALLY does:

When you install the RPM, it verifies several other rpms exist that are needed for Oracle (or installs them if they're missing). If these rpms require rpms you don't have, yum will install those too. Here's the list:

procps module-init-tools ethtool initscripts bind-utils nfs-utils util-linux-ng pam
xorg-x11-utils xorg-x11-xauth smartmontools
binutils glibc glibc-devel
ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel
make sysstat openssh-clients
psmisc net-tools unzip bc tar

...in addition,

if you're on RHEL7, it'll also require compat-libcap1

if you're on RHEL8, it'll require both libnsl compat-openssl10

The next thing it does is add the required OS groups. They are:

oinstall,dba,oper,backupdba,dgdba,kmdba,racdba

...and then creates the oracle OS user and puts it in those groups.

Next, it adjusts settings in sysctl.conf:

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Be aware, it sets fs.aio-max-nr to a value *much* lower than you likely need. See note 2229798.1 for details.

Next, on my system it set up user limits with these values. I *think* the memlock setting was based on the amount of ram I had in my VM at the time...which was, not much:

Adding oracle soft nofile 1024
Adding oracle hard nofile 65536
Adding oracle soft nproc 16384
Adding oracle hard nproc 16384
Adding oracle soft stack 10240
Adding oracle hard stack 32768
Adding oracle hard memlock 134217728
Adding oracle soft memlock 134217728
Adding oracle soft data unlimited
Adding oracle hard data unlimited

Next, it adjusts the boot parameters:

Originally : crashkernel=auto rd.lvm.lv=rhel/root rd.lvm.lv=rhel/swap rhgb quiet

Changed to : crashkernel=auto rd.lvm.lv=rhel/root rd.lvm.lv=rhel/swap rhgb quiet numa=off transparent_hugepage=never (which also disabled thp’s memory compaction)

Lastly, it edits /etc/sysconfig/network:

Adding NOZEROCONF=yes

You may need to adjust those settings for your environment if its large. Sizing issues aside, after you install the rpm, within a few seconds you'll have a very stable system, ready for a RAC install. There are still other recommended things you should do...for example, verifying the network is using jumbo packets for the interconnect and reserving hugepages for your SGA, but the RPM does a lot of the OS work for you.

Hopefully this will help you better answer the question, "What does the Oracle Pre-install RPM do?"

The complete post-12c online procedure to move a file from file system to ASM (ORA-01110,ORA-01157,ORA-01187,ORA-12850,RMAN-03009)

2020-07-23T14:46:00.000-07:00

In the Coronovirus-19 world, most of us would rather spend time learning about the Seattle Kraken, Max Ehrich, Roy Jones Jr and the Washington Football Team. They'll probably eventually do what Prince did and just call themselves, "Symbol." Roy Jones and Max Ehrich will both get knocked out and the Blues will have one more team to beat before they get their next cup. If you're reading this...you probably also care about Oracle databases...so let me tell you about a recent experience I had.

A client I work with had an issue the other day (actually 2 issues) in a container database. This database has very strict uptime SLA's...so downtime for a bounce of an instance isn't an option. The online process to move a datafile from file system to ASM is a 3 step process in 12c+...until now I've always thought it was a single "move datafile" command.

1. They hit a bug similar to the issue discussed in note 2558640.1, while doing a select from cdb_data_files, they got "ORA-12850: Could not allocate slaves on all specified instances: 3 needed, 0 allocated".

2. This happened in a script that's used to add datafiles as needed. Unfortunately, that script created a datafile with some of the error text instead of "+DATA"...so the file was created in the dbs directory on node 1 instead of putting it into ASM. This is RAC, so the datafile was only able to be accessed on the first node. RAC requires shared storage.

First order of business, get it working again. This is 18.7, so it was easy to move the file into ASM without downtime. The filename was ridiculous with special characters, and the file was on the file system on node 1, so from node 1 I did:

alter database move datafile 1654 to '+DATA' ;

...and that worked, now the datafile is in ASM's shared storage. I kicked off a backup (which failed):

RMAN-03009: failure of backup command on ch4 channel at 07/23/2020 11:23:24
ORA-01157: cannot identify/lock data file 1654 - see DBWR trace file
ORA-01110: data file 1654: '+DATA/DB/DATAFILE/sysaux.22113.1246514439'

On node 1, this file is 100% working in ASM. On other nodes, it was unavailable, giving ORA-01157's. In gv$datafile I could see it was online and available on all nodes. This made no sense to me. I even double checked by going into asmcmd and doing an "ls" from one of the nodes that was failing...yep...it was there. It was acting like it was offline, but reporting it was online.

I googled/checked metalink and didn't really find a good hit. Finally I thought...there's no harm in onlining a file that says its online...so I did an:

ALTER DATABASE DATAFILE 1654 ONLINE;

The error changed from:
ORA-01157: cannot identify/lock data file 1654
to:
ORA-01187 cannot read from file 1654 because it failed verification tests

It was still working on node 1, but getting ORA-01187 on the other nodes. This time I found a solution:

ALTER SYSTEM CHECK DATAFILES;

...which I did on the nodes that were having issues. After that, everything began to work.

With the solution and the original error, I went back on MOS and this time found the ultimate solution: (DOC 2499264.1) which states:

ORA-01157 error is expected to occur when running a query on dba_datafiles on a particular instance (of RAC database) for a file moved (just after creation) from Filesystem to ASM, without instance wide identification.

This problem was previously addressed in Bug 27268242 which was also closed as "Not a Bug" because this is an expected behavior.

(I think they meant cluster or database-wide identification.)
<VENT> Come on, Oracle! You shouldn't expect to get an error when running a correct query on a valid datafile. This just causes more trouble and steps and after a datafile move, there's no reason to leave it in a partially working state. If you're in RAC and somebody moves a file to ASM...let the other nodes know! There's no conceivable situation where somebody would want to move a file in RAC and have it only available to a single instance...make the alter database datafile move check datafiles when its coming from the filesystem for us!
<?VENT>

Until Larry starts listening to me...this means we have multiple steps.

That note also included a resolution:
alter system check datafiles;
alter system flush shared_pool;

...so at this point, the datafile is working correctly on all nodes.

This means we have 3 statements to do when we move a file from the file system to ASM:
alter database move datafile 1654 to '+DATA';
alter system check datafiles;

alter system flush shared_pool;

Now that its working, I can re-address problem #1 above...the root cause.

Note 2558640.1 discusses a similar problem when OEM is hitting the views that have the datafiles. My issues isn't in OEM, but in every other way this matches up. The work around:

alter system set "_px_cdb_view_enabled"=false comment='Work-around for internal view error and OEM-see bug 29595328';

This prevents the issue from happening again, but it also slows down the query (and others) hitting the cdb_data_files from 45 seconds (there's a lot of datafiles) to 90 seconds. I suppose that's better than getting this error...I opened an SR to ask this be fixed in future releases.

I love the new feature in 12c+ to do online datafile moves...but it would be great if the process was documented when moving a datafile from the file system...all I've seen in the doc's is the basic move datafile command, not all 3 steps.

With the system working and in a state where this won't happen again...I can now return to C19's bad hair and toilet paper and coin shortages.

RHEL7 Control Groups on Oracle Database Servers

2020-05-06T10:48:00.000-07:00

After some linux patching on some database servers I was surprised to see an entry in an alert log I hadn’t seen before…complete with a WARNING message. After digging around I found it was due to Linux control groups. In an effort to manage a server with multiple workloads, RHEL7 introduced control groups and they’ve increased features/abilities with each dot release. On the affected Oracle nodes in RHEL7 we see entries on startup in the alert log:

************************ Large Pages Information *******************

Parameter use_large_pages = ONLY

Per process system memlock (soft) limit = UNLIMITED

Large page usage restricted to processor group "user.slice"

Total Shared Global Region in Large Pages = 60 GB (100%)

WARNING:

The parameter _linux_prepage_large_pages is explicitly disabled.

Oracle strongly recommends setting the _linux_prepage_large_pages

parameter since the instance is running in a Processor Group. If there is

insufficient large page memory, instance may encounter SIGBUS error

and may terminate abnormally.

Large Pages used by this instance: 30721 (60 GB)

Large Pages unused in Processor Group user.slice = 1722 (3444 MB)

Large Pages configured in Processor Group user.slice = 120000 (234 GB)

Large Page size = 2048 KB

You may also see:

Large page usage restricted to processor group "system.slice/oracle-ohasd.service"

…this concerned me because if our large page usage is restricted and we can’t do a prepage, eventually the db_cache would warm up and allocate memory beyond the limitation, causing an instance crash. Also, “WARNING” on startup is something to take a closer look at. That undocumented parameter was most definitely NOT explicitly disabled. This message appears in every database that’s being controlled by processor groups, so it appears prepage of large pages is disabled automatically. There’s a note (2414778.1) that says this message can be ignored and its removed in 12.2+.

So…what amount of memory is our group being limited to by default?

>systemctl show user.slice|grep Mem

MemoryCurrent=18446744073709551615

MemoryAccounting=no

MemoryLimit=18446744073709551615

…which works out to be 17,179,869,184GB, (roughly 17 Exabytes) which is probably a sufficient amount of RAM (per node) for any database out there…so we’re basically being limited to slightly less than infinity….nothing to worry about.

Are control groups useful in a multi-database instance server environment? To set up CPU limits per instance, you could use this or instance caging…I would prefer to set the cpu_count parameter and use instance caging. Memory Limits? Memory utilization for the SGA is set via sga_target/max_size…so I don’t think there’s a play for it here either. Maybe the accounting features could be useful…if you wanted to see how much CPU one instance in a multiple instance db server was using historically? There are awr tables that track that…but getting the information from CG might be easier….

One potentially useful way to use control groups for a database server with multiple instances is to throttle bandwidth on IO to prevent one instance from using up all the IO and affecting the performance of a different instance (assuming each instance is on its own asm disks or luns.) There’s a note about how to get that to work on Metalink: 2495924.1. This has been a useful feature in Exadata for years with IORM…now you can have similar functionality without Exadata.

One other interesting feature of control groups is the ability to look at the resources each group is using in real time (if you have accounting on) with the systemd-cgtop command. If you don’t have accounting, you’ll see something like this, which is a summary of process counts, cpu, memory and IO for each group:

It took some time to find the details about what the new alert log entries were telling me with the new warning and processor group information. Hopefully this information will save you some time.

Oracle Cloud Backups - The easy way to mass delete files in a large OCI bucket

2019-08-30T12:15:00.000-07:00

In some previous posts, I talked about the benefits of doing backups to Oracle's cloud...if you run the numbers it will be significantly (magnitudes) less expensive than backups on prem or to other cloud vendors. Its safer than keeping backups geographically close to you, they're very fast (depending on your WAN speed, the storage array's write speed will be the bottleneck for your restore), they're encrypted, compressed and secured. Once you have everything scripted...its a "set it and forget it." The uptime far exceeds typical on-site backup MML systems...which is important because failing to do timely archivelog backups may translate into a database outage.

I've really only had 1 complaint until now. Here's the full story...skip down to "The Solution" if you're impatient.

When I first helped a customer set up their backups to Oracle's Classic backup service (pre-OCI), there was a problem. The design called for the database backups to go into a different bucket for each databases. When the non-prod databases were refreshed from production, they wanted to delete the bucket that held those backups. No problem...there's a big red delete button on the bucket page, right? Wrong! You can't delete a bucket that has files in it. When you do a backup of even a medium sized database, you can have hundreds of thousands of files in it. The only way to delete files on the console is to select 100 at a time and click delete. I had 10 databases/buckets, over a million files and it took me about 5 seconds to delete 100 files. If I did nothing else...we're talking ~14 hrs of work. In reality it was going to take somebody days to delete the backups after every refresh. Completely unacceptable....

I opened an SR to ask for a better way. They pointed me to Cloudberry Explorer (which is a pretty cool product.) CBE allows you do pull up the files in Oracle's (or virtually any other) cloud in an interface similar to Window's file explorer. Great! I set it up, selected all the files I wanted to delete and clicked the little delete button. After the files were indexed, it kicked off multiple threads to get this done. 3 by 3 (about 3 sec per file), they started to delete. I left my laptop to it over the weekend. When I came back on Mon...it was still deleting them.

I updated the SR...they pointed me to using curl in batch to hit the API (Doc ID 2289491.1). Cool! I love linux scripting...it took a while to set it up, but eventually I got it to work...and one by one (about 3 sec per file) they started to delete. Argh.

A colleague I work with wrote a similar procedure in perl...but it was using the same API, and had the same results.

I updated the SR...they essentially said there's no way to do what I'm trying to do any faster.

I opened an new SR for an enhancement request to add a way to do mass deletes...here's the response:

"The expectation to delete the bucket directly is like a Self Destructive program ,Many customer will accidentally delete the container and will come back to US for recovery which is not possible."
...and then they closed my enhancement request.

My first thought was to send an email to Linus Torvalds and let him know about the terrible mistake he made introducing rm to his OS. (sarcasm) My second thought was that the company that charges for my TB's of storage usage isn't motivated to help me delete the storage.

The Solution: Lifecycle the files out.
1. First enable Service Permissions (click here).
2. In OCI, go to Object Storage, click on the bucket you want to clean up and click on "Lifecycle Policy Rules." Click on "Create Rule."
3. Put in anything for name (its going to be deleted in a minute, so this doesn't matter.) Select "Delete" from the lifecycle action and set the number of days to 1. Leave the state at the default Enabled, then click the Create button. This will immediately start deleting all the files in the bucket that are over 1 day old...in my case, that was all of them.

4. The Approximate Count for the bucket will update in a few minutes. Soon (within a min or so) it will say 0 objects. Now when you click the red Delete button to remove the bucket, it will go away. I was able to remove about 150 Terrabytes in a few seconds.

I have no idea why Oracle made this so difficult...their job is to provide a resource, not protect us from making a mistake. Anyway, I hope this helps some of you out.

How to monetize your blog with BAT

2019-08-13T09:03:00.001-07:00

I think one thing is clear at this point...you'll either get into blockchain technology, or you'll regret it when you get in later. This is still the time of "Early Adopters"...you're not too late. How does this tie in to a mainly Oracle/database blog? Recently I heard Oracle's Larry Ellison gave a speech to Wharton Business School graduates about blockchain where he said this is the single most important new technology in decades. A Bloomberg report stated Oracle is ready to announce its entry into Blockchain including a platform as a service (PaaS) Blockchain product...IBM and others have already done the same thing. We've all seen blockchain enter the finance and IoT spaces, but the applications are endless...its my other passion, besides databases. You could say blockchain is a new database technology. Its going to change the world.

Blockchain gave us Bitcoin and many other coins/tokens. You've all heard of Bitcoin, but you may not have heard of Basic Attention Coin (BAT). BAT works with the Brave browser, which can generate BAT tokens as you use it. It then allows you to tip websites automatically when you use them. *More info here*. To do this you just need to download and use the Brave browser . Think "Chrome" but its faster, more secure, ads blocked, built by Brendan Eich, the guy that invented java script and Mozilla. The idea is that from a user's perspective, you can reward content creators (like websites, twitter/youtube/blog channels, etc) when they help you out (at really no cost to you)...and from the content provider's perspective, they can get free money.

I've used it for about a year, and it keeps getting better, but I wanted to monetize my blog with it. To do this, you have to register your channel, to do that, you need to put in a file with a key into a folder called ".well-known". Blogger has no way to do this, but I found a way to get it to work.

1. Go to https://publishers.basicattentiontoken.org/publishers/home
2. Create an account and click on "Add Channel"
3. Click on Website
4. Add your website: ie: otipstricks.blogspot.com
You're then given two options to verify via DNS or by adding a file...choose to add the file.

That will give you the instructions to add a file called /.well-known/brave-rewards-verification.txt with metadata in it.

5. Now go into your blog's settings and go to "Pages" and click "New Page."
6. I named my page brave-rewards-verification.txt, although I'm not sure that was necessary, and then I pasted in the metadata I got from step 4.
7. Click save and publish...this gives you a page you can access like this:
https://otipstricks.blogspot.com/p/this-is-brave-rewards-publisher.html
8. Back to blogger settings->search preferences->Custom Redirects->Edit->New Redirect
9. Create a redirect that looks like this, with relative paths:
From:/.well-known/brave-rewards-verification.txt
To:/p/this-is-brave-rewards-publisher.html
Permanent:Yes
10. Now go back to https://publishers.basicattentiontoken.org/publishers/home and click on the verify button. Within a few seconds it will say, "Channel Verified."
11. Now create an uphold wallet (which is another link on that page and follow the instructions to connect it to your new bat publishers account, and you're done. Now, when people come to your page, they can tip your blog automatically, in the background.

Now, when you go into brave and click on brave rewards, you can check your website:

Oracle's Backup Cloud Service (OCI) - 3 setup your backup

2019-08-08T09:44:00.000-07:00

In this series of posts:

Oracle's Backup Cloud Service (OCI)-1 - The Oracle OCI Backup Cloud

Oracle's Backup Cloud Service (OCI) - 2 - setup OCI and your server environment

Oracle's Backup Cloud Service (OCI) - 3 - You are here

At this point, you've read about why you want to use OCI and the benefits and you've read how to setup your environment for it. This one is about setting up your backups to use it. The only negative I can say after using OCP/OCI for over a year is that they change the security certificates without warning. If you install/setup the software per the docs and you have a lot of databases...this can easily occupy many hours that day, reinstalling all the OCI software for every Oracle_Home in your infrastructure. This is why in my previous post, I recommended you use a shared mount.

You have already:
1. Installed OCI software on a shared mount
2. Setup a container in the cloud web page
3. Used the jar file to create the environment files you need
4. Enabled block change tracking in the database (for incrementals)
5. Enabled medium compression in rman (per the OCI backup docs, this is normally licensed as part of the compression pack, but it appears if you're paying for OCI backups, this is included. Talk to your Oracle sales guy.)

In the server you used in the last post, as the db software owner:
1. cd /nas/dba/backups/cfg

2. ls

...you should see a file called opc[database name].ora

3. You can use this as a template for all your other databases...just copy the file to have the new database name and update the container parameter in the file.

4. If you use stored scripts in an rman repository, you can add the backup scripts similar to this. This is just the archivelog backup...you'll want incrementals and fulls, obviously, but the key is the allocate channel commands, which are the same. Add more channels to increase performance...until your network guys complain:

printing stored global script: tstoracle01_TSTDB1_arch

{

allocate channel ch1 DEVICE TYPE 'SBT_TAPE' CONNECT 'sys/[the_pw]@TSTDB1' PARMS 'SBT_LIBRARY=/nas/dba/backups/cfg/libopc.so, SBT_PARMS=(OPC_PFILE=/nas/dba/backups/cfg/opcTSTDB_a.ora)';

allocate channel ch2 DEVICE TYPE 'SBT_TAPE' CONNECT 'sys/[the_pw]@TSTDB2' PARMS 'SBT_LIBRARY=/nas/dba/backups/cfg/libopc.so, SBT_PARMS=(OPC_PFILE=/nas/dba/backups/cfg/opcTSTDB_a.ora)';

allocate channel ch3 DEVICE TYPE 'SBT_TAPE' CONNECT 'sys/[the_pw]@TSTDB3' PARMS 'SBT_LIBRARY=/nas/dba/backups/cfg/libopc.so, SBT_PARMS=(OPC_PFILE=/nas/dba/backups/cfg/opcTSTDB_a.ora)';

crosscheck archivelog all;

delete expired archivelog all;

backup as compressed backupset archivelog filesperset 8 not backed up 1 times delete input;

}

5. Make sure your rman environment is set up correctly:

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE;

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/nas/dba/backups/cfg/libopc.so, SBT_PARMS=(OPC_PFILE=/nas/dba/backups/cfg/opcTSTDB_a.ora)';

6. Assuming you can tnsping to each node from node 1, and you kick off the script from node 1, when you kick off the backup, it should work from all 3 nodes. You'll also need a maint script to delete backups that are outside of your retention period...otherwise your OCI storage will grow too large. Here's an example of a maint script:
printing stored global script: global_maint_backup
{
crosscheck backupset device type sbt_tape;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
}

7. One of the requirements for OCI is that the backups MUST be encrypted. This is easy...before your backup script, after you get in to rman, just add:

set encryption on identified by '1VeryGoodPassword!' ONLY;

...and when you do the restore, you have to add a line to decrypt:

set decryption identified by '1VeryGoodPassword!';

8. When you do the restore, you'll probably want to use many more channels then you do when you're backing up...assuming its an emergency and you need the restore done asap. When you add lots of channels, depending on your network equipment, it may help you compete for bandwidth favorably from the cat videos being watched by all the marketing managers at your company.

Oracle's Backup Cloud Service (OCI) - 2 (setup OCI and your server environment)

2019-08-08T08:51:00.000-07:00

In this series of posts:

Oracle's Backup Cloud Service (OCI)-1 - The Oracle OCI Backup Cloud

Oracle's Backup Cloud Service (OCI) - 2 - You are here

Oracle's Backup Cloud Service (OCI) - 3 setup your backup

In my previous post, I talked about OCI Backups and their advantages. Here's how you do it:

Before you start, verify you have Java 1.7+ installed on your server (java -version) and download the MML software:
http://www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html

In a browser, go to:
https://console.us-ashburn-1.oraclecloud.com/

1. Setup your tenant
2. Create your user

...those steps keep changing, so I'm not going to tell you how to do them...but if you go to that page, it should be pretty self-explanatory. Ok, so at this point you're in.

3. On the top right, select the region that's geo-located closest to you. Be aware of any in-country requirements for your database backups.

4. On the top left, click on the hamburger (the button with 3 lines), and then click “Object Storage”. The Object Storage page will appear:

5. To make management easier, put each database’s backups into its own bucket. If you don’t see the bucket for the database you’re working on, create one by clicking on the “Create Bucket” button.

6. Fill in the “Bucket Name” text box with the database you want to add and select “standard tier”, then click the “Create Bucket” button. The list price of standard tier is $0.025 cents/GB, and archive price is $0.0025 cents, but you’re required to keep archive for 90 days and there’s up to a 4 hour lag if you need to pull back archived files...so unless its a rare backup you're going to keep for an extended time, you want standard.

7. For the install to work, we first need to get:

a.Cloud Infrastructure API signing keys

b.tenant OCID

c.user OCID

d.fingerprint of our RSI key.

8. Click the profile button in the top right corner (it looks like a head in a circle) and select user details. Click "copy" and paste the USER OCID to the editor of your choice. It should start out with: "ocid1.user.oc1.."

9. Click again on the profile button in the top right corner (that looks like a head in a circle) and this time select "Tenancy: [whatever your tenancy is called]"

10. Under the Tenancy information, click on "Copy" and copy the tenant OCID and paste it into the editor of your choice.

11. Assuming you have an nfs mount that's shared on all your database servers, make a directory on that mount... ie: mkdir /nas/dba/backups/cfg/opc_wallet

12. In your database server, go to the directory you unzipped the software into and get a new RSA key pair:

java -jar oci_install.jar -newRSAKeyPair -walletDir /nas/dba/backups/cfg/opc_wallet

13. View the oci_pub file and select it so it loads into your laptop's clipboard (starting at the first dash, stopping after the last dash)

14. Once again back to the Profile button in the top right corner, click on your user and scroll down to the bottom of the page where it says, "API Keys" and click Ad Public Keys and paste in your new key from the previous step...when complete, click ADD.

15. A new fingerprint will be displayed. Select that fingerprint and paste into the text editor of your choice.

16. Using all the things you put in your editor of choice, create the install command. Put yours all in one line...I broke this out to make it more readable:

java -jar oci_install.jar -host https://objectstorage.us-phoenix-1.oraclecloud.com

-pvtKeyFile /nas/dba/backups/cfg/opc_wallet/oci_pvt

-pubFingerPrint [paste finger print here]

-tOCID [paste tenancy OCID]

-uOCID [paste user OCID]

-libDir /nas/dba/backups/cfg

-walletDir /nas/dba/backups/cfg/opc_wallet

-bucket [the bucket you created in step 5]

-cOCID [the OCID of the compartment you're using, if any]

-configFile /nas/dba/backups/cfg

...at this point the environment is set up and you're ready to move on to the next post.

Oracle's Backup Cloud Service (OCI)-1

2019-08-08T08:09:00.001-07:00

In this series of posts:
Oracle's Backup Cloud Service (OCI)-1 - You are here

Oracle's Backup Cloud Service (OCI) - 2 setup OCI and your server environment

Oracle's Backup Cloud Service (OCI) - 3 setup your backup

IMHO, one of the best use cases for the cloud is backups. I honestly don't know why Oracle doesn't market this more...its been the best Oracle Cloud experience I've seen. The bar is low there...but this service has been flawless. The questions re:security have been MOSTLY answered (save a few for AWS at Capital One). One of the trends I'm seeing is for companies to move some of their database backups to the cloud. If you can't handle downtime more than a few minutes, this isn't for you, you may need storage array snaps or a fast dataguard failover...but what about your dev/test/qa database backups? Putting at least some of your backups in the cloud might make a lot of financial sense. In a recent analysis, I saw a company that found it was (all things considered) about 400% cheaper to put their dev/test backups in Oracle's cloud. Backing up to Oracle's new OCI cloud (as opposed to Oracle's Classic Cloud Backup service) was even cheaper than AWS Glacier for their use case.

What about restore performance? Dev/Test might be more tolerant than prod for an outage during a restore/recovery, but how long are we talking here? The answer:it really depends more on your infrastructure than it does on the OCI cloud...it is FAST. My test used an all-flash Dell/EMC VMax and my 3 RAC nodes were the only things on the new array. I have no way to know how much spare network bandwidth there was, but I did this test at night using a 1Gb link. The key is...using Oracle backups allows you to use medium compression. So...whatever your bandwidth is...multiple that by your compression ratio, and that's the restore speed you can expect (unless you hit a different bottleneck...storage array performance, cpu, etc...). I posted in the past about the performance benefits for using rman compression. In a recent test, my waits were on writing to the storage array, not the network or OCI itself.

The Test:
Oracle RAC 12.2

Almost 14TB (13,940,708 MB)

Starting at 9:53

Finishing at 12:52

...so 3 hrs...~4.66TB/hr.

So you ask...its secured, its much cheaper and its as fast as it could possibly be? You also get an automatic air-gap, offsite backup. What's the downside?

Once you understand it, its easy to do and the up-time has been great, too, but the instructions are almost impossible to locate online...and when you do, they're obsolete or talking about classic, not OCI. The only instructions (that weren't wrong) I found were in the README...but they weren't great and they were about how to set it up for a database...you probable have more than 1, right? Several times/yr, maybe without warning, Oracle will update their security certificates which will force you to do a re-install...but if you plan your installation with this in mind, you can make that almost painless. In my next post, I'll describe what I did.....

Data Cleanup (how to exercise the ghost from the machine)

2019-08-07T14:25:00.000-07:00

The scariest movie I ever saw was the Exorcist...to tell the truth, I don't think I actually saw it...while I was watching it I had my eyes closed half the time and I left early. This is relevant to Oracle Databases...stay with me here. Database upgrades are a *HOT* topic right now...with the extinction of 12.1.0.2 free support, everybody is moving to 18c or 19c. I recently helped a customer with a problem that was preventing their database upgrades from being successful. They were moving a legacy database into a multi-tenant pluggable database using datapump and rows were being dropped. We initially assumed it was a character set issue, but we were going from UTF8 to UTF8. We eventually learned that this database had long ago migrated to UTF8 from a different character set, and the conversion was done incorrectly, so we were left with bad data in the legacy tables.

They said they've had issues with "ghosts" in the data...two columns that appeared to equal each other not equaling each other in plsql, that kind of thing...but they always attributed it to bugs. Now we knew the column's data had hidden, invisible characters in them. They didn't care about the hidden characters, but the rest of the data in the column needed to be saved. They only needed the basic, common ascii character set, so I wrote this quick little stored procedure to dynamically take the data and scan it, replacing any unusual characters with a space. After this the import worked without an issue, and the database ghosts were exercised (no young and old priest required). There were many 100 mil+ row tables, but the number of rows with bad data were only in the thousands...so I didn't do a forall update...if your data has a larger number of rows to change, you might want to modify this.

When its complete, you can see if everything worked ok with this:
select /*+ PARALLEL 8 */ * from ascii_check where CPOS=-1 order by 1,2,3;

[insert usual disclaimer here] I used this on many very large tables and it appears to do what I intended (which was to use the character's ascii code range to decide if it was a character we wanted to not), but review it closely to make sure it does what you want it to do in your environment before you use it. No warranties/promises...it'll probably not work...yada yada yada....

First, create this table that will serve as a log to keep all the data that was changed, in case something unexpected happens or you need to revert back:

create table ascii_check (Table_Owner varchar2(30),Table_Name varchar2(255), Col_Name varchar2(255), old_rowid varchar2(255), cpos number, code number, old_value varchar2(255 char));

...once you feel confident its doing what you want...you can submit it as a job, and have many tables being scanned and corrected at the same time.

CREATE OR REPLACE procedure data_cleanup
(
Table_Owner_in in varchar2,
Table_Name_in in varchar2,
Column_Name_in in varchar2,
Testing_in in varchar2
)
is
v_Char number;
v_String2 varchar2(32000);
v_Flag boolean;
v_Flag2 boolean := true;
v_Counter number :=0;
v_Errm varchar2(255);
v_ErrTrack number :=0;
v_Count number := 0;
already_completed exception;
pragma exception_init(already_completed,-20001);
type rct_RowIDs is ref cursor;
rc_RowIDs rct_RowIDs;
v_Err_Rowid varchar2(18);

type t_Row is record
(
Old_RowID varchar2(18),
Old_Data varchar2(32000)
);
type t_Rows is table of t_Row index by pls_integer;
c_Rows t_Rows;
begin
execute immediate 'ALTER SESSION SET ddl_lock_timeout=900';
select /*+ PARALLEL 16 */ count(0) into v_Count from ascii_check where table_owner=Table_Owner_in and Table_Name=Table_Name_in and col_name=Column_Name_in and old_value='Completed Successfully.';
if v_Count!=0 then
raise already_completed;
end if;
delete /*+ PARALLEL 16 */ from ascii_check where table_owner=Table_Owner_in and Table_Name=Table_Name_in and col_name=Column_Name_in and cpos=-1;
v_ErrTrack := 1;

OPEN rc_RowIDs FOR 'select /*+ PARALLEL 16 */ rowid,'||Column_Name_in||' from '||Table_Owner_in||'.'||Table_Name_in||' order by rowid';
loop
FETCH rc_RowIDs BULK COLLECT INTO c_Rows LIMIT 50000;
v_ErrTrack := 2;
-- for each row
for i in 1..c_Rows.count loop

v_ErrTrack := 3;
v_String2 := '';
v_Flag := false;
-- for each character in each row
for j in 1..nvl(length(c_Rows(i).Old_Data),0) loop
v_ErrTrack := 4;
v_Char := ascii(substr(c_Rows(i).Old_Data,j,1));
v_ErrTrack := 5;
if (v_Char<32 or v_Char>126) then
v_String2 := v_String2||' ';
v_Flag := true;
v_ErrTrack := 6;
insert into ascii_check values (Table_Owner_in,Table_Name_in,Column_Name_in,c_Rows(i).Old_RowID,j,v_Char,substr(c_Rows(i).Old_Data,1,240));
elsif v_Char=39 then
v_String2 := v_String2||'''''';
v_Flag := true;
v_ErrTrack := 6;
insert into ascii_check values (Table_Owner_in,Table_Name_in,Column_Name_in,c_Rows(i).Old_RowID,j,v_Char,substr(c_Rows(i).Old_Data,1,240));
else
v_ErrTrack := 7;
v_String2 := v_String2||substr(c_Rows(i).Old_Data,j,1);
end if;
end loop;
v_ErrTrack := 8;
if v_Flag then
v_Counter := v_Counter +1;
if Testing_in ='FALSE' then
v_ErrTrack := 9;
v_Err_Rowid := c_Rows(i).Old_RowID;
begin
execute immediate 'update '||Table_Owner_in||'.'||Table_Name_in||' set '||Column_Name_in||'='''||v_String2||''' where rowid='''||c_Rows(i).Old_RowID||'''';
exception
when DUP_VAL_ON_INDEX then
execute immediate 'update '||Table_Owner_in||'.'||Table_Name_in||' set '||Column_Name_in||'=''EDITED-'||v_String2||''' where rowid='''||c_Rows(i).Old_RowID||'''';
--insert into ascii_check values (Table_Owner_in,Table_Name_in, Column_Name_in, v_Err_Rowid, -1,-1,v_ErrTrack||'-'||v_String2);
v_Flag2 := False;
end;
end if;
end if;
v_ErrTrack := 10;
end loop;
v_ErrTrack := 11;
exit when c_Rows.count < 50000;
end loop;
close rc_RowIDs;
dbms_output.put_line('There were '||v_Counter||' issues detected.');
if v_Flag2 then
insert into ascii_check values (Table_Owner_in,Table_Name_in,Column_Name_in,'',-1,-1,'Completed Successfully.');
else
insert into ascii_check values (Table_Owner_in,Table_Name_in,Column_Name_in,'',-1,-1,'Completed Unsuccessfully.');
end if;
commit;
exception
when already_completed then
dbms_output.put_line('Already completed. Exiting...');
when others then
v_Errm := sqlerrm;
dbms_output.put_line(v_Errm||'@'||v_ErrTrack);
insert into ascii_check values (Table_Owner_in,Table_Name_in, Column_Name_in, substr(v_Errm,1,240), -1,-1,v_ErrTrack||'-'||v_String2||'-'||v_Err_Rowid);
commit;
close rc_RowIDs;
end;
/

We're using up storage too fast! Where is all the growth coming from?

2019-07-23T08:28:00.002-07:00

Technical challenges aside, its difficult to navigate the politics of being an Oracle DBA. In today's world, data warehouses/data lakes are expected to ingest massive amounts of data and still perform well. Ok, no problem...this can be done...but when the storage utilization spikes, the storage guys (who write the checks for the expensive storage arrays the data is on)...they want to know if/why they need to buy another storage array. DBA's are expected to perform storage planning, but are rarely informed when the data owners increase utilization until after the fact.

This question comes up pretty often...why is the database growing so fast? Coming in to a new environment I'm not familiar with...I have a bunch of scripts I usually run to get a profile of the databases. Usually a non-technical manager wants to see a summary, so if this data can be presented graphically as part of a presentation, communication flows better, and it looks more professional.

All the storage utilization data is being kept in the history tables...its just a matter of pulling it . This is a dynamic query that creates a query...the results of that can be put into Excel and graphed. Once the results are graphed, things pretty clearly jump out at you. So...

1. Run this query (if you aren't using 8k block size, change 8192 below to w/e you're using):

select stmnt from (
select 1 o1, 0 o2, 'with stacked as
(
select snap_id, tablespace_id, trunc(to_date(rtime,''MM/DD/YYYY HH24:MI:SS'')) rtime, (tablespace_size*8192)/1024/1024/1024 ts_size
from dba_hist_tbspc_space_usage tdu
where snap_id in (select max(snap_id) from dba_hist_tbspc_space_usage tdu2
where trunc(to_date(tdu.rtime,''MM/DD/YYYY HH24:MI:SS''))=trunc(to_date(tdu2.rtime,''MM/DD/YYYY HH24:MI:SS'')))
)
select distinct rtime,' stmnt from dual
union all
select 2, TS#,'(select ts_size from stacked s2 where s2.tablespace_id='||TS#||' and s1.rtime=s2.rtime and rownum=1) as "'||NAME||'"'||decode((tsm-ts#),0,null,',') from v$tablespace ts1 , (select max(TS#) tsm from v$tablespace) ts2
union all
select 3,0,'from stacked s1
where (select ts_size from stacked s2 where s2.tablespace_id=0 and s1.rtime=s2.rtime) is not null;' from dual
) order by o1,o2;

2. Take the output of that query, and run it. It'll create a very wide data set with daily storage size data for each tablespace.

3. Cut/Paste and put that data into Excel.

4. Select the data columns in Excel and at the top, select insert, then on the "Charts" group, select your favorite chart type..."2-D, stacked area" works well for this data, IMHO.

You should now see something like this (GB on the Y axis):

...so now its clear where the acceleration in growth came from. The relatively new, orange "troublemaker_data" tablespace had a ~10TB growth spurt until May 1st, where it looks like it leveled off. This empowers you to go after the data owners and ask them if this will continue, and you can tell the storage guys there was growth but it settled down and they don't need to panic and buy more storage.

I hope this helps you with your storage planning!

Watch out for the latest 12.1 PSU/proactive patches!

2019-07-22T09:08:00.003-07:00

We all have horror stories about "That one time, when we restored and recovered the database, and the CIO was calling out managers every few minutes, reminding them the company is at a stand-still...this is costing us $XXX,XXX per minute!!!!!" Now imagine after a few hours in to your restore you have to say, "We hit a bug. We have to open an SR, get a patch and start over." Discovering you can't successfully restore/recover when the heat is on due to a bug would make a great horror movie...nothing is more scary. Luckily I hit this bug during a restore test...so I had some time to deal with it...but I thought I should let everybody know about it.

I know a lot of people are still running 12.1.0.2. The Jan and Apr 2019 PSU (the two latest PSU's) introduce a bug (25107334) that cause you to be unable to open the database. I hit this 3 hours into a restore:

ORA-65108: invalid use of a cursor belonging to another container

The work around is that you manually put the $SEED database into read-only mode. Simple enough...but if you're restoring/recovering a database, that's not a option. There's now a patch (25107334), that solves the issue. While waiting for MOS to come up with a solution, I used a 19.3 home to restore the database, and it worked fine...so that's an optional work-around too.

As people migrate to 19.3, there are earlier bugs that prevent 12.1 from working with a 19.3 GI...those bugs are fixed in these PSU's...so patching 12.1.0.2 needs to be part of the 19.3 upgrade plan, and this patch needs to be part of the 12.1.0.2 patch.

Hopefully this will be part of future PSU/proactive patch bundles...and we can keep the nightmares in the movies!

/*+ UPDATE */
The July PSU is out (12.1.0.2.190716) and per (1924126.1), 25107334 is fixed in that PSU.

Upgrade to Oracle 19.3!

2019-07-22T08:49:00.001-07:00

Oracle has postponed End of Support for 11.2.0.4 many, many times, but no mas! In fact, if you're running EBS, 11.2.0.4 is STILL supported with extended support fees waived, almost 11 years after its initial release. I know a couple of large EBS shops that are still running on 11.2.0.4. To upgrade the database for EBS is a huge task...so its understandable why they'd put this off as long as possible...but time is running out. See 742060.1 for details.

Also, 12.1 premier support ran out almost a year ago, but I know there are a lot of people still running critical databases on that version.

At this point, everybody is aware that Oracle changed their naming convention...so you can think of 19.3 really being 12.2.X. There are *many* great new features in 19.3...its the terminal release of Oracle 12.2. Oracle recommends you not upgrade at this point to anything other that 19c. I've heard rumors that the next db version that'll be supported for EBS is 19.3 (currently the max is 12.1...they're skipping 12.2 and 18.3.)

So its inevitable...you're going to go to 19c. Here's are some things to keep in mind:

1. In order to upgrade your database to 19.3, you...of course...need to upgrade your ASM's Grid Infrastructure to 19.3

2. In order to upgrade your Grid Infrastructure to 19.3, (assuming you're on RHEL) you MUST be on RHEL 7. 18.X was fine with RHEL 6, but 19.3 requires RHEL 7.

3. There are a lot of changes in the OS requirements. Make sure you review/verify the install documentation before you install it.

I'll post more about the changes in the 19.3 upgrade process later.

2019-04-26T10:59:00.000-07:00

19c Linux x86-64 for on-prem/non-Exadata is available for download!

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle19c-linux-5462157.html

Some interesting things to know...

1. Its available as the traditional download or as an RPM to download.

2. Just like the Exadata version that requires OEL 7, the on-prem version requires:

OEL 7.4, UEK 4 (4.1.12-112.16.7+)

OEL 7.4, UEK 5 (4.14.35-1818.1.6+)

OEL 7.4, RH Compat (3.10.0-693.5.2.0.1+)

RHEL 7.4+ (3.10.0-693.5.2.0.1+)

SUSE ES 12 SP3 (4.4.103-92.56+)

Have fun!

Yet one more End Of Support date push for 11.2.0.4

2019-04-12T13:46:00.003-07:00

If you haven't heard, on 1/26 Oracle updated their release/end of life document (742060.1) to say Extended Support fees have been waived through 12/31/2018. I understand Oracle trying to work with their customers, but a lot of people took that to mean, "Great, I can postpone working on this upgrade."

12.1.0.2 has been around since 7/22/2014, so we're coming up to 3 years to plan and execute an upgrade. 11.2 has been around since 9/1/2010...the terminal patchset isn't as old, but 7 years for a database is an eternity.

/* UPDATE */

As of 4/6/2019, if you're on Oracle E-Business Suite, database 11.2 and 12.1 extended support fee waiver has been extended again through Dec 2020...Wow! That's an extension of almost 2 years! (2522948.1) That means 11.2 will have had a 10+year supported run!

This is understandable since I've heard Oracle is skipping 12.2 database support, and 18.3 isn't the "long-term" version (and 12.2 and 18.X) isn't going to be supported by EBS. This means the database version you would need to upgrade to for EBS is 19.2. Unfortunately, 19.2 isn't available yet for non-Exadata/ODA, so there's nothing to upgrade to for the majority of EBS customers. They couldn't end support on 12.1 at the end of June and release 19.2 support for EBS at that same time. It typically takes months to upgrade an entire EBS landscape...so...this was inevitable.

Still, I hope you don't read this and think this grants you more time to procrastinate. There are *a lot* of great features in 19.2! Don't allow getting kicked off the old database version to be your primary motivation!

Poor query performance after stats were gathered?

2018-04-27T12:00:00.002-07:00

Although the Oracle optimizer is brilliant, its not infallible. Its vulnerability is that it depends on good table statistics to determine an optimal plan. I optimized a query today that was projecting to use over 2PB of temp space. It was a *horrible* plan...I started to try to rewrite it and thought...why is Oracle doing this...there isn't THAT much data. The first thing I checked was the accuracy of the table stats...within about 10 minutes I had the query completing in ~5 seconds.

When it comes to gathering stats, there are 2 schools of thought out there...those that gather stats frequently and those that gather stats that generate plans they're happy with, then lock them...or at least gather them much less frequently and intentionally.

Some people believe stats should be gathered frequently to always have the optimal query performance all the time. If the table data changes sizes dramatically and frequently...this might be ok. If the stats were gathered with estimates (which is commonly done), its possible that you'll gather stats based on a subset of your data that doesn't represent the whole table. So then your stats aren't great...but even then...usually the optimizer gets the right plan or a plan that's "close enough" that it doesn't cause any pain. Jonathan Lewis points out in "The Cost-Based Optimizer" (great book, by the way), that one of the primary purposes of table statistics are to mathematically create relativity between the table row counts in a join.

This means, if the largest table in a join today is the largest table tomorrow...ie: if you have 1mil rows today and it grows to 2 mil rows, you probably don't want your plan to change and so you don't need to regather stats.

Let's say the 1mil row table becomes a 1 row table, you could regather stats and get a new plan and everything would be great. The next day a data load brings it up to 10 mil rows...suddenly the plan that ran great isn't finishing in its SLA. A good plan for a query today isn't necessarily a good plan tomorrow.

My opinion is...if your table sizes fluctuate, you probably want to gather stats when the tables are large and lock them, which will cause your plans to be stable. If the table is much smaller tomorrow, your plan might not be optimal, but it won't be worse than it is today...so you'll have ~ consistency in run time performance (and you meet your SLA's.)

Oracle has made great strides to improve this...with 12.1's OPTIMIZER_ADAPTIVE_FEATURES and 12.2's OPTIMIZER_ADAPTIVE_STATISTICS, Oracle will correct itself with statistics feedback...which will prevent you from "falling off the temp usage cliff." Although these features are great...it would be better to not have a problem that needs to be corrected in the first place.

Since these problems are usually on complex views (on views, on views, on views...ets...) Here's a little query you can run to find the dependencies of the top level view, gather stats on its tables/indexes, and lock them (so the problem doesn't happen again.) I'm gathering them w/null est % (ie:compute)...adjust that and the degree/method_opt to fit your needs. This should generate pretty good stats and lock them, allowing the optimizer to make its brilliant decisions once again.

select
distinct 'begin'||chr(13)||'
SYS.DBMS_STATS.GATHER_TABLE_STATS (
ownname=>'''||o2.owner||''',
tabname=>'''||o2.object_name||''',
estimate_percent => NULL,
method_opt=> ''FOR ALL INDEXED COLUMNS SIZE AUTO '',
degree => 32,
cascade => TRUE,
no_invalidate => FALSE);'||chr(13)||
'end;'||chr(13)||
'exec dbms_stats.lock_table_stats('''||o2.owner||''','''||o2.object_name||''');'
from sys.dba_objects o1,
sys.dba_objects o2,
(Select object_id, referenced_object_id
from (select object_id, referenced_object_id
from public_dependency
where referenced_object_id <> object_id) pd
start with object_id = (select object_id from dba_objects where object_name='YOUR_TABLE_NAME' and owner='YOUR_TABLE_OWNER')
connect by nocycle prior referenced_object_id = object_id) o3
where o1.object_id = o3.object_id
and o2.object_id = o3.referenced_object_id
and o2.object_type='TABLE'
and o1.owner not in ('SYS', 'SYSTEM')
and o2.owner not in ('SYS', 'SYSTEM')
and o1.object_name <> 'DUAL'
and o2.object_name <> 'DUAL';

Effect of MBPS vs Latency

2017-08-24T14:22:00.000-07:00

I do *a lot* of performance testing on high performance storage arrays for multiple vendors. Usually if I'm involved, the client is expecting to put mission critical databases on their new expensive storage, and they need to know it performs well enough to meet their needs.

So...parsing that out..."meet their needs"...means different things to different people. Most businesses are cyclical, so the performance they need today is likely not the performance they need at their peek. For example...Amazon does much more business the day after Thanksgiving than they do in a random day in May. If you gather the usage stats being used in May and size it appropriately, you're going to get a call in a few months when performance is exposed.

Before I talk about latency, let me just say AWR does a great job of keeping performance data, if you have your data kept long enough...preferably at least 2 business cycles so you can do comparisons and projections.

This statement will keep AWR data for 3 years, capturing it at an aggressive 15 minute interval:

execute dbms_workload_repository.modify_snapshot_settings (interval => 15,retention => 1576800);

...at that point, see my other post re:gathering IOPS and Throughput requirements.

Anyway, I often have discussions with people who don't understand the effect of latency on OLTP databases. This is a overly-focused serial example, but its enough to make the point. Think about this...let's say you have a normal 8K block Oracle database using Netapp or EMC NFS on an active-active 10Gb network. Let's say your amazing all-SSD storage array is capable of flowing 10Gb between multiple paths. So...the time to move 8K over a 10Gb pipe is...

Throughput...
10Gb/s=10485760Kb/s
(8Kb/s)/(10485760Kb/s)=0.000000762939453125 seconds to copy 8Kb over the 10Gb pipe.

Latency...by the time it passes through your FC network, gets processed by the storage array, gets retrieved from disk, and makes it back to your server can easily exceed a few ms...but for fun let's say we're getting an 8ms response time. That's .008 seconds.

.008/0.000000762939453125=10,485.76...

...so the effect of latency on your block is 10,485X greater than the effect of throughput. If your throughput magically got faster but your latency stayed the same...performance wouldn't really improve very much. If you went from 8ms to 5ms, on the other hand, this would have a huge effect on your database performance.

There's a lot that can affect latency...usually the features in use on the storage array play a big part. CPU utilization on the storage array can become too high. This is ultra complicated for the storage array guys to diagnose. On EMC VMAX3's for example, CPU is allocated to "pools" for different features. So...even though you may not use eNAS, by default, you allocate a lot of your VMAX CPU cores to it. When your FC traffic pegs its cores and latency tanks...the administrator may think to look at the CPU utilization and not see an issue...there's free CPU available...just not in the pool used for the FC front end cores, so it creates a bottleneck. Awesome performance improvements are possible by working closely with your storage vendor to reduce latency during testing...about 6 months ago I worked with a team that achieved improvements by over 50% from the standard VMAX3 as delivered by adjusting those allocations.

All this to say...Latency is very important for common OLTP databases. Don't ignore throughput, but don't focus on it.

The last secret tweak for improving datapump performance (Part 2)

2017-08-24T08:52:00.000-07:00

In my previous post, I mentioned some of the common datapump performance tweaks we see. In this one, I want to talk about one that's never mentioned, and it might be the best of all.

5. ADOP - The last tweak...I don't think I've seen any blog posts or Oracle documentation about this as it applies to datapump...is ADOP-Auto degree of parallelization. This can be a *huge*...by 4X or more...improvement on imports, which is typically where most of your datapump time is spent. This has been around since 11.2, but until recently (12.1,12.2) its been a little difficult to control how parallel things would run at. To enable it, you simply set:

parallel_degree_policy=auto
parallel_min_time_threshold=60

(which means, if the optimizer thinks this statement will take more than 60 seconds, it will consider parallelizing it)

This is nice because quick queries will run without the overhead of parallelism, and long running queries might find value in parallelism.

Today in 12c, we have the parallel_degree_level parameter, but in 11.2 we could tweak the parallelism by adjusting max_pmbps in sys.resource_io_calibrate$. From my testing, 200=~parallel 2 or 3. A SMALLER value increases the amount of parallelism (50=~parallel 20.) Effectively this gives us the same effect as the new 12c feature...which is to make Oracle make rational decisions on how parallel the auto parallelism should be.

Datapump is a logical copy (as opposed to a physical backup/restore) so it can't copy the original indexes, it has to rebuild them. If you have a typical import with 10,000 tables and indexes, the last 100 are big, the last 10 are huge. Datapump's parallelism will rip through the small objects very quickly, with one process per object. When the time comes to rebuild the indexes on the huge tables, datapump will again assign one process per index rebuild. When the create index statement is analyzed by the optimizer, it will create it in parallel based on the algorithm derived from max_pmbps (even though the create index statement may be parallel 2 or noparallel). This will save many hours on a large datapump import. Its crazy to see a serialized create index statement with 50 busy parallel slaves...but that's what can happen.

When its done, the indexes all have the original parallel spec they started with. Nothing is any different than it would have been if you hadn't used ADOP (other than it was done much, much faster.)

One word of caution: You have to watch your system resources and parallel limits. If you have datapump running at parallel 50, that means potentially you'll be rebuilding 50 indexes simultaneously. If they're each "large" and the optimizer thinks they'll take over [parallel_min_time_threshold] seconds to rebuild, each of them could be built parallel and you could have hundreds (datapump 50 * adop 50) of parallel processes. This is a wonderful thing if your system can handle it. Depending on your parallel limit parameters, ADOP may queue the statements until you have enough parallel processes...to prevent the system from overloading. IMHO, that's also a wonderful thing, but it may be unexpected. The truly unfortunate situation is when you have them too high. You'll use up your server resources and inefficiently use CPU...and may even swap if you run low on RAM. So test!

...but that's what dry runs are for. I hope this last tweak helps you. I've seen it make miraculous differences meeting otherwise impossible SLA's for datapump export/imports. Two other posts you may want to read are:
1. Gwen Shapira has the best post on it, IMHO)
2. Kerry Osborne has a nice post on the 12c changes.

Previous post -> The last secret tweak for improving datapump performance (Part 1)
This post -> The last secret tweak for improving datapump performance (Part 2)

The last secret tweak for improving datapump performance (Part 1)

2017-08-24T08:51:00.000-07:00

There are 10,000 blog posts and oracle docs on the internets (thank you, Mr Bush) for improving datapump performance. This is one of the features used very frequently in Oracle shops around the world. And DBA's are under huge stress to meet impossible downtime SLA's for their export/import. I think they all miss the best tweak (ADOP)...but they basically summarize to one simple fact...outside of parallelism, if you have a well-tuned database on fast storage, there's not a lot more you can do to improve performance more than a few percentage. The obvious improvements are:

1. Parallelize! To quote Vizzini, "I do not think it means what you think it means."

This will create multiple processes and each will take one object and work with it, each one serialized (usually). This is great, and if all your objects are equally sized, this is perfect...but that's not typically reality. Usually you have a few objects that are much bigger than the rest and each of them by default will only get a single process. This limit really hurts during imports, when you need to rebuild large indexes...more on this later.

Check that its working as expected by hitting control-c and typing in status. Ideally, you should see all parallel slaves working. Check not just that they exist, but that they're working (verify you used %U in your dump filename if they aren't.) ie: DUMPFILE=exaexp%U.dmp PARALLEL=100

2. If you're importing into a new database, you have the flexibility to make some temporary changes to tweak things. Verify Disk Asynchronous IO is set to true (DISK_ASYNCH_IO=true) and disable all the block verification (DB_BLOCK_CHECK=FALSE, DB_BLOCK_CHECKSUM=FALSE) These aren't "game changers" but they'll give you 10-20% improvements, depending on how you had them set previously.

3. Memory Settings - Datapump parallelization uses some of the streams API's, and so the streams pool is used. Make sure you have enough memory for the shared_pool, streams_pool and the db_cache_size parameters. Consult your gv$streams_pool_advice, gv$shared_pool_advice, gv$db_cache_advice and gv$sga_target_advice views. I like to tune it so as the delta in ESTD_DB_TIME_FACTOR from one row to the row below it approaches zero, the corresponding size of the pool is close to 1. (Any more than that is a waste, any less than that is lost performance.)

Sometimes you'll see a huge dropoff and its more clear than this example...but you get the idea. If you're importing into a new database, you'll need to run the import dry run and then check these views to make sure this is tuned well.

SGA_SIZE	SGA_SIZE_FACTOR	ESTD_DB_TIME	ESTD_DB_TIME_FACTOR	Time Factor Diff
158720	0.5	12162648	1.2045
178560	0.5625	11421479	1.1311	0.0734
198400	0.625	10937800	1.0832	0.0479
218240	0.6875	10607608	1.0505	0.0327
238080	0.75	10604578	1.0502	0.0003
257920	0.8125	10375361	1.0275	0.0227
277760	0.875	10222886	1.0124	0.0151
297600	0.9375	10101716	1.0004	0.012
317440	1	10097674	1	0.0004
337280	1.0625	10017905	0.9921	0.0079
357120	1.125	9955300	0.9859	0.0062
376960	1.1875	9908850	0.9813	0.0046
396800	1.25	9905821	0.981	0.0003
416640	1.3125	9870479	0.9775	0.0035
436480	1.375	9844225	0.9749	0.0026
456320	1.4375	9826049	0.9731	0.0018
476160	1.5	9821001	0.9726	0.0005

4. Something often missed when importing into a new database, size your redo logs to be relatively huge. The redo logs will work like a cache and cycle around. Eventually if you're adding data extremely fast, the last log will fill and can't switch until the next log is cleared. "Huge" is relative to the size, speed of your database and hardware. While you're running your import, select * from v$log and make sure you see at least one "inactive" logs in front of the current log.

The best, virtually unused tweak is in the next post....

This post: The last secret tweak for improving datapump performance (Part 1)
Next post: The last secret tweak for improving datapump performance (Part 2)

Vertica Architecture (Part 2)

2017-02-22T14:53:00.001-08:00

This is continued from my previous post.

In review, I spoke with some very smart and experienced Vertica consultants regarding the DR architecture, and found the most obvious solutions all had huge drawbacks.

1. Dual-Load: Double your license costs(?), there's also the potential to have the two clusters out of synch, which means you need to put logic in your loads to handle the possibility that a load succeeds in datacenter 1 and fails in datacenter 2.
2. Periodic Incremental Backups:Need identical standby system (aka, half the capacity and performance of your hardware because the standby is typically idle)
3. Replication solutions provided by storage vendors: The recommended design uses local storage, not storage arrays, so this is difficult to implement, in addition to the expense and the potential of replicating media failures.

At first, here's what we did instead:

Initially (aka don't do this), we set up 2 failgroups, 3 nodes in datacenter 1 and 3 in datacenter 2. Failgroups in Vertica are intended for use where you could have known dependencies that are transparent to Vertica...for example, a server rack. Both failgroups are in the same cluster, and so data that's entered into nodes 1,2 or 3 get replicated automatically by Vertica to the other failgroup's nodes 4, 5 and 6.

We were trying to protect ourselves from the possibility of a complete datacenter failure, or a WAN failure. The WAN is a 10Gb, low latency dark fiber link with a ring design, so highly available. Although the network is HA, the occasional "blip" happens, where a very brief outage causes a disconnection. Clusters don't like disconnections.

We were very proud of this design until we tested it...it completely failed. It made sense...although logically we had all the data we needed in a single failgroup, if we simulated a network outage we'd see all 6 nodes go down. This is actually an intentional outcome, and a good thing. If you've worked with clusters before...you know its much better to have the cluster go down than to have it stay up in a split brain scenario and corrupt all your data. If the cluster stays up and becomes out of synch, you have to fix whatever the initial issue was, and you compound the problem with the need to restore all your data.

So...intentionally, if you have half your nodes go down, Vertica causes the whole cluster to go down, even if you have all the data you need to stay up in the surviving nodes. Oracle RAC uses a disk voting mechanism to decide which part of the cluster stays up, but there's no such mechanism in Vertica.

We were back to the 3 original options...all with their drawbacks. While pouring over the documentation looking for an out-of-the-box solution, I noticed Vertica 8 introduced a new type of node called an Execute node. Again...very little documentation on this, but I was told this was a more official way to deal with huge ingest problems like they had at Facebook (35TB/hr). Instead of using Ephimeral nodes (nodes in transition between being up and being down) like they did, you could create execute nodes that only store the catalog...they store no other data, but only exist for the purpose of ingestion.

Upon testing, we also found Execute nodes "count" as a node in the cluster...so instead of having 6 nodes-3 nodes in DC1 and 3 in DC2, we'd add a 7th node in a cloud (we chose Oracle's cloud.) Its a great use case for a cloud server because it has almost no outgoing data, almost no CPU utilization (only enough to maintain the catalog) and the only IO is for the catalog. So now, if DC1 went down, we had a quorum of 4 surviving nodes (4,5,6,7)...if DC2 went down, we still have 4 surviving nodes (1,2,3,7). If all the nodes stayed up, but the WAN between DC1 and DC2 stopped functioning, Vertica would kill one of the failgroups and continue to function...so no risk of a split brain.

We're continuing to test, but at this point, its performed perfectly. This has effectively doubled our performance and capacity because we have a 6 node cluster instead of two 3 node clusters. Its all real time, and there's no complex dual load logic to program in our application.

Next, I'll talk about Vertica backups.

Vertica Capacity Architecture

2017-02-21T15:00:00.001-08:00

Since nearly the first time I logged in to an Oracle database, I remember finding issues with documentation and the occasional error in documentation. Its understandable...usually this was due to a change or a new feature that was introduced and the documentation just wasn't updated. The real problem was in me...I was judging Oracle's documentation vs perfection...I should have lowered my expectations and appreciated what it was instead of being upset for what it wasn't.

While evaluating and designing the architecture for an HP Vertica database for a client, I gained a new appreciation for Oracle's documentation. I expected to find everything I needed to do a perfect Vertica cluster install across 2 data centers in an active/active configuration for DR. When the documentation failed and I resorted to gGoogle, I mostly found people with the same questions I had and no solutions.

Soooo...I thought I'd make a few notes on what I learned and landed on. I am by no means a Vertica expert, but I've definitely learned a lot about and I've had the opportunity to stand on the shoulders of a few giants recently.

Our requirement is to store 10TB of actual data, we don't know how well it will compress...so we're ignoring compression for capacity planning purposes. How much physical storage do you need for that much data? Vertica licensing is based on data capacity, but that's not the amount of capacity used...its the amount of data ingested. Vertica makes "projections" that (in Oracle terms) I think of as self-created materialized views and aggregates that can later be used for query re-write. Vertica will learn from your queries what it needs to do in the future to improve performance, it'll create projections and these projections use storage. Since there's columnar compression in Vertica by default, these projections are stored efficiently...and they aren't counted toward your licensed total. I've heard stories that companies have had so many (200+) that the performance of importing data was hampered...these physically stored objects are updated as data is loaded. Since projections will take up storage you have to account for that in the early design, but it completely depends on your dataset and access patterns. Estimates based on other companies I've spoken with are between 0% (everything is deduped) and 50% (their ETL is done in Vertica, so less deduplication), so lets say 35%.

Also, you're strongly recommended to use local storage (raid 1+0...mirrored and striped), and the storage is replicated in multiple nodes for protection. They call this concept k-safety. The idea is that you can lose "k" nodes, and the database would still continue to run normally. We would run K+1 (the default).

In order to do a rebalance (needed when you add or remove a node), the documentation suggests you have 40% capacity free.

Also, Vertica expects you to isolate your "catalog" metadata from your actual data, so you need to set up one mirrored raid group with ~150GB for catalog...and OS, etc. They give an example architecture using HP hardware with servers that have 24 slots for drives. 2 of them are used for mirroring the OS/Catalog, leaving 22 for your actual data. Knowing SSD's are the future for storage, the systems we worked on are Cisco UCS C-series with 24 slots filled with 100% SSD's. From the feedback from Vertica, this will help with rebuild times, but not so much with normal processing performance, since so much of Vertica is done in memory. There's a huge price increase in $/GB between 400 and 800GB drives.

So...if you have 6 nodes with 22 slots, each populated with 400GB SSD's, you have 52,800GB. Half that for raid 1+0=26,400. If you have an HA architecture, you'd expect to half that again (3 nodes in datacenter 1, 3 nodes in datacenter 2)...which brings you to 13,200GB. Since you have to keep at least 40% free for a rebalance operation, that brings you down to 7,920GB. We have to account for projections...we said the would be 35% of our dataset...which brings us to 5,148GB. All the data in Vertica is copied to 2 nodes, so half the storage again....2,574GB.

Hmmm...2.5TB of storage is less than our 10TB requirement. I'll show you how we changed the design to double capacity in my next post.

UDEV updated

2017-01-19T12:42:00.000-08:00

In my previous post, I wrote about automating the creation of your Oracle RAC cluster. One of the more complicated parts to that is using shared VMDK's and configuring UDEV to create aliases so all your RAC nodes have the same name for the same ASM disk, no matter what order udev finds the devices.

Assuming you used the VM create script in the previous post, the prerequisites of:

1. "disk.EnableUUID"="true";
2. Data on SCSI adapter 1, Redo on SCSI adapter 2, and FRA on SCSI adapter 3
3. Node equivalence

...should already be met, and this should just work. This needs to be executed as root, and should be tested in a sandbox environment until you're confident. I've used it for years, but like everything on the internet, no warranties or promises implied or otherwise. It may be found to connect to a secret government computer and play Global Thermonuclear War.

#! /bin/sh
###################################
# Name: udev_rules.sh
# Date: 5/9/2012
# Purpose: This script will create all the udev rules necessary to support
# Oracle ASM for RH 5 or RH6. It will name the aliased devices
# appropriately for the different failgroups, based on the contoller
# they're assigned to.
# Revisions:
# 5/8/2012 - JAB: Created
# 5/10/2012 - JAB: Will now modify the existing rules to allow the addition of a
# single new disk.
# 1/8/2013 - JAB: assorted RH6 related issues corrected.
###################################
data_disk=0
redo_disk=0
arch_disk=0
release_test=`lsb_release -r | awk 'BEGIN {FS=" "}{print $2}' | awk 'BEGIN {FS="."}{print $1}'`
echo "Detected RH release ${release_test}"

if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
echo -e "Detected a pre-existing asm rules file. Analyzing...\c"
for y in {1..50}
do
found_data_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-data-disk${y}`
found_redo_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-redo-disk${y}`
found_arch_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-arch-disk${y}`
if [ -n "${found_data_disk}" ]; then
let "data_disk++"
fi
if [ -n "${found_redo_disk}" ]; then
let "redo_disk++"
fi
if [ -n "${found_arch_disk}" ]; then
let "arch_disk++"
fi
echo -e ".\c"
done
echo "complete."
echo "Existing rules file contains:"
echo " ASM Data Disks: ${data_disk}"
echo " ASM Redo Disks: ${redo_disk}"
echo " ASM Arch Disks: ${arch_disk}"
new_file="false"
else
echo "Detected no pre-existing asm udev rules file. Building..."
new_file="true"
fi

for x in {a..z}
do
if [ -n "`ls /dev/sd* | grep sd${x}1 `" ] ; then
asm_test1=`file -s /dev/sd${x}1 |grep "/dev/sd${x}1: data" `
asm_test2=`file -s /dev/sd${x}1 |grep "Oracle ASM" `
#echo "Testing for sd${x}1 complete."
if [[ -n "${asm_test1}" || -n "${asm_test2}" ]] ; then
# ie: scsi_device:1:0:1:0
if [ "${release_test}" = "5" ]; then
controller=`ls /sys/block/sd${x}/device|grep scsi_device | awk 'BEGIN {FS=":"}{print $2}'`
result=`/sbin/scsi_id -g -u -s /block/sd${x}`
elif [ "${release_test}" = "6" ]; then
controller=`ls /sys/block/sd${x}/device/scsi_device | awk 'BEGIN {FS=":"}{print $1}'`
result=`/sbin/scsi_id -g -u -d /dev/sd${x}`
fi
if [ "${controller}" = "3" ]; then
if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
else
found_uuid=
fi
if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
echo "Detected a new data disk. Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
let "data_disk++"
if [ "${release_test}" = "5" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-data-disk${data_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
elif [ "${release_test}" = "6" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-data-disk${data_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
fi
fi
elif [ "${controller}" = "4" ]; then
if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
else
found_uuid=
fi
if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
echo "Detected a new Redo disk. Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
let "redo_disk++"
if [ "${release_test}" = "5" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-redo-disk${redo_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
elif [ "${release_test}" = "6" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-redo-disk${redo_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
fi
fi
elif [ "${controller}" = "5" ]; then
if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
else
found_uuid=
fi
if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
echo "Detected a new Arch disk. Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
let "arch_disk++"
if [ "${release_test}" = "5" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-arch-disk${arch_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
elif [ "${release_test}" = "6" ]; then
echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-arch-disk${arch_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
fi
fi
fi
else
echo "/dev/sd${x} is not an asm disk."
fi
fi
done
echo "Complete."

echo "To see the ASM UDEV rules: cat /etc/udev/rules.d/99-oracle-asmdevices.rules"

Updated VMWare 6 Oracle RAC vm create script

2017-01-19T12:24:00.002-08:00

I don't know if its just me, but it seems like everything I need to do has an impossible timeline. When I make VM's for testing, its SOOOO tedious to use a point-and-click interface like virtual center. In the past for large proof of concepts I've had to create literally hundreds of VM's running RAC. To make things faster and to keep my sanity, I wrote a series of scripts to make setting up an Oracle environment nearly automated.

The steps are:
1. Create the RAC node vm's (See this post below)
2. Install the OS via kickstart (or just boot from the OS install ISO and manually configure the OS)
3. Install the Oracle pre-install rpm
- If you aren't running Oracle Enterprise Linux, or if you want to customize the UID/GID etc, click < HERE >
4. Configure udev/passwordless SSH for root/Oracle
- For the udev part, this is automated via my script here
5. For Oracle Virtual Machine, Oracle Database Appliance and Exadata, the RAC Pack wrote a wonderful script called OneCommand. I find this to be a *huge* timesaver, so I extract that from the OVM OVF (found at edelivery.oracle.com) and modify the params.ini and netconfig.ini files.

That's it...if you have everything prepped, depending on your hardware you can go from nothing to running a new 4 node RAC environment in about 20 minutes, with very little "human" time.

A friend of mine integrated these steps into his company's internal cloud...filling in a few variables on a web page (server names, cpu, memory, storage sizes, db name, etc) he can click a button, go to lunch and when he comes back he can log in to his new cluster.

A few years ago I posted my VMWare Oracle RAC node create script, here's an updated version for VSphere 6 and PowerCLI 6.3 Release 1. It has the changes needed for shared VMDK's and a way to disable the change block tracking that occasionally finds its way into the VMX's. If you're setting up a flex cluster, you'll need to uncomment the asm_network lines.

Just modify the parameters in the top section. Hopefully this saves you some time!

#################################################################
## This script works with VMWare 6's change in eager zeroing
# Modify variables below
#################################################################

connect-viserver -Server VC06.poc.local -Protocol https

$vmName1 = "strora01"
$vmName2 = "strora02"
$vmName3 = "strora03"
$vmName4 = "strora04"
$rac_vm_cpu = 50
$rac_vm_ram_mb = (16GB/1MB)
$rac_vm_ram_mb_rez = (8GB/1MB)
# below is the network label in vmware for the vlan
$public_network_name = "VLAN100(pub)"
$private_network_name = "VLAN101(priv)"
#$asm_network_name = "VLAN102(asm)"
#$backup_network_name = "VLAN103(backup)"
$osstore = "os_binaries"
$osstore_size_MB = (50GB/1MB)
$orastore = "os_binaries"
$orastore_size_KB = (100GB/1KB)
$datastore1 = "ora_data1"
$datastore2 = "ora_data2"
$datastore3 = "ora_data3"
$datastore4 = "ora_data4"
$datastore_size_KB = (400GB/1KB)
$recostore1 = "ora_redo1"
$recostore2 = "ora_redo2"
$recostore3 = "ora_redo3"
$recostore4 = "ora_redo4"
$recostore_size_KB = (16GB/1KB)
$archstore1 = "ora_fra1"
$archstore2 = "ora_fra2"
$archstore3 = "ora_fra3"
$archstore4 = "ora_fra4"
$archstore_size_KB = (25GB/1KB)
$vm_hostname = "ucsblade135.poc.local"
$OS_CDROM = "[os_binaries]OS_IMAGE/V77197-01.iso"

#################################################################
# End variable section
# No edits should be necessary below this line
#################################################################

$VM1 = new-vm `
-Host "$vm_hostname" `
-Name $vmName1 `
-Datastore (get-datastore "$osstore") `
-GuestID rhel6_64Guest `
-MemoryMB 4096 `
-DiskMB $osstore_size_MB `
-NetworkName "$public_network_name" `
-DiskStorageFormat "Thin"

$vm2 = new-vm `
-Host "$vm_hostname" `
-Name $vmName2 `
-Datastore (get-datastore "$osstore") `
-GuestID rhel6_64Guest `
-MemoryMB 4096 `
-DiskMB $osstore_size_MB `
-NetworkName "$public_network_name" `
-DiskStorageFormat "Thin"

$VM3 = new-vm `
-Host "$vm_hostname" `
-Name $vmName3 `
-Datastore (get-datastore "$osstore") `
-GuestID rhel6_64Guest `
-MemoryMB 4096 `
-DiskMB $osstore_size_MB `
-NetworkName "$public_network_name" `
-DiskStorageFormat "Thin"

$VM4 = new-vm `
-Host "$vm_hostname" `
-Name $vmName4 `
-Datastore (get-datastore "$osstore") `
-GuestID rhel6_64Guest `
-MemoryMB 4096 `
-DiskMB $osstore_size_MB `
-NetworkName "$public_network_name" `
-DiskStorageFormat "Thin"

Function Change-Memory {
Param (
$VM,
$MemoryMB
)
Process {
$VMs = Get-VM $VM
Foreach ($Machine in $VMs) {
$VMId = $Machine.Id

$VMSpec = New-Object VMware.Vim.VirtualMachineConfigSpec
$VMSpec.memoryMB = $MemoryMB
$RawVM = Get-View -Id $VMId
$RawVM.ReconfigVM_Task($VMSpec)
}
}
}

Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM1
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM2
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM3
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM4

Set-VM -vm(get-vm $VM1) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $vm2) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $VM3) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $VM4) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false

Get-VM $VM1 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $vm2 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $VM3 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $VM4 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez

New-NetworkAdapter -VM $vm1 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm2 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm3 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm4 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false

Function Enable-MemHotAdd($vm){
$vmview = Get-vm $vm | Get-View
$vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec

$extra = New-Object VMware.Vim.optionvalue
$extra.Key="mem.hotadd"
$extra.Value="true"
$vmConfigSpec.extraconfig += $extra

$vmview.ReconfigVM($vmConfigSpec)
}

enable-memhotadd $vm1
enable-memhotadd $vm2
enable-memhotadd $vm3
enable-memhotadd $vm4

Function Enable-vCpuHotAdd($vm){
$vmview = Get-vm $vm | Get-View
$vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec

$extra = New-Object VMware.Vim.optionvalue
$extra.Key="vcpu.hotadd"
$extra.Value="true"
$vmConfigSpec.extraconfig += $extra

$vmview.ReconfigVM($vmConfigSpec)
}

enable-vCpuHotAdd $vm1
enable-vCpuHotAdd $vm2
enable-vCpuHotAdd $vm3
enable-vCpuHotAdd $vm4

New-HardDisk -vm($VM1) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($vm2) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($VM3) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($VM4) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_1 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_1 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_1 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_1 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

###################################

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_2 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_2 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_2 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_2 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2

#######################

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_3 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_3 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_3 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_3 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$ExtraOptions = @{
# per VMware, SAP and Oracle VMware Best Practices
"disk.EnableUUID"="true";
"ctkEnabled"="false";
"scsi0:0.ctkEnabled"="false";
"scsi0:1.ctkEnabled"="false";
"scsi1:0.ctkEnabled"="false";
"scsi1:1.ctkEnabled"="false";
"scsi1:2.ctkEnabled"="false";
"scsi1:3.ctkEnabled"="false";
"scsi2:0.ctkEnabled"="false";
"scsi2:1.ctkEnabled"="false";
"scsi2:2.ctkEnabled"="false";
"scsi2:3.ctkEnabled"="false";
"scsi3:0.ctkEnabled"="false";
"scsi3:1.ctkEnabled"="false";
"scsi3:2.ctkEnabled"="false";
"scsi3:3.ctkEnabled"="false";
"ethernet2.coalescingScheme"="disabled";
"sched.mem.pshare.enable"="false";
"numa.vcpu.preferHT"="true";
"tools.syncTime" = "False";

# per VMware's Hardening Guide - Enterprise Level
"isolation.tools.diskShrink.disable"="true";
"isolation.tools.diskWiper.disable"="true";
"isolation.tools.copy.disable"="true";
"isolation.tools.paste.disable"="true";
"isolation.tools.setGUIOptions.enable"="false";
"isolation.device.connectable.disable"="true";
"isolation.device.edit.disable"="true";
"vmci0.unrestricted"="false";
"log.keepOld"="10";
"log.rotateSize"="1000000";
"tools.setInfo.sizeLimit"="1048576";
"guest.command.enabled"="false";
"tools.guestlib.enableHostInfo"="false"
}
$vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec;
Foreach ($Option in $ExtraOptions.GetEnumerator()) {
$OptionValue = New-Object VMware.Vim.optionvalue
$OptionValue.Key = $Option.Key
$OptionValue.Value = $Option.Value
$vmConfigSpec.extraconfig += $OptionValue
}

$vmview=get-vm $vmName1 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName2 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName3 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName4 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)

new-CDDRIVE -VM $VM1 -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM2 -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM3 -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM4 -isopath "$OS_CDROM" -startConnected

function Set-MultiWriter{
param($VM, $DiskName)
echo $VM
$TempVM = $VM | Get-View
$Devicy=$TempVM.Config.Hardware.Device
foreach ($device in $Devicy) {
if($device -is [VMware.Vim.VirtualDisk] -and $device.deviceInfo.Label -eq $DiskName) {
$diskDevice = $device
$diskDeviceBaking = $device.backing
break
}
}

$spec = New-Object VMware.Vim.VirtualMachineConfigSpec
$spec.deviceChange = New-Object VMware.Vim.VirtualDeviceConfigSpec
$spec.deviceChange[0].operation = 'edit'
$spec.deviceChange[0].device = New-Object VMware.Vim.VirtualDisk
$spec.deviceChange[0].device = $diskDevice
$spec.DeviceChange[0].device.backing = New-Object VMware.Vim.VirtualDiskFlatVer2BackingInfo
$spec.DeviceChange[0].device.backing = $diskDeviceBaking
$spec.DeviceChange[0].device.backing.sharing = "sharingMultiWriter"
Write-Host "`nEnabling Multiwriter flag on on VMDK:" $diskName "for VM:" $vmname
$task = $TempVM.ReconfigVM_Task($spec)
$task1 = Get-Task -Id ("Task-$($task.value)")
$task1 | Wait-Task
}
Set-MultiWriter $VM1 "Hard disk 3"
Set-MultiWriter $VM1 "Hard disk 4"
Set-MultiWriter $VM1 "Hard disk 5"
Set-MultiWriter $VM1 "Hard disk 6"
Set-MultiWriter $VM1 "Hard disk 7"
Set-MultiWriter $VM1 "Hard disk 8"
Set-MultiWriter $VM1 "Hard disk 9"
Set-MultiWriter $VM1 "Hard disk 10"
Set-MultiWriter $VM1 "Hard disk 11"
Set-MultiWriter $VM1 "Hard disk 12"
Set-MultiWriter $VM1 "Hard disk 13"
Set-MultiWriter $VM1 "Hard disk 14"
Set-MultiWriter $VM2 "Hard disk 3"
Set-MultiWriter $VM2 "Hard disk 4"
Set-MultiWriter $VM2 "Hard disk 5"
Set-MultiWriter $VM2 "Hard disk 6"
Set-MultiWriter $VM2 "Hard disk 7"
Set-MultiWriter $VM2 "Hard disk 8"
Set-MultiWriter $VM2 "Hard disk 9"
Set-MultiWriter $VM2 "Hard disk 10"
Set-MultiWriter $VM2 "Hard disk 11"
Set-MultiWriter $VM2 "Hard disk 12"
Set-MultiWriter $VM2 "Hard disk 13"
Set-MultiWriter $VM2 "Hard disk 14"
Set-MultiWriter $VM3 "Hard disk 3"
Set-MultiWriter $VM3 "Hard disk 4"
Set-MultiWriter $VM3 "Hard disk 5"
Set-MultiWriter $VM3 "Hard disk 6"
Set-MultiWriter $VM3 "Hard disk 7"
Set-MultiWriter $VM3 "Hard disk 8"
Set-MultiWriter $VM3 "Hard disk 9"
Set-MultiWriter $VM3 "Hard disk 10"
Set-MultiWriter $VM3 "Hard disk 11"
Set-MultiWriter $VM3 "Hard disk 12"
Set-MultiWriter $VM3 "Hard disk 13"
Set-MultiWriter $VM3 "Hard disk 14"
Set-MultiWriter $VM4 "Hard disk 3"
Set-MultiWriter $VM4 "Hard disk 4"
Set-MultiWriter $VM4 "Hard disk 5"
Set-MultiWriter $VM4 "Hard disk 6"
Set-MultiWriter $VM4 "Hard disk 7"
Set-MultiWriter $VM4 "Hard disk 8"
Set-MultiWriter $VM4 "Hard disk 9"
Set-MultiWriter $VM4 "Hard disk 10"
Set-MultiWriter $VM4 "Hard disk 11"
Set-MultiWriter $VM4 "Hard disk 12"
Set-MultiWriter $VM4 "Hard disk 13"
Set-MultiWriter $VM4 "Hard disk 14"

What amuses Tom Kyte? (aka: To wrap, or not to wrap?) Selective Encryption Part 3 of 3

2016-04-28T14:49:00.001-07:00

So...this post is not about rap, its about wrapping plsql. If you've come here looking for a music site...run away! There's nothing about Jay-Z, the Beyonce formation Tour, Kanye West, Lil Wayne or Drake.

If you think you're getting security by wrapping your plsql, stick around...its not protection. AS Tom said, "I put "protection" in quotes because I've always been amused by 'wrapping' code."

In my previous post, I had a function that returned a 256-bit key, and mentioned a few ways of protecting it...but cautioned about using plsql wrap. This is only slightly better than doing nothing.

The function in the previous post is:

create or replace function blackan.my_key return varchar2 as
begin
return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';
end;

I run the wrap utility and it creates my_function.plb:

wrap iname=my_function.sql

Taking a peek in the file I see:

create or replace function blackan.my_key wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
7e ba
mtACEgpb2btQRc85YlHfKReZyCowgyr6f8tqynSm3f80Fbor4H4vlzbU3ANp9BtM2jLlkqnk
vF5IANpFigWMarvPqiRJdFXQs+5cQGjZZxauuFyxO9bW1UkgQotGR+YJ78l+VqExIcanEJXy
LoQv715qogTgWUFi22C8R47oRG4zYWj5tlQcWQ==
/

You might be thinking, "Wow! That sure looks secure! Comparing it to the original, I can't see anything that's the same!" Don't think that...its a false sense of security. There are TONS of post/utilities...even websites that will "unwrap" your plsql.

Here's one:
http://www.codecrete.net/UnwrapIt

If you paste the wrapped code above in to the website, you'll see:

FUNCTION my_key RETURN VARCHAR2 AS 
BEGIN 
  RETURN '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 
END;

So...don't trust the security of wrapping plsql.

On the other hand, this allows you to get insight...when metalink is failing you, you may be able to paste the internal wrapped plsql packages from Oracle and diagnose your own issues.

I hope this helps you see you shouldn't trust the plsql wrap to keep your data secure.

SDE Part 1
SDE Part 2
YOU ARE HERE>> SDE Part 3

Selective Encryption Part 2 of 3

2016-04-28T13:55:00.000-07:00

In my previous post I talked about the importance of encryption in the database. To be fair, sometimes it really isn't important, its just required for some reason. This is more of a solution to the 2nd, as part of an overall security scheme. Also, for a scenario where some of the data in a table is replicated to other databases/platforms, etc, this could be a way to auto-obfuscate. The encrypted data would be moved through GG or w/e CDC you use without a key...making it very secure after it leaves the database.

We're going to use a block cipher...so first, you need to pick a cipher algorithm (here are the block options), a block cipher chaining modifier and a padding modifier from below, and add up their associated numbers. (ie: AES256 with CBC and PKCS5 would be 8+256+4096=4360) We'll use 4360 for our example.

Here are some of the options in dbms_crypto:

-- Block Cipher Algorithms
ENCRYPT_DES := 1;
ENCRYPT_3DES_2KEY := 2;
ENCRYPT_3DES := 3;
ENCRYPT_AES := 4;
ENCRYPT_PBE_MD5DES := 5;
ENCRYPT_AES128 := 6;
ENCRYPT_AES192 := 7;
ENCRYPT_AES256 := 8;

-- Block Cipher Chaining Modifiers
CHAIN_CBC := 256;
CHAIN_CFB := 512;
CHAIN_ECB := 768;
CHAIN_OFB := 1024;

-- Block Cipher Padding Modifiers
PAD_PKCS5 := 4096;
PAD_NONE := 8192;
PAD_ZERO := 12288;
PAD_ORCL := 16384;

1. First, create a key and capture the string that gets generated...don't lose this key:

set serveroutput on size 1000000
declare
v_Number number := 256/8; --256 bit key
v_Key RAW(32);
begin
v_Key := DBMS_CRYPTO.RANDOMBYTES(v_Number);
dbms_output.put_line(v_Key);
end;

2. Now replace the red string below with the output generated above. This is meant as a very simple example. In real life you'd want to do everything you can to make this complex and difficult to read. Putting it in a package and wrapping it would make it slightly less simple to get at. Putting this data somewhere else encypted (like the OS or a different db) would be better. How you do this is up to you...but just creating a plain text function isn't sufficient, and depending on plsql wrap is a bad idea...as Tom Kyte put it, "Using the latest wrap would provide the highest degree of "protection". That is all. I put "protection" in quotes because I've always been amused by 'wrapping' code." I'll explain that more in a different post.

create or replace function blackan.my_key return varchar2 as

begin

return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';

end;

3. Now generate the base table and the decrypting view on the table:

drop table blackan.hacker_booty_base;

create table blackan.hacker_booty_base (EmpName raw(120), Emp_Num raw(88), SSN raw(80), Credit_Card raw(120), CC_Ex_Date raw(88), Address raw(120), PW raw(120));

create or replace view blackan.hacker_booty as

select

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(EmpName,4360,my_key), 'AL32UTF8'),1,30) EmpName,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Emp_Num,4360,my_key), 'AL32UTF8'),1,22) Emp_Num,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(SSN,4360,my_key), 'AL32UTF8'),1,20) SSN,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Credit_Card,4360,my_key), 'AL32UTF8'),1,30) Credit_Card,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(CC_Ex_Date,4360,my_key), 'AL32UTF8'),1,30) CC_Ex_Date,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Address,4360,my_key), 'AL32UTF8'),1,30) Address,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(PW,4360,my_key), 'AL32UTF8'),1,30) PW

from hacker_booty_base;

4. Now create the encrypting "instead of" trigger on the view to redirect the dml to the base table after encrypting it:

CREATE OR REPLACE TRIGGER blackan.ioiudr_hacker_booty

INSTEAD OF INSERT OR UPDATE OR DELETE

ON blackan.hacker_booty

FOR EACH ROW

BEGIN

if INSERTING then

insert into hacker_booty_base values (

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW, 'AL32UTF8'),4360,my_key));

elsif updating then

update hacker_booty_base set

EmpName=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

Emp_Num=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num, 'AL32UTF8'),4360,my_key),

SSN=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN, 'AL32UTF8'),4360,my_key),

Credit_Card=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card, 'AL32UTF8'),4360,my_key),

CC_Ex_Date=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date, 'AL32UTF8'),4360,my_key),

Address=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address, 'AL32UTF8'),4360,my_key),

PW=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW, 'AL32UTF8'),4360,my_key)

where SSN=:OLD.SSN;

else

delete from hacker_booty_base where SSN=:OLD.SSN;

end if;

end;

...and that's it. Let's insert a row of fake data:

insert into blackan.hacker_booty values ('Richy Rich',1,'123-45-6789','111 2222 3333 4444', sysdate+365, '1 Main St, St Louis, MO, 12345', 'Shhh');

When you look at the actual data-at-rest, its encrypted:

select empname, emp_num, ssn from blackan.hacker_booty_base;

EMPNAME	EMP_NUM	SSN
454460100E82C91F8B26AEE3E35EE3E8	9F1BE1034151056A760A880E5D7BC5C3	29B471D31D8E2255D1B0C7B6D5084759

When you look at the data via your application, which would use the view, you see the data exactly as you entered it:

select empname, emp_num, ssn from blackan.hacker_booty;

EMPNAME,EMP_NUM,SSN

Richy Rich 1 123-45-6789

SDE Part 1
YOU ARE HERE>> SDE Part 2
SDE Part 3

Selective Encryption - Part 1 of 3

2016-04-28T09:06:00.003-07:00

Encryption in databases is nothing new, but its becoming a more prominent topic every day. One of my first experiences with it was in dealing with a database that had extremely confidential data, and it was a known target to foreign countries who were actively attempting to access the network to get to the database. Not to be overly dramatic, but if this data was compromised, people would be killed. I wasn't working for a Gov't agency at the time...so don't read too much into all I just said. The point is, every company stores data that needs to be secured, and the reality of it is, security is an illusion we can tell ourselves we have until we're proven wrong. So from that you have two choices:

1. Give up, its inevitable our data will be breached.
2. Make it as difficult as possible for our data to be breached, and if it is, encrypt everything valuable to make it more difficult at that point.

I choose 2. :) In the scenario I mentioned, we had security layer on security layer..and I don't mean to say this is the complete solution to make your data secured. Given the data in your database will eventually be in the hands of people who took it, what can you do? Oracle sells TDE, which does a lot of the work for you, but its a licensed feature. It used to include transparent data encryption at rest and data in flight. Thanks to a security issue a few years ago that exposed encrypted network traffic, encryption of network traffic in flight is now part of your normal db license (aka no additional cost.)

Strongauthentication services (Kerberos, PKI, and RADIUS) and network encryption (native network encryption and SSL/TLS) are no longer part of Oracle Advanced Security andare available in all licensed editions of all supported releases of the Oracle database.

What about data at rest and backups? Every company keeps PII (personal identifiable information) on their employees...Name, SSN, Address, Phone number, etc. What about your company's customers? If your unencrypted data is breached, your company is required by law to notify the people who may have had PII stolen. Would your company lose future business if this happened? I've been told the FCC requires databases storing network endpoint addresses to be 256-bit encrypted. For almost all dba's, encryption isn't really optional anymore.

So...We talked about TDE...there's also something Oracle refers to as SDE (selective data encryption) ...and this is free (1930738.1). This is really easy to implement...its not perfect, but its better than nothing. I'll show you how in the next post.

YOU ARE HERE>> SDE Part 1
SDE Part 2
SDE Part 3