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.