Tag Archives: 11.2

An introduction to Policy Managed Databases in 11.2 RAC

I just realised this week that I haven’t really detailed anything about policy managed RAC databases. I remembered having done some research about server pools way back when 11.2.0.1 came out. I promised to spend some time looking at the new type of database that comes with server pools: policy managed databases but somehow didn’t get around to doing it. Since I’m lazy I’ll refer to these databases as PMDs from now on as it saves a fair bit of typing.

So how are PMDs different from Administrator Managed Databases?

First of all you can have PMDs with RAC only, i.e. in a multi-instance active/active configuration. Before 11.2 RAC you had to tie an Oracle instance to a cluster node. This is why you see instance prefixes in a RAC spfile. Here is an example from my lab 11.2.0.3.6 cluster:

DEMO1.__db_cache_size=1073741824
[...]
DEMO2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DEMO/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='...'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DEMO'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
DEMO2.instance_number=2
DEMO1.instance_number=1
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=310378496
*.processes=300
*.remote_listener='rac11gr2scan.example.com:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=1610612736
DEMO1.thread=1
DEMO2.thread=2
DEMO1.undo_tablespace='UNDOTBS2'
DEMO2.undo_tablespace='UNDOTBS1'

Note that the instance_number, thread and undo tablespace are manually (=administrator) managed. If these aren’t set or configured incorrectly you will run into all sorts of fun. Continue reading

The renamdg command revisited-ASMLib

I have already written about the renamedg command, but since then fell in love with ASMLib. The use of ASMLib introduces a few caveats you should be aware of.

USAGE NOTES

This document presents research I performed with ASM on a lab environment. It should be applicable to any environment, but you should NOT use this for production-the renamedg command still is buggy, and you should not mess with ASM disk headers in an important system such as production or staging/UAT. You set the importance here!  The recommended setup for cloning disk groups is to use a data guard physical standby database on a different storage array to create a real time copy of your production database on that array. Again, do not use you production array for this!

Continue reading

crsctl status resource – state details are really useful

A very short post about a cool new feature I noticed today. RAC 11.2 has moved a lot of commands previously having their own syntax into crsctl. One of the cool new things is the fact that crsctl status resource -t (“tabular”) reports state details. Here I could see that my lab environment had a stuck archiver. Other state details include information about the cluster time synchronisation daemon ctss, or ASM instances. Have a look at my 4 node cluster:

[oracle@rac11gr2node2 ~]$ crsctl stat res -t 
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.LISTENER.lsnr
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.OCRVOTE.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.asm
 ONLINE  ONLINE       rac11gr2node1            Started             
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.eons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.gsd
 OFFLINE OFFLINE      rac11gr2node1                                
 OFFLINE OFFLINE      rac11gr2node2                                
 OFFLINE OFFLINE      rac11gr2node3                                
 OFFLINE OFFLINE      rac11gr2node4                                
ora.net1.network
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.ons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.registry.acfs
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.LISTENER_SCAN2.lsnr
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.LISTENER_SCAN3.lsnr
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.oc4j
 1        OFFLINE OFFLINE                                                   
ora.poldb.db
 1        ONLINE  INTERMEDIATE rac11gr2node3            Stuck Archiver      
 2        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
ora.poldb.drcp.svc
 1        ONLINE  ONLINE       rac11gr2node3                                
 2        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.poldb.nondrcp.svc
 1        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.polstdby.db
 1        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
 2        OFFLINE OFFLINE                                                   
ora.prod.batchserv.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.prod.db
 1        ONLINE  ONLINE       rac11gr2node1            Open                
 2        ONLINE  ONLINE       rac11gr2node2                                
ora.prod.reporting.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node1.vip
 1        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node2.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.rac11gr2node3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.rac11gr2node4.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan1.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.scan2.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                

Nice!

orapki 11.2 bug when password complexity is too low

I am currently experimenting with SSL encryption for ONS, an 11.2 new feature. My system is OEL 5.5 64bit with PSU 11.2.0.1.1 installed on the stack.

Apart from the poor documentation, the orapki tool that can be used to create self-signed certificates for testing has a bug in 11.2. There is also a documentation bug that fails to mention the need to set the sticky bit in the directory where the certificate is to be stored. Anyway, I followed the appendix F in the Advanced Security Guide to create the wallet but I failed to get this to work:

[grid@rac11gr2node1 ~]$ mkdir orapki
[grid@rac11gr2node1 ~]$ chmod +t orapki
[grid@rac11gr2node1 ~]$ ls -ld /home/grid/orapki/
drwxr-xr-t 2 grid oinstall 4096 Jun 15 08:48 /home/grid/orapki/
[grid@rac11gr2node1 ~]$ ls -al /home/grid/orapki/
total 16
drwxr-xr-t  2 grid oinstall 4096 Jun 15 08:48 .
drwx------ 20 grid oinstall 4096 Jun 15 08:48 ..
[grid@rac11gr2node1 ~]$ orapki wallet create -wallet /home/grid/orapki/ -pwd oracle -auto_login
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Unable to save wallet at /home/grid/orapki/

Whatever I tried this didn’t work. I even tried creating the wallet in /tmp (which has the sticky bit set) but no luck. Oracle Support claimed I was doing something wrong, as their example worked:

[grid@rac11gr2node1 ~]$ orapki wallet create -wallet /home/grid/orapki/ -pwd "welcome1" -auto_login
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

[grid@rac11gr2node1 ~]$ ls -la orapki
total 32
drwxr-xr-t  2 grid oinstall 4096 Jun 15 08:49 .
drwx------ 20 grid oinstall 4096 Jun 15 08:48 ..
-rw-------  1 grid oinstall 3589 Jun 15 08:49 cwallet.sso
-rw-------  1 grid oinstall 3512 Jun 15 08:49 ewallet.p12

So where was the difference? The only thing I could see was the password. And indeed, the error message is wrong. Support created bug 9817962 – ORAPKI DISPLAYS INVALID ERROR MESSAGE WHEN PASSWORD COMPLEXITY IS NOT MET.

Funny enough, orapki displays the correct error message in 11.1.0.7:

$ orapki wallet create -wallet  -pwd "oraclexyzsss" -auto_login
Invalid password....
PASSWORD_POLICY : Passwords must have a minimum length of eight 
characters and contain alphabetic characters combined with numbers or 
special characters.

I wonder what other surprises await me…

PRKP-1061, CRS-2518 and CRS-0219 with 10.2 RDBMS and 11.2 CRS

Couldn’t find a better title for this, but hopefully makes it easier to locate the problem when using your favourite search engine. I tried to manage my 10.2.0.4.1 3 node RAC database “QA” for which I upgraded Grid Infrastructure to 11.2.0.1. The problem arose when I tried to disable the 10g database, and then when I wanted to start it. Here’s the output of my feeble attempt:

[oracle@rac11gr2node1 ~]$ srvctl disable database -d QA
PRKP-1061 : Can not find a service member to start for service qa.
CRS-2518: Invalid directory path ‘/u01/app/grid/product/10.2.0/bin/racgwrap’
CRS-0219: Could not update resource ‘ora.qa.db’.

Ooops. That looked bad, although some other commands worked, like srvctl status database etc. It turned out that the problem is with the database’s resource profile:

export ORACLE_HOME=/u01/app/grid/product/11.2.0/grid  
# note the 11.2 Grid Infrastructure home!

$ORACLE_HOME/bin/crsctl stat res ora.qa.db -p | less

NAME=ora.qa.db
TYPE=application
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--,
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=/u01/crs/oracle/product/crs/bin/racgwrap
ACTIVE_PLACEMENT=0

This strange behaviour is caused by bug  bug 9257105. Even though the upgrade from CRS 10.2 to 11.2 finishes successfully, the OCR configuration for any pre-11.2 database still points to the pre-11.2 CRS home (remember that the 11.2 upgrade is out-of-place!). The bug is fixed with 11.2.0.1.2 and 11.2.0.2.0 (none of which are out yet).

A workaround is availble: as the pre-11.2 database owner, execute the following command for each pre-11.2 database from the Grid Infrastructure home:

[grid@rac11gr2node1:~ ]$ crsctl modify res ora.<dbname>.db -attr "ACTION_SCRIPT=$GRID_HOME/bin/racgwrap"

You can use the crsctl stat res ora.<dbname>.db -p command to check the action script now points to the correct ORACLE_HOME. Done, enjoy!

It is indeed possible to install Oracle 11.2 on OpenSolaris

After I set up OpenSolaris 2009.06 (see my previous posts) on my openSuSE 11.2 dom0 I was keen to set up Oracle 11.2 single instance on ZFS. ZFS is a new-ish filesystem and successor to ufs no Solaris 10. I like it a lot for simplicty and ease of use. Piet de Visser should love it too :)

But before I could start working on this I needed to do some prep work.

Storage preparation on the dom0

Very simple-I created 2 more zero padded files in my domU base directory and added those to the domU configuration file, as in:

disk = [
 "file:/m/xen/osol/system,xvda,w",
 "file:/m/xen/osol/oracle_bin,xvdb,w",
 "file:/m/xen/osol/oracle_data,xvdc,w",
]
vif = [ "mac=00:16:3e:1b:e8:18,bridge=br1,script=vif-bridge" ]

Be sure to add a MAC address to your configuration file or otherwise Solaris will cry out loud the next restart claiming the new storage pool has been used with a different system. That’s not a problem (can be fixed with zpool import -f) but it’s not pretty either.

Addition of new storage pool

This is really simple! First of all you need to find out which of the disks presented to the system are the new ones. I only used 1 disk for the rpool (default storage pool), so I tried to identify this one first:

root@opensolaris:~# zpool status rpool
 pool: rpool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 rpool       ONLINE       0     0     0
 c7t0d0s0    ONLINE       0     0     0

errors: No known data errors

Then I identified the disks on the system:

root@opensolaris:~# format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
 0. c7t0d0 <DEFAULT cyl 4095 alt 0 hd 128 sec 32>
 /xpvd/xdf@51712
 1. c7t1d0 <Unknown-Unknown-0001-10.00GB>
 /xpvd/xdf@51728
 2. c7t2d0 <Unknown-Unknown-0001-10.00GB>
 /xpvd/xdf@51744
Specify disk (enter its number): ^C
root@opensolaris:~#

So, not really surprisingly, c7t1d0 and c7t2d0 were the new disks. I created a new storage pool “oraclepool” with these 2 disks (not recommended for production!)

root@opensolaris:~# zpool create oraclepool c7t1d0 c7t2d0

root@opensolaris:~# zpool status -v
 pool: oraclepool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 oraclepool  ONLINE       0     0     0
 c7t1d0      ONLINE       0     0     0
 c7t2d0      ONLINE       0     0     0

errors: No known data errors

 pool: rpool
 state: ONLINE
 scrub: none requested
config:

 NAME        STATE     READ WRITE CKSUM
 rpool       ONLINE       0     0     0
 c7t0d0s0    ONLINE       0     0     0

errors: No known data errors

With that done, I created a few file systems:

  • oraclepool/binaries
  • oraclepool/oradata
zfs create oraclepool/oradata
zfs create oraclepool/binaries

By default these are mounted to poolname/fs name, i.e. /oraclepool/oradata which isn’t too convenient. Luckily, zfs allows you to easily change the mountpoint without touch /etc/vfstab (which isn’t recommended anyway).

So, enter these commands to change the mountpoints:

zfs set mountpoint=/u01 oraclepool/binaries
zfs set mountpoint=/u01/oradata oraclepool/oradata

The final layout prior to the installation was as follows:

zfs set mountpoint=/u01 oraclepool/binaries
root@opensolaris:~# zfs list
NAME                       USED  AVAIL  REFER  MOUNTPOINT
oraclepool                7.98G  11.6G    19K  /oraclepool
oraclepool/binaries       6.68G  11.6G  6.68G  /u01
oraclepool/oradata        1.30G  11.6G  1.30G  /u01/oradata
rpool                     5.27G  2.55G  77.5K  /rpool
rpool/ROOT                3.01G  2.55G    19K  legacy
rpool/ROOT/opensolaris    3.01G  2.55G  2.87G  /
rpool/dump                 256M  2.55G   256M  -
rpool/export              2.22M  2.55G    21K  /export
rpool/export/home         2.20M  2.55G  1.63M  /export/home
rpool/export/home/martin   584K  2.55G   584K  /export/home/martin
rpool/swap                   2G  4.08G   474M  -

Oracle user creation

Create the oracle user as always, I did the following:

  • groupadd oinstall
  • groupadd dba
  • useradd -g oinstall -G dba -d /export/home/oracle -s `which bash` -m oracle
  • chown -R oracle:oinstall /u01
  • projadd -U oracle -K “project.max-shm-memory=(priv,4096MB,deny)” user.oracle
  • projmod -s -K “project.max-sem-ids=(priv,256,deny)” user.oracle

Set static IP

Edit /etc/hosts to include your hostname, then edit /etc/nwam/llp to include your network interface, the keyword “static” and the ip/netmask.

My example uses:

root@opensolaris:~# cat /etc/nwam/llp
xnf0    static 192.168.99.11/24

Change Swap

It’s necessary to increase swap space or otherwise the ld will fail during the “linking phase”.  I increased to 2G from 512M:

root@opensolaris:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   512M     -
root@opensolaris:~# zfs set volsize=2G rpool/swap
root@opensolaris:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE    SOURCE
rpool/swap  volsize   2G       -

Oracle Installation

We need to create a symlink for -lcrypto, otherwise one of the shared libraries won’t link with a missing reference to “-lcrypto”:

ln -s /lib/amd64/libcrypto.so /usr/sfw/lib/amd64

Run the installer in silent mode – I modified one of the response files and simply executed ./runInstaller -silent -debug -fore -responseFile /path/to/responseFile.rsp -ignoreSysPrereqs

Database Creation

I ran dbca in silent mode as oracle as follows:

cd /u01/app/oracle/product/11.2.0/dbhome_1/
export ORACLE_HOME=`pwd`
cd bin
./dbca -silent -createDatabase -gdbName orcl \
  -templateName General_Purpose.dbc -emConfiguration none \
  -datafileDestination /u01/oradata -sysPassword xxx \
  -systemPassword xxx -storageType FS -initParams  \
  filesystemio_options=setall

Success!

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> !cat /etc/release
 OpenSolaris 2009.06 snv_111b X86
 Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
 Use is subject to license terms.
 Assembled 07 May 2009

SQL>

Now off to learn dtrace…

Reference

Praise where praise is due-I got some good pointers from the pythian blog.

Upgrade ASM 10.2 to 11.2 single instance

This post describes how to upgrade an existing single instance ASM installation to the latest and greatest, Oracle 11.2. The most noteworthy change is that ASM is no longer installed using RDBMS installer but rather the Grid Infrastructure.

Huh-installing CRS for single instance? That at first sounded like a bit of an overkill but Oracle left us with no choice. As you can see later, it’s not as bad as it seems, I have to say I rather like the Oracle Restart feature (see my previous blog post).

So-as always-start by downloading the necessary software linux.x64_11gR2_grid.zip and unzip it somewhere convenient.

Preparation

I recommend getting some metadata from the ASM instance just in case:


SQL> select name,state from v$asm_diskgroup;

NAME			       STATE
------------------------------ -----------
DATA			       MOUNTED
FRA			       MOUNTED
LOG			       MOUNTED

SQL> select name,path from v$asm_disk;

NAME                           PATH
------------------------------ -------------------
DATA1                          ORCL:DATA1
LOG1                           ORCL:LOG1
FRA1                           ORCL:FRA1

SQL> show parameter asm

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string	 DATA, FRA, LOG
asm_diskstring			     string	 ORCL:*
asm_power_limit 		     integer	 1
SQL>

Continue reading