Infrastructure at your Service

Oracle 11g Archives - Page 2 of 8 - Blog dbi services

Pierre Sicot

statistics_level parameter

By | Database management | No Comments

With Enterprise Manager Cloud Control 12c configuring and using the compliance standard utility allows us to discover events we should not have seen in other ways. Today at a customer’s site I discovered the following message:   The configuration Best Practice for Oracle Database discovered the statistics_level parameter was set to the value ALL.   By selecting the Violation in the Configuration Best Practice for Oracle Database, we can display the message:     By…

Read More
Oracle Team

Index on trunc(date) – do you still need old index?

By | Database management, Oracle | 6 Comments

By Franck Pachot . Sometimes we have to index on ( trunc(date) ) because a SQL statement uses predicate on it instead of giving a range from midnight to midnight. When you do that you probably keep the index on the column. That’s two indexes to maintain for DML. Do we need it? I’ll show a feature that appeared in 11.2.0.2 (info from oracle-l) so let’s set the optimizer to behave as before that feature….

Read More
Oracle Team

How to remap tablespaces using Oracle Import/Export Tool

By | Database management | One Comment

Since Oracle 10g, Oracle provides a great tool to import and export data from databases: Data Pump. This tool offers several helpful options, particularly one that allows to import data in a different tablespace than the source database. This parameter is REMAP_TABLESPACE. However, how can you do the same when you cannot use Data Pump to perform Oracle import and export operations? I was confronted with this issue recently, and I had to deal with…

Read More
Oracle Team

Linux: how to monitor the nofile limit

By | Operation systems, Oracle | One Comment

By Franck Pachot . In a previous post I explained how to measure the number of processes that are generated when a fork() or clone() call checks the nproc limit. There is another limit in /etc/limits.conf – or in /etc/limits.d – that is displayed by ‘ulimit -n’. It’s the number of open files – ‘nofile’ – and here again we need to know what kind of files are counted. nofile ‘nofile’ is another limit that…

Read More
Oracle Team

Oracle policy managed databases: Policies and policy sets

By | Database management | One Comment

In this post, I will continue my experiments with Oracle policy managed databases with Grid Infrastructure, and present one of the new features introduced with Grid Infrastructure 12c last June: the cluster configuration policies and policy sets. It allows the administrator to dynamically reconfigure all server pools at a time, with a single command, according to the business workload. To take a simple example, imagine you have two servers allocated for your production instances, and…

Read More
Oracle Team

Implementing policy-managed Oracle databases

By | Database management | No Comments

Policy-managed databases appeared with Oracle 11g R2 but are not very common yet. I never had the opportunity to administer these kind of databases. Since Oracle now presents policy-databases as the default installation mode and as a best practice, I wanted to know more about this feature which fully automatizes the databases repartition within an Oracle cluster. In this post, I will describe how to implement and handle policy-managed databases, and in a future post…

Read More
Oracle Team

Oracle TNS_ADMIN issues due to bad environment settings

By | Database management | 2 Comments

Recently, I faced a TNS resolution problem at a customer. The reason was a bad environment setting: The customer called the service desk because of a DBLINK pointing to a bad database. The users were supposed to be redirected to a development database, and the DBLINK was redirecting to a validation database instead. The particularity of the environment is that development and validation databases are running on the same server, but on different Oracle homes, each…

Read More
Oracle Team

Oracle is hanging? Don’t forget hanganalyze and systemstate!

By | Database management, Oracle | 12 Comments

By Franck Pachot . sqlplus / as sysdba oradebug setmypid oradebug unlimit oradebug hanganalyze 3 oradebug dump ashdumpseconds 30 oradebug dump systemstate 266 oradebug tracefile_name Your Oracle database – production DB, of course – is hanging. All users are blocked. You quickly check the obvious suspects (archivelog destination full, system swapping, etc.) but it’s something else. Even you, the Oracle DBA, cannot do anything: any select is hanging. And maybe you’re even not able to…

Read More
Oracle Team

Oracle 12c: Partitioning enhancements Part II – Other improvements

By | Database management | No Comments

Oracle Database 12c offers several enhancements for partitioning. In a previous blog posting, I talked about reference partitioning enhancements. This post will present other enhancements in relation to general partitioning. Asynchronous index maintenance With Oracle database, a global index becomes UNUSABLE when dropping or truncating the partition on which this index points, until the clause UPDATE GLOBAL INDEXES is used. With Oracle 11g, droping or truncating a partition involves the index maintenance, consisting on the…

Read More
Grégory Steulet

Errors while installing Oracle Database Vault on Oracle 11.2.0.3

By | Database management | 2 Comments

During one of my last consulting missions, I had to install Oracle Database Vault on an existing Oracle environment. It clearly was not a straigthforward process, since I experienced some weird errors such as: ORA-28003: password verification for the specified password failed, ORA-20001: Password length less than 8, and ORA-01917: user or role ‘LBACSYS’ does not exist. After having a look at several log files, I found the root causes of this error. Below, you…

Read More