Oracle

Leave a comment Posted on Oracle, Oracle Dba

First Thoughts on 12C from a Dba Perspective

Introduction.

Last few weeks ago I invested some time in attending  a webinar on Oracle 12CRac Upgrade  and  i was also present at the 2 Days  Oracle Roadshow for Partners in the Netherlands  and i must say I was really impressed with the community an Oracle sharing new Technology this way.  Of course an article like this can only give you a glimpse of all the great new features  that are out there for you  but I will try to show you some of my favorites  from a Dbas perspective.

It was not much of a surprise to see that Oracle in the Database environment will focus on Big Data (seems that it never stops growing)  on Engineered Systems ( still have to put my hands some time on an Exadata but so far no project brought me near that) and of course into the Cloud computing ( Not without a reason they called their latest Release 12C where C stands for Cloud computing and for consolidation of data(bases)).

From an architecture point it is clear that Oracle will focus on a continued drive towards shared servers with a common OS and shared Databases in the Multi tenant environment (on the web and in the Debates already know as the Pluggable Databases).  As a dba working in a large Data-center that makes extra curious and cautious cause in consolidation this way is also something to bear in mind  when consolidating more and more databases on less and less ( but more powerful machine). You and your team should try hard to bring similar environments together on such multi tenant environments cause from experience it can be very hard to patch the very  diverse environments on limited boxes ( even with rolling upgrades , Rac being present etc.).

Lets take a look at some of the key Focus Areas:

Multi tenant Architecture.

In order to save resources both in hardware,  memory by consolidate databases  Oracle has introduced  Container databases (aka CDB). A Container databases in itself will act like literally a container cause it will be able to hold multiple Pluggable Databases ( meanwhile already well-known as pluggable Databases  (PDB).  AS in mentioned in my intro please be careful what you bring together though  try to look for similar environments with regard to security , patching requirements etc.

The big promise the Container databases will offer:

  • Consolidation of (pluggable) databases in to a container save Resources , offer the ability to handle multiple as on and even  give separate PDBs different priorities in the Container Database.
  • With changing workload unplugging and plugging a Pluggable database into another container.
  • In separating duties between Infra structure dbas and Application Dbas  the Multi tenant Dbs  offer you the option to manage the containers  using the Infra structure Dbas and it offers you the option in the dedicated PDB to set up Application Dba environment  limited to that one PDB only.
  • Manage the PDBS as one since you set up  your backups on the Container Level ( restore on the  Pluggable Db level is of course possible).
  • Flexible management with the PDBS since you can move them towards different tiers when they become more mission critical. ( And  with a specific tier you cover Slas with regard to (High) availability and  restores).
  • Fast provisioning with regard to setting up cloning , copy of the Pluggable Databases. Which as a Dba i particularly like since it offers  option to refresh preproduction from  Prod, maybe even move from Preproduction to production that way.

Automatic Data Optimizing.

Even though i will not cover this in this Article i think it is good to mention key development as with regard to  Performance tuning and Information life cycle management.  You really should check out the great features of:

  • Heat Map
  • (Smart) compression ( somehow i think  this technology was adopted from the Exadata environment)
  • The Automated tiering . It looks really like dbas together with their customers can put an effort in automating Information life-cycle management  in 12c.

Flex ASM.

In my day to day business dealing with a lot of Rac environments , and indeed sofar ( 11.2 included)  it can be a real bad day when an ASM instance crashes cause it will take all other instances on that box with her.  IN 12c  there is no longer a 1:1 map between Node-ASM instance. So  you will be able to have nodes without ASM instances  that are connected ( indeed most likely using the same Interconnect) to nodes that Do have ASM instances. And  of course you are right ASM could still be a single point of failure..  but of course Oracle thought of that  by allowing that non ASM node to connect to a different ASM Instance and this concept really makes me excited !  Well Done Oracle .

Real Application Clusters (RAC).

  • Till 12c it was a requirement to have all servers same in capacities like cpu, memory that is no longer needed .
  • Adding – Removing nodes become more easy.
  • Setting up pools and being able to move nodes between pools .
  • Multiple scans possible.
  • Support of Ipv6.
  • Of course supporting the Multi tenant options that 12c is offering .
  • Flex clustering where  Central ( Hub nodes ) can be easily connected to so-called Leaf Nodes which  are not directly connected to the storage nor via interconnects to different nodes . The promise this offers is  that it will be easier to add or remove (leaf) nodes since they only connected to their hub node.

Hmm i guess i could be writing on an and on. But the purpose of this article is only to share some insights and to make you want more by looking for more yourself .

Some hints to get you even more started:

  • Two great book recommendations i recently purchased myself:
    • Expert Oracle Rac 12c ( reading it  and it is an asset to read !)
    • Expert Oracle Enterprise Manager 12c ( will be great reading about 12C Grind control since i really need to get my hand back on the Enterprise Manager).
  • The web  is really a Candy store with regard to information being present on the topic of 12c. My recommendation would be to get involved in blogging and twitter to have early insight on the developments in the community that is out there waiting for you
  • A big recommendation ( also a big Thank you to Steve Karam on the Web also know as the Oracle Alchemist ) who did  excellent job in offering a Summary on a lot of articles on 12c that are already out there. so TY Steve ! : http://www.oraclealchemist.com/news/install-oracle-12c-12-1/

AS always  happy reading.

Mathijs

Leave a comment Posted on Oracle, Oracle Dba, Rac

Asm Instance not starting after Cluster Node Reboot

Introduction.

I have been involved again in a situation where  the Rac cluster did not start after a reboot of the server during a maintenance window. And as always a true challenge that was. In such cases it is true that the alert log of the node and the ohasd logging  will be your best friends ( well together with Metalink and Google of course).

Details:

After a Os patching action on one of the nodes on one of my 11.2 Racs (Grid Infrastructure)  i was contacted can you please take a look cause the clusterware is not starting. After first investigation it showed that statement was not entirely true .  The cluster ware itself had been started but the  log file for the ohasd. showed following details , that it was not able to start the asm Resource due to ORA-01031: insufficient privileges.

this is what it showed:

## /opt/crs/product/11.2.0.2_a/crs/log/Mysrvr1r/ohasd [+ASM1]# view ohasd.log
2013-08-06 11:05:01.643: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/11.2.0.2_a/crs/bin/oraagent_oracle
2013-08-06 11:05:01.644: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.644: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.644: [   CRSPE][1991387456] {0:0:2} Got agent-specific msg: ORA-01031: insufficient privileges
2013-08-06 11:05:01.646: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/11.2.0.2_a/crs/bin/oraagent_oracle
2013-08-06 11:05:01.646: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.646: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.829: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/11.2.0.2_a/crs/bin/oraagent_oracle
2013-08-06 11:05:01.829: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the last reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} RI [ora.asm 1 1] new internal state: [STABLE] old value: [CLEANING]
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} CRS-2681: Clean of 'ora.asm' on 'Mysrvr1r' succeeded

That did not look all to good. I had a first guess about what was going on by trying to connect to the asm instance on that box via sqlplus ( sqlplus / as sysasm). When that showed  also the ORA-01031: insufficient privileges.

I had to giggle cause when  looking for that  message on the web  i ended up with my blog. Which proves once again that you can help yourself by helping others by sharing in the Oracle community.   Basically i  focused on  three metalink notes that might apply:

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]

UNIX: Checklist for Resolving Connect AS SYSDBA Issues [ID 69642.1]

UNIX: Diagnostic C program for ORA-1031 from CONNECT INTERNAL / AS SYSDBA [ID 67984.1]

The third note (67984.1) was my bingo !  So it was proved that my groupid ( dba) altered from 101 to some other value by a ldap lookup.. I have asked the Linux colleague  to disable these lookups and after that the asm instance started and all the instances as well.  As a workaround , in the /etc/ldap.conf they have added the oracle user to the nss_initgroups_ignoreusers to prevent this from happening.

Happy reading,

Mathijs

Leave a comment Posted on Oracle, Oracle Dba, Rac

Plan to setup OID (Oracle implementation of the Ldap) in a Rac environment

Introduction:

For some time it was planned to implement OID and frankly from the start I knew that would be something different compared to the things I have done so far.  Well but then again it does represent a new frontier does it not? So I felt challenged with the implementation. As an extra aspect during this setup we implemented the OID with Rac databases as a backend.  But as always the quote of the day for this was Success just loves good preperations. So we started with a setup of  a plan. The plan we have implemented captures the following 7 steps to success:

  • Check and plan the layout of your Ldap environment and your ldap.ora. For us that meant creating an environment to hold all production environments, one to hold the test environments well and after all  we added a third content as a concatination of both other environemnts since we  wanted to make sure that in the ldap.ora with only one setting in place we could contact all environments for clients who  will be on citrix servers ( and as far as I know in ldap.ora only one DEFAULT_ADMIN_CONTEXT = “dc=central,dc=env ,dc=EU” which you can use without using full qualified names):

CN=ORCLADMIN

And

dc= prod dc=EU

And

dc=test dc=EU

And

“dc=central,dc=env,dc=EU”

### think about the Ports for the Ldap you will need, one port for NON SSL, one for SSL traffic. Ask yourself if you will come across Firewalls during the setup  & operation.

  • Install a database (in this setup we use 11.2 as version). Since we wanted to deliver a High available solution in this setup we have implanted 3 Rac databases with 2 Oracle Instances each.
  • Install Weblogic 10.3.2 —> It should not be needed in the first place I read in some articles but I spoke to colleagues and they recommended that I should download the Weblogic part as well.  Only download I see is Weblogic server 12c.  So I download installers with Oracle Weblogic Server and Oracle coherence: http://www.oracle.com/technetwork/middleware/weblogic/downloads/index.html Since I am installing on 64bit Linux I was considering download generic file but given the fact that these boxes do not have Java setup properly decided to go with (wls1211_linux32.bin).
  • Setup and implement rules with regard to bulk load and bulk delete.
  • Implement Replication between the Environments.

Happy reading,

Mathijs

Leave a comment Posted on Oracle

Endpoints_listener.ora not updated when altering listener.ora by hand

As a part of standards every (RAC) database has its own listener in my projects. This week i was asked to alter the local listener to a different port to keep it similar to a specific port that I used on other database(s) in that project.
Technically this was Of course a little effort  2 be made to keep everybody happy.

I altered my listener.ora (yup still an old-fashioned kind of guy) manually and I stopped and started the listener:

srvctl start listener -l listener_mydb1
srvctl status listener -l listener_mydb1

And in the database i had altered the local_listener too  to show new ports well. And of course  I altered the tnsnames entry too.  So I had it all covered i thought. That was only until the moment that I checked the listener for that database for its status. And much to my surprise I saw this.

Continue reading →

Leave a comment Posted on Oracle

When listener does not know of service requested in connect (ORA-12514).

Introduction:

After setting up a taf service in one of  my rac environments i checked if the (local) listener that was configured for the database was showing the services as to be expected. Much to my surprise that  was not the case. So puzzled as i was ( after all this was not the first rac I build) I went on to a hunt to find what was going on. In this blog is my  share my steps / thoughts how i came to the happy end that of course was out there waiting for me.

Continue reading →

Leave a comment Posted on Oracle, Oracle Dba, Rac

Thoughts on Adding a Single Instance To Oracle Clusterware (Grid Infra).

General: Configuring Active/Passive Clustering for Oracle Database ( Single Instance in Crs)

The Oracle database can easily be configured to use the Cluster ware framework for high availability.

Using Grid Infrastructure to protect a database resource is a very cost-effective way of setting up an Active/passive cluster. As an added advantage, using only one vendor’s software stack to implement the cluster can make troubleshooting easier. Staff already familiar with RAC will easily be able to set up and run this configuration because it uses a same software stack: all commands and log files are in familiar locations, and troubleshooting does not rely on the input from other teams.

To set up an active/passive cluster with 11g Release 2, you need to initially install Grid Infrastructure on all nodes of the cluster. Grid Infrastructure will give the user with a cluster logical volume manager: ASM. If for some reason another file system is required, you can choose the supported cluster file systems, including ACFS. Using a cluster file system that is mounted concurrently to both cluster nodes offers the advantage of not having to remount the database files and binaries in case of a node failure. Some configurations we saw suffered from extended fail over periods caused by required file system checks before the file system could be remounted.

On top of the Grid Infrastructure build, you do a local installation of the RDBMS. It is important that you do not choose a cluster installation when prompted so; otherwise, you risk violating your license agreement with Oracle.

Continue reading →

Leave a comment Posted on Oracle, Oracle Dba, Rac

Steps to Recreate my Central Inventory in a Real Applications Cluster environment on Linux

Introduction:

In the last quarter of 2012 i have set up  a 4 Node Real Application Cluster on Linux which was great to do as always. In day-to-day business I had lost track of this environment  which in itself is not that bad of course. However this week I needed the environment as a preparation for patching so it would be mandatory to check with opatch last patch installed in the grid infra structure and in the rdbms homes that have been set up on those boxes. Well it was to be expected that other dbas would work on those boxes, maybe / likely might have installed new patches so it was really like getting back together with old friends to see what was under the hood.

Well it was a bit of disappointment that the inventory destination  (/opt/oracle/orInventory) was simply empty . Opatch did not like it either cause it  exited with a returncode 73. But then again on the bright side this meant i could take note of the necessary steps  performed  and turn it into a blog.

Continue reading →

Leave a comment Posted on Oracle, Oracle Dba

Rman Catalog Crosscheck and Delete obsolete an Approach

Introduction

When You are or will be working with Rman  as your backup solution you will have to ask yourself several questions . One of the elementary ones will be that you have to consider whether or not you will be utilizing an Rman Catalog or that  you will have faith in the existing  multiplexed controlfiles.  On the web I see debates going on about the use of a catalog and indeed given the idea that you will not need a catalog , since you are not be using stored scripts, you might get timeouts simply cause working with catalog shows long time no communication ( and you have those hmm lets say aggressive  firewalls in place that will snap at that so-it-seems idle sessions) etc. Well this post is about what you should do if you decide to have a catalog  for rman after all or if a catalog is in place due to company hmm  history – habits – or standards.

What  you should consider to have in place ( of course always  depends on your settings and configuration) Should be  a maintenance job for your Rman Catalog for the specific Database ( target)  you have added there.  With some explain below I would like to show you what was done for that maintenance part  by showing scripts we used for that.

Details:

#!/bin/ksh
#set -vx
PROG=$(basename $0)

if [ -z “$1” ]; then
echo “ERROR – wrong start commando”
echo “EXAMPLE – $PROG <SID>”
exit 1
fi

## Script will take the Instance that has been registered as an input parameter
export ORACLE_SID=$1

## Finding the ORATAB since it will help to set Oracle Home
if [ “${OS}” = “Linux” ];then
export ORATAB=/var/opt/oracle/oratab
else
export ORATAB=/etc/oratab
fi
ORATABLINE=`grep $ORACLE_SID $ORATAB`
export ORACLE_HOME=`echo $ORATABLINE | cut -f2 -d:`

## exporting several Parameters among which logging path and logfilename with a timestamp in it
export LOGPATH=/opt/oracle/scripts/log
export CURRENT_TIMESTAMP=`date +%Y%m%d_%H%M%S`   # Format: YYYYMMDD_HHMISS   e.g.: 20110907_150455
export LOGFILE=${ORACLE_SID}_crosscheck_${CURRENT_TIMESTAMP}.log
export NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”
export ORA_NLS32=${ORACLE_HOME}/ocommon/nls/admin/data
export ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
export NLS_LANG=American_America.WE8ISO8859P15

## Connect to rman and your catalog  and start logging (msglog)  to the specified logdir and logfile.
## then allocate a channel. Note if you make backups to TAPE as we do you HAVE to allocate a channel for maintenance type SBT_TAPE and you HAVE to mention in our case specific Networker Server and Networker Client.
## then you perform the crosscheck backup which will check in the Networker database if the Backups is still available. If it is not the backup will be tagged expired in the Rman catalog.
## Once the  crosscheck is finished we wanted to see a result so did a list backup.
## If you are sure that what you see is correct  delete noprompt expired will erase the no longer existing backups in rman.. MAYBE best to run script first without this. Always best to be safe then sorry.
## After delete another Listing is performed as a record of the post  delete information in the catalog.

${ORACLE_HOME}/bin/rman TARGET  / RCVCAT rman_${ORACLE_SID}/${ORACLE_SID}@RMAN MSGLOG ${LOGPATH}\/${LOGFILE} << EOF
allocate channel for maintenance device type ‘SBT_TAPE’;
send ‘NSR_ENV=(NSR_SERVER=adebckus-nl,NSR_CLIENT=adesnouh)’;
crosscheck backup;
list backup of database summary;
delete noprompt expired backup;
list backup of database summary;
release channel;
exit
EOF

Happy Reading and best  of Luck,

Mathijs

Leave a comment Posted on Oracle, Oracle Dba, Rac

When Starting a Rac database brings you: ORA-29760: instance_number parameter not specified.

Introduction

Recently was supporting a colleague who was unable to start a Real Application Database after he had  created the environment by manual scripts. As i already mentioned in another post nothing wrong with exploring both methods ( either using DBCA (with care but hey any tool will come with a manual and  some shortcomings) or manual. Well if you decide to work manually  of course it will take maybe even  more discipline in setting up init.ora files , registering the database and its instances into the Grid Infra structure layer etc.  But generally speaking there is a challenge in either approach you choose. Well on to our case.  The Database would not start via the clusterware (with srvctl ) but manually  it was possible to start either of the two instances in this Rac.

Investigation:

Well first glimpse to see what might be going on:

SQL> select * from gv$instance;

INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
———- ————— —————- —————————————————————- —————– ——————- ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 1 MYDB1 server1hr 11.2.0.3.0 11.04.2013 16:14:31 OPEN YES 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

2 2 MYDB2 server2hr 11.2.0.3.0 11.04.2013 16:25:54 OPEN YES 2 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

That query  showed both instances running. 

Then the colleague tried:

srvctl start database -d  … 

PRCR-1079 : Failed to start resource ora.mydb.db

CRS-5017: The resource action “ora.mydb.db start” encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to “(:CLSN00107:)” in “/opt/crs/product/11203_ee_64/crs/log/server1hr/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

CRS-2674: Start of ‘ora.mydb.db’ on ‘server1hr’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.mydb.db’ on that would satisfy its placement policy
CRS-5017: The resource action “ora.mydb.db start” encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to “(:CLSN00107:)” in “/opt/crs/product/11203_ee_64/crs/log/server2hr/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

CRS-2674: Start of ‘ora.mydb.db’ on ‘server2hr’ failed

Well then it was time to Google  for ORA-29760: instance_number parameter not specified

A Fellow blogger  mentioned the following Mos Note to solve this issue. And it once again proved that if you really settle for doing manual setup of you Rac environment you have to be well prepared and consistent in the naming of your parameters in you configuration file ( init.ora as a base for an spfile) and the way you enter services in the cluster layer. It once again proved that  names are registered in the way you enter them.  In plain English it is Case sensitive so  the old saying : what you type is what you get is very applicable.

MOS note ‘ORA-29760: instance_number parameter not specified’ When Starting the Database with Srvctl [ID 749515.1]

Happy reading and as always best of luck.

Mathijs.

Leave a comment Posted on Oracle, Rac

11.2 Rac – Instance unwilling to start with Error ORA-00132

Introduction:

This week  had one of those days where after working together with customer and Oracle Consultant  one  of  the Rac Databases needed a restart due to a variable change.  Together with customer it was decided to restart second  instance first so first took care of the  environment by enabling a Maintenance mode so our monitoring tool would not strike me with tickets.

The second instance was   friendly:

  • the shutdown: srvctl stop instance -d -i went smooth .
  • the startup: srvctl start instance -d -i went smooth .

Then i  performed same steps on first Instance:

  • the shutdown: srvctl stop instance -d -i went smooth .
  • the startup: srvctl start instance -d -i   failed  with an error message that puzzled me a lot .

ORA-00132: syntax error or unresolved network name ‘myserver-scan:33000’

I checked my scan listener settings:

oracle@myserver-s1:/opt/oracle [CRS]#
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node myserver-s4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node myserver-s2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node myserver-s3

oracle@myserver-s1:/opt/oracle [CRS]# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:33000
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:33000
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:33000

Crs agent log showed same error and i could not start the instance via the  srvctl tool. The error suggested that my remote listener ( i am using scan ) was the issue.. But the  remote listener was not even part of  my tnsnames.ora, since my spfile value for the remote listener = myserver-scan:33000.

That was soo confusing. Well  I attempted to start the Instance via sqlplus  and that worked. I checked the cluster and it reported  ONLINE ONLINE on the resource so  the first pressure was off.

To solve it , make sure that  the sqlnet.ora is similar on all environments and it seems to need the ezconnect information in there  ( in names.directory_path).([ID 1318026.1])

Happy reading

Mathijs