Create the Instance
The following (Listing #7) script will build the basic database instance using locally-managed tablespaces and with a database character set of AL32UTF8 and NLS character set of AL16UTF16.
These character set parameters make it possible for the database to accurately store unicode information within the database, in case this functionality is required in the future. I have also specified a block size of 16K; however, if you want to save a little disk space on a laptop configuration, then you might set this parameter to 4K or 8K.
Listing #7 -- filename: db_create.sql
Use SQL/Plus (from an Oracle account terminal session) to execute this script to create the instance. If the db_create_sql file is not located within the directory where you invoked SQL/Plus, then you will need to provide the full path for SQL/Plus to use when executing the file.
oracle% sqlplus /nolog @/Users/oracle/db_create.sql
SQL/Plus will run for a couple of hours to create the instance. When it completes, you will be left back at the UNIX prompt. During the database build process you should see quite a few commands scrolling up on the screen. You want to look for situations in which you get "Not connected to Oracle" types of errors. This can be an indication that the database did not actually get created. It is a good idea to get in the habit of checking the /Users/oracle/bdump/alert_prod1.log file to see if some type of error has occurred that prevented the database from being created --for instance, if your computer didn't have enough memory to create the database instance, you would see that error listed in the alert_prod1.log file. Important issues such as missing/damaged database files will also be listed in the alert.log, as well.
You can also use the
ps command to check which oracle account processes are
currently running on the computer. Here is an example listing of the
ps command that shows the various Oracle processes that are running, like
su-2.05a$ ps -aux | grep oracle
oracle 1015 0.0 0.1 66852 692 std S 3:27PM 0:00.08 -su (bash)
oracle 1024 0.0 0.9 345236 6004 ?? Ss 3:50PM 0:02.40 ora_pmon_prod1
oracle 1026 0.0 0.8 344888 5380 ?? Ss 3:50PM 0:02.55 ora_dbw0_prod1
oracle 1028 0.0 0.8 344676 4972 ?? Ss 3:50PM 0:02.55 ora_lgwr_prod1
oracle 1030 0.0 0.8 344692 5320 ?? Ss 3:50PM 0:02.58 ora_ckpt_prod1
oracle 1032 0.0 0.8 344676 5000 ?? Ss 3:50PM 0:02.43 ora_smon_prod1
oracle 1034 0.0 0.8 344676 4964 ?? Ss 3:50PM 0:02.42 ora_reco_prod1
oracle 1036 0.0 0.8 344676 4980 ?? Ss 3:50PM 0:02.64 ora_cjq0_prod1
oracle 1038 0.0 0.8 345260 4976 ?? Ss 3:50PM 0:02.32 ora_s000_prod1
oracle 1040 0.0 0.8 345104 5284 ?? Ss 3:50PM 0:02.39 ora_d000_prod1
oracle 1042 0.0 0.8 345104 5292 ?? Ss 3:50PM 0:02.34 ora_d001_prod1
oracle 1044 0.0 0.8 345104 5300 ?? Ss 3:50PM 0:02.20 ora_d002_prod1
oracle 1046 0.0 0.8 348788 5064 ?? Ss 3:50PM 0:02.25 ora_arc0_prod1
oracle 1048 0.0 0.8 348788 5052 ?? Ss 3:50PM 0:02.13 ora_arc1_prod1
oracle 1051 0.0 0.0 66140 56 std R+ 3:51PM 0:00.01 grep oracle
Now that the database has been built, you should start the listener so that remote connections can be made to the database.
oracle% lsnrctl start
You should now be able to ping the database with
oracle% tnsping prod1
tnsping gives an error, then check to make sure that the tnsnames.ora file
exists and that the connection string "
PROD1" exists within the file.
If you try to use
tnsping with a connection string that does not exist, then
you will get the following error:
TNS-12545: Connect failed because target host or object does not exist
If the listener has not been started, then you will get the following error:
TNS-12541: TNS:no listener
Log In Locally
You can log directly into the database from the server where Oracle has been installed by using the SQL/Plus. You will need to do this from the oracle UNIX account.
oracle% sqlplus /nolog
conn / as sysdba
After being logged in as sysdba, you can start up, shut down, create database user accounts, and perform any other required DBA tasks. Here is how you would create another user named user1 (with password "welcome") with non-DBA privileges in the database:
create user user1 identified by welcome
default tablespace users
temporary tablespace temp
quota unlimited on users;
grant connect to user1;
grant resource to user1;
Log In Remotely
You can use SQL/Plus to make a remote connection from another computer by using the format:
oracle% sqlplus /nolog
conn user1/welcome sysdba@<host>:<port>:<sid>
which would look like this:
conn user1/welcome sysdba@G4:1521:prod1
The above listed connection method should work even if you have problems with the tnsnames.ora file on your remote computer. If the tnsnames.ora file is set up correctly on your remote computer, then you can simply use the connection string within tnsnames.ora:
conn user1/welcome sysdba@prod1
When configuring a production database, there are many other post-install tasks which should be completed. These tasks include configuring archivelog mode, writing/installing/scheduling/testing nightly RMAN backups, regularly scheduling full or partial database exports, writing/installing/scheduling performance tuning scripts, and documenting the database and its recovery procedures.
Please feel free to look through the rest of the example scripts on my Web site; these are used for building Oracle 9.2 databases on Mac OS X. I intend to update these scripts as Oracle provides updated beta versions and the final production software for Mac OS X (currently expected in Q2 2003).
There is also a top-level Web page that you may find helpful if you want to compare the Mac OS X database creation scripts to the scripts that are used to create Oracle 9.2 databases on Solaris or Windows.
I have covered a lot of ground in this series of articles. Everything from what the release of Oracle 9i on Mac OS X means to Oracle, Apple, Mac OS X users, and UNIX DBAs to the actual process of installing Oracle 9.2 on Mac OS X.
We have succeeded in creating an Oracle 9.2.0 database on Mac OS X in a manner that eliminates a number of the limitations imposed by Oracle's official instructions. This installation procedure:
- Allows the DBA to install the Oracle database software and datafiles anywhere on the server, attached hard drives, or disk array.
- Uses a DBA-defined name for the Oracle Home directory -- instead of the default orahome directory.
- Makes use of the standard Oracle Optimal Flexible Architecture (OFA) layout for database files -- instead of using the default $ORACLE_HOME/dbs directory.
- Configures the database and listener for automatic startup during the system booting process -- instead of requiring the DBA to perform these tasks manually. (Shutting down the database must still be done manually, due to a Mac OS X 10.2.2 limitation.)
- Eliminates the need for the DBA to manually run the Oracle
DBENVscripts before using SQL/Plus or other database utilities.
I look forward to Oracle's release of additional beta versions and finally, the production release of Oracle 9i on Mac OS X.
Return to the Mac DevCenter.