Skip navigation.

Feed aggregator

Oracle Management Cloud – IT Analytics

Marco Gralike - Thu, 2016-04-07 05:54
In this post I will give you a first glance of a demo environment of…

next generation of compute services

Pat Shuff - Thu, 2016-04-07 01:07
Years ago I was a systems administrator at a couple of universities and struggled making sure that systems were operational and supportable. The one thing that frustrated me more than anything else was how long it took to figure out how something was configured. We had over 100 servers in the data center and on each of these server we had departmental web servers, mail servers, and various other servers to serve the student and faculty users. We standardized on an Apache web server but there were different versions, different configurations, and different additions to each one. This was before virtualization and golden masters became a trendy topic and things were built from scratch. We would put together Linux server with Apache web servers, PHP servers, and MySQL. These later became called LAMP servers. Again, one frustration was the differences between the different versions, how they were compiled, and how they were customized to handle a department. It was bad enough that we had different Linux versions but we had different versions of every other software combination. Debugging became a huge issue because you first had to figure out how things were configure then you had to figure out where the logs were stored and then could start looking at what the issue was.

We have been talking about cloud compute services. In the past blogs we have talked about how to deploying an Oracle Linux 6.4 server onto compute clouds in Amazon, Azure, and Oracle. All three look relatively simple. All three are relatively robust. All three have advantages and disadvantages. In this blog we are going to look at using public domain pre-compiled bundles to deploy our LAMP server. Note that we could download all of these modules into out Linux compute services using a yum install command. We could figure out how to do this or look at web sites like digitalocean.com that go through tutorials on how to do this. It is interesting buy I have to ask why. It took about 15 minutes to provision our Linux server. Doing a yum update takes anywhere from 2-20 minutes based on how old you installation is and how many patches have been released. We then take an additional 10-20 minutes to download all of the other modules, edit the configuration files, open up the security ports, and get everything started. We are 60 minutes into something that should take 10-15 minutes.

Enter stage left, bitnami.com. This company does exactly what we are talking about. They take public domain code and common configurations that go a step beyond your basic compute server and provision these configurations into cloud accounts. In this blog we will look at provisioning a LAMP server. We could have just as easily have configured a wiki server, tomcat server, distance education moodle server, or any other of 100+ public domain configurations that bitmai supports.

The first complexity is linking your cloud accounts into the bitnami service. Unfortunately, the accounts are split into three different accounts; oracle.bitnami.com, aws.bitnami.com, and azure.bitnami.com. The Oracle and Azure account linkages are simple. For Oracle you need to look up the rest endpoint for the cloud service. First, you go to the top right, click the drop down to do account management.

From this you need to look up the rest endpoint from the Oracle Cloud Console by clicking on the Details link from the main cloud portal.

Finally, you enter the identity domain, username, password, and endpoint. With this you have linked the Oracle Compute Cloud Services to Bitnami.

Adding the Azure account is a little simpler. You go to the Account - Subscriptions pull down and add account.

To add the account you download a certificate from the Azure portal as described on the bitnami.com site and import it into the azure.bitnami.com site.

The Amazon linkage is a little more difficult. To start with you have to change your Amazon account according to Bitnami Instructions. You need to add a custom policy that allows bitnami to create new EC2 instances. This is a little difficult to initially understand but once you create the custom policy it becomes easy.

Again, you click on the Account - Cloud Accounts to create a new AWS linkage.

When you click on the create new account you get an option to enter the account name, shared key, and secret key to your AWS account.

I personally am a little uncomfortable providing my secret key to a third party because it opens up access to my data. I understand the need to do this but I prefer using a public/private ssh key to access services and data rather than a vendor provided key and giving that to a third party seems even stranger.

We are going to use AWS as the example for provisioning our LAMP server. To start this we go to http://aws.bitnami.com and click on the Library link at the top right. We could just as easily have selected azure.bitnami.com or oracle.bitnami.com and followed this exact same path. The library list is the same and our search for a LAMP server returns the same image.

Note that we can select the processor core count, disk size, and data center that we will provision into. We don't get much else to choose from but it does the configuration for us and provisions the service in 10-15 minutes. When you click the create key you get an updated screen that shows progress on what is being done to create the VM.

When the creation is complete you get a list of status as well as password access to the application if there were a web interface to the application (in this case apache/php) and an ssh key for authentication as the bitnami user.

If you click on the ppk link at the bottom right you will download the private ssh key that bitnami generates for you. Unfortunately, there is not a way of uploading your own keys but you can change that after the fact for the users that you will log in as.

Once you have the private key, you get the ip address of the service and enter it into putty for Windows and ssh for Linux/Mac. We will be logging in as the user bitnami. We load the ssh key into the SSH - Auth option in the bottom right of the menu system.

When we connect we will initially get a warning but can connect and execute common commands like uname and df to see how the system is configured.

The only differences between the three interfaces is the shapes that you can choose from. The Azure interface looks similar. Azure has fewer options for processor configuration so it is shown as a list rather than a sliding scale that changes the processor options and price.

The oracle.bitnami.com create virtual machine interface does not look much different. The server selection is a set of checkboxes rather than a radio checkbox or a sliding bar. You don't get to check which data center that you get deployed into because this is tied to your account. You can select a different identity domain which will list a different data center but you don't get a choice of data centers as you do with the other services. You are also not shown how much the service will cost through Oracle. The account might be tied to an un-metered service which comes in at $75/OCPU/month or might be tied to a metered service which comes in at $0.10/OCPU/hour. It is difficult to show this from the bitnami provisioning interface so I think that they decided to not show the cost as they do with the other services.

In summary, using a service like bitnami for pre-configured and pre-compiled software packages is the future because it has time and cost advantages. All three cloud providers have marketplace vendors that allow you to purchase commercial packages or deploy commercial configurations where you bring your own license for the software. More on that later. Up next, we will move up the stack and look at what it takes to deploy a the Oracle database on all three of these cloud services.

Links for 2016-04-06 [del.icio.us]

Categories: DBA Blogs

Use external property file in WLST

Darwin IT - Thu, 2016-04-07 00:41
I frequently create a wlst script, that needs properties. Not so exciting, but how to do that in a convenient way, and how to detect in a clean way that properties aren't set?

You could read a property file like described here. The basics are to use in fact Java to create a properties object and a FileInputStream to read it:
#Script to load properties file.

from java.io import File
from java.io import FileInputStream
from java.util import Properties


#Load properties file in java.util.Properties
def loadPropsFil(propsFil):

inStream = FileInputStream(propsFil)
propFil = Properties()
propFil.load(inStream)

return propFil

I think the main disadvantage is that it clutters the script-code and you need to call 'myPorpFil.getProperty(key)' to get the property value.

Following the documentation you can use the commandline option '-loadProperties propertyFilename' to explicitly provide a property file. I found this actually quite clean. Every property in the file becomes automatically available as a variable in your script.

Besides that I found a teriffic blog-post on error handling in wlst. It states that with ' except NameError, e:' you can handle the reference to a variable that is not declared earlier.

I combined these two sources to come up with a script template that alows me to provide property files for different target environments as a commandline option, while detecting if properties are provided. So let's assume you create a porpererty file named for instance 'localhost.properties' like:
#############################################################################
# Properties voor localhost Integrated Weblogic
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2016-04-06
#
#############################################################################
#
# Properties voor localhost
adminUrl=localhost:7101
adminUser=weblogic
adminPwd=welcome1
clustername=LocalCluster
# Generieke properties voor het creeeren van JMS componenten
#jmsFileStoresBaseDir=/app/oracle/config/cluster_shared/filestore/
jmsFileStoresBaseDir=c:/Data/JDeveloper/SOA/filestore
#Filestore 01
...

Then you can use that with the following script, named for instance 'createJMSServersWithFileStoreV2.py':
#############################################################################
# Create FileStores and JMS Servers
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2016-04-06
#
#############################################################################
# Modify these values as necessary
import sys, traceback
scriptName = 'createJMSServersWithFileStoreV2.py'
#
#
def usage():
print 'Call script as: '
print 'Windows: wlst.cmd'+scriptName+' -loadProperties localhost.properties'
print 'Linux: wlst.sh'+scriptName+' -loadProperties environment.properties'
print 'Property file should contain the following properties: '
print "adminUrl='localhost:7101'"
print "adminUser='weblogic'"
print "adminPwd='welcome1'"

def main():
try:
#Connect to administration server
print '\nConnect to AdminServer via '+adminUrl+' with user '+adminUser
connect(adminUser, adminPwd, adminUrl)
...
except NameError, e:
print 'Apparently properties not set.'
print "Please check the property: ", sys.exc_info()[0], sys.exc_info()[1]
usage()
except:
apply(traceback.print_exception, sys.exc_info())
stopEdit('y')
exit(exitcode=1)

#call main()
main()
exit()

You can call it like 'wlst createJMSServersWithFileStoreV2.py -loadProperties localhost.properties'. If you don't provide a property file you'll get:
e:\wls>wlst createJMSServersWithFileStoreV2.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Apparently properties not set.
Please check the properties: exceptions.NameError adminUrl
Call script as:
Windows: wlst.cmdcreateJMSServersWithFileStoreV2.py -loadProperties localhost.properties
Linux: wlst.shcreateJMSServersWithFileStoreV2.py -loadProperties environment.properties
Property file should contain the following properties:
adminUrl='localhost:7101'
adminUser='weblogic'
adminPwd='welcome1'


Exiting WebLogic Scripting Tool.


e:\wls>

Pretty clean. You could even use the 'except NameError, e:' construct to conditionally execute code when properties are set by ignoring/handling the situation when particular properties are intentionally not provided.

Tomcat Runtime added to Web Console of IBM Bluemix

Pas Apicella - Thu, 2016-04-07 00:05
I almost always use the tomcat buildpack within IBM Bluemix for my Java based applications. By default IBM bluemix will use the IBM Liberty buildpack for java apps unless you specify otherwise. The buildpacks on Bluemix can be viewed using "cf buildpacks" and the tomcat buildpack is referred to as "java_buildpack"

So to use the tomcat buildpack in a manifest.yml you would target it as follows

applications:
- name: pas-javaapp
  memory: 512M
  instances: 1
  host: pas-javaapp
  domain: mybluemix.net
  path: ./passapp.war
  buildpack: java_buildpack

Now the Web Console catalog for "Runtimes" shows Tomcat for those creating an application from the Console itself. This was done for those who wish to use Tomcat on Bluemix can cleary see it's an option as per the screen shot below and don't have to start with the Liberty Buildpack if they don't wish to do so.


http://feeds.feedburner.com/TheBlasFromPas
Categories: Fusion Middleware

Partner Webcast – Oracle WebLogic Server 12.2.1 Multitenancy and Continuous Availability

As part of the latest major Oracle Fusion Middleware release, Oracle announced the largest release of Oracle WebLogic Server in a decade. Oracle WebLogic Server 12c, the world’s first cloud-native,...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle Management Cloud – Application Performance Monitoring

Marco Gralike - Wed, 2016-04-06 15:42
A while ago I created a first post about the Oracle Management Cloud ( #OMC…

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

Pythian Group - Wed, 2016-04-06 14:38

This Log Buffer Edition rounds up Oracle, SQL Server, and MySQL blog posts of the week.

Oracle:

When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10? within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING”.

Most developers have struggled with wires in SOA composites. You may find yourself in a situation where a wire has been deleted. Some missing wires are restored by JDeveloper. Other missing wires have to be added manually, by simply re-connecting the involved adapters and components. Simple.

In-Memory Parallel Query and how it works in 12c.

Oracle recently launched a new family of offerings designed to enable organizations to easily move to the cloud and remove some of the biggest obstacles to cloud adoption. These first-of-a-kind services provide CIOs with new choices in where they deploy their enterprise software and a natural path to easily move business critical applications from on premises to the cloud.

Two Oracle Server X6-2 systems, using the Intel Xeon E5-2699 v4 processor, produced a world record x86 two-chip single application server SPECjEnterprise2010 benchmark result of 27,509.59 SPECjEnterprise2010 EjOPS. One Oracle Server X6-2 system ran the application tier and the second Oracle Server X6-2 system ran the database tier.

SQL Server:

To be able to make full use of the system catalog to find out more about a database, you need to be familiar with the metadata functions.

Powershell To Get Active Directory Users And Groups into SQL!

A code review is a serious business; an essential part of development. Whoever signs off on a code review agrees, essentially, that they would be able to support it in the future, should the original author of the code be unavailable to do it.

Change SQL Server Service Accounts with Powershell

Learn how to validate integer, string, file path, etc. input parameters in PowerShell as well as see how to test for invalid parameters.

MySQL:

The MySQL Utilities has announced a new beta release of MySQL Utilities. This release includes a number of improvements for usability, stability, and a few enhancements.

In this webinar, we will discuss the practical aspects of migrating a database setup based on traditional asynchronous replication to multi-master Galera Cluster.

Docker has gained widespread popularity in recent years as a lightweight alternative to virtualization. It is ideal for building virtual development and testing environments. The solution is flexible and seamlessly integrates with popular CI tools.

How ProxySQL adds Failover and Query Control to your MySQL Replication Setup

Read-write split routing in MaxScale

Categories: DBA Blogs

Birmingham City University (BCU) Talk #5

Tim Hall - Wed, 2016-04-06 12:13

bcuI had a day off work today so I could go over to Birmingham City University (BCU) and do a talk to the students.

Today’s session was more about giving them an idea of where databases fit into the big picture as far as data and data processing is concerned. I obviously come at this as a relational database guy, but the presentation also included NoSQL and Hadoop. I hope nobody is naive enough anymore to think relational databases are the correct solution for every use case, so it’s important to be objective about this stuff, rather than to push your own agenda.

Over recent weeks I’ve spent time trawling through material to get a feel for the subject matter, and it’s quite depressing to see the territorial pissing that goes on between all these camps. Each trying to convince you their solution is suitable for use cases where it clearly is not. To be fair, most of this is not coming from the experts themselves, but from the hangers on who can’t cope with the fact their favourite tech might not be perfect. We’ve all been there!

Mail Subscription Change

Michael Feldstein - Wed, 2016-04-06 11:57

By Michael FeldsteinMore Posts (1068)

For those of you who subscribe to e-Literate by email, be aware that we’re switching over to a new system for handling emails today. Among other things, we’ve had complaints that a few people had trouble unsubscribing.

Which is bad.

The new plugin, Mailchimp, will hopefully solve this problem while enabling us to do some new things we’ve been thinking about as well. (More on that soon.)

We will switch over to the new plugin shortly after I post this message and will be publishing one or two new posts in the next 24 hours. So if you don’t receive any emails by this time tomorrow, or if you have any other email-related problems, then please let us know.

The post Mail Subscription Change appeared first on e-Literate.

My Presentation at APEX Connect 2016

Denes Kubicek - Wed, 2016-04-06 11:12
On 28th of April I will be presenting at APEX Connect in Berlin. The topic is Integration of APEX with HERE Maps. If you are interested to see how you can integrate APEX with Maps (HERE or Google or some other) please join the presentation. The best thing is that this integration ist done using APEX Plugins only. I will try to show as many functionalities as possible including Routing, Tracing, Positioning and a combination of all the features together.

Categories: Development

Six things I learned about privacy from some of the world’s top IT pros

Pythian Group - Wed, 2016-04-06 07:32

 

Everyone has their own experiences with data privacy. Ask almost anyone, and they’ll be able to tell you how a retail company found some innovative way of collecting their personal data, or how Facebook displayed a suggested post or ad that was uncannily targeted at them based on personal information they did not purposely share.

 

The discussion about privacy can get pretty interesting when you get a group of CIOs and IT leaders in the same room to talk about it.  And that’s just what we did at Pythian’s Velocity of Innovation (Velocity) events in New York, San Francisco and Sydney. With their ears constantly to the ground for the latest trends and unparalleled insider IT knowledge, there’s no better group with whom to talk security and privacy than our Velocity panelists and attendees.

 

At Pythian we manage the revenue-generating systems for some of the world’s leading enterprises, and work with our clients on some of the most difficult security and privacy issues. But staying on top of the evolving threats to data privacy is a constant challenge, even for the most knowledgeable security experts. One of our biggest challenges is helping our clients use data responsibly to improve customer experiences, while protecting individual privacy and safeguarding personally identifiable information from malicious threats. So there’s always more we can learn. This is one of the reasons we keep listening to our customers and peers through various channels and events like our Velocity discussions.

 

Privacy is a high stakes game for companies that want to maximize their use of client data while adhering to legislation designed to protect personal information. In many countries, new, more rigorous laws are emerging to enable individuals to maintain more control over their personal information, and to help them understand when they are being monitored or when their personal information is recorded, shared or sold. These laws include the recently  reformed  EU Data protection rules and PIPEDA in Canada. But companies are responding by finding clever ways of making it attractive for consumers to trade their data for services, or worse, by making it impossible to get service at all without giving up certain information.

With these issues in mind, we had some interesting discussions at each of our Velocity sessions. Here are six main privacy-related themes that emerged at these recent events:

 

  1. Privacy and security are not the same

Although are related, they refer to different ideas. Privacy is a major goal of security, and relates to a consumer’s right to safeguard their personal information. It can involve vulnerable data such as social media data, customer response data, demographic data or other personal information. In general, privacy is the individual’s right to keep his or her data to himself or herself. By contrast, security refers to the protection of enterprise or government systems. Security may incorporate customer privacy as part of its agenda, but the two are not synonymous. According to Tim Sheedy, principal analyst with Forrester Research, a panelist at our Sydney event, security and privacy are different things. “But if you have a security breach, privacy becomes an issue,” he said. “So they’re closely linked.”

 

  1. Balance is key

Our panelists agreed that you have to balance protecting customer data with moving the business forward. But if you’re in the insurance business, like San Francisco Velocity panelist and CTO at RMS Cory Isaacson, that’s a tough balance to strike. He has to worry about how his company’s data privacy measures up against industry standards and the regulations they have meet. At the same time he has to ensure that data security doesn’t paralyze innovation in the business. He says you have to be innovative with moving the business forward, as well as keeping up with standards and regulations.

“If you listen to your ISO auditor you’re never going to get out of bed in the morning,” Isaacson said. “If you’re faced with securing highly sensitive customer data, you have to incorporate it into the way you work every day instead of letting it slow you down.”

 

  1. Don’t be creepy

Companies have to walk a fine line between using personal data to enhance the customer experience and being intrusive for no good reason. Forrester’s Sheedy warns that perception is everything. “Don’t be creepy,” he said. “When you’re creepy that that’s when privacy becomes an issue. Don’t underestimate the ability of people to give away information for a discount or for a benefit. But the key is that there has to be a benefit. When there isn’t an upside, it becomes creepy for customers, and you have to wonder if you’ve  gone too far.”

 

  1. People will share data in exchange for perks.

Our Sydney panelists and attendees exchanged ideas about new ways that companies are obtaining information about customers, behaviours and preferences. They talked about social media companies learning your behaviours by using more than just the data you share on your profile, including using tactics like tracking your location through GPS and triangulation between cell phone towers.

 

“When I visit Pythian’s head office in Ottawa, the nearby coffee shop has an app that asks if I want them to prepare my usual drink when it detects I’m nearby. Is that intrusive? No, because I opted in,” said Francisco Alvarez, vice president, APAC at Pythian.

 

A Marketing Magazine article reported that in a recent survey, a majority of consumers will share data for certain benefits: 80 percent said they would share data for rewards from a company, 79 percent would share data for cash back, and 77 percent would share data for coupons. The majority also said they would share personal data for location-based discounts (69 percent).

 

  1. Millennials have a higher tolerance for sharing data.

The Australian panelists talked about this extensively, citing personal experience. They expressed the same concern many of us feel  about how much data their children’s generation is a sharing online and with companies. One of our attendees had a child who was denied a prestigious scholarship because his digital footprint wasn’t favourable. But, Alvarez said, this type of consequence doesn’t seem to deter millennials from freely sharing very personal information. And when it comes to sharing information with businesses in exchange for perceived benefits, their tolerance is very high.  

 

In fact, according to a survey conducted by the USC Annenberg Center for Digital Future and Bovitz Inc., millennials (individuals between the ages of 18 and 35) have a different attitude than Internet users 35 years and older when it comes to sharing their personal data online with businesses.  According to the study results, millennials were more likely than older respondents (35 years and older) to trade some of their personal information in exchange for more relevant advertising.

 

  1. New  approaches for protecting customer data are on the horizon

In our San Francisco discussion, Pythian Chief Data Officer Aaron Lee cited Google’s new initiative for protecting customer data.  The new approach involves placing corporate applications on the Internet itself and focusing its security efforts on registered devices and user authentication.
“It’s really simple idea,” Lee said.  “In most companies the internal network is special. If you’re on the internal network you get stuff you can’t get from outside just because you’re sitting there on the network. That’s not the case anymore. With Google’s Beyond Corp initiative it is like there are no more internal networks. If you want to talk to my service, you treat it like you came in from the internet just like everybody else. That way I can apply a consistent approach to authentication, authorization, all that good stuff. It’s actually acting to reduce the surface of complexity. It’s an approach that takes the simplest way that we can secure these things, and treats them the same regardless of where they actually live,” he said.

Categories: DBA Blogs

DEFAULT_CACHE_SIZE mentioned in alert.log of an #Oracle database

The Oracle Instructor - Wed, 2016-04-06 03:53

Today, I got this message in my alert.log file:

Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 709 MBs bigger than current size.

When I look at the datafile sizes and compare them with the buffer cache size, it shows:

 

SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                                       MB
-------------------------------------------------- ----------
Fixed SGA Size                                     2,80265045
Redo Buffers                                       13,1953125
Buffer Cache Size                                        3296
In-Memory Area Size                                      2048
Shared Pool Size                                          736
Large Pool Size                                            32
Java Pool Size                                             16
Streams Pool Size                                           0
Shared IO Pool Size                                       208
Data Transfer Cache Size                                    0
Granule Size                                               16
Maximum SGA Size                                         6144
Startup overhead in Shared Pool                    181,258133
Free SGA Memory Available                                   0

14 rows selected.

SYS@cloudcdb > select sum(bytes)/1024/1024 as mb from v$datafile;

        MB
----------
      3675

It is true, the database doesn’t fit completely into the buffer cache, missing roughly that amount of space mentioned. There is no such parameter as DEFAULT_CACHE_SIZE, though.
What we have instead is DB_CACHE_SIZE. In order to fix that issue, I was using this initialization parameter file to create a new spfile from:

[oracle@uhesse-service2 dbs]$ cat initCLOUDCDB.ora
*.audit_file_dest='/u02/app/oracle/admin/CLOUDCDB/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u02/app/oracle/oradata/CLOUDCDB/control01.ctl','/u03/app/oracle/fra/CLOUDCDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CLOUDCDB'
*.db_recovery_file_dest='/u03/app/oracle/fra'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLOUDCDBXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.sga_target=6g
*.pga_aggregate_target=2g
*.inmemory_size=1g
*.db_cache_size=4g

That reduced the size of the In-Memory Column Store to make room for the buffer cache. Now the database fits nicely into the buffer cache again:

SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo;

NAME                                                       MB
-------------------------------------------------- ----------
Fixed SGA Size                                     2,80265045
Redo Buffers                                       13,1953125
Buffer Cache Size                                        4256
In-Memory Area Size                                      1024
Shared Pool Size                                          800
Large Pool Size                                            32
Java Pool Size                                             16
Streams Pool Size                                           0
Shared IO Pool Size                                         0
Data Transfer Cache Size                                    0
Granule Size                                               16
Maximum SGA Size                                         6144
Startup overhead in Shared Pool                    181,290176
Free SGA Memory Available                                   0

14 rows selected.

Accordingly the message in the alert.log now reads
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED

Don’t get me wrong: I’m not arguing here against the In-Memory Option or in favor of Full Database Caching. Or whether it makes sense to use any of them or both. This post is just about clarifying the strange message in the alert.log that may confuse people.

And by the way, my demo database is running in the Oracle Cloud:-)


Tagged: 12c New Features
Categories: DBA Blogs

Oracle Linux on Amazon AWS

Pat Shuff - Wed, 2016-04-06 01:07
In this entry we are going to create a Linux 6.4 virtual machine on Amazon AWS EC2. In our last entry we did this on the Microsoft Azure using a single processor instance and 1.75 GB of RAM. The installation took a few steps and was relatively easy to install. We will not look at how to create an Amazon account but assume that you already have an account. The basic AWS console looks like the image below

When we click on the EC2 console instance it allows us to look at our existing instances as well as create new ones.

Clicking on the "Launch Instance" button allows us to start the virtual machine instance creation. We are given a choice of sources for the virtual machine. The default screen does not offer Oracle Linux as an option so we have to go to the commercial or community screens to get OEL 6.x as an option.

It is important to note that the commercial version has a surcharge on an hourly basis. If we search on Oracle Linux we get a list of different operating system versions and database and WebLogic installations. The Orbitera version in the commercial version adds a hefty surcharge of $0.06 per hour for our instance and gets more expensive on an hourly basis as the compute shapes get larger. This brings the cost to 7x times that of the Oracle Compute Service and 5x the times of the Microsoft Azure instance.

The community version allows us to use the same operating system configuration without the surcharge. The drawback to this option is trustability on the configuration as well as repeatability. The key advantage over the commercial version is that it has version control and will be there a year from now. The community version might or might not be there in a year and if you need to create a new virtual machine based on something that you did a year ago might or might not be there. On the flip side, you can find significantly older versions of the operating system in the community version that you can not in the commercial version.

Given that I am cheap (and funding this out of my own pocket) we will go through the community version to reduce the hourly cost. The main problem with this option is that we installed Oracle Linux 6.4 when installing on Oracle Compute Cloud Service and Microsoft Azure. On Amazon AWS we have to select Oracle Linux 6.5 since the 6.4 version is not available. We could select 6.6 and 6.3 but I wanted to get as close to 6.4 as possible. Once we select the OS version, we then have to select a processor shape.

Note that the smaller memory options are not available for our selection. We have to scroll down to the m3.medium shape with 1 virtual processor and 3.75 GB of RAM as the smallest configuration.

The configuration screen allows us to launch the virtual machine into a virtual network configuration as well as different availability zones. We are going to accept the defaults on this screen.

The disk selection page allows us to configure the root disk size as well as alternate disks to attach to the services. By default the disk selection for our operating system is 40 GB and traditional spinning disk. You can select a higher speed SSD configuration but there are additional hourly charges for this option.

The tags screen is used to help you identify this virtual machine with projects, programs, or geographical affiliations. We are not going to do anything on this screen and skip to the port configuration screen.

The port screen allows us to open up security ports to communicate with the operating system. Note that this is an open interface that allows us to open any ports that we desire and provide access to ports like 80 and 443 to provide access to web services. We can create white lists or protected networks when we create access points or leave everything open to the internet.

We are going to leave port 22 as the only port open. If we did open other ports we would need to change the iptables configuration on the os instance. We can review the configuration and launch the instance on the next screen.

When we create the instance we have to select a public and private key to access the virtual machine. You had to previously create this instance through the AWS console.

Once we select the key we get a status update of the virtual machine creation.

If we go to the EC2 instance we can follow the status of our virtual machine. In this screen shot we see that the instance is initializing.

We can now connect using putty or ssh to attach to the virtual machine. It is important to note that Amazon uses a different version of the private key. They use the pem extension which is just a different version of the ppk extension. There are tools to convert the two back and forth but we do need to select a different format when loading the private key using putty on Windows. By default the key extension that it looks for is ppk. We need to select all files to find the pem keys. If you follow the guidelines from Amazon you can convert the pem key to a ppk key and access the instance as was done previously.

It is important to note that you can not login as oracle but have to login as root. To enable logging in as oracle you will need to copy the public key into the .ssh directory in the /home/oracle directory. This is a little troubling having the default login as root and having to enable and edit files to disable this. A security model that allows you to login as oracle or opc and sudo to root is much preferable.

In summary, the virtual machine creation is very similar to the Oracle Compute Cloud Service and Microsoft Azure Cloud Service. The Amazon instance was a little more difficult to find. Oracle installations are not the sweet spot in AWS and other Linux instances are preferred. The ssh keys are a little unusual in that the EC2 instance wants a different format of the ssh keys and if Amazon generates them for you it requires a conversion utility to get it into standard format. The cost of the commercial implementation drives the price almost to cost prohibitive. The processor and memory configuration are similar to the other two cloud providers but I was able to try a 1 processor and 1 GB instance and it failed due to insufficient resources. We had to fall back to a much larger memory footprint for the operating system to boot.

All three cloud vendors need to work on operating system selection. When you search for Oracle Linux you not only get various versions of the operating system but database and weblogic server configurations as well. The management consoles are vastly different between the three cloud vendors as well. It is obvious what the background and focus is of the three companies. Up next, using bitnami to automate service installations on top of a base operating system.

All Good Things Must Come To An End

Floyd Teter - Tue, 2016-04-05 18:13
All good things must come to an end
           - Geoffrey Chaucer,  "Troilus and Criseyde", circa 1385

Time has proven repeatedly that Chaucer was right.  And, for this blog, the time has come to wrap it up and call it done.

When I decided to rejoin Oracle, I was well-aware of the risk that I might have to dial back or close down my professional presence on social media.  Oracle makes great effort to shape their messages, especially product-related messages, through all available channels...including social media.  And, for the most part, Oracle products are exactly what I've explored with my own social media accounts.

Over the eight months since I've rejoined Oracle, there have been times when the things I write here and on Twitter have "crossed wires" with Oracle's own messaging.  And it's now reached a point where it could distract from what we aiming to achieve as a team.  And we're talking about information related to Oracle's products and services.  Simply stated, as a member of the Oracle team, I adhere to the concept that Oracle has a right to control the messaging about Oracle's products and Oracle's services.

One more thing I'd like to clarify here in this last post:  there is nothing draconian working in the background here.  Nobody at Oracle has threatened my standing within the company if I fail to dial things back.  No slap on the wrist or anything like that.  Quite the opposite:  I'm a member of a team attempting to accomplish significant things, and my commentary on Oracle-related products and services is heading towards becoming a distraction...possibly even a detriment...to that effort.  So don't go there.  This is my decision and mine alone.

Now don't get the idea that I'm done blogging.  I still have some serious passion for user experience, business metrics, SaaS and smart approaches to software design & development.  So it's highly likely that you'll see me start something up on one or more of those subject.  It just won't have an Oracle-related context.  So I'm not done.  I'm just done with this.

So this is it for ORCLville.  This is also it for Oracle-related commentary on my Twitter and LinkedIn accounts.  It's been both an educational and a fun ride for me.  Hope y'all got something out of it too.  Keep an eye out on my Twitter account (fetter) - you'll see new things from me in the near future.

UPDATE:  Be sure to check out my new, technology-agnostic blog The Enterprise Software Puzzle!

Redshift Table Maintenance: Vacuuming

Pythian Group - Tue, 2016-04-05 12:38
Overview

Part of the appeal of AWS’ Redshift is that it’s a managed service, which means lower administration costs. While you don’t have to hire a full time DBA to make sure it runs smoothly (from Pythian’s experience it takes ~10-20 hours/month to manage Redshift), there are still some tasks that should be attended to keep it happy:

  • Vacuuming
  • Analyzing
  • Skew analysis
  • Compression analysis
  • Query monitoring

Let us start with Vacuuming as the first topic of a series of deeper dives into this list.

Vacuuming is an integral part of performance maintenance of Redshift.  Since deletes and updates both flag the old data, but don’t actually remove it, if we’re doing those kinds of actions, vacuuming is needed to reclaim that space. Updates and deletes can be pretty big performance hits (a simple update can easily take 60 secs on a 50 million record table on a small cluster, so we’re looking at 20 minutes for a similar update on a 1 billion record table), so we try to avoid them as much as we can on large tables. The space reclamation portion of the vacuum typically accounts for 10% of the time we see spent on the tables.  We can use the SORT ONLY parameter to skip this phase, but we generally have no compelling reason to.

In addition, if tables have sort keys, and table loads have not been optimized to sort as they insert, then the vacuums are needed to resort the data which can be crucial for performance.  While loads of empty tables automatically sort the data, subsequent loads are not. We have seen query times drop by 80% from the implementation of vacuuming, but of course the impact varies with table usage patterns.

The biggest problem we face with vacuuming is the time it takes. While vacuuming does not block reads or writes, it can slow them considerably as well as take significant resources from the cluster, and you can only vacuum one table at a time. Remember that resource utilization can be constrained through WLM queues. A typical pattern we see among clients is that a nightly ETL load will occur, then we will run vacuum and analyze processes, and finally open the cluster for daily reporting. The faster the vacuum process can finish, the sooner the reports can start flowing, so we generally allocate as many resources as we can.

Operations

Let us start with the process itself.  It’s simple enough and you can get syntax documentation from AWS . There’s not too much that’s tricky with the syntax and for most use cases

VACUUM myschema.mytablename;

will suffice.  Note that INTERLEAVED sort keys need the REINDEX parameter added for all re-indexing to occur.  You can discern which tables have this set up by using the query:

select schemaname, tablename
from (SELECT
n.nspname AS schemaname
,c.relname AS tablename
,min(attsortkeyord) min_sort FROM pg_namespace AS n
INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
WHERE c.relkind = 'r'
AND abs(a.attsortkeyord) > 0
AND a.attnum > 0
group by 1,2 )
where min_sort<0

In order to give the vacuum process more resources, we preface this command with

SET wlm_query_slot_count TO <N>;

where N is the maximum number of query slots we think we can get away with.  If you’re not sure what that number should be (we’ll discuss WLM queues in another post), usually 5 is a safe number though be warned that if the value of wlm_query_slot_count is larger than the number of available slots for the service class, the vacuum command will fail.

Knowing when to vacuum is reasonably straight forward. Anytime after substantial inserts, updates, or deletes are made is always appropriate, but you can be more exacting by querying two tables:

select * from STL_ALERT_EVENT_LOG where Solution LIKE ‘%VACUUM command%’

and

select * from SVV_TABLE_INFO where unsorted > 8

The latter check works great for daily loads. We will often set the threshold at 8 (percent) immediately after the loads, then run another vacuum process in the evening with a lower threshold (4 percent) that addresses larger tables that take a fair amount of time to vacuum since we want to avoid that situation in the morning.

Last fall AWS built a nice tool to automate vacuums, Analyze & Vacuum Schema Utility, that incorporated these queries. It works quite well, and we recommend it to our clients as a simple way to set up this maintenance. However, note that it does not automatically add the REINDEX parameter for those tables with INTERLEAVED sortkeys. The code is all available, so it is easy enough to adjust to make more custom filtering of tables (on fact_* and dim_* for instance) within a schema.

AWS has built a very useful view, v_get_vacuum_details, (and a number of others that you should explore if you haven’t already) in their Redshift Utilities repository that you can use to gain some insight into how long the process took and what it did. None of the system tables for vacuuming keep any historical information which would be nice for tracking growing process times, but you can see them for a week in STL_QUERY which gets purged to a history of 7 days. I recommend creating a simple process to track the vacuum data:

First create the table:

create table vacuum_history sortkey (xid) as select * from v_get_vacuum_details where processing_seconds > 0;

Then set up a cron process to populate:

0 18 * * * psql -h myRScluster -U myUser -p5439 -c “INSERT INTO vacuum_history SELECT * FROM v_get_vacuum_details WHERE xid > (SELECT MAX(xid) FROM vacuum_history) where processing_seconds > 0;” &> /var/log/vacuum_history.log

Once you start to see tables taking an inordinate amount of time to vacuum, some additional intervention may be appropriate. Our team recently ran into a sizable table (3 billion records) that had been taking 3 hours to vacuum daily. Some issue occurred where the table needed a partial reload of 2 billion rows. Once that finished, we ran a vacuum which kept going all afternoon. Checking SVV_VACUUM_PROGRESS we could see that it would take almost 30 hours to complete. Note that restarting a stopped vacuum does not mean the process will pick up where it left off. Since this would have impacted the daily load performance, we killed the vacuum with “cancel <pid>” using the pid pulled from

select pid, text from SVV_QUERY_INFLIGHT where text like ‘%Vacuum%’

We then ran a deep copy (created a new version of the table and ran a SELECT INTO) which took about 5 hours. The load into an empty table triggers the correct sorting, so a subsequent vacuum took only a few minutes to complete.

Just a note on killing long running vacuums: it sometimes doesn’t work especially once it’s in the initialize merge phase. We’ve found that continually issuing the cancel command while it’s in the sort phase is effective, but the point it to be wary of vacuuming large tables for their first time. Vacuums on large, unsorted tables write temporary data to disk, so there is also the potential to run out of disk and freeze the cluster, so be sure to always check that up to 3x the table size of disk space is available.

There are a few simple strategies to prevent long running vacuums:

  • Load your data in SORTKEY order: The incoming data doesn’t have to be pre-ordered, just greater than existing data.
  • Vacuum often: A table with a small unsorted region vacuums faster than one with a large unsorted region.
  • If tables become too large to vacuum within a maintenance window, consider breaking them apart: We often see multi-billion record tables where the only data being queried is from the last month or two.
  • Deep copies can be a faster solution than vacuums.
Categories: DBA Blogs

Considering Total Cost of Incident in the Age of “What if?”

Pythian Group - Tue, 2016-04-05 11:03

 

I’m currently in the process of moving houses and planning a wedding – busy, exciting, happy times. There are lots of positives – more yard space, more square footage, time with friends and family, cake…the list goes on. However, mixed in with these big, happy life changes is an underlying anxiety that something could go wrong. There’s always a chance that accidents, inclement weather or illness could ruin the party for everyone. It’s not pleasant to consider and I’d honestly rather sweep it all under the rug and go on with the merry-making (mmm cake!). But the voice at the back of my head won’t stop whispering what if?

 

It’s this voice that’s prompted me to take steps towards protecting my investments with liability insurance. Now, I’m not keen on laying out funds where I don’t have to, and I’m always the first to try and save a buck (coupons are my best friends!), but where any large investment is being made, it only makes sense for me to part with some of my hard coupon-saved dollars to protect that. I may never need it, true… but what if?

 

At Pythian, we spend a lot of our time exploring the world of what if? What if there’s an outage? What if there’s a breach? What will we lose? What will it cost? These are uncomfortable questions for a lot of organizations and especially uncomfortable when things are going well. If you haven’t had an outage or a data breach yet, it is easier to assume that this won’t happen. What if we invest in protection and that spend is wasted because nothing ever goes wrong? Naïve? Maybe. Comfortable? Oh yeah.

 

In a post-Snowden, post-Target, post-Home Depot world, it’s more important than ever for organizations to move out of the comfortable and really consider what the total impact of any one incident could be on their environment and take steps to insure themselves against it. Let’s spend some time being uncomfortable, considering more than just the Total Cost of Operations and think instead about the Total Cost of Incident.

Categories: DBA Blogs

FBDA -- 4 : Partitions and Indexes

Hemant K Chitale - Tue, 2016-04-05 09:47
Continuing our investigation of the FBDA architecture.

oracle@ora12102 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 5 23:25:10 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Tue Apr 05 2016 23:23:47 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select table_name, def_tablespace_name, partitioning_type, partition_count, status
2 from user_part_tables
3 order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
DEF_TABLESPACE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
SYS_FBA_HIST_93250
FBDA RANGE 1 VALID


SQL>
SQL> set pages600
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_93250') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_93250')
--------------------------------------------------------------------------------

CREATE TABLE "HEMANT"."SYS_FBA_HIST_93250"
( "RID" VARCHAR2(4000),
"STARTSCN" NUMBER,
"ENDSCN" NUMBER,
"XID" RAW(8),
"OPERATION" VARCHAR2(1),
"ID_COLUMN" NUMBER,
"DATA_COLUMN" VARCHAR2(15),
"DATE_INSERTED" DATE,
"D_1729869_NEW_COL_1" VARCHAR2(5)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA"
PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA" )


SQL>


So, although my active table (TEST_FBDA) is not partitioned, the History table is Range Partitioned on the ENDSCN column, with a single partition !   All the rows in the History table are in the MAXVALUE partition.  I wonder if and when it will ever be split ?

What if I create a partitioned table and then define Flashback Archiving on it ?

SQL> create table test_fbda_partitioned (
2 id_column number,
3 data_column varchar2(15),
4 date_inserted date)
5 partition by range (id_column)
6 (partition p_100 values less than (101),
7 partition p_200 values less than (201),
8 partition p_300 values less than (301),
9 partition p_400 values less than (401),
10 partition p_max values less than (MAXVALUE))
11 /

Table created.

SQL> alter table test_fbda_partitioned flashback archive fbda;

Table altered.

SQL> insert into test_fbda_partitioned
2 select rownum, to_char(rownum), trunc(sysdate)
3 from dual connect by level < 701;

700 rows created.

SQL> commit;

Commit complete.

SQL> update test_fbda_partitioned
2 set data_column=data_column;

700 rows updated.

SQL> commit;

Commit complete.

SQL> col subobject_name format a15
SQL> select object_type, subobject_name, object_id
2 from user_objects
3 where object_name = 'TEST_FBDA_PARTITIONED'
4 order by 3,1;

OBJECT_TYPE SUBOBJECT_NAME OBJECT_ID
----------------------- --------------- ----------
TABLE 93342
TABLE PARTITION P_100 93343
TABLE PARTITION P_200 93344
TABLE PARTITION P_300 93345
TABLE PARTITION P_400 93346
TABLE PARTITION P_MAX 93347

6 rows selected.

SQL>
SQL> select table_name
2 from user_tables
3 where table_name like '%93342%'
4 order by 1;

TABLE_NAME
--------------------------------------------------------------------------------
SYS_FBA_DDL_COLMAP_93342
SYS_FBA_HIST_93342
SYS_FBA_TCRV_93342

SQL>
SQL> select dbms_metadata.get_ddl('TABLE','SYS_FBA_HIST_93342') from dual;

DBMS_METADATA.GET_DDL('TABLE','SYS_FBA_HIST_93342')
--------------------------------------------------------------------------------

CREATE TABLE "HEMANT"."SYS_FBA_HIST_93342"
( "RID" VARCHAR2(4000),
"STARTSCN" NUMBER,
"ENDSCN" NUMBER,
"XID" RAW(8),
"OPERATION" VARCHAR2(1),
"ID_COLUMN" NUMBER,
"DATA_COLUMN" VARCHAR2(15),
"DATE_INSERTED" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA"
PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA" )


SQL>


So, even though my active table (TEST_FBDA_PARTITIONED) is created as a Range Partitioned Table partitioned on ID_COLUMN, the corresponding History table is Range Partitioned on ENDSCN with a single MAXVALUE partition.
Therefore, Oracle ignores my partitioning definition when creating the History table.  The History table (seems to be ?) always Range Partitioned on ENDSCN and starts using the MAXVALUE partition up-front.  (When will this Partition be split ?  I could search MoS for Docs / Bugs, but I'll do that exercise later).

Now that we know that the History table doesn't use our Partition Key, we must wonder about Partition Pruning when running AS OF queries on the active table that need to access the History Table.  If we can't Partition Prune, can we Index the History table ?

SQL> select table_name                   
2 from user_tables
3 where table_name like 'SYS_FBA_HIST%'
4 /

TABLE_NAME
--------------------------------------------------------------------------------
SYS_FBA_HIST_93250
SYS_FBA_HIST_93342

SQL> create index sys_fba_hist_93250_ndx_1 on sys_fba_hist_93250(id_column) tablespace fbda;

Index created.

SQL> create index sys_fba_hist_93342_ndx_1 on sys_fba_hist_93342(id_column) tablespace fbda;

Index created.

SQL> select dbms_metadata.get_ddl('INDEX','SYS_FBA_HIST_93250_NDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','SYS_FBA_HIST_93250_NDX_1')
--------------------------------------------------------------------------------

CREATE INDEX "HEMANT"."SYS_FBA_HIST_93250_NDX_1" ON "HEMANT"."SYS_FBA_HIST_932
50" ("ID_COLUMN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA"


SQL> select dbms_metadata.get_ddl('INDEX','SYS_FBA_HIST_93342_NDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','SYS_FBA_HIST_93342_NDX_1')
--------------------------------------------------------------------------------

CREATE INDEX "HEMANT"."SYS_FBA_HIST_93342_NDX_1" ON "HEMANT"."SYS_FBA_HIST_933
42" ("ID_COLUMN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "FBDA"


SQL>


Yes !  Oracle does allow us to build custom indexes on the History tables.  (I believe I saw this somewhere in the documentation or a note).

Next Post (possibly) : More DML operations and whether auto-purging based on the defined RETENTION 3 DAY period.  (Note : The Retention Period is defined at the Flashback Archive level, not at the individual table (active/History) level.  If you need to have tables with different Retention Periods, you need to define different Flashback Archives, although they can all be in the same Tablespace).
.
.
.

Categories: DBA Blogs

Mind your rdbms/audit on 12c

Pythian Group - Tue, 2016-04-05 09:17

Recently we’ve ran into an interesting question from one of our clients. They were seeing messages of the following form in syslog:


"Aug 11 11:56:02 ***** kernel: EXT3-fs warning (device ******): ext3_dx_add_entry: Directory index full!"

I haven’t encountered this before, and did a bit of research. My initial suspicion ended up being correct, and it was due to too many files being created, somewhere in that file system. I had a look around, and eventually checked out the ORACLE_HOME of the ASM / Grid Infrastructure software, which is running version 12.1.0.2 on that host.

I snooped around using du -sh to check which directories or sub-directories might be the culprit, and the disk usage utility came to a halt after the “racg” directory. Next in line would be “rdbms”. The bulb lit up somewhat brighter now. Entering the rdbms/audit directory, I issued the common command you would if you wanted to look at a directories contents: “ls”.

Five minutes later, there was still no output on my screen. Okay, we found the troublemaker. So we’re now being faced with a directory that has potentially millions of files in it. Certainly we all are aware that “rm” isn’t really able to cope with a situation like this. It would probably run for a couple minutes until it’s done parsing the directory index, and then yell “argument list too long” at us. Alternatively, we could use find, combined with -exec (bad idea), -delete, or even pipe into rm using xargs. Looking around a bit on the good ol’ friend google, I came across this very interesting blog post by Sarath Pillai.

I took his PERL one-liner, adjusted it a wee bit since I was curious how many files we actually got in there and ran it on a sandbox system with a directory with 88’000 files in it:


perl -e 'my $i=0;for(<*>){$i++;((stat)[9]<(unlink))} print "Files deleted: $i\n"'

It completed in 4.5 seconds. That’s pretty good. In Sarath’s tests he was able to delete half a million files in roughly a minute. Fair enough.

After getting the OK from the client, we ran it on the big beast. It took 10 minutes.


Files deleted: 9129797

9.1 million files. Now here comes the interesting bit. This system has been actively using 12.1.0.2 ASM since May 6th, 2015. That’s only 3 months. That translates to 3 million files per month. Is this really a desirable feature? Do we need to start running Hadoop just to be able to mine the data in there?

Looking at some of the files, it seems ASM is not only logging user interactions there, but also anything and everything done by any process that connects to ASM.

As I was writing this, I happened to take another peek at the directory.


[oracle@cc1v3 audit]$ ls -1 | wc -l
9134657

Remember those numbers from before? Three million a month? Double that.

I suspect this was due to the index being full, and Linux has now re-populated the index with the next batch. Until it ran full again.

A new syslog entry just created at the same time seems to confirm that theory:

Aug 12 00:09:11 ***** kernel: EXT3-fs warning (device ******): ext3_dx_add_entry: Directory index full!

After running the PERL one-liner again, we deleted another vast amount of files:


Files deleted: 9135386

It seems that the root cause is the added time stamp to the file names of the audit files that Oracle writes in 12.1. The file names are much more unique, which gives Oracle the opportunity to generate so many more of them. Where in previous versions, with an adequately sized file system you’d probably be okay for a year or more; on 12.1.0.2, on an active database (and our big beast is very active) you have to schedule a job to remove them, and ensure it runs frequently (think 18+ million files in 3 months to put “frequently” into perspective).

Categories: DBA Blogs

LittleArduinoProjects#200 Mini Solenoid Engine

Paul Gallagher - Tue, 2016-04-05 08:02
Solenoid engines? They definitely fall into the the category of because you can.
Some people take it to a whole other level (like this V8 model).

What I have here is much more modest - a single-cylinder engine powering an impromptu-wire-art drive train. The mini-solenoid used here has a very small effective stroke - about 8mm - which necessarily constrains the gearing. I was inspired by 30GB's similar model for the layout.

As always, all notes, schematics and code are in the Little Arduino Projects repo on GitHub.