Skip navigation.

DBA Blogs

The Power of the Oracle Database Proxy Authenticated Connections

Pythian Group - Mon, 2014-10-27 08:46

We recently received this inquiry from a client:

“Can an Oracle database account support two passwords at once so we can roll out updated credentials to application servers gradually rather than having to change them all at the same time? Then once all of the application servers have been configured to use the new/second password we can change or remove the first one?

The short answer is no. Like most computer technologies, an Oracle database user has only one password that is valid at any given time. However, a very powerful and under-appreciated feature of the Oracle database could be used in this case: It is called proxy authentication.

 

How proxy authenication works

Introduced with Oracle Database 10g, the basic premise of proxy authentication is that a user with the required permission can connect to the Oracle database using their own credentials, but proxy into another user in the database.  To put it more plainly: connect as USER_A but using the password of USER_B !

The proxy permission is granted through the “CONNECT THROUGH” privilege.  Interestingly, it is granted through an ALTER USER command as really it’s an “authorization” and property of the user and not truly a privilege like the traditional privileges we’re used to:

SQL> connect / as sysdba
Connected.
SQL> alter user USER_A grant connect through USER_B;

User altered.

SQL>

 

Now USER_B who may not know the password for USER_A can connect as USER_A by specifying the proxy account in square brackets in the connection string:

SQL> connect USER_B[USER_A]/passw0rd
Connected.
SQL> show user
USER is "USER_A"
SQL>

 

The password specified was the one for USER_B, not USER_A.  Hence the credentials for USER_B were used but the end result is that the session is connected as USER_A!

Specifically when a proxy authenticated connection is made the USERENV namespace parameters are updated as follows:

  • The “SESSION_USER” becomes USER_A
  • The “SESSION_SCHEMA” also becomes USER_A
  • The “PROXY_USER” remains USER_B who initiated the connection and who’s credentials were used

Since the syscontext(‘USERENV’,’PROXY_USER’) remains unchanged, the connection is properly audited and information on who made the initial connection can still recorded in audit records.  However for all other purposes, USER_B has effectively connected to the database as USER_A without having to know USER_A’s password.

So back to the original question, a possible approach to their problem would be to create a second USER_B that has permission to proxy into their application user account APP_USER.  Then they could gradually roll out the credential change to use the new USER_B and proxy into APP_USER to all of their app servers.  Once all app servers have been updated it would then be safe to change the password on the base application account APP_USER.

 

A similar feature is the ability to change the current session’s schema. For example as USER_B issuing:

alter session set current_schema = USER_A;

 

This is a very quick and simple approach, but isn’t quite the same. Doing this only changes the “CURRENT_SCHEMA” which is the currently active default schema. Hence any queries issued without specifying the schema name will default to “CURRENT_SCHEMA”. But there are many cases when actually connecting to another user is required. For example, if the DBA needs to drop and re-create a database link then the “current_schema” approach will not suffice.  But the proxy authenticated connection alternative will work perfectly.

Another case where the “current_schema” approach may be an issue is if the application is user aware.  What I mean by this is that possibly the application has some logic such as “if user = USER_A then do suff“.  If you connect as USER_B and simply changes the current schema then the boolean logic of this condition will evaluate to FALSE.  However if you use a proxy authenticated connection user USER_A, the condition will evaluate to TRUE.

Previously if the DBA needed to connect to the database as a specific user (maybe to re-create a DB link for example) they might employ the old trick of temporarily changing the user’s password, quickly connecting, and then quickly changing it back using the extracted/saved password hash.  However there are numerous serious problems with this approach:

  1. The schema may be locked
  2. The password may be controlled by a PROFILE that may also need to be adjusted.
  3. Account intrusion detection tools may detect the connection.
  4. The connection may not be properly audited via Oracle or external auditing tools.
  5. The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!

Hence that approach should never be used. The proxy authenticated connection alternative doesn’t have any of those issues and is perfectly safe.

 

A simple example

Putting it all together into a small example to show how the userenv properties are affected:

SQL> alter user USER_A grant connect through USER_B;

User altered.

SQL> connect USER_B[USER_A]/passw0rd
Connected.
SQL> alter session set current_schema = SCOTT;

Session altered.

SQL> select sys_context('USERENV','SESSION_USER') as session_user,
  2  sys_context('USERENV','SESSION_SCHEMA') as session_schema,
  3  sys_context('USERENV','CURRENT_SCHEMA') as current_schema,
  4  sys_context('USERENV','PROXY_USER') as proxy_id,
  5  user
  6  from dual;

SESSION_USER   SESSION_SCHEMA CURRENT_SCHEMA PROXY_ID       USER
-------------- -------------- -------------- -------------- ------------
USER_A         SCOTT          SCOTT          USER_B         USER_A

SQL>

 

As can be seen above, for all intensive purposes the connection has been made to USER_A but using the credentials of USER_B.  USER_A’s password did not need to be known nor was the USER_A account affected or adjusted in any way.

FAQs

What if USER_A’s password is locked or expired?

The answer is that the connection will still report the same error as it would have if a direct connection to USER_A was made:

SQL> connect / as sysdba
Connected.
SQL> alter user USER_A account lock;

User altered.

SQL> connect USER_B[USER_A]/passw0rd
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL>

Can this be used with other tools such as Data Pump?

(Importing as the actual user instead of a DBA user was necessary with Oracle 10g under specific circumstances such as importing JOBs and REFRESH GROUPS).  The answer is yes it works with Data Pump and other similar tools:

$ impdp dumpfile=temp.dmp nologfile=y include=JOB

Import: Release 11.2.0.4.0 - Production on Wed Oct 15 19:10:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: USER_B[USER_A]/passw0rd

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER_A"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER_A"."SYS_IMPORT_FULL_01":  USER_B[USER_A]/******** dumpfile=temp.dmp nologfile=y include=JOB
Processing object type SCHEMA_EXPORT/JOB
Job "USER_A"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 15 19:10:22 2014 elapsed 0 00:00:01

$

Notice that the Data Pump master table is created in the USER_A schema even though we connected using the USER_B credentials.

Is proxy authentication supported by JDBC/JDBC thin driver?

Yes, it works through almost any OCI connection including JDBC connections.

What about Oracle Wallets?

The answer again is yes, they can support it too! See below for an example using an Oracle Wallet:

$ mkstore -wrl "/u01/app/oracle/wallet" -createCredential ORCL USER_A passw0rd
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1
$ mkstore -wrl "/u01/app/oracle/wallet" -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: ORCL USER_A
$

$ sqlplus /@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:04 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "USER_A"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus [app_user]/@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:14 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "APP_USER"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

 

Reporting and Revoking

Finally, how can we report on what proxy authentication authorizations we’ve granted as we need to clean them up (revoke them)?  Or perhaps we just need to report on or audit what’s out there?  Fortunately, it’s as simple as querying a catalog view to see what’s been set and we can remove/revoke through another simple ALTER USER command:

SQL> select * from PROXY_USERS;

PROXY        CLIENT       AUT FLAGS
------------ ------------ --- -----------------------------------
USER_B       USER_A       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL> alter user USER_A revoke connect through USER_B;

User altered.

SQL> select * from PROXY_USERS;

no rows selected

SQL>

 

Conclusion

Since the introduction of the GRANT ANY OBJECT privilege with Oracle9i, the number of times that the DBA needs to actually connect as other users has been reduced.  However, there still are some distinct situations such as those mentioned in the examples above when the connection as another user may be absolutely necessary.

Thanks to the proxy authenticated connection capabilities introduced with Oracle Database 10g, connecting as another user when you don’t know the other account’s password has become a breeze.  And even if you do know the password, connecting through proxy authentication can still add value with the additional audit information.

Have any other situations where connecting as another user is absolutely necessary? Share them in the comments section below.

 

References

http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm

Categories: DBA Blogs

Log Buffer #394, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2014-10-27 08:22

This week’s log buffer edition collects some of the insightful blog posts from Oracle, SQL Server, and MySQL.

Oracle:

Oracle StorageTek T10000D Tape Drive achieves FIPS 140-2 Validation.

Oracle is a Leader in IDC MarketScape for Global Trade Management.

The Benefits of Integrating a Google Search Appliance with an Oracle WebCenter or Liferay Portal.

Maintenance Windows is too small? Autotask Jobs fail.

SOA Suite 12c: Querying LDAP directories using the LDAP Adapter.

SQL Server:

Regaining access to SQL server after changing the domain.

NuGet has transformed the ease of getting and installing the latest version of .NET packages, tools and frameworks.

The Mindset of the Enterprise DBA: Delegating Work.

Stairway to SQL PowerShell Level 8: SQL Server PowerShell Provider.

Finding a table name from a page ID.

MySQL:

A few weeks ago, Gharieb received an interesting Galera Cluster support case from one of customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

Resources for Database Clusters: 9 DevOps Tips, ClusterControl 1.2.8 Release, HAProxy Webinar Replay & More.

Refactoring replication topology with Pseudo GTID.

Improvements to STRICT MODE in MySQL.

Monitoring progress and temporal memory usage of Online DDL in InnoDB.

Categories: DBA Blogs

OCP 12C – Oracle Data Redaction

DBA Scripts and Articles - Mon, 2014-10-27 07:00

What is Oracle Data Redaction ? Oracle Data Redaction is meant to mask (redact) sensitive data returned from application queries. Oracle Data Redaction doesn’t make change to data on disk, the sensitive data is redacted on the fly before it is returned to the application. You can redact column data by using one of the following methods: Full … Continue reading OCP 12C – Oracle Data Redaction →

The post OCP 12C – Oracle Data Redaction appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Getting Started with Impala Interactive SQL for Apache Hadoop by John Russell; O'Reilly Media

Surachart Opun - Sat, 2014-10-25 11:52
Impala is open source and a query engine that runs on Apache Hadoop. With Impala, you can query data, whether stored in HDFS or Apache HBase – including SELECT, JOIN, and aggregate functions – in real time. If you are looking for a book getting start with it - Getting Started with Impala Interactive SQL for Apache Hadoop by John Russell (@max_webster). Assist readers to write, tune, and port SQL queries and other statements for a Big Data environment, using Impala. The SQL examples in this book start from a simple base for easy comprehension, then build toward best practices that demonstrate high performance and scalability. For readers, you can download QuickStart VMs and install. After that, you can use it with examples in a book.
In a book, it doesn't assist readers to install Impala or how to solve the issue from installation or configuration. It has 5 chapters and not much for the number of pages, but enough to guide how to use Impala (Interactive SQL) and has good examples. With chapter 5 - Tutorials and Deep Dives, that it's highlight in a book and the example in a chapter that is very useful.
Free Sampler.

This book assists readers.
  • Learn how Impala integrates with a wide range of Hadoop components
  • Attain high performance and scalability for huge data sets on production clusters
  • Explore common developer tasks, such as porting code to Impala and optimizing performance
  • Use tutorials for working with billion-row tables, date- and time-based values, and other techniques
  • Learn how to transition from rigid schemas to a flexible model that evolves as needs change
  • Take a deep dive into joins and the roles of statistics
[test01:21000] > select "Surachart Opun" Name,  NOW() ;
Query: select "Surachart Opun" Name,  NOW()
+----------------+-------------------------------+
| name           | now()                         |
+----------------+-------------------------------+
| Surachart Opun | 2014-10-25 23:34:03.217635000 |
+----------------+-------------------------------+
Returned 1 row(s) in 0.14sBook: Getting Started with Impala Interactive SQL for Apache HadoopAuthor: John Russell (@max_webster)Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

Recap of yesterday’s Arizona Oracle User Group (AZORA) meeting

Bobby Durrett's DBA Blog - Fri, 2014-10-24 11:15

Yesterday was the first meeting of the newly restarted Arizona Oracle User Group, AZORA.  It was a good first meeting to kick off what I hope will turn into an active club.

We met in the Tempe Public Library in a large downstairs room with a projector screen and plenty of seating with tables so it was easy to take notes.  We also had snacks.  I got a Paradise Bakery chocolate chip cookie and bottled water so that put me in a good mood for the afternoon meeting. They also had some giveaways and so I picked up an Oracle pen and notebook to use to take notes during the talk.  They also gave away three or four Rich Niemiec books and some gift cards as prizes, but, as usual, I didn’t win anything.

The first talk was about “Big Data”.  I’m skeptical about any buzzword, including big data, but I enjoyed hearing from a technical person who was actually doing this type of work.  I would rather hear an honest perspective from someone in the midst of the battle than get a rosy picture from someone who is trying to sell me something.  Interestingly, the talk was about open source big data software and not about any Oracle product, so it gave me as an Oracle database specialist some insight into something completely outside my experience.

The second talk was about another buzzword – “The Cloud”.  The second talk was as helpful as the first because the speaker had exposure to people from a variety of companies that were actually doing cloud work.  This talk was more directly related to my Oracle database work because you can have Oracle databases and applications based on Oracle databases deployed in the cloud.

It was a good first meeting and I hope to attend and help support future meetings.  Hopefully we can spread the news about the club and get even more people involved and attending so it will be even more useful to all of us.  I appreciate those who put in the effort to kick off this first meeting.

– Bobby





Categories: DBA Blogs

OCP 12C – Resource Manager and Performance Enhancements

DBA Scripts and Articles - Fri, 2014-10-24 07:16

Use Resource Manager for a CDB and a PDB Managing Resources between PDBs The Resource Manager uses Shares ans Utilization limit to manage resources allocated to PDBs. The more “Shares” you allocate to a PDB, the more resource it will have. Shares are allocated through DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE. One directive can only concern one PDB and you can’t … Continue reading OCP 12C – Resource Manager and Performance Enhancements →

The post OCP 12C – Resource Manager and Performance Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – SQL Enhancements

DBA Scripts and Articles - Thu, 2014-10-23 08:20

Extended Character Data Type Columns In this release Oracle changed the maximum sixe of three data types  In Oracle 12c if you set a VARCHAR2 to 4000 bytes or less it is stored inline, if you set it to more than 4000 bytes then it is transformed in extended character data type and stored out … Continue reading OCP 12C – SQL Enhancements →

The post OCP 12C – SQL Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Deploying a Private Cloud at Home — Part 6

Pythian Group - Thu, 2014-10-23 07:35

Today’s blog post is part six of seven in a series dedicated to Deploying a Private Cloud at Home, where I will be demonstrating how to configure controller node with legacy networking ad OpenStack dashboard for webgui. Feel free to check out part five where we configured compute node with OpenStack services.

  1. First load the admin variables admin-openrc.sh
    source /root/admin-openrc.sh
  2. Enable legacy networking
    openstack-config --set /etc/nova/nova.conf DEFAULT network_api_class nova.network.api.API
    openstack-config --set /etc/nova/nova.conf DEFAULT security_group_api nova
  3. Restart the Compute services
    service openstack-nova-api restart
    service openstack-nova-scheduler restart
    service openstack-nova-conductor restart
  4. Create the IP pool which will be assigned to the instances we will launch later. My network is 192.168.1.0/24. I took a subpool of the range and I am using that subnet to assign IPs to the VMs. As the VMs will be on my shared network I want the ip in the same range my other systems on the network.
    Here I am using the subnet of 192.168.1.16/28
  5. Create a network
    nova network-create vmnet --bridge br0 --multi-host T --fixed-range-v4 192.168.1.16/28
  6. Verify networking by listing the network
    nova net-list
  7. Install dashboard. Dashboard gives you webui to manage OpenStack instances and services. As we will be using the default configuration I am not going in detail with this.
    yum install -y mod_wsgi openstack-dashboard
  8. Update the ALLOWED_HOSTS in local_settings to include the addresses you wish to access the dashboard from. I am running these in my Intranet so I allowed every host in my network. But you can specify which hosts you want to give access.
    ALLOWED_HOSTS = ['*']
  9. Start and enable Apache web server
    service httpd start
    chkconfig httpd on
  10. You can now access the dashboard at http://controller/dashboard

 

This completes the configuration of OpenStack private cloud. We can use the same guide for RackSpace private cloud as it too is based on OpenStack Icehouse, but that is for another time.

Now that we have a working PaaS cloud, we can configure any SaaS on top of it, but that will require another series altogether.

Stay tuned for part seven, our final post in the series Deploying Private Cloud at Home, where I will be sharing scripts that will automate the installation and configuration of controller and compute nodes.

Categories: DBA Blogs

Reminder: first Arizona Oracle User Group meeting tomorrow

Bobby Durrett's DBA Blog - Wed, 2014-10-22 14:02

Fellow Phoenicians (citizens of the Phoenix, Arizona area):

This is a reminder that tomorrow is the first meeting of the newly reborn (risen from the ashes) Arizona Oracle User Group.  Here are the meeting details: url

I hope to meet some of my fellow Phoenix area DBAs tomorrow afternoon.

– Bobby

 


Categories: DBA Blogs

OCP 12C – DataPump, SQL*Loader, External Tables Enhancements

DBA Scripts and Articles - Wed, 2014-10-22 13:57

Oracle DataPump Enhancements Full Transportable Export and Import of Data In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database. To create a fully transportable export of your database you need to specify … Continue reading OCP 12C – DataPump, SQL*Loader, External Tables Enhancements →

The post OCP 12C – DataPump, SQL*Loader, External Tables Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

MySQL: Troubleshooting an Instance for Beginners

Pythian Group - Wed, 2014-10-22 09:17
IMG_1299

So as you may know, my new position involves the MySQL world, so I’m in the task of picking up the language and whereabouts of this DBMS, and my teamate Alkin Tezuysal (@ask_dba on Twitter) has a very cool break and fix lab which you should check out if you are going to Percona Live London 2014, he will be running this lab, so be sure to don’t miss out.

So the first thing I tried was to bring up the service, but to my surprise, the MySQL user didn’t exist. So the first thing I did was create the user.

Note: Whenever you see “…”, it is to shorten the output.

[user-lab@ip-10-10-10-1 ~]$ service mysqld start
touch: cannot touch ‘/var/log/mysqld.log’: Permission denied
chown: invalid user: ‘mysql:mysql’
chmod: changing permissions of ‘/var/log/mysqld.log’: Operation not permitted
mkdir: cannot create directory ‘/var/lib/msql’: Permission denied
[user-lab@ip-10-10-10-1 ~]$ id mysql
id: mysql: no such user
[user-lab@ip-10-10-10-1 ~]$ sudo useradd mysql

So now that the user exists, I try to bring it up and we are back at square one as the initial configuration variable in the .cnf file is incorrect. But there is another problem, as there is more than one .cnf file.

[user-lab@ip-10-10-10-1 ~]$ sudo su -
Last login: Thu Jul 31 11:37:21 UTC 2014 on pts/0
Last failed login: Tue Oct 14 05:45:47 UTC 2014 from 60.172.228.40 on ssh:notty
There were 1269 failed login attempts since the last successful login.
[root@ip-10-10-10-1 ~]# service mysqld start
Initializing MySQL database: Installing MySQL system tables...
141014 17:05:46 [ERROR] /usr/libexec/mysqld: unknown variable 'tmpd1r=/var/tmp'
141014 17:05:46 [ERROR] Aborting

141014 17:05:46 [Note] /usr/libexec/mysqld: Shutdown complete

Installation of system tables failed! Examine the logs in
/var/lib/msql for more information.

...

 [FAILED]

In the Oracle world, it is easier to troubleshoot. Here in the MySQL world, the best way to see which .cnf file is being used, we do it with an strace command.

[root@ip-10-10-10-1 ~]# strace -e trace=open,stat /usr/libexec/mysqld
open("/etc/ld.so.cache", O_RDONLY|O_CLOEXEC) = 3
open("/lib64/libpthread.so.0", O_RDONLY|O_CLOEXEC) = 3
open("/lib64/libaio.so.1", O_RDONLY|O_CLOEXEC) = 3
...
stat("/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=255, ...}) = 0
open("/etc/my.cnf", O_RDONLY)           = 3
stat("/etc/mysql/my.cnf", 0x7fffe4b38120) = -1 ENOENT (No such file or directory)
stat("/usr/etc/my.cnf", {st_mode=S_IFREG|0644, st_size=25, ...}) = 0
open("/usr/etc/my.cnf", O_RDONLY)       = 3
stat("/root/.my.cnf", {st_mode=S_IFREG|0644, st_size=33, ...}) = 0
open("/root/.my.cnf", O_RDONLY)         = 3
...
141014 17:12:05 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

So now I can see that the /usr/etc/my.cnf is the one with the incorrect wording variable, so we modify it to have it the correct one.

[root@ip-10-10-10-1 ~]# cat /usr/etc/my.cnf
[mysqld]
tmpd1r=/var/tmp
[root@ip-10-10-10-1 ~]# sed -i -e 's/tmpd1r/tmpdir/' /usr/etc/my.cnf
[root@ip-10-10-10-1 ~]# cat /usr/etc/my.cnf
[mysqld]
tmpdir=/var/tmp

Another try, but again the same result — but even worse this time, as there is no output. After digging around, I found that the place to look is the /var/log/mysqld.log and the problem was that some libraries belonged to root user, instead of the MySQL user.

[root@ip-10-10-10-1 ~]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
[root@ip-10-10-10-1 ~]# cat /var/log/mysqld.log
141014 17:16:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/msql
141014 17:16:33 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
141014 17:16:33 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
141014 17:16:33 InnoDB: The InnoDB memory heap is disabled
141014 17:16:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141014 17:16:33 InnoDB: Compressed tables use zlib 1.2.7
141014 17:16:33 InnoDB: Using Linux native AIO
/usr/libexec/mysqld: Can't create/write to file '/var/tmp/ib1rikjr' (Errcode: 13)
141014 17:16:33  InnoDB: Error: unable to create temporary file; errno: 13
141014 17:16:33 [ERROR] Plugin 'InnoDB' init function returned error.
141014 17:16:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
141014 17:16:33 [ERROR] Unknown/unsupported storage engine: InnoDB
141014 17:16:33 [ERROR] Aborting

141014 17:16:33 [Note] /usr/libexec/mysqld: Shutdown complete
[root@ip-10-10-10-1 ~]# perror 13
Error code 13: Permission denied
[root@ip-10-10-10-1 ~]# ls -l /var/lib/mysql/mysql/plugin.*
-rw-rw---- 1 root root 8586 Mar 13  2014 /var/lib/mysql/mysql/plugin.frm
-rw-rw---- 1 root root    0 Mar 13  2014 /var/lib/mysql/mysql/plugin.MYD
-rw-rw---- 1 root root 1024 Mar 13  2014 /var/lib/mysql/mysql/plugin.MYI
[root@ip-10-10-10-1 ~]# chown -R mysql:mysql /var/lib/mysql/mysql/

So I think, yey, I’m set and it will come up! I give it one more shot and, you guessed it, same result and different error :( This time around the problem seemed to be that the memory assigned is incorrect and we don’t have enough on the machine, so we change it.

[root@ip-10-10-10-1 ~]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
141014 17:36:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141014 17:36:15 [Note] Plugin 'FEDERATED' is disabled.
141014 17:36:15 InnoDB: The InnoDB memory heap is disabled
141014 17:36:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141014 17:36:15 InnoDB: Compressed tables use zlib 1.2.7
141014 17:36:15 InnoDB: Using Linux native AIO
141014 17:36:15 InnoDB: Initializing buffer pool, size = 100.0G
InnoDB: mmap(109890764800 bytes) failed; errno 12
141014 17:36:15 InnoDB: Completed initialization of buffer pool
141014 17:36:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool
141014 17:36:15 [ERROR] Plugin 'InnoDB' init function returned error.
141014 17:36:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
141014 17:36:15 [ERROR] Unknown/unsupported storage engine: InnoDB
141014 17:36:15 [ERROR] Aborting
[root@ip-10-10-10-1 ~]# grep 100 /etc/my.cnf
innodb_buffer_pool_size=100G
[root@ip-10-10-10-1 ~]# sed -i -e 's/100G/256M/' /etc/my.cnf
[root@ip-10-10-10-1 ~]# grep innodb_buffer_pool_size /etc/my.cnf
innodb_buffer_pool_size=256M

Now, I’m not even expecting this instance to come up, and I am correct — It seems a filename has incorrect permissions.

[root@ip-10-10-10-1 ~]# service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
root@ip-10-10-10-1 ~]# cat /var/log/mysqld.log
...
141014 17:37:15 InnoDB: Initializing buffer pool, size = 256.0M
141014 17:37:15 InnoDB: Completed initialization of buffer pool
141014 17:37:15  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
141014 17:37:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@ip-10-10-10-1 ~]# ls -l /var/lib/mysql/ibdata1
-rw-rw---- 1 27 27 18874368 Mar 13  2014 /var/lib/mysql/ibdata1
[root@ip-10-10-10-1 ~]# ls -l /var/lib/mysql
total 83980
-rw-rw---- 1    27    27 18874368 Mar 13  2014 ibdata1
-rw-rw---- 1    27    27 33554432 Mar 13  2014 ib_logfile0
-rw-rw---- 1    27    27 33554432 Mar 13  2014 ib_logfile1
drwx------ 2 mysql mysql     4096 Mar 13  2014 mysql
drwx------ 2 root  root      4096 Mar 13  2014 performance_schema
drwx------ 2 root  root      4096 Mar 13  2014 test
[root@ip-10-10-10-1 ~]# chown -R mysql:mysql /var/lib/mysql

Now, I wasn’t even expecting the service to come up, but to my surprise it came up!

[root@ip-10-10-10-1 ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

So now, what I wanted to do was connect and start working, but again, there was another error! I saw that it was related to the socket file mysql.sock, so I changed it to the correct value in our .cnf file

[root@ip-10-10-10-1 ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@ip-10-10-10-1 ~]# service mysql status
mysql: unrecognized service
[root@ip-10-10-10-1 ~]# service mysqld status
mysqld (pid  5666) is running...
[root@ip-10-10-10-1 ~]# ls -l /tmp/mysql.sock
ls: cannot access /tmp/mysql.sock: No such file or directory
[root@ip-10-10-10-1 ~]# grep socket /var/log/mysqld.log | tail -n 1
Version: '5.5.36'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@ip-10-10-10-1 ~]# lsof -n | grep mysqld | grep unix
mysqld    5666    mysql   12u     unix 0xffff880066fbea40       0t0     981919 /var/lib/mysql/mysql.sock
[root@ip-10-10-10-1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql

innodb_data_file_path=ibdata1:18M
innodb_buffer_pool_size=256M
innodb_log_file_size=32M
sort_buffer_size=60M

[client]
socket=/tmp/mysql.sock
[root@ip-10-10-10-1 ~]# vi /etc/my.cnf
[root@ip-10-10-10-1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@ip-10-10-10-1 ~]# mysql -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Conclusion

As you can see, there are different ways to troubleshoot the startup of a MySQL instance, so hope this helps you out in your journey when you are starting to use this DBMS and also if you know of another way, let me know in the comment section below.

Please note that this blog post was originally published on my personal blog.

Categories: DBA Blogs

OCP 12C – Partitioning Enhancements

DBA Scripts and Articles - Wed, 2014-10-22 09:13

Online Partition operations Table Partitions and subpartitions can now be moved online. [crayon-5496de4419e3c598025627/] Compression options can also be added during an online partition move. [crayon-5496de4419e47929430664/] Reference Partitioning Enhancements Truncate or Exchange Partition with Cascade option With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when … Continue reading OCP 12C – Partitioning Enhancements →

The post OCP 12C – Partitioning Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

getting re certified for 12c ( makes me grumpy ) ...

Grumpy old DBA - Wed, 2014-10-22 08:02
So let's be honest at the beginning here.

I have way too many professional certifications for any reasonable semi honest human being.  Along with two old Solaris admin certifications 7 and 8 I am ( besides an MBA degree ha ha ) OCP in 7.3 / 8.0 / 8i / 9i / 10g.  At one point a ways back I was teaching all the OCP curriculum for a local community college yes real classes with live instructors so it made sense to actually take the tests my students were taking.

But it's been a really long time now since I did the 10g new features class and did the 10g OCP upgrade exam.  I thought about doing the 11g upgrade at one point ( and attended the 11g new features class ) but never finished it.

Oracle has recently announced a change in policy where old certifications will start "aging out" / expiring.  I don't like this change at all but well it's probably time for me to do the 12c new features class and get 12c certified.

So now it's going to be somewhat of a nightmare to jump through all the hoops.  I have paper copies of ( some of anyhow ) the OCP's and text exams.

You used to take the tests at Sylvan Prometric sites but now there seems to be some other players involved in this whole thing.  Plus I don't have access to any of the old work emails.

This is probably going to make me a wee bit grumpy getting this all sorted out but maybe please please someone inside at oracle might bail me out?

Update on 10/24/2014: So false alarm this is getting sorted out much faster than I was imagining thanks to the oracle university support team.
Categories: DBA Blogs

High System Time on Windows Running in a VM

Pythian Group - Wed, 2014-10-22 07:59

Recently I’ve seen an issue with CPU usage on a server running Windows 2003 Server in a VMware. This is a small Virtual Machine with just 2 cores allocated (which are possibly mapped to “threads” on a host level but I don’t know the details). For some reason very high System CPU time was reported in a Statspack report.

Here is how it looks like in a 1 hour Statspack report:

Host CPU  (CPUs: 2  Cores: 2  Sockets: 0)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO     WCPU
                    ------- -------   ------- ------- ------- ------- --------
                                         3.04    8.77   88.19

Note that the System CPU time is more than twice the User CPU time on average (remember the averages could be misleading sometimes). This caught my attention as usual. Although the average CPU used is not really high, this server is somewhat sluggish even for a one hop RDP connection over the VPN.
I have tried to find out some details about what is going on. Since I’m not a Windows guy, I did not know what kind of tools could be used to track places in the OS kernel that take too much time. On Linux this is relatively easy starting with strace/pstack/perf utilities and other command line tools. Windows is different.

I’ve started to search for the options available, and the first thing to find is of course Perfmon, which allows to track and visualize different OS related metrics (counters in Perfmon terminology) on a system, CPU, or process levels. I’ve used it to capture a few key metrics such as User Time, System Time (which is apparently called Privileged Time on Windows), Queue length and Context Switches per second. From a graph of the CPU usage the issue is visible:

Here the white line is representing Privileged (or System) CPU, and yellow line is Total CPU. It’s clear that almost all used CPU is accounted to the Privileged part.
By the way it is actually very easy to see a similar picture in a standard Performance tab of Task Manager, you just need to select View then Show Kernel Times and Privileged part of the used CPU will be displayed in red.

After that I have searched for details of where to find why Privileged CPU time is so high. A good article that I have found is here. Although it is relatively old, it fits my case as the OS is a 32 bit Windows 2003 Server. The article points to a tool called KernRates. This is a command line tool with a very easy interface: you run it, wait for some time and stop it with Ctrl-C. After that the tool prints the profile of system calls by module. Here is what I’ve seen:

C:\Program Files\KrView\Kernrates>Kernrate_i386_Win2000.exe
 /==============================\
<         KERNRATE LOG           >
 \==============================/
Date: 2014/09/03   Time: 12:39:21
Machine Name: ***
Number of Processors: 2
PROCESSOR_ARCHITECTURE: x86
PROCESSOR_LEVEL: 6
PROCESSOR_REVISION: 1706
Physical Memory: 3072 MB
Pagefile Total: 6996 MB
Virtual Total: 2047 MB
PageFile1: \??\C:\pagefile.sys, 4080MB
OS Version: 5.2 Build 3790 Service-Pack: 2.0
WinDir: C:\WINDOWS

Kernrate User-Specified Command Line:
Kernrate_i386_Win2000.exe

Kernel Profile (PID = 0): Source= Time,
Using Kernrate Default Rate of 25000 events/hit
Starting to collect profile data

***> Press ctrl-c to finish collecting profile data
===> Finished Collecting Data, Starting to Process Results

------------Overall Summary:--------------

P0     K 0:00:03.703 ( 8.7%)  U 0:00:00.734 ( 1.7%)  I 0:00:38.046 (89.6%)  DPC 0:00:00.031 ( 0.1%)  Interrupt 0:00:00.406 ( 1.0%)
       Interrupts= 22840, Interrupt Rate= 538/sec.

P1     K 0:00:02.343 ( 5.5%)  U 0:00:00.656 ( 1.5%)  I 0:00:39.484 (92.9%)  DPC 0:00:00.000 ( 0.0%)  Interrupt 0:00:00.281 ( 0.7%)
       Interrupts= 20017, Interrupt Rate= 471/sec.

TOTAL  K 0:00:06.046 ( 7.1%)  U 0:00:01.390 ( 1.6%)  I 0:01:17.531 (91.2%)  DPC 0:00:00.031 ( 0.0%)  Interrupt 0:00:00.687 ( 0.8%)
       Total Interrupts= 42857, Total Interrupt Rate= 1009/sec.

Total Profile Time = 42484 msec

                                       BytesStart          BytesStop         BytesDiff.
    Available Physical Memory   ,       372678656,       363945984,        -8732672
    Available Pagefile(s)       ,      3285475328,      3281805312,        -3670016
    Available Virtual           ,      2131267584,      2130219008,        -1048576
    Available Extended Virtual  ,               0,               0,               0

                                  Total      Avg. Rate
    Context Switches     ,        61247,         1442/sec.
    System Calls         ,       305201,         7184/sec.
    Page Faults          ,        58440,         1376/sec.
    I/O Read Operations  ,         3496,         82/sec.
    I/O Write Operations ,         2637,         62/sec.
    I/O Other Operations ,        29567,         696/sec.
    I/O Read Bytes       ,     59649045,         17062/ I/O
    I/O Write Bytes      ,      2653894,         1006/ I/O
    I/O Other Bytes      ,    624604436,         21125/ I/O

-----------------------------

Results for Kernel Mode:
-----------------------------

OutputResults: KernelModuleCount = 109
Percentage in the following table is based on the Total Hits for the Kernel

Time   33235 hits, 25000 events per hit --------
 Module                                Hits   msec  %Total  Events/Sec
intelppm                              30310      42486    91 %    17835286
ntkrnlpa                               2337      42486     7 %     1375158
hal                                     271      42486     0 %      159464
mfehidk01                                74      42486     0 %       43543
Ntfs                                     58      42486     0 %       34128
mfehidk                                  52      42486     0 %       30598
mfeapfk                                  47      42486     0 %       27656
mfeavfk01                                17      42486     0 %       10003
tcpip                                    13      42486     0 %        7649
win32k                                   12      42486     0 %        7061
mfeavfk                                  10      42486     0 %        5884
fltmgr                                    6      42486     0 %        3530
CLASSPNP                                  3      42486     0 %        1765
SCSIPORT                                  3      42486     0 %        1765
RDPDD                                     2      42486     0 %        1176
afd                                       2      42486     0 %        1176
Npfs                                      2      42486     0 %        1176
NDIS                                      2      42486     0 %        1176
symmpi                                    2      42486     0 %        1176
TDTCP                                     1      42486     0 %         588
rdbss                                     1      42486     0 %         588
netbt                                     1      42486     0 %         588
mfetdi2k                                  1      42486     0 %         588
ipsec                                     1      42486     0 %         588
termdd                                    1      42486     0 %         588
TDI                                       1      42486     0 %         588
vmxnet                                    1      42486     0 %         588
KSecDD                                    1      42486     0 %         588
atapi                                     1      42486     0 %         588
volsnap                                   1      42486     0 %         588
ftdisk                                    1      42486     0 %         588

================================= END OF RUN ==================================
============================== NORMAL END OF RUN ==============================

The default output contains some basic information about the system, CPU usage, memory and context switching. The kernel modules profile is the most interesting part here. It lists some modules with internal names and the profile data: number of times the module was running during a sample; this is the most important information. So in mycase intelppm was the top running kernel module.
I’ve searched again, now for intelppm, and found a few posts describing similar symptoms. Apparently intelppm is a CPU driver. Sometimes it causes issues such as BSOD or high CPU usage, especially if it is a cloned VM and CPU architecture changes in between. It was not clear if this something which can be disabled, but there were posts suggesting that stopping this service (which is not listed in Services) helped a few people. So I have recommended the client to try to disable this driver with the following commands:

sc config intelppm start=disabled
sc stop intelppm

Theoretically this should disable Intel CPU driver and Windows should try to use another if it is available. When we tried to run it, the 2nd command (to stop the driver) failed with the following message:

[SC] ControlService FAILED 1052:

The requested control is not valid for this service.

So it is not possible to stop the driver online, and Windows restart is necessary.
We did a restart of the VM. After that, the situation was a bit different: the CPU time was somewhat reduced; but the privileged part was still quite high with hal (Hardware Abstraction Layer) on top instead of intelppm:

Time   95865 hits, 25000 events per hit --------
 Module                                Hits   msec  %Total  Events/Sec
hal                                   82669     125183    86 %    16509629
ntkrnlpa                              11788     125183    12 %     2354153
mfehidk                                 474     125183     0 %       94661
mfeapfk                                 224     125183     0 %       44734
Ntfs                                    207     125183     0 %       41339
vmmemctl                                155     125183     0 %       30954
mfeavfk                                  92     125183     0 %       18373
tcpip                                    85     125183     0 %       16975
win32k                                   54     125183     0 %       10784
fltmgr                                   14     125183     0 %        2795
mfetdi2k                                 11     125183     0 %        2196
TDI                                      10     125183     0 %        1997
RDPWD                                     9     125183     0 %        1797
PartMgr                                   9     125183     0 %        1797
KSecDD                                    7     125183     0 %        1397
SCSIPORT                                  7     125183     0 %        1397
afd                                       6     125183     0 %        1198
symmpi                                    6     125183     0 %        1198
RDPDD                                     5     125183     0 %         998
ipsec                                     5     125183     0 %         998
NDIS                                      5     125183     0 %         998
CLASSPNP                                  5     125183     0 %         998
mfebopk                                   4     125183     0 %         798
Npfs                                      3     125183     0 %         599
termdd                                    3     125183     0 %         599
vmxnet                                    2     125183     0 %         399
volsnap                                   2     125183     0 %         399
ndisuio                                   1     125183     0 %         199
mrxsmb                                    1     125183     0 %         199
rdbss                                     1     125183     0 %         199
atapi                                     1     125183     0 %         199

But in terms of Oracle performance everything changed: everything now run much faster, including simple queries in the SQL*Plus. A particular query started to run 3 times faster on average:

-- stats before
SQL> @sqlstats cp9jr3hp1jupk
                  Elapsed     Ela/exec                            User IO     Rows per   Versi           Share  Avg hard
       Execs            s            s      CPU, s    Gets/exec         s         exec     ons   Loads Mem, MB parse, ms PX Exec
------------ ------------ ------------ ----------- ------------ --------- ------------ ------- ------- ------- --------- -------
         135      170.093        1.260      155.31          835      4.29            1       1       1     .02    350.99       0

-- stats after
SQL> @sqlstats cp9jr3hp1jupk
                  Elapsed     Ela/exec                            User IO     Rows per   Versi           Share  Avg hard
       Execs            s            s      CPU, s    Gets/exec         s         exec     ons   Loads Mem, MB parse, ms PX Exec
------------ ------------ ------------ ----------- ------------ --------- ------------ ------- ------- ------- --------- -------
         604      212.151         .351      154.75        1,013     31.79            1       1       1     .02      8.34       0

It looks like the change helped, but there is no sign that it helped on the OS level. This makes me think that such an improvement in performance may be attributed to something else, such as OS, hypervisor or combination of them and Oracle. In any case, high system time is not good and it usually indicates that something is wrong.

Categories: DBA Blogs

Partner Webcast – Oracle JDeveloper 12c : Productive Java-based Application Development

Oracle JDeveloper provides a productive development environment that covers the full development lifecycle and integrates features addressing the multitude of technologies used for building...

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

StatsPack and AWR Reports -- Bits and Pieces -- 1

Hemant K Chitale - Wed, 2014-10-22 04:58
I am planning to put up a few posts on snippets from StatsPack and AWR reports.  This is my first post.
Note : Some figures / details may be slightly changed / masked to hide the real source.

Logical I/O and Change rates :
1.  From a 9.2 StatsPack Report:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: Std Block Size: 4K
Shared Pool Size: Log Buffer:

Load Profile
~~~~~~~~~~~~ Per Second
---------------
Redo size: 56,031.63
Logical reads: 68,286.24
Block changes: 314.88
Physical reads: 842.92
Physical writes: 134.76

With a 4KB Block Size 68,286.24 Logical Reads translates to slightly over 266MB/second. Logical I/O is CPU-bound.  Database activity is Read-Intensive with a high rate of Reads relative to Writes.

2.  From an 11.2 AWR Report :
Cache Sizes
BeginEndBuffer Cache:
Std Block Size:16KShared Pool Size:
Log Buffer:Load Profile
Per SecondPer TransactionPer ExecPer CallDB Time(s):



DB CPU(s):



Redo size:1,593,612.1Logical reads:51,872.5
Block changes:4,212.4Physical reads:63.8
Physical writes:133.1

With a 16KB Block Size, 51,872.5 Logical Reads translates to slightly over 810MB/second.  This consumes CPU cycles.  However, here the number of Block Changes is noticeably high in this environment. This is also reflected in the high Redo rate -- slightly over 5,471MB/hour (Note : "Redo size" is in Bytes).


CPU Consumption :
1.  From a 9.2 StatsPack Report :
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 37.5 2.1
CPU used when call started 37.6 2.1

This indicates 0.375seconds of CPU usage per second -- i.e. approximately 37.5% of 1 CPU (let's take this as an older non-multicore architecture). If the server has 4 CPUs, CPU consumption is 9.4%

2.  From an 11.2 AWR Report :
Instance Activity Stats
  • Ordered by statistic name
StatisticTotalper Secondper Trans... deleted rows ....


... deleted rows ....


CPU used by this session46.85CPU used when call started46.27
This indicates 0.468seconds of CPU usage per second -- i.e. approximately 46.8% of 1 Core.  This is also reflected in the Load Profile section :
Load Profile
Per SecondPer TransactionPer ExecPer CallDB Time(s):



DB CPU(s):0.50.10.000.00
How many CPUs does this machine have ?  AWR reports this :
Host NamePlatformCPUsCoresSocketsMemory (GB)Linux x86 64-bit16162

That means we are using less than half of 1 of 16 cores !  This translates to CPU consumption of 3.125%  The server has too many CPU cores !
Categories: DBA Blogs

Script to compare plan performance

Bobby Durrett's DBA Blog - Tue, 2014-10-21 17:55

Here’s a zip of a script I modified today: zip

Here’s an example output:

 QUERY_NUM SQL_ID        PLAN_HASH_VALUE EXECUTIONS AVG_ELAPSED OPTIMIZER_COST AVG_FETCHES  AVG_SORTS AVG_DISK_READS AVG_BUFFER_GETS   AVG_ROWS    AVG_CPU AVG_IOWAIT AVG_DIRECT_WRITES AVG_PHYS_READS AVG_PHYS_WRITES
---------- ------------- --------------- ---------- ----------- -------------- ----------- ---------- -------------- --------------- ---------- ---------- ---------- ----------------- -------------- ---------------
         1 gxk0cj3qxug85      2051250004   39504258  31630.2852             15  4.71993394 .444248288     4.07294381      440.124393 41.3960784 3447.83137 28056.5262                 0     .006406626               0
         1 gxk0cj3qxug85       548353012   24360619  31854.5456             15  4.73696596 .574941507     4.16225047      443.290799 41.5668639 3501.62695 28205.9349                 0     .009019804               0
         2 53b6j9nvd2vwa       376144290    1069593  438746.758     19.6425025  33.7741683          0     58.9193684       3864.5447  332.18592 32952.0388 406548.271                 0     19.2981312               0
         2 53b6j9nvd2vwa       655563694    1008553  414586.506     15.0111675  33.7122908          0     58.6486828      3851.28671 331.575216 32283.0233 382834.453                 0     12.4507269               0
         2 53b6j9nvd2vwa      2504177057     274652  418157.478     19.5541562  32.8918486          0     61.1868838      3726.22908 323.358235 31005.0901 388545.269                 0     23.2050923               0
         3 4usguw7d6kxy4      2543632952    1070303   151648.49     12911.7175  2.30832577 .999997197     5.49557555      6221.80141 16.7674061 130072.596 10153.4778                 0     .388805787               0
         3 4usguw7d6kxy4      3221641997     996033  151860.479      11987.696  2.22684489 .999998996     7.10842914      6073.16306  15.902446  127194.45 13655.9405                 0     .316254582               0
         3 4usguw7d6kxy4      1764817920          2    277287.5          12860           1          1              1            6956          5     260000    10575.5                 0              1               0
         4 buhvbrfw1uyrd      2225737849    2871021  37985.4363     7.37064414  32.5216263          0      5.0695129      439.697767 319.641241 3463.90361 34645.8396                 0     2.01971389               0
         5 bvttgft3kj2hg      3569562598     293543  252814.965     20.6213018  95.7064212 .999996593     12.3908524      11035.1541 951.571562 137023.945 95634.8559                 0     1.94147365               0
         6 084v1t4whvzwd       883418687      70258  940211.781     4875.03099  1.00001423          0     30.6947394      22558.3683 .954980216 880713.798 16997.2457                 0     2.93185118               0
         7 972sx2uhfswn5       632842214  229406586  279.732773             14  1.00002261 .092121309     .003579313      25.6327361 .941600155 229.417607  23.334532                 0     .000786085               0

I’m using this script to help figure out which plans to lock in with SQL profiles.  For example, sql_id gxk0cj3qxug85 executes plan 2051250004 more often than plan 548353012 and they seem similar in performance so maybe it would be safe to just lock in the first plan.

I’ve also been reviewing the plans manually to see if there were major differences or if the plans were essentially the same.

I had a few minutes so I thought I would pass this along.

– Bobby

Categories: DBA Blogs

OCP 12C – Index and Table Enhancements

DBA Scripts and Articles - Tue, 2014-10-21 07:16

Table Enhancements Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application. To create an invisible column: [crayon-5496de441a710629572881/]  You can’t create invisible columns on : External … Continue reading OCP 12C – Index and Table Enhancements →

The post OCP 12C – Index and Table Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Deploying a Private Cloud at Home — Part 5

Pythian Group - Mon, 2014-10-20 13:05

Today’s blog post is part five of seven in a series dedicated to Deploying Private Cloud at Home, where I will be demonstrating how to configure Compute node and OpenStack services on the compute node. We have already installed the MySQL Python library on compute node in previous posts.

  1. Install OpenStack compute packages on the node
    yum install -y openstack-nova-compute openstack-utils
  2. Configure Nova compute service
    openstack-config --set /etc/nova/nova.conf database connection mysql://nova:Youre_Password@controller/nova
    openstack-config --set /etc/nova/nova.conf DEFAULT auth_strategy keystone
    openstack-config --set /etc/nova/nova.conf keystone_authtoken auth_uri http://controller:5000
    openstack-config --set /etc/nova/nova.conf keystone_authtoken auth_host controller
    openstack-config --set /etc/nova/nova.conf keystone_authtoken auth_protocol http
    openstack-config --set /etc/nova/nova.conf keystone_authtoken auth_port 35357
    openstack-config --set /etc/nova/nova.conf keystone_authtoken admin_user nova
    openstack-config --set /etc/nova/nova.conf keystone_authtoken admin_tenant_name service
    openstack-config --set /etc/nova/nova.conf keystone_authtoken admin_password Your_Password
    openstack-config --set /etc/nova/nova.conf DEFAULT rpc_backend qpid
    openstack-config --set /etc/nova/nova.conf DEFAULT qpid_hostname controller
    openstack-config --set /etc/nova/nova.conf DEFAULT my_ip Your_Compute_node_IP
    openstack-config --set /etc/nova/nova.conf DEFAULT vnc_enabled True
    openstack-config --set /etc/nova/nova.conf DEFAULT vncserver_listen 0.0.0.0
    openstack-config --set /etc/nova/nova.conf DEFAULT vncserver_proxyclient_address Your_Compute_node_IP
    openstack-config --set /etc/nova/nova.conf DEFAULT novncproxy_base_url http://controller:6080/vnc_auto.html
    openstack-config --set /etc/nova/nova.conf DEFAULT glance_host controller
  3. Start the Compute service and its dependencies. Configure them to start automatically when the system boots
    service libvirtd start
    service messagebus start
    service openstack-nova-compute start
    chkconfig libvirtd on
    chkconfig messagebus on
    chkconfig openstack-nova-compute on
  4. Enable IP forwarding
    perl -pi -e 's,net.ipv4.ip_forward = 0,net.ipv4.ip_forward = 1,' /etc/sysctl.conf
    perl -pi -e 's,net.ipv4.conf.default.rp_filter = 1,net.ipv4.conf.default.rp_filter = 0,' /etc/sysctl.conf
    echo "net.ipv4.conf.all.rp_filter=0" >> /etc/sysctl.conf
    sysctl -p
  5. Install legacy networking components and Flat DHCP
    yum install -y openstack-nova-network openstack-nova-api

    We are using legacy networking and single NIC on both controller and compute nodes. Flat and public interfaces will be the same on below configuration. In this case, it is etho replace with the one you have on your system.

    openstack-config --set /etc/nova/nova.conf DEFAULT network_api_class nova.network.api.API
    openstack-config --set /etc/nova/nova.conf DEFAULT security_group_api nova
    openstack-config --set /etc/nova/nova.conf DEFAULT network_manager nova.network.manager.FlatDHCPManager
    openstack-config --set /etc/nova/nova.conf DEFAULT firewall_driver nova.virt.libvirt.firewall.IptablesFirewallDriver
    openstack-config --set /etc/nova/nova.conf DEFAULT network_size 254
    openstack-config --set /etc/nova/nova.conf DEFAULT allow_same_net_traffic False
    openstack-config --set /etc/nova/nova.conf DEFAULT multi_host True
    openstack-config --set /etc/nova/nova.conf DEFAULT send_arp_for_ha True
    openstack-config --set /etc/nova/nova.conf DEFAULT share_dhcp_address True
    openstack-config --set /etc/nova/nova.conf DEFAULT force_dhcp_release True
    openstack-config --set /etc/nova/nova.conf DEFAULT flat_network_bridge br0
    openstack-config --set /etc/nova/nova.conf DEFAULT flat_interface eth0
    openstack-config --set /etc/nova/nova.conf DEFAULT public_interface eth0
  6. Start the services and configure them to start when the system bootsservice openstack-nova-network start
    service openstack-nova-metadata-api start
    chkconfig openstack-nova-network on
    chkconfig openstack-nova-metadata-api on
  7. Restart networking
    service network restart

 

This completes the configuration of compute node. Stay tuned for part six where we will configure network services on controller node.

Categories: DBA Blogs

OCP 12C – ADR and Network Enhancements

DBA Scripts and Articles - Mon, 2014-10-20 11:59

ADR enhancements In oracle 12c the Automatic Diagnostic Repository contains a new log directory with 2 subdirectories : DDL Debug The DDL log When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log. The DDL logging feature … Continue reading OCP 12C – ADR and Network Enhancements →

The post OCP 12C – ADR and Network Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs