Monday, December 13, 2010

My Oracle Support (MOS) Maintenance outages - irritating as hell for a Global 24 x 7 support site.











The
"Thank you in advance for your patience, and we apologize for any inconvenience.
The My Oracle Support Team" 
does not do it.


The MOS folks write their maintenance notifications as if it's no big deal, as the outage is on a Sunday night - IN THE U.S.

For those of us ahead of US time, it's Monday morning, the time when you follow up on weekend problems - one of the times of the week when you are more likely to want to use MOS to log SRs or search the knowledge base for the myriad of Oracle bugs that could be the cause of your issue unavailable.

Not impressed for a Global support site, based on technologies that support 24x7 operation.

Friday, December 3, 2010

DBAs: The Heroes of the IT World

Here Here!
Just been looking through pages on Database Configuration and Change Management in relation to the SDLC and stumbled upon this intelligent (because of the statement) post.
DBAs: The Heroes of the IT World

Thursday, October 28, 2010

DBMS_STATS returns ORA-20001 is an invalid identifier for externally authenticated Windows domain user names

Trying to collect stats as an externally identified Windows domain user on a wee table in a wee database on my notebook I got:
SQL> show user
USER is "OPS$NZLDOM01\BROCASK"
SQL> BEGIN
  2    SYS.DBMS_STATS.GATHER_TABLE_STATS (
  3        OwnName        => 'OPS$NZLDOM01\BROCASK'
  4       ,TabName        => 'PARENT_TAB'
  5      ,Estimate_Percent  => 0
  6      ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO'
  7      ,Degree            => 4
  8      ,Cascade           => FALSE
  9      ,No_Invalidate     => FALSE);
 10  END;
 11  /
BEGIN
*
ERROR at line 1:
ORA-20001: OPS$NZLDOM01\BROCASK is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 20392
ORA-06512: at "SYS.DBMS_STATS", line 20415
ORA-06512: at line 2
This is a known issue that according to Metalink / MOS note
Bug 7229351 - DBMS_STATS ORA-20001 for username with special characters [ID 7229351.8]

Description

DBMS_STATS procedures may report ORA-20001 errors if a username contains a special character such as a '\'.

eg: Executing DBMS_STATS.DELETE_DICTIONARY_STATS as user OPS$FOO\BAR raises Error ORA-20001: OPS$FOO\BAR is an invalid identifier. 

It is supposed to be fixed in
however I'm running 11.2 Patch 7 on Windows and the problem still exists :)

The note suggests as a workaround: "Do not use a user name that contains special characters"


Which is a bit hopeless given that using a domain user on windowes forces the username to be ...domain name\username.


However I was able to use Toad to collect stats, which made me wonder how it did it given that it was using DBMS_STATS. What it does is it whacks a pair of double-quotes around the username before it puts on the single quotes:
SQL> BEGIN
  2    SYS.DBMS_STATS.GATHER_TABLE_STATS (
  3        OwnName        => '"OPS$NZLDOM01\BROCASK"'
  4       ,TabName        => 'PARENT_TAB'
  5      ,Estimate_Percent  => 0
  6      ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO'
  7      ,Degree            => 4
  8      ,Cascade           => FALSE
  9      ,No_Invalidate     => FALSE);
 10  END;
 11  /

PL/SQL procedure successfully completed.
Yay!

Monday, October 25, 2010

Install 11g Release 2 RAC On Linux Using VirtualBox Walkthrough

Tim Hall has provided a nice walkthrough of installing 11.2 RAC on VirtualBox using shared disk rather than a 3rd software NAS.

Because 11.2 clusterware is now a bloated fat pig and uses atleast 1 GB of RAM per machine, to set this up you need about 3GB per machine at least, which means having a wee RAC instance on your notebook is no longer viable.

Friday, August 20, 2010

LadyJava Music Video for JavaZone

Terrific remake of Lady Gaga's Bad Romance with Java-based lyrics. Oracle gets a mention.

Features Jenny Skavlan, a Norwegian model, actress and TV presenter.

Monday, August 16, 2010

Oracle VM VirtualBox now supports shared disk for clusterware / RAC support

I am loving Oracle now owning Sun and therefore VirtualBox - or "Oracle VM VirtualBox" as it is now called.

Since it came under Oracle's wing, there have been hundreds of bugs fixed and now the latest release of VirtualBox supports shared disk. Lack of shared disk support has been a complaint for folk wanting to install clusterware of some sort for Oracle RAC installations on VirtualBox.

From the Changelog for VirtualBox 3.2.8:

The following items were fixed and/or added:
...
Sharing disks: support for attaching one disk to several VMs without external tools and tricks (see here for a short explanation)

Wednesday, March 17, 2010

How to relink Oracle when "relink all" fails.

Are you trying to relink oracle and getting
"make: *** No rule to make target `install'. Stop."

or something similar?

The problem is due to "relink all" trying relink all the products in the Oracle Home without checking whether they are actually installed or not.

This does not always work as some makefiles take different arguments, and some Oracle Home installations do not have the components installed for which there are makefiles.

This relinking problem has been addressed in 11.2 with the 11.2 installer getting a -relink option.

Assuming you are not using 11.2, then what to do? Well...

I offer this only as what I currently do. I haven't seen this problem properly discussed anwhere, so this is a 'works for me'.

Each Oracle Home has a file:
 $ORACLE_HOME/inventory/make/makeorder.xml

This file lists the correct order to run the make files, and the targets to use. From this we can build up a list of makefile commands that should be run.

Alternatively you can run the relink command with all the other valid parameters - some will fail if the component is not installed, but theoretically this should compile everything.

At the moment though I am not 100% confidant this is the case, and so for a bit of paranoid DBA practice I also run the make files as listed in makeorder.xml

IMPORTANT NOTE: After relinking, login as root (or use sudo if configured) to execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags.

By way of example from my last relink - after upgrading from OEL5 U2 to OEL5 U4:

Note that relinking is done as the oracle home owner, not root.

Step 0: Before doing anything check umask is 022. If not:
$ umask 022

Relink database home works (mostly) but fails on relinking ODBC at the end.


The error that initiated this post: Tried a relink all on an ASM home.
Resulted in
 make: *** No rule to make target `install'. Stop."

when trying to link the ODBC driver.

Solution: Well see below, for what I did in teh ASM case, however if you want to relink ODBC this is what I found in a Metalink note:
cd $ORACLE_HOME/odbc/lib
cp $ORACLE_HOME/lib/sqoci.a .
cp $ORACLE_HOME/lib/sqora.a .
cp $ORACLE_HOME/lib/utility.a .
make -f ins_odbc.mk isqora
cp libsqora.so* $ORACLE_HOME/lib

Relink ASM home


. oraenv [+ASM]
cdo
# Note: cdo is a shell alias defined as: alias cdo='cd $ORACLE_HOME'

relink oracle
relink client_sharedlib
relink client
relink utilities
relink ldap
#relink oemagent - no target
relink network
relink ctx
relink interMedia

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
make -f rdbms/lib/ins_rdbms.mk ioracle
make -f rdbms/lib/ins_rdbms.mk client_sharedlib
make -f sqlplus/lib/ins_sqlplus.mk install
make -f network/lib/ins_net_client.mk preinstall
make -f network/lib/ins_net_client.mk itnsping
make -f rdbms/lib/ins_rdbms.mk "irman"
make -f plsql/lib/ins_plsql.mk "install"
make -f network/lib/ins_net_client.mk "nnfgt.o"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f rdbms/lib/ins_rdbms.mk "utilities"
make -f ldap/lib/ins_ldap.mk "clientonlyinstall"
make -f network/lib/ins_net_client.mk "ntcontab.o"
make -f network/lib/ins_net_client.mk  "nnfgt.o"
make -f network/lib/ins_net_client.mk  "install"  
make -f network/lib/ins_nau.mk "ioklist"
make -f network/lib/ins_nau.mk  "iokinit"
make -f network/lib/ins_nau.mk "iokdstry"
make -f sysman/lib/ins_emagent.mk "agent"
make -f sysman/lib/ins_emagent.mk "nmb"
make -f sysman/lib/ins_emagent.mk  "nmo"
make -f sysman/lib/ins_emagent.mk "nmhs"
make -f sysman/lib/ins_emagent.mk "tclexec"
make -f rdbms/lib/ins_rdbms.mk "all_no_orcl"
make -f srvm/lib/ins_srvm.mk "install"
make -f racg/lib/ins_has.mk  "racg_install"
make -f network/lib/ins_net_server.mk "install"
make -f rdbms/lib/ins_rdbms.mk "ctx_on"
make -f ctx/lib/ins_ctx.mk "install"
make -f rdbms/lib/ins_rdbms.mk  "ipc_g"
make -f sysman/lib/ins_emagent.mk "emsubagent"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f rdbms/lib/ins_rdbms.mk "idg4odbc"
make -f precomp/lib/ins_precomp.mk "links"
make -f precomp/lib/ins_precomp.mk "relink"
make -f precomp/lib/ins_precomp.mk  "gen_pcscfg"
make -f rdbms/lib/ins_rdbms.mk "svr_tool"
make -f rdbms/lib/ins_rdbms.mk "patchset_opt"

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh

Startup Oracle listener
Start up ASM instance

Relink agent home


# agentenv is a custom script that sets Oracle environment variables appropriately 
# for the agent home - all homes are using the one OS user - 'oracle'.
# Normally I recommend one OS user per home.

. agentenv
relink client
relink oemagent

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
cdo
make -f network/lib/ins_net_client.mk itnsping
make -f network/lib/ins_net_client.mk nnfgt.o
make -f network/lib/ins_net_client.mk mkldflags
make -f network/lib/ins_net_client.mk client_sharedlib
make -f sysman/lib/ins_emagent.mk agent
make -f sysman/lib/ins_emagent.mk nmb
make -f sysman/lib/ins_emagent.mk nmo
make -f sysman/lib/ins_emagent.mk nmhs
make -f sysman/lib/ins_emagent.mk emsubagent

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh

Relink OMS HOme


Note omsenv is a custom script setting shell vriables for the OEM/OMS home.
. omsenv
cdo

relink client
relink network
relink client_sharedlib
relink oemagent
relink utilities

DBA Paranoia: To be doubly sure, run make files as listed in $ORACLE_HOME/inventory/make/makeorder.xml
make -f network/lib/ins_net_client.mk "nnfgt.o"
make -f network/lib/ins_net_client.mk "mkldflags"
make -f network/lib/ins_net_client.mk "client_sharedlib"
make -f sysman/lib/ins_sysman.mk agent
make -f sysman/lib/ins_sysman.mk nmo
make -f sysman/lib/ins_sysman.mk nmb
make -f sqlplus/lib/ins_sqlplus.mk install
make -f network/lib/ins_net_client.mk install
make -f network/lib/network/lib/ins_nau.mk ioklist
make -f network/lib/network/lib/ins_nau.mk iokinit
make -f network/lib/network/lib/ins_nau.mk iokdstry
make -f webcache/lib/ins_calypso.mk install

Execute the $ORACLE_HOME/root.sh script to reset the appropriate file permissions/flags:
sudo $ORACLE_HOME/root.sh

References


Howto Relink an Agent ? [ID 805495.1]

Oracle 11gR2 Relink New Feature [ID 883299.1]

Thursday, March 4, 2010

Fun query cancelling using TCP out-of-band signalling

How to CANCEL a query running in another session using an interesting  dodgy method.

How to CANCEL a query running in another session? - Tanel Poder's blog

See also

Sending and Receiving Out-of-band Data
Chapter 3 Advanced Topics for Stream Sockets
HP 9000 Networking: BSD Sockets Interface Programmer's Guide
http://docs.hp.com/en/B2355-90136/ch03s07.html

Killing Database Sessions - tips from Tanel on killing at the OS level & using oradebug

Tanel Poder in one of his blog entries detailed his preferred approach for killing database sessions - in particular the annoying ones that wont die.

In short:
1) Verify whether you’re killing the right session first (Naturally)

2) ALTER SYSTEM KILL SESSION

3) If that doesn’t work immediately then check whether the target session has acknowledged the kill and is rolling back its (large) transaction.

4) If there’s no rollback happening and session just seems to be stuck, then its time to kill that session’s process from OS level.

5) If couple of minutes after killing the process from OS level that sessions and its locks & resources are still not released then attach to your own process with oradebug and run “ORADEBUG WAKEUP 2″ a couple of times (and checking if the session has gone + waiting few seconds between each invocation).

See Tanel's post for full details.

Alter system kill session and ORA-00031: session marked for kill

Saturday, February 20, 2010

DBMS_SCHEDULER gotcha - ORA-00942: table or view does not exist when trying to drop job

Are you trying to drop a list of jobs like:

BEGIN

DBMS_SCHEDULER.DROP_JOB( job_name=> 'job1,job2...,jobn');

END;

and getting an error?

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 5261

ORA-06512: at "SYS.DBMS_SCHEDULER", line 609

ORA-06512: at line 2

You think this must be a security or permissions issue and you grant "CREATE JOB" and "MANAGE SCHEDULER" to the account, but it still doesn't work. You try running it as SYS and you still get the error.

You search Metalink and get no hits. You search google and you get no hits.

You try just dropping one of the jobs in the list and you get a different error:

ERROR at line 1:

ORA-27475: "XXXX.JOB1" must be a job

ORA-06512: at "SYS.DBMS_ISCHED", line 182

ORA-06512: at "SYS.DBMS_SCHEDULER", line 615

ORA-06512: at line 2

And THAT gives you the hint as to the actual cause of the problem:

ONE OF THE JOBS IN THE LIST HAS ALREADY BEEN DROPPED, OR THE NAME IS WRONG.

Check the jobs against what is currently listed and remove the offender.

Shoot the person that supplied you the list of jobs to be dropped.

Saturday, January 9, 2010

How to Configure Solaris 10 Project Resource Controls for Oracle.


This is not an all-inclusive installation guide – Oracle already has written that


Oracle® Database Installation Guide 10g Release 2 (10.2) for Solaris Operating System (SPARC 64-Bit) HTML


Oracle® Database Installation Guide 10g Release 2 (10.2) for Solaris Operating System (x86) HTML


Oracle® Database Installation Guide 10g Release 2 (10.2) for Solaris Operating System (x86-64) HTML


Rather this is to supplement Chapter 2, Preinstallation Tasks. Sections 2.54 – 2.6 inclusive. Oracle forgot to add, and continues to forget, the details on creating a project for Oracle and configuring resource controls for that project.


So, picking up from where you create the Oracle Inventory group, oinstall, and the database administrator group, dba. (   # groupadd oinstall     # groupadd dba)



Configure Kernel Parameters and Resource Controls


From the Oracle installation guide it is not clear which kernel parameters are supposed to use the new Solaris 10 resource control facility, and those which are to use the the /etc/system file.


The following table is ripped from the 10.2 installation guide and as it says in the doco, the kernel parameter and shell limit values shown in the following section are recommended values only.


For production database systems, Oracle recommends that you tune these values to optimize the performance of the system. Refer to your operating system documentation for more information about tuning kernel parameters blah blah blah.


The table contains the four resource controls that replace the old entries in the /etc/system file for a specific kernel parameter. Note that the /etc/system file is still used for the parameters listed with an ‘NA’ in the Resource Control Column.























































Kernel Parameter



Resource Control



Recommended Value



shmsys:shminfo_shmmax



project.max-shm-memory



4294967295



shmsys:shminfo_shmmni



project.max-shm-ids



100



semsys:seminfo_semmsl



project.max-sem-nsems



256



semsys:seminfo_semmni



project.max-sem-ids



100



noexec_user_stack



NA



1



semsys:seminfo_semmns



NA



1024



semsys:seminfo_semvmx



NA



32767



shmsys:shminfo_shmmin



NA



1



shmsys:shminfo_shmseg



NA



10



 

I’ll ignore the kernel parameters in /etc/system – that’s documented already. Let’s take a look at the resource control settings instead. Much more interesting.


Before we start setting resource values, we first need to create a project for the Oracle installation:



Create a Project for Oracle


# projadd group.dba

You should now see the project listed by the projects command and in /etc/project

bash-3.00# projects -l group.dba

group.dba

        projid : 100

        comment: ""

        users  : (none)

        groups : (none)

        attribs: 

 

bash-3.00# cat /etc/project

system:0::::

user.root:1::::

noproject:2::::

default:3::::

group.staff:10::::

group.dba:100::::

 

Details on the /etc/project file:


man pages section 4: File Formats: project(4)


http://docs.sun.com/app/docs/doc/816-5174/6mbb98ui4?a=view#project-4



 

 

Assuming all looks good, then proceeed…



Configure Project Resource Controls


 

projmod -sK "project.max-shm-memory=(privileged,2G,deny)" group.dba

projmod -sK "project.max-sem-ids=(privileged,100,deny)" group.dba

projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba

projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.dba

 

 

Just to check (for fun):

bash-3.00# projects -l group.dba

group.dba

        projid : 100

        comment: ""

        users  : (none)

        groups : (none)

        attribs: project.max-sem-ids=(privileged,100,deny)

                 project.max-sem-nsems=(privileged,256,deny)

                 project.max-shm-ids=(privileged,100,deny)

                 project.max-shm-memory=(privileged,2147483648,deny)

 


Hurrah!


 


 


For other values that can be assigned to a project, see project(4) and man pages section 5: Standards, Environments, and Macros: resource_controls(5) http://docs.sun.com/app/docs/doc/816-5175/6mbba7f33?a=view


 


BTW, don’t use the prctl command to change the values (as shown in part of the installation guide) as the changed parameters do not persist after a system restart (as it says later in the installation guide after you have already followed their example and used prctl). Use projmod


 


For further details see Solaris 10 Reference Manual Collection, man pages section 5: Standards, Environments, and Macros, resource_controls(5)


http://docs.sun.com/app/docs/doc/816-5175/6mbba7f33?a=view


 


System Administration Guide: Solaris Containers-Resource Management and Solaris Zones


"Setting Resource Controls"


http://docs.sun.com/app/docs/doc/817-1592/6mhahuoj5?a=view


 


System Administration Guide: Solaris Containers-Resource Management and Solaris Zones, "Projects and Tasks (Overview)"


http://docs.sun.com/app/docs/doc/817-1592/6mhahuoh9?a=view


 



 

 

3. Create the Oracle user account, oracle.


 



    # useradd -g oinstall -G dba -m -d /export/home0/oracle -s /bin/bash -K project=group.dba oracle

 

·  Set the password of the oracle user:


# passwd -r files oracle




 

 

What’s with this –K value? From the sun man page doco:


-K key=value


Replace existing or add to a user's key=value pair attributes. Multiple -K options can be used to replace or add multiple key=value pairs. The generic -K option with the appropriate key can be used instead of the specific implied key options (-A, -P, -R, -p). See user_attr(4) for a list of valid key=value pairs. Values for these keys are usually found in man pages or other sources related to those keys. For example, see project(4) for guidance on values for the project key. Use the command ppriv(1) with the -v and -l options for a list of values for the keys defaultpriv and limitpriv.


Keys must not be repeated. Specifying a key= without a value removes an existing key=value pair.


The type key must be specified only without a value or with the role value. Specifying the type key without a value leaves the account as a normal user, with the role value changing from a normal user to a role user. As a role account, no roles (-R or roles=value) can be present.


Address : http://docs.sun.com/app/docs/doc/816-5166/6mbb1kqjj?a=view



 

See user_attr(4) for a list of valid key=value pairs

 

 

The Oracle user’s default project


 

 


Assuming all is OK, we should now see in the /etc/user_attr file an entry for Oracle.


 



bash-3.00# cat /etc/user_attr

#

# Copyright 2007 Sun Microsystems, Inc.  All rights reserved.

# Use is subject to license terms.

#

# /etc/user_attr

#

# execution attributes for profiles. see user_attr(4)

#

#ident  "@(#)user_attr  1.1     07/01/31 SMI"

#

#

adm::::profiles=Log Management

lp::::profiles=Printer Management

postgres::::type=role;profiles=Postgres Administration,All

root::::auths=solaris.*,solaris.grant;profiles=Web Console Management,All;lock_after_retries=no

oracle::::type=normal;project=group.dba

 

 

 

Oracle’s default project should now be group.dba



 

Check:

 

bash-3.00# su - oracle

Sun Microsystems Inc.   SunOS 5.10      Generic January 2005

-bash-3.00$ id -p

uid=101(oracle) gid=102(oinstall) projid=100(group.dba)

-bash-3.00$

 

Most satisfactory.

 

 

If you have already created the Oracle user – perhaps you are trouble shooting and have just found this great posting by me – you can use the usermod command to assign Oracle to the project.

 

# usermod -K project=group.dba oracle

 

 

You may find that while oracle is a member of the project, it’s default project is not group.dba. If this is the case, peruse the following, as there is an algorithm Solaris uses to determine the default project:

 

Determining a User's Default Project


To log in to the system, a user must be assigned a default project. A user is automatically a member of that default project, even if the user is not in the user or group list specified in that project.


Because each process on the system possesses project membership, an algorithm to assign a default project to the login or other initial process is necessary. The algorithm is documented in the man page getprojent(3C). The system follows ordered steps to determine the default project. If no default project is found, the user's login, or request to start a process, is denied.


The system sequentially follows these steps to determine a user's default project:


1.       If the user has an entry with a project attribute defined in the /etc/user_attr extended user attributes database, then the value of the project attribute is the default project. See the user_attr(4) man page.


2.       If a project with the name user.user-id is present in the project database, then that project is the default project. See the project(4) man page for more information.


3.       If a project with the name group.group-name is present in the project database, where group-name is the name of the default group for the user, as specified in the passwd file, then that project is the default project. For information on the passwd file, see the passwd(4) man page.


4.       If the special project default is present in the project database, then that project is the default project.







 



In Confusion Conclusion


 


That should be enough to get you going – you can continue on with the Oracle installation guide 2.7 Identifying Required Software Directories


 



Final Note / Appendix (can you have an appendix in a blog post?) - Commands Used With Projects and Tasks


The commands that are shown in the following table provide the primary administrative interface to the project and task facilities.









































Man Page Reference Description 
projects(1)Displays project memberships for users. Lists projects from project database. Prints information on given projects. If no project names are supplied, information is displayed for all projects. Use the projects command with the -l option to print verbose output.
newtask(1)Executes the user's default shell or specified command, placing the execution command in a new task that is owned by the specified project. newtask can also be used to change the task and the project binding for a running process. Use with the -F option to create a finalized task.
passmgmt(1M)Updates information in the password files. Use with the -K key=value option to add to user attributes or replace user attributes in local files.
projadd(1M)Adds a new project entry to the /etc/project file. The projadd command creates a project entry only on the local system. projadd cannot change information that is supplied by the network naming service.

Can be used to edit project files other than the default file, /etc/project. Provides syntax checking for project file. Validates and edits project attributes. Supports scaled values.
projmod(1M)Modifies information for a project on the local system. projmod cannot change information that is supplied by the network naming service. However, the command does verify the uniqueness of the project name and project ID against the external naming service.

Can be used to edit project files other than the default file, /etc/project. Provides syntax checking for project file. Validates and edits project attributes. Can be used to add a new attribute, add values to an attribute, or remove an attribute. Supports scaled values.
projdel(1M)Deletes a project from the local system. projdel cannot change information that is supplied by the network naming service.
useradd(1M)Adds default project definitions to the local files. Use with the -K key=value option to add or replace user attributes.
userdel(1M)Deletes a user's account from the local file. 
usermod(1M)Modifies a user's login information on the system. Use with the -K key=value option to add or replace user attributes.