Log Buffer #442: A Carnival of the Vanities for DBAs

This Log Buffer Edition collects and then showers around some of the information-rich blog posts from Oracle, SQL Server and MySQL.Oracle:Generic Java Server for Static content and Directory Listing using API mode or command line mode.OEM agents on each host upload data to your management servers every few minutes.exitcommit … or your career down the drain.Encryption is the Easy Part; Managing those Keys is Difficult.Managing the OBIEE BI Server Cache from ODI 12c.SQL Server:Email addresses are very prevalent in IT systems and often used as a natural primary key. The repetitive storage of email addresses in multiple tables is a bad design choice. Following is a design pattern to store email addresses in a single table and to retrieve them efficiently.OpenStack: The Good and Not-So-Good Bits.By defining server- and database-level audits, you can record just about any kind of event that occurs in SQL Server, which can be an invaluable source of security troubleshooting and forensic information when security breaches occur.Koen Verbeeck shows how to easily extract metadata from files in your directories with Power Query.Implementing John Conway’s Game of Life in Microsoft SQL Server.MySQL:Oracle HA, DR, data warehouse loading, and license reduction through edge apps.Easy Load-balancing and High-availability using MySQL Router.When hosting data on Amazon turns bloodsport.MySQL 5.7 Labs — Using Loopback Fast Path With Windows 8/2012.How to evaluate if MySQL table can be recovered. Learn more about Pythian’s expertise in Oracle , SQL Server & MySQL.

MySQL Plugin for Oracle Enterprise Manager on VirtualBox: installation gotchas

At the last OOW MySQL Plugin for Oracle Enterprise Manager (OEM) was recognized as most popular MySQL product. If you don’t have OEM installed, but want to test the plugin you can download OEM virtual box template. But, althought this is the easiest way to get started, you still need to make few additions. At least I had to do them when deployed such installation for MySQL Support Team.Here they are. I prefer to use command line when possible.0. Import virtual machine image and change network adapter to working one, then allow to connections via rdesktop:sudo vboxmanage import VBox_EM12cR4.ova –vsys 0 –vmname  myhost-vbox04 \–vsys 0 –unit 17 –disk  /data0/vbox/myhost-vbox04/myhost-vbox04.vmdk sudo vboxmanage modifyvm myhost-vbox04 –bridgeadapter1 ‘eth0’sudo vboxmanage modifyvm myhost-vbox04 –vrdeaddress myhost –vrde on –vrdeport 90041. The machine comes with 3G RAM which is not enough. Better to increase up to 8::sudo vboxmanage modifyvm myhost-vbox04 –memory 81922. Also 40G of disk space will be used completely right after you start OEM. Therefore it is better to don’t follow me and don’t reach this point, but increase it right after installing the machine. I increased mine up to 100G. The machines comes with vmdk disk format which does not support dynamic size change. Therefore you need to convert it to vdi format first, then resize.sudo vboxmanage clonehd myhost-vbox04.vmdk myhost-vbox04.vdi –format vdisudo vboxmanage modifyhd myhost-vbox04.vdi –resize 1024003. Then convert it back to vmdk and replace old disk with new one:sudo vboxmanage clonehd myhost-vbox04.vdi myhost-vbox04-01.vmdk –format vmdksudo vboxmanage storageattach myhost-vbox04 –storagectl “SATA” –device 0  \
–port 0 –type hdd –medium /data0/vbox/myhost-vbox04/myhost-vbox04-01.vmdk4. We are not done yet: 60G will stay invisible unless we change disk partitions. I did it with help of utility GParted, but you can use any, including system’s.5. Turn machine off and eject GParted CD:sudo vboxmanage storageattach myhost-vbox04 –storagectl “IDE” –port 0 \
–device 0 –medium emptydrive6. Boot the machine, login as root user (password is welcome1) and confirm that although OS ecognizes extended disk, but partition which holds OEM installation still full:$ /sbin/fdisk -lDisk /dev/sda: 107.3 GB, 107374182400 bytes255 heads, 63 sectors/track, 13054 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytes   Device Boot      Start         End      Blocks   Id  System/dev/sda1   *           1          13      104391   83  Linux/dev/sda2              14       13055   104753177+  8e  Linux LVMDisk /dev/dm-0: 36.5 GB, 36574330880 bytes255 heads, 63 sectors/track, 4446 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesDisk /dev/dm-0 doesn’t contain a valid partition tableDisk /dev/dm-1: 7314 MB, 7314866176 bytes255 heads, 63 sectors/track, 889 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesDisk /dev/dm-1 doesn’t contain a valid partition table$ df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup00-LogVol00                       33G   32G     0 100% //dev/sda1              99M   23M   71M  25% /boottmpfs                 4.0G     0  4.0G   0% /dev/shmThis happens, because OEM is on LVM partition and we need to fix it too:$ /sbin/lvmlvm> pvresize /dev/sda2lvm> lvextend -L+55G /dev/mapper/VolGroup00-LogVol00  Extending logical volume LogVol00 to 89.06 GB  Logical volume LogVol00 successfully resizedlvm> quit  Exiting. $ /sbin/resize2fs /dev/mapper/VolGroup00-LogVol00resize2fs 1.39 (29-May-2006)Filesystem at /dev/mapper/VolGroup00-LogVol00 is mounted on /; on-line resizing requiredPerforming an on-line resize of /dev/mapper/VolGroup00-LogVol00 to 23347200 (4k) blocks.The filesystem on /dev/mapper/VolGroup00-LogVol00 is now 23347200 blocks long.$ df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup00-LogVol00                       87G   32G   51G  39% //dev/sda1              99M   23M   71M  25% /boottmpfs                 4.0G     0  4.0G   0% /dev/shmVoi-la! Now we can logout from root account and start OEM.7. But not yet if you need to connect to this virtual machine via ssh and cannot tune your network in such a way that emcc.example.com will be resolved to the virtual machine. In my case I had to connect to this machine via ssh and I cannot tune my network, only virtual machine’s options. So I set network options as usual (GUI can be used) and changed ORACLE_HOSTNAME from emcc.example.com to appropriate value in .bashrc and ./start_agent.sh files.Unfortunately this change does not instruct WebLogic to listen remote port even after I disabled machine’s firewall, so I still have to use rdesktop to be able to see OEM GUI.8. Finally we can start OEM as described in the README file, provided at edelivery.oracle.com for this virtual machine: do not use ./start_all.sh, but start each part separately:./start_db.sh/u01/OracleHomes/Middleware/oms/bin/emctl start oms./start_agent.shAnd, last but not least, it is better to perform OEM setup wearing such a t-shirt:I would not describe how to setup MySQL Plugin, because MySQL User Reference Manual contains detailed and clear instructions. I’d better show few screenshots how you can monitor both remote and local servers.Just connected with a server on remote machine:
And on local one:
After test load with help of mysqlslap utility:
And this strange chart show how MySQL works on virtual machine:

Book review : EM12c

OCM Oracle ACE Porus Homi Havewala wrote a new book on Cloud Control Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos It is a book about Enterprise Manager, but also a book about the Chaos. There is no separation of roles […] there is no DBA Designer (the Senior DBA) […] and no […]

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569