DbVisualizer 6.5.7 is released

Today DbVisualizer 6.5.7 is released. This release has all the additions that are used in the book Mastering Oracle Scheduler. Next to that a little support for ASM and Resource Manager is included.

Mastering Oracle Scheduler in Oracle is available now !

In what I see as a delayed birthday present, yesterday finally the book Mastering Oracle Scheduler in Oracle 11g Databases is published.

Writing the book was real fun. Before writing this book I already had completed a few projects involving the migration from a third party scheduler to the Oracle Scheduler. This gave me an advantage that was very usable during the writing of this book. The book is based on the 11g release of oracle but is also very usable for the 10g version.

During my projects I had a hard time finding my way through the scheduler. The manual was not very helpful and oracle support did - at that time - have hardly any knowledge about this very powerful product. The manuals do cover how procedures should be called but did not explain anything about why a procedure should be called and when.

This is where I hope my book can make things easier for anyone who is going to automate business processes using the Oracle Scheduler. The product really deserves attention. It is very competitive, if you know how to unleash the real power.

While writing the book I stumbled upon a few bugs and I am happy to tell that all bugs I found are solved. This I have tested using oracle 11.1.0.7 on Linux. Most problems where generic, so if you do not run on Linux, a patch should easily be ported to any other platform.

Schedulers are fun to play with and I have done so for many years. The product is still improving every release and I hope that the enhancement requests that I filed will be incorporated into the next release scheduler, making it even better. It is very clear that oracle needs users requests and feedback to improve their products and it is good to see that they really use it.

Now that the book is ready it is time to find myself a new boat.

Howto configure resource manager using DbVisualizer

I already told about the fact that you can teach the DbVisualizer tool perform about every trick that you can come up with to do in a database. One thing I found that was lacking was Resource Manager. Together with Nathan Aaron I implemented some form of Resource Manager control in dbvis. Here is a short explanation about how to use it.

first download my oracle.xml for DbVisualizer - see the links section for the exact location

Connect to an oracle database, preferably as a dba and go to the DBA tree in the object tree for the database that you connected to.
Open the DBA tree and go to Resource Mamanger

In dbvis you can configure actions on objects in the tree. For Resource Manager you will need to create a pending area, make changes, validate and submit the pending area when you are satisfied, or clear the pending are when you do not want to commit your changes. Those actions can be seen by using ctrl-click on the Resource Manager object in the tree. The actions menu for Resource Manager will popup.

Every time when you connect to the database to work on Resource Manager you will need to Create Pending Area again. You can not connect to the previously created pending area. You can recognize if a pending area is active by opening the sub trees. The Resource Plans trees show the defined Resource Manager Plans. I used a little encoding to show which plan is active and an indicator for Top plan or Sub plan. Here you can see the the plan called PLANBOARD is the active Top plan.
Currently the pending area is not active. Create the pending area by choosing Create Pending Area from the Resource Manager context menu. A confirmation screen will popup that can also show the SQL that is going to be used. Hit enter to complete the action and see what happens to the object tree.
It suddenly shows the same info as before with an extra copy with PENDING as status. Here you can see that Create Pending Area effectively duplicates the current configuration so you can work on the duplicate.

Now open the PLANBOARD plan (you can download the sql to generate this plan from the PLANBOARD log I made before). And open the Resource Plan Directives. where you now should be able to see the plan directives that are defined for the plan called planboard. Now remove the Resource Consumer Group PRIO3 from the plan by ctrl-clicking on the PRIO03 directive in the object tree. For every action in dbvis you get a popup asking for confirmation and optionally showing the generated SQL. Again, hit enter to remove the plan from the plan in the pending area. If you regret this action you can clear the pending area, create it again and start all over. In this case we are going to create an extra Resource Consumer Group PRIO04 to be used in this plan. To do so activate the context menu on Resource Consumer Groups and select Create Resource Consumer Group and complete the popup window that appears. and hit enter to make it all happen. In the left pane you will immediately see the new item (marked by a star) appear. Now go back to the Resource Plans tree and activate the context menu item Add Group to Plan from the PLANBOARD plan in the pending area. and select the PRIO04 group from the list of values. Now activate the context menu Modify Resource Directive from the PRIO04 Resource Plan Directive and complete the parameter window that popped up. Make the changes that you like and hit enter to put them in the pending area.

Now check the validity of the pending area by selecting Validate Pending Area from the Resource Manager context menu. If nothing pops up, the pending area is valid. If there is a problem, it will be told you. In this example I was foolish enough to make an error to show what happens. Click on Resource Plan Directives in the PLANBOARD plan in the Pending area and decide what to change to make the pending area valid. If finally the pending area is valid submit the Pending Area by choosing the appropriate menu item from the Resource Manager context menu. When you do so you will see that the pending area is gone when the action is complete.

In a next episode I will explain how to use the mysterious Generate Monitor SQL that is also in the Resource Manager context menu.

Ronald - who is counting down to the publication date of his book ...

contents of planboard presentation about scheduler

During my presentation about the Oracle Scheduler I demonstrated a little script that generated a lot of jobs, first a series of jobs tied to a window and later the same series of jobs not tied to a schedule. This demonstrated that when the jobs were tied to a window, the order of the executions was mostly in the alphabetical order of the job class that were defined for the jobs. Not really what I expected, can be a bug, it is not documented how the order should be but I expected more random choices of jobs over the job classes.

You can find the code here. Download the zipfile, unzip it, run install.sql as a dba and check the job counts and resource usage for the defined resource consumer groups. Run the remove.sql to get rid of the installed items. For the remove you also need to be connected as a dba.

For monitoring the job counts you can use the following sql:

select sysdate
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO1%') p1
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO2%') p2
,(select count(*) from all_scheduler_jobs where owner = 'PLANBOARD' and job_name like 'STATS_PRIO3%') p3
from dual;

The cpu consumption can be monitored using:

select sysdate
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO1')PRIO1CONSUMED_CPU_TIME
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO2')PRIO2CONSUMED_CPU_TIME
,(select CONSUMED_CPU_TIME from V$RSRC_CONSUMER_GROUP where name = 'PRIO3')PRIO3CONSUMED_CPU_TIME
from dual;

In the presentation I had these two queries running as a monitor in DbVisualizer.
To make DbVisualizer more useful in combination with Oracle Scheduler and Oracle Resource Manager you might find it helpful to download and use this oracle.xml file that I made with many contributions of Nathan Aaron in it. I am sure that this will somehow find its way in the production release of DbVisualizer in the near future. For now, download and unpack the file. Place it in the profiles folder in the DbVisualizer package. It is fully up to date until DbVisualizer version 6.5.6

tuning redo apply on zfs

We are running large databases on zfs filesystems. The performance is not yet optimal but sufficient to be able to work. Tuning is an ongoing process. Recently we applied the latest zfs patches and introduced some new tunables on the standby site.

Before these changes the redo apply rate was somewhere around 15MB/s. Not a speed monster but enough for our goal.

After these changes on the operating system the apply rate was 2MB/s. Not the speedboost where we were hoping for, we hoped on a similar factor in the other direction.

This meant digging and find a way to improve the situation, with only 2MB/s we could hardly keep up with production. Simple testing with dd showed that we could easily get files read with 150MB/s. During the monitoring of the redo apply we saw that to read a single archive, the database needed a few minutes. After the archive was read, it took only 10 to 20 seconds to flush the data to disk. A bit strange.

According to metalink note 387343.1

ALTER SYSTEM DUMP LOGFILE ‘’ VALIDATE;

should show the upperbound of the redo read rate. Using the tracefile we found that the file was read with a rate of 140MB/s. Not bad at all and much better than what we could see happening in the databases recovery process. The conclusion is that the database does not use the same code to read the archive during the recovery as it does when dumping the logfile.

One of the ways to control this is the filesystemio_options parameter that we had set to directIO. We changed this

alter system set filesystemio_options=setall;

The result was surprising, the redo read rate went up to 140MB/s, better than we had ever seen before. A quick test with the dump logfile showed that now the dump rate was with 40MB/s. This again shows that the metalink note is not exactly telling the truth. 40MB/s is not the upperbound of the read read rate...

equipment used:

• 32 core Sun M5000.


• Oracle version 10.2.0.4


• Solaris version 10.6 (februari 2009)


• redo file size 400MB.


• db_cache_size=4G


• dbsize=2,5T

Conclusion:
we can use zfs for large databases but need to be aware of strong reactions when tuning zfs on the OS. ZFS is getting better every patch release.

how to install oracle 10.2.0.4 on intel (client) macs - quick

Finally oracle released the long awaited 10gR2 release of the rdbms for intel macs. The release notes show that it is far from complete. Missing is (amongst others) support for RAC, ASM and APEX.

a Quick installation

In this quick installation we forget everything that we normally do for a production capable installation. It does not make sense to setup for production on a system that is not supported. This installation is on a client version of MACOSX 10.5.6

pre install work

Because the runInstaller script references /bin/awk, /bin/sed and /bin/uname, make those tool available for the installer using the terminal application, found in /Applications/Utilities/

sudo ln -sf /usr/bin/awk /bin/
sudo ln -sf /usr/bin/sed /bin/
sudo ln -sf /usr/bin/uname /bin/

Adjusting the runInstaller script is not going to help, the installation will fail in the link process. Make sure that the location in which you are going to install exists or at least, that the installer is able to create it. In this case I created a directory

sudo mkdir /Users/oracle/
sudo chown ronr

The installation

after unpacking the db.zip file that was downloaded from http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204macsoft_x86-64.html startup the terminal application found in /Applications/Utilities and use

cd Downloads/db/Disk1/
./runInstaller

The installer pops up enter the desired location for the installation. In this case I chose to install in /Users/oracle/product/rdbms/10.2.0.4 and deselected the 'Create Starter Database'. We can do that later. Hit the Next button admire the summary and click on the Install button. After a while the installer asks to run the root.sh and after doing this the installation part is ready. Run the root.sh using

sudo /Users/oracle/product/rdbms/10.2.0.4/root.sh

post install

add /Users/oracle/product/rdbms/10.2.0.4 to /etc/oratab
using vi or just do so using

echo "ORCL:/Users/oracle/product/rdbms/10.2.0.4:N" >>/etc/oratab

Assuming that you are going to create a database with instance name ORCL. The root.sh installed the oraenv script in /usr/local/bin and when you are lucky, this directory is in your PATH environment variable. If not, make sure that is by issuing

PATH=/usr/local/bin:$PATH
export PATH

type

. oraenv

mind the leading dot, it is important because this will make sure that the oraenv script is able to set your environment for your current shell. Without the dot it will do what it normally does, only in the process where the oraenv runs. After oraenv exits, that carefully crafted environment is also gone.... So use the dot. When oraenv asks to enter a ORACLE_SID enter ORCL, or whatever you put in /etc/oratab.

$>sqlplus
dyld: Library not loaded: /b/227/sqlplus/lib/libsqlplus.dylib
  Referenced from: /Users/oracle/product/rdbms/10.2.0.4/bin/sqlplus
  Reason: image not found
Trace/BPT trap

He, this is not what I was hoping for .... It looks like sqlplus thinks that its libraries are installed in /b/227/sqlplus/lib/ and not in /Users/oracle/product/rdbms/10.2.0.4/bin/sqlplus/lib. We can fix this by setting the library load path

DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export DYLD_LIBRARY_PATH

sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 11 23:18:14 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12547: TNS:lost contact

Again not the friendly 'connect to an idle instance' what we were hoping for. Now it is time to dive in the manual.

ulimit -s 32000

is not going to help. Up to the kernal parameters

sudo /usr/sbin/sysctl -w kern.sysv.shmall=2097152
sudo /usr/sbin/sysctl -w net.inet.ip.portrange.first=1024
sudo /usr/sbin/sysctl -w kern.maxproc=2068
sudo /usr/sbin/sysctl -w kern.maxprocperuid=2068

Still no good.

ulimit -n 2048

just to see:

sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 11 23:43:58 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

Despite the fact that I clearly said to take my own private group as dba group I see:

test10 ronr@minimac2:/Users/oracle/product/rdbms/10.2.0.4/rdbms/lib
$>cat config.c 

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};   

I can change the config.c and relink the installation to get this into effect but a little cleaner is to create the dba group and make myself member of this group:

sudo dscl . -create /groups/dba 
sudo dscl . -append /groups/dba gid 510 
sudo dscl . -append /Groups/dba GroupMembership ronr

Now the dba group exists and I(ronr) am a member of the dba group.

sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 12 00:01:20 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

and the listener is also working:

snrctl start

LSNRCTL for MacOS X Server: Version 10.2.0.4.0 - Production on 12-APR-2009 00:04:48

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /Users/oracle/product/rdbms/10.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for MacOS X Server: Version 10.2.0.4.0 - Production
Log messages written to /Users/oracle/product/rdbms/10.2.0.4/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=minimac2.local)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for MacOS X Server: Version 10.2.0.4.0 - Production
Start Date                12-APR-2009 00:04:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /Users/oracle/product/rdbms/10.2.0.4/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=minimac2.local)(PORT=1521)))
The listener supports no services
The command completed successfully

final note

Oracle clearly states that this installation is not supported. This is a different statement as: 'it is not working'. At the moment I have no time to take this any further but I am sure that the listed procedure is enough for many to play with the database on a macbook.
Happy hacking !

moving scheduler jobs from one to an other database

howto imp/exp a job without causing a running job on imp

The challenge here is to transport jobs defined in a source database to a target database, without having the jobs running after the import finishes. We can do this by disabling the job in the source database but because this is an important production job that has to run very frequently this is not an option.


This is where we can use a schedule. In the schedule in the source database, we should take into account that there are scheduled outages for maintenance. We could use this to prevent the job from running in the target database. Lets say in the source database we have a schedule for maintenance that excludes executions during planned maintenance. We can create a schedule with the same name in the source database, that has the maintenance period active before the import takes place.

The mintenance schedule could be defined as something like

--/
begin dbms_scheduler.create_schedule(
	schedule_name=>'maintenance', 
	start_date=>sysdate+10000, -- about never ....
	comments=>'allow jobs to run'); 
end;
/

Define the job using a schedule or a job_interval that has an exclude=maintenance.

freq=secondly;exclude=maintenance;

In the target database we can define the schedule called maintenance to be active forever.


This effectively prevent the job to run in the target database after the import.

--/
begin dbms_scheduler.create_schedule(
	schedule_name=>'maintenance', 
	start_date=>sysdate, 
	repeat_interval=>'freq=daily;', 
	end_date=>sysdate+2, 
	comments=>'dont allow jobs to run'); 
end;
/

During the import - which has to be a datapump import, the schedule is not overwritten/replaced by the schedule from the source database.
This allows us to export a running job, without having to change it, to import it, without immediatly starting to run. In the example above, the job will eventually start running. Don't forget that the exclude only excludes complete days, no smaller periods. Maybe something for a future update?


I hope this is helpful for someone.